I have a very large dataframe that includes a categorical column with multiple categories and an epoch column associating each category with a number of epochs. I am trying to determine all of the epoch ranges where ALL of the categories overlap. The data is not always measured in a consistent manner, so the epochs do not necessarily match 1 to 1. My thought was to make a table with columns for epoch and each category and have a row for each epoch within the timeframe and merge my data to the nearest epoch to find where all categories are overlapping, but this is obviously a horribly inefficient way to do it. Below is a sample dataframe.
type epoch time_diff
0 0 1722549639000 3000
1 0 1722549642000 3000
2 0 1722549651000 9000
3 0 1722549654000 3000
4 0 1722549657000 3000
5 0 1722549687000 30000
6 0 1722549690000 3000
7 0 1722549693000 3000
8 0 1722549705000 12000
9 1 1722549633000 3000
10 1 1722549651000 18000
11 1 1722549654000 3000
12 1 1722549657000 3000
13 1 1722549669000 12000
14 1 1722549672000 3000
15 1 1722549675000 3000
16 1 1722549678000 3000
17 1 1722549681000 3000
18 1 1722549684000 3000
19 1 1722549702000 18000
20 2 1722549633000 3000
21 2 1722549636000 3000
22 2 1722549639000 3000
23 2 1722549642000 3000
24 2 1722549645000 3000
25 2 1722549651000 6000
26 2 1722549654000 3000
27 2 1722549657000 3000
28 2 1722549660000 3000
29 2 1722549663000 3000
30 2 1722549666000 3000
31 3 1722549678000 30000
32 3 1722549708000 30000
33 3 1722549738000 30000
34 3 1722549768000 30000
35 3 1722549798000 30000
36 3 1722549828000 30000
37 3 1722549858000 30000
38 3 1722549888000 30000
39 3 1722549918000 30000
40 3 1722549948000 30000
41 3 1722549978000 30000
2