4

I am currently trying to change a csv into a dataframe with pandas from python and modify it in order for it to have a format like the following:

Year    Country        Serie1           Serie2        ...
        Afganistan     Something        Something     Something
1970    Columbia       Sth              Sth           Sth 
        Zimbabwue      S                S             S

Where year is an index that has only one data for the first batch of countries, a second one for the second batch etc.

The data I have is from:

https://github.com/antonio1695/Python/blob/master/CMU/GS.csv

And the code I did was:

todo2=pd.read_csv("GS.csv")
todo2 = todo2.rename(columns = {'1970 [YR1970]':'1970'})
todo2 = todo2.rename(columns = {'1980 [YR1980]':'1980'})
todo2 = todo2.rename(columns = {'1990 [YR1990]':'1990'})
todo2 = todo2.rename(columns = {'1995 [YR1995]':'1995'})
todo2 = todo2.rename(columns = {'2000 [YR2000]':'2000'})
todo2 = todo2.rename(columns = {'2005 [YR2005]':'2005'})
todo2 = todo2.rename(columns = {'2010 [YR2010]':'2010'})
todo2 = todo2.rename(columns = {'2015 [YR2015]':'2015'})
GS1970 = todo2.pivot(index = 'Country Name',columns = 'Series Name', values = '1970') # Pivotear como 
GS1980 = todo2.pivot(index = 'Country Name',columns = 'Series Name', values = '1980') # lo queriamos.
GS1990 = todo2.pivot(index = 'Country Name',columns = 'Series Name', values = '1990')
GS1995 = todo2.pivot(index = 'Country Name',columns = 'Series Name', values = '1995')
GS2000 = todo2.pivot(index = 'Country Name',columns = 'Series Name', values = '2000')
GS2005 = todo2.pivot(index = 'Country Name',columns = 'Series Name', values = '2005')
GS2010 = todo2.pivot(index = 'Country Name',columns = 'Series Name', values = '2010')
GS2015 = todo2.pivot(index = 'Country Name',columns = 'Series Name', values = '2015')
GS1970['Year']=1970
GS1980['Year']=1980
GS1990['Year']=1990
GS1995['Year']=1995
GS2000['Year']=2000
GS2005['Year']=2005
GS2010['Year']=2010
GS2015['Year']=2015

GS = GS1970.append(GS1980).append(GS1990).append(GS1995).append(GS2000).append(GS2005).append(GS2010).append(GS2015)

GS.index.names=['Country']
GS=GS.set_index('Year','Country').sort_index()

It works almost perfectly, but it gives me a dataframe like this:

    Year      Serie1           Serie2        ...
              Something        Something     Something
    1970      Sth              Sth           Sth 
              S                S             S

As you can see, Country is missing as an index and I cant seem to be able to add it. I'd really appreciate your help.

I'll add some of the data that is in the link:

