1

Suppose I have a data frame as follows:

df = pd.DataFrame(np.array([[2018,'R1','C1',1],[2018,'R1','C2',2],[2018,'R1','C3',3],[2018,'R1','C4',4],[2018,'R1','C5',5],[2018,'R2','C6',6],[2018,'R2','C7',7],[2018,'R2','C8',8],[2018,'R2','C9',9],[2018,'R2','C10',10]]),columns=['Year', 'Region', 'Country', 'Spend'])

I'd like to add summary lines to each possible group by (groups) so that the output dataframe looks something like this:

enter image description here

I have learned how to add summary/total lines as a column based on this StackOverflow post. But I would like to have these total lines merged to the original data frame as show in the above screenshot and I would like to accomplish this in as few lines as possible (that is, avoiding having to call different groupby combinations manually like below):

df['ByYearTotalCount'] = df.groupby(['Year'])['Spend'].transform('sum')
df['ByYearByRegionTotalCount'] = df.groupby(['Year','Region'])['Spend'].transform('sum')

Could anyone help me come up with the best pandas-like way to accomplish this? Thank you in advance for your help!

Scott Boston
  • 147,308
  • 15
  • 139
  • 187
user1330974
  • 2,500
  • 5
  • 32
  • 60

1 Answers1

1

Use groupby to get the Total/Subtotal rows first and concat the result dataframes

df2 = df.groupby(['Year'])['Spend'].sum().reset_index()
df3 = df.groupby(['Year', 'Region'])['Spend'].sum().reset_index()
df = pd.concat([df, df2, df3], sort=False).fillna('All').sort_values(by=['Region', 'Country'])

Output

df2
   Year  Spend
0  2018     55

df3
   Year Region  Spend
0  2018     R1     15
1  2018     R2     40

df
   Year Region Country  Spend
0  2018    All     All     55
0  2018     R1     All     15
0  2018     R1      C1      1
1  2018     R1      C2      2
2  2018     R1      C3      3
3  2018     R1      C4      4
4  2018     R1      C5      5
1  2018     R2     All     40
9  2018     R2     C10     10
5  2018     R2      C6      6
6  2018     R2      C7      7
7  2018     R2      C8      8
8  2018     R2      C9      9
henrywongkk
  • 1,840
  • 3
  • 17
  • 26
  • 1
    Thank you! I ended up doing a similar thing but using `append`. But your suggested solution looks neat. I'm accepting it because it does the job that I wanted. Thank you again. – user1330974 Oct 08 '19 at 13:17