logo标签列表

每个时间段有多个匹配项的pd.merge_asof？

pythonpandasdataframe
4

我正试图通过时间将两个数据框合并，并且存在多个匹配项。 我正在寻找所有在df1的endofweek之前7天或更少的timestamp的df2实例。 可能有不止一个符合条件的记录，我想要所有匹配项，而不仅仅是第一个或最后一个（pd.merge_asof只提供第一个或最后一个匹配项）。

import pandas as pd
df1 = pd.DataFrame({'endofweek': ['2019-08-31', '2019-08-31', '2019-09-07', '2019-09-07', '2019-09-14', '2019-09-14'], 'GroupCol': [1234,8679,1234,8679,1234,8679]})
df2 = pd.DataFrame({'timestamp': ['2019-08-30 10:00', '2019-08-30 10:30', '2019-09-07 12:00', '2019-09-08 14:00'], 'GroupVal': [1234, 1234, 8679, 1234], 'TextVal': ['1234_1', '1234_2', '8679_1', '1234_3']})
df1['endofweek'] = pd.to_datetime(df1['endofweek'])
df2['timestamp'] = pd.to_datetime(df2['timestamp'])

我已经尝试过

pd.merge_asof(df1, df2, tolerance=pd.Timedelta('7d'), direction='backward', left_on='endofweek', right_on='timestamp', left_by='GroupCol', right_by='GroupVal')

但这使我感到困扰。

   endofweek  GroupCol           timestamp  GroupVal TextVal
0 2019-08-31      1234 2019-08-30 10:30:00    1234.0  1234_2
1 2019-08-31      8679                 NaT       NaN     NaN
2 2019-09-07      1234                 NaT       NaN     NaN
3 2019-09-07      8679                 NaT       NaN     NaN
4 2019-09-14      1234 2019-09-08 14:00:00    1234.0  1234_3
5 2019-09-14      8679 2019-09-07 12:00:00    8679.0  8679_1

我正在失去文本 1234_1。是否有一种外连接的方法可以在 pd.merge_asof 中保留所有 df2 的实例，而不仅仅是第一个或最后一个？

我的理想结果应该像这样（假设 endofweek 时间被视为那一天的 00:00:00）：

   endofweek  GroupCol           timestamp  GroupVal TextVal
0 2019-08-31      1234 2019-08-30 10:00:00    1234.0  1234_1
1 2019-08-31      1234 2019-08-30 10:30:00    1234.0  1234_2
2 2019-08-31      8679                 NaT       NaN     NaN
3 2019-09-07      1234                 NaT       NaN     NaN                 
4 2019-09-07      8679                 NaT       NaN     NaN 
5 2019-09-14      1234 2019-09-08 14:00:00    1234.0  1234_3
6 2019-09-14      8679 2019-09-07 12:00:00    8679.0  8679_1
2个回答
1

pd.merge_asof 只能进行左连接。在尝试加速 groupby/merge_ordered 示例时遇到了很多挫折，使用不同方向上的 pd.merge_asof 处理两个数据源更直观且更快，然后进行外连接以合并它们。

left_merge = pd.merge_asof(df1, df2,
    tolerance=pd.Timedelta('7d'), direction='backward', 
    left_on='endofweek', right_on='timestamp', 
    left_by='GroupCol', right_by='GroupVal')

right_merge = pd.merge_asof(df2, df1, 
    tolerance=pd.Timedelta('7d'), direction='forward', 
    left_on='timestamp', right_on='endofweek',
    left_by='GroupVal', right_by='GroupCol')

merged = (left_merge.merge(right_merge, how="outer")
    .sort_values(['endofweek', 'GroupCol', 'timestamp'])
    .reset_index(drop=True))

merged

   endofweek  GroupCol           timestamp  GroupVal TextVal
0 2019-08-31      1234 2019-08-30 10:00:00    1234.0  1234_1
1 2019-08-31      1234 2019-08-30 10:30:00    1234.0  1234_2
2 2019-08-31      8679                 NaT       NaN     NaN
3 2019-09-07      1234                 NaT       NaN     NaN
4 2019-09-07      8679                 NaT       NaN     NaN
5 2019-09-14      1234 2019-09-08 14:00:00    1234.0  1234_3
6 2019-09-14      8679 2019-09-07 12:00:00    8679.0  8679_1