Country,Country Code,Series,Series Code,1970 [YR1970],1980 [YR1980],1990 [YR1990],1995 [YR1995],2000 [YR2000],2005 [YR2005],2010 [YR2010],2015 [YR2015]
Afghanistan,AFG,Annual statutory teacher salaries in public institutions in USD. Upper Secondary. Starting salary,OECD.TSAL.3.E0,..,..,..,..,..,..,..,..
Afghanistan,AFG,"Adult literacy rate, population 15+ years, female (%)",SE.ADT.LITR.FE.ZS,..,..,..,..,..,..,..,24.15337
Afghanistan,AFG,"Adult literacy rate, population 15+ years, both sexes (%)",SE.ADT.LITR.ZS,..,..,..,..,..,..,..,38.15612
Afghanistan,AFG,"Adult literacy rate, population 15+ years, gender parity index (GPI)",UIS.LR.AG15T99.GPI,..,..,..,..,..,..,..,0.46456
Afghanistan,AFG,"Adult literacy rate, population 15+ years, male (%)",SE.ADT.LITR.MA.ZS,..,..,..,..,..,..,..,51.99192
Afghanistan,AFG,Annual statutory teacher salaries in public institutions in USD. Pre-Primary. Starting salary,OECD.TSAL.0.E0,..,..,..,..,..,..,..,..
Afghanistan,AFG,Annual statutory teacher salaries in public institutions in USD. Primary. Starting salary,OECD.TSAL.1.E0,..,..,..,..,..,..,..,..
Afghanistan,AFG,Duration of compulsory education (years),SE.COM.DURS,..,..,..,..,6,..,9,..
Afghanistan,AFG,"Early school leavers from primary education, both sexes (number)",UIS.ESL.1.T,..,..,..,..,..,..,..,..
Afghanistan,AFG,"Early school leavers from primary education, female (number)",UIS.ESL.1.F,..,..,..,..,..,..,..,..
Afghanistan,AFG,"Early school leavers from primary education, male (number)",UIS.ESL.1.M,..,..,..,..,..,..,..,..
Afghanistan,AFG,"Effective transition rate from primary to lower secondary general education, both sexes (%)",SE.SEC.PROG.ZS,..,83.90343,..,..,..,..,..,..
Afghanistan,AFG,"Effective transition rate from primary to lower secondary general education, female (%)",SE.SEC.PROG.FE.ZS,..,79.29369,..,..,..,..,..,..
Afghanistan,AFG,"Effective transition rate from primary to lower secondary general education, male (%)",SE.SEC.PROG.MA.ZS,..,84.8347,..,..,..,..,..,..
Afghanistan,AFG,"Enrolment in lower secondary education, private institutions, both sexes (number)",UIS.E.2.PR,..,..,..,..,..,..,..,..
Afghanistan,AFG,"Enrolment in lower secondary education, private institutions, female (number)",UIS.E.2.PR.F,..,..,..,..,..,..,..,..
Afghanistan,AFG,"Enrolment in lower secondary education, public institutions, female (number)",UIS.E.2.PU.F,..,..,..,..,..,..,..,..
Afghanistan,AFG,GDP per capita (current US$),NY.GDP.PCAP.CD,157.2584608,275.6498281,..,..,..,257.1757947,569.9407288,..
Afghanistan,AFG,Government expenditure in educational institutions as % of GDP (%),UIS.XGDP.FSGOV.FDINSTADM.FFD,..,..,..,..,..,..,..,..
Afghanistan,AFG,Government expenditure in post-secondary non-tertiary institutions as % of GDP (%),UIS.XGDP.4.FSGOV.FDINSTADM.FFD,..,..,..,..,..,..,..,..
Afghanistan,AFG,Government expenditure in pre-primary institutions as % of GDP (%),UIS.XGDP.0.FSGOV.FDINSTADM.FFD,..,..,..,..,..,..,..,..
Afghanistan,AFG,Government expenditure in primary institutions as % of GDP (%),UIS.XGDP.1.FSGOV.FDINSTADM.FFD,..,..,..,..,..,..,..,..
Afghanistan,AFG,Government expenditure in secondary institutions education as % of GDP (%),XGDP.23.FSGOV.FDINSTADM.FFD,..,..,..,..,..,..,..,..
Afghanistan,AFG,"Gross enrolment ratio, post-secondary non-tertiary, both sexes (%)",UIS.GER.4,..,..,..,..,..,..,4.89862,..
Afghanistan,AFG,"Gross enrolment ratio, post-secondary non-tertiary, female (%)",UIS.GER.4.F,..,..,..,..,..,..,3.08739,..
Afghanistan,AFG,"Gross enrolment ratio, post-secondary non-tertiary, male (%)",UIS.GER.4.M,..,..,..,..,..,..,6.59065,..
Afghanistan,AFG,Labor force with tertiary education (% of total),SL.TLF.TERT.ZS,..,..,..,..,..,..,..,..
Afghanistan,AFG,"Labor force with secondary education, male (% of male labor force)",SL.TLF.SECO.MA.ZS,..,..,..,..,..,..,..,..
Afghanistan,AFG,"Labor force with secondary education, female (% of female labor force)",SL.TLF.SECO.FE.ZS,..,..,..,..,..,..,..,..
Afghanistan,AFG,Labor force with secondary education (% of total),SL.TLF.SECO.ZS,..,..,..,..,..,..,..,..
Afghanistan,AFG,"Labor force with primary education, female (% of female labor force)",SL.TLF.PRIM.FE.ZS,..,..,..,..,..,..,..,..
Afghanistan,AFG,Labor force with primary education (% of total),SL.TLF.PRIM.ZS,..,..,..,..,..,..,..,..
Afghanistan,AFG,"Labor force with primary education, male (% of male labor force)",SL.TLF.PRIM.MA.ZS,..,..,..,..,..,..,..,..
Afghanistan,AFG,Internet users (per 100 people),IT.NET.USER.P2,..,..,0,..,..,1.224148084,4,..
Afghanistan,AFG,"Labor force with tertiary education, female (% of female labor force)",SL.TLF.TERT.FE.ZS,..,..,..,..,..,..,..,..
Afghanistan,AFG,"Labor force with tertiary education, male (% of male labor force)",SL.TLF.TERT.MA.ZS,..,..,..,..,..,..,..,..
Afghanistan,AFG,"Labor force, female (% of total labor force)",SL.TLF.TOTL.FE.ZS,..,..,15.44573728,14.43635836,13.62850388,13.9706651,15.53812459,..
Afghanistan,AFG,"Labor force, total",SL.TLF.TOTL.IN,..,..,3083498,4246528,4823765,6112379,7049139,..
Afghanistan,AFG,"Lower secondary completion rate, both sexes (%)",SE.SEC.CMPT.LO.ZS,..,13.64288,..,..,..,17.04929,..,..
Afghanistan,AFG,"Lower secondary completion rate, female (%)",SE.SEC.CMPT.LO.FE.ZS,..,5.88732,..,..,..,9.49475,..,..
Afghanistan,AFG,"Lower secondary completion rate, male (%)",SE.SEC.CMPT.LO.MA.ZS,..,21.05249,..,..,..,24.13043,..,..
Albania,ALB,Annual statutory teacher salaries in public institutions in USD. Upper Secondary. Starting salary,OECD.TSAL.3.E0,..,..,..,..,..,..,..,..
Albania,ALB,"Adult literacy rate, population 15+ years, female (%)",SE.ADT.LITR.FE.ZS,..,..,..,..,..,..,..,96.88302
Albania,ALB,"Adult literacy rate, population 15+ years, both sexes (%)",SE.ADT.LITR.ZS,..,..,..,..,..,..,..,97.62414
Albania,ALB,"Adult literacy rate, population 15+ years, gender parity index (GPI)",UIS.LR.AG15T99.GPI,..,..,..,..,..,..,..,0.98478
Albania,ALB,"Adult literacy rate, population 15+ years, male (%)",SE.ADT.LITR.MA.ZS,..,..,..,..,..,..,..,98.38066
Albania,ALB,Annual statutory teacher salaries in public institutions in USD. Pre-Primary. Starting salary,OECD.TSAL.0.E0,..,..,..,..,..,..,..,..
Albania,ALB,Annual statutory teacher salaries in public institutions in USD. Primary. Starting salary,OECD.TSAL.1.E0,..,..,..,..,..,..,..,..
Albania,ALB,Duration of compulsory education (years),SE.COM.DURS,..,..,..,..,8,8,8,..
Albania,ALB,"Early school leavers from primary education, both sexes (number)",UIS.ESL.1.T,..,..,..,..,..,..,2005.99341,..
Albania,ALB,"Early school leavers from primary education, female (number)",UIS.ESL.1.F,..,..,..,..,..,..,916.50116,..
Albania,ALB,"Early school leavers from primary education, male (number)",UIS.ESL.1.M,..,..,..,..,..,..,1089.49231,..
Albania,ALB,"Effective transition rate from primary to lower secondary general education, both sexes (%)",SE.SEC.PROG.ZS,..,..,..,..,97.24541,..,99.18615,..
Albania,ALB,"Effective transition rate from primary to lower secondary general education, female (%)",SE.SEC.PROG.FE.ZS,..,..,..,..,97.59525,..,99.27686,..
Albania,ALB,"Effective transition rate from primary to lower secondary general education, male (%)",SE.SEC.PROG.MA.ZS,..,..,..,..,96.90948,..,99.10345,..
Albania,ALB,"Enrolment in lower secondary education, private institutions, both sexes (number)",UIS.E.2.PR,..,..,..,..,..,6464,9569,..
Albania,ALB,"Enrolment in lower secondary education, private institutions, female (number)",UIS.E.2.PR.F,..,..,..,..,..,3311,4499,..
Albania,ALB,"Enrolment in lower secondary education, public institutions, female (number)",UIS.E.2.PU.F,..,..,..,..,126583,118498,99726,..
Albania,ALB,GDP per capita (current US$),NY.GDP.PCAP.CD,..,..,639.4638993,760.5593758,1175.788981,2709.142931,4094.358832,..
Albania,ALB,Government expenditure in educational institutions as % of GDP (%),UIS.XGDP.FSGOV.FDINSTADM.FFD,..,..,..,..,..,..,..,..
Albania,ALB,Government expenditure in post-secondary non-tertiary institutions as % of GDP (%),UIS.XGDP.4.FSGOV.FDINSTADM.FFD,..,..,..,..,..,..,..,..
Albania,ALB,Government expenditure in pre-primary institutions as % of GDP (%),UIS.XGDP.0.FSGOV.FDINSTADM.FFD,..,..,..,..,..,..,..,..
Albania,ALB,Government expenditure in primary institutions as % of GDP (%),UIS.XGDP.1.FSGOV.FDINSTADM.FFD,..,..,..,..,..,..,..,..
Albania,ALB,Government expenditure in secondary institutions education as % of GDP (%),XGDP.23.FSGOV.FDINSTADM.FFD,..,..,..,..,..,..,..,..
Albania,ALB,"Gross enrolment ratio, post-secondary non-tertiary, both sexes (%)",UIS.GER.4,..,..,..,..,..,..,..,..
Albania,ALB,"Gross enrolment ratio, post-secondary non-tertiary, female (%)",UIS.GER.4.F,..,..,..,..,..,..,..,..
Albania,ALB,"Gross enrolment ratio, post-secondary non-tertiary, male (%)",UIS.GER.4.M,..,..,..,..,..,..,..,..
Albania,ALB,Labor force with tertiary education (% of total),SL.TLF.TERT.ZS,..,..,..,..,..,..,..,..
Albania,ALB,"Labor force with secondary education, male (% of male labor force)",SL.TLF.SECO.MA.ZS,..,..,..,..,..,..,..,..
Albania,ALB,"Labor force with secondary education, female (% of female labor force)",SL.TLF.SECO.FE.ZS,..,..,..,..,..,..,..,..
Albania,ALB,Labor force with secondary education (% of total),SL.TLF.SECO.ZS,..,..,..,..,..,..,..,..
Albania,ALB,"Labor force with primary education, female (% of female labor force)",SL.TLF.PRIM.FE.ZS,..,..,..,..,..,..,..,..
Albania,ALB,Labor force with primary education (% of total),SL.TLF.PRIM.ZS,..,..,..,..,..,..,..,..
Albania,ALB,"Labor force with primary education, male (% of male labor force)",SL.TLF.PRIM.MA.ZS,..,..,..,..,..,..,..,..
Albania,ALB,Internet users (per 100 people),IT.NET.USER.P2,..,..,0,0.011168695,0.114097347,6.043890864,45,..
Albania,ALB,"Labor force with tertiary education, female (% of female labor force)",SL.TLF.TERT.FE.ZS,..,..,..,..,..,..,..,..
Albania,ALB,"Labor force with tertiary education, male (% of male labor force)",SL.TLF.TERT.MA.ZS,..,..,..,..,..,..,..,..
Albania,ALB,"Labor force, female (% of total labor force)",SL.TLF.TOTL.FE.ZS,..,..,40.56193622,42.93937258,41.15738229,41.41250834,40.91694803,..
Albania,ALB,"Labor force, total",SL.TLF.TOTL.IN,..,..,1420090,1376110,1339903,1300764,1263474,..
Albania,ALB,"Lower secondary completion rate, both sexes (%)",SE.SEC.CMPT.LO.ZS,..,..,..,..,82.06566,..,94.48979,..
Albania,ALB,"Lower secondary completion rate, female (%)",SE.SEC.CMPT.LO.FE.ZS,..,..,..,..,81.63975,..,96.4149,..
Albania,ALB,"Lower secondary completion rate, male (%)",SE.SEC.CMPT.LO.MA.ZS,..,..,..,..,82.48391,..,92.71123,..
Algeria,DZA,Annual statutory teacher salaries in public institutions in USD. Upper Secondary. Starting salary,OECD.TSAL.3.E0,..,..,..,..,..,..,..,..
Algeria,DZA,"Adult literacy rate, population 15+ years, female (%)",SE.ADT.LITR.FE.ZS,..,..,..,..,..,..,..,73.13369
Algeria,DZA,"Adult literacy rate, population 15+ years, both sexes (%)",SE.ADT.LITR.ZS,..,..,..,..,..,..,..,80.19891
Algeria,DZA,"Adult literacy rate, population 15+ years, gender parity index (GPI)",UIS.LR.AG15T99.GPI,..,..,..,..,..,..,..,0.83895
Algeria,DZA,"Adult literacy rate, population 15+ years, male (%)",SE.ADT.LITR.MA.ZS,..,..,..,..,..,..,..,87.17262
Algeria,DZA,Annual statutory teacher salaries in public institutions in USD. Pre-Primary. Starting salary,OECD.TSAL.0.E0,..,..,..,..,..,..,..,..
Algeria,DZA,Annual statutory teacher salaries in public institutions in USD. Primary. Starting salary,OECD.TSAL.1.E0,..,..,..,..,..,..,..,..
Algeria,DZA,Duration of compulsory education (years),SE.COM.DURS,..,..,..,..,10,10,10,..
Algeria,DZA,"Early school leavers from primary education, both sexes (number)",UIS.ESL.1.T,..,196992.3281,161233,..,61373.84375,40614.15234,32862.78906,..
Algeria,DZA,"Early school leavers from primary education, female (number)",UIS.ESL.1.F,..,97245.28906,88311.75,..,23733.01367,14010.44629,9263.79688,..
Algeria,DZA,"Early school leavers from primary education, male (number)",UIS.ESL.1.M,..,99747.04688,72921.25,..,37640.82813,26603.70703,23598.99219,..
Algeria,DZA,"Effective transition rate from primary to lower secondary general education, both sexes (%)",SE.SEC.PROG.ZS,..,61.98958,88.89488,87.03647,93.25744,92.31375,98.57422,..
Algeria,DZA,"Effective transition rate from primary to lower secondary general education, female (%)",SE.SEC.PROG.FE.ZS,..,62.18438,86.92801,85.00355,94.1204,93.12243,97.06229,..
Algeria,DZA,"Effective transition rate from primary to lower secondary general education, male (%)",SE.SEC.PROG.MA.ZS,..,61.85777,90.43619,88.79378,92.46102,91.5552,100,..
Algeria,DZA,"Enrolment in lower secondary education, private institutions, both sexes (number)",UIS.E.2.PR,..,..,..,..,..,..,2332,..
Algeria,DZA,"Enrolment in lower secondary education, private institutions, female (number)",UIS.E.2.PR.F,..,..,..,..,..,..,1721,..
Algeria,DZA,"Enrolment in lower secondary education, public institutions, female (number)",UIS.E.2.PU.F,..,..,..,..,962680,1159670,1548517,..
Algeria,DZA,GDP per capita (current US$),NY.GDP.PCAP.CD,334.2595552,2189.775719,2394.420608,1444.907936,1757.011974,3102.037384,4473.486446,..
Algeria,DZA,Government expenditure in educational institutions as % of GDP (%),UIS.XGDP.FSGOV.FDINSTADM.FFD,..,..,..,..,..,..,..,..
Algeria,DZA,Government expenditure in post-secondary non-tertiary institutions as % of GDP (%),UIS.XGDP.4.FSGOV.FDINSTADM.FFD,..,..,..,..,..,..,..,..
Algeria,DZA,Government expenditure in pre-primary institutions as % of GDP (%),UIS.XGDP.0.FSGOV.FDINSTADM.FFD,..,..,..,..,..,..,..,..
Algeria,DZA,Government expenditure in primary institutions as % of GDP (%),UIS.XGDP.1.FSGOV.FDINSTADM.FFD,..,..,..,..,1.54447,..,..,..
Algeria,DZA,Government expenditure in secondary institutions education as % of GDP (%),XGDP.23.FSGOV.FDINSTADM.FFD,..,..,..,..,1.67496,..,..,..
Algeria,DZA,"Gross enrolment ratio, post-secondary non-tertiary, both sexes (%)",UIS.GER.4,..,..,..,..,..,..,..,..
Algeria,DZA,"Gross enrolment ratio, post-secondary non-tertiary, female (%)",UIS.GER.4.F,..,..,..,..,..,..,..,..
Algeria,DZA,"Gross enrolment ratio, post-secondary non-tertiary, male (%)",UIS.GER.4.M,..,..,..,..,..,..,..,..
Algeria,DZA,Labor force with tertiary education (% of total),SL.TLF.TERT.ZS,..,..,..,..,..,..,..,..
Algeria,DZA,"Labor force with secondary education, male (% of male labor force)",SL.TLF.SECO.MA.ZS,..,..,..,..,..,..,..,..
Algeria,DZA,"Labor force with secondary education, female (% of female labor force)",SL.TLF.SECO.FE.ZS,..,..,..,..,..,..,..,..
Algeria,DZA,Labor force with secondary education (% of total),SL.TLF.SECO.ZS,..,..,..,..,..,..,..,..
Algeria,DZA,"Labor force with primary education, female (% of female labor force)",SL.TLF.PRIM.FE.ZS,..,..,..,..,..,..,..,..
Algeria,DZA,Labor force with primary education (% of total),SL.TLF.PRIM.ZS,..,..,..,..,..,..,..,..
Algeria,DZA,"Labor force with primary education, male (% of male labor force)",SL.TLF.PRIM.MA.ZS,..,..,..,..,..,..,..,..
Algeria,DZA,Internet users (per 100 people),IT.NET.USER.P2,..,..,0,0.001768954,0.491705679,5.843942092,12.5,..
Algeria,DZA,"Labor force with tertiary education, female (% of female labor force)",SL.TLF.TERT.FE.ZS,..,..,..,..,..,..,..,..
Algeria,DZA,"Labor force with tertiary education, male (% of male labor force)",SL.TLF.TERT.MA.ZS,..,..,..,..,..,..,..,..
Algeria,DZA,"Labor force, female (% of total labor force)",SL.TLF.TOTL.FE.ZS,..,..,11.63240408,12.79140636,13.48421486,14.93502388,16.8776628,..
Algeria,DZA,"Labor force, total",SL.TLF.TOTL.IN,..,..,6210711,7773938,8956873,10122642,11308343,..
Algeria,DZA,"Lower secondary completion rate, both sexes (%)",SE.SEC.CMPT.LO.ZS,..,30.99068,57.86388,57.14134,51.51907,72.37749,58.44122,..
Algeria,DZA,"Lower secondary completion rate, female (%)",SE.SEC.CMPT.LO.FE.ZS,..,23.78618,50.54142,55.18334,56.52461,77.84766,66.43117,..
Algeria,DZA,"Lower secondary completion rate, male (%)",SE.SEC.CMPT.LO.MA.ZS,..,37.86296,64.89034,59.02348,46.7084,67.13218,50.74376,..
American Samoa,ASM,Annual statutory teacher salaries in public institutions in USD. Upper Secondary. Starting salary,OECD.TSAL.3.E0,..,..,..,..,..,..,..,..
American Samoa,ASM,"Adult literacy rate, population 15+ years, female (%)",SE.ADT.LITR.FE.ZS,..,97.21991,..,..,..,..,..,..
American Samoa,ASM,"Adult literacy rate, population 15+ years, both sexes (%)",SE.ADT.LITR.ZS,..,97.34416,..,..,..,..,..,..
American Samoa,ASM,"Adult literacy rate, population 15+ years, gender parity index (GPI)",UIS.LR.AG15T99.GPI,..,0.99743,..,..,..,..,..,..
American Samoa,ASM,"Adult literacy rate, population 15+ years, male (%)",SE.ADT.LITR.MA.ZS,..,97.46996,..,..,..,..,..,..
American Samoa,ASM,Annual statutory teacher salaries in public institutions in USD. Pre-Primary. Starting salary,OECD.TSAL.0.E0,..,..,..,..,..,..,..,..
American Samoa,ASM,Annual statutory teacher salaries in public institutions in USD. Primary. Starting salary,OECD.TSAL.1.E0,..,..,..,..,..,..,..,..
American Samoa,ASM,Duration of compulsory education (years),SE.COM.DURS,..,..,..,..,..,..,..,..
American Samoa,ASM,"Early school leavers from primary education, both sexes (number)",UIS.ESL.1.T,..,..,..,..,..,..,..,..
American Samoa,ASM,"Early school leavers from primary education, female (number)",UIS.ESL.1.F,..,..,..,..,..,..,..,..
American Samoa,ASM,"Early school leavers from primary education, male (number)",UIS.ESL.1.M,..,..,..,..,..,..,..,..
American Samoa,ASM,"Effective transition rate from primary to lower secondary general education, both sexes (%)",SE.SEC.PROG.ZS,..,..,..,..,..,..,..,..
American Samoa,ASM,"Effective transition rate from primary to lower secondary general education, female (%)",SE.SEC.PROG.FE.ZS,..,..,..,..,..,..,..,..
American Samoa,ASM,"Effective transition rate from primary to lower secondary general education, male (%)",SE.SEC.PROG.MA.ZS,..,..,..,..,..,..,..,..
American Samoa,ASM,"Enrolment in lower secondary education, private institutions, both sexes (number)",UIS.E.2.PR,..,..,..,..,..,..,..,..
American Samoa,ASM,"Enrolment in lower secondary education, private institutions, female (number)",UIS.E.2.PR.F,..,..,..,..,..,..,..,..
American Samoa,ASM,"Enrolment in lower secondary education, public institutions, female (number)",UIS.E.2.PU.F,..,..,..,..,..,..,..,..
Improve this question
2
  • Slice some data from csv with header and add to question.SO folks are not likely to download csv data. Commented Jul 14, 2016 at 2:24
  • Ok, give me a sec. Commented Jul 14, 2016 at 2:25

