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

ddarikpa  于 2023-09-29  发布在  其他
关注(0)|答案(6)|浏览(135)

我经常使用pandas通过范围条件进行合并（连接）。
例如，如果有两个 Dataframe ：

A（A_id，A_value）
B（B_id，B_low，B_high，B_name）

它们很大并且大小大致相同（假设每个记录为2 M）。
我想在A和B之间建立一个内部连接，所以A_value将在B_low和B_high之间。
使用的SQL语法为：

  1. SELECT *
  2. FROM A,B
  3. WHERE A_value between B_low and B_high

这将是非常简单，简短和有效的。
同时，在pandas中，唯一的方法（不是使用我发现的循环）是在两个表中创建一个虚拟列，在其上连接（相当于交叉连接），然后过滤掉不需要的行。这听起来既沉重又复杂：

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

我的另一个解决方案是通过使用B[(x>=B.B_low) & (x<=B.B_high)]掩码在每个A值上应用B上的搜索函数，但这听起来效率也很低，可能需要索引优化。
是否有更优雅和/或更有效的方法来执行此操作？

pandas

6条答案

按热度按时间
sycxhyv7

sycxhyv71#

设置

考虑嵌套A和B

  1. A = pd.DataFrame(dict(
  2.         A_id=range(10),
  3.         A_value=range(5, 105, 10)
  4.     ))
  5. B = pd.DataFrame(dict(
  6.         B_id=range(5),
  7.         B_low=[0, 30, 30, 46, 84],
  8.         B_high=[10, 40, 50, 54, 84]
  9.     ))
  11. A
  13.    A_id  A_value
  14. 0     0        5
  15. 1     1       15
  16. 2     2       25
  17. 3     3       35
  18. 4     4       45
  19. 5     5       55
  20. 6     6       65
  21. 7     7       75
  22. 8     8       85
  23. 9     9       95
  25. B
  27.    B_high  B_id  B_low
  28. 0      10     0      0
  29. 1      40     1     30
  30. 2      50     2     30
  31. 3      54     3     46
  32. 4      84     4     84

numpy

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

  1. a = A.A_value.values
  2. bh = B.B_high.values
  3. bl = B.B_low.values
  5. i, j = np.where((a[:, None] >= bl) & (a[:, None] <= bh))
  7. pd.concat([
  8.     A.loc[i, :].reset_index(drop=True),
  9.     B.loc[j, :].reset_index(drop=True)
  10. ], axis=1)
  12.    A_id  A_value  B_high  B_id  B_low
  13. 0     0        5      10     0      0
  14. 1     3       35      40     1     30
  15. 2     3       35      50     2     30
  16. 3     4       45      50     2     30

为了解决注解问题并给予类似于左连接的东西，我附加了A中不匹配的部分。

  1. pd.concat([
  2.     A.loc[i, :].reset_index(drop=True),
  3.     B.loc[j, :].reset_index(drop=True)
  4. ], axis=1).append(
  5.     A[~np.in1d(np.arange(len(A)), np.unique(i))],
  6.     ignore_index=True, sort=False
  7. )
  9.     A_id  A_value  B_id  B_low  B_high
  10. 0      0        5   0.0    0.0    10.0
  11. 1      3       35   1.0   30.0    40.0
  12. 2      3       35   2.0   30.0    50.0
  13. 3      4       45   2.0   30.0    50.0
  14. 4      1       15   NaN    NaN     NaN
  15. 5      2       25   NaN    NaN     NaN
  16. 6      5       55   NaN    NaN     NaN
  17. 7      6       65   NaN    NaN     NaN
  18. 8      7       75   NaN    NaN     NaN
  19. 9      8       85   NaN    NaN     NaN
  20. 10     9       95   NaN    NaN     NaN
