Sometimes you see all the data but only with the right visualization you’ll grasp what really changed. For example when looking at a table with absolute numbers of advertising revenue distributed to different types of media in Germany in the last twelve years I saw: The market shrunk and daily newspapers obviously lost advertising between 2001 and 2013. But what else?

After visualizing this data as a bump chart I could see at a glance when what happened: We see different shifts in this twelve year. First free newspapers overtook paid magazines (2005), then display ads on the web overtook radio (2008) and then TV overtook daily newspapers (2010). A bump chart can be useful for analyzing this kind of data.

Net advertising revenue in Germany (in million Euros)

advertising medium2001200220032004200520062007200820092010201120122013
sum21723201411928119583198342035020812203661836718744189331837617839
daily newspapers5642493744554501447745334567437336943638355732322917
TV4469395638113860393041144156403636403954398140374125
mail3256333533043398339833193347329230812984298728642600
free paper1751170217461836189819431971200819662011206020011932
magazine2092193518621839179118561822169314091450144012811235
yellow pages1269125012201196119711991214122511841155113910951019
trade journals107496687786590295610161031852860875858889
outdoor advertising760713710720769787820805737766811867891
radio678595579618664680743711678692709719746
online18522724627133249568975476486199010541151
weekly newspapers287268225240253260270266208218214199175
cinema170161161147132117106777275858880
newspaper supplement90978690919090878286858179
Source: Zentralverband der deutschen Werbewirtschaft


But how to build one? Here is a How-to:

1. Prepare the data

I used data from a table I prepared for Wikipedia. It is easy to grab this kind of numbers directly from Wikipedia, mark the table (without the headline) and paste it into a spreadsheet like the free (as in speech and beer) Libreoffice Calc (here is the odc-file).

screen_8

Then save it as a csv-file (comma separated values). That is a format tools like the one we will be using next interpret. Open the saved file with a text editor (like Notepad++ or Textwrangler).

screen_10

See all the commas? Great, they separate the values in a way Datawrangler understands. Copy the data.

And paste it here at Datawrangler. This is a tool which can transform a spreadsheet a human can easily read into a spreadsheet that visualization tools like the one we will use can interpret. The difference: We like many figures in a row (if the reading direction follows time), software tools often like a normalized format better: one figure, one year, one data type per row.

screen_11

With Datawrangler this is done in seconds.

Select the first row (containing years) and click on promote in the menu above, than on promote row #1 to header on the left.

screen_13

Next step is the real reshaping of the data. First select all columns containing figures by clicking on every year in the first row with the apple or shift key pressed. When all columns are selected you choose fold from the menu above and than the option fold 2001, 2002 (and so on in my example) using header as a key.

screen_14

It should look like this:

screen_15

You export the data with a quite small export button on the left.

screen_16

3. Visualize the data

Now let’s head over to RAW – the missing link between spreadsheets and vector graphics a team at the DensityDesign Lab of Politecnico di Milano developed. Paste the data here.

Select Bum Chart as your preferred output.

Scroll down and map the dimensions. That means: You tell which of the three numbers in every row is what (Date, Group, Figure).

screen_17

And now you’re done! You can customize width, hight and colors and so on and copy the embed code into your blog or wherever you want to publish the bump chart.

4. Customize and enjoy

Some details in the embed code can only be changed with a good text editor. For example: In my example RAW added a comma to every year. I don’t need that, so I fixed that with an editor. I searched for 2,011 and exchanged that with 2011. You can change stroke-width, font-size and the font-family used for your categories too.

screen_20

You can work on the downloaded SVG with a vector based graphics editor like Inkscape and export the file in all sorts of formats. The PNG-File above is an export from Inkscape. Below you see an embedded version.

2001200220032004200520062007200820092010201120122013AnzeigenblätterAußenwerbungFachzeitschriftenFernsehenFilmtheaterHörfunkOnline-AngebotePublikumszeitschriftenTageszeitungenVerzeichnis-MedienWerbung per PostWochenzeitungenZeitungsbeilagen