In this recipe, we will prepare the data set that we used in the Creating a population pyramid recipe of Chapter 2, Advanced Charts. The original file, which is a comma separated value (CSV) file, looks like the following:
To follow this recipe, download the file from BCStats using the following URL:
http://www.bcstats.gov.bc.ca/StatisticsBySubject/Demography/PopulationProjections.aspx.
When you download, make the following selections and click on Generate Output:
- Select British Columbia for Region
- Select all the years
- Select totals
- Select 5-Year Age Groups
Beside the result pane, click on the CSV icon at the top-right corner of the result pane to download the .csv file. Save the file as Population_Projections.csv.
Here are the steps to prepare the .csv file:
- Click on New Data Source icon and connect to the text file in this recipe:
- Select all the age groups that are presented as individual columns.
- While all the age group columns are selected, right-click on one of the selected fields and choose Pivot:
- Right-click on the newly created Pivot Field Names field and choose Rename. Rename this field Age Group.
- Right-click on the newly created Pivot Field Values field and choose Rename. Rename this field Population:
- Under Filters, click on Add.
- In the Edit Data Source Filters window, click on Add.
- In the Age Group filter window, select the Wildcard tab.
- Type
Totalunder Match value and check the Exclude checkbox:
- Click OK when done.
- Add a new sheet and create your visualization using this data set.
Tableau works best with data sets that are tall and narrow instead of short and wide. In the file that we are using in this recipe, the measure field—population—is split by age group. Each population value for an age group is provided as a column, so we end up with multiple measures:
This format is hard to work with, because all these measures are supposed to be a single measure. If we had a single measure for population values, and have another dimension for age group, the analysis will be more flexible. We can slice and dice population by age group if we need to.
Tableau provides a way for us to shape this file by pivoting the values, using the original measure names as a dimension, and collecting all the population values into a single column. Although you may also be able to pivot at the data source level, it is great to have this capability within Tableau.
Please refer to the Creating a population pyramid recipe in Chapter 2, Advanced Charts