Reshape data frame, so the index column values become the columns-CodePudding

I want to reshape the data so that the values in the index column become the columns

My Data frame:

     Gender_Male    Gender_Female   Location_london Location_North  Location_South
Cat                 
V         5              4              4                2              3    
W         15            12             12                7              8
X         11            15             16                4              6
Y         22            18             21                9              9
Z         8              7              7                4              4

Desired Data frame:

Is there an easy way to do this? I also have 9 other categorical variables in my data set in addition to the Gender and Location variables. I have only included two variables to keep the example simple.

Code to create the example dataframe:

df1 = pd.DataFrame({
    'Cat' : ['V','W', 'X', 'Y', 'Z'],
    'Gender_Male' :   [5, 15, 11, 22, 8],
    'Gender_Female' : [4, 12, 15, 18, 7],
    'Location_london': [4,12, 16, 21, 7], 
    'Location_North' : [2, 7,  4,  9, 4], 
    'Location_South' : [3, 8,  6,  9, 4]  
}).set_index('Cat')
df1

CodePudding user response：

You can transpose the dataframe and then split and set the new index:

Transpose

dft = df1.T
print(dft)

Cat              V   W   X   Y  Z
Gender_Male      5  15  11  22  8
Gender_Female    4  12  15  18  7
Location_london  4  12  16  21  7
Location_North   2   7   4   9  4
Location_South   3   8   6   9  4

Split and set the new index

dft.index = dft.index.str.split('_', expand=True)
dft.columns.name = None
print(dft)

                 V   W   X   Y  Z
Gender   Male    5  15  11  22  8
         Female  4  12  15  18  7
Location london  4  12  16  21  7
         North   2   7   4   9  4
         South   3   8   6   9  4