Data Assignment: Mortality rates and Life Expectancy: Bolivia, Zambia and Russia

The objective of this assignment is to analyze official World Health Organization data on mortality rates from start to finish using Excel. We will use raw data to construct a mortality table and compute age-specific life expectancy.

In the process, you will need to use Excel skills such as pivot tables and graph functions to manipulate the data.

Step 1: Data from Zambia

Open the uploaded datasets from Zambia. (The dataset were downloaded last year from the WHO website).

• Download the Excel table from the class website.
• Go to the Combined Data tab; from there, keep the data corresponding to the indicator lx – number of people left alive at age x (column B), and drop all other rows. Make sure to keep the column titles.
• Choose a sex (male or female); keep the columns for that sex corresponding to the years 2000, 2010, and 2015. Delete all other columns.
• Highlight all data.
• Go to the “Insert” Ribbon and click on Pivot table.
• The pivot table has 3 relevant “areas”. Rows, Columns, and Values. The row area should have the row values you see in the example above (age groups in our case). The column area will have the column titles. Note (from the example above) that our columns have 2 variables: the year, and the gender. The value area contains the data we are interested in, namely, the mortality data (AKA the “Numeric Value”)
• The pivot table also has a “PivotTable Builder”. The builder has a list of all the column variables in the data (“field name”). It also has 3 areas of interest: Column Labels, Row Labels, Values.
• Construct the table in wide form by dragging the field names to the appropriate areas within the builder.
• Click on the ? in the values area to change the data from Count to “Average”. You should now have something very similar to the table in the website.
• Make sure that the column titles are row titles are correctly organized.
• Now take the column corresponding to 2000, 2010 and 2015, and copy these columns in a new excel tab. We will work with these three columns from now on.
• We are now ready to construct mortality rates. The mortality rate is the percentage of the population of age group a who does not survive to age group a+1 (i.e. the following age group). From the data given, it can be calculated as
• Now create a line graph with mortality curves associated with the three years. You should have 3 lines, one for each year. Label the lines and axis clearly. Make sure that the age groups are clearly legible in the X axis. Format the lines in such a way that they can be identified when printed in black and white. Add an informative title. Use nice font—make the image pleasing to the eye!

Step 2: Pivot table

You are now left with a variable equal to the number of people alive for a number of age groups. Note also that the data is disaggregated by year (2000, 2010, 2015) and by sex (Male, Female).You will need to construct one line graph with the mortality rates for either sex in 2000, 2010 and 2015. For this, you will need to use a pivot table to reformat the data by column. (In jargon, the data is presented long and you need to reformat it to wide). In other words, consider the following example:

Long form Age group Year Sex Mortality <1 1990 M 100000 1-4 1990 M 98000 <1 1990 F 100000 1-4 1990 F 98000 <1 2000 M 100000 1-4 2000 M 98000 <1 2000 F 100000 1-4 2000 F 98000 <1 2000 Both 100000 1-4 2000 Both 98000

Wide form 1990 Age group Male Female <1 10000 10000 1-4 9800 9800

Step 3: Construct mortality rate table and graph

Mortality_rate@age a=[(#ppl age a)-(#ppl age a+1)]/( #ppl age a)

Create three columns with the mortality rates associated to the years 2000, 2010 and 2015 for Zambia. Drop mortality rates for age groups 85 and above. Before you calculate mortality rates, make sure that your data is in the right order!

Step 5: replicate graphs from Bolivia.

You can find the data for Burkina Faso on the WHO website:

http://apps.who.int/gho/data/node.imr.LIFE_0000000031?lang=en

Step 4: Replicate graphs for Russia.

Find the data for Russia. Follow all steps above.

Deliverables

Graphs:

Upload the three graphs on Canvas. You do not need to upload the tables.