时间戳

首页 > 解决方案 > 如何使用 pandas 数据框连接多个列的列值在一定范围内的两个数据框？

python - 如何使用 pandas 数据框连接多个列的列值在一定范围内的两个数据框？

问题描述

参考此处提出的相同问题join dataframes for single column，现在我想将其扩展到另外两列，例如：

df1：

price_start  price_end  year_start  year_end  score
         10         50        2001      2005     20
         60        100        2001      2005     50
         10         50        2006      2010     30

df2:

Price  year
   10  2001
   70  2002
   50  2010

现在我想将 df1 的分数映射到 df2 值。

预期输出：

price year score

10 2001 20

70 2002 50

50 2010 30

标签： pythonpandas

解决方案

解决方案 1：小数据集的简单解决方案

对于小型数据集，您可以交叉连接df1和df2by .merge()，然后使用指定条件按价格在范围内和年份在范围内的条件进行过滤.query()，如下所示：

(df1.merge(df2, how='cross')
    .query('(Price >= price_start) & (Price <= price_end) & (year >= year_start) & (year <= year_end)')
    [['Price', 'year', 'score']]
)

如果您的 Pandas 版本早于 1.2.0（2020 年 12 月发布）并且不支持合并how='cross'，您可以使用：

(df1.assign(key=1).merge(df2.assign(key=1), on='key').drop('key', axis=1)
    .query('(Price >= price_start) & (Price <= price_end) & (year >= year_start) & (year <= year_end)')
    [['Price', 'year', 'score']]
)

结果：

   Price  year  score
0     10  2001     20
4     70  2002     50
8     50  2010     30

解决方案 2：大型数据集的 Numpy 解决方案

对于大型数据集和性能问题，您可以使用numpy 广播（而不是交叉连接和过滤）来加快执行时间：

我们寻找Price在df2价格范围内df1和year在df2年份范围内df1：

d2_P = df2.Price.values
d2_Y = df2.year.values

d1_PS = df1.price_start.values
d1_PE = df1.price_end.values
d1_YS = df1.year_start.values
d1_YE = df1.year_end.values

i, j = np.where((d2_P[:, None] >= d1_PS) & (d2_P[:, None] <= d1_PE) & (d2_Y[:, None] >= d1_YS) & (d2_Y[:, None] <= d1_YE))

pd.DataFrame(
    np.column_stack([df1.values[j], df2.values[i]]),
    columns=df1.columns.append(df2.columns)
)[['Price', 'year', 'score']]

结果：

   Price  year  score
0     10  2001     20
1     70  2002     50
2     50  2010     30

性能比较

第 1 部分：比较每个 3 行的原始数据集：

解决方案1：

%%timeit
(df1.merge(df2, how='cross')
    .query('(Price >= price_start) & (Price <= price_end) & (year >= year_start) & (year <= year_end)')
    [['Price', 'year', 'score']]
)

5.91 ms ± 87.3 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

解决方案2：

%%timeit
d2_P = df2.Price.values
d2_Y = df2.year.values

d1_PS = df1.price_start.values
d1_PE = df1.price_end.values
d1_YS = df1.year_start.values
d1_YE = df1.year_end.values

i, j = np.where((d2_P[:, None] >= d1_PS) & (d2_P[:, None] <= d1_PE) & (d2_Y[:, None] >= d1_YS) & (d2_Y[:, None] <= d1_YE))

pd.DataFrame(
    np.column_stack([df1.values[j], df2.values[i]]),
    columns=df1.columns.append(df2.columns)
)[['Price', 'year', 'score']]

703 µs ± 9.29 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

基准总结：5.91 ms vs 703 µs，快 8.4 倍

第 2 部分：比较具有 3,000 和 30,000 行的数据集：

数据设置：

df1a = pd.concat([df1] * 1000, ignore_index=True)
df2a = pd.concat([df2] * 10000, ignore_index=True)

解决方案1：

%%timeit
(df1a.merge(df2a, how='cross')
    .query('(Price >= price_start) & (Price <= price_end) & (year >= year_start) & (year <= year_end)')
    [['Price', 'year', 'score']]
)

27.5 s ± 3.24 s per loop (mean ± std. dev. of 7 runs, 1 loop each)

解决方案2：

%%timeit
d2_P = df2a.Price.values
d2_Y = df2a.year.values

d1_PS = df1a.price_start.values
d1_PE = df1a.price_end.values
d1_YS = df1a.year_start.values
d1_YE = df1a.year_end.values

i, j = np.where((d2_P[:, None] >= d1_PS) & (d2_P[:, None] <= d1_PE) & (d2_Y[:, None] >= d1_YS) & (d2_Y[:, None] <= d1_YE))

pd.DataFrame(
    np.column_stack([df1a.values[j], df2a.values[i]]),
    columns=df1a.columns.append(df2a.columns)
)[['Price', 'year', 'score']]

3.83 s ± 136 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

基准总结：27.5 s vs 3.83 s，快 7.2 倍

推荐阅读