5 Replies Latest reply on Oct 28, 2017 6:46 PM by Steve Rohde

    Structuring Data for Spider Graphs

    Steve Rohde

      OBJECTIVE

      Render a "spider map" from a table of point-to-point trips in a station-based bike sharing system.

      Assign measures such trip count, average duration and elevation difference to station start-end pairs (i.e. spider line) as line attribute (e.g., line or colour).

       

      BACKGROUND

      I'm working with a data file of bike sharing trip history. The bike sharing system allows a user to check out a bike from a docking station, ride it, and then return it to another docking station.  For each trip, the system records the station ID and date-time of the start and end of the trip.

       

      DATA

      My primary data table is structured like this...

       

           Table 1. Trip Records

      1.jpg

       

       

      I also have a table of station information.

       

           Table 2. Stations

      2.jpg

       

       

      I'm able to add location (lat/long pair) and elevation for the start and end stations by creating two left joins via station code (one for the start station and another for the end station).

      3.jpg

       

      The examples above are a subset of my real-world data set that contains ~10,000,000 point-to-point bike trip records for ~500 bike docking stations.

       

      PROBLEM

      I'm able to do all sorts of interesting visualizations but there's one type that I cannot figure out: spider maps.  I'd like to be able to show, for example, the top 5 most popular destinations from a selected station on a map, with the start station show as a centre point and 5 lines radiating out to the various end stations.

       

      Tableau Help has a detailed, example-filled how to article about this >>  Create Maps that Show Paths Between Origins and Destinations in Tableau. Coincidentally,  one of the examples (the second one) in this article is about trips in a bike sharing system. Unfortunately, the source data for the example is structured differently than mine

       

      My Data:  One trip between two station is represented by one row, like this:

      5.jpg

      Tableau Example Data: One trip between two stations is represented by two rows, like this:

      4.jpg

       

      Tableau states explicitly in the help article, "...there should also be two rows in your data source for each path."

       

      I can easily add the path ID as a calculated field ([start-station-code]+"_"+[end_station_code]) but I don't know what to do beyond that.  I've even programatically created a table of all possible station-to-station paths that looks like this...

      6.jpg

      ...but I don't know if I can really use it.

       

      REQUEST

      I'm looking for suggestions for things I might do within Tableau that would allow me to use my exiting data source as-is to create a spider map, rather than re-structuring the data table (e.g., create two rows from every one row) outside of Tableau.