展开查看全部
赞(0）分享  回复(0）举报  2023-09-29
qyswt5oh

qyswt5oh2#

不确定这样是否更有效，但是你可以直接使用sql（例如从模块sqlite3）和pandas（灵感来自this question），比如：

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

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

赞(0）分享  回复(0）举报  2023-09-29
pzfprimi

pzfprimi3#

我不知道它的效率有多高，但有人写了一个 Package 器，允许你对pandas对象使用SQL语法。这就是所谓的pandasql。文档明确声明支持连接。这可能至少更容易阅读，因为SQL语法非常易读。

赞(0）分享  回复(0）举报  2023-09-29
cyvaqqii

cyvaqqii4#

来自pyjanitor的conditional_join以高性能和高效的方式解决了这个问题，使用二进制搜索，而不是交叉连接：

  1. # pip install pyjanitor
  2. import pandas as pd
  3. import janitor

内部联接

  1. A.conditional_join(B, 
  2.                    ('A_value', 'B_low', '>='), 
  3.                    ('A_value', 'B_high', '<='),
  4.                    # you may get more performance 
  5.                    # with numba
  6.                    use_numba=False
  7.                   )
  9.    A_id  A_value  B_id  B_low  B_high
  10. 0     0        5     0      0      10
  11. 1     3       35     1     30      40
  12. 2     3       35     2     30      50
  13. 3     4       45     2     30      50

LEFT JOIN

  1. A.conditional_join(
  2.        B, 
  3.        ('A_value', 'B_low', '>='), 
  4.        ('A_value', 'B_high', '<='), 
  5.        how = 'left'
  6.     )
  8.     A_id  A_value  B_id  B_low  B_high
  9. 0      0        5   0.0    0.0    10.0
  10. 1      1       15   NaN    NaN     NaN
  11. 2      2       25   NaN    NaN     NaN
  12. 3      3       35   1.0   30.0    40.0
  13. 4      3       35   2.0   30.0    50.0
  14. 5      4       45   2.0   30.0    50.0
  15. 6      5       55   NaN    NaN     NaN
  16. 7      6       65   NaN    NaN     NaN
  17. 8      7       75   NaN    NaN     NaN
  18. 9      8       85   NaN    NaN     NaN
  19. 10     9       95   NaN    NaN     NaN
展开查看全部
赞(0）分享  回复(0）举报  2023-09-29
g2ieeal7

g2ieeal75#

举一个简单的例子：

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

返回

  1. A
  2. 0   2
  3. 1   3
  4. 2   4
  5. 3   5
  6. 4   6

现在让我们定义第二个 Dataframe

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

导致

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

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

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

导致

  1. A
  2. 3   5.0
展开查看全部
赞(0）分享  回复(0）举报  2023-09-29
xqkwcwgp

xqkwcwgp6#

我知道这是一个老问题，但对于新手来说，现在有一个pandas.merge_asof函数，它根据最接近的匹配执行连接。
如果你想合并一个DataFrame（df_right）的一个列在另一个DataFrame（df_left）的两个列之间，你可以这样做：

  1. df_left = pd.DataFrame({
  2.     "time_from": [1, 4, 10, 21],
  3.     "time_to": [3, 7, 15, 27]
  4. })
  6. df_right = pd.DataFrame({
  7.     "time": [2, 6, 16, 25]
  8. })
  10. df_left
  12.   time_from time_to
  13. 0        1       3
  14. 1        4       7
  15. 2       10      15
  16. 3       21      27
  18. df_right
  20.   time
  21. 0    2
  22. 1    6
  23. 2   16
  24. 3   25

首先，找到右DataFrame的匹配项，这些匹配项最接近左DataFrame的左边界（time_from），但比左DataFrame的左边界（time_from）大：

  1. merged = pd.merge_asof(
  2.     left=df_1,
  3.     right=df_2.rename(columns={"time": "candidate_match_1"}),
  4.     left_on="time_from",
  5.     right_on="candidate_match_1",
  6.     direction="forward"
  7. )
  9. merged
  11.   time_from time_to candidate_match_1
  12. 0        1       3                 2
  13. 1        4       7                 6
  14. 2        10      15               16
  15. 3        21      27               25

正如你所看到的，索引2中的候选匹配是错误的，因为16不在10和15之间。
然后，找到右DataFrame的匹配项，这些匹配项最接近但小于左DataFrame的右边界（time_to）：

  1. merged = pd.merge_asof(
  2.     left=merged,
  3.     right=df_2.rename(columns={"time": "candidate_match_2"}),
  4.     left_on="time_to",
  5.     right_on="candidate_match_2",
  6.     direction="backward"
  7. )
  9. merged
  11.   time_from time_to candidate_match_1   candidate_match_2
  12. 0        1        3                2                   2
  13. 1        4        7                6                   6
  14. 2        10      15               16                   6
  15. 3        21      27               25                  25

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

  1. merged["match"] = None
  2. merged.loc[merged["candidate_match_1"] == merged["candidate_match_2"], "match"] = \
  3.     merged.loc[merged["candidate_match_1"] == merged["candidate_match_2"], "candidate_match_1"]
  5. merged
  7.   time_from time_to candidate_match_1   candidate_match_2   match
  8. 0        1       3                 2                   2       2
  9. 1        4       7                 6                   6       6
  10. 2        10     15                16                   6    None
  11. 3        21     27                25                   25     25
展开查看全部
赞(0）分享  回复(0）举报  2023-09-29
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com