我有两个值的数据集(df1和df2)，它们都有一定的范围(Start和End)。
我想用df2上相应重叠值范围(Start/End)的列Num中的值来注释第一个(df1)。
示例：df1中的第一行的范围为0至2300000，由于2300000低于df2中第一行的End，并且整个范围0至2300000与62920至121705338的范围重叠，因此将用Num3进行注释。同样，df1的行2的范围2300000-5400000与62920-121705338的范围重叠，行2也将用Num-3进行注释。
然而，在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 |
我尝试了pandasmerge创建一个键，并尝试了基于一系列列的query，但没有真正奏效。
提前感谢！！
根据您的描述，您正在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:对于小数据集，可以通过.merge()交叉连接df1和df2，然后使用.query()根据重叠条件进行过滤，如下所示：
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:用.groupby()和.sum():将相同范围(相同的Start和End)的Num值相加
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广播(而不是交叉连接和过滤)来加快执行时间：
步骤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:用.groupby()和.sum():将相同范围(相同的Start和End)的Num值相加
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
基于@SeaBean的逻辑，一个选项是使用pyjanitor的conditional_join，然后是groupby:
# pip install pyjanitor
import pandas as pd
import janitor
(
df1
.conditional_join(
# add suffix here
# to avoid MultiIndex, which happens
# if the columns overlap
df2.add_suffix('_y'),
# column from left, column from right, comparator
('Start', 'End_y', '<='),
('End', 'Start_y', '>='))
.rename(columns={'Num_y':'Num'})
.groupby(['Start', 'End'], as_index = False)
.Num
.sum()
)
Start End Num
0 0 2300000 3
1 2300000 5400000 3
2 143541857 200000000 7