1

I have the next DataFrame.

data={
 'date':['2023-01', '2023-01', '2023-01', '2023-01', '2023-02', '2023-02', '2023-02', '2023-02', '2023-02'],
'concept':['FOOD', 'CAR', 'EDUCATION', 'ELECTRICITY', 'FOOD', 'CAR', 'TRAVEL', 'WATER', 'ELECTRICITY'],
'amount':[455.54, 237.41, 3.91, 213.2, 28.72, 422.72, 263.29, 3.93, 89.35]  
      }
df = pd.DataFrame(data)

I would like to reorder the information so I have all the concepts as rows, and then each column is a month. Finally a third column with the total sum of all months. The result should be a DataFrame like this:

Jan-23 Feb-23 Total
CAR 237.41 422.72 660.13
EDUCATION 3.91 0 3.91
ELECTRICITY 213.2 89.35 302.55
FOOD 455.54 28.72 484.26
TRAVEL 0 263.29 263.29
WATER 0 3.93 3.93

How could I do it?

nekovolta
  • 496
  • 2
  • 14
  • I don't know about the additional column but every matrix-like structure should have a `transpose` function. I'd assume dataframes do too. – Lone Lunatic Mar 15 '23 at 15:41
  • 1
    It's not a transpose, it's a [`pivot`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.pivot.html) – roganjosh Mar 15 '23 at 15:43

1 Answers1

2

You can use the pivot function like this and then use the sum function

new_df = df.pivot(columns='date', index='concept', values='amount')
new_df['total'] = new_df.sum(axis=1)
P. Shroff
  • 396
  • 3
  • 5