英文:
pandas groupby nunique per multiple columns
问题
Sure, here's the code for adding the "count_per_id_user_and_date" column to your DataFrame using groupby and transform:
import pandas as pddf = pd.DataFrame({'id':[1,1,2,2,3],'user':['u1', 'u1', 'u2', 'u2', 'u3'],'date':['2021-04-25','2021-04-25','2021-04-25','2021-04-26', '2021-04-25'],'sth_else1':['xx','yy','xx','xx','xx'],'sth_else2':['zz','yy','zz','xx','xx']})df['count_per_id_user_and_date'] = df.groupby(['id', 'user', 'date'])['user'].transform('count')print(df)
This code will add the desired column to your DataFrame, showing the count of unique user/date combinations per id in the whole DataFrame.
英文:
I have a dataframe
df = pd.DataFrame({'id':[1,1,2,2,3],'user':['u1', 'u1', 'u2', 'u2', 'u3'],'date':['2021-04-25','2021-04-25','2021-04-25','2021-04-26', '2021-04-25'],'sth_else1':['xx','yy','xx','xx','xx'], 'sth_else2':['zz','yy','zz','xx','xx']})
id user date sth_else1 sth_else20 1 u1 2021-04-25 xx zz1 1 u1 2021-04-25 yy yy2 2 u2 2021-04-25 xx zz3 2 u2 2021-04-26 xx xx4 3 u3 2021-04-25 xx xx
and I would like to add a column (so probably use groupby with transform?) to that dataframe that shows me the number of unique user/date combinations I have per id in the whole dataframe, so that I would get this
id user date sth_else1 sth_else2 count_per_id_user_and_date0 1 u1 2021-04-25 xx zz 11 1 u1 2021-04-25 yy yy 12 2 u2 2021-04-25 xx zz 23 2 u2 2021-04-26 xx xx 24 3 u3 2021-04-25 xx xx 1
how would I do this?
答案1
得分: 1
如果您想要计算每个ID的用户/日期唯一组合数量，您可以首先识别非重复项，然后使用groupby.transform('sum')进行操作：
df['count_per_id_user_and_date'] = (~df[['id', 'user', 'date']].duplicated()).groupby(df['id']).transform('sum')
或者：
df['count_per_id_user_and_date'] = df['id'].map(df[['id', 'user', 'date']].drop_duplicates().groupby('id').size())
输出结果：
id user date sth_else1 sth_else2 count_per_id_user_and_date0 1 u1 2021-04-25 xx zz 11 1 u1 2021-04-25 yy yy 12 2 u2 2021-04-25 xx zz 23 2 u2 2021-04-26 xx xx 24 3 u3 2021-04-25 xx xx 1
英文:
If you want to count the number of unique combinations of user/date per id, you can first identify the non-duplicates, then groupby.transform('sum'):
df['count_per_id_user_and_date'] = (~df[['id', 'user', 'date']].duplicated()).groupby(df['id']).transform('sum')
Or:
df['count_per_id_user_and_date'] = df['id'].map(df[['id', 'user', 'date']].drop_duplicates().groupby('id').size())
Output:
id user date sth_else1 sth_else2 count_per_id_user_and_date0 1 u1 2021-04-25 xx zz 11 1 u1 2021-04-25 yy yy 12 2 u2 2021-04-25 xx zz 23 2 u2 2021-04-26 xx xx 24 3 u3 2021-04-25 xx xx 1
答案2
得分: 0
你可以尝试使用以下代码来实现：
df['final_result'] = df.groupby(['id', 'user', 'date'])['id'].transform('size')
英文:
Maybe you should try this piece of code to make it happen
df['final_result'] = df.groupby(['id', 'user', 'date'])['id'].transform('size')
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论