6 Replies Latest reply on Mar 30, 2018 8:56 AM by Sarah Battersby

    Creating Spiderweb Chart for the From and to location

    Bhargav Reddy J M

      Hi,

       

      I have the date from State to State as below. I want to draw a spiderweb chart as from location in center and lines drawn to other states. I went through some online discussions for creating the path ID but i couldn't do that. How to create the path ID and plot the lines

       

      From Stateto State
      AlabamaAlabama
      AlabamaAlaska
      AlabamaArizona
      AlabamaArkansas
      AlabamaCalifornia
      AlabamaColorado
      AlabamaConnecticut
      AlabamaDelaware
      AlabamaFlorida
      AlabamaGeorgia
      AlabamaHawaii
      AlabamaIdaho
      AlabamaIllinois
      AlabamaIndiana
      AlabamaIowa
      AlabamaKansas
      AlabamaKentucky
      AlabamaLouisiana
      AlabamaMaine
      AlabamaMaryland
      AlabamaMassachusetts
      AlabamaMichigan
      AlabamaMinnesota
      AlabamaMississippi
      AlabamaMissouri
      AlabamaMontana
      AlabamaNebraska
      AlabamaNevada
      AlabamaNew Hampshire
      AlabamaNew Jersey
      AlabamaNew Mexico
      AlabamaNew York
      AlabamaNorth Carolina
      AlabamaNorth Dakota
      AlabamaOhio
      AlabamaOklahoma
      AlabamaOregon
      AlabamaPennsylvania
      AlabamaPuerto Rico
      AlabamaRhode Island
      AlabamaSouth Carolina
      AlabamaSouth Dakota
      AlabamaTennessee
      AlabamaTexas
      AlabamaUtah
      AlabamaVermont
      AlabamaVirgin Islands
      AlabamaVirginia
      AlabamaWashington
      AlabamaWest Virginia
      AlabamaWisconsin
      AlabamaWyoming
        • 2. Re: Creating Spiderweb Chart for the From and to location
          Sarah Battersby

          One thing you will need to do to the sample table of data that you put in your question is make it so that you have two ROWS for each path.  You currently have two COLUMNS for each path (From State and To State). 

           

          For example, you have:

          From Stateto State
          AlabamaAlabama
          AlabamaAlaska

           

          And you need something more like this (you don't really need both from and to state in the same row in the final table, I just left them for reference):

           

          From Stateto StateOrigin or DestinationPathID
          AlabamaAlabama

          Origin

          1
          AlabamaAlabamaDestination1
          AlabamaAlaskaOrigin2
          AlabamaAlaskaDestination2

           

          I've found that an easy way to convert your data structure to two rows for each path is to just add two new columns (Origin and Destination); give all rows in the Origin column a value of 1, give all rows int he Destination column a value of 2, and then pivot on these two rows in Tableau. 

           

          You can then create your unique ID using a calculated field of the from-to fields and it should work to make the map.

           

          I have written up some information about how to create these maps here: Origin-Destination Maps (or Flow Maps)

           

          -Sarah

          Tableau Research

          • 3. Re: Creating Spiderweb Chart for the From and to location
            Bhargav Reddy J M

            Hi Aditya,

             

            I have tried that and i couldn't understand how to generate PathID for the data i have of 360*360 cities.

             

            Looking for the logic which could help me to generate the pathID.

             

            Regards,

            Bhargav

            • 4. Re: Creating Spiderweb Chart for the From and to location
              Bhargav Reddy J M

              Hi Sarah,

               

              Thanks for the information. But i have 360*360 postcode city combinations and the rows to duplicate and assign the Origin and Destination flag and create PathID. Is there a way other than this to achieve this using some table calculation method!

               

              Best Regards,

              Bhargav

              • 5. Re: Creating Spiderweb Chart for the From and to location
                Sarah Battersby

                No matter where you do the prep of the data (adding a column in Excel and pivoting in Tableau desktop, using Maestro (in Beta), writing a Python script, etc.) in order to draw an origin destination map you will need one row for every stop along the line.  If you just have two points in every line (origin and destination), each unique path will require TWO rows. 

                 

                In Tableau desktop you can't pivot on calculated fields (to the best of my knowledge).  I believe you can pivot on calculated fields in Maestro if you want to try the Beta of that for your data prep (Welcome to Tableau's Pre-Release and User Research Community ) - I will try that out later today to confirm.  

                 

                Even with 360*360 combinations (I assume this means you have a table with ~129k rows) you can probably create the new fields in Excel in about a minute.  Then take the file back into Tableau and do the pivot. 

                 

                Otherwise, you could write a python script that would re-organize the data so that each origin-destination pair on a row was split into two rows, but I would bet that would take longer than using Excel (or probably even longer than signing up for the Beta program, downloading and installing Maestro, and doing it there )

                 

                Once your data is prepped (two rows for every path), the rest is all table calculations in Tableau.  One table calc to give each path a unique ID (I'd suggest something simple like a concatenated string of "origin" + "destination"), and then make the map.

                 

                -Sarah.

                Tableau Research

                • 6. Re: Creating Spiderweb Chart for the From and to location
                  Sarah Battersby

                  For reference - I just tried out the data prep option in the Maestro Beta - it took about three minutes (only this long because I hadn't used it in a while and forgot where some of the buttons were located...it would be faster otherwise). The prep flow looks like this:

                   

                  Once the data was prepped, I opened in Tableau.  Created a calculated field for the STATE to use in the path (simple calculation: if [Pivot1 Values] = 'Orig' then [From State] else [to State] end)

                   

                  Then I had a flow map:

                   

                  -Sarah

                  Tableau Research

                  1 of 1 people found this helpful