1

I have a dataframe that looks like 

Country | IndicatorName | Value
Spain   | Indicator1    | 3
Spain   | Indicator2    | 4
Germany | Indicator16   | 24
......

And I want to convert it into a dataframe with IndicatorName columns, Country rows and Value intersections

Country | Indicator 1 | Indicator 2 | Indicator 3 | ......
Spain   |     3       |     4       |   16        | ......
Germany |     23      |     232     |   232       | ......
.......

I am trying through groupby(["IndicatorName","Value"]) but not sure how to proceed

import pandas as pd
indicators = pd.read_csv("Indicators.csv")
indicators.groupbby(["IndicatorName","Value"])
.....

Is there a proper way to deal with this or does it need to be done via iteration?

Arkaitz Jimenez
  • 22,500
  • 11
  • 75
  • 105

1 Answers1

1

I'm not sure of the inital df format as the desired df appears to have different values.

Is the below helpful?

df = pd.DataFrame({'Country' : ['Spain', 'Spain', 'Germany'],
                   'IndicatorName':['Indicator1', 'Indicator2', 'Indicator16'],
                  'Value':[3, 4, 24]
                  })


df.pivot(index = 'Country', columns='IndicatorName', values='Value').fillna(0)


IndicatorName   Indicator1  Indicator16     Indicator2
    Country             
    Germany            0.0        24.0              0.0
    Spain              3.0         0.0              4.0
Francisco
  • 492
  • 4
  • 19
  • No problem as long it's helpful :) – Francisco Dec 01 '18 at 15:26
  • @arkaitz Jimenez I can't comment yet on your post. This was relly helpfull for me on pivots https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe – Francisco Dec 01 '18 at 15:29
  • I am getting "ValueError: Index contains duplicate entries, cannot reshape" – Arkaitz Jimenez Dec 01 '18 at 15:54
  • Hi see above link question 1 answer - This occurs because pandas is attempting to reindex either a columns or index object with duplicate entries. There are varying methods to use that can perform a pivot. Some of them are not well suited to when there are duplicates of the keys in which it is being asked to pivot on. You migth require pivot_table() and some form of aggregation function – Francisco Dec 01 '18 at 15:59
  • Yes, I think this is exactly what I needed – Arkaitz Jimenez Dec 01 '18 at 17:05