2 Answers 2

Reset to default
2 
import pandas as pd
import re

todo2 = pd.read_csv("GS.csv")
todo2.columns = [re.sub(r'(\d+) \[YR\1\]', r'\1', col) for col in todo2.columns]
melted = pd.melt(todo2, id_vars=['Series Name', 'Country Name'], var_name='Year')
result = melted.set_index(['Series Name', 'Year', 'Country Name'])['value'].unstack('Series Name')

yields a DataFrame, result, with a MultiIndex of Years and Country Names, and a column index of Series Names.

In [53]: result.iloc[:3,:2].head()
Out[53]: 
Series Name       Age dependency ratio (% of working-age population)  \
Year Country Name                                                      
1970 Afghanistan                                         88.12683234   
     Albania                                             85.54006984   
     Algeria                                              101.510271   

Series Name       Average number of hours spent on unpaid domestic work (housework and child care), female  
Year Country Name                                                                                           
1970 Afghanistan                                                  ..                                        
     Albania                                                      ..                                        
     Algeria                                                      ..

Instead of 

todo2 = todo2.rename(columns = {'1970 [YR1970]':'1970'})
todo2 = todo2.rename(columns = {'1980 [YR1980]':'1980'})
todo2 = todo2.rename(columns = {'1990 [YR1990]':'1990'})
todo2 = todo2.rename(columns = {'1995 [YR1995]':'1995'})
todo2 = todo2.rename(columns = {'2000 [YR2000]':'2000'})
todo2 = todo2.rename(columns = {'2005 [YR2005]':'2005'})
todo2 = todo2.rename(columns = {'2010 [YR2010]':'2010'})
todo2 = todo2.rename(columns = {'2015 [YR2015]':'2015'})

