pandas convert dataframe to pivot_table where index is the sorting values

user_8893827 0
Viewed 357

i have the following dataframe:

   site   height_id  height_meters
0  9      c3         24
1  9      c2         30
2  9      c1         36
3  3      c0         18
4  3      bf         24
5  3      be         30
6  4      10         18
7  4      0f         24
8  4      0e         30

i want to transform it to the following this column indexes is values of 'site' and the values is 'height_meters' and i want it to be indexed by the order of the values (i looked in the internet and didnt find somthing similar... tried to groupby and make some pivot table without success):

   9   3   4
0  24  18  18
1  30  24  24
2  36  30  24

the gap between numbers isn't necessary ... here is the df

my_df = pd.DataFrame(dict(
    site=[9, 9, 9, 3, 3, 3, 4, 4, 4],
    height_id='c3,c2,c1,c0,bf,be,10,0f,0e'.split(','),
    height_meters=[24, 30, 36, 18, 24, 30, 18, 24, 30]
))
pandas-groupby pandas dataframe python
1 Answers

You can use GroupBy.cumcount for counter of column site:

print (my_df.groupby('site').cumcount())

0    0
1    1
2    2
3    0
4    1
5    2
6    0
7    1
8    2
dtype: int64

You can convert it to index with site column and reshape by Series.unstack:

df = my_df.set_index([my_df.groupby('site').cumcount(), 'site'])['height_meters'].unstack()
print (df)
site   3   4   9
0     18  18  24
1     24  24  30
2     30  30  36

Similar solution with DataFrame.pivot and column created by cumcount:

df = my_df.assign(new=my_df.groupby('site').cumcount()).pivot('new','site','height_meters')
print (df)
site   3   4   9
new             
0     18  18  24
1     24  24  30
2     30  30  36

If order is important add DataFrame.reindex by unique values of column site:

df = (my_df.set_index([my_df.groupby('site').cumcount(), 'site'])['height_meters']
           .unstack()
           .reindex(my_df['site'].unique(), axis=1))
print (df)
site   9   3   4
0     24  18  18
1     30  24  24
2     36  30  30

Last for remove site (new) columns and index names is possible use DataFrame.rename_axis:

df = df.rename_axis(index=None, columns=None)
print (df)
    3   4   9
0  18  18  24
1  24  24  30
2  30  30  36
user_29010020
Related