I am new to python and trying to reshape table from excel file as I have multiple header I am trying to convert first header into 2 separate column. i am attaching my code output and data here. Input Table
import pandas as pd
import numpy as nm
df = pd.read_excel(r'.\test.xlsx', header=[0, 1])
df = (df.stack(0, dropna=False)
.rename_axis(index=('Customer','Date'), columns=None)
.reset_index())
df.to_csv(r'.\testnew.csv',index=False)
print(df)
Desired Output -
| Customer | Date | Budget | Actual | Amount |
|---|---|---|---|---|
| John | Jan-20 | 100 | 50 | 0 |
| John | Feb-20 | |||
| John | Mar-20 | |||
| Chris | Jan-20 | 120 | 80 | 0 |
| Chris | Feb-20 | 50 | 10 | 20 |
| Chris | Mar-20 | 50 | 45 |
