pandas group_by dataframe outputs only the aggregation column when written to excel; how to get the entire output on excel?

Question

I am trying to group and sum-aggregate a specific column in my dataframe and then write this entire output to excel; however, when i check the excel file after using the below code, it only contains the one aggregated column as the output and does not include any of the other grouping. I am someone could help me correct the code or provide suggestions as to how to achieve this? Thanks in advance!

my_df = pd.DataFrame(df.groupby(['Parent Category','Expense'])['Parent Category','Expense','Variance'].sum())

Next, I use the below code to write it to excel but it does not write the output into excel as in the image above

writer = pd.ExcelWriter('Test.xlsx', engine = 'xlsxwriter')
my_df.to_excel(writer,sheet_name = '1', index = False)
writer.save()

why is the group by output not the same when written to excel?

try using the `as_index=False` parameter to `.groupby()`? That will keep the column headers in the right place. — Todd, Apr 23 '20 at 23:55
I tried using the as_index parameter as well! it worked! Thank you! — Vic, Apr 23 '20 at 23:56

score 2 · Accepted Answer · answered Apr 23 '20 at 23:31

2

Can you please try the following?

writer = pd.ExcelWriter('Test.xlsx', engine = 'xlsxwriter')
my_df.reset_index().to_excel(writer,sheet_name = '1', index = False)
writer.save()

Or if want to keep multi-index:

writer = pd.ExcelWriter('Test.xlsx', engine = 'xlsxwriter')
my_df.to_excel(writer,sheet_name = '1', index = True)
writer.save()

answered Apr 23 '20 at 23:31

Allen Qin

19,507
8
51
67

thanks for your response! both of those solutions worked. just had to keep the index variable to its default value, True! – Vic Apr 23 '20 at 23:47

pandas group_by dataframe outputs only the aggregation column when written to excel; how to get the entire output on excel?

1 Answers1