Comparing city spending with Tableau

In this post I create a Tableau dashboard that compares how US cities raise and spend money, using data from the Fiscally Standardized Cities (FiSCs) database.

I learned the basics of Tableau a few years ago, and have been impressed with its ability to produce interactive visualizations and share them online.

One feature I wanted to explore further was the ability for users to ‘drill down’ into the results presented in a dashboard. This avoids overwhelming the user with too much data up front, while still giving them the freedom to explore the data in more detail should they choose. It can also help the user to understand hierarchies present in the data, and how the various parts make up the whole. (Pivot tables can be used to similar effect in Microsoft Excel, but I wanted to apply this in a Tableau dashboard.)

As an example, I created a dashboard showing how major cities in the US raise and spend money. The animation below demonstrates the main features of the dashboard, the full version of which can be found here.

 
Notice how the user is first presented with overall revenue / expenditure per resident, and can then drill down to see the results at a finer level of detail. The chart to the right shows the city’s ranking for whichever service the user clicks on. The map allows the user compare revenue or expenditure per head between neighboring cities.

Overall I find Tableau to be great for producing interactive visualizations, and for performing exploratory data analysis using its click-and-point interface. But while the interface works great for standard visualizations, I’m often surprised at the need to use workarounds to achieve seemingly simple tasks (for example, in the dashboard above, creating a text box that returned the name of the service currently selected by the user). Tableau’s user-friendly interface lacks the conciseness and reproducibility of code, so a useful approach is to do as much work as possible in R first (e.g. cleaning and restructuring the data) and then using Tableau for the final visualization.

Tell me more about city spending!

I produced this dashboard using the Fiscally Standardized Cities (FiSCs) database, which enables a meaningful comparison of the 150 largest cities in the US. It does so by adding the revenue and expenditure of each city government to the relevant portion of the revenue and expenditure of overlapping counties, independent school districts and special districts (e.g. airports and hospital districts). Without this standardization, comparisons across city governments alone can be misleading.

The dashboard highlights some interesting facts about Dallas, where I live, such as:

• Schools account for the largest share of local expenditure, though Dallas is not unusual in this respect.
• Dallas spends a lot on airports on a per capita basis, presumably due to the Dallas-Fort Worth (DFW) airport. This also brings in revenue. However the questionable return on investment has led to some calls for the airport to be sold off.
  
• Hospitals are the third largest category of expenditure. This presumably reflects Parkland Hospital, a safety net hospital that provides care for uninsured patients, funded through a property tax on Dallas county residents. This has raised questions of fairness given that the hospital is used by low-income residents from surrounding counties, which boast lower property taxes and provide the bare minimum state requirements for indigent health care.
• Dallas spends a lot servicing its debt. Interest payments of $510 per resident are the 5th largest out of all the major cities, and is greater than its spending on police and fire services combined.