I would like to groupby customer and count how many times they have purchased in each store. And each store should have their own column
Original dataframe:
customer store transaction value
0 John KFC 5
1 John McDonalds 5
2 John KFC 6
3 Mary KFC 3
4 Mary McDonalds 5
5 Mary Burger King 1
6 Peter McDonalds 2
7 Peter McDonalds 5
8 Peter McDonalds 8
9 Peter KFC 1
My expected output is:
customer KFC McDonalds Burger King
John 2 1 0
Mary 1 1 1
Peter 1 3 0
To be honest I'm not 100% sure which pandas function to use for this. I think I've tried them all, including melt and none of these have worked:
d.groupby(['customer','store']).agg({'store': ['count']})
d.groupby('customer').agg('count')
d.pivot(index='customer', columns='store', values='Amount')
I think I need to use pivot, but I can't figure it out.
My df:
pd.DataFrame({'customer': pd.Series(['John', 'John', 'John', 'Mary', 'Mary', 'Mary', 'Peter', 'Peter', 'Peter', 'Peter'],dtype='object',index=pd.RangeIndex(start=0, stop=10, step=1)), 'store': pd.Series(['KFC', 'McDonalds', 'KFC', 'KFC', 'McDonalds', 'Burger King', 'McDonalds', 'McDonalds', 'McDonalds', 'KFC'],dtype='object',index=pd.RangeIndex(start=0, stop=10, step=1)), 'transaction value': pd.Series([5, 5, 6, 3, 5, 1, 2, 5, 8, 1],dtype='int64',index=pd.RangeIndex(start=0, stop=10, step=1))}, index=pd.RangeIndex(start=0, stop=10, step=1))
