Visualizing carpooling patterns in Germany

Mitfahrgelegenheit

Some time ago I was working on a new blog on sharing economy lets-share.de. The blog was specialized on assessing global and local sharing platforms and providing an overview of events based on the sharing model. After helping to develop the digital strategy and the exact focus of the blog, I decided it was high time to add some data-driven stories visualizing important issues of the sharing economy, which change our lives.

Idea

Mitfahrgelegenheit.de is the German version of Carpooling.com and is highly popular in Germany. I decided to create a visualization, which would show carpooling patterns between cities in Germany and would possibly reveal hidden connections.

I had to work with these tables,

I had to work with these tables,

Workflow

I called the German office of Mitfahrgelegenheit.de and asked them for the data. What I needed was the database of all travels in the past years. Well, I didn’t get it. After that, I had to rethink the concept and think about my limitations more seriously. After all, I had no developer and no designer to support me. That’s where I came to the idea to limit my visualization just to one day, which was May 1, which is a public holiday in Germany.

Extracting data was the most difficult part: I first resorted to the add-ons and the Google Spreadsheets formula helping to extract tables from a website. However, the sheer amount of data points made me think of a different solution. I needed a tool, which would help me extract next tables from numerous pages following each other.

Using Outwit Hub to scrape the data

Using Outwit Hub to scrape the data

Outwit Hub proved to be the tool I wanted. In the free version, it can extract up to 100 rows. After buying its full version, I was able to extract all the rows I needed by copying and pasting the URL of the first page and clicking on the arrow symbol which means “extract all following tables as well”. I then saved the data as an Excel file. Since I didn’t have much rows, it was quite plausible to use Google Spreadsheets (which can only work with a limited number of rows).

Sorting data with fusion tables

Sorting data with fusion tables

After that I cleaned the data using Excel and started to make sense of it using Google Pivot Tables.

Using Google Pivot Tables

Using Google Pivot Tables

Limitations: I had no coding/scraping experience, no coder at hand and a very limited time.

Tools  I used:

  1. Google spreadsheets (=importHTML (url, query, index))
  2. Google scraper add-on
  3. OutWit Hub (paid version)
  4. Excel
  5. Google Spreadsheets
  6. Google Pivot Tables
  7. Google Fusion Tables: creating the network diagram and embedding it on the blog
  8. Datawrapper: creating bar charts with top-10 cities
The first attempt to make a graph

The first attempt to make a graph

Outcome and outlook

This was the first data journalism piece I ever created, and that was the piece I created on my own. I have noticed how important it was to have a basic knowledge of statistics (median, modus, correlations) and be able to write a couple of lines of code on your own. But, most important, you need to have a clear idea of what you want to tell the readers with your story. If I have had more time and resources, I would have done the following:

  1. Connect the visualization to the API of Mitfahrgelegennheit.de should they have one.
  2. Create a feed, which would update the visualization automatically. That way, the users would always be able to see real-time information.
  3. Create historical view of the travels to different cities. In an ideal case, every city should have a travel history and be comparable with other cities (how many people came to the city and how many people left on a particular day, compare two and more cities, add events like Oktoberfest to put this into a larger context).
  4. Personalize the story and let users change their city of destination and have a look at the most common price they would have to pay or the time with the largest number of offers. Ideally, these should be a link, which would bring users direct to the particular offer at mitfahrgelegenheit.de. You could even integrate the booking form into the visualization.
Advertisements

One thought on “Visualizing carpooling patterns in Germany

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s