55

I have a table in csv format that looks like this. I would like to transpose the table so that the values in the indicator name column are the new columns,

Indicator       Country         Year   Value    
1               Angola          2005    6
2               Angola          2005    13
3               Angola          2005    10
4               Angola          2005    11
5               Angola          2005    5
1               Angola          2006    3
2               Angola          2006    2
3               Angola          2006    7
4               Angola          2006    3
5               Angola          2006    6

I would like the end result to like like this:

Country    Year     1     2     3     4     5
Angola     2005     6     13    10    11    5
Angola     2006     3     2     7     3     6

I have tried using a pandas data frame with not much success.

print(df.pivot(columns = 'Country', 'Year', 'Indicator', values = 'Value'))

Any thoughts on how to accomplish this?

bjurstrs
  • 949
  • 2
  • 7
  • 17
  • @alfasin He could be showing us the tabular data. Regardless I'm *guessing* that if he's using a pandas dataframe that he must already have it parsed as a table somewhere. – Adam Smith Feb 05 '15 at 06:00
  • What does your dataframe look like currently? What does it do when you call `pivot` on it, that you're calling "not much success?" – Adam Smith Feb 05 '15 at 06:03
  • 2
    I've provided several detailed examples and alternative approaches in this [**Q&A**](https://stackoverflow.com/q/47152691/2336654) that you or others might find helpful. – piRSquared Nov 11 '17 at 22:24

2 Answers2

94

You can use pivot_table:

pd.pivot_table(df, values = 'Value', index=['Country','Year'], columns = 'Indicator').reset_index()

this outputs:

 Indicator  Country     Year    1   2   3   4   5
 0          Angola      2005    6   13  10  11  5
 1          Angola      2006    3   2   7   3   6
JAB
  • 12,401
  • 6
  • 45
  • 50
  • 4
    Looking at the very first line (column names) of the output - why is there an indicator column [left column('`Indicator`')] in addition to columns derived from the indicators column [right 5 columns (`1`, `2`, `3`, `4`, `5`)]? – 3kstc Mar 08 '18 at 23:43
  • It’s the index. – JAB Mar 09 '18 at 01:25
  • thanks - I tried doing [something similar](https://stackoverflow.com/questions/49185446/how-to-pivot-a-dataframe-with-pandas) but had no luck. – 3kstc Mar 09 '18 at 02:01
  • @JAB if the 'Indicator column on the left is the index, why is it still there after doing a `reset_index`? – thegreatcoder Mar 01 '23 at 19:40
9

This is a guess: it's not a ".csv" file, but a Pandas DataFrame imported from a '.csv'.

To pivot this table you want three arguments in your Pandas "pivot". e.g., if df is your dataframe: 

table = df.pivot(index='Country',columns='Year',values='Value')  
print (table)

This should should give the desired output.

Jason Sprong
  • 119
  • 1
  • 1