Converting multiple variables into one column and creating a matching values column using pandas

user_15671498 0
Viewed 368

I have a Table with the following format:

Country GDP LifeExp
USA 6.5 75
UK 9.5 78
Italy 5.5 80

I need to change the Table above to the format of the Table below. This is just a small part of the actual table so hard coding is not going to cut it unfortunately.

Country Indicator name Value
USA GDP 6.5
USA LifeExp 75
UK GDP 9.5
UK LifeExp 78
Italy GDP 5.5
Italy LifeExp 80

Here is the code to create the first Table:

import pandas as pd
df = pd.DataFrame({'Country':["USA", "UK", "Italy"],
                  'GDP':[6.5, 9.5, 5.5],
                  'LifeExp':[75,78,80]})

I've never uploaded something before on stackoverflow so I hope I've provided sufficient info for someone to help me with this problem.

Thanks in advance!

pandas transform dataframe formatting python
2 Answers

You can use .melt() with .sort_values(), as follows:

(df.melt(id_vars='Country', var_name='Indicator name', value_name='Value')
   .sort_values('Country', ascending=False)
).reset_index(drop=True)




# Result

  Country Indicator name  Value
0     USA            GDP    6.5
1     USA        LifeExp   75.0
2      UK            GDP    9.5
3      UK        LifeExp   78.0
4   Italy            GDP    5.5
5   Italy        LifeExp   80.0

You can choose sorting order of Country column. If you want it in ascending order, you can simply remove the parameter ascending=False in the .sort_values() function.

user_150706970

Use .stack() and .reset_index():

print(
    df.set_index("Country")
    .stack()
    .reset_index()
    .rename(columns={"level_1": "Indicator Name", 0: "Value"})
)

Prints:

  Country Indicator Name  Value
0     USA            GDP    6.5
1     USA        LifeExp   75.0
2      UK            GDP    9.5
3      UK        LifeExp   78.0
4   Italy            GDP    5.5
5   Italy        LifeExp   80.0
user_100359850
Related