CODEWITHSUNDEEP
pythonpandasdataframetranspose
Laura Walker
Laura Walker

Reputation: 307

Transposing Data in Python

I have data from the World Bank that look like this: 

Country Name    Country Code    1995    1996    1997    1998    1999    2000    2001    2002    2003    2004    2005    2006    2007    2008    2009    2010    2011    2012    2013    2014    2015
Aruba           ABW            80326    83195   85447   87276   89004   90858   92894   94995   97015   98742   100031  100830  101218  101342  101416  101597  101936  102393  102921  103441  103889

It's population data from 250 some countries and I've just shown the first one for the sake of example. How would I be able to transpose this so that each country and year are on a single row like this?

Country Name    Country Code    Year    Population
Aruba           ABW             1995    80326
Aruba           ABW             1996    83195
Aruba           ABW             1997    85447
Aruba           ABW             1998    87276

And so on and so forth

Upvotes: 2

Views: 334

Answers (1)

miradulo
miradulo

Reputation: 29740

You could use pd.melt. 

pd.melt(df, id_vars=['Country Name', 'Country Code'], 
        var_name='Year', value_name='Population')

Or alternatively, could add the Country Name and Country Code to the index, stack, then reset the index

df = df.set_index(['Country Name', 'Country Code']).stack().reset_index()

but then you'll have to set the column names post-process. pd.melt is probably nicer for this, and is most likely faster as well.

Demo

>>> pd.melt(df, id_vars=['Country Name', 'Country Code'],
            var_name='Year', value_name='Population')

   Country Name Country Code  Year  Population
0         Aruba          ABW  1995       80326
1         Aruba          ABW  1996       83195
2         Aruba          ABW  1997       85447
3         Aruba          ABW  1998       87276
4         Aruba          ABW  1999       89004
5         Aruba          ABW  2000       90858
6         Aruba          ABW  2001       92894
7         Aruba          ABW  2002       94995
8         Aruba          ABW  2003       97015
9         Aruba          ABW  2004       98742
10        Aruba          ABW  2005      100031
11        Aruba          ABW  2006      100830
12        Aruba          ABW  2007      101218
13        Aruba          ABW  2008      101342
14        Aruba          ABW  2009      101416
15        Aruba          ABW  2010      101597
16        Aruba          ABW  2011      101936
17        Aruba          ABW  2012      102393
18        Aruba          ABW  2013      102921
19        Aruba          ABW  2014      103441
20        Aruba          ABW  2015      103889

Upvotes: 4

Related Questions