R语言时间日志补全：填充时间间隙生成Type=0行

嘿，这个需求我之前处理过好几次，不管是用SQL还是Python Pandas都能轻松搞定，给你两种常用的解决方案：

假设你的数据表名为time_logs，我们可以用窗口函数LAG()来检测时间间隙，然后生成填充行：

WITH gap_detection AS (
    SELECT
        LOG_START_DT,
        LOG_END_DT,
        Type,
        -- 获取上一行的结束时间，按开始时间排序保证顺序正确
        LAG(LOG_END_DT) OVER (ORDER BY LOG_START_DT) AS prev_end_dt
    FROM time_logs
)
-- 先输出原始数据行
SELECT LOG_START_DT, LOG_END_DT, Type
FROM gap_detection
UNION ALL
-- 输出间隙填充行：Type设为0，起止时间为上一行结束到当前行开始
SELECT
    prev_end_dt AS LOG_START_DT,
    LOG_START_DT AS LOG_END_DT,
    0 AS Type
FROM gap_detection
-- 只保留确实存在间隙的行，排除第一行（prev_end_dt为NULL）
WHERE prev_end_dt IS NOT NULL AND LOG_START_DT > prev_end_dt
-- 最后按开始时间排序，保证时间线连贯
ORDER BY LOG_START_DT;

说明： #

1. LAG(LOG_END_DT) OVER (ORDER BY LOG_START_DT) 用来获取当前行的上一条记录的结束时间，必须按LOG_START_DT排序，否则间隙检测会出错。
2. 通过LOG_START_DT > prev_end_dt判断是否存在时间间隙，存在则生成Type=0的填充行。
3. 用UNION ALL合并原始行和填充行，最后排序得到完整的时间序列。

如果是用Python处理本地数据，Pandas的时间处理能力可以轻松实现需求：

import pandas as pd

# 导入你的数据（这里用你提供的示例数据）
data = [
    ["3/28/2018 9:30", "3/28/2018 12:15", 2],
    ["3/28/2018 13:30", "3/28/2018 16:30", 1],
    ["3/28/2018 17:15", "3/28/2018 20:00", 2],
    ["3/28/2018 21:15", "3/29/2018 0:00", 2],
    ["3/29/2018 0:00", "3/29/2018 0:30", 1]
]

# 转为DataFrame
df = pd.DataFrame(data, columns=["LOG_START_DT", "LOG_END_DT", "Type"])

# 第一步：将时间字符串转为datetime类型，这是时间运算的基础
df["LOG_START_DT"] = pd.to_datetime(df["LOG_START_DT"])
df["LOG_END_DT"] = pd.to_datetime(df["LOG_END_DT"])

# 获取上一行的结束时间（shift(1)表示向上偏移一行）
df["prev_end_dt"] = df["LOG_END_DT"].shift(1)

# 筛选出存在时间间隙的行
gap_rows = df[df["LOG_START_DT"] > df["prev_end_dt"]].copy()

# 生成填充行：Type=0，起止时间为间隙的首尾
fill_rows = pd.DataFrame({
    "LOG_START_DT": gap_rows["prev_end_dt"],
    "LOG_END_DT": gap_rows["LOG_START_DT"],
    "Type": 0
})

# 合并原始数据和填充行，然后按开始时间排序，重置索引
result = pd.concat([df.drop("prev_end_dt", axis=1), fill_rows]) \
           .sort_values("LOG_START_DT") \
           .reset_index(drop=True)

# 输出结果
print(result)

说明： #

1. 必须先把时间列转为datetime类型，否则无法进行时间大小比较。
2. shift(1)用来获取上一行的结束时间，和SQL里的LAG()作用一致。
3. 最后合并数据并排序，确保时间序列是连贯的。

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