<jupyter_start><jupyter_text>* [PANDAS: 3. Pivot Table](1)* [PANDAS: 4. Group By](2)<jupyter_code>from IPython.display import IFrame<jupyter_output><empty_output><jupyter_text>PANDAS: 3. Pivot Table<jupyter_code>IFrame(src='https://www.youtube.com/embed/ODFpGo7UomA', width=640, height=400)
import pandas as pd
!head -12 data/env_wastrt.tsv
df = pd.read_table('data/env_wastrt.tsv',header=0,sep=',|\t',engine='python',
                  names=['unit','hazard','operation','waste','country',2016,2014,2012,
                        2010,2008,2006,2004])
df.loc[df.waste=='W074','waste']='Plastic'
df.loc[df.waste=='W071','waste']='Glass'
df.loc[df.waste=='W06_07A','waste']='Recyclable'
df.loc[df.waste=='W12-13','waste']='Mineral'
df.loc[df.waste=='W10','waste']='Mixed ordinary wastes'
df.loc[df.waste=='W073','waste']='Rubber'
df.loc[df.waste=='W072','waste']='Paper and cardboard'
df.loc[df.waste=='W081','waste']='Vehicles'
df.loc[df.waste=='W01-05','waste']='Chemical&Medical'
df.loc[df.waste=='W075','waste']='Wood'
df.loc[df.waste=='W076','waste']='Textile'
df.loc[df.waste=='W063','waste']='Metal'
df.loc[df.operation=='DSP_L','operation']='Landfill'
df.loc[df.operation=='DSP_I','operation']='Incineration'
df.loc[df.operation=='RCV_R','operation']='Recycling'
df.loc[df.operation=='TRT','operation']='Waste treatment'
df.loc[df.operation=='DSP_L_OTH','operation']='Landfill&Other'
df.loc[df.operation=='DSP_OTH','operation']='Other'
df.loc[df.operation=='RCV_E','operation']='Energy recovery'
df.loc[df.operation=='RCV_R_B','operation']='Recycling&Backfilling'
df.loc[df.operation=='RCV_B','operation']='Backfilling'
df.iloc[:,5:] = df.iloc[:,5:].applymap(lambda x: x.rstrip('bcdefinprsuz')) 
df.iloc[:,5:] = df.iloc[:,5:].apply(pd.to_numeric,errors='coerce')
df.head()
df[(df.unit=='KG_HAB')&(df.waste=='TOTAL')&(df.hazard=='NHAZ')].head()
df.to_csv('data/wastrt.csv')<jupyter_output><empty_output><jupyter_text>Pivot Table<jupyter_code>(df[(df.unit=='KG_HAB')&(df.waste=='TOTAL')&(df.hazard=='NHAZ')]
  .pivot_table(index='country',columns='operation',values=2014,aggfunc='first')
  ).loc[['SE','IT','DK','EL']]
(df[(df.unit=='KG_HAB')&(df.waste=='Mineral')&(df.hazard=='NHAZ')]
  .pivot_table(index='country',columns='operation',values=2014,aggfunc='first')
  ).loc[['SE','IT','DK','EL']]
(df[(df.unit=='KG_HAB')&(df.waste=='Plastic')&(df.hazard=='NHAZ')]
  .pivot_table(index='country',columns='operation',values=2014,aggfunc='first')
  ).loc[['SE','IT','DK','EL']]
(df[(df.unit=='KG_HAB')&(df.waste=='Rubber')&(df.hazard=='NHAZ')]
  .pivot_table(index='country',columns='operation',values=2014,aggfunc='first')
  ).loc[['SE','IT','DK','EL']]
(df[(df.unit=='KG_HAB')&((df.waste=='Plastic')|(df.waste=='Rubber'))&(df.hazard=='NHAZ')]
  .pivot_table(index='country',columns='operation',values=2014,aggfunc='sum')
  ).loc[['SE','IT','DK','EL']]<jupyter_output><empty_output><jupyter_text>PANDAS: 4. Group By<jupyter_code>IFrame(src='https://www.youtube.com/embed/oh8UijClQoE', width=640, height=400)<jupyter_output><empty_output><jupyter_text>MultiIndex Creation (see previous lecture)<jupyter_code>df.set_index(keys=['unit','hazard','waste','operation','country'],inplace=True)
df.head()
df.xs(['KG_HAB','NHAZ','Plastic'],level=[0,1,2]).head()<jupyter_output><empty_output><jupyter_text>Unstack<jupyter_code>df.xs(['KG_HAB','NHAZ','Plastic'],level=[0,1,2]).unstack(0).head()
df.xs(['KG_HAB','NHAZ','Plastic'],level=[0,1,2]).unstack(0).loc[['SE','IT','DK','EL'],2014]
( df.xs(['KG_HAB','NHAZ','Plastic'],level=[0,1,2])
 .loc[(['Energy recovery','Landfill','Recycling','Waste treatment']
       ,['SE','IT','DK','EL']),(2014,2010)]
).unstack(0)<jupyter_output><empty_output><jupyter_text>GroupBy<jupyter_code>for name, group in df.xs(['T','NHAZ','Plastic'],level=[0,1,2])[2014].groupby('country'):
    print(name,type(group),group)
