在pandas中按范围合并/连接的最佳方法

Question

在pandas中按范围合并/连接的最佳方法

pythonpandasnumpyjoin

46

我经常使用pandas通过范围条件进行合并(join)。

例如，如果有两个数据框:

A (A_id, A_value)

B (B_id,B_low, B_high, B_name)

这两个数据框都很大，大小大约相同(假设每个数据框有200万条记录)。

我想在A和B之间进行内部连接(inner join)，使得A_value在B_low和B_high之间。

使用SQL语法，即为:

SELECT *
FROM A,B
WHERE A_value between B_low and B_high

而在Pandas中，唯一的方法（不使用循环的方法）是在两个表中都创建一个虚拟列，在其上进行连接（等同于交叉连接），然后过滤掉不需要的行。这听起来很繁琐和复杂:

A['dummy'] = 1
B['dummy'] = 1
Temp = pd.merge(A,B,on='dummy')
Result = Temp[Temp.A_value.between(Temp.B_low,Temp.B_high)]

我另外一个想法是对每个A的值应用一个B的搜索函数，使用B[(x>=B.B_low) & (x<=B.B_high)]掩码，但这似乎也很低效并且可能需要索引优化。

有没有更优雅和/或高效的方法来执行此操作？

- Dimgold

2

这个问答可能会有帮助。 - Andras Deak -- Слава Україні

1

看起来他们使用了类似于我自己建议的方法（虚拟列、笛卡尔积和掩码过滤器）。令人惊讶的是，没有内置的解决方案。 - Dimgold

1

你有没有看过被采纳的答案呢？不要从 Stack Overflow 上的问题中学习。虽然也许我没有意识到答案做了同样的事情，如果是这样的话，抱歉 :) - Andras Deak -- Слава Україні

1

看起来是一个更加优化内存的解决方案，但是并不优雅 :( - Dimgold

嗯，merge函数不接受任何函数参数的事实表明可能没有优雅的解决方案。但我对pandas了解甚少，所以希望有专家能够出现并证明相反的观点 :) - Andras Deak -- Слава Україні

就我所知，你的SQL语句在概念上与你所述的Pandas解决方案是相同的，只是更加高效。 - J. Doe

6个回答

12

我不确定这是否更有效，但您可以像以下方式使用sql和pandas（灵感来源于这个问题）：

conn = sqlite3.connect(":memory:") 
df2 = pd.DataFrame(np.random.randn(10, 5), columns=["col1", "col2", "col3", "col4", "col5"])
df1 = pd.DataFrame(np.random.randn(10, 5), columns=["col1", "col2", "col3", "col4", "col5"])
df1.to_sql("df1", conn, index=False)
df2.to_sql("df2", conn, index=False)
qry = "SELECT * FROM df1, df2 WHERE df1.col1 > 0 and df1.col1<0.5"
tt = pd.read_sql_query(qry,conn)

您可以根据您的应用程序需要调整查询。

- Adonis

也许这是做同样的事情的简单方法？from pandasql import sqldf qry = "SELECT * FROM df1, df2 WHERE df1.col1 > 0 and df1.col1<0.5" df = sqldf(qry) - undefined

2

我不知道它有多高效，但有人写了一个包装器，可以让你在pandas对象中使用SQL语法。这被称为pandasql。文档明确说明支持连接操作。至少这可能更容易阅读，因为SQL语法非常易读。

- baloo

2

条件连接来自pyjanitor，可能有助于提高抽象和便利性；

# pip install pyjanitor
import pandas as pd
import janitor

内连接

A.conditional_join(B, 
                   ('A_value', 'B_low', '>='), 
                   ('A_value', 'B_high', '<=')
                  )

   A_id  A_value  B_id  B_low  B_high
0     0        5     0      0      10
1     3       35     1     30      40
2     3       35     2     30      50
3     4       45     2     30      50

左连接

A.conditional_join(
       B, 
       ('A_value', 'B_low', '>='), 
       ('A_value', 'B_high', '<='), 
       how = 'left'
    )

    A_id  A_value  B_id  B_low  B_high
0      0        5   0.0    0.0    10.0
1      1       15   NaN    NaN     NaN
2      2       25   NaN    NaN     NaN
3      3       35   1.0   30.0    40.0
4      3       35   2.0   30.0    50.0
5      4       45   2.0   30.0    50.0
6      5       55   NaN    NaN     NaN
7      6       65   NaN    NaN     NaN
8      7       75   NaN    NaN     NaN
9      8       85   NaN    NaN     NaN
10     9       95   NaN    NaN     NaN

- sammywemmy

我遇到了以下错误： “无法将 '<PandasArray> [5、15、25、35、35、35、45、45、45、55、55、55、55、65、65、65、65、75、75、75、75] 长度为21，dtype为int64' 解释为数据类型”有什么想法吗？ - Quixotic22

嗨@Quixotic22，最近发布了pyjanitor的新版本; 您不应再遇到该错误。如果您遇到任何问题，可以在GitHub页面上留下问题。 - sammywemmy

1

lets take a simple example:

