How to find number of rows that fall within a time period of each row, while satisfying criteria in other columns?

Question

I have an example dataframe with a column of names and a column of datetimes.

import random
np.random.seed(1)

numberList = ['Mark','James','Sarah']
df = pd.DataFrame({'Date':pd.date_range(start='1/1/2020', freq='BH', periods=20),
             'Name':[random.choice(numberList) for x in range(20)]})

    Date                Name
0   2020-01-01 09:00:00 James
1   2020-01-01 10:00:00 Sarah
2   2020-01-01 11:00:00 Sarah
3   2020-01-01 12:00:00 James
4   2020-01-01 13:00:00 Mark
5   2020-01-01 14:00:00 James
6   2020-01-01 15:00:00 Mark
7   2020-01-01 16:00:00 Sarah
8   2020-01-02 09:00:00 Mark
9   2020-01-02 10:00:00 Sarah
10  2020-01-02 11:00:00 Sarah
11  2020-01-02 12:00:00 Mark
12  2020-01-02 13:00:00 Sarah
13  2020-01-02 14:00:00 Sarah
14  2020-01-02 15:00:00 Mark
15  2020-01-02 16:00:00 Mark
16  2020-01-03 09:00:00 Sarah
17  2020-01-03 10:00:00 Sarah
18  2020-01-03 11:00:00 Mark
19  2020-01-03 12:00:00 Sarah

For each row I am trying to find the total number of rows that have a datetime within 10 hours after, and the name matches.

I've managed to do this with the code below, however on a much larger dataset this takes forever. Is there a better was accomplish this?

