<jupyter_start><jupyter_text>Capstone ProjectTo tie most of what we have learned together in this tutorial, let's analyse the *world-happiness* dataset.Below are the tasks that need to be done.  Loading dataload the data in `data/world-happiness/{year}.csv` for each year to each a column 'data_year' to keep track of the source year<jupyter_code># insert solution here
dfs = {}
for year in (2015, 2016, 2017):
    i_df = pd.read_csv(f'../data/world-happiness/{year}.csv')
    i_df['data_year'] = year
    print(i_df.columns)
    dfs[year] = i_df<jupyter_output>Index(['Country', 'Region', 'Happiness Rank', 'Happiness Score',
       'Standard Error', 'Economy (GDP per Capita)', 'Family',
       'Health (Life Expectancy)', 'Freedom', 'Trust (Government Corruption)',
       'Generosity', 'Dystopia Residual', 'data_year'],
      dtype='object')
Index(['Country', 'Region', 'Happiness Rank', 'Happiness Score',
       'Lower Confidence Interval', 'Upper Confidence Interval',
       'Economy (GDP per Capita)', 'Family', 'Health (Life Expectancy)',
       'Freedom', 'Trust (Government Corruption)', 'Generosity',
       'Dystopia Residual', 'data_year'],
      dtype='object')
Index(['Country', 'Happiness.Rank', 'Happiness.Score', 'Whisker.high',
       'Whisker.low', 'Economy..GDP.per.Capita.', 'Family',
       'Health..Life.Expectancy.', 'Freedom', 'Generosity',
       'Trust..Government.Corruption.', 'Dystopia.Residual', 'data_year'],
      dtype='object')<jupyter_text>Correcting column namescorrect the column names in the 2017 data so to be consistent with the previous years<jupyter_code># insert solution here
dfs[2017].rename(columns={
    'Economy..GDP.per.Capita.': 'Economy (GDP per Capita)', 
    'Health..Life.Expectancy.': 'Health (Life Expectancy)', 
    'Trust..Government.Corruption.': 'Trust (Government Corruption)'
}, inplace=True)
dfs[2017].columns = dfs[2017].columns.str.replace('.', ' ')
#checking
print(dfs[2015].columns)
print(dfs[2017].columns)<jupyter_output>Index(['Country', 'Region', 'Happiness Rank', 'Happiness Score',
       'Standard Error', 'Economy (GDP per Capita)', 'Family',
       'Health (Life Expectancy)', 'Freedom', 'Trust (Government Corruption)',
       'Generosity', 'Dystopia Residual', 'data_year'],
      dtype='object')
Index(['Country', 'Happiness Rank', 'Happiness Score', 'Whisker high',
       'Whisker low', 'Economy (GDP per Capita)', 'Family',
       'Health (Life Expectancy)', 'Freedom', 'Generosity',
       'Trust (Government Corruption)', 'Dystopia Residual', 'data_year'],
      dtype='object')<jupyter_text>Standardize Country namescorrect the names in all DataFrames to common ones.**HINT**: you can use the following correction dictionary or create your own```pythoncorrecting_country_names = {    'Taiwan': 'Taiwan Province of China',    'Hong Kong': 'Hong Kong S.A.R., China',}```<jupyter_code># insert solution here
# this is how 'correcting_country_names' was created
def compare_series(s1, s2, s2_name='s2'):
    set1, set2 = set(s1), set(s2)
    missing_in_s1 = sorted(set2 - set1)
    missing_in_s2 = sorted(set1 - set2)
    print(f'extra_in_{s2_name}   : {missing_in_s1}\nmissing_in_{s2_name} : {missing_in_s2}')
    
compare_series(s1=dfs[2017]['Country'], s2=dfs[2015]['Country'], s2_name='2015')
compare_series(s1=dfs[2017]['Country'], s2=dfs[2016]['Country'], s2_name='2016')
correcting_country_names = {
    'Taiwan': 'Taiwan Province of China',
    'Hong Kong': 'Hong Kong S.A.R., China',
}

for k,v in correcting_country_names.items():
    dfs[2015]['Country'].replace(k,v, inplace=True)
    dfs[2016]['Country'].replace(k,v, inplace=True)
    dfs[2017]['Country'].replace(k,v, inplace=True)
    
compare_series(s1=dfs[2017]['Country'], s2=dfs[2015]['Country'], s2_name='2015')
compare_series(s1=dfs[2017]['Country'], s2=dfs[2016]['Country'], s2_name='2016')<jupyter_output>extra_in_2015   : ['Comoros', 'Djibouti', 'Laos', 'Oman', 'Somaliland region', 'Suriname', 'Swaziland']
missing_in_2015 : ['Belize', 'Namibia', 'Somalia', 'South Sudan']
extra_in_2016   : ['Comoros', 'Laos', 'Puerto Rico', 'Somaliland Region', 'Suriname']
missing_in_2016 : ['Central African Republic', 'Lesotho', 'Mozambique']<jupyter_text>Remove Inconsistent Countries remove the rows of the countries that are not in all datasets<jupyter_code>print('starting with n_rows: ', [df.shape[0] for df in dfs.values()])

