Python Pandas - Aggregation and count

Question

I have a dataframe (below there's a super simplified version) which has transactions data of product bought and device used:

CUST_ID PRODUCT DEVICE
----------------------

1       A       MOBILE
1       B       TABLET
2       B       LAPTOP
2       A      MOBILE 
3       C      TABLET
3       C      TABLET

I would like to transform it in order to have frequencies of purchase for each product and device usage by single cust_id view: i.e. a dataframe (3x7)

CUST_ID PRODUCT_A   PRODUCT_B   PRODUCT_C   DEVICE_MOBILE   DEVICE_LAPTOP   DEVICE_TABLET

1   1   1   0   1   0   1
2   1   1   0   1   1   0
3   0   0   2   0   0   2

I tried to use the .pivot_table() function, but it adds me indexes and duplicate columns. This is a simplified version, I would need to do this for many products and devices, so maybe a function or loop would be more efficient?

Yuca · Accepted Answer · 2018-10-17 12:51:52Z

1

You can use pd.get_dummies and df.groupby

pd.get_dummies(df, columns=['PRODUCT','DEVICE']).groupby(['CUST_ID'], as_index=False).sum()

Output:

CUST_ID  PRODUCT_A  PRODUCT_B  PRODUCT_C  DEVICE_LAPTOP  DEVICE_MOBILE  \
0       1          1          1          0              0              1   
1       2          1          1          0              1              1   
2       3          0          0          2              0              0   

   DEVICE_TABLET  
0              1  
1              0  
2              2

answered Oct 17, 2018 at 12:51

Yuca

6,1114 gold badges26 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jpp · Accepted Answer · 2018-10-17 12:29:42Z

0

You can use pd.crosstab twice and join the results:

cross1 = pd.crosstab(index=df['CUST_ID'], columns=df['PRODUCT'])
cross2 = pd.crosstab(index=df['CUST_ID'], columns=df['DEVICE'])

res = cross1.join(cross2)

print(res)

         A  B  C  LAPTOP  MOBILE  TABLET
CUST_ID                                 
1        1  1  0       0       1       1
2        1  1  0       1       1       0
3        0  0  2       0       0       2

answered Oct 17, 2018 at 12:29

jpp

166k37 gold badges301 silver badges363 bronze badges

3 Comments

Mastodon87 Over a year ago

thanks a lot! I'm just getting this error when I try to join the different cross tables:

Mastodon87 Over a year ago

TypeError: categories must match existing categories when appending

jpp Over a year ago

@JamesPietroZanzarelli, Looks like you are using Categorical Data. This isn't part of your question. Convert your series to regular object dtype or (possibly better) post a new question with a minimal reproducible example if you're stuck.

Collectives™ on Stack Overflow

Python Pandas - Aggregation and count

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related