[FIXED] Need to Reshape / Transpose Dataframe in Python

Issue

I have the following dataframe and I want to transpose it so that the elements in column "Trans_Type" become new columns to represent the values from column "Trans_amount".

import pandas as pd
import numpy as np

df = pd.DataFrame([['21/10/2023','1','CR',2323],
                    ['21/10/2023','1','CR',23],
                    ['21/10/2023','1','DR',65],
                    ['21/10/2023','2','CR',3.3],
                    ['21/10/2023','3','CR',56],      
                    ['21/10/2023','3','DR',23.66],
                    ['21/10/2023','4','CR',54.34],
                    ['22/10/2023','4','CR',23.34],
                    ['22/10/2023','4','DR',5.5]],
columns = ['Date','Account_Number','Trans_Type','Trns_Amount'])
df

DF Output:

    Date        Account_Number  Trans_Type  Trns_Amount
0   21/10/2023  1               CR          2323.00
1   21/10/2023  1               CR          23.00
2   21/10/2023  1               DR          65.00
3   21/10/2023  2               CR          3.30
4   21/10/2023  3               CR          56.00
5   21/10/2023  3               DR          23.66
6   21/10/2023  4               CR          54.34
7   22/10/2023  4               CR          23.34
8   22/10/2023  4               DR          5.5

Expected Output:

    Date        Account_Number    CR      DR
0   21/10/2023  1                 2323.00 NaN
1   21/10/2023  1                 23.00   NaN
2   21/10/2023  1                 NaN     65.00
3   21/10/2023  2                 3.30    NaN
4   21/10/2023  3                 56.00   NaN
5   21/10/2023  3                 NaN     23.66
6   21/10/2023  4                 54.34   NaN
7   22/10/2023  4                 23.34   NaN
8   22/10/2023  4                 NaN     5.50

Any assistance would be great. Thanks Alan

Solution

You can use DataFrame.reset_index for helper column with unique values, so possible use DataFrame.pivot for reshape:

out = (df.reset_index()
         .pivot(index=['index','Date','Account_Number'],
                columns='Trans_Type', 
                values='Trns_Amount')
         .reset_index(level=[1,2]))
print (out)
Trans_Type        Date Account_Number       CR     DR
index                                                
0           21/10/2023              1  2323.00    NaN
1           21/10/2023              1    23.00    NaN
2           21/10/2023              1      NaN  65.00
3           21/10/2023              2     3.30    NaN
4           21/10/2023              3    56.00    NaN
5           21/10/2023              3      NaN  23.66
6           21/10/2023              4    54.34    NaN
7           22/10/2023              4    23.34    NaN
8           22/10/2023              4      NaN   5.50

Answered By - jezrael