python - Pandas DataFrame from WB WDI data: combine year columns into "year" variable and merge rows -

i have dataset (.tsv file) following columns. (it's world bank's new wdi all-in all-time single-download dataset. nice!)

country countrycode varname 1960 1961 1962 afghanistan afg gdp 5.6 5.7 5.8 afghanistan afg gini .77 .78 .75 afghanistan afg educ 8.1 8.2 8.3 afghanistan afg pop 888 889 890 albania alb gdp 6.6 6.7 6.8 albania alb gini .45 .46 .47 albania alb educ 6.2 6.3 6.4 albania alb pop 777 778 779 

i need pandas dataframe ['gdp','gini','edu','pop'] columns, along ['country', 'countrycode', 'year']. values "year" columns! , i'd there 1 row each country-year combination.

for instance, columns , first row be

country countrycode year gdp gini educ pop afghanistan afg 1960 5.6 .77 8.1 888 

this seems complex pivot or opposite-of-"melt", cannot figure out.

in [59]: df out[59]:        country countrycode varname    1960    1961    1962 0  afghanistan         afg     gdp    5.60    5.70    5.80 1  afghanistan         afg    gini    0.77    0.78    0.75 2  afghanistan         afg    educ    8.10    8.20    8.30 3  afghanistan         afg     pop  888.00  889.00  890.00 4      albania         alb     gdp    6.60    6.70    6.80 5      albania         alb    gini    0.45    0.46    0.47 6      albania         alb    educ    6.20    6.30    6.40 7      albania         alb     pop  777.00  778.00  779.00  in [60]: df = df.set_index(['country', 'countrycode', 'varname'])  in [61]: df.columns.name = 'year'  in [62]: df.stack().unstack('varname') out[62]: varname                       gdp  gini  educ  pop country     countrycode year afghanistan afg         1960  5.6  0.77   8.1  888                         1961  5.7  0.78   8.2  889                         1962  5.8  0.75   8.3  890 albania     alb         1960  6.6  0.45   6.2  777                         1961  6.7  0.46   6.3  778                         1962  6.8  0.47   6.4  779 

the latter frame mutliindex, can reset_index move multiindex regular columns.