Search code examples
pythonpandasdataframegroup-by

Reorganizing DataFrame (interchanging row information as columns)

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?

Solution

  • 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)