The task for this project is to compare Excel Pivot Tables and Tableau on the analysis of a dataset regarding cities in the United States. Specifically, the following questions were developed:
-
Analyse the data and identify the state with the most cities in it, and that with the most airports.
-
Find counties with the highest population in them: are they the counties with the most cities in them as well? Compare the findings. For these counties, show also the distribution of the rankings.
-
Which are the fairest cities, i.e., cities where the median and average income are the closest, to live in? You can compute a “fairness score” as the difference between the average income and the median income. What can you say about the fairness score and the mean income, e.g., the richer the population of the city, the less fair it is? Describe also the relationship between the fairness score and the city’s population.
-
Geographically visualize the cities and their population on a map. If plotting all of the cities becomes too computationally expensive, you may consider to cut only up to a certain number, e.g., top 100 cities (by population) in the US.
-
Is there any correlation between (1) the population and the available land; (2) median income and average airport score and (3) the available water area with the average elevation of the airports in the city?
-
Perform analysis at the state level: which are the most populous states? How many counties and cities do they have? Visualize these findings on a map as well.
-
Can you observe any particular trends in the data? Are there any significant insights that you gained while analysing the dataset?
-
Compare the usage of Excel’s pivot tables with the features offered by Tableau. Which tool is more suitable and convenient for which types of tasks? Is there anything that was possible to do with Tableau that you couldn’t do with pivot tables and the other way round?