0

I have a dataframe with 5 columns, The first 2 columns show the persons name and month, and the remaining columns are categories of expense, Below is an Image of how this data set looks:

Data set

I need to summarise this data and show the total expense per month per category. Below is an image of how the output should look like:

Final Desired Output

In the actual data set I have far more categories (22) than the 3 categories of expense shown here, so some form of automation would definitely help instead of mentioning each and every column name.

However, I'll be happy with either solutions/help. Many Thanks in advance and below is the code to create the data set:

data = {'Name': ['Tom', 'Nick','Jack', 'Tom', 'Nick','Jack', 'Tom', 'Nick','Jack'],
        'Month': ['01', '01', '01', '02', '02', '02', '03', '03', '03'],
        'Super':[52, 25, 125, 40, 35, 90, 42, 29, 88], 
       'travel':[32, 18, 41, 23, None, 45, 28, 21, 76], 
       'bar':  [24, 12, 38, 14, 9, None, 28, 9, 22]}


df = pd.DataFrame(data)
df
SAJ
  • 368
  • 1
  • 2
  • 14
  • https://softwareengineering.meta.stackexchange.com/questions/6166/open-letter-to-students-with-homework-problems – Daviid Aug 03 '23 at 10:50
  • https://meta.stackoverflow.com/questions/334822/how-do-i-ask-and-answer-homework-questions – Daviid Aug 03 '23 at 10:50

1 Answers1

1

Here's the solution for your query:

df.groupby('Month').agg('sum')

Note that sum sums only numeric columns.

Niqua
  • 386
  • 2
  • 15