Python Pandas的双重索引

Question

Python Pandas的双重索引

4

我正在尝试使用Python中的Pandas将CSV转换为数据框，并修改它，以使其具有以下格式：

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

year是一个索引，对于第一批国家只有一个数据，对于第二批等等则有第二个数据。

我使用的数据来自于：

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

我的代码如下：

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()

它几乎完美地工作，但它给我一个这样的数据框：

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

如您所见，国家作为索引缺失，我似乎无法添加它。真的很感谢您的帮助。

我将添加一些链接中的数据:

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,..,..,..,..,..,..,..,..

- Antonio López Ruiz

从带有标题的 CSV 中切取一些数据，并添加到问题中，这样 Stack Overflow 的用户就不太可能下载 CSV 数据了。 - Merlin

好的，请稍等一下。 - Antonio López Ruiz

2个回答

0

上面来自unubtu的答案是正确的方法。不过，回答你最初的问题：

1）正确的语法是GS.set_index(['Year', 'Country'])而不是GS.set_index('Year', 'Country')，后者会将第二个位置参数设置为Country，这不是你想要的。

2）Country是数据框GS的索引。行GS.set_index(['Year', 'Country'])旨在使用数据框的列设置索引。因此，需要将Country作为列而不是索引。

另外，不要分配给GS.index.names。Pandas使用该属性来保存MultiIndex标签，并不鼓励直接访问。请改用Index方法set_names。

- Vamsi

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- unutbu · Accepted Answer

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')

该函数返回一个名为result的DataFrame，其中包含Year和Country Name的复合索引，以及Series Name的列索引。

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                                                      ..

代替

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'})

您可以使用正则表达式替换：

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

请注意，todo2具有列。

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

我们希望保留 系列名称、国家名称 作为列，并将年份标签放在新列中。这个操作可以通过pd.melt函数完成：

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

id_vars = ['Series Name', 'Country Name']指定要保留的列。所有其余列都会被“melted（融合）”。请注意，现在我们有一个Year列：

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           ..

我们快要完成了。现在使用set_index将 ['Series Name'，'Year'，'Country Name'] 列移动到索引中，然后unstack Series Name索引级别。

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

unstack操作将索引级别移动到列级别。因此，您最终得到的是来自Series Name的值作为列。