Python - Creating a custom dataframe from transposing an existing one

user_19257503 0
Viewed 31

I have a dataframe that consists of the following enteries:

Dataset

The dataset consists information about each country for about 10 years for 5 indicators as represented above.

I am trying to convert Indicator column into rows for all the 5 indicators and similarly convert each year column into one merged columns.

Ideally the final output should look like:

Final Dataset

So, the country column should have extra enetries according to the number of years and the values should transpose according to each indicator.

I tried using pandas in built functions such as melt and pivot but not getting anywhere.

Any guidance on this would be appricated. Thanks!

pandas python
1 Answers

You can use stack and unstack:

out = (df.set_index(['Country Name', 'Indicator'])
         .rename_axis(columns='Year').stack('Year')
         .unstack('Indicator').rename_axis(columns=None)
         .reset_index())
print(out)

# Output
  Country Name  Year         I         J         K         L
0            A  2008  0.002535  0.966967  0.033397  0.487713
1            A  2009  0.797714  0.642878  0.752803  0.527796
2            A  2010  0.773789  0.288100  0.013059  0.918957
3            B  2008  0.979707  0.968245  0.820731  0.309862
4            B  2009  0.086082  0.608920  0.629591  0.156926
5            B  2010  0.983092  0.536192  0.380157  0.091473
6            C  2008  0.834870  0.145200  0.225985  0.686520
7            C  2009  0.771646  0.834432  0.519951  0.651756
8            C  2010  0.003791  0.292212  0.257748  0.473694

Alternative with melt and pivot:

out = (df.melt(['Country Name', 'Indicator'], var_name='Year')
         .pivot(['Country Name', 'Year'], 'Indicator', 'value')
         .rename_axis(columns=None).reset_index())

Input data:

>>> df
   Country Name Indicator      2008      2009      2010
0             A         I  0.002535  0.797714  0.773789
1             A         J  0.966967  0.642878  0.288100
2             A         K  0.033397  0.752803  0.013059
3             A         L  0.487713  0.527796  0.918957
4             B         I  0.979707  0.086082  0.983092
5             B         J  0.968245  0.608920  0.536192
6             B         K  0.820731  0.629591  0.380157
7             B         L  0.309862  0.156926  0.091473
8             C         I  0.834870  0.771646  0.003791
9             C         J  0.145200  0.834432  0.292212
10            C         K  0.225985  0.519951  0.257748
11            C         L  0.686520  0.651756  0.473694
user_152399510
Related