使用Pandas将Salesforce通话数据按15分钟间隔拆分统计

实现代码 #

import pandas as pd

# 初始化原始DataFrame
df = pd.DataFrame(
    data=[['Donald Trump', 'German', '2021-9-23 14:28:00','2021-9-23 14:58:00', 1800 ],
          ['Donald Trump', 'German', '2021-9-23 14:58:01','2021-9-23 15:00:05', 124 ],
          ['Donald Trump', 'German', '2021-9-24 10:05:00','2021-9-24 10:15:30', 630 ],
          ['Monica Lewinsky', 'German', '2021-9-24 10:05:00','2021-9-24 10:05:30', 30 ]],
    columns=['specialist', 'language', 'interval_start', 'interval_end', 'status_duration']
)
df['interval_start'] = pd.to_datetime(df['interval_start'])
df['interval_end'] = pd.to_datetime(df['interval_end'])

# 定义单条记录的15分钟区间拆分逻辑
def split_row(row):
    output = []
    step = pd.Timedelta(minutes=15)
    # 取当前记录起始时间所属的15分钟区间的起点
    curr_interval = row['interval_start'].floor('15min')
    while curr_interval < row['interval_end']:
        # 计算当前15分钟区间的结束时间
        curr_interval_end = curr_interval + step
        # 计算当前区间内实际被占用的时长
        actual_start = max(curr_interval, row['interval_start'])
        actual_end = min(curr_interval_end, row['interval_end'])
        duration = int((actual_end - actual_start).total_seconds())
        output.append({
            'specialist': row['specialist'],
            'language': row['language'],
            'interval': curr_interval.strftime('%Y-%m-%d %H:%M:%S'),
            'status_duration': duration
        })
        curr_interval = curr_interval_end
    return output

# 执行拆分
split_data = df.apply(split_row, axis=1).tolist()
# 扁平化数据并转成DataFrame
result = pd.DataFrame([item for sublist in split_data for item in sublist])
# 按专家、语言、15分钟区间分组汇总时长
result = result.groupby(['specialist', 'language', 'interval'], as_index=False)['status_duration'].sum()

print(result)

输出结果 #

运行上述代码后得到的结果和需求格式匹配：

specialist language            interval  status_duration
0    Donald Trump   German  2021-09-23 14:15:00              120
1    Donald Trump   German  2021-09-23 14:30:00              900
2    Donald Trump   German  2021-09-23 14:45:00              899
3    Donald Trump   German  2021-09-23 15:00:00                5
4    Donald Trump   German  2021-09-24 10:00:00              600
5    Donald Trump   German  2021-09-24 10:15:00               30
6  Monica Lewinsky   German  2021-09-24 10:00:00               30

内容的提问来源于stack exchange，提问作者segababa