将通话数据拆分为15分钟的通话间隔

Question

我是python和pandas的新手，尽管我对间隔做了很多研究，但我还是找不到解决问题的方法，我希望有人能帮我

这是我的DF的一个样本

df = pd.DataFrame(
    data=[['Mel Gibson', 'German', '2021-9-23 14:22:38', 301 ],
          ['Jim Carrey', 'German', '2021-9-23 14:27:39', 1041 ],
          ['Mel Gibson', 'German','2021-9-24 13:33:22',12]],
    columns=['specialist', 'Language', 'Interval Start', 'status_duration']
)
df['Interval Start'] = pd.to_datetime(df['Interval Start'])

我想做的是将状态持续时间转换为15分钟的间隔，并按专家和每天进行分组

我希望的输出如下所示：

df = pd.DataFrame(
    data=[['Mel Gibson', 'German', '2021-9-23 14:15:00', 301 ],
          ['Jim Carrey', 'German', '2021-9-23 14:15:00', 141 ],
          ['Jim Carrey', 'German', '2021-9-23 14:30:00', 900 ],
          ['Mel Gibson', 'German','2021-9-24 13:30:00',12]],
    columns=['specialist', 'Language', 'Interval Start', 'status_duration']
)

因此，基本上我需要将状态持续时间中的秒划分为15分钟的间隔，直到没有剩余的持续时间

编辑：

我的原始数据如下：

    df = pd.DataFrame(
            data=[['Mel Gibson', 'German', '2021-9-23 14:22:38', 301 ],
                  ['Mel Gibson', 'German', '2021-9-23 14:27:40', 4678 ],
                  ['Mel Gibson', 'German','2021-9-24 13:33:22',12]],
            columns=['specialist', 'Language', 'Interval Start', 'status_duration']
        )
        df['Interval Start'] = pd.to_datetime(df['Interval Start'])

Henry的代码只为第一行提供了输出，跳过了第二行

也就是说，如果电话在10:35:00开始，这个间隔（10:30-10:45）不能超过600秒，因为距离开始时间只有10分钟了

Answer 1

您可以使用dt.floor()函数进行舍入：

df['Interval Start'] = df['Interval Start'].dt.floor("15min")

结果（基于您编辑的数据）：

   specialist Language      Interval Start  status_duration
0  Mel Gibson   German 2021-09-23 14:15:00              301
1  Mel Gibson   German 2021-09-23 14:15:00             4678
2  Mel Gibson   German 2021-09-24 13:30:00               12

然后我添加了一列，其中包含您期望的间隔数：

df['len'] = 1 + df['status_duration']//900

结果:

0  Mel Gibson   German 2021-09-23 14:15:00              301    1
1  Mel Gibson   German 2021-09-23 14:15:00             4678    6
2  Mel Gibson   German 2021-09-24 13:30:00               12    1

然后，您可以使用numpy.repeat()复制相应的行，并使用timedelta()列出理解以构建相应的间隔

import numpy as np
from datetime import timedelta

new_df = pd.DataFrame({'specialist': np.repeat(df['specialist'], df['len']),
                'Language': np.repeat(df['Language'], df['len']),
                'Interval Start': [el for sublist in [[x['Interval Start'] + timedelta(minutes=15*y) for y in range(0, x['len'])] for i, x in df.iterrows()] for el in sublist],
                'status_duration': [el for sublist in [([900]*(x['len']-1)+[x['status_duration']%900]) for i, x in df.iterrows()] for el in sublist]
})

结果:

   specialist Language      Interval Start  status_duration
0  Mel Gibson   German 2021-09-23 14:15:00              301
1  Mel Gibson   German 2021-09-23 14:15:00              900
1  Mel Gibson   German 2021-09-23 14:30:00              900
1  Mel Gibson   German 2021-09-23 14:45:00              900
1  Mel Gibson   German 2021-09-23 15:00:00              900
1  Mel Gibson   German 2021-09-23 15:15:00              900
1  Mel Gibson   German 2021-09-23 15:30:00              178
2  Mel Gibson   German 2021-09-24 13:30:00               12

最后，您可能需要重置索引：

new_df = new_df.reset_index(drop=True)

结果:

   specialist Language      Interval Start  status_duration
0  Mel Gibson   German 2021-09-23 14:15:00              301
1  Mel Gibson   German 2021-09-23 14:15:00              900
2  Mel Gibson   German 2021-09-23 14:30:00              900
3  Mel Gibson   German 2021-09-23 14:45:00              900
4  Mel Gibson   German 2021-09-23 15:00:00              900
5  Mel Gibson   German 2021-09-23 15:15:00              900
6  Mel Gibson   German 2021-09-23 15:30:00              178
7  Mel Gibson   German 2021-09-24 13:30:00               12

Answer 2

一种方法是利用商和status_duration的余数，explode结果，最后以秒为单位将时间相加：

ref = (df.groupby(["specialist", "Language", pd.Grouper(key="Interval Start", freq="D")], as_index=False)
         .agg(status_duration=("status_duration", lambda d: [*([900]*(d.iat[0]//900)), d.iat[0]%900]),
              Interval=("Interval Start", "first"))
         .explode("status_duration"))

ref["Interval"] = ref["Interval"].dt.floor("15min")+pd.to_timedelta(ref.groupby(ref.index).cumcount()*900, unit="sec")

print (ref)

   specialist Language status_duration            Interval
0  Jim Carrey   German             900 2021-09-23 14:15:00
0  Jim Carrey   German             141 2021-09-23 14:30:00
1  Mel Gibson   German             301 2021-09-23 14:15:00
2  Mel Gibson   German              12 2021-09-24 13:30:00