<jupyter_start><jupyter_text>Analyze open data sets with pandas DataFramesIn this notebook, you'll learn how to use open data from the data sets on the Watson Studio home page in a Python notebook. You will load, clean, and explore the data with pandas DataFrames. Some familiarity with Python is recommended. This notebook runs on Python 2 and 3.The data sets for this notebook are from the World Development Indicators (WDI) data set. The WDI data set is a statistical benchmark that helps measure the progress of human development. The WDI data set draws on data from the World Bank and more than 30 partners. You'll look at seven key development variables, sorted by country and year. Learning goals1. Access data from the Watson Studio home page.1. Merge multiple data sets in pandas DataFrames.1. Structure data for analysis.1. Generate summary statistics.1. Create charts with custom formatting.1. Show relationships between all variables.  Table of contentsThe main parts of this notebook are:1. [Load data into DataFrames](load)    1. [Load the first data set](first)    1. [Load another data set](loadsecond)    1. [Join the DataFrames](joindataframes)    1. [Add more data](loadmore)    1. [Create a MultiIndex](multiindex)1. [Check the data](check)1. [Analyze the data](analysis)    1. [Create scatter plots with matplotlib](matplotlib)    1. [Create a scatter plot matrix](matrix)1. [Next steps](summary)  1. Load data into DataFramesTo load the data, you obtain access key links and put them into the code to create pandas DataFrames. You will load data into multiple DataFrames and then merge the DataFrames and create a MultiIndex to structure the data for analysis.  A. Load the first data setThe first data set that you'll load contains life expectancy data.To get your access key link and load the first data set:1. Find the data set and get the access key link:    1. On the Watson Studio home page, search for "life expectancy."    1. Click the card with the title [Life expectancy at birth by country in total years](https://apsportal.ibm.com/exchange/public/entry/view/f15be429051727172e0d0c226e2ce895).    1. Click the link button.    1. Hover above the link button next to the access key to display the link.    1. Double-click the link to select it, copy the link, and click **Close**.    1. Insert the access key link and run the cell to load the data:    1. In the cell below, replace the **LINK-TO-DATA** string in the `read_csv()` method with the link.    1. Run the following cell to import the pandas and numpy libraries, rename and load three columns of the data into the DataFrame, and show the first five rows of data.<jupyter_code>import numpy as np
import pandas as pd
# life expectancy at birth in years
life = pd.read_csv("UNdata_life_expectancy_at_birth_total_years.csv",usecols=['Country or Area','Year','Value'])
life.columns = ['country','year','life']
life[0:5]<jupyter_output><empty_output><jupyter_text>B. Load another data setThe life expectancy figures are more meaningful if you combine them with other data sets. Get an access key link for this data set: [Total population by country](https://apsportal.ibm.com/exchange/public/entry/view/889ca053a19986a4445839358a91963e). Paste your link in the following cell, which loads three columns of the data and prints the number of countries in each data set:<jupyter_code># population
population = pd.read_csv("UNdata_population_total.csv",usecols=['Country or Area', 'Year','Value'])
population.columns = ['country', 'year','population']

print("Nr of countries in life:", np.size(np.unique(life['country'])))
print("Nr of countries in population:", np.size(np.unique(population['country'])))<jupyter_output>Nr of countries in life: 246
Nr of countries in population: 245<jupyter_text>C. Join DataFramesAs you can see, the two data sets don't fit together perfectly: one contains more countries than the other. When you join two DataFrames, it's common to introduce nulls or NaNs (a value that's not a number when a number is expected) into the resulting DataFrame. Use the pandas `merge` function to make the data fit together. In the following cell, the `how='outer'` option specifies to keep the data from the `life` and `population` columns. The `on=['country','year']` option specifies which columns to merge on. To view the options for the `merge` function, see Merge, join, and concatentate.<jupyter_code>df = pd.merge(life, population, how='outer', on=['country','year'])
df[400:405]<jupyter_output><empty_output><jupyter_text>D. Add more data Add more data about development to the DataFrame. For each data set, you'll include the country and year columns, plus a column that contains the key development variable for that data set. For the employment data set, you'll derive a column from three original columns.For each of these data sets, get an access key link for the data and paste the link into the appropriate section of the following cell:* [Population below national poverty line, total, percentage](https://apsportal.ibm.com/exchange/public/entry/view/3a42de810e682d5a83e76fda9fea2bbe)* [Primary school completion rate % of relevant age group by country](https://apsportal.ibm.com/exchange/public/entry/view/97fc6c420265012ae9b5e9439b9ee293)* [Total employment, by economic activity (Thousands)](https://apsportal.ibm.com/exchange/public/entry/view/21c789ae4cfadf52f1bb14b5d4a0b99c)* [Births attended by skilled health staff (% of total) by country](https://apsportal.ibm.com/exchange/public/entry/view/87160cc8374805cb3b07dff4b4f9223e)* [Measles immunization % children 12-23 months by country](https://apsportal.ibm.com/exchange/public/entry/view/752e36ea889eca360e989baa785c2a5e)<jupyter_code># Population below national poverty line, total, percentage
poverty = pd.read_csv("UNdata_Export_20151013_112943678-poverty_clean.csv",usecols=['Country or Area', 'Year','Value'])
poverty.columns = ['country', 'year','poverty']
df = pd.merge(df, poverty, how='outer', on=['country','year'])

# Primary school completion rate % of relevant age group by country
school = pd.read_csv("UNdata_Primary_completion_rate_total_%_relevant_age_group.csv",usecols=['Country or Area', 'Year','Value'])
school.columns = ['country', 'year','school']
df = pd.merge(df, school, how='outer', on=['country','year'])

# Total employment, by economic activity (Thousands)
employmentin = pd.read_csv("Total_employment_by_economic_activity.csv",usecols=['Country or Area', 'Year','Value','Sex','Subclassification'])
employment = employmentin.loc[(employmentin.Sex=='Total men and women')&
                              (employmentin.Subclassification=='Total.')]
employment = employment.drop('Sex', 1)
employment = employment.drop('Subclassification', 1)
employment.columns = ['country', 'year','employment']
df = pd.merge(df, employment, how='outer', on=['country','year'])

# Births attended by skilled health staff (% of total) by country
births = pd.read_csv("Births_attended_by_skilled_health_staff_%_of_total.csv",usecols=['Country or Area', 'Year','Value'])
births.columns = ['country', 'year','births']
df = pd.merge(df, births, how='outer', on=['country','year'])

# Measles immunization % children 12-23 months by country
measles = pd.read_csv("UNdata_Immunization_measles_%_children_12to23_months.csv",usecols=['Country or Area', 'Year','Value'])
measles.columns = ['country', 'year','measles']
df = pd.merge(df, measles, how='outer', on=['country','year'])

df[0:50]<jupyter_output><empty_output><jupyter_text>The resulting table looks strange because the first 40 rows contain incorrect values, such as numbers in the `country` column and text in the `year` column. Run the following cell to delete the first 40 rows from the DataFrame:<jupyter_code>df=df.drop(df.index[0:40])

df[0:10]<jupyter_output><empty_output><jupyter_text>E. Create a MultiIndexYou structure the data for analysis by creating a pandas MultiIndex. A MultiIndex is a hierarchical index that groups the data so that you can easily summarize it.For example, by grouping by country and then by year, you can plot the average value per country for all years instead of plotting a value for every country for every year.Run this cell to create a MultiIndex on the country and the year:<jupyter_code>df2 = df.set_index(['country','year'])

df2[0:10]<jupyter_output><empty_output><jupyter_text>You can see that for the 1980s, the only data for Afghanistan is about measles immunizations. If you are curious about other variables, you can add more WDI data sets from the Watson Studio home page to this DataFrame. Not all WDI data is properly formatted. You might need to clean the data before you add it. You can reuse the code samples that you just ran. Make sure that you keep checking the results by running commands like `df2[0:10]` on your tables when you load or change them.   2. Check the data You can check the data with the `describe()` method, which calculates basic summary statistics for each of the columns in the DataFrame (except the country and year columns). Run the following cell to show the number of values (count), the mean, the standard deviation (std), the minimum value, three percentiles, and the maximum value for the columns:<jupyter_code>df2.describe()<jupyter_output><empty_output><jupyter_text>3. Analyze the dataNow that you have enough sample data to work with, you can find the correlation between different variables. You'll create scatter plots that each compare two variables.   A. Create scatter plots with matplotlibRun the following cell to create two scatter plots that relate the values of population and amount of schooling to life expectancy. Use `matplotlib` library commands to create charts with custom font, figure size, and colors.<jupyter_code>import matplotlib.pyplot as plt
%matplotlib inline

plt.rcParams['font.size']=11
plt.rcParams['figure.figsize']=[8.0, 3.5]
fig, axes=plt.subplots(nrows=1, ncols=2)
df2.plot(kind='scatter', x='life', y='population', ax=axes[0], color='Blue');
df2.plot(kind='scatter', x='life', y='school', ax=axes[1], color='Red');
plt.tight_layout()<jupyter_output><empty_output><jupyter_text>The chart on the left shows that increased life expectancy leads to higher population. Each dot represents a value for one country for one year.The chart on the right shows that the life expectancy increases with the percentage of school completion. Each dot represents the average value for one country for all years that have data. Notice that the percentage goes up to 200% instead of 100%. This might indicate a problem with the data. You can remove the outliers and keep only the values less than 100% with a command like: `df2[df2.school>100]=float('NaN')`. Even better, you can check where these values in the original data came from. Otherwise, a range like this might indicate an error in your code. In this case, however, the values are correct because of the way the school completion data was calculated: read the description of [Primary school completion rate % of relevant age group by country](https://apsportal.ibm.com/exchange/public/entry/view/97fc6c420265012ae9b5e9439b9ee293). Percentages higher than 100 indicate that older people are finishing primary school.  B. Create a scatter plot matrixYou can create charts that show the relationships between all variables with a scatter plot matrix. Each dot represents the average value for one country for all years that have data. Run the following cell to create scatter plots and histograms from the DataFrame with the `scatter_matrix()` method. Because the data does not cover the same years, group the data by country (with some loss of data). Many variables are percentages, so convert the employment figures to percentages as well. Drop the `population` column, because you don't need it anymore.<jupyter_code>from pandas.tools.plotting import scatter_matrix

# group by country
grouped = df2.groupby(level=0)
dfgroup = grouped.mean()

# employment in % of total population
dfgroup['employment']=(dfgroup['employment']*1000.)/dfgroup['population']*100
dfgroup=dfgroup.drop('population',1)

scatter_matrix(dfgroup,figsize=(12, 12), diagonal='kde');<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0893428501107556, 'fraction_numerical': 0.025678890803183196, 'mean_word_length': 5.567887931034483, 'pattern_counts': {'":': 0, '<': 36, '<?xml version=': 0, '>': 37, 'https://': 8, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '211152', 'license': 'MIT', 'n_tokens_mistral': 3577, 'n_tokens_neox': 3289, 'n_words': 1479, 'path': 'racketframes/validation/open_data_sets_dataframes.ipynb', 'repo_name': 'jbclements/RacketFrames', 'size': '12189'}