you could use regex substitution:

todo2.columns = [re.sub(r'(\d+) \[YR\1\]', r'\1', col) for col in todo2.columns]

Notice that todo2 has columns

In [50]: todo2.columns
Out[50]: 
Index(['Series Name', 'Country Name', '1970', '1975', '1980', '1985', '1990',
       '1995', '2000', '2005', '2010', '2015'],
      dtype='object')

We want to preserve Series Name, Country Name as columns and place the year labels in a new column. This operation can be done by pd.melt:

melted = pd.melt(todo2, id_vars=['Series Name', 'Country Name'], var_name='Year')

id_vars=['Series Name', 'Country Name'] specifies the columns to be preserved. All the remaining columns are "melted". Notice we now have a Year column:

In [51]: melted.head()
Out[51]: 
                                         Series Name    Country Name  Year  \
0  Age dependency ratio (% of working-age populat...     Afghanistan  1970   
1  Age dependency ratio (% of working-age populat...         Albania  1970   
2  Age dependency ratio (% of working-age populat...         Algeria  1970   
3  Age dependency ratio (% of working-age populat...  American Samoa  1970   
4  Age dependency ratio (% of working-age populat...         Andorra  1970   

         value  
0  88.12683234  
1  85.54006984  
2   101.510271  
3           ..  
4           ..

We're almost done. Now use set_index to move ['Series Name', 'Year', 'Country Name'] columns into the index, and then unstack the Series Name index level. 

result = melted.set_index(['Series Name', 'Year', 'Country Name'])['value'].unstack('Series Name')

The unstack operation moves the index level to a column level. Thus, you end up with columns which are values from Series Name.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Worked perfectly, except I have data that is like this: SL.TLF.TOTL.FE.ZS
0

The above answer from unubtu has the correct way of doing this. However, answering your original question

1) The correct syntax is GS.set_index(['Year', 'Country']) rather than GS.set_index('Year', 'Country') which sets the second positional argument to Country. Not what you want.

2) Country is the index of the data frame GS. The line GS.set_index(['Year', 'Country']) is meant to set the index with columns of the data frame. So, one would require Country to be a column rather than an index.

Also, do not assign to GS.index.names. Pandas uses that attribute for holding MultiIndex labels and discourages direct access. Use the Index method set_names instead.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.