7

I want to unstack one column in my Pandas DataFrame. The DataFrame is indexed by the 'Date' and I want to unstack the 'Country' column so each Country is its own column. The current pandas DF looks like this:

             Country   Product      Flow Unit  Quantity  
Date                                                         
2002-01-31   FINLAND  KEROSENE  TOTEXPSB  KBD    3.8129     
2002-01-31    TURKEY  KEROSENE  TOTEXPSB  KBD    0.2542     
2002-01-31  AUSTRALI  KEROSENE  TOTEXPSB  KBD   12.2787     
2002-01-31    CANADA  KEROSENE  TOTEXPSB  KBD    5.1161     
2002-01-31        UK  KEROSENE  TOTEXPSB  KBD   12.2013

When I use df.pivot I get the following error "ReshapeError: Index contains duplicate entries, cannot reshape" This is true since I'm looking at a Dates that are reported at the same time by each country. What I would like is to unstack the 'Country Column so only one Date would show for each month.

the DataFrame headers like this Date would still be the index:

Date        FINLAND TURKEY  AUSTRALI  CANADA Flow      Unit

2002-01-31  3.8129  0.2542  12.2787   5.1161 TOTEXPSB   KBD

I have worked on this for a while and I'm not getting anywhere so any direction or insight would be great.

Also, note you are only seeing the head of the DataFrame so years of Data is in this format.

Thanks,

Douglas

user3055920
  • 776
  • 1
  • 9
  • 18

1 Answers1

4

If you can drop Product, Unit, and Flow then it should be as easy as

df.reset_index().pivot(columns='Country', index='Date', values='Quantity')

to give

Country  AUSTRALI    CANADA  FINLAND TURKEY  UK
Date                    
2002-01-31   12.2787     5.1161  3.8129  0.2542  12.2013
jmz
  • 4,138
  • 28
  • 27
  • Please allow me to add a little information. This DataFrame has already been reduced. So Flow column has only one unique string "TOTEXPSB" Also, this is the same for the Unit Column "KBD" is the only object in the column. So I can delete both columns. I see your thinking now and will delete those columns and see if I can get it to work. Thank you! Douglas – user3055920 Feb 04 '14 at 17:01
  • Thanks Douglas, I've updated my answer assuming you can also drop `Product`. If that's not the case let me know. – jmz Feb 04 '14 at 17:05
  • Thanks! That is a very clean solution. – user3055920 Feb 06 '14 at 19:55