Pandas merge Country columns into single row

Question

[]

My current dataset looks like the picture above (First picture). I want it in a shape like this (Second picture):

country 1945 1950 1951 Afghanistan 0.01 0.08 0.09 Zimbabwe 0.5 0.6 0.7

So every data for a single country is in a single row.

Can someone help me achive this using Python and Pandas

Answer 1

try:

df = df.set_index(['Entity','Year'],append=True).unstack()

Answer 2

Looking at your data and the desired output, I see the following transformations:

• Set the country name as the index for each row
• Pivot the year into column and at the intersection of the index and year, select the value of Annual CO2 emissions

Fortunately, pandas.DataFrame has the pivot method which does both like so:

>>> df
        Entity  Year  CO2Emissions
0  Afghanistan  1945             1
1  Afghanistan  1950             2
2     Zimbabwe  1950             3
3     Zimbabwe  1955             4
>>> df.pivot('Entity', columns='Year', values='CO2Emissions')
Year         1945  1950  1955
Entity
Afghanistan   1.0   2.0   NaN
Zimbabwe      NaN   3.0   4.0