如何在不完全匹配的情况下对区间索引进行分组

Question

我有两张数据。它们看起来是这样的：

df_a
     Framecount                                        probability
0           0.0  [0.00019486549333333332, 4.883635666666667e-06...
1           1.0  [0.00104359155, 3.9232405e-05, 0.0015722045000...
2           2.0  [0.00048501002666666667, 1.668179e-05, 0.00052...
3           3.0  [4.994969500000001e-05, 4.0931635e-07, 0.00011...
4           4.0  [0.0004808829, 5.389742e-05, 0.002522127933333...
..          ...                                                ...
906       906.0  [1.677140566666667e-05, 1.1745095666666665e-06...
907       907.0  [1.5164155000000002e-05, 7.66629575e-07, 0.000...
908       908.0  [8.1334184e-05, 0.00012675669636333335, 0.0028...
909       909.0  [0.00014893802999999998, 1.0407592500000001e-0...
910       910.0  [4.178489e-05, 2.17477925e-06, 0.02094931, 0.0...

和：

df_b
     start    stop
0     12.12   12.47
1     13.44   20.82
2     20.88   29.63
3     31.61   33.33
4     33.44   42.21
..      ...     ...
228  880.44  887.92
229  888.63  892.07
230  892.13  895.30
231  895.31  900.99
232  907.58  908.35

当df_a.probability处于df_b.start和df_b.stop之间时，我想将df_a.Framecount合并到df_b.start。df_a.probability的聚合统计量应该是mean，但我遇到了错误，因为df_a.probability是dtype np数组。

我正在使用@TrentonMcKinney提供的代码：

import pandas as pd
import numpy as np

# setup df with start and stop ranges
data = {'start': [12.12, 13.44, 20.88, 31.61, 33.44, 880.44, 888.63, 892.13, 895.31, 907.58], 'stop': [12.47, 20.82, 29.63, 33.33, 42.21, 887.92, 892.07, 895.3, 900.99, 908.35]}
df = pd.DataFrame(data)

# setup sample df_a with Framecount as fc, and probability as prob
np.random.seed(365)
df_a = pd.DataFrame({'fc': range(911), 'prob': np.random.randint(1, 100, (911, 14)).tolist()})

# this will convert the column to np.arrays instead of lists; the remainder of the code works regardless
# df_a.prob = df_a.prob.map(np.array)

# create an IntervalIndex from df start and stop
idx = pd.IntervalIndex.from_arrays(df.start, df.stop, closed='both')

这非常有用，除非在这样的情况下，开始和停止的时间都在同一秒钟之内，比如df_b的第一行，开始和停止分别是12.12和12.47。当发生这种情况时，我只想用最近的Framecount值返回df_a.probability值。在本例中，第一个df_b开始/停止索引为12.12-12.47，因为它是第二个，所以没有df_a.Framecount值。所以，当df_a.probability df_a.Framecount == 12时，我想返回==数组。

Answer 1

这可能是我所期望的更长的代码片段，但它可以满足您的需要。也许有更简单的选择，我没有想过。我使用了您提供的代码重新生成问题。

df_a.prob = df_a.prob.map(np.array)    
idx = pd.IntervalIndex.from_arrays(df.start, df.stop, closed='both')
probs=[]
for row, i in enumerate(idx):
     #here, for each intervalIndex we are creating a boolean series showing whether framecount is in IntervalIndex.
     series_bool=df_a.fc.apply(lambda a: a in i) 
     if any(series_bool):
          #if fc is in the range of interval index, we simply take the mean of the zipped list. here zip() solves the problem of taking the mean of np.array dtype objects.
          probs.append([np.mean(k) for k in zip(*df_a.iloc[series_bool[series_bool].index].prob)])
     else:
          #if fc is not in the range of IntervalIndex, i simply rounded the start number and added that probability to the probs list.
          dfa_idx=int(round(df.loc[row,"start"]))
          probs.append(df_a.loc[dfa_idx, "prob"])

现在，我们可以将问题列表与df_b合并：

df['probability']=probs

最后，使用您提供的代码，df_b如下所示：

    start    stop                                              probs
0   12.12   12.47  [61, 83, 62, 72, 25, 32, 82, 35, 43, 10, 30, 5...
1   13.44   20.82  [49.285714285714285, 57.142857142857146, 51.42...
2   20.88   29.63  [42.666666666666664, 42.55555555555556, 46.0, ...
3   31.61   33.33  [87.5, 49.0, 46.5, 54.5, 75.0, 47.0, 24.0, 40....
4   33.44   42.21  [48.55555555555556, 66.22222222222223, 45.7777...
5  880.44  887.92  [51.857142857142854, 50.57142857142857, 63.714...
6  888.63  892.07  [45.25, 23.5, 67.25, 68.0, 38.25, 47.25, 50.25...
7  892.13  895.30  [61.333333333333336, 44.0, 43.333333333333336,...
8  895.31  900.99  [68.2, 44.6, 50.8, 35.2, 53.2, 40.4, 34.8, 77....
9  907.58  908.35  [17.0, 78.0, 24.0, 33.0, 88.0, 3.0, 43.0, 2.0,...