10 Replies Latest reply on May 23, 2017 12:48 AM by Norbert Maijoor

    How to generate a map from list of global city names only?

    Stacey Marcelle

      Hello,

       

      I apologize; I am a new user of Tableau Public.  A primary reason for trying to use it: Microsoft Office (Excel) for Mac is kludgy; too many things simply don't work or are inefficient.  I have a relatively small set of data, a list of the top 100 global cities and a list of respective populations from 2010 - 2100. 

       

      I wanted to plot the data on a map and see how it changes over time.  Are there larger migration trends (i.e., toward Africa and Southeast Asia)?

      I just want to begin working with it in Tableau, mostly to see what it is capable of.

       

      Given Bob Lyons article on using packaged workbooks I was reluctant to post anything. However, in lieu of a "Welcome" page and documentation of what is where on the community pages, how to get started, join a group, etc. (I assume I already am a member since I signed up).  I found documentation on the importance of a package workbook, and how to create one, etc. but nothing about how to pose general questions especially for Tableau Public users.  Perhaps there is a different forum altogether.

       

      1. Videos: I watched all of the tutorials;

      2. Packaged workbook: Tableau will not allow me to save anything period, unless I have a "Viz" to upload to public.  I can save to the Tableau public server only.

       

      I appreciate any and all help.  Sorry again for wasting anyone's time.

       

      Sincerely,

      Stacey

        • 1. Re: How to generate a map from list of global city names only?
          Norbert Maijoor

          Hi Stacey,

           

          Find my approach as reference below and stored in attached workbook version 9.3 located in the original thread.

           

           

          What did i do..

           

          1. I "pivoted" the dataset to get the years as an Dimension. Pivoting is explained here

           

          2  I set a Geographic role to City

           

           

          3. Double clicked City on which the worksheet is "filled" with the map depicted

           

           

          4 38  of the total of 142 cities  are not "recognized" by the tableau city database

           

           

          5. For the 38 cities you could enter Latitude and Longitude manually.....

           

           

          6. I dropped Years on the Pages Shelf which is explained here, Pages Shelf  and Value on Colour and Size Shelf

           

           

          7. i defined an extract

           

          8. and published the workbook to Public

           

           

           

           

           

           

          Hope it helps. Let me know.

           

          Regards,

          Norbert

          2 of 2 people found this helpful
          • 2. Re: How to generate a map from list of global city names only?
            Prayson Wilfred Daniel

            Norbert has answered your question brilliantly. This is for those who wants more accurate geolocation of specific address e.g. Gurrevej 45,2560 Hvidovre.

            Sometimes, generating one own latitude and longitude of specific locations is better than Tableau's generated geolocation.

             

            Using Python to automate boring stuff, you can write a simple 30 lines(or less) scripts that will pass your address into Google Maps API(or other services) and return geolocation.

             

            Here is how:

            1. Get the list of the addresses you want geolocation. Save them as CSV(e.g. Cities.csv)

            2. Write a simple Python script that will append your Latitudes and Longitudes (e.g. geoGoogle.py #github file).

             

             

            3.Make sure you have the script and CSV file in some folder (e.g. ....\Documents\Python Scripts). Run the script: python geoScript.py Cities.csv

             

            4. The output will have geo+yourfilename.csv (e.g. geoCities.csv)

             

            5. Left join your data to get your Geolocation

             

             

             

             

            And there you have it.

            1 of 1 people found this helpful
            • 3. Re: How to generate a map from list of global city names only?
              Ritesh Bisht

              Hi Stacey,

               

              There are few Unknown locations (37) , This will help you to update unknown location  Custom Geocode Your Data

              But coming back to your question if you want to see your data as a Map.

               

              1) Connect your excel sheet with Tableau then change the 'Cities' dimension to  Geographical Role as below.

               

              Screen Shot 2017-05-17 at 12.31.22 PM.png

               

              2) After that double Click on 'Cities' dimension you will see a Map.

               

               

              3) Now Create a calculation as Diff

              [ 2100 ]-[2010]

               

              Screen Shot 2017-05-17 at 2.00.38 PM.png

               

              4) Now drag this to Color

              Screen Shot 2017-05-17 at 2.01.19 PM.png

               

              Here you can Notice ,

               

              Trend Shows that we have Drastic Population (Darker the color (towards red) shows more increase in Population)

               

              Here you can see Reddish dots predominantly towards South Asia (India) and parts of Africa

               

              NOTE that I directly equated 2010-2100, you can have phase by phase analysis

               

               

              Screen Shot 2017-05-17 at 1.53.38 PM.png

               

              So this is a basic way to see the same , I have not made it too complicated, hope it helped , if yes please mark it as Helpful .

               

              Let me know for any queries.

               

              Thanks,

              Ritesh

              1 of 1 people found this helpful
              • 4. Re: How to generate a map from list of global city names only?
                Stacey Marcelle

                Hello Norbert,

                 

                I greatly appreciate your detailed and highly prescriptive feedback and explanation on the steps you would take to answer my specific questions.  Of course, those steps elicit more questions which, at the risk of alienating myself within the community, I will document in the event others have similar questions, either previously or in the future. 

                 

                For product development/voice-of-the-customer driven initiatives:

                     Setup: Tableau Public on MacAir 2 | Sierra v 10.12.4 | Microsoft Office/Excel 2016 for Mac

                          Background (context for consumer needs and prior consumer experience):

                 

                I recently discovered a unique data source online - detailed and well-documented methodology from the University of Ontario and Global Cities Institute's world's largest city population estimates through 2100 for an expanded set of 101 global cities, from which one might derive robust projections for a broad range of useful applications. The data have intrinsic value, notwithstanding multiple operational hurdles:

                    1. The data are only available in PDF format.  'Laid off while on FMLA while caring for my dying spouse' [he passed away less than one week later] and a relocation to start over compounded by a dearth of free PDF-to-MS Excel file-conversion tools (or more specifically, tables within it) required budget-oriented fiscal stewardship (i.e., manual data entry despite my former high six-figure salary.  That process was by itself inefficient;
                    2. My "MS-Office/Excel for Mac" setup and poor documentation required unforeseen workarounds to reproduce the data.  Based on prior experience, Excel obfuscates the completion of too many would-be simple tasks to entrust it with such potentially insightful data beyond the task of converting the data itself. Thus, I intended to use another Tableau to uncover, reveal and evangelize the story.  I set out to learn what I could by watching training videos, using past data sets and mocking up other data sets and participating in e-Learning sessions;
                    3. As a researcher by training and experience, my mind goes naturally to a cross-tab format - which I understand from blogs, etc. is the bane of anyone trying to use Tableau.  I was informed directly by the Tableau product team that the product "has definite limitations when it comes to traditional research and those familiar with research methods" - with one person noting it "cannot handle multiple responses or any of the typical market research frameworks."  Yet to date, I cannot find documentation or insights on how to anticipate and mitigate the likelihood of those issues creeping into future interactions with Tableau. But I digress.]

                 

                Problem statement: I wish there was an easier way to efficiently and accurately get data into a digital format for consistent analysis across operating systems and platforms.  So I am most grateful for your patience and guidance.

                 

                Norbert: I viewed each of the files referenced in your response. Each step makes sense.

                Do you know of any other resources for those tools (other than rote experience)?  I am not certain how I or anyone would know to take those steps as they are not in any of the 21 videos I watched that I recall. Nor have I discovered likely use cases within Tableau's documentation.

                 

                0. Referenced files: I can click on and view each file referenced from community.tableau.com. But when I click the last item, the system downloads it and then immediately returns an 'error' (see attachment 2 below):

                 

                "Errors occurred while trying to download file "Users/stacey...map.nalwai.twbx".  The load was not able to complete successfully."

                 

                0.a  The workbook you saved and uploaded in version 9.3, I assume you are referencing' the Tableau version number 9.3. 

                Also, are you able to view or save files in Tableau Public as I am using? I assume so but don't know how to confirm.  Much of the documentation I read refers to Tableau Desktop.  I am unclear on the actual product delineation (for example, if Tableau Public is the free version of Tableau Desktop)

                 

                0.b The original data file: the original population estimate data sets are available in PDF-only format online through the University of Ontario website: https://shared.uoit.ca/shared/faculty-sites/sustainability-today/publications/population-predictions-of-the-101-largest-…

                 

                1. Pivot original data: how did you know a 'pivot' could be necessary or helpful?

                From the 'Data Source' tab, I used the "Data Interpreter" tool and it provided no insight about my data nor did it recommend any changes to the format of the data itself.  Should it have?

                 

                1.b. Implications: without the benefit of context (i.e., having seen training videos in which 'trainers' successfully work with sample data sets located on Tableau servers) one might infer that to be manageable, each dimension can occupy only one column regardless of the desire to trend data and regardless of any differences in the definition or 'permutations' of the dimension. How does one know when Tableau can make sense of data that is more than two columns (1 dimension) wide?  I am trying to figure out an analog for why this occurs or is necessary.

                 

                2 & 3 Assigning 'Geographical role' to city & 'Double clicking city to populate the map: how does one intuit this step?  I recall one training video documented the ability to 'double-click' a city/state/country to auto-populate the columns/rows with the corresponding data inputs to produce the associated filled map.  I actually did create five (5) side-by-side bar charts, one for each population-estimate year, though I have not been able to replicate how I did so, without potentially losing all of my feedback, or otherwise creating confusion for anyone else.

                 

                4.  Tableau city/geo database: Doing so on my end yields only two dots on the map: Alexandria and Cairo, Egypt.  On the other 138, I know where every city is so tried to manually select the city from the drop downs.  Despite being an obvious and apparent 'option' the inputs are not retained.  I assume this is what you meant by 'manually entering the latitude and longitude' for each of the missing city locations on the map (in your case, a blissful 38 only). [see attachments 3, 4 & 5 below]

                 

                5. Reconciling 'unrecognized' city aOddly, almost all of the cities listed appear in the Tableau city database - however, due to some limitation likely associated with my access being limited to Tableau public, I cannot affect how those are defined even within my own dataset.

                 

                Also, at this point I want to thank Prayson Wilfred Daniel for kindly offering a version of how to do automate the above process for efficiency. While conceptually I get everything within that work flow, as I am not a programmer I cannot replicate it. But hopefully others find it useful.  [And Prayson, if I am missing something that obviates your solution or there is some reason it will not work given my use of Tableau Public, I am all ears.  I have automated dozens of processes for F50 companies like Amazon, Microsoft and T-Mobile.  While it feels like 'learning Python' is not a task I should take on right now, it's possible I am overthinking the process you outlined.]

                 

                6. Pages Shelf:  I appreciate you doing this step.  [Product feedback] Having read the documentation (found here Shelves and Cards) I am unclear how to anticipate a) the scenarios or use cases in which to replicate this step in the future, or b) (in the event this step is optional, superfluous or sub-optimal) the steps I might take to preclude future need of this step.  Any POV?

                 

                7. Define an extract: [Product feedback] I was able to navigate to a product help page but have not found detail about 'Extracts'.  The Tableau 'help' search function for 'extract' yields a bunch of categories, none of which reference 'extract' so in the interest of time, I will read about ASAP.

                 

                8. Save the file to "Public": I failed to realize that by hitting 'control Z' too many times, I cleared all of my entries.  Hence, the inability to save anything to public in my initial post of this discussion topic.  My 'world cities 2100' abomination is now saved to the Tableau Public server (my apologies to the University of Ontario for leaving your data in such a pitiful state while I learn to visualize it in Tableau).

                 

                Finally, I assume access for each member of the community is personalized based on the revenue Tableau derives from that person (as it is on most sites). I saved the most recent version of my workbook to Tableau Public [it should be available]. Also, I have not yet found documentation on the process for creating an appropriately 'packaged workbook' but I will continue to look; I understand its importance in maintaining the integrity of the Tableau community.

                 

                Thank you also to Ritesh Bisht for offering additional insights on how to tackle these issues.

                 

                Ritesh, I really appreciate your response.  Is that something you were able to do using Tableau Public or were you using some other version? My apologies again, but I cannot open the Tableau file once it is downloaded.

                 

                Regarding Step 2 of your response:

                In hindsight, the basis of your calculation makes sense, notwithstanding Tableau documentation's explicit statement, "Pivoted data do not work with calculated fields" (unless data calculations and calculated fields are different, as I suspect, in which case I will have to read up on how they differ, when and how to use each, etc.)  But I do not recall having seen any mention of 'calculating' differentials or any other calculations within or across data sets in any of the first 21 training videos.  I am likely wrong and I more than happy to learn from doing this in practice.

                 

                From your explanation and the static image you attached, it sounds like the population changes I hypothesize having entered all of the data hold true. Note these changes will likely be attributable more to higher sustained fertility rates and rising life expectancy rates (including reduced infant mortality) within Southeast Asia and sub-Saharan Africa, the collective results of anticipated socioeconomic and cultural, technological, and environmental changes than to mass-migrations.  Moreover, a move away from agrarian methods, the effects of climactic changes from global warming and accessibility to modern conveniences including healthcare will influence future decisions on where to live at an individual and family level.

                 

                Norbert, Ritesh and Prayson (and to everyone who works on or contributes to the Tableau experience), thank you for confirming that Tableau will be a useful tool once I learn how to use it properly.  I am excited to discover the power of Tableau.  And I hope my copious product feedback is useful to those tasked with optimizing and innovating the Tableau experience.

                 

                Kind regards,

                Stacey

                • 5. Re: How to generate a map from list of global city names only?
                  Ritesh Bisht

                  Hey Stacey Marcelle ,

                   

                  Thanks for the appreciation but you copied the wrong Ritesh Bisht earlier , I am the original VERSION

                   

                  Please click below to see the Visualization as mentioned above. I am using Tableau 10.2 version .

                  If you need more info in my section , please feel free.

                  Tableau Public

                   

                  Thanks,

                  Ritesh

                  • 6. Re: How to generate a map from list of global city names only?
                    Stacey Marcelle

                    Hi Ritesh,

                     

                    I apologize for inadvertently acknowledging someone else for your work and assistance. Regarding your feedback:

                     

                    1. The error messages I received were triggered by an old version of Tableau.  

                    2. Do you not recommend pivoting the data as Norbert suggested?

                     

                    I connected the Excel file to Tableau, selected 'City" and defined "Geographic Role" as "City" (not 'none' as is highlighted in the pic).  It does nothing - instead I get 137 unassigned.  Tableau Public

                     

                    It's pretty clear I have no clue how Tableau works.  It's like it is written in a language that is polar opposite from what makes sense to me.  Excel makes sense - plot x and y and if it doesn't look right, swap em.  I wish Tableau was a s flexible - or better yet, intuitive.  Any resources you recommend, I'll happily read or review them.

                     

                    Thank you,

                    Stacey

                    • 7. Re: How to generate a map from list of global city names only?
                      Stacey Marcelle

                      Hi Norbert,

                       

                      I appreciate again you sending such detailed action on your method.  Using Tableau 10.2.1:

                       

                      1.  I still do not understand the purpose of "Pivot", or rather:

                      a.  How to preclude the need for a pivot in the first place - there is no instruction as to how to think about and plan one's data input so one is not necessary.

                      b. Why the Tableau 'data interpreter' does not recommend this.

                      c.  Do you have any insight on either of these issues?

                       

                      2. How did you know to set the geographic role to city?

                       

                      3. Your step 6:

                      How did you know to "Drop Years on the Pages Shelf which is explained here, Pages Shelf  and Value on Colour and Size Shelf"?

                       

                      This does not seem intuitively obvious.  So I am trying to figure out how to learn to do this naturally.  It is not covered in any of the Tableau tutorials/training videos.

                       

                      4.  I have tried to find the file you reference but with Tableau version 10.2.2 open and me logged into Tableau's server, the software cannot/does not access the file.  I wrote to the Tableau team, but alas, as I am not a paid customer, they do not respond.

                       

                      I appreciate any and all additional insight you have on this matter.  I don't know how many hours/days to invest in this one task before I simply abandon Tableau altogether.

                       

                      Thank you very much,

                      Stacey

                      • 8. Re: How to generate a map from list of global city names only?
                        Norbert Maijoor

                        Hi Stacey,

                         

                        Markets are closed now in Europe CET 22:15;)  and we "call it a day".

                        I will come back to you tomorrow at first when the markets open in Europe.

                        Have a nice day.

                         

                        Regards,

                        Norbert

                        • 9. Re: How to generate a map from list of global city names only?
                          Stacey Marcelle

                          Hi Norbert,

                           

                          I understand and appreciate your response.  I await your feedback tomorrow.  Until then, I will try to get more insight from others' responses as well. There is much ambiguity - or at least lack of understanding on my part.

                           

                          Thank you,

                          Stacey

                          • 10. Re: How to generate a map from list of global city names only?
                            Norbert Maijoor

                            Goodmorning Stacey,

                             

                            Below you will find my feedback for each point mentioned.

                             

                            1. I still do no not understand the purpose of "Pivot"

                             

                            Pivoting is explained here

                            In your initial dataset there are 5 measures(2010-2100) and is "modeled" as a "key-figure-model". For this case I need an "account-model" to be able to display one measure depicted against one dimension with multiple values (2010-2100)

                             

                             

                            Key figure model & Account model is explained here

                             

                             

                            2. How to prelude the need for a pivot?

                             

                            The need of depicting one measure against the same dimension (time)  at one location within a visualization in this case your geomap.

                             

                            3. Why the Tableau data interpreter does not recommend this.

                             

                            The Tableau data interpreter will come in action when it recognizes your data is not in a column structure as depicted below

                             

                            Your dataset is provided in a "perfect" column structure. Each column has his own header

                             

                             

                            4. How did you know to set the geographical role to city

                             

                            To get the full functionality of geomaps in Tableau a dimension must have a geographical role. Based on header "Cities" and content recognized is for me obvious to use City

                             

                            5. How did you know to Drop...etc

                             

                            Over the last 3 year I spend may be hundreds of hours on this forum, youtube, and trainingmaterials and I have seen quite some BI-tools in the last 10 years. So doing is for me "learning naturally"

                             

                             

                            6. Server-issue

                             

                            I am not a GURU on the Server side of Tableau:). But the following steps could be an option. Open my workbook in version 9.3 on your desktop and save it against your higher version 10.2.2. and publish it to your server.

                             

                            Once again review my feedback an let me know if there are still questions to be answered.

                             

                            Regards,

                            Norbert