CODEWITHSUNDEEP
pythonexcelpandasdataframe
Annie
Annie

Reputation: 37

Reshape multi-header table in python

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)

Printed Output - enter image description here

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

Upvotes: 3

Views: 262

Answers (1)

jezrael
jezrael

Reputation: 863281

I believe you need DataFrame.stack:

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

Upvotes: 4

Related Questions