Transpose csv structure using python pandas

Question

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.

Wouter Overmeire · Accepted Answer · 2013-04-16 09:36:40Z

6

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.

answered Apr 16, 2013 at 9:36

Wouter Overmeire

69.7k10 gold badges67 silver badges44 bronze badges

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

4 Comments

richie Over a year ago

tried that. But I get this; ,level_0,level_1,0 0,0,Country,AUS 1,0,1960,450.0 2,0,1961,567.0 3,0,1962,723.0 4,1,Country,NZ 5,1,1961,125.0 6,1,1962,320.0 7,2,Country,IND 8,2,1960,350.0

richie Over a year ago

Based on your answer, I tried df.transpose().stack().reset_index() and have come somewhere close to what I want

Wouter Overmeire Over a year ago

The output in my example, matches (except for NaN) exactly what you wanted to get. If you do not get the same result, this only means that you are extracting the data differently from the csv file, and your starting frame is different than mine.

richie Over a year ago

Oh yes. I should have done this 'data.set_index('Country').stack().reset_index()' And I'll have to do a 'data.set_index('Country').stack(dropna=False).reset_index()' to include the nans. Thanks!!!!

Collectives™ on Stack Overflow

Transpose csv structure using python pandas

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related