Laboratory Four. Using Tablebuilder to Access 2001 Census data

Professional Development Course in Mapping and GIS

Mapping and GIS Home

Introduction

This laboratory will introduce you to the use of Statistics New Zealand's Population Census database on the Internet. This database can be used to show the current demography of an area as well as changes over time. The Census holds information on a wide range of demographic themes; e.g. income, age, and gender. The Statistics New Zealand Internet site provides a table management tool and the option to export and use Census tables in Excel. The Excel tables that we produce can be saved in .dbf format suitable for importing into a GIS application.

In this laboratory you are required to work through the two sets of instructions provided.

• The first set of instructions lists the steps that you need to follow to produce an Excel table that can be exported from Tablebuilder. The table will be built from the Census data source known as Age Group and Sex, for the Census Night Population Count, 1991, 1996 and 2001. The table will be exported to Excel.
• The second set of instructions lists the Excel procedures required to create a new variable in the Table. We will create three crude dependency ratios for a group of Census Area Units in any territorial local authority. In the exemplar case study this analysis is carried out for the Tauranga District Council.

The Excel table should be saved in both .xls and .dbf format; the dbf files can be imported into desktop GIS applications that allow us to produce a map output subsequently.

1. What is Tablebuilder?

Tablebuilder is an initiative in web-based data delivery that allows users to access a wide variety of data from the 2001 Census of Population and Dwellings. It combines easy-to-use table management software with approximately 300 very large base tables of data created by Statistics New Zealand. The table management software enables you to select one of the base tables, choose specific variables and geography from within that table and then create your own table to these specifications. The results can then be printed directly or downloaded for further analysis in either Excel or csv format.

Tablebuilder provides detailed data at the Area Unit level. Meshblocks are the smallest spatial units of data recording and Area Units are aggregations of meshblocks. They are non-administrative areas in between meshblocks and territorial local authorities in size. Area units must either define or aggregate to define regional councils, territorial authorities, urban areas and statistical areas. Each Area Unit must be a single geographic entity with a unique name referring to a geographical feature. Area units normally contain a population of 3,000-5,000, though this can vary. Nationally, there are a total of 74 territorial authorities (15 cities and 59 districts). Area units of main or secondary urban areas generally coincide with suburbs or parts of suburbs. In rural areas a number of area units have only two or three meshblocks and a very low population count.

2. Internet data supply.

On your computer computers, open Internet Explorer and navigate to Statistics New Zealand's home page at http://www.stats.govt.nz/. Go to the Site map shown in the first line of links at the top of the page. Scroll down to Products and Services and click on Census 2001. Scroll to the 2001 Census - Output Products and Services. Click on the [more] icon to go to Table builder and select Age Group and Sex, for the Census Usually Resident Population Count, 1991, 1996 and 2001. Choose Total New Zealand Territorial Authority/Area Unit. When you make the TLA selection you will see a scrollable list of TLAs (Figure 1), among which will be listed your target for this exercise, Tauranga District.

[image to be inserted here]

Figure 1. Listing of TLAs with summary statistics

Click on the underlined name of the District, and the row item will expand to list all the Census Area Units within Tauranga District (Figure 2).

[image to be inserted here]

Figure 2. Listing of the Tauranga Census Area Units (CAUs)

We could expand the columns out to include each five-year age group for male and female classes for each of the Censuses, but this would be a large table. Instead, we will just use the age classes in this exercise. Open up these classes by clicking on the Age Group Total icon (not the descriptive text tile next to the words Age Group). This will produce the listing shown in Figure 3.

[image to be inserted here]

Figure 3. Listing of Age groups for Tauranga CAUs

Note that the blue box on the left of your screen shows that you are Viewing a table, and that there is an option to Download. Choose this option and, from the Select file format pull-down menu, select Microsoft Excel then Go. When the Excel file format displays, make sure you save the file in .xls format, and then open Excel and minimise the Internet Explorer screen.

Task One: making a Tablebuilder table. Produce an annotated table of the 1991, 1996 and 2001 total populations for all of the Census Area Units in the Territorial Local Authority of your choice. Write a few sentences on the most striking features of change in the table.

3. Analysing Population Census data in Excel

We will explore one data manipulation of the Excel data. What is the geographical distribution of the dependency index in Tauranga TLA in 2001? In this example youth dependency is defined as the number of the population under the age of 15 as a percentage of the number of people in the 15-65 age group. Age dependency is the number of people 65 and over as a percentage of the number of people 15-64. In Excel we edit the table by:

• deleting columns of information for 1991 and 1996,
• allowing just one line of title detail (for subsequent processing purposes),
• removing some rows of data that have no usable population data,
• summing the 0-14 and 65+ age groups into new (inserted) columns,
• summing the 15-64 age groups into a new column
• creating a dependency indices (percentage) from these sums, and
• saving the resulting table as a .dbf file.

Task Two: calculations in Excel. Open your Excel table (Tauranga2001.xls) and click on the column heading 'B' (it will be highlighted). Scroll to the right to column 'AK', and hold down the Shift key while pressing Enter. All columns containing data for the 1991 and 1996 Census will be highlighted. From the Edit menu, select Delete to remove this portion of the table.Using the same method, delete all rows of data from the top of the table other than the one holding the age groups. Remove any rows of data that do not have meaningful data (remembering that these will then not be mappable in any GIS exercise). In the Tauranga case these areas are Waikareao Estuary and Motuopae Island.

In cell T2, calculate the total population for the first CAU by entering =SUM(B2:S2). In the Tauranga case this figure will be 6075. Highlight this cell, and drag the 'handle' on the bottom right of the cell downwards over the remaining cells in the column to replicate this formula across other rows of data. In cell T1 enter the text 'Total'.

Sum the youth age groups in cell U2, the 'working' age groups in V2, and the elderly age groups in W2. Extend these summations across all rows in the table.

Make three columns that show the percentages that these three age groups represent in the total population. For example, in cell X2 enter the formula =(V2/T2)*100.

Print the output file and save the file as Tauranga.dbf. The dbf extension can be translated as database format.

4. Geographical analysis

In this case your analysis should be carried out using inspection of a table. Generally such analysis is aided by mapping the data and having a clear view of the spatial distribution of features. This is an easy task in ArcView, but the laboratory commentary is based simply on downloading the data, and carrying out Excel calculations.

Task Three. Write some interpretative comments on your table. In the Tauranga case note the high youth dependency in Matapihi, Bethlehem and Bellevue. The contrasts with the higher percent working age groups in Mt Maunganui North, Te Reti and Poike are apparent, and the percentages of senior citizens in Te Maunga, Otumoetai North and Tauranga Central are clear.

Bring the work from the three tasks into one document and attach it as a file to an email to lex@waikato.ac.nz

Mapping and GIS Home