英文:
Python delete rows for each group after first occurance in a column
问题
以下是您要翻译的内容：
I Have a dataframe as follows:
df = pd.DataFrame({'Key':[1,1,1,1,2,2,2,4,4,4,5,5],'Activity':['A','A','H','B','B','H','H','A','C','H','H','B'],'Date':['2022-12-03','2022-12-04','2022-12-06','2022-12-08','2022-12-03','2022-12-06','2022-12-10','2022-12-03','2022-12-04','2022-12-07','2022-12-03','2022-12-13']})
I need to count the activities for each 'Key' that occur before 'Activity' == 'H' as follows:
Required Output
My Approach
- Sort df by Key & Date ( Sample input is already sorted)
- drop the rows that occur after 'H' Activity in each group as follows:
- Groupby df.groupby(['Key', 'Activity']).count()
Is there a better approach, if not then help me in code for dropping the rows that occur after 'H' Activity in each group.
Thanks in advance !
英文:
I Have a dataframe as follows:
df = pd.DataFrame({'Key':[1,1,1,1,2,2,2,4,4,4,5,5],'Activity':['A','A','H','B','B','H','H','A','C','H','H','B'],'Date':['2022-12-03','2022-12-04','2022-12-06','2022-12-08','2022-12-03','2022-12-06','2022-12-10','2022-12-03','2022-12-04','2022-12-07','2022-12-03','2022-12-13']})
I need to count the activities for each 'Key' that occur before 'Activity' == 'H' as follows:
Required Output
My Approach
-
Sort df by Key & Date ( Sample input is already sorted)
-
drop the rows that occur after 'H' Activity in each group as follows:
-
Groupby df.groupby(['Key', 'Activity']).count()
Is there a better approach , if not then help me in code for dropping the rows that occur after 'H' Activity in each group.
Thanks in advance !
答案1
得分: 1
你可以将“H”日期“带回”到每一行以便进行比较。
首先在一个新列中标记每个“H”日期：
df.loc[df["Activity"] == "H", "End"] = df["Date"]
Key Activity Date End0 1 A 2022-12-03 NaT1 1 A 2022-12-04 NaT2 1 H 2022-12-06 2022-12-063 1 B 2022-12-08 NaT4 2 B 2022-12-03 NaT5 2 H 2022-12-06 2022-12-066 2 H 2022-12-10 2022-12-107 4 A 2022-12-03 NaT8 4 C 2022-12-04 NaT9 4 H 2022-12-07 2022-12-0710 5 H 2022-12-03 2022-12-0311 5 B 2022-12-13 NaT
对每个分组向后填充新列：
df["End"] = df.groupby("Key")["End"].bfill()
Key Activity Date End0 1 A 2022-12-03 2022-12-061 1 A 2022-12-04 2022-12-062 1 H 2022-12-06 2022-12-063 1 B 2022-12-08 NaT4 2 B 2022-12-03 2022-12-065 2 H 2022-12-06 2022-12-066 2 H 2022-12-10 2022-12-107 4 A 2022-12-03 2022-12-078 4 C 2022-12-04 2022-12-079 4 H 2022-12-07 2022-12-0710 5 H 2022-12-03 2022-12-0311 5 B 2022-12-13 NaT
然后可以选择Date在End之前的行：
df.loc[df["Date"] < df["End"]]
Key Activity Date End0 1 A 2022-12-03 2022-12-061 1 A 2022-12-04 2022-12-064 2 B 2022-12-03 2022-12-067 4 A 2022-12-03 2022-12-078 4 C 2022-12-04 2022-12-07
生成最终形式时，可以使用.pivot_table()：
(df.loc[df["Date"] < df["End"]].pivot_table(index="Key", columns="Activity", values="Date", aggfunc="count").reindex(df["Key"].unique()) # 添加没有匹配的键，例如 `5`.fillna(0).astype(int))
Activity A B CKey1 2 0 02 0 1 04 1 0 15 0 0 0
英文:
You can bring the H dates "back" into each previous row to use in a comparison.
First mark each H date in a new column:
df.loc[df["Activity"] == "H" , "End"] = df["Date"]
Key Activity Date End0 1 A 2022-12-03 NaT1 1 A 2022-12-04 NaT2 1 H 2022-12-06 2022-12-063 1 B 2022-12-08 NaT4 2 B 2022-12-03 NaT5 2 H 2022-12-06 2022-12-066 2 H 2022-12-10 2022-12-107 4 A 2022-12-03 NaT8 4 C 2022-12-04 NaT9 4 H 2022-12-07 2022-12-0710 5 H 2022-12-03 2022-12-0311 5 B 2022-12-13 NaT
Backward fill the new column for each group:
df["End"] = df.groupby("Key")["End"].bfill()
Key Activity Date End0 1 A 2022-12-03 2022-12-061 1 A 2022-12-04 2022-12-062 1 H 2022-12-06 2022-12-063 1 B 2022-12-08 NaT4 2 B 2022-12-03 2022-12-065 2 H 2022-12-06 2022-12-066 2 H 2022-12-10 2022-12-107 4 A 2022-12-03 2022-12-078 4 C 2022-12-04 2022-12-079 4 H 2022-12-07 2022-12-0710 5 H 2022-12-03 2022-12-0311 5 B 2022-12-13 NaT
You can then select rows with Date before End
df.loc[df["Date"] < df["End"]]
Key Activity Date End0 1 A 2022-12-03 2022-12-061 1 A 2022-12-04 2022-12-064 2 B 2022-12-03 2022-12-067 4 A 2022-12-03 2022-12-078 4 C 2022-12-04 2022-12-07
To generate the final form - you can use .pivot_table()
(df.loc[df["Date"] < df["End"]].pivot_table(index="Key", columns="Activity", values="Date", aggfunc="count").reindex(df["Key"].unique()) # Add in keys with no match e.g. `5`.fillna(0).astype(int))
Activity A B CKey1 2 0 02 0 1 04 1 0 15 0 0 0
答案2
得分: 1
以下是您要翻译的代码部分：
(df.loc[df['Activity'].eq('H').groupby(df['Key']).cumsum().eq(0)].set_index('Key')['Activity'].str.get_dummies().groupby(level=0).sum().reindex(df['Key'].unique(), fill_value=0).reset_index())或(df['Activity'].where(df['Activity'].ne('H').groupby(df['Key']).cumprod()).str.get_dummies().groupby(df['Key']).sum())
英文:
Try this:
(df.loc[df['Activity'].eq('H').groupby(df['Key']).cumsum().eq(0)].set_index('Key')['Activity'].str.get_dummies().groupby(level=0).sum().reindex(df['Key'].unique(),fill_value=0).reset_index())
or
(df['Activity'].where(df['Activity'].ne('H').groupby(df['Key']).cumprod()).str.get_dummies().groupby(df['Key']).sum())
Output:
Key A B C0 1 2 0 01 2 0 1 02 4 1 0 13 5 0 0 0
答案3
得分: 1
# 按键和日期排序df.sort_values(['Key', 'Date'], inplace=True)# 当筛选后没有保留值时，保持Key在结果中df.Key = df.Key.astype('category')# 对每个Key在第一个H之后的所有行进行筛选，然后进行数据透视df[~df.Activity.eq('H').groupby(df.Key).cummax()].pivot_table(index='Key', columns='Activity', aggfunc='size').reset_index()# 活动 键 A B C# 0 1 2 0 0# 1 2 0 1 0# 2 4 1 0 1# 3 5 0 0 0
英文:
You can try:
# sort by Key and Datedf.sort_values(['Key', 'Date'], inplace=True)# this is to keep Key in the result when no values are kept after the filterdf.Key = df.Key.astype('category')# filter all rows after the 1st H for each Key and then pivotdf[~df.Activity.eq('H').groupby(df.Key).cummax()].pivot_table(index='Key', columns='Activity', aggfunc='size').reset_index()#Activity Key A B C#0 1 2 0 0#1 2 0 1 0#2 4 1 0 1#3 5 0 0 0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论