基于时间间隔合并数据框的pandas操作

Question

我有一个数据框，其中包含每 10 分钟的日期时间列和数值列:

df1 = pd.DataFrame({'time' : pd.date_range('1/1/2018', periods=20, freq='10min'), 'value' : np.random.randint(2, 20, size=20)})

另外还有一个事件安排，包含开始时间和结束时间。同时可能会发生多个事件:

df2 = pd.DataFrame({'start_time' : ['2018-01-01 00:00:00', '2018-01-01 00:00:00','2018-01-01 01:00:00', '2018-01-01 01:00:00', '2018-01-01 01:00:00', '2018-01-01 02:00:00' ], 'end_time' : ['2018-01-01 01:00:00', '2018-01-01 01:00:00', '2018-01-01 02:00:00','2018-01-01 02:00:00', '2018-01-01 02:00:00', '2018-01-01 03:00:00'], 'event' : ['A', 'B', 'C', 'D', 'E', 'F'] })
df2[['start_time', 'end_time']] = df2.iloc[:,0:2].apply(pd.to_datetime)

我希望在 df1 上执行左连接，将所有在起始时间和结束时间范围内的事件都包括进来。我的输出表应该如下：

                  time  value event
0  2018-01-01 00:00:00      5     A
1  2018-01-01 00:00:00      5     B
2  2018-01-01 00:10:00     15     A
3  2018-01-01 00:10:00     15     B
4  2018-01-01 00:20:00     16     A
5  2018-01-01 00:20:00     16     B
.....
17 2018-01-01 02:50:00      7     F

我尝试了这些SO解决方案，但由于时间间隔重复而失败。

- robroc

---

我不明白你是从哪里得到第一个和第二个的value=5。 - user3483203

@user3483203 value 被设置为一个随机数，所以结果是一个例子。 - Joachim Isaksson

啊，应该先读一下，谢谢 :P - user3483203

Answer 1

设置（仅使用df1中的少数条目为简洁起见）：

df1 = pd.DataFrame({'time' : pd.date_range('1/1/2018', periods=20, freq='10min'), 'value' : np.random.randint(2, 20, size=20)})
df2 = pd.DataFrame({'start_time' : ['2018-01-01 00:00:00', '2018-01-01 00:00:00','2018-01-01 01:00:00', '2018-01-01 01:00:00', '2018-01-01 01:00:00', '2018-01-01 02:00:00' ], 'end_time' : ['2018-01-01 01:00:00', '2018-01-01 01:00:00', '2018-01-01 02:00:00','2018-01-01 02:00:00', '2018-01-01 02:00:00', '2018-01-01 03:00:00'], 'event' : ['A', 'B', 'C', 'D', 'E', 'F'] })

df1 = df1.sample(5)
df2[['start_time', 'end_time']] = df2.iloc[:,0:2].apply(pd.to_datetime)

你可以使用简单的列表推导式来实现目标。本答案假设您的DataFrame中所有日期列实际上都是datetime类型。 步骤1 使用列表推导式和简单的区间检查，找到在特定时间范围内发生的所有事件：

packed = list(zip(df2.start_time, df2.end_time, df2.event))
df1['event'] = [[ev for strt, end, ev in packed if strt <= el <= end] for el in df1.time]

                  time  value      event
2  2018-01-01 00:20:00      8     [A, B]
14 2018-01-01 02:20:00     14        [F]
8  2018-01-01 01:20:00      6  [C, D, E]
19 2018-01-01 03:10:00     16         []
4  2018-01-01 00:40:00      7     [A, B]

步骤2：

最后，使用另一个列表推导式将上一个结果中的每个列表拆分为新行：

pd.DataFrame(
    [[t, val, e] for t, val, event in zip(df1.time, df1.value, df1.event)
    for e in event
    ], columns=df1.columns
)

输出：

                 time  value event
