How to groupby interval index, aggregate mean on a list of lists, and join to another dataframe?

• The error occurs because idx.get_indexer_non_unique(df_vid['Framecount']) creates a tuple, and you can't groupby a tuple, in this way.
  • df_vid.groupby(idx.get_indexer_non_unique(df_vid['Framecount'])[0]) selecting the first array in the tuple would work.
• idx.get_indexer(df_a.fc) will result in an array, with the index of the interval that fc belongs to. If there is not a matching interval, the index will show as -1.
• df_a.groupby(idx.get_indexer(df_a.fc)) groups by an array of indices.
• .agg({'prob': list}) aggreages all the lists, for each fc, into a list.
  • The result for each group, is a list of lists
• .prob.map(np.mean) returns the the overall mean, for all lists in the group
• .prob.apply(lambda x: [np.mean(v) for v in x]) returns a list of means, for each list.
• No 'fc' value falls into the bin for 12.12 - 12.47.

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')

This will create a list of means on axis=0

dfg = df_a.groupby(idx.get_indexer(df_a.fc)).agg({'prob': list}).prob.apply(lambda x: np.mean(x, axis=0))

# join df with dfg
dfj = df.join(dfg)

# display(dfj) for list of means
    start    stop                                                                                  prob
0   12.12   12.47                                                                                   NaN
1   13.44   20.82  [49.3, 57.1, 51.4, 45.9, 47.1, 45.9, 45.9, 55.3, 32.6, 48.0, 42.0, 45.0, 50.4, 54.4]
2   20.88   29.63  [42.7, 42.6, 46.0, 45.9, 54.1, 55.9, 50.1, 55.2, 51.7, 54.0, 37.6, 60.9, 49.2, 45.6]
3   31.61   33.33  [87.5, 49.0, 46.5, 54.5, 75.0, 47.0, 24.0, 40.5, 52.5, 21.0, 51.0, 72.5, 34.5, 50.5]
4   33.44   42.21  [48.6, 66.2, 45.8, 64.7, 43.1, 69.0, 54.4, 52.1, 52.6, 59.6, 51.1, 42.1, 43.3, 38.0]
5  880.44  887.92  [51.9, 50.6, 63.7, 47.7, 51.3, 34.9, 51.3, 53.0, 53.4, 65.1, 38.6, 49.4, 48.1, 44.1]
6  888.63  892.07  [45.2, 23.5, 67.2, 68.0, 38.2, 47.2, 50.2, 75.8, 35.2, 46.8, 55.0, 57.5, 44.2, 78.0]
7  892.13  895.30  [61.3, 44.0, 43.3, 36.3, 63.7, 89.7, 51.7, 57.0, 50.0, 68.7, 80.7, 46.3, 66.7, 11.3]
8  895.31  900.99  [68.2, 44.6, 50.8, 35.2, 53.2, 40.4, 34.8, 77.4, 61.0, 35.2, 26.0, 47.8, 30.4, 55.4]
9  907.58  908.35     [17.0, 78.0, 24.0, 33.0, 88.0, 3.0, 43.0, 2.0, 36.0, 48.0, 8.0, 87.0, 36.0, 34.0]

This will create a single mean for each group

dfg = df_a.groupby(idx.get_indexer(df_a.fc)).agg({'prob': list}).prob.map(np.mean)

# join df with dfg
dfj = df.join(dfg)

# display(dfj) for overall mean
    start    stop       prob
0   12.12   12.47        NaN
1   13.44   20.82  47.877551
2   20.88   29.63  49.380952
3   31.61   33.33  50.428571
4   33.44   42.21  52.182540
5  880.44  887.92  50.224490
6  888.63  892.07  52.303571
7  892.13  895.30  55.047619
8  895.31  900.99  47.171429
9  907.58  908.35  38.357143

I would love someone to come up with a solution that doesn't involve loops, but since everything is sorted I think the performance will actually not be that bad (linear in the lengths of the two dataframes and no memory overhead). I don't know the precise specification of your dataframes, so I'll create some sample ones first.

n_a = 11
df_a = pd.DataFrame(
    {"Framecount": list(range(n_a)), "probability": np.random.rand(n_a)}
)

n_b = 6
start = np.linspace(0, n_a, n_b)
end = start + n_a / (n_b - 1) - 1e-5
df_b = pd.DataFrame({"start": start, "end": end, "mean": [np.nan] * n_b})

print(df_a)
    Framecount  probability
0            0     0.099412
1            1     0.492661
2            2     0.043000
3            3     0.382923
4            4     0.208177
5            5     0.110007
6            6     0.369756
7            7     0.324723
8            8     0.702838
9            9     0.182167
10          10     0.578837

print(df_b)
   start       end  mean
0    0.0   2.19999  NaN
1    2.2   4.39999  NaN
2    4.4   6.59999  NaN
3    6.6   8.79999  NaN
4    8.8  10.99999  NaN
5   11.0  13.19999  NaN

Now I'll loop through the dataframes, aggregating all values between the current start and end and assign in the corresponding row in df_b:

i = j = 0
while i < n_a and j < n_b:
    # seek to next row of df_b where start <= df_a[i]
    while i < n_a and df_a.loc[i, "Framecount"] < df_b.loc[j, "start"]:
        i += 1

    accum = 0
    count = 0
    while i < n_a and df_a.loc[i, "Framecount"] < df_b.loc[j, "end"]:
        accum += df_a.loc[i, "probability"]
        count += 1
        i += 1

    df_b.loc[j, "mean"] = accum / count
    j += 1

print(df_b)
   start       end      mean
0    0.0   2.19999  0.211691
1    2.2   4.39999   0.29555
2    4.4   6.59999  0.239882
3    6.6   8.79999  0.513781
4    8.8  10.99999  0.380502
5   11.0  13.19999      NaN