如何在Stata中基于日期创建天数变量（适配跨午夜场景）

核心逻辑：先将每条记录的时间调整——若记录时间在午夜00:00至早6:00之间，将其日期归为前一天；再按参与者ID分组，对调整后的日期计算连续排名，得到从1开始的调查天数。

以下是几种常用工具的实现方法：

R语言实现 #

library(dplyr)
library(lubridate)

# 示例数据
df <- data.frame(
  Id = c(1,1,1,1,2,2,2,2,3,3,3,4,4,4,4),
  date = c("08/03/2020 08:17", "08/03/2020 12:01", "08/04/2020 15:08", "08/04/2020 22:16",
           "07/03/2020 08:10", "07/03/2020 12:03", "07/04/2020 15:07", "07/05/2020 00:16",
           "08/22/2020 09:17", "08/23/2020 11:04", "08/24/2020 00:01",
           "10/03/2020 08:37", "10/03/2020 11:13", "10/04/2020 15:20", "10/04/2020 23:05")
)

# 转换为datetime格式
df$datetime <- mdy_hm(df$date)

# 调整日期：午夜至早6点的记录归为前一天
df$adjusted_date <- ifelse(hour(df$datetime) < 6, 
                           date(df$datetime) - days(1), 
                           date(df$datetime))
df$adjusted_date <- as.Date(df$adjusted_date)

# 按ID分组计算天数排名
df <- df %>%
  group_by(Id) %>%
  mutate(day = dense_rank(adjusted_date)) %>%
  ungroup() %>%
  select(Id, date, day)

print(df)

Python（Pandas）实现 #

import pandas as pd

# 示例数据
data = {
    'Id': [1,1,1,1,2,2,2,2,3,3,3,4,4,4,4],
    'date': ["08/03/2020 08:17", "08/03/2020 12:01", "08/04/2020 15:08", "08/04/2020 22:16",
             "07/03/2020 08:10", "07/03/2020 12:03", "07/04/2020 15:07", "07/05/2020 00:16",
             "08/22/2020 09:17", "08/23/2020 11:04", "08/24/2020 00:01",
             "10/03/2020 08:37", "10/03/2020 11:13", "10/04/2020 15:20", "10/04/2020 23:05"]
}
df = pd.DataFrame(data)

# 转换为datetime格式
df['datetime'] = pd.to_datetime(df['date'], format='%m/%d/%Y %H:%M')

# 调整日期：早于6点的记录归为前一天
df['adjusted_date'] = df['datetime'].apply(lambda x: x.date() - pd.Timedelta(days=1) if x.hour <6 else x.date())

# 按ID分组计算天数排名
df['day'] = df.groupby('Id')['adjusted_date'].rank(method='dense').astype(int)

# 保留目标列
df = df[['Id', 'date', 'day']]
print(df)

SQL实现（MySQL为例） #

SELECT 
    Id,
    date,
    DENSE_RANK() OVER (PARTITION BY Id ORDER BY adjusted_date) AS day
FROM (
    SELECT 
        Id,
        date,
        CASE 
            WHEN HOUR(STR_TO_DATE(date, '%m/%d/%Y %H:%i')) < 6 
            THEN DATE_SUB(STR_TO_DATE(date, '%m/%d/%Y %H:%i'), INTERVAL 1 DAY)
            ELSE DATE(STR_TO_DATE(date, '%m/%d/%Y %H:%i'))
        END AS adjusted_date
    FROM your_table_name -- 替换为你的表名
) AS subquery
ORDER BY Id, date;

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