i have a excel sheet

now i want to group it like this

here is my code i took it from pandas official doc but it's not working

import pandas as pd

df = pd.DataFrame({'Animal': ['Falcon', 'Falcon','Parrot', 'Parrot'],
                   'Max Speed': [380., 370., 24., 26.]})
l = df.groupby("Animal", group_keys=True).apply(lambda x: x)
l.to_excel("kk.xlsx", index=False)

CodePudding user response：

Are you sure you want a group-by operation? You don't seem to be doing any aggregation on the data based on the screenshot you've provided.

Maybe setting the index on the Animal column is what you need?

I.e., df.set_index("Animal")

Docs: https://pandas.pydata.org/pandas-docs/version/0.22/generated/pandas.DataFrame.set_index.html

CodePudding user response：

As @Stefan Popov points out, there is no aggregation of data so no point in groupby. Assuming that all you want is a different presentation then you could just remove the duplicated Animal names. That removes links to the data but forms the output you want.

l = df.duplicated('Animal', keep = 'last')
df['Animal'] = df['Animal'][l]
df['Animal']= df['Animal'].fillna('')

print(df)

which results in:

   Animal  Max Speed
0  Falcon      380.0
1              370.0
2  Parrot       24.0
3               26.0

which when you drop the index and send to Excel gives the result you indicated. But note that you have lost the link from some speeds to the Animal.