CODEWITHSUNDEEP
pythonpandasdataframe
aabujamra
aabujamra

Reputation: 4636

Python / Pandas - Transposing part of a dataframe and grouping keys

I have this dataframe:

b_id        conta      valor
302          12.3       123
303          12.3       323
302          11.4       674
...

I'm having quite a hard time trying to make it look like this:

b_id    12.3     11.4 ...
 302     123      674
 303     323      NaN
 ...

Already tried different merges, concats and joins but I haven't been successful, nothing appears to make it look like this. Could someone help?

Upvotes: 1

Views: 481

Answers (1)

jezrael
jezrael

Reputation: 862661

You need use pivot or set_index + unstack for reshape:

df = df.pivot(index='b_id', columns='conta', values='valor')
print (df)
conta   11.4   12.3
b_id               
302    674.0  123.0
303      NaN  323.0

df = df.set_index(['b_id', 'conta'])['valor'].unstack()
print (df)
conta   11.4   12.3
b_id               
302    674.0  123.0
303      NaN  323.0

If duplicates in columns created new index and columns:

print (df)
   b_id  conta  valor
0   302   12.3    123<-b_id=302, conta=12.3
1   302   12.3    100<-b_id=302, conta=12.3
2   303   12.3    323
3   302   11.4    674

... then need pivot_table with some aggregate function like mean, sum or use groupby + aggregate function + unstack:

df = df.pivot_table(index='b_id', columns='conta', values='valor', aggfunc='mean')
print (df)
conta   11.4   12.3
b_id               
302    674.0  111.5
303      NaN  323.0

df = df.groupby(['b_id', 'conta'])['valor'].mean().unstack()
print (df)
conta   11.4   12.3
b_id               
302    674.0  111.5
303      NaN  323.0

Upvotes: 2

Related Questions