python - 如何查找落在每一行的时间段内的行数，同时满足其他列中的条件？

Question

我有一个示例数据框，其中包含一列名称和一列日期时间。

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

对于每一行，我试图找到日期时间在 10 小时内且名称匹配的行总数。

我已经设法用下面的代码做到了这一点，但是在更大的数据集上这需要永远。有没有更好的方法可以做到这一点？

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<x + datetime.timedelta(hours = 10))]))
    df.loc[total.index,'Total'] = total.values
df

结果:

    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

编辑:实际数据至少有 80000 行和 200 多个名称。日期列具体到第二个。，日期列包含重复的条目，其中两个不同的名称可以具有相同的日期时间，但没有一个名称具有多个相同的日期时间条目。

编辑-------------------------------------------- -

我已经标记了 Rik Kraan 的答案，尽管它在使用我自己的数据时确实产生了较慢的结果。因此，我想比较这两种方法的性能。下面运行一个测试，比较样本大小高达 50000 行，增量为 1000 行。对于我的特定用例，Rik 的解决方案看起来更快了 48/49 千行，之后原始解决方案似乎更好。

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

Answer 1

如果您的数据不是很大，请对Name 进行自合并并查询:

df['Total'] = (df.reset_index().merge(df, on='Name')
   .loc[lambda x: (x.Date_y-x.Date_x<thresh) & (x.Date_x <= x.Date_y)]
   .groupby('index').size()
)

输出:

                  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