<jupyter_start><jupyter_text>Pandas for panel data  1 Overview 2 Slicing and reshaping data<jupyter_code>import pandas as pd
#set_option（para，value）函数
pd.set_option('display.max_columns', 6)  # 显示6列

#格式化浮点数，显示两位小数
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.tail()
realwage.head()
realwage = realwage.pivot_table(values='value',
                                index='Time',
                                columns=['Country', 'Series', 'Pay period'])
realwage.head()
#to_datetime函数处理日期格式
realwage.index=pd.to_datetime(realwage.index)
type(realwage.index)
type(realwage.columns)
realwage.columns.names
#选取美国的数据
realwage['United States'].head()
#.stack() 内层的行转换为列 .unstack()  内层的列转换成行   对行列进行变化
realwage.stack().head()
#指定stack的层级
realwage.stack(level='Country').head()
realwage['2015'].stack(level=(1,2)).head()       # 第2和第3层转为行
#选择一年，一个特定的层级，transpose转置
realwage['2015'].stack(level=(1,2)).transpose().head()  # 第2、3层转为行、转置
#用2015汇率测算的，不同国家在不同年份每小时最低工资，xs.根据标签选取行或者列
realwage_f = realwage.xs(('Hourly', 'In 2015 constant prices at 2015 USD exchange rates'),
                         level=('Pay period', 'Series'), axis=1)
realwage_f.head()<jupyter_output><empty_output><jupyter_text>3 Merging dataframes and filling NaNs<jupyter_code>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=worlddata.rename(columns={'Country (en)':'Country'})
worlddata.head()
#merge worlddata和realwage_f这两个dataframe,pandas默认的是行合并
realwage_f.transpose().head()

#merge有四种形式，left join ,right join ,outer join,inner join，默认为inner join
merged = pd.merge(realwage_f.transpose(), worlddata,
                  how='left', left_index=True, right_on='Country')
merged.head()
#判断是否有缺失值 .isnull()
merged[merged['Continent'].isnull()]
#创建一个包含这些缺失国家和对应所在大陆的dictionary
missing_continents = {'Korea': 'Asia',
                      'Russian Federation': 'Europe',
                      'Slovak Republic': 'Europe'}

merged['Country'].map(missing_continents)
#.fillna()用来填补缺失值
merged['Continent'] = merged['Continent'].fillna(merged['Country'].map(missing_continents))

# Check for whether continents were correctly mapped

merged[merged['Country'] == 'Korea']
#将美洲国家汇总起来
replace = ['Central America', 'North America', 'South America']

for country in replace:
    merged['Continent'].replace(to_replace=country,
                                value='America',
                                inplace=True)
#.sort_index()
merged = merged.set_index(['Continent', 'Country']).sort_index()
merged.head()
merged.columns
#to_datetime()
merged.columns = pd.to_datetime(merged.columns)
merged.columns = merged.columns.rename('Time')
merged.columns
merged = merged.transpose()
merged.head()<jupyter_output><empty_output><jupyter_text>4 Grouping and summarizing data<jupyter_code>#Groupby分组函数：mean()
merged.mean().head(10)
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.mean(axis=1).head()
merged.mean(axis=1).plot()
plt.title('Average real minimum wage 2006 - 2016')
plt.ylabel('2015 USD')
plt.xlabel('Year')
plt.show()
merged.mean(level='Continent', axis=1).head()
merged.mean(level='Continent', axis=1).plot()
plt.title('Average real minimum wage')
plt.ylabel('2015 USD')
plt.xlabel('Year')
plt.show()
merged = merged.drop('Australia', level='Continent', axis=1)
merged.mean(level='Continent', axis=1).plot()
plt.title('Average real minimum wage')
plt.ylabel('2015 USD')
plt.xlabel('Year')
plt.show()
merged
merged.stack().head()
merged.stack().describe()
#group分组函数,生成一个groupby对象
grouped = merged.groupby(level='Continent', axis=1)
grouped
#.size()返回一个含有分组大小的Series
grouped.size()
import seaborn as sns

continents = grouped.groups.keys()
#.kdeplot用于核密度估计，shade=True控制阴影
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()
grouped<jupyter_output><empty_output><jupyter_text>Exercises<jupyter_code>employ = pd.read_csv('https://github.com/QuantEcon/QuantEcon.lectures.code/raw/master/pandas_panel/employ.csv')
employ = employ.pivot_table(values='Value',
                            index=['DATE'],
                            columns=['UNIT','AGE', 'SEX', 'INDIC_EM', 'GEO'])
employ.index = pd.to_datetime(employ.index) # ensure that dates are datetime format
employ.head()
employ.columns.names
#.unique()函数去除其中重复的元素，并按元素由小到大返回一个无重复元素的元组或列表
for name in employ.columns.names:
    print(name, employ.columns.get_level_values(name).unique())
#swaplevel调整索引级别，.sort_index()按行或者列排序
employ.columns = employ.columns.swaplevel(0,-1)
employ = employ.sort_index(axis=1)
#.tolist()矩阵转换成列表
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()
employ_f = employ.xs(('Percentage of total population', 'Active population'),
                     level=('UNIT', 'INDIC_EM'),
                     axis=1)
employ_f.head()
employ_f = employ_f.drop('Total', level='SEX', axis=1)
box = employ_f['2015'].unstack().reset_index()
#箱体图，showfliers:是否显示异常值，x="AGE"指定绘图数据
sns.boxplot(x="AGE", y=0, hue="SEX", data=box, palette=("husl"), showfliers=False)
plt.xlabel('')
plt.xticks(rotation=35)
plt.ylabel('Percentage of population (%)')
plt.title('Employment in Europe (2015)')
#bbox_to_anchor：表示legend的位置，前一个表示左右，后一个表示上下
plt.legend(bbox_to_anchor=(1,0.5))
plt.show()<jupyter_output><empty_output>