Pandas:根据多个类别的 Dataframe B中的时间戳范围过滤 Dataframe A中的时间戳

xlpyo6sf  于 2023-03-21  发布在  其他
关注(0)|答案(1)|浏览(94)

第一个数据框(df 1)具有父类别、子类别和时间戳列。第二个数据框(df 2)具有每个父类别和子类别的时间戳的最小值和最大值。
我希望以这样的方式过滤df 1中的时间戳:对于每个父子类别,只保留df 2指定的最小和最大边界(包括边界)内的时间戳。
对于reprex,以下是从较大样本中提取的两个 Dataframe 的子集:

import pandas as pd

data_df1 = [[2, 9, "2023-01-10 15:03:24.100"],
            [2, 9, "2023-01-10 15:03:30.500"],
            [2, 9, "2023-01-10 15:05:20.300"],
            [2, 9, "2023-01-10 15:05:59.600"],
            [2, 10, "2023-01-10 15:03:24.100"],
            [2, 10, "2023-01-10 15:03:30.500"],
            [2, 11, "2023-01-10 15:03:40.300"],
            [2, 11, "2023-01-10 15:04:42.600"],
            [2, 11, "2023-01-10 15:05:54.600"],
            [3, 9, "2023-01-10 15:05:54.100"],
            [3, 9, "2023-01-10 15:06:30.500"],
            [3, 9, "2023-01-10 15:07:20.300"],
            [3, 9, "2023-01-10 15:08:59.600"],
            [3, 10, "2023-01-10 15:05:55.200"],
            [3, 10, "2023-01-10 15:06:01.500"],
            [3, 10, "2023-01-10 15:06:10.300"],
            [3, 11, "2023-01-10 15:05:59.600"],
            [3, 11, "2023-01-10 15:06:05.600"],
            [3, 11, "2023-01-10 15:06:06.900"]]

data_df2 = [[2, 9, "2023-01-10 15:03:25.600", "2023-01-10 15:05:53.600"],
            [2, 10, "2023-01-10 15:03:24.200", "2023-01-10 15:03:34.500"],
            [2, 11, "2023-01-10 15:03:41.900", "2023-01-10 15:05:53.900"],
            [3, 9, "2023-01-10 15:05:55.400", "2023-01-10 15:08:23.200"],
            [3, 10, "2023-01-10 15:05:55.200", "2023-01-10 15:06:03.100"],
            [3, 11, "2023-01-10 15:05:56.000", "2023-01-10 15:06:06.000"]]

df1 = pd.DataFrame(data_df1, columns = ['Parent_Cat', 'Child_Cat', 'TimeStamp'])
df2 = pd.DataFrame(data_df2, columns = ['Parent_Cat', 'Child_Cat', 'Tmin', 'Tmax'])

因此,对于上述数据集,0-18行索引中的df 1应保留以下内容:第1、2、5、7、10、11、13、14、16和17条。

qcbq4gxm

qcbq4gxm1#

使用左连接DataFrame.merge,然后使用Series.between过滤boolean indexing

df1['TimeStamp'] = pd.to_datetime(df1['TimeStamp'])
df2['Tmin'] = pd.to_datetime(df2['Tmin'])
df2['Tmax'] = pd.to_datetime(df2['Tmax'])

df = df1.merge(df2, on=['Parent_Cat','Child_Cat'], how='left')

df = df.loc[df['TimeStamp'].between(df['Tmin'], df['Tmax'], inclusive=True), 
            df1.columns]
print (df)
    Parent_Cat  Child_Cat               TimeStamp
1            2          9 2023-01-10 15:03:30.500
2            2          9 2023-01-10 15:05:20.300
5            2         10 2023-01-10 15:03:30.500
7            2         11 2023-01-10 15:04:42.600
10           3          9 2023-01-10 15:06:30.500
11           3          9 2023-01-10 15:07:20.300
13           3         10 2023-01-10 15:05:55.200
14           3         10 2023-01-10 15:06:01.500
16           3         11 2023-01-10 15:05:59.600
17           3         11 2023-01-10 15:06:05.600

相关问题