R语言合并不等长ATUS数据集触发vecseq行数超限错误

错误原因 #

你遇到的合并错误，是因为仅用TUCASEID作为合并键导致的笛卡尔积爆炸：每个用户的所有陪伴对象记录，会和该用户的所有活动记录逐一配对，生成的行数远超系统限制。实际上，陪伴对象是和具体活动绑定的，必须同时用TUCASEID（用户ID）和TUACTIVITY_N（活动ID）作为合并键，才能正确关联每个活动的陪伴对象与对应时长。

修正后的完整代码 #

library(data.table)
library(tidyverse)
library(ggplot2)

# 转换为data.table（保留原数据，避免覆盖）
setDT(atuswho_0322)
setDT(atussum_0322)
setDT(atusact_0322)

# 提取年龄与年份数据
ageyr <- atussum_0322[, .(TUCASEID, TEAGE, TUYEAR)]

# 提取活动与时长数据
actdur <- atusact_0322[, .(TUCASEID, TUACTIVITY_N, TUACTDUR24)]

# 处理陪伴对象数据：筛选+重编码
who <- atuswho_0322[, .(TUCASEID, TUWHO_CODE, TUACTIVITY_N)] %>%
  filter(TUWHO_CODE %in% c("18", "19","20","21","22","23","24","25","26","40","51", "53", "54", "61")) %>%
  mutate(TUWHO_CODE = recode(TUWHO_CODE,
                             "18" = "Alone",
                             "19" = "Alone",
                             "20" = "Partner",
                             "21" = "Partner",
                             "22" = "Children",
                             "24" = "Family",
                             "23" = "Family",
                             "25" = "Family",
                             "26" = "Family",
                             "53" = "Family",
                             "54" = "Friends",
                             "40" = "Children",
                             "61" = "Coworkers",
                             "51"= "Family")) %>%
  rename(Companion = TUWHO_CODE) # 重命名为更清晰的列名

# 正确合并：按用户ID+活动ID关联陪伴对象与活动时长
final <- merge(who, actdur, by = c("TUCASEID", "TUACTIVITY_N"))

# 合并年份数据
final <- merge(final, ageyr, by = "TUCASEID")

# 按年份+陪伴类型聚合总时长（可根据需求改为mean计算平均时长）
summary_data <- final %>%
  group_by(TUYEAR, Companion) %>%
  summarise(Total_Duration = sum(TUACTDUR24, na.rm = TRUE),
            .groups = "drop")

# 绘制时间序列图
ggplot(summary_data, aes(x = TUYEAR, y = Total_Duration, color = Companion)) +
  geom_line(linewidth = 1) +
  geom_point(size = 2) +
  labs(x = "年份", y = "总耗时", title = "2003-2022年不同陪伴对象的活动耗时趋势", color = "陪伴对象") +
  theme_minimal() +
  scale_x_continuous(breaks = seq(2003, 2022, 2)) # 调整X轴刻度间隔

关键说明 #

1. 合并键修正：用c("TUCASEID", "TUACTIVITY_N")作为合并键，确保每个活动的陪伴对象只关联对应活动的时长，避免冗余行。
2. 数据聚合：通过group_by+summarise计算每年各陪伴类型的总耗时（如果需要人均时长，可将sum改为mean）。
3. 可视化优化：添加刻度间隔、清晰标签，让时间趋势更易读。

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