1

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 ...

ajrlewis
  • 2,968
  • 3
  • 33
  • 67
  • `melt` then pivot: `df.melt(['store', 'quantity']).pivot_table(index='value', columns='store', values='quantity', aggfunc='sum', fill_value=0)` – user3483203 Jul 30 '19 at 15:58
  • this is pivot problem you can try search how to pivot with for loop like pd.concat([df.pivot() for x in ['store', 'quantity'])]) – BENY Jul 30 '19 at 16:10
  • 1
    @anky_91 that is fine my friend, you can keep your answer here :-) – BENY Jul 30 '19 at 16:22

1 Answers1

2

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
anky
  • 74,114
  • 11
  • 41
  • 70