Pandas Pivot Table Of Sales

I have a list like below: saleid upc 0 155_02127453_20090616_135212_0021 02317639000000 1 155_02127453_20090616_135212_0021 00000000000888

Solution 1:

Option 1

df.groupby(['saleid', 'upc']).size().unstack(fill_value=0)

Option 2

pd.crosstab(df.saleid, df.upc)

Setup

from StringIO import StringIO
import pandas as pd

text = """    saleid                              upc
0   155_02127453_20090616_135212_0021   02317639000000
1   155_02127453_20090616_135212_0021   00000000000888
2   155_01605733_20090616_135221_0016   00264850000000
3   155_01072401_20090616_135224_0010   02316877000000
4   155_01072401_20090616_135224_0010   05051969277205"""

df = pd.read_csv(StringIO(text), delim_whitespace=True, dtype=str)
df

Solution 2:

simple pivot_table() solution:

In [16]: df.pivot_table(index='saleid', columns='upc', aggfunc='size', fill_value=0)
Out[16]:
upc                                00000000000888  00264850000000  02316877000000  02317639000000  05051969277205
saleid
155_01072401_20090616_135224_0010               0               0               1               0               1
155_01605733_20090616_135221_0016               0               1               0               0               0
155_02127453_20090616_135212_0021               1               0               0               1               0