按ID分组计算相邻行end_dt与当前行strt_dt的日期差

原始数据集 #

id strt_dt     end_dt
1  2013-05-07 2013-05-13
1  2013-05-14 2013-05-20
1  2013-05-21 2013-05-27
2  2013-05-14 2013-05-15
2  2013-05-16 2013-05-22
2  2013-05-23 2013-05-29

期望结果 #

id strt_dt     end_dt    diff
1  2013-05-07 2013-05-13 NA
1  2013-05-14 2013-05-20 1
1  2013-05-21 2013-05-27 1
2  2013-05-14 2013-05-15 NA
2  2013-05-16 2013-05-22 1
2  2013-05-29 2013-05-29 7

需求说明 #

按ID分组，每组第一行的diff字段为NA，其余行计算当前行strt_dt与上一行end_dt的天数差。

解决方案1：R语言（dplyr包） #

先将日期列转换为日期格式，再分组计算差值：

library(dplyr)
library(lubridate)

# 构建示例数据集
df <- data.frame(
  id = c(1,1,1,2,2,2),
  strt_dt = ymd(c("2013-05-07","2013-05-14","2013-05-21","2013-05-14","2013-05-16","2013-05-23")),
  end_dt = ymd(c("2013-05-13","2013-05-20","2013-05-27","2013-05-15","2013-05-22","2013-05-29"))
)

# 按ID分组计算天数差
df <- df %>%
  group_by(id) %>%
  mutate(diff = as.numeric(strt_dt - lag(end_dt))) %>%
  ungroup()

# 输出结果
print(df)

解决方案2：Python语言（pandas库） #

转换日期类型后，通过groupby和shift获取上一行的结束日期，再计算差值：

import pandas as pd

# 构建示例数据集
df = pd.DataFrame({
    'id': [1,1,1,2,2,2],
    'strt_dt': pd.to_datetime(["2013-05-07","2013-05-14","2013-05-21","2013-05-14","2013-05-16","2013-05-23"]),
    'end_dt': pd.to_datetime(["2013-05-13","2013-05-20","2013-05-27","2013-05-15","2013-05-22","2013-05-29"])
})

# 计算每组内当前行开始日期与上一行结束日期的天数差
df['diff'] = df.groupby('id')['strt_dt'].sub(df.groupby('id')['end_dt'].shift(1)).dt.days

# 输出结果
print(df)

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