如何连接两个列值在一定范围内的数据框?
- 2024-12-03 08:44:00
- admin 原创
- 163
问题描述:
给定两个数据框df_1
和df_2
,如何将它们连接起来,使得日期时间列df_1
位于 数据框中的start
和之间:end
`df_2`
print df_1
timestamp A B
0 2016-05-14 10:54:33 0.020228 0.026572
1 2016-05-14 10:54:34 0.057780 0.175499
2 2016-05-14 10:54:35 0.098808 0.620986
3 2016-05-14 10:54:36 0.158789 1.014819
4 2016-05-14 10:54:39 0.038129 2.384590
print df_2
start end event
0 2016-05-14 10:54:31 2016-05-14 10:54:33 E1
1 2016-05-14 10:54:34 2016-05-14 10:54:37 E2
2 2016-05-14 10:54:38 2016-05-14 10:54:42 E3
得到相应的event
其中df1.timestamp
介于df_2.start
和 之间df2.end
timestamp A B event
0 2016-05-14 10:54:33 0.020228 0.026572 E1
1 2016-05-14 10:54:34 0.057780 0.175499 E2
2 2016-05-14 10:54:35 0.098808 0.620986 E2
3 2016-05-14 10:54:36 0.158789 1.014819 E2
4 2016-05-14 10:54:39 0.038129 2.384590 E3
解决方案 1:
一个简单的解决方案是interval index
从start and end
设置中创建closed = both
然后用来get_loc
获取事件,即(希望所有的日期时间都在时间戳dtype中)
df_2.index = pd.IntervalIndex.from_arrays(df_2['start'],df_2['end'],closed='both')
df_1['event'] = df_1['timestamp'].apply(lambda x : df_2.iloc[df_2.index.get_loc(x)]['event'])
输出 :
时间戳 AB 事件
0 2016-05-14 10:54:33 0.020228 0.026572 E1
1 2016-05-14 10:54:34 0.057780 0.175499 E2
2 2016-05-14 10:54:35 0.098808 0.620986 E2
3 2016-05-14 10:54:36 0.158789 1.014819 E2
4 2016-05-14 10:54:39 0.038129 2.384590 E3
解决方案 2:
首先使用 IntervalIndex 根据感兴趣的间隔创建参考索引,然后使用 get_indexer 对包含感兴趣的离散事件的数据框进行切分。
idx = pd.IntervalIndex.from_arrays(df_2['start'], df_2['end'], closed='both')
event = df_2.iloc[idx.get_indexer(df_1.timestamp), 'event']
event
0 E1
1 E2
1 E2
1 E2
2 E3
Name: event, dtype: object
df_1['event'] = event.to_numpy()
df_1
timestamp A B event
0 2016-05-14 10:54:33 0.020228 0.026572 E1
1 2016-05-14 10:54:34 0.057780 0.175499 E2
2 2016-05-14 10:54:35 0.098808 0.620986 E2
3 2016-05-14 10:54:36 0.158789 1.014819 E2
4 2016-05-14 10:54:39 0.038129 2.384590 E3
参考:一个关于IntervalIndex.get_indexer.
解决方案 3:
选项 1
idx = pd.IntervalIndex.from_arrays(df_2['start'], df_2['end'], closed='both')
df_2.index=idx
df_1['event']=df_2.loc[df_1.timestamp,'event'].values
选项 2
df_2['timestamp']=df_2['end']
pd.merge_asof(df_1,df_2[['timestamp','event']],on='timestamp',direction ='forward',allow_exact_matches =True)
Out[405]:
timestamp A B event
0 2016-05-14 10:54:33 0.020228 0.026572 E1
1 2016-05-14 10:54:34 0.057780 0.175499 E2
2 2016-05-14 10:54:35 0.098808 0.620986 E2
3 2016-05-14 10:54:36 0.158789 1.014819 E2
4 2016-05-14 10:54:39 0.038129 2.384590 E3
解决方案 4:
您可以使用模块pandasql
import pandasql as ps
sqlcode = '''
select df_1.timestamp
,df_1.A
,df_1.B
,df_2.event
from df_1
inner join df_2
on d1.timestamp between df_2.start and df2.end
'''
newdf = ps.sqldf(sqlcode,locals())
解决方案 5:
在这个方法中,我们假设使用了 TimeStamp 对象。
df2 start end event
0 2016-05-14 10:54:31 2016-05-14 10:54:33 E1
1 2016-05-14 10:54:34 2016-05-14 10:54:37 E2
2 2016-05-14 10:54:38 2016-05-14 10:54:42 E3
event_num = len(df2.event)
def get_event(t):
event_idx = ((t >= df2.start) & (t <= df2.end)).dot(np.arange(event_num))
return df2.event[event_idx]
df1["event"] = df1.timestamp.transform(get_event)
解释get_event
对于 中的每个时间戳df1
,t0 = 2016-05-14 10:54:33
例如
(t0 >= df2.start) & (t0 <= df2.end)
将包含 1 个 true。(参见示例 1)。然后,与 进行点积,以获取 a所属np.arange(event_num)
事件的索引。t0
例子:
示例 1
t0 >= df2.start t0 <= df2.end After & np.arange(3)
0 True True -> T 0 event_idx
1 False True -> F 1 -> 0
2 False True -> F 2
再举t2 = 2016-05-14 10:54:35
一个例子
t2 >= df2.start t2 <= df2.end After & np.arange(3)
0 True False -> F 0 event_idx
1 True True -> T 1 -> 1
2 False True -> F 2
我们最终将transform
每个时间戳转换成一个事件。
解决方案 6:
您可以pandas
通过将df_1
索引设置为时间戳字段来使索引对齐为您工作
import pandas as pd
df_1 = pd.DataFrame(
columns=["timestamp", "A", "B"],
data=[
(pd.Timestamp("2016-05-14 10:54:33"), 0.020228, 0.026572),
(pd.Timestamp("2016-05-14 10:54:34"), 0.057780, 0.175499),
(pd.Timestamp("2016-05-14 10:54:35"), 0.098808, 0.620986),
(pd.Timestamp("2016-05-14 10:54:36"), 0.158789, 1.014819),
(pd.Timestamp("2016-05-14 10:54:39"), 0.038129, 2.384590),
],
)
df_2 = pd.DataFrame(
columns=["start", "end", "event"],
data=[
(
pd.Timestamp("2016-05-14 10:54:31"),
pd.Timestamp("2016-05-14 10:54:33"),
"E1",
),
(
pd.Timestamp("2016-05-14 10:54:34"),
pd.Timestamp("2016-05-14 10:54:37"),
"E2",
),
(
pd.Timestamp("2016-05-14 10:54:38"),
pd.Timestamp("2016-05-14 10:54:42"),
"E3",
),
],
)
df_2.index = pd.IntervalIndex.from_arrays(df_2["start"], df_2["end"], closed="both")
只需设置df_1["event"]
为df_2["event"]
df_1["event"] = df_2["event"]
瞧瞧
df_1["event"]
timestamp
2016-05-14 10:54:33 E1
2016-05-14 10:54:34 E2
2016-05-14 10:54:35 E2
2016-05-14 10:54:36 E2
2016-05-14 10:54:39 E3
Name: event, dtype: object
解决方案 7:
一个选项是使用pyjanitor的conditional_join:
# pip install pyjanitor
import pandas as pd
import janitor
(df_1
.conditional_join(
df_2,
# variable arguments
# tuple is of the form:
# col_from_left_df, col_from_right_df, comparator
('timestamp', 'start', '>='),
('timestamp', 'end', '<='),
how = 'inner')
.drop(columns=['start', 'end'])
)
timestamp A B event
0 2016-05-14 10:54:33 0.020228 0.026572 E1
1 2016-05-14 10:54:34 0.057780 0.175499 E2
2 2016-05-14 10:54:35 0.098808 0.620986 E2
3 2016-05-14 10:54:36 0.158789 1.014819 E2
4 2016-05-14 10:54:39 0.038129 2.384590 E3
您可以使用参数决定连接类型 => left
,right
,或。inner
`how`
解决方案 8:
在StackOverflow 上 firelynx 的解决方案中,这表明多态性不起作用。我不得不同意 firelynx 的观点(经过大量测试)。但是,将多态性的思想与piRSquared 的 numpy 广播解决方案结合起来,它就可以起作用了!
唯一的问题是,最终,在底层,numpy 广播实际上会进行某种交叉连接,我们会过滤所有相等的元素,从而导致O(n1*n2)
内存和O(n1*n2)
性能下降。可能有人可以让这个过程在一般意义上更有效率。
我在这里发帖的原因是 firelynx 的解决方案问题被关闭,因为它是这个问题的重复,我倾向于不同意。因为这个问题和其中的答案在你有多个点属于多个间隔时不会给出解决方案,而只针对一个点属于多个间隔给出解决方案。我下面提出的解决方案确实处理了这些 nm 关系。
基本上,创建以下两个类PointInTime
并Timespan
实现多态性。
from datetime import datetime
class PointInTime(object):
doPrint = True
def __init__(self, year, month, day):
self.dt = datetime(year, month, day)
def __eq__(self, other):
if isinstance(other, self.__class__):
r = (self.dt == other.dt)
if self.doPrint:
print(f'{self.__class__}: comparing {self} to {other} (equals) gives {r}')
return (r)
elif isinstance(other, Timespan):
r = (other.start_date < self.dt < other.end_date)
if self.doPrint:
print(f'{self.__class__}: comparing {self} to {other} (Timespan in PointInTime) gives {r}')
return (r)
else:
if self.doPrint:
print(f'Not implemented... (PointInTime)')
return NotImplemented
def __repr__(self):
return "{}-{}-{}".format(self.dt.year, self.dt.month, self.dt.day)
class Timespan(object):
doPrint = True
def __init__(self, start_date, end_date):
self.start_date = start_date
self.end_date = end_date
def __eq__(self, other):
if isinstance(other, self.__class__):
r = ((self.start_date == other.start_date) and (self.end_date == other.end_date))
if self.doPrint:
print(f'{self.__class__}: comparing {self} to {other} (equals) gives {r}')
return (r)
elif isinstance (other, PointInTime):
r = self.start_date < other.dt < self.end_date
if self.doPrint:
print(f'{self.__class__}: comparing {self} to {other} (PointInTime in Timespan) gives {r}')
return (r)
else:
if self.doPrint:
print(f'Not implemented... (Timespan)')
return NotImplemented
def __repr__(self):
return "{}-{}-{} -> {}-{}-{}".format(self.start_date.year, self.start_date.month, self.start_date.day, self.end_date.year, self.end_date.month, self.end_date.day)
顺便说一句,如果您不想使用 ==,而是使用其他运算符(例如 !=、<、>、<=、>=),您可以为它们创建相应的函数(、、、、、__ne__
)。__lt__
`__gt____le__
__ge__`
您可以将其与广播结合使用,方法如下。
import pandas as pd
import numpy as np
df1 = pd.DataFrame({"pit":[(x) for x in [PointInTime(2015,1,1), PointInTime(2015,2,2), PointInTime(2015,3,3), PointInTime(2015,4,4)]], 'vals1':[1,2,3,4]})
df2 = pd.DataFrame({"ts":[(x) for x in [Timespan(datetime(2015,2,1), datetime(2015,2,5)), Timespan(datetime(2015,2,1), datetime(2015,4,1)), Timespan(datetime(2015,2,1), datetime(2015,2,5))]], 'vals2' : ['a', 'b', 'c']})
a = df1['pit'].values
b = df2['ts'].values
i, j = np.where((a[:,None] == b))
res = pd.DataFrame(
np.column_stack([df1.values[i], df2.values[j]]),
columns=df1.columns.append(df2.columns)
)
print(df1)
print(df2)
print(res)
这给出了预期的输出。
<class '__main__.PointInTime'>: comparing 2015-1-1 to 2015-2-1 -> 2015-2-5 (Timespan in PointInTime) gives False
<class '__main__.PointInTime'>: comparing 2015-1-1 to 2015-2-1 -> 2015-4-1 (Timespan in PointInTime) gives False
<class '__main__.PointInTime'>: comparing 2015-1-1 to 2015-2-1 -> 2015-2-5 (Timespan in PointInTime) gives False
<class '__main__.PointInTime'>: comparing 2015-2-2 to 2015-2-1 -> 2015-2-5 (Timespan in PointInTime) gives True
<class '__main__.PointInTime'>: comparing 2015-2-2 to 2015-2-1 -> 2015-4-1 (Timespan in PointInTime) gives True
<class '__main__.PointInTime'>: comparing 2015-2-2 to 2015-2-1 -> 2015-2-5 (Timespan in PointInTime) gives True
<class '__main__.PointInTime'>: comparing 2015-3-3 to 2015-2-1 -> 2015-2-5 (Timespan in PointInTime) gives False
<class '__main__.PointInTime'>: comparing 2015-3-3 to 2015-2-1 -> 2015-4-1 (Timespan in PointInTime) gives True
<class '__main__.PointInTime'>: comparing 2015-3-3 to 2015-2-1 -> 2015-2-5 (Timespan in PointInTime) gives False
<class '__main__.PointInTime'>: comparing 2015-4-4 to 2015-2-1 -> 2015-2-5 (Timespan in PointInTime) gives False
<class '__main__.PointInTime'>: comparing 2015-4-4 to 2015-2-1 -> 2015-4-1 (Timespan in PointInTime) gives False
<class '__main__.PointInTime'>: comparing 2015-4-4 to 2015-2-1 -> 2015-2-5 (Timespan in PointInTime) gives False
pit vals1
0 2015-1-1 1
1 2015-2-2 2
2 2015-3-3 3
3 2015-4-4 4
ts vals2
0 2015-2-1 -> 2015-2-5 a
1 2015-2-1 -> 2015-4-1 b
2 2015-2-1 -> 2015-2-5 c
pit vals1 ts vals2
0 2015-2-2 2 2015-2-1 -> 2015-2-5 a
1 2015-2-2 2 2015-2-1 -> 2015-4-1 b
2 2015-2-2 2 2015-2-1 -> 2015-2-5 c
3 2015-3-3 3 2015-2-1 -> 2015-4-1 b
与基本 Python 类型相比,拥有类的开销可能会带来额外的性能损失,但我还没有研究过这一点。
以上就是我们创建“内部”连接的方法。创建“(外部)左连接”、“(外部)右连接”和“(完整)外部”连接应该很简单。
解决方案 9:
如果 中的时间跨度df_2
不重叠,则可以使用 numpy 广播将时间戳与所有时间跨度进行比较,并确定它介于哪个时间跨度之间。然后使用argmax
确定'Event'
要分配哪个(因为最多只能有 1 个不重叠的时间跨度)。
该where
条件用于NaN
任何可能超出所有时间跨度的情况(因为argmax
无法正确处理此情况)
import numpy as np
m = ((df_1['timestamp'].to_numpy() >= df_2['start'].to_numpy()[:, None])
& (df_1['timestamp'].to_numpy() <= df_2['end'].to_numpy()[:, None]))
df_1['Event'] = df_2['event'].take(np.argmax(m, axis=0)).where(m.sum(axis=0) > 0)
print(df_1)
timestamp A B Event
0 2016-05-14 10:54:33 0.020228 0.026572 E1
1 2016-05-14 10:54:34 0.057780 0.175499 E2
2 2016-05-14 10:54:35 0.098808 0.620986 E2
3 2016-05-14 10:54:36 0.158789 1.014819 E2
4 2016-05-14 10:54:39 0.038129 2.384590 E3
解决方案 10:
有一些使用 sqlite3 的答案。我会将duckdb添加到当代工具中,以便根据范围进行合并。如果您了解一点 SQL,那么使用 duckdb 非常简单。
让我们开始并创建数据框。
import pandas as pd
import duckdb as ddb
df_1 = pd.DataFrame(
{
"timestamp": pd.to_datetime(["2016-05-14 10:54:33", "2016-05-14 10:54:34", "2016-05-14 10:54:35", "2016-05-14 10:54:36", "2016-05-14 10:54:39"]),
"A": [0.020228, 0.057780, 0.098808, 0.158789, 0.038129],
"B": [0.026572, 0.175499, 0.620986, 1.014819, 2.384590],
}
)
df_2 = pd.DataFrame(
{
"start": pd.to_datetime(["2016-05-14 10:54:31", "2016-05-14 10:54:34", "2016-05-14 10:54:38"]),
"end": pd.to_datetime(["2016-05-14 10:54:33", "2016-05-14 10:54:37", "2016-05-14 10:54:42"]),
"event": ["E1", "E2", "E3"]
}
)
然后 duckdb 让我们直接在 pandas 数据框上使用 SQL,无需在代码中转换或启动任何内容,也不需要创建连接。
result = ddb.sql("""
SELECT *
FROM df_1
LEFT JOIN df_2
ON df_1.timestamp BETWEEN df_2.start AND df_2.end
""").df()
瞧瞧
时间戳 | 一个 | 乙 | 开始 | 结尾 | 事件 | |
---|---|---|---|---|---|---|
0 | 2016-05-14 10:54:33 | 0.020228 | 0.026572 | 2016-05-14 10:54:31 | 2016-05-14 10:54:33 | E1 |
1 | 2016-05-14 10:54:34 | 0.05778 | 0.175499 | 2016-05-14 10:54:34 | 2016-05-14 10:54:37 | E2 |
2 | 2016-05-14 10:54:35 | 0.098808 | 0.620986 | 2016-05-14 10:54:34 | 2016-05-14 10:54:37 | E2 |
3 | 2016-05-14 10:54:36 | 0.158789 | 1.01482 | 2016-05-14 10:54:34 | 2016-05-14 10:54:37 | E2 |
4 | 2016-05-14 10:54:39 | 0.038129 | 2.38459 | 2016-05-14 10:54:38 | 2016-05-14 10:54:42 | E3 |
如果您注重性能,则使用极坐标数据框也可以实现同样的效果。