0

I am wanting to extract the following:

ID Name Rating
123 Red 5
123 Red 4
123 Red 3
123 Red 5
123 Red 4
123 Red 3
456 Blue 8
456 Blue 4
456 Blue 3
789 Yellow 6
789 Yellow 8

And basically return this:

ID Name Rating Count
123 Red 4 6
456 Blue 5 3
789 Yellow 7 2

(ID and Name have been consolidated, rating is returning and average, and there is also a value count of the previously unique rows)

I have tried all manner of things with pandas.groupy but it doesn't seem to like this number of columns... Best I have managed is the following:

newtable = data.groupby('Name', as_index=False, sort=False)['Rating'].mean()

If anyone has any suggestions it would be greatly appreciated!

afroduck
  • 143
  • 8
  • 1
    Use `data.groupby('Name', as_index=False, sort=False).agg(Rating=('Rating','mean'), Count=('Name','size'))` – jezrael Oct 26 '22 at 11:51
  • Thanks so much! This works really well. Quick question: How can I also keep the ID column in my results? – afroduck Oct 26 '22 at 11:56
  • 1
    Here are same values per groups `Name`, so use `data.groupby(['ID','Name'], as_index=False, sort=False).agg(Rating=('Rating','mean'), Count=('Name','size'))` – jezrael Oct 26 '22 at 11:57

0 Answers0