0 2018-01-01 00:20:00      8     A
1 2018-01-01 00:20:00      8     B
2 2018-01-01 02:20:00     14     F
3 2018-01-01 01:20:00      6     C
4 2018-01-01 01:20:00      6     D
5 2018-01-01 01:20:00      6     E
6 2018-01-01 00:40:00      7     A
7 2018-01-01 00:40:00      7     B

- user3483203

---

这对于所提供的示例数据框非常有效。如果我有更多列要在最终输出中保留，您是否看到一种抽象的方法？ - robroc

你只需要将它们添加到第二步中“zip”列中即可。你可以在列表中跟踪它们，然后反向“zip”它们。 - user3483203

Answer 2

我不完全确定您的问题，但是如果您想加入“在开始和结束时间内的事件”，那么听起来您需要类似于SQL的“between”操作符。您的数据并没有特别清晰。

Pandas本身不具备此功能，但Pandasql可以实现。它允许您对数据框运行sqlite。我认为您需要类似于以下的东西：

import pandasql as ps

sqlcode = '''
select *
from df1
inner join df2 on df1.event=df2.event
where df2.time >= d1.start_time and df2.fdate <= d1.stop_time
'''

newdf = ps.sqldf(sqlcode,locals())

相关问题： 如何合并pandas数据框，其中一个值在两个值之间

- AZhao

---

df1 没有 event。 - harvpan

这看起来非常方便。pandasql 在幕后做了什么？ - stevepastelan

Answer 3

一种方法是使用条件连接，它来自于pyjanitor：

# pip install pyjanitor
import pandas as pd
import janitor

out = df1.conditional_join(
            df2, 
           ('time', 'start_time', '>='), 
           ('time', 'end_time', '<=')
        )

out.head()
                 time  value start_time            end_time event
0 2018-01-01 00:00:00     14 2018-01-01 2018-01-01 01:00:00     A
1 2018-01-01 00:00:00     14 2018-01-01 2018-01-01 01:00:00     B
2 2018-01-01 00:10:00     10 2018-01-01 2018-01-01 01:00:00     A
3 2018-01-01 00:10:00     10 2018-01-01 2018-01-01 01:00:00     B
4 2018-01-01 00:20:00     15 2018-01-01 2018-01-01 01:00:00     A

- sammywemmy

Answer 4

你可以在 df2 上工作，使用重新采样 '10min'（就像在 df1 中一样）创建一个包含所有时间的列，然后使用 merge。这需要进行大量操作，可能不是最有效的方法。

df2_manip = (df2.set_index('event').stack().reset_index().set_index(0)
                .groupby('event').resample('10T').ffill().reset_index(1))

而 df2_manip 看起来像：

                        0 event     level_1
event                                      
A     2018-01-01 00:00:00     A  start_time
A     2018-01-01 00:10:00     A  start_time
A     2018-01-01 00:20:00     A  start_time
A     2018-01-01 00:30:00     A  start_time
A     2018-01-01 00:40:00     A  start_time
A     2018-01-01 00:50:00     A  start_time
A     2018-01-01 01:00:00     A    end_time
B     2018-01-01 00:00:00     B  start_time
B     2018-01-01 00:10:00     B  start_time
B     2018-01-01 00:20:00     B  start_time
B     2018-01-01 00:30:00     B  start_time
...

现在你可以合并:

df1 = df1.merge(df2_manip[[0, 'event']].rename(columns={0:'time'}))

然后你会得到df1：

                  time  value event
0  2018-01-01 00:00:00      9     A
1  2018-01-01 00:00:00      9     B
2  2018-01-01 00:10:00     16     A
3  2018-01-01 00:10:00     16     B
...
33 2018-01-01 02:00:00      6     D
34 2018-01-01 02:00:00      6     E
35 2018-01-01 02:00:00      6     F
36 2018-01-01 02:10:00      2     F
37 2018-01-01 02:20:00     18     F
38 2018-01-01 02:30:00     14     F
39 2018-01-01 02:40:00      5     F
40 2018-01-01 02:50:00      3     F
41 2018-01-01 03:00:00      9     F

- Ben.T