按ID计算随访时间：仅日期数据下代码失效问题排查

Hey there! Let's work through this problem together. I understand you're trying to calculate follow-up time in years for your 16,000-row, 100-variable dataframe, and the code from the similar Calculating time difference by ID question isn't working because you only have date-only data (no time component), unlike the datetime example in that post. Let's break down how to fix this step by step.

First, the most common issue here is that your date strings might not be converted to a proper date data type. If they're stored as plain text, date difference calculations will fail. Let's cover the two most common languages for this task:

For R: #

# Convert string dates to Date class (automatically ignores any time components if present)
df$date <- as.Date(df$date)

# Verify the class to be safe
class(df$date)  # Should return "Date"

If your dates use a non-standard format (like DD/MM/YYYY instead of YYYY-MM-DD), specify it explicitly:

df$date <- as.Date(df$date, format = "%d/%m/%Y")

For Python (Pandas): #

import pandas as pd

# Convert strings to date-only datetime objects
df['date'] = pd.to_datetime(df['date']).dt.date

# Or keep as datetime but normalize to midnight (time parts won't affect calculations)
df['date'] = pd.to_datetime(df['date']).normalize()

The core logic is to group your data by Incident.ID.., grab the earliest (baseline) date for each group, then compute the difference between each date and the baseline, converting that to years.

In R (Using dplyr + lubridate for precision): #

library(dplyr)
library(lubridate)

df <- df %>%
  group_by(Incident.ID..) %>%
  mutate(
    # Get the earliest date for each incident as baseline
    baseline_date = min(date, na.rm = TRUE),
    # Calculate exact years between dates (accounts for leap years/month lengths)
    followup_years = time_length(interval(baseline_date, date), "year")
  ) %>%
  ungroup()

If you don't need ultra-precise year calculations, a simpler approximation works too:

df <- df %>%
  group_by(Incident.ID..) %>%
  mutate(
    baseline_date = min(date, na.rm = TRUE),
    followup_days = as.numeric(date - baseline_date),
    followup_years = followup_days / 365.25  # 365.25 accounts for leap years roughly
  ) %>%
  ungroup()

In Python (Pandas): #

import pandas as pd
from dateutil.relativedelta import relativedelta

# First sort by ID and date to ensure baseline is the earliest entry
df = df.sort_values(['Incident.ID..', 'date'])

# Add baseline date per incident
df['baseline_date'] = df.groupby('Incident.ID..')['date'].transform('min')

# Approximate year calculation
df['followup_years_approx'] = (df['date'] - df['baseline_date']).dt.days / 365.25

# Precise year calculation (accounts for exact months/days between dates)
def calculate_precise_years(row):
    delta = relativedelta(row['date'], row['baseline_date'])
    return delta.years + delta.months/12 + delta.days/365.25

df['followup_years_precise'] = df.apply(calculate_precise_years, axis=1)

• NA values: Always add na.rm = TRUE to min() so groups with missing dates don't break the calculation.
• Performance: Your 16k-row dataset is totally manageable, but if you want faster grouping in R, use data.table:

  library(data.table)
  setDT(df)
  df[, baseline_date := min(date, na.rm = TRUE), by = Incident.ID..]
  df[, followup_years := time_length(interval(baseline_date, date), "year")]
  

• Date parsing failures: If your dates won't convert, double-check the format string matches your actual date structure (e.g., %m/%d/%Y for US-style dates).

Let's apply this to your sample data (even though it has time stamps, the code will ignore them and work perfectly):

# Sample data
df <- data.frame(
  Incident.ID.. = c(rep("INCFI0000029582",4), rep("INCFI0000029587",4)),
  date = c("2014-09-25 08:39:45", "2014-09-26 10:15:00", "2015-09-25 09:00:00", "2016-09-24 14:30:00",
           "2015-01-10 07:45:00", "2015-06-10 11:20:00", "2016-01-09 08:10:00", "2017-01-10 12:00:00")
)

# Convert to Date class and calculate follow-up
df$date <- as.Date(df$date)

library(dplyr)
library(lubridate)
df <- df %>%
  group_by(Incident.ID..) %>%
  mutate(
    baseline_date = min(date, na.rm = TRUE),
    followup_years = time_length(interval(baseline_date, date), "year")
  ) %>%
  ungroup()

print(df)

This will output accurate follow-up years, even with the original time-stamped dates.

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