CODEWITHSUNDEEP
pandasgroup-by
Shuvayan Das
Shuvayan Das

Reputation: 1048

Creating variables based on whether a column contains a particular string during groupby in pandas

I have the below data which signifies how many times a person used different services :

account     site                        hitCount
243601      auth.svcs.facebook.com      3
243601      auth.svcs.facebook.com      1
243601      respframework.facebook.com  2
243601      respframework.facebook.com  1
243601      auth.svcs.facebook.com      6
243601      auth.svcs.facebook.com      2
243601      pie.prod.facebook.com       1
243601      profile.facebook.com        5
243601      respframework.facebook.com  4
243601      mediasearch.facebook.com    1
243601      pie.prod.facebook.com       2
243601      auth.svcs.facebook.com      1
243601      auth.svcs.facebook.com      1
243601      respframework.facebook.com  1
243601      profile.facebook.com        2
243601      auth.svcs.facebook.com      4
243601      collaborateext.facebook.com 1
243601      auth.svcs.facebook.com      1
243601      auth.svcs.facebook.com      2
243601      auth.svcs.facebook.com      4
243601      www.facebook.com            2

The sample data is for 1 customer. The original data has about 80k customers.

I am doing a group by per account to get a sum of the number of hits as below:

df_hits.groupby(level = 0)['hitCount'].sum().reset_index()

However, I also need to create 3 more variables as below:

account hitCount    profile_hit profile_hit_count   non_profile_hit_count
243601  47          1           2                   45

I am not sure how to create the other variables during group by. Can someone please help me with this?

Upvotes: 1

Views: 35

Answers (1)

jezrael
jezrael

Reputation: 863226

You can use:

#create new column for check string profile and cast to integers
df_hits =df_hits.assign(profile_hit_count=df_hits['site'].str.contains('profile').astype(int))
#aggregate `sum` twice - for profile_hit_count for count aocurencies
df = df_hits.groupby(level = 0).agg({'hitCount':'sum', 'profile_hit_count':'sum'})
#difference
df['non_profile_hit_count'] = df['hitCount'] - df['profile_hit_count']
#check if not 0 and cast to integer if necessary
df['profile_hit'] = df['profile_hit_count'].ne(0).astype(int)
print (df)
         hitCount  profile_hit_count  non_profile_hit_count  profile_hit
account                                                                 
243601         47                  2                     45            1

Upvotes: 1

Related Questions