[FIXED] Convert column data to row wise data

Issue

I have a dataframe as shown below,

data_dict = {'CustCode': {0: 64, 1: 64, 2: 97, 3: 97, 4: 97, 5: 97, 6: 97, 7: 97, 8: 97, 9: 110}, 'InvoiceMonth': {0: 'Aug', 1: 'Sep', 2: 'Apr', 3: 'Aug', 4: 'Feb', 5: 'Jan', 6: 'Jul', 7: 'Mar', 8: 'May', 9: 'Feb'}, 'TotalAmount': {0: 357300, 1: 419800, 2: 515000, 3: 740700, 4: 301200, 5: 197200, 6: 112400, 7: 534900, 8: 220500, 9: 422500}}

How can I convert that into something like this(created manually few rows to show desired DataFrame),

Any help will be appreciated.

Solution

You need to pivot your table, and since there are some missing months on your sample you also need to add them seperately:

import numpy as np
new_df = df.pivot(index='CustCode', columns='InvoiceMonth', values='TotalAmount')

months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]

new_df[[col for col in months if col not in new_df.columns]] = np.nan
new_df['Total'] = new_df.sum(axis=1)
new_df.reset_index(inplace=True)

Answered By - Nuri Taş