The included Excel file lists HIV estimated prevalence of people ages from 15 to 49 in the world from 1979 to 2011. Use the dataset to complete following tasks:

1. Add one column as continent in the dataset and label each country/regionin the dataset to an appropriate continent such as Europe, Asia, Africa, North America, South America, Australia, or Antarctica. Explain how do you validate the correctness of your labelling. Output the updated dataset as a new CSV file. (1 point). (Note: You must write a Python program to complete the labelling, manully labelling wont get any credit).
2. Write a Python program to find the country/region in each continent thathas the highest average HIV estimated prevalence of people ages from 15 to 49 of from year 2000 to 2011. Find the country/region in each continent that has the lowest average HIV estimated prevalence of people ages from 15 to 49 of from year 2000 to 2011. Create a bar chart to show the highest average HIV estimated prevalence of people ages from 15 to 49 of from year 2000 to 2011 in each continent (1 point). Create a bar chart to show the lowest average HIV estimated prevalence of people ages from 15 to 49 of from year 2000 to 2011 in each continent (1 point). Create an overlaid bar chart to show the highest and lowest average HIV estimated prevalence of people ages from 15 to 49 of from year 2000 to 2011 in each continent (1 point). Select a country/region that is different from the average highest or lowest HIV estimated prevalence of people ages from 15 to 49 from year 2000 to 2011 from each continent, then create an overlaid line chart for the selected country/region, the average highest and lowest HIV estimated prevalence of people ages from 15 to 49 from year 2000 to 2011 for each continent (1 point).
3. Write a Python progrma to calculate the average HIV estimated prevalence of people ages from 15 to 49 for each year in the dataset for each continent (you only need simply add the estimate prevalence number of all countries/regions and divided by the number of the countries/regions in the continent). Based on the calculation, create a line chart for each continent to show the changes of the average HIV estimated prevalence from 1979 to 2011 (1 point). Create an overlaid line chart for all continents to show their changes of the average HIV estimated prevalence from1979 to 2011 (1 point).

4. Create two scatter plots to show the data (i.e. each country/region) inyear 1990 and year 2010, respectively. The vertical axis in the scatter plot is the HIV estimated prevalence, and the horizontal axis is the corresponding year average HIV estimated prevalence in each continent, which you calculated above. Using different color to show data from different continent (1 point). If you found any interesting result from the charts, explain it.
5. Write a report to explain how each question is implemented and its outputgraphs (2 point).

Estimated HIV Prevalence % - (Ages 15-49) The data from 1990 onwards you see in this graph is from UNAIDS, but Gapminder has provided extra estimates for some countries before 1990, and the compilation was documented and could be accessed from the link below.Due to visualization limits, we only display the countries with the 1st available estimate before 1988, and for countries that have no data or estimate available until 1988, we display it from 1990 onwards using the data from UNAIDS. Data for Brazil was the average of the high and low estimate from UNAIDS from 1990 onwards, and 1979 was the estimate from Gapminder Foundation. 2 3 4 5 6 Definition and explanations Indicator name Definition of indicator Unit of measurement Estimated HIV Prevalence % - (Ages 15-49) Estimated number of people living with HIV per 100 population of age group 15-49. 7 Data source Source organization(s) Link to source organization Complete reference Link to complete reference UNAIDS online database http://www.aidsinfoonline.org Documentation from Gapminder Foundation http://www.gapminder.org/gapminder-world/documentation/qd006 8 9 10 11 12 13 14 15 16 17 18 19 Specific information about this indicator Uploader Time of uploading 26.04.2013 4 46 values given as Data About Footnotes Settings v Estimated HIV Prevalence % - (Ages 15-49) The data from 1990 onwards you see in this graph is from UNAIDS, but Gapminder has provided extra estimates for some countries before 1990, and the compilation was documented and could be accessed from the link below.Due to visualization limits, we only display the countries with the 1st available estimate before 1988, and for countries that have no data or estimate available until 1988, we display it from 1990 onwards using the data from UNAIDS. Data for Brazil was the average of the high and low estimate from UNAIDS from 1990 onwards, and 1979 was the estimate from Gapminder Foundation. 2 3 4 5 6 Definition and explanations Indicator name Definition of indicator Unit of measurement Estimated HIV Prevalence % - (Ages 15-49) Estimated number of people living with HIV per 100 population of age group 15-49. 7 Data source Source organization(s) Link to source organization Complete reference Link to complete reference UNAIDS online database http://www.aidsinfoonline.org Documentation from Gapminder Foundation http://www.gapminder.org/gapminder-world/documentation/qd006 8 9 10 11 12 13 14 15 16 17 18 19 Specific information about this indicator Uploader Time of uploading 26.04.2013 4 46 values given as Data About Footnotes Settings v