Redefining a pandas dataframe based on its group

Question

Iam using this dataframe

  source   fruit  2019  2020  2021
0      a   apple     3     1     1
1      a  banana     4     3     5
2      a  orange     2     2     2
3      b   apple     3     4     5
4      b  banana     4     5     2
5      b  orange     1     6     4

i want to refine it like this

 source   fruit  2019  2020  2021
0      a   total     9     6     8
1      a   seeds     5     3     3
2      a  banana     4     3     5
3      b   total     8    15    11
4      b   seeds     4    10     9
5      b  banana     4     5     2

total is sum of all fruits in that year for each source.
seeds is the sum of fruits containing seeds for each year for each source.

I tried
Appending new empty rows : Insert a new row after every nth row & Insert row at any position
But wasn't getting the expected result.

What would be the best way to get the desired output?

score 2 · Accepted Answer · answered May 10 '21 at 10:47

TRY:

df1 = df.groupby('source', as_index=False).sum().assign(fruit = 'total')
seeds = ['orange','apple']
df2 = df.loc[df['fruit'].isin(seeds)].groupby('source', as_index=False).sum().assign(fruit = 'seeds')
final_df = pd.concat([df.loc[~df['fruit'].isin(seeds)], df1,df2])

Redefining a pandas dataframe based on its group

1 Answers1