• 当前位置：首页
  • IT科技
  • 如何连接两个列值在一定范围内的数据框？

如何连接两个列值在一定范围内的数据框？

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 ...

问题描述：

给定两个数据框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()

瞧瞧

时间戳一个乙开始结尾事件
02016-05-14 10:54:330.0202280.0265722016-05-14 10:54:312016-05-14 10:54:33E1
12016-05-14 10:54:340.057780.1754992016-05-14 10:54:342016-05-14 10:54:37E2
22016-05-14 10:54:350.0988080.6209862016-05-14 10:54:342016-05-14 10:54:37E2
32016-05-14 10:54:360.1587891.014822016-05-14 10:54:342016-05-14 10:54:37E2
42016-05-14 10:54:390.0381292.384592016-05-14 10:54:382016-05-14 10:54:42E3

如果您注重性能，则使用极坐标数据框也可以实现同样的效果。

相关推荐
置顶 政府信创国产化的10大政策解读
政府信创国产化的10大政策解读
  政府信创国产化的10大政策解读一、信创国产化的背景与意义信创国产化，即信息技术应用创新国产化，是当前中国信息技术领域的一个重要发展方向。其核心在于通过自主研发和创新，实现信息技术应用的自主可控，减少对外部技术的依赖，并规避潜在的技术制裁和风险。随着全球信息技术竞争的加剧，以及某些国家对中国在科技领域的打压，信创国产化显...
工程项目管理   1590  
置顶 2025年20款好用的项目管理软件推荐，项目管理提效的20个工具和技巧
2025年20款好用的项目管理软件推荐，项目管理提效的20个工具和技巧
  为什么项目管理通常仍然耗时且低效？您是否还在反复更新电子表格、淹没在便利贴中并参加每周更新会议？这确实是耗费时间和精力。借助软件工具的帮助，您可以一目了然地全面了解您的项目。如今，国内外有足够多优秀的项目管理软件可以帮助您掌控每个项目。什么是项目管理软件？项目管理软件是广泛行业用于项目规划、资源分配和调度的软件。它使项...
项目管理软件   1361  
信创产品在政府采购中的占比分析
信创产品在政府采购中的占比分析
  信创产品在政府采购中的占比分析随着信息技术的飞速发展以及国家对信息安全重视程度的不断提高，信创产业应运而生并迅速崛起。信创，即信息技术应用创新，旨在实现信息技术领域的自主可控，减少对国外技术的依赖，保障国家信息安全。政府采购作为推动信创产业发展的重要力量，其对信创产品的采购占比情况备受关注。这不仅关系到信创产业的发展前...
信创和国产化的区别   18  
国货国用信创对科技创新的推动作用
国货国用信创对科技创新的推动作用
  信创，即信息技术应用创新产业，旨在实现信息技术领域的自主可控，摆脱对国外技术的依赖。近年来，国货国用信创发展势头迅猛，在诸多领域取得了显著成果。这一发展趋势对科技创新产生了深远的推动作用，不仅提升了我国在信息技术领域的自主创新能力，还为经济社会的数字化转型提供了坚实支撑。信创推动核心技术突破信创产业的发展促使企业和科研...
信创工作   18  
信创技术对中小企业的影响与价值
信创技术对中小企业的影响与价值
  信创技术，即信息技术应用创新产业，旨在实现信息技术领域的自主可控与安全可靠。近年来，信创技术发展迅猛，对中小企业产生了深远的影响，带来了诸多不可忽视的价值。在数字化转型的浪潮中，中小企业面临着激烈的市场竞争和复杂多变的环境，信创技术的出现为它们提供了新的发展机遇和支撑。信创技术对中小企业的影响技术架构变革信创技术促使中...
信创国产化   19  
热门文章
项目管理软件
政府信创国产化的10大政策解读
2025soft
2025年20款好用的项目管理软件推荐，项目管理提效的20个工具和技巧
img020
使用 Python 'Requests' 模块的代理
img044
10个项目管理常见问题解答：包括难点和解决方法
项目管理软件
项目延期的原因有哪些？如何有效预防和管理？
项目里程碑计划模板
项目里程碑计划模板怎么写?设定项目里程碑的5个方法
项目管理软件
项目管理指南：制定项目实施计划的6大步骤有哪些？
项目管理软件有哪些？
热门标签
更多>
IPD流程 项目管理 IT项目管理 敏捷开发 项目管理软件 Scrum 敏捷项目管理 IPD流程管理 项目风险管理 项目管理工具 项目经理 极限编程  产品管理  项目经理职责 项目路线图 项目进度管理 团队管理 测试管理工具 项目预算管理 PMP认证 瀑布式管理 项目范围管理 IT科技 项目管理平台 工程项目管理 项目资源管理 pmp证书考试
云禅道AD
禅道项目管理软件

云端的项目管理软件

尊享禅道项目软件收费版功能

无需维护，随时随地协同办公

内置subversion和git源码管理

每天备份，随时转为私有部署

免费试用