how to use first two rows in dataframe to be column indexes in python

Question

I have a pandas dataframe that was a bunch of one row dataframes concatenated together. The concatenated dataframe looks like this:

import pandas as pd

Type = ['Type','Category_A','Category_A','Category_B']
Year = ['Year','2020','2021','2020']
Group1 = ['Group1',123.45, 456.78, 678.90]
Group2 = ['Group2',999.23,1234.23,6543.23]
Group3 = ['Group3',12.45,546.12,6573.12]
Group4 = ['Group4',98.65, 657.34,654.78]

Type_df = pd.DataFrame([Type], columns=['Title','A','B','C'])
Year_df = pd.DataFrame([Year], columns=['Title','A','B','C'])
Group1_df = pd.DataFrame([Group1], columns=['Title','A','B','C'])
Group2_df = pd.DataFrame([Group2], columns=['Title','A','B','C'])
Group3_df = pd.DataFrame([Group3], columns=['Title','A','B','C'])
Group4_df = pd.DataFrame([Group4], columns=['Title','A','B','C'])

concat = pd.concat([Type_df, Year_df, Group1_df, Group2_df, Group3_df, Group4_df])

print(concat)

Output is:

 Title           A           B           C
0    Type  Category_A  Category_A  Category_B
0    Year        2020        2021        2020
0  Group1      123.45      456.78       678.9
0  Group2      999.23     1234.23     6543.23
0  Group3       12.45      546.12     6573.12
0  Group4       98.65      657.34      654.78

What I am trying to do is take the first two rows of data and make them the first two columns, then make the next rows (Group1 - Group4) and make them columns 3 - 6. So, my end result will look something like this:

Type         Year    Group1     Group2    Group3    Group4
Category_A   2020    123.45     999.23     12.45     98.65
Category_A   2021    456.78    1234.23    546.12    657.34
Category_B   2020     678.9    6543.23   6573.12    654.78

I'm still very new at Python and I have tried a few different versions of pivot, pivot_table, and melt but I cannot seem to ever get it to work correctly. Any help would be appreciated.

Answer 1

Change your code to

Type_df = pd.DataFrame(Type, index=['Title','A','B','C']) # here change remove [] and make that the input from column to index 
Year_df = pd.DataFrame(Year, index=['Title','A','B','C'])
Group1_df = pd.DataFrame(Group1, index=['Title','A','B','C'])
Group2_df = pd.DataFrame(Group2, index=['Title','A','B','C'])
Group3_df = pd.DataFrame(Group3, index=['Title','A','B','C'])
Group4_df = pd.DataFrame(Group4, index=['Title','A','B','C'])
concat = pd.concat([Type_df, Year_df, Group1_df, Group2_df, Group3_df, Group4_df],axis=1)
concat
Out[963]: 
                0     0       0        0        0       0
Title        Type  Year  Group1   Group2   Group3  Group4
A      Category_A  2020  123.45   999.23    12.45   98.65
B      Category_A  2021  456.78  1234.23   546.12  657.34
C      Category_B  2020   678.9  6543.23  6573.12  654.78
concat.columns=concat.loc['Title',:]# do a little bit adjust here
concat.drop('Title',inplace=True)
concat
Out[966]: 
Title        Type  Year  Group1   Group2   Group3  Group4
A      Category_A  2020  123.45   999.23    12.45   98.65
B      Category_A  2021  456.78  1234.23   546.12  657.34
C      Category_B  2020   678.9  6543.23  6573.12  654.78