Transpose MultiIndex in Pandas-CodePudding

I have multiple dataframes with billing information for each month, i.e. data for Jan, Mar, Apr, etc..

I am trying to do the following for just one dataframe at a time, to keep it simple because I am fairly new to Pandas:

march = np.arange(27).reshape(3, 9)
march = pd.DataFrame(data=march, index=['A', 'B', 'C'], columns=['mar'   chr(ord('A')   i) for i in range(9)])

Multi-indexing on one of the dataframes:

dates = ['March']
cols = pd.MultiIndex.from_product([dates, march.columns.to_list()])
    
pd.DataFrame(march, columns=cols)

Output:

(https://i.stack.imgur.com/Wp3AH.png)

I want it so that it shows up like a transposed dataframe, i.e.:

(https://i.stack.imgur.com/ucXAG.png)

I tried stacking and unstacking but it doesn't work and just reverts to the untransposed format.

My end goal is to be able to do this to multiple dataframes and output them all in one table showing each month and their respective data.

For example:

       January    February
      A   B   C   A  B  C
colA  x   x   x   x  x  x
colB  x   x   x   x  x  x
etc

Sorry if this seems like a newbie question, I am new to Pandas and the similar questions posted didn't work for my case.

CodePudding user response：

For MultiIndex assign it back to march.columns and for reshape use DataFrame.stack and Series.unstack:

dates = ['March']
march.columns = pd.MultiIndex.from_product([dates, march.columns.to_list()])
    

df = march.stack().unstack(0)
print (df)
     March        
         A   B   C
marA     0   9  18
marB     1  10  19
marC     2  11  20
marD     3  12  21
marE     4  13  22
marF     5  14  23
marG     6  15  24
marH     7  16  25
marI     8  17  26