在Pandas中查找区间的交集

Question

我有两个数据框

df_a=

     Start Stop Value
    0  0     100  0.0
    1  101   200  1.0
    2  201  1000  0.0

df_b=
       Start Stop Value
    0  0     50 0.0
    1  51   300 1.0
    2  301 1000  0.0

我想生成一个DataFrame，其中包含由Start和Stop标识的时间间隔，这些时间间隔在df_a和df_b中的Value相同。对于每个时间间隔，我想存储：Value是否相同，以及在df_a和df_b中的值是什么。

df_out=
  Start Stop SameValue Value_dfA Value_dfB
      0    50    1          0       0
      51   100   0          0       1
      101  200   1          1       1
      201  300   0          0       1
    [...]

- 00__00__00

---

你是如何得到“102”这个起始值的？ - AChampion

这是上一个区间的上限101之后的下一个值。 - Guybrush

考虑到2个自由度df_a和df_b，前一个区间的上限难道不仅是100吗？ - AChampion

也许 pandas.merge_asof 对于这个问题会有所帮助？（https://pandas.pydata.org/pandas-docs/stable/generated/pandas.merge_asof.html）。 - Guybrush

@Guybrush，那没有意义，但不用担心，原帖的作者已经澄清了。 - AChampion

Answer 1

不确定这是否是最佳方法，但您可以使用reindex、join、groupby和agg来获取您的间隔，例如：

使用reindex()将每个df扩展，以便索引为范围的每个值（从Start到Stop），并使用pad填充值：

In []:
df_a_expanded = df_a.set_index('Start').reindex(range(max(df_a['Stop'])+1)).fillna(method='pad')
df_a_expanded

Out[]:
         Stop  Value
Start               
0       100.0    0.0
1       100.0    0.0
2       100.0    0.0
3       100.0    0.0
4       100.0    0.0
...
997    1000.0    0.0
998    1000.0    0.0
999    1000.0    0.0
1000   1000.0    0.0

[1001 rows x 2 columns]

In []:
df_b_expanded = df_b.set_index('Start').reindex(range(max(df_b['Stop'])+1)).fillna(method='pad')

将两个扩展的 dfs 连接起来：

In []:
df = df_a_expanded.join(df_b_expanded, lsuffix='_dfA', rsuffix='_dfB').reset_index()
df

Out[]:
      Start  Stop_dfA  Value_dfA  Stop_dfB  Value_dfB
0         0     100.0        0.0      50.0        0.0
1         1     100.0        0.0      50.0        0.0
2         2     100.0        0.0      50.0        0.0
3         3     100.0        0.0      50.0        0.0
4         4     100.0        0.0      50.0        0.0
...

注意：您可以忽略“Stop”列，也可以在上一步中删除它们。

没有标准的方法可以仅按连续值（类似于itertools.groupby）进行分组，因此需要使用cumsum()技巧：

In []:
groups = (df[['Value_dfA', 'Value_dfB']] != df[['Value_dfA', 'Value_dfB']].shift()).any(axis=1).cumsum()
g = df.groupby([groups, 'Value_dfA', 'Value_dfB'], as_index=False)

现在您可以通过使用min，max聚合组来获得所需的结果：

In []:
df_out = g['Start'].agg({'Start': 'min', 'Stop': 'max'})
df_out

Out[]:
   Value_dfA  Value_dfB  Start  Stop
0        0.0        0.0      0    50
1        0.0        1.0     51   100
2        1.0        1.0    101   200
3        0.0        1.0    201   300
4        0.0        0.0    301  1000

现在您只需要添加SameValue列，如果需要，可以对列进行排序，以获得所需的精确输出：

In []:
df_out['SameValue'] = (df_out['Value_dfA'] == df_out['Value_dfB'])*1
df_out[['Start', 'Stop', 'SameValue', 'Value_dfA', 'Value_dfB']]

Out[]:
   Start  Stop  SameValue  Value_dfA  Value_dfB
0      0    50          1        0.0        0.0
1     51   100          0        0.0        1.0
2    101   200          1        1.0        1.0
3    201   300          0        0.0        1.0
4    301  1000          1        0.0        0.0

这假设两个数据框的范围相同，否则您需要处理使用 join() 时会得到的 NaN。

- AChampion

Answer 2

我找到了一种方法，但不确定它是否是最有效的。你有输入数据：

import pandas as pd
dfa = pd.DataFrame({'Start': [0, 101, 201], 'Stop': [100, 200, 1000], 'Value': [0., 1., 0.]})
dfb = pd.DataFrame({'Start': [0, 51, 301], 'Stop': [50, 300, 1000], 'Value': [0., 1., 0.]})

