我创建了一些数据,看起来像这样:
import pandas as pd
d = {'Time': ['01.10.2019, 09:56:52', '01.10.2019, 09:57:15', '02.10.2019 09:57:23', '02.10.2019 10:02:58', '02.10.2019 13:11:58', '02.10.2019 13:22:55', '03.10.2019, 09:56:52', '03.10.2019, 09:57:15', '04.10.2019 09:57:23', '04.10.2019 10:02:58', '04.10.2019 13:11:58', '04.10.2019 13:22:55']
,'Action': ['Opened', 'Closed', 'Opened', 'Closed', 'Opened', 'Closed', 'Opened', 'Closed', 'Opened', 'Closed', 'Opened', 'Closed']
,'Name': ['Bayer', 'Bayer', 'ITM', 'ITM', 'ITM' , 'ITM', 'ITM', 'ITM', 'Treso', 'Treso', 'Geco' , 'Geco']}
df = pd.DataFrame(data=d)
Time Action Name
0 01.10.2019, 09:56:52 Opened Bayer
1 01.10.2019, 09:57:15 Closed Bayer
2 02.10.2019, 09:57:23 Opened ITM
3 02.10.2019, 10:03:58 Closed ITM
4 02.10.2019, 13:11:58 Opened ITM
5 02.10.2019, 13:22:55 Closed ITM
6 03.10.2019, 09:56:52 Opened ITM
7 03.10.2019, 09:57:15 Closed ITM
8 04.10.2019, 09:57:23 Opened Treso
9 04.10.2019, 10:03:58 Closed Treso
10 04.10.2019, 13:11:58 Opened Geco
11 04.10.2019, 13:22:55 Closed Geco
现在我想根据以下条件删除数据:
- 如果打开和关闭之间的时间不到5分钟,并且名称相同,则应将其删除
- 如果有一个打开动作,名称相同并且在关闭后重复出现,并且在同一天 -> 应删除所有具有相同名称的内容,从第一个打开到最后一个打开。例如,线2到线5应该被删除,但不是直到线7,因为它是一天后。
例如,第二个条件:如果存在此输入:
Time Action Name
0 02.10.2019, 09:57:23 Opened ITM
1 02.10.2019, 10:03:58 Closed ITM
2 02.10.2019, 13:11:58 Opened ITM
3 02.10.2019, 13:22:55 Closed ITM
4 03.10.2019, 09:56:52 Opened ITM
5 03.10.2019, 09:57:15 Closed ITM
我的输出应该像这样:
0 02.10.2019, 13:11:58 Opened ITM
1 02.10.2019, 13:22:55 Closed ITM
2 03.10.2019, 09:56:52 Opened ITM
3 03.10.2019, 09:57:15 Closed ITM
因为已经到了第二天,所以从10月2日到3日的时间跨度存在,而其他时间跨度均不超过5分钟。
但如果我们遇到以下情况:
0 02.10.2019, 09:57:23 Opened ITM
1 02.10.2019, 10:03:58 Closed ITM
2 02.10.2019, 13:11:58 Opened ITM
3 02.10.2019, 13:22:55 Closed ITM
4 02.10.2019, 09:56:52 Opened ITM
5 02.10.2019, 09:57:15 Closed ITM
只保留第二行和第三行的数据,其余数据应删除:
2 02.10.2019, 13:11:58 Opened ITM
3 02.10.2019, 13:22:55 Closed ITM
My wished output should be like this:
Time Action Name
0 02.10.2019, 09:57:23 Opened ITM
3 02.10.2019, 13:22:55 Closed ITM
4 03.10.2019, 09:56:52 Opened ITM
5 03.10.2019, 09:57:15 Closed ITM
6 04.10.2019, 09:57:23 Opened Treso
7 04.10.2019, 10:03:58 Closed Treso
8 04.10.2019, 13:11:58 Opened Geco
9 04.10.2019, 13:22:55 Closed Geco
我的尝试:
df_new = df.assign(group=pd.to_datetime(df["Time"]).diff().dt.seconds.gt(300).cumsum()).groupby(["group",
"Time",
"Action",
"Name"]).first()
有人能帮帮我吗?
shift()
可以处理小于5分钟的情况。 - noah