python - Pandas DataFrame groupby overlapping intervals of variable length

Question

I am trying to group a DataFrame by 2 columns (see example below). For the first column, I want each value to belong to a group. For the second column, I want to group by overlapping intervals of unequal size.

My understanding is that pd.cut() only allows me to group by non-overlapping intervals.

Here is an example:

    0   1   2
0   0   4   1721
1   0   5   2353
2   0   6   58
3   0   7   524
4   1   1   1934
5   1   2   1318
6   1   2   1307
7   1   2   301
8   1   2   502
9   1   3   996
10  1   3   32

By grouping by column 0 and 1 I want:

0  1    2
0 [4,5] [1721,2353]
  [5,6] [2353,58]
  [6,7] [58,524]
1 [1,2] [1934,1318,1307,301,502]
  [2,3] [1318,1307,301,502,996,32]

I would then take mean or std of column 2. Any suggestion? Thanks !

Answer 1

Starting with:

    gr1  gr2   val
0     0    4  1721
1     0    5  2353
2     0    6    58
3     0    7   524
4     1    1  1934
5     1    2  1318
6     1    2  1307
7     1    2   301
8     1    2   502
9     1    3   996
10    1    3    32

First, create bins from values in gr2:

bounds = df.gr2.sort_values().unique()
bins = list(zip(bounds[:-1], bounds[1:]))

def overlapping_bins(x):
    return pd.Series([l for l in bins if l[0] <= x <= l[1]])

Then assign val values to bins:

df = pd.concat([df, df.gr2.apply(overlapping_bins).stack().reset_index(1, drop=True)], axis=1).rename(columns={0: 'bins'}).drop('gr2', axis=1)

And then .groupby() resulting bins:

df.groupby(['gr1', 'bins']).val.apply(lambda x: x.tolist())

gr1  bins  
0    (3, 4)                             [1721]
     (4, 5)                       [1721, 2353]
     (5, 6)                         [2353, 58]
     (6, 7)                          [58, 524]
1    (1, 2)       [1934, 1318, 1307, 301, 502]
     (2, 3)    [1318, 1307, 301, 502, 996, 32]
     (3, 4)                          [996, 32]