( df.xs(['T','NHAZ','Plastic'],level=[0,1,2]).groupby('country')
 .transform(lambda x : x / x[-1] * 100) )
import numpy as np
( df.xs(['T','NHAZ','Plastic'],level=[0,1,2]).groupby('country')
 .transform(lambda x : x / x[-1] * 100 if x[-1]>0 else x*np.nan) 
 .loc[(['Energy recovery','Landfill','Recycling'],['SE','IT','DK','EL','EU28']),
     ([2014,2010])] ).unstack(0)<jupyter_output><empty_output><jupyter_text>Recalculating the Aggregate EU28https://ec.europa.eu/eurostat/statistics-explained/index.php/Glossary:Country_codes<jupyter_code>(df.xs(['T','NHAZ','Plastic'],level=[0,1,2])
 .loc[(['Energy recovery','Landfill','Recycling','Waste treatment'],['EU28']),
     ([2014,2010])]).sort_index().T
(df.xs(['T','NHAZ','Plastic'],level=[0,1,2])
 .drop(labels=['EU28','IS','LI','NO','CH','ME','MK','AL','RS','TR','XK','HA'],level=1)
 .groupby('operation').sum()
 .loc[['Energy recovery','Landfill','Recycling','Waste treatment'],[2014,2010]]).T
(df.xs(['T','NHAZ','Plastic'],level=[0,1,2])
 .drop(labels=['EU28','IS','LI','NO','CH','ME','MK','AL','RS','TR','XK','HA'],level=1)
 .groupby('operation')
 .agg( lambda x : x.mean() if x.count()>0 else np.nan )
 .loc[['Energy recovery','Landfill','Recycling','Waste treatment'],[2014,2010]]).T
f_mean = lambda x : x.mean() if x.count()>0 else np.nan
f_mean.__name__ = 'mean'
f_std = lambda x : np.std(x) if x.count()>0 else np.nan
f_std.__name__ = 'standard deviation'

(df.xs(['T','NHAZ','Plastic'],level=[0,1,2])
 .drop(labels=['EU28','IS','LI','NO','CH','ME','MK','AL','RS','TR','XK','HA'],level=1)
 .groupby('operation')
 .agg( { 2014 : [f_mean,f_std], 2010 : [f_mean,f_std] } ) ).T<jupyter_output><empty_output><jupyter_text>Merge<jupyter_code>trt_2014 = (df.xs(['T','NHAZ','Plastic'],level=[0,1,2])
 .loc[(['Energy recovery','Landfill','Recycling','Waste treatment'],['SE','IT','DK','EL']),2014]
).unstack(0)
trt_2014
df = pd.read_table('data/waste.tsv',header=0,sep=',|\t',engine='python',
                  names=['unit','hazard','activity','waste','country',2016,2014,2012,
                        2010,2008,2006,2004])
df.loc[df.waste=='W074','waste']='Plastic'
df.loc[df.activity=='G-U_X_G4677','activity']='Services'
df.loc[df.activity=='C','activity']='Manufacturing'
df.loc[df.activity=='F','activity']='Construction'
df.loc[df.activity=='EP_HH','activity']='Households'
df.loc[df.activity=='TOTAL_HH','activity']='Total'
df.iloc[:,5:] = df.iloc[:,5:].applymap(lambda x: x.rstrip('bcdefinprsuz')) 
df.iloc[:,5:] = df.iloc[:,5:].apply(pd.to_numeric,errors='coerce')
df = df.set_index(keys=['unit','hazard','waste','activity','country'],inplace=False)
df.head()
gen_2014 = (df.xs(['T','NHAZ','Plastic'],level=[0,1,2])
 .loc[(['Manufacturing','Construction','Households','Total'],['SE','IT','EL','EU28']),2014]
).unstack(0)
gen_2014
pd.merge(trt_2014,gen_2014,how='outer',left_index=True,right_index=True)
pd.merge(trt_2014,gen_2014,how='inner',left_index=True,right_index=True)
pd.merge(trt_2014,gen_2014,how='left',left_index=True,right_index=True)
pd.merge(trt_2014,gen_2014,how='right',left_index=True,right_index=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.2763233601841197, 'fraction_numerical': 0.048906789413118525, 'mean_word_length': 10.34257748776509, 'pattern_counts': {'":': 0, '<': 37, '<?xml version=': 0, '>': 41, 'https://': 3, 'lorem ipsum': 0, 'www.': 2, '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': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '344019', 'license': 'MIT', 'n_tokens_mistral': 3158, 'n_tokens_neox': 2830, 'n_words': 217, 'path': 'lectures/pandas_3_4.ipynb', 'repo_name': 'urania277/jupyter-course', 'size': '6952'}