Is it possible to pivot in this way with pandas?

user_11090490 0
Viewed 34

I have this dataframe.

report_id landcover_type data_type mean area
615 Acid grassland canopyheight 2 493.9125
615 Arable and horticulture canopyheight 4 0.86
615 Acid grassland carbonstoragewoodlands 8 493.9125
615 Arable and horticulture carbonstoragewoodlands 161 0.86

is it possible to pivot it so I have a new column for each distinct data_type with their own aggregation of my choosing like this?

report_id landcover_type canopyheight_mean carbonstorage_total area
615 Acid Grassland 2 8 493.9125
615 Arable and horticulture 4 161 493.9125
pandas dataframe
1 Answers

If need change aggregate function by data - if numeric aggregate mean else aggregate join use:

print (df)
   report_id           landcover_type               data_type  mean area
0        615           Acid grassland            canopyheight     2   aa
1        615           Acid grassland            canopyheight     7   rr
2        615  Arable and horticulture            canopyheight     4   ss
3        615           Acid grassland  carbonstoragewoodlands     8   dd
4        615  Arable and horticulture  carbonstoragewoodlands   161   ff
f = lambda x: x.mean() if np.issubdtype(x.dtype, np.number) else ','.join(x)
df1 = df.pivot_table(index=['report_id', 'landcover_type'], 
                     columns= 'data_type',
                     aggfunc=f)

df1.columns = df1.columns.map('_'.join)
df1 = df1.reset_index()
print (df1)
   report_id           landcover_type area_canopyheight  \
0        615           Acid grassland             aa,rr   
1        615  Arable and horticulture                ss   

  area_carbonstoragewoodlands  mean_canopyheight  mean_carbonstoragewoodlands  
0                          dd                4.5                          8.0  
1                          ff                4.0                        161.0
user_29010020
Related