首先，我会创建 df_out 的 Start 和 Stop 列，代码如下：

df_out = pd.DataFrame({'Start': sorted(set(dfa['Start'])|set(dfb['Start'])), 
                       'Stop': sorted(set(dfa['Stop'])|set(dfb['Stop']))})

要获取列名为 Value_dfA（以及Value_dfB）中与右侧范围（Start，Stop）相关联的dfa（和dfb）的值，我会执行以下操作：

df_out['Value_dfA'] = df_out['Start'].apply(lambda x: dfa['Value'][dfa['Start'] <= x].iloc[-1])
df_out['Value_dfB'] = df_out['Start'].apply(lambda x: dfb['Value'][dfb['Start'] <= x].iloc[-1])

要获取列SameValue，请执行以下操作：

df_out['SameValue'] = df_out.apply(lambda x: 1 if x['Value_dfA'] == x['Value_dfB'] else 0,axis=1)

如果有必要，您可以使用以下方式重新排列列：

：

df_out = df_out[['Start', 'Stop', 'SameValue', 'Value_dfA', 'Value_dfB']]

您的输出结果是：

   Start  Stop  SameValue  Value_dfA  Value_dfB
0      0    50          1        0.0        0.0
1     51   100          0        0.0        1.0
2    101   200          1        1.0        1.0
3    201   300          0        0.0        1.0
4    301  1000          1        0.0        0.0

- Ben.T

---

@AChampion，如果你只是在dfb['Stop']中将300替换为200，确实存在问题，但是在dfb中201到300的间隔没有值，因为下一个开始是在301。在这种情况下，它确实不起作用。但是，如果间隔是连续的，那么你还必须将dfb['Start']中的301替换为201，在这种情况下，它就可以工作了 :) - Ben.T

你是对的，抱歉，不用理会。已删除评论。 - AChampion

其实我认为你指出我的回答中的这个问题是正确的，以防其他人有缺失间隔的数据。我想我们可以添加一些代码来填充缺失的间隔以防止中断。 - Ben.T

Answer 3

这里有一个快速计算重叠区间的答案（回答了标题中的问题）：

from io import StringIO    
import pandas as pd    
from ncls import NCLS    

c1 = StringIO("""Start Stop Value
0     100  0.0
101   200  1.0
201  1000  0.0""")

c2 = StringIO("""Start Stop Value
0     50 0.0
51   300 1.0
301 1000  0.0""")

df1 = pd.read_table(c1, sep="\s+")
df2 = pd.read_table(c2, sep="\s+")

ncls = NCLS(df1.Start.values, df1.Stop.values, df1.index.values)

x1, x2 = ncls.all_overlaps_both(df2.Start.values, df2.Stop.values, df2.index.values)

df1 = df1.reindex(x2).reset_index(drop=True)
df2 = df2.reindex(x1).reset_index(drop=True)

# print(df1)
# print(df2)

df = df1.join(df2, rsuffix="2")

print(df)
#    Start  Stop  Value  Start2  Stop2  Value2
# 0      0   100    0.0       0     50     0.0
# 1      0   100    0.0      51    300     1.0
# 2    101   200    1.0      51    300     1.0
# 3    201  1000    0.0      51    300     1.0
# 4    201  1000    0.0     301   1000     0.0

使用这个最终的数据框，获取你所需的结果应该很简单（但留给读者作为练习）。

请参阅NCLS以获取区间重叠数据结构。

- The Unfun Cat

Answer 4

我有一个O(nlog(n))的解决方案，其中n是df_a和df_b的行数之和。具体步骤如下：将两个数据框的'value'列分别重命名为'value_a'和'value_b'，然后将df_b附加到df_a中。

df = df_a.append(df_b)

按照 start 列对 df 进行排序。

df = df.sort_values('start')

生成的数据框将如下所示：

 start  stop    value_a value_b
0   0   100     0.0      NaN
0   0   50      NaN      0.0
1   51  300     NaN      1.0
1   101 200     1.0      NaN
2   201 1000    0.0      NaN
2   301 1000    NaN      0.0

向前填充缺失值：

df = df.fillna(method='ffill')

计算 same_value 列：

df['same_value'] = df['value_a'] == df['value_b']

重新计算 stop 列：

df.stop = df.start.shift(-1)

你将会得到你想要的数据框（除了第一行和最后一行，这很容易修复）：

 start   stop value_a value_b   same_value
0   0     0.0   0.0   NaN     False
0   0     51.0  0.0   0.0     True
1   51    101.0 0.0   1.0     False
1   101   201.0 1.0   1.0     True
2   201   301.0 0.0   1.0     False
2   301   NaN   0.0   0.0     True

- Lokesh