df['Total'] = 0
for i in df.Name.unique():
    df2 = df[df.Name == i]
    total = df2['Date'].apply(lambda x: len(df2[(df2.Date>=x) & (df2.Date


Result:
    Date                Name    Total
0   2020-01-01 09:00:00 James   3
1   2020-01-01 10:00:00 Sarah   3
2   2020-01-01 11:00:00 Sarah   2
3   2020-01-01 12:00:00 James   2
4   2020-01-01 13:00:00 Mark    2
5   2020-01-01 14:00:00 James   1
6   2020-01-01 15:00:00 Mark    1
7   2020-01-01 16:00:00 Sarah   1
8   2020-01-02 09:00:00 Mark    4
9   2020-01-02 10:00:00 Sarah   4
10  2020-01-02 11:00:00 Sarah   3
11  2020-01-02 12:00:00 Mark    3
12  2020-01-02 13:00:00 Sarah   2
13  2020-01-02 14:00:00 Sarah   1
14  2020-01-02 15:00:00 Mark    2
15  2020-01-02 16:00:00 Mark    1
16  2020-01-03 09:00:00 Sarah   3
17  2020-01-03 10:00:00 Sarah   2
18  2020-01-03 11:00:00 Mark    1
19  2020-01-03 12:00:00 Sarah   1

EDIT:
The actual data is at least 80000 rows and with 200+ names.
The Date column is specific up to the second.,
The Date column contains duplicate entries where two different Names can have the same datetime, but no single Name will have more than one of the same datetime entries.
EDIT-----------------------------------------------
I've marked Rik Kraan's answer although it did produce slower results when using my own data.
Because of this I wanted to compare the performance of both methods. Below runs a test of both comparing sample sizes up to 50000 rows in 1000 row increments. For my specific use case it looks like Rik's solution is faster up 48/49 thousand rows, after which the original solution seems better.
import time
import random
import datetime

Rows = []
Rik_Kraan = []
Willacya = []

for i in range(1000,50000,1000):
    
    Rows.append(i)
    
    # Creates Dataframe where number of names is 20% the length of the Dataframe.
    numberList = ["Name_"+str(j) for j in range(1,int(i*.2))]
    df_test = pd.DataFrame({'Date':pd.date_range(start='1/1/2020', freq='S', periods=i),
                 'Name':[random.choice(numberList) for x in range(i)]})
    
    # Rik_Kraan solution using masking
    start = time.time() 
    dates = df_test['Date'].values
    name = df_test['Name'].values
    df_test.assign(Total=np.sum((dates[:, None] <= (dates+pd.Timedelta(10, 'H'))) & (dates[:, None] >= dates) & (name[:, None] == name), axis=0))
    end = time.time()
    Rik_Kraan.append(end-start)

    # Original Solution
    start = time.time()
    for j in df_test.Name.unique():
        df2 = df_test[df_test.Name == j].copy()
        total = df2['Date'].apply(lambda x: len(df2[(df2.Date<=x) & (df2.Date>x - datetime.timedelta(hours = 1))]))
        df_test.loc[total.index,'Total'] = total.values
    end = time.time()
    Willacya.append(end-start)   
    
pd.DataFrame({'Num_Rows':Rows,'Rik_Kraan':Rik_Kraan,'Willacya':Willacya}).set_index('Num_Rows').plot()

user_4238408 · Answer

If your data is not to big, do a self-merge on Name and query:

df['Total'] = (df.reset_index().merge(df, on='Name')
   .loc[lambda x: (x.Date_y-x.Date_x


Output:
                  Date   Name  Total
0  2020-01-01 09:00:00  James      3
1  2020-01-01 10:00:00  Sarah      3
2  2020-01-01 11:00:00  Sarah      2
3  2020-01-01 12:00:00  James      2
4  2020-01-01 13:00:00   Mark      2
5  2020-01-01 14:00:00  James      1
6  2020-01-01 15:00:00   Mark      1
7  2020-01-01 16:00:00  Sarah      1
8  2020-01-02 09:00:00   Mark      4
9  2020-01-02 10:00:00  Sarah      4
10 2020-01-02 11:00:00  Sarah      3
11 2020-01-02 12:00:00   Mark      3
12 2020-01-02 13:00:00  Sarah      2
13 2020-01-02 14:00:00  Sarah      1
14 2020-01-02 15:00:00   Mark      2
15 2020-01-02 16:00:00   Mark      1
16 2020-01-03 09:00:00  Sarah      3
17 2020-01-03 10:00:00  Sarah      2
18 2020-01-03 11:00:00   Mark      1
19 2020-01-03 12:00:00  Sarah      1

user_12891614 · Answer

We can also use numpy broadcasting. Essentially, for each row we want to count how many rows within a time interval of 10 hours have the same name .

First make numpy arrays of the columns of interest

dates = df['Date'].values
name = df['Name'].values

Second create a mask by comparing the rows with each other. This yields an array of shape number_of_rows * number_of_rows

(dates[:, None] <= (dates+pd.Timedelta(10, 'H'))) & (dates[:, None] >= dates) & (name[:, None] == name)

Finally We can take the sum of each columns, which provide us with the total number of identical names in the next 10 hours, and assign this to a new column.

df.assign(Total=np.sum((dates[:, None] <= (dates+pd.Timedelta(10, 'H'))) & (dates[:, None] >= dates) & (name[:, None] == name), axis=0))

user_12891614 · Answer

You can create shifted columns that include the names within the following 10 hours. If we compare these columns with the original Name, we get multiple boolean columns indicating if the name in the Name column is present in the following rows. A simple sum over the rows then gets the intended Total column.

# Make copy of the original dataframe and set the Date column as index
df_shifted = df.set_index('Date')

# Loop over the coming 10 hours and create shifted columns
for i in range(1,10):
    df_shifted[i] = df_shifted.shift(periods=-i, freq='H')['Name']
    # Compare with the original Name column
    df_shifted[i] = df_shifted[i] == df_shifted['Name']

# Set the original Name column to True (as we want to count these names as well)
df_shifted['Name'] = True

# Assign new total column to the original dataframe
df.assign(Total=df2.sum(axis=1).values)

Your solution had a CPU time of 24.9 ms

CPU times: user 24.9 ms, sys: 0 ns, total: 24.9 ms
Wall time: 21.8 ms

My proposed solution is a little quicker:

CPU times: user 9.76 ms, sys: 4.41 ms, total: 14.2 ms
Wall time: 12.1 ms

Hope this is helpful

How to find number of rows that fall within a time period of each row, while satisfying criteria in other columns?

3 Answers