How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from ex-CodePudding

which has 4 to 5 merged cells, blank cells. I need to get them in a format where a column is created for each merged cell

Please find the link for Sample file and also the output required below

Link : https://docs.google.com/spreadsheets/d/1fkG9YT9YGg9eXYz6yTkl7AMeuXwmNYQ44oXgA8ByMPk/edit?usp=sharing

Code so far:

dfs = pd.read_excel('/Users/john/Desktop/Internal/Raw Files/Med/TVS/sample.xlsx',sheet_name=None, header=[0,4], index_col=[0,1])

df_2 = (pd.concat([df.stack(0).assign(name=n) for n,df in dfs.items()])
          .rename_axis(index=['Date','WK','Brand','A_B','Foo_Foos','Cur','Units'], columns=None)
          .reset_index())

How do I create multiple column in the above code? Right now i'm getting the below error:

ValueError: Length of names must match number of levels in MultiIndex.

CodePudding user response：

Try this:

df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = [0,1])
df.stack(level=[0,1,2,3,4])