pandas group by and sum with values being displayed

Question

I need to group by two columns and sum the third one. My data looks like this:

site        industry        spent
Auto        Cars             1000
Auto        Fashion          200
Auto        Housing          100
Auto        Housing          300
Magazine    Cars             100
Magazine    Fashion          200
Magazine    Housing          300
Magazine    Housing          500

My code:

df.groupby(by=['site', 'industry'])['Revenue'].sum()

The output is:

                       spent
site        industry
Auto        Cars             1000
            Fashion          200
            Housing          400
Magazine    Cars             100
            Fashion          200
            Housing          800

When I convert it to csv I only get one column - spent. My desired output is the same format as the original data only the revenue needs to be summed and I need to see all the values in columns.

Answer 1

Try this, using as_index=False:

df = df.groupby(by=['site', 'industry'], as_index=False).sum()
print(df)


       site industry  spent
0      Auto     Cars   1000
1      Auto  Fashion    200
2      Auto  Housing    400
3  Magazine     Cars    100
4  Magazine  Fashion    200
5  Magazine  Housing    800