list_of_unique_country_names = [set(i['Country']) for i in dfs.values()]
common_country_names = set.intersection(*list_of_unique_country_names)

trimmed_dfs = {year: df[df['Country'].isin(common_country_names)].copy() for year, df in dfs.items()}

print('trimmed to n_rows: ', [df.shape[0] for df in trimmed_dfs.values()])<jupyter_output>starting with n_rows:  [158, 157, 155]
trimmed to n_rows:  [148, 148, 148]<jupyter_text>Add Region to 2017 dataadd Region column to the 2017 data by using the same mapping of the previous years<jupyter_code>mapping_2015 = {k:v for k,v in trimmed_dfs[2015].apply(lambda row: (row['Country'],row['Region']), axis=1)}
mapping_2016 = {k:v for k,v in trimmed_dfs[2016].apply(lambda row: (row['Country'],row['Region']), axis=1)}

mapping_2015 == mapping_2016  # checking they match
trimmed_dfs[2017]['Region'] = trimmed_dfs[2017]['Country'].apply(mapping_2015.get)
trimmed_dfs[2017].head(3)<jupyter_output><empty_output><jupyter_text>Remove Inconsistent Columns remove the columns that are not in all datasets<jupyter_code>list_of_unique_columns = [set(df.columns) for df in trimmed_dfs.values()]
common_columns = sorted(set.intersection(*list_of_unique_columns))

common_columns
trimmed_dfs = {year: df[common_columns].copy() for (year,df) in trimmed_dfs.items()}

# check columns match
for k,v in trimmed_dfs.items():
    print(k, sorted(v.columns) == common_columns)<jupyter_output>2015 True
2016 True
2017 True<jupyter_text>Combine All DataFramesnow that all three dataframes have been standardized, we can combine them (vertically)<jupyter_code>df = pd.concat(trimmed_dfs.values(), axis='index')
df.head()<jupyter_output><empty_output><jupyter_text>Analysing the dataUse the cleaned dataset to answer the following questions/tasks.Feel free to explore more and answer your own questions.  Which country is consistely more Generous? which least Generous?<jupyter_code>display('most:', df.sort_values('Generosity').tail(3))  # Myanmar
display('least:', df.sort_values('Generosity').head(3))  # Greece<jupyter_output><empty_output><jupyter_text>Which country has seen the biggest drop in Happiness Score within 1 year? which the biggest increase?<jupyter_code>pivotted = df.pivot_table(index='Country', columns='data_year', values='Happiness Score')
display(pivotted.head())

happiness_diffs = pivotted.diff(axis=1).stack()
display(happiness_diffs.head())

print('\n'+'='*80)
print('biggest drop:', happiness_diffs.idxmin(), ': ', happiness_diffs.min())
print('biggest increase:', happiness_diffs.idxmax(), ': ', happiness_diffs.max())<jupyter_output><empty_output><jupyter_text>Which Region, on average, has seen the biggest drop in Happiness Score within 1 year? which the biggest increase?<jupyter_code>pivotted = df.pivot_table(index='Region', columns='data_year', values='Happiness Score', aggfunc='mean')
display(pivotted.head())

happiness_diffs = pivotted.diff(axis=1).stack()
display(happiness_diffs.head())

print('\n'+'='*80)
print('biggest drop:', happiness_diffs.idxmin(), ': ', happiness_diffs.min())
print('biggest increase:', happiness_diffs.idxmax(), ': ', happiness_diffs.max())<jupyter_output><empty_output><jupyter_text>Which variables are most/least correlated with *Happiness Score*? make a scatterplot of them against *Happiness Score*.NOTE: remove *Happiness Rank*<jupyter_code>corr = df.corr()['Happiness Score'].drop(['Happiness Score','Happiness Rank'])

fig, axes = plt.subplots(1,2)
fig.set_size_inches(25,4)
df.plot.scatter(corr.idxmax(), 'Happiness Score', grid=True, ax=axes[0])
df.plot.scatter(corr.idxmin(), 'Happiness Score', grid=True, ax=axes[1])<jupyter_output><empty_output><jupyter_text>Taking the data average, is it clear if we getting happier or sadder? Which was the saddest year?<jupyter_code>yearly_mean = df.groupby('data_year')['Happiness Score'].mean()
yearly_mean.plot()

# not clear
# 2016 saddest<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15336387750180855, 'fraction_numerical': 0.029659995177236557, 'mean_word_length': 6.466246624662467, 'pattern_counts': {'":': 0, '<': 44, '<?xml version=': 0, '>': 44, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '161895', 'license': 'MIT', 'n_tokens_mistral': 3044, 'n_tokens_neox': 2806, 'n_words': 661, 'path': 'solutions/10_capstone_project_solutions.ipynb', 'repo_name': 'gabrielecalvo/pandas_tutorial', 'size': '8294'}