基于另一个数据帧中的重叠范围和相同范围的总和值，从2列映射范围

Question

我有两个数据集（df1和df2）的值，它们都有一定的范围（Start和End）

我想用df2上对应的重叠值范围（Start/End）的Num列中的值来注释第一个（df1）

示例：df1中的第一行范围为0-2300000，由于2300000低于df2中第一行的End，并且整个范围0-2300000与62920-121705338的范围重叠，因此将用^{{}注释。同样，第2行df1的范围2300000-5400000与第2行62920-121705338的范围重叠，第2行也将被注释为Num{}

但是，对于df1的最后一行，范围包含df2中的两行，因此需要在df2的最后两行Num中输出和

所需的输出将是df3

df1.head()

|Start    |End      |Tag    |
|---------|---------|-------|
|0        |2300000  |gneg45 |   
|2300000  |5400000  |gpos25 |
|143541857|200000000|gneg34 |

df2.head()

| Start   |   End   |  Num   |
|---------|---------|--------|
|62920    |121705338|  3     |   
|143541857|147901334|  2     |
|147901760|151020217|  5     |


df3 = 

|Start    |End      |Num    |
|---------|---------|-------|
|0        |2300000  |3      |   
|2300000  |5400000  |3      |
|143541857|200000000|7      |

我试图创建一个键，并基于一系列列创建一个键，但没有任何效果

提前谢谢

Answer 1

根据您的描述，您正在查找df1和df2中的重叠范围，以便df1从df2中获取Num值

为了制定重叠范围条件的条件，让我们如下说明非重叠范围的相反条件：

要么：

                                         |<       >| 
                                      df2.Start       .df2.End
           |<      ->| 
        df1.Start       df1.End

或：

                 |<       >| 
              df2.Start       .df2.End
                                             |<      ->| 
                                          df1.Start       df1.End

该非重叠范围条件可表述为：

（df1.End<；df2.Start）或（df1.Start>；df2.End）

因此，重叠范围条件，作为相反的，是上述条件的否定，即：

（df1.End<；df2.Start）|（df1.Start>；df2.End））

这相当于：

（df1.End>；=df2.Start）及；（df1.Start<；=df2.End）

[注：我们通过考虑对立面来推导重叠条件并得到否定，因为重叠条件有更多的场景。有4种情况：（1）df1覆盖整个df2范围和更多；（2）df1完全包含在df2范围内；（3）仅在左端重叠；（4）仅在右端重叠。我们可以通过我们的方法简化逻辑。]

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

步骤1:对于小数据集，您可以通过^{}交叉连接df1和df2，然后使用^{}按重叠条件过滤，如下所示：

df3 = (df1.merge(df2, how='cross', suffixes=('_df1', '_df2'))
          .query('(End_df1 >= Start_df2) & (Start_df1 <= End_df2)')
          .rename({'Start_df1': 'Start', 'End_df1': 'End'}, axis=1)
          [['Start', 'End', 'Num']]
      )

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

df3 = (df1.assign(key=1).merge(df2.assign(key=1), on='key', suffixes=('_df1', '_df2')).drop('key', axis=1)
          .query('(End_df1 >= Start_df2) & (Start_df1 <= End_df2)')
          .rename({'Start_df1': 'Start', 'End_df1': 'End'}, axis=1)
          [['Start', 'End', 'Num']]
      )

中间结果：

print(df3)

       Start        End  Num
0          0    2300000    3
3    2300000    5400000    3
7  143541857  200000000    2
8  143541857  200000000    5

第2步：通过{a3}和{a4}对相同范围（相同{}和{}）的{}值求和：

df3 = df3.groupby(['Start', 'End'])['Num'].sum().reset_index()

结果：

print(df3)

       Start        End  Num
0          0    2300000    3
1    2300000    5400000    3
2  143541857  200000000    7

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

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

第1步：

d1_S = df1.Start.to_numpy()
d1_E = df1.End.to_numpy()
d2_S = df2.Start.to_numpy()
d2_E = df2.End.to_numpy()

# filter for overlapping range condition and get the respective row indexes of `df1`, `df2` in `i` and `j`
i, j = np.where((d1_E[:, None] >= d2_S) & (d1_S[:, None] <= d2_E))

df3 = pd.DataFrame(
          np.column_stack([df1.values[i], df2.values[j]]),
          columns=df1.columns.append(df2.columns + '_df2')
      )

中间结果：

print(df3)

       Start        End     Tag  Start_df2    End_df2 Num_df2
0          0    2300000  gneg45      62920  121705338       3
1    2300000    5400000  gpos25      62920  121705338       3
2  143541857  200000000  gneg34  143541857  147901334       2
3  143541857  200000000  gneg34  147901760  151020217       5

第2步：通过{a3}和{a4}对相同范围（相同{}和{}）的{}值求和：

df3 = df3.groupby(['Start', 'End'])['Num_df2'].sum().reset_index(name='Num')

结果：

print(df3)

       Start        End  Num
0          0    2300000    3
1    2300000    5400000    3
2  143541857  200000000    7