df=pd.DataFrame([2,3,4,5,6],columns=['A'])

返回

现在让我们定义第二个数据框。

df2=pd.DataFrame([1,6,2,3,5],columns=['B_low'])
df2['B_high']=[2,8,4,6,6]

结果为

    B_low   B_high
0   1       2
1   6       8
2   2       4
3   3       6
4   5       6

我们开始吧；我们希望输出为索引 3 和 A 值 5。

df.where(df['A']>=df2['B_low']).where(df['A']<df2['B_high']).dropna()

结果是

    A
3   5.0

- suvy

那不是连接(join)，只是堆叠(stacking)。 - Dimgold

1

我知道这是一个老问题，但对于新手来说，现在有pandas.merge_asof函数可以执行基于最接近匹配的连接。

如果您想要进行合并，以便将一个DataFrame (df_right) 的一列放置在另一个DataFrame (df_left) 的两列之间，则可以执行以下操作：

df_left = pd.DataFrame({
    "time_from": [1, 4, 10, 21],
    "time_to": [3, 7, 15, 27]
})

df_right = pd.DataFrame({
    "time": [2, 6, 16, 25]
})

df_left

  time_from time_to
0        1       3
1        4       7
2       10      15
3       21      27


df_right

  time
0    2
1    6
2   16
3   25

首先，找到最接近但比左侧DataFrame的左边界（time_from）更大的正确DataFrame的匹配项：

merged = pd.merge_asof(
    left=df_1,
    right=df_2.rename(columns={"time": "candidate_match_1"}),
    left_on="time_from",
    right_on="candidate_match_1",
    direction="forward"
)

merged

  time_from time_to candidate_match_1
0        1       3                 2
1        4       7                 6
2        10      15               16
3        21      27               25

正如您所看到的，索引2中的候选匹配是错误的，因为16不在10和15之间。

然后，在右DataFrame中查找最接近但小于左DataFrame右边界（time_to）的匹配项：

merged = pd.merge_asof(
    left=merged,
    right=df_2.rename(columns={"time": "candidate_match_2"}),
    left_on="time_to",
    right_on="candidate_match_2",
    direction="backward"
)

merged

  time_from time_to candidate_match_1   candidate_match_2
0        1        3                2                   2
1        4        7                6                   6
2        10      15               16                   6
3        21      27               25                  25

最后，保留候选匹配相同的匹配项，这意味着右侧DataFrame的值在左侧DataFrame的2列值之间：

merged["match"] = None
merged.loc[merged["candidate_match_1"] == merged["candidate_match_2"], "match"] = \
    merged.loc[merged["candidate_match_1"] == merged["candidate_match_2"], "candidate_match_1"]

merged

  time_from time_to candidate_match_1   candidate_match_2   match
0        1       3                 2                   2       2
1        4       7                 6                   6       6
2        10     15                16                   6    None
3        21     27                25                   25     25

- Theodore Samuel

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- piRSquared · Accepted Answer

设置
考虑数据框 A 和 B

A = pd.DataFrame(dict(
        A_id=range(10),
        A_value=range(5, 105, 10)
    ))
B = pd.DataFrame(dict(
        B_id=range(5),
        B_low=[0, 30, 30, 46, 84],
        B_high=[10, 40, 50, 54, 84]
    ))

A

   A_id  A_value
0     0        5
1     1       15
2     2       25
3     3       35
4     4       45
5     5       55
6     6       65
7     7       75
8     8       85
9     9       95

B

   B_high  B_id  B_low
0      10     0      0
1      40     1     30
2      50     2     30
3      54     3     46
4      84     4     84

numpy
最简单的方法是使用numpy广播。
我们寻找每个实例中A_value大于等于B_low并且同时A_value小于等于B_high。

a = A.A_value.values
bh = B.B_high.values
bl = B.B_low.values

i, j = np.where((a[:, None] >= bl) & (a[:, None] <= bh))

pd.concat([
    A.loc[i, :].reset_index(drop=True),
    B.loc[j, :].reset_index(drop=True)
], axis=1)

   A_id  A_value  B_high  B_id  B_low
0     0        5      10     0      0
1     3       35      40     1     30
2     3       35      50     2     30
3     4       45      50     2     30

为了回应评论并提供类似于左连接的东西，我附加了与A不匹配的部分。

pd.concat([
    A.loc[i, :].reset_index(drop=True),
    B.loc[j, :].reset_index(drop=True)
], axis=1).append(
    A[~np.in1d(np.arange(len(A)), np.unique(i))],
    ignore_index=True, sort=False
)

    A_id  A_value  B_id  B_low  B_high
0      0        5   0.0    0.0    10.0
1      3       35   1.0   30.0    40.0
2      3       35   2.0   30.0    50.0
3      4       45   2.0   30.0    50.0
4      1       15   NaN    NaN     NaN
5      2       25   NaN    NaN     NaN
6      5       55   NaN    NaN     NaN
7      6       65   NaN    NaN     NaN
8      7       75   NaN    NaN     NaN
9      8       85   NaN    NaN     NaN
10     9       95   NaN    NaN     NaN