如何在Pandas中计算重叠的日期时间间隔？

Question

我是一名有用的助手，可以为您翻译文本。

以下是一个包含两个日期时间列的DataFrame：

    start               end
0   01.01.2018 00:47    01.01.2018 00:54
1   01.01.2018 00:52    01.01.2018 01:03
2   01.01.2018 00:55    01.01.2018 00:59
3   01.01.2018 00:57    01.01.2018 01:16
4   01.01.2018 01:00    01.01.2018 01:12
5   01.01.2018 01:07    01.01.2018 01:24
6   01.01.2018 01:33    01.01.2018 01:38
7   01.01.2018 01:34    01.01.2018 01:47
8   01.01.2018 01:37    01.01.2018 01:41
9   01.01.2018 01:38    01.01.2018 01:41
10  01.01.2018 01:39    01.01.2018 01:55

我想要计算在给定时间结束之前同时活跃的间隔数（换句话说：每一行与其余行重叠的次数）。

例如，从00:47到00:52只有一个间隔是活动的，从00:52到00:54有两个间隔是活动的，从00:54到00:55又只有一个间隔是活动的，以此类推。

我尝试将列堆叠在一起，按日期排序，并通过整个数据帧进行迭代，为每个“开始”+1计数器和每个“结束”-1。它起作用，但在我原始的数据帧上，我有几百万行，迭代需要很长时间 - 我需要找到更快的方法。

我的原始基本且不太好的代码：

import pandas as pd
import numpy as np

df = pd.read_csv('something.csv', sep=';')

df = df.stack().to_frame()
df = df.reset_index(level=1)
df.columns = ['status', 'time']
df = df.sort_values('time')
df['counter'] = np.nan
df = df.reset_index().drop('index', axis=1)

print(df.head(10))

提供：

    status  time                counter
0   start   01.01.2018 00:47    NaN
1   start   01.01.2018 00:52    NaN
2   stop    01.01.2018 00:54    NaN
3   start   01.01.2018 00:55    NaN
4   start   01.01.2018 00:57    NaN
5   stop    01.01.2018 00:59    NaN
6   start   01.01.2018 01:00    NaN
7   stop    01.01.2018 01:03    NaN
8   start   01.01.2018 01:07    NaN
9   stop    01.01.2018 01:12    NaN

并且：

counter = 0

for index, row in df.iterrows():

    if row['status'] == 'start':
        counter += 1
    else:
        counter -= 1
    df.loc[index, 'counter'] = counter

最终输出：

        status  time                counter
    0   start   01.01.2018 00:47    1.0
    1   start   01.01.2018 00:52    2.0
    2   stop    01.01.2018 00:54    1.0
    3   start   01.01.2018 00:55    2.0
    4   start   01.01.2018 00:57    3.0
    5   stop    01.01.2018 00:59    2.0
    6   start   01.01.2018 01:00    3.0
    7   stop    01.01.2018 01:03    2.0
    8   start   01.01.2018 01:07    3.0
    9   stop    01.01.2018 01:12    2.0

有没有不使用 iterrows() 的方法来完成这件事情呢？

提前感谢！

- chestnut

Answer 1

使用 Series.cumsum 与 Series.map（或 Series.replace）：

new_df = df.melt(var_name = 'status',value_name = 'time').sort_values('time')
new_df['counter'] = new_df['status'].map({'start':1,'end':-1}).cumsum()
print(new_df)
   status                time  counter
0   start 2018-01-01 00:47:00        1
1   start 2018-01-01 00:52:00        2
11    end 2018-01-01 00:54:00        1
2   start 2018-01-01 00:55:00        2
3   start 2018-01-01 00:57:00        3
13    end 2018-01-01 00:59:00        2
4   start 2018-01-01 01:00:00        3
12    end 2018-01-01 01:03:00        2
5   start 2018-01-01 01:07:00        3
15    end 2018-01-01 01:12:00        2
14    end 2018-01-01 01:16:00        1
16    end 2018-01-01 01:24:00        0
6   start 2018-01-01 01:33:00        1
7   start 2018-01-01 01:34:00        2
8   start 2018-01-01 01:37:00        3
9   start 2018-01-01 01:38:00        4
17    end 2018-01-01 01:38:00        3
10  start 2018-01-01 01:39:00        4
19    end 2018-01-01 01:41:00        3
20    end 2018-01-01 01:41:00        2
18    end 2018-01-01 01:47:00        1
21    end 2018-01-01 01:55:00        0

---

我们也可以使用numpy.cumsum：

new_df['counter'] = np.where(new_df['status'].eq('start'),1,-1).cumsum()

- ansev

---

非常感谢您！我的初始循环需要超过2个小时才能处理完我的数据。而您的解决方案只需要不到一秒钟的时间。赞！ - chestnut

Answer 2

我只是把所有东西整合在一起，帮助像我这样的新手。

import pandas as pd
import numpy as np

df = pd.read_csv('startend.csv', sep=',' , index_col=0 , infer_datetime_format=True)
df = df.stack().to_frame()
df = df.reset_index(level=1)
df.columns = ['status', 'time']
df = df.reset_index().drop('index', axis=1)
df['time'] = pd.to_datetime(df['time'])
df = df.sort_values('time')

new_df = pd.melt(df,id_vars="time",value_name="status")
new_df.drop(columns=["variable"],inplace=True)
new_df['counter'] = np.where(new_df['status'].eq('start'),1,-1).cumsum()
print(new_df)

- Ranganayakulu Konduri