此外，它比我的其他答案快得多：
import time
n=1000
start=time.time()
for i in range(n):
    left_merge = pd.merge_asof(df1, df2,
        tolerance=pd.Timedelta('7d'), direction='backward', 
        left_on='endofweek', right_on='timestamp', 
        left_by='GroupCol', right_by='GroupVal')
    right_merge = pd.merge_asof(df2, df1, 
        tolerance=pd.Timedelta('7d'), direction='forward', 
        left_on='timestamp', right_on='endofweek',
        left_by='GroupVal', right_by='GroupCol')
    merged = (left_merge.merge(right_merge, how="outer")
        .sort_values(['endofweek', 'GroupCol', 'timestamp'])
        .reset_index(drop=True))

end = time.time()

end-start
15.040804386138916
这仅在 df1 和 df2 之间的最大匹配数量为 2 时才有效，对吗？ - Bebeerna
0

我尝试的一种方法是在一个数据框上使用groupby，然后在pd.merge_ordered中对另一个数据框进行子集划分：

merged = (df1.groupby(['GroupCol', 'endofweek']).
apply(lambda x: pd.merge_ordered(x, df2[(
(df2['GroupVal']==x.name[0])
&(abs(df2['timestamp']-x.name[1])<=pd.Timedelta('7d')))], 
left_on='endofweek', right_on='timestamp')))

merged

                       endofweek  GroupCol           timestamp  GroupVal TextVal
GroupCol endofweek
1234     2019-08-31 0        NaT       NaN 2019-08-30 10:00:00    1234.0  1234_1
                    1        NaT       NaN 2019-08-30 10:30:00    1234.0  1234_2
                    2 2019-08-31    1234.0                 NaT       NaN     NaN
         2019-09-07 0 2019-09-07    1234.0                 NaT       NaN     NaN
         2019-09-14 0        NaT       NaN 2019-09-08 14:00:00    1234.0  1234_3
                    1 2019-09-14    1234.0                 NaT       NaN     NaN
8679     2019-08-31 0 2019-08-31    8679.0                 NaT       NaN     NaN
         2019-09-07 0 2019-09-07    8679.0                 NaT       NaN     NaN
         2019-09-14 0        NaT       NaN 2019-09-07 12:00:00    8679.0  8679_1
                    1 2019-09-14    8679.0                 NaT       NaN     NaN

merged[['endofweek', 'GroupCol']] = (merged[['endofweek', 'GroupCol']]
.fillna(method="bfill"))

merged.reset_index(drop=True, inplace=True)

merged
   endofweek  GroupCol           timestamp  GroupVal TextVal
0 2019-08-31    1234.0 2019-08-30 10:00:00    1234.0  1234_1
1 2019-08-31    1234.0 2019-08-30 10:30:00    1234.0  1234_2
2 2019-08-31    1234.0                 NaT       NaN     NaN
3 2019-09-07    1234.0                 NaT       NaN     NaN
4 2019-09-14    1234.0 2019-09-08 14:00:00    1234.0  1234_3
5 2019-09-14    1234.0                 NaT       NaN     NaN
6 2019-08-31    8679.0                 NaT       NaN     NaN
7 2019-09-07    8679.0                 NaT       NaN     NaN
8 2019-09-14    8679.0 2019-09-07 12:00:00    8679.0  8679_1
9 2019-09-14    8679.0                 NaT       NaN     NaN

然而，对我来说，结果似乎非常缓慢：

import time
n=1000
start=time.time()
for i in range(n):
    merged = (df1.groupby(['GroupCol', 'endofweek']).
    apply(lambda x: pd.merge_ordered(x, df2[(
    (df2['GroupVal']==x.name[0])
    &(abs(df2['timestamp']-x.name[1])<=pd.Timedelta('7d')))], 
    left_on='endofweek', right_on='timestamp')))

end = time.time()

end-start
40.72932052612305

我非常感激任何的改进！

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接