Pandas and Panel Data Application!

import pandas as pd
pd.set_option('display.max_columns', 6)
# Reduce decimal points to 2

pd.options.display.float_format = '{:,.2f}'.format

realwage = pd.read_csv(
    'https://github.com/QuantEcon/QuantEcon.lectures.code/raw/master/pandas_panel/realwage.csv'
)

realwage.head()  # Show first 5 rows

realwage = realwage.pivot_table(
    values='value', index='Time', columns=['Country', 'Series', 'Pay period'])
realwage.head()

realwage.index = pd.to_datetime(realwage.index)
realwage.index

realwage.stack(level='Country')

realwage['2015'].stack(level='Country')

realwage_c = realwage['2015'].stack(level=[1, 2]).transpose()  # no time index
realwage_c

realwage_f = realwage.xs(
    ('Hourly', 'In 2015 constant prices at 2015 USD exchange rates'),
    level=('Pay period', 'Series'),
    axis=1)
realwage.head()

realwage_f.head()

realwage.xs(('Annual', 'Brazil'), level=[2, 0], axis=1)  # () not []

realwage.xs(('Annual', 'Brazil'), level=[2, 0], axis=1)  # () not []

realwage_c.xs('Annual', level='Pay period', axis=1)

worlddata = pd.read_csv(
    'https://github.com/QuantEcon/QuantEcon.lectures.code/raw/master/pandas_panel/countries.csv',
    sep=';')
worlddata.head()

worlddata = worlddata[['Country (en)', 'Continent']]

worlddata.head(2)

worlddata = worlddata.rename(
    columns={'Country (en)': 'Country',
             'Continent': 'Continent'})
worlddata

realwage_f.head()

realwage_f.transpose().head()

merged = pd.merge(
    realwage_f.transpose(),
    worlddata,
    how='left',
    left_index=True,
    right_on='Country')
merged.head()

merged[merged.Continent.isnull()]

missing_continents = {
    'Korea': 'Asia',
    'Russian Federation': 'Europe',
    'Slovak Republic': 'Europe'
}

merged.Continent = merged['Continent'].fillna(
    merged.Country.map(missing_continents))

merged

merged[merged.Country == "Korea"]

list_country = ['Central America', 'North America', 'South America']
for country in list_country:
    merged.Continent.replace(
        to_replace=country, value='Americas', inplace=True)

merged.head()

merged = merged.set_index(['Continent', 'Country']).sort_index()
merged.head()

merged.columns = pd.to_datetime(merged.columns)
merged.columns = merged.columns.rename('Time')
merged

merged = merged.transpose()
merged.head()

import matplotlib.pyplot as plt
import matplotlib
matplotlib.style.use('seaborn')
%matplotlib inline

merged.mean().sort_values(ascending=False).plot(
    kind='bar', title="Average real minimum wage 2006 - 2016")

# Set country labels
country_labels = merged.mean().sort_values(
    ascending=False).index.get_level_values('Country').tolist()
plt.xticks(range(0, len(country_labels)), country_labels)
plt.xlabel('Country')

plt.show()

merged.columns.get_level_values('Continent').tolist()

merged.mean(axis=1).plot()

merged.head()

merged.mean(level='Country', axis=1)

merged.mean(level='Continent', axis=1).plot()

merged.drop('Americas', level=0, axis=1)

merged.stack().describe()

grouped = merged.groupby(level='Continent', axis=1)
grouped

grouped.size()

grouped.groups.keys()

grouped.get_group('Asia')['2015'].unstack()

import seaborn as sns

continents = grouped.groups.keys()

for continent in continents:
    sns.kdeplot(grouped.get_group(continent)[
                '2015'].unstack(), label=continent, shade=True)

plt.title('Real minimum wages in 2015')
plt.xlabel('US dollars')
plt.show()

employ = pd.read_csv("https://lectures.quantecon.org/_downloads/employ.csv")

employ.set_index(pd.to_datetime(employ.DATE), inplace=True)

employ.drop(['Unnamed: 0', 'DATE'], axis=1, inplace=True)

employ.dropna(how='all', inplace=True)
employ.head()

employ = employ.pivot_table(index='DATE', columns=[
                            'AGE', 'GEO', 'SEX', 'UNIT'], values='Value')
employ

employ.xs('Males', level=2, axis=1)

for col in employ.columns.names:
    print(col, employ.columns.get_level_values(col).unique())

employ.columns = employ.columns.swaplevel(0, 1)
employ = employ.sort_index(axis=1)
employ

geo_list = employ.columns.get_level_values('GEO').unique().tolist()
countries = [x for x in geo_list if not x.startswith('Euro')]
employ = employ[countries]
employ.columns.get_level_values('GEO').unique()

geog = employ.columns.get_level_values('GEO').unique().tolist()
geog2 = [i for i in geog if not i.startswith('Euro')]  # starts

employ = employ[geog2]

employ_f = employ.xs(('Percentage of total population'),
                     level=('UNIT'),
                     axis=1)
employ_f.head()

employ_f = employ_f.drop('Total', level='SEX', axis=1)
employ_f

box = employ_f['2015'].unstack().reset_index()
box

sns.boxplot(
    x="AGE", y=0, hue="SEX", data=box, palette=("husl"), showfliers=False)