CODEWITHSUNDEEP
csvpython-2.7pandas
richie
richie

Reputation: 18638

Transpose csv structure using python pandas

I have the following csv structure:

'Country'    '1960'    '1961'    '1962'
AUS          450        567       723
NZ                      125       320
IND          350        375       395
SL        
PAK          100        115       218

Using Python Pandas how do I convert(transpose) the above structure to the following?

'Country'   'Year'   'Value'
AUS          1960     450
AUS          1961     567
AUS          1962     723
NZ           1960   
NZ           1961     125
...

My attempts at using pivot have been futile.

Upvotes: 2

Views: 741

Answers (1)

Wouter Overmeire
Wouter Overmeire

Reputation: 69106

In [19]: df
Out[19]:
year     1960  1961  1962
Country
AUS       450   567   723
NZ        NaN   125   320
IND       350   375   395
SL        NaN   NaN   NaN
PAK       100   115   218

In [20]: df.stack().reset_index()
Out[20]:
   Country  year    0
0      AUS  1960  450
1      AUS  1961  567
2      AUS  1962  723
3       NZ  1961  125
4       NZ  1962  320
5      IND  1960  350
6      IND  1961  375
7      IND  1962  395
8      PAK  1960  100
9      PAK  1961  115
10     PAK  1962  218

Apparently NaN are dropped along the way.

Upvotes: 6

Related Questions