Home > Enterprise >  Pandas sort and bin data within dataframe to make pivottable
Pandas sort and bin data within dataframe to make pivottable

Time:06-18

I have a dataframe with (random) observations for height, period and zones as such:

Height = [1, 4, 3, 3, 3, 2, 4, 2, 3, 3, 3, 1, 4, 3, 3, 4, 1, 4, 2, 2]
Period = [5, 4, 2, 4, 2, 2, 3, 3, 5, 2, 4, 5, 4, 2, 4, 4, 3, 5, 4, 3]
Zone = [1,1,3,1,4,1,1,1,1,4,1,3,2,1,4,2,4,4,2,4]

Direction = [292.5,  22.5, 202.5, 337.5, 292.5, 337.5, 337.5, 337.5,  22.5, 292.5,  22.5, 157.5, 112.5, 337.5, 292.5, 112.5, 247.5, 247.5,
       112.5, 292.5]

I want to make a table with the zones on the indices, the unique periods on the columns and then for each combination of index-column I want to have the maximum of the height as such:

enter image description here

Any idea how to do this?

CodePudding user response:

You can try

out = (df.pivot_table(index='Zone', columns='Period', values='Height', aggfunc='max')
       .rename(index=lambda x: f'Zone={x}', columns=lambda x: f'Period={x}'))
print(out)

Period  Period=2  Period=3  Period=4  Period=5
Zone
Zone=1       3.0       4.0       4.0       3.0
Zone=2       NaN       NaN       4.0       NaN
Zone=3       3.0       NaN       NaN       1.0
Zone=4       3.0       2.0       3.0       4.0
  • Related