Switch Header and Column in a DataFrame-CodePudding

Economy  Year  Indicator1 Indicator2  Indicator3  Indicator4    . 
  UK       1      23       45          56          78
  UK       2      24       87          32          42
  UK       3      22       87          32          42
  UK       4       2       87          32          42
  FR       .       .        .           .           .

This is my data which extends on and held as a DataFrame, I want to switch the Header(Indicators) and the Year columns, seems like a pivot. There are hundreds of indicators and 20 years.

CodePudding user response：

Use DataFrame.melt with DataFrame.pivot:

df = (df.melt(['Economy','Year'], var_name='Ind')
        .pivot(['Economy','Ind'], 'Year', 'value')
        .reset_index()
        .rename_axis(None, axis=1))
print (df)
  Economy         Ind   1   2   3   4
0      UK  Indicator1  23  24  22   2
1      UK  Indicator2  45  87  87  87
2      UK  Indicator3  56  32  32  32
3      UK  Indicator4  78  42  42  42

CodePudding user response：

Another option is to set Year column as index and then use transpose.
Consider the code below:

import pandas as pd

df = pd.DataFrame(columns=['Economy', 'Year', 'Indicator1', 'Indicator2', 'Indicator3', 'Indicator4'],
    data=[['UK', 1, 23, 45, 56, 78],['UK', 2, 24, 87, 32, 42],['UK', 3, 22, 87, 32, 42],['UK', 4, 2, 87, 32, 42],
    ['FR', 1, 22, 33, 11, 35]])

# Make Year column as index
df = df.set_index('Year')
# Transpose columns to rows and vice-versa
df = df.transpose()
print(df)

gives you

Year         1   2   3   4   1
Economy     UK  UK  UK  UK  FR
Indicator1  23  24  22   2  22
Indicator2  45  87  87  87  33
Indicator3  56  32  32  32  11
Indicator4  78  42  42  42  35

CodePudding user response：

u can use transpose like this :

df = df.set_index('Year')
df = df.transpose()
print (df)