How to calculate unique rows with values in pandas-CodePudding

How do calculate the amount of rows that have the same user_id but unique purchase_date and creating new column with this information

Example of data:

user_id product_id  trial   refunded    purchase_date   country_code    media_source
30  476006  tenwords_1w_9.99_offer  False   False   2020-07-28  US  Facebook Ads
12  478118  tenwords_1w_9.99_offer  False   False   2020-09-28  Organic/Unknown Organic/Unknown
88  485260  tenwords_1w_9.99_offer  False   True    2020-09-16  Organic/Unknown Organic/Unknown
49  689561  tenwords_1w_9.99_offer  False   False   2020-09-28  Organic/Unknown Organic/Unknown
57  1096091 tenwords_1w_7.99_7free  True    False   2020-09-14  US  Facebook Ads
72  1304477 tenwords_1w_9.99_offer  False   False   2020-08-08  US  Facebook Ads
76  1434617 tenwords_1w_7.99_7free  True    False   2020-08-22  US  Facebook Ads
73  1523083 tenwords_1w_9.99_offer  False   False   2020-07-04  CA  Facebook Ads
98  1535384 tenwords_1w_9.99_offer  False   False   2020-07-13  US  Facebook Ads
90  1796915 tenwords_1w_7.99_7free  True    False   2020-08-21  US  Facebook Ads
54  1814069 tenwords_1w_9.99_offer  False   False   2020-07-01  Organic/Unknown Organic/Unknown
32  1988951 tenwords_1w_9.99_offer  False   False   2020-07-04  ZA  Facebook Ads
58  1993544 tenwords_1w_9.99_offer  False   False   2020-07-05  MX  Facebook Ads
56  1993544 tenwords_1w_9.99_offer  False   True    2020-08-16  MX  Facebook Ads
11  1994244 tenwords_1w_9.99_offer  False   False   2020-07-05  US  Facebook Ads
53  1998351 tenwords_1w_7.99_7free  False   False   2020-08-10  TW  Facebook Ads
80  2001394 tenwords_1w_9.99_offer  False   False   2020-07-14  BR  Facebook Ads
9   2016953 tenwords_1w_7.99_7free  True    False   2020-07-11  AU  Facebook Ads
35  2049401 tenwords_1w_9.99_offer  False   False   2020-07-17  US  Facebook Ads
33  2064109 tenwords_1w_9.99_offer  False   False   2020-07-18  US  Facebook Ads
41  2066336 tenwords_1w_7.99_7free  True    False   2020-07-19  MX  Facebook Ads
42  2075234 tenwords_1w_9.99_offer  False   False   2020-07-20  US  Facebook Ads
71  2076892 tenwords_1w_9.99_offer  False   False   2020-07-20  Organic/Unknown Organic/Unknown
59  2082187 tenwords_1w_7.99_7free  True    False   2020-07-20  PH  Facebook Ads

CodePudding user response：

Your wanted result can be achieved with a groupby statement, it will form a groupby object. And give you the amount, of unique purchase_dates a certain user has. So this line of code can help you.

potato = df.groupby(['user_id'])['purchase_date'].transform('nunique')

This shall return the wanted series, now is just a matter of adding it to your dataframe

df['NewColumn'] = potato