CODEWITHSUNDEEP
pythonpandaspivottransform
ajrlewis
ajrlewis

Reputation: 3058

Transforming/pivoting a DataFrame Instance

I have the following pandas data frame:

import pandas as pd
data = dict(store=['A', 'B', 'B'], color=['red', 'black', 'black'], size=['small', 'medium', 'small'], quantity=[2, 4, 1])
df = pd.DataFrame(data)

which looks like this:

  store  color    size  quantity
0     A    red   small         2
1     B  black  medium         4
2     B  black   small         1

What I want to do is the pivot function to create a new data frame that uses the store column as a header and the the quantity value for each of the unique row values of the color and size columns. So it should look like this:

       A   B
black  0   5
red    2   0 
small  2   1
medium 0   4

Can this be done? Something like df.pivot(columns='store') seems to be the way to go ...

Upvotes: 1

Views: 27

Answers (1)

anky
anky

Reputation: 75080

You can use melt and pivot_table():

m=df.melt(['store','quantity'])

m.pivot_table(index='value',columns='store',values='quantity',aggfunc='sum',fill_value=0)

store   A  B
value       
black   0  5
medium  0  4
red     2  0
small   2  1

Upvotes: 2

Related Questions