Pandas: Cross the time intervals between groups by

Question

I have a dataframe of the following form

import pandas as pd

Out[1]:
df = pd.DataFrame({'id':[1,2,3,4,5],
          'group':['A','A','A','B','B'],
          'start':['2012-08-19','2012-08-22','2013-08-19','2012-08-19','2013-08-19'],
          'end':['2012-08-28','2013-09-13','2013-08-19','2012-12-19','2014-08-19']})

     id group       start         end
0   1     A  2012-08-19  2012-08-28
1   2     A  2012-08-22  2013-09-13
2   3     A  2013-08-19  2013-08-21
3   4     B  2012-08-19  2012-12-19
4   5     B  2013-08-19  2014-08-19

For a given row in my data frame, I would like to count the number of elements in the same group that have an overlapping time interval.

For example, in group A, id 2 varies from August 22, 2012 to September 13, 2013, and therefore overlaps between id 1 (from August 19, 2012 to August 28, 2012), and also from id 3 (August 19, 2013 to August 21, 2013) for qty 2.

Conversely, there are no matches between elements in group B

So, for my dataframe example above, I would like to create something like

Out[2]:
   id group       start         end  count
0   1     A  2012-08-19  2012-08-28      1
1   2     A  2012-08-22  2013-09-13      2
2   3     A  2013-08-19  2013-08-21      1
3   4     B  2012-08-19  2012-12-19      0
4   5     B  2013-08-19  2014-08-19      0

I could "convince" this, but I would like to know if there is a more efficient way for Pandas to do this.

Thank you in advance for your help.

Answer 1

, , ... , . , O (M ^ 2) , , .

In [11]: def interval_overlaps(a, b):
    ...:     return min(a["end"], b["end"]) - max(a["start"], b["start"]) > np.timedelta64(-1)


In [12]: def count_overlaps(df1):
    ...:     return sum(interval_overlaps(df1.iloc[i], df1.iloc[j]) for i in range(len(df1) - 1) for j in range(i, len(df1)) if i < j)

In [13]: df.groupby("group").apply(count_overlaps)
Out[13]:
group
A    2
B    0
dtype: int64

- .

---

: count_overlaps , , agg :

In [21]: def count_overlaps(df1):
    ...:     return pd.Series([df1.apply(lambda x: interval_overlaps(x, df1.iloc[i]), axis=1).sum() - 1 for i in range(len(df1))], df1.index)

In [22]: df.groupby("group").apply(count_overlaps)
Out[22]:
group
A      0    1
       1    2
       2    1
B      3    0
       4    0
dtype: int64

In [22]: df["count"] = df.groupby("group").apply(count_overlaps).values

In [23]: df
Out[23]:
         end group  id      start  count
0 2012-08-28     A   1 2012-08-19      1
1 2013-09-13     A   2 2012-08-22      2
2 2013-08-19     A   3 2013-08-19      1
3 2012-12-19     B   4 2012-08-19      0
4 2014-08-19     B   5 2013-08-19      0

Answer 2

" " ish, :

df .

df.start = pd.to_datetime(df.start)
df.end = pd.to_datetime(df.end)

df['count'] = df.apply(lambda row: len(df[ ( ( (row.start <= df.start) & (df.start <= row.end) ) \
                                            | ( (df.start <= row.start) & (row.start <= df.end) ) )
                           & (row.id != df.id) & (row.group == df.group) ]),axis=1)

Answer 3

import datetime
def ol(a, b):
    l=[]
    for x in b:
        l.append(max(0, int(min(a[1], x[1]) - max(a[0], x[0])>=datetime.timedelta(minutes=0))))
    return sum(l)


df['New']=list(zip(df.start,df.end))
df['New2']=df.group.map(df.groupby('group').New.apply(list))
df.apply(lambda x : ol(x.New,x.New2),axis=1)-1

Out[495]: 
0    1
1    2
2    1
3    0
4    0
dtype: int64

Delay

#My method 
df.apply(lambda x : ol(x.New,x.New2),axis=1)-1

100 loops, best of 3: 5.39 ms per loop

#@Andy Method 
df.groupby("group").apply(count_overlaps)    
10 loops, best of 3: 23.5 ms per loop

#@Nathan Method

df.apply(lambda row: len(df[ ( ( (row.start <= df.start) & (df.start <= row.end) ) \
                       | ( (df.start <= row.start) & (row.start <= df.end) ) )
                       & (row.id != df.id) & (row.group == df.group) ]),axis=1)

10 loops, best of 3: 25.8 ms per loop