Pivot Rows in Pandas Dataframe

Question

My dataframe currently looks like:

ID               FIELD        VALUE
12463634          TEST         22.2 
12463634      E_REASON          010
12463634      IN_SCOPE            Y
12463635          TEST         99.5 
12463635      E_REASON          020
12463635      IN_SCOPE            N

I'd like my dataframe to look like:

ID           TEST     E_REASON     IN_SCOPE
12463634     22.2          010            Y
12463635     99.5          020            N

I have tried running this code:

df.pivot_table(index = "ID", columns = "FIELD", values = "VALUE")

However, I am seeing this error:

DataError: No numeric types to aggregate

Please advise. Thanks!

I think possibly what you may want is `pivot` rather than `pivot_table`. — Ajean, Aug 10 '17 at 16:03
Only if they make non-unique indices. I just tried it and pivot works fine. — Ajean, Aug 10 '17 at 16:05

jezrael · Accepted Answer · 2017-08-10T16:16:23.707

Use pivot or unstack:

df = df.pivot(index = "ID", columns = "FIELD", values = "VALUE")
print (df)
FIELD    E_REASON IN_SCOPE  TEST
ID                              
12463634      010        Y  22.2
12463635      020        N  99.5

df = df.set_index(['ID', 'FIELD'])['VALUE'].unstack()
print (df)
FIELD    E_REASON IN_SCOPE  TEST
ID                              
12463634      010        Y  22.2
12463635      020        N  99.5

If duplicates need pivot_table with some aggregate function - sum or ','join:

print (df)
         ID     FIELD VALUE
0  12463634      TEST  22.2
1  12463634  E_REASON   010
2  12463634  IN_SCOPE     Y<-same ID and FIELED
3  12463634  IN_SCOPE    Y1<-same ID and FIELED
4  12463635      TEST  99.5
5  12463635  E_REASON   020
6  12463635  IN_SCOPE     N


df = df.pivot_table(index = "ID", columns = "FIELD", values = "VALUE", aggfunc='sum')
print (df)
FIELD    E_REASON IN_SCOPE  TEST
ID                              
12463634      010      YY1  22.2
12463635      020        N  99.5

Or:

df = df.pivot_table(index = "ID", columns = "FIELD", values = "VALUE", aggfunc=','.join)
print (df)
FIELD    E_REASON IN_SCOPE  TEST
ID                              
12463634      010     Y,Y1  22.2
12463635      020        N  99.5

pivot worked just fine - thanks! – kjmerf Aug 10 '17 at 16:16 — kjmerf, Aug 10 '17 at 16:16

score 0 · Answer 2 · answered Aug 10 '17 at 16:05

An alternative option is to do something like:

df.groupby(['ID', 'FIELD']).sum().unstack()

This way you don't lose the label 'VALUES'

Explanation

You can groupby and sum() which will give you

In [31]: df.groupby(['ID', 'FIELD']).sum()
Out[31]:
                  VALUE
ID       FIELD
12463634 E_REASON   010
         IN_SCOPE     Y
         TEST      22.2
12463635 E_REASON   020
         IN_SCOPE     N
         TEST      99.5

Then the unstack moves the last indexed row to a column

Pivot Rows in Pandas Dataframe

2 Answers2

Explanation