如何在R中合并无同名列、无共同键且行数不同的三个数据框

问题背景 #

我需要在R中合并三个结构差异较大的数据框，它们列名不同、无统一连接键且行数各异，数据是欧亚蓝山雀40年的繁殖记录：

• Demo.data：记录繁殖配对信息、后代脚环标识及出飞后代数量
• morph.data：包含亲鸟的形态测量数据（体重mass、跗骨长度tarsus等）
• Chick.data：包含雏鸟的形态测量数据

核心问题 #

1. Demo.data中雌雄亲鸟的脚环号是分列为maleBand#和femaleband#的格式，而morph.data和Chick.data中脚环号是单列格式（band#/Band#）
2. 脚环号存在跨年份重复的情况，无法直接仅通过脚环号进行数据连接

目标模型 #

需要构建两个线性混合效应模型：

Mean_Off_tarsus ~ #_offspring + Prnt_mass + Mean_Prnt_tarsus + (1|maleID) + (1|femaleID) + (1|nest ID) + (1|year)
Mean_Off_mass ~ #_offspring + Prnt_mass + Mean_Prnt_tarsus + (1|maleID) + (1|femaleID) + (1|nest ID) + (1|year)

各数据框结构 #

人口统计数据框（Demo.data） #

structure(list(population = c("pir", "pir", "pir", "pir", "pir", "pir", 
"pir", "pir", "pir", "pir"),
year = structure(c(17L, 7L, 7L, 6L, 6L, 8L, 9L, 6L, 4L, 5L), levels = c("1976", "1977", "1978", "1979",
"1980", "1981", "1982", "1983", "1984", "1985", "1986", "1987",
"1988", "1989", "1990", "1991", "1992", "1993", "1994", "1995",
"1996", "1997", "1998", "1999", "2000", "2001", "2002", "2003",
"2004", "2005", "2006", "2007", "2008", "2009", "2010", "2011",
"2012", "2013", "2014", "2015", "2016", "2017", "2018", "2019",
"2020", "2021"), class = "factor"),
nest = c("107", "72", "105",
"80", "110", "77", "77", "2", "37", "47"),
`#offspring` = c(5L,6L, 5L, 3L, 4L, 8L, 7L, 2L, 4L, 5L),
`maleBand#` = c("3475700", "2709315",
"2709871", "2546628", "2546650", "2864916", "3034870", "2709096",
"2310927", "2310911"),
`femaleband#` = c("2221152", "2546612", "2546616",
"2546643", "2546644", "2546644", "2546644", "2546651", "2563274",
"2563341"),chickband1 = c("3945742", "2709339", "2709892", "2546630",
"2546645", "2864917", "3034911", "2709097", "2563288", "2563348"
), chickband2 = c("3945743", "2709340", "2709893", "2546631", "2546646",
"2864918", "3034912", "2709098", "2563289", "2563349"),
chickband3 = c("3945744",
"2709341", "2709894", "2546632", "2546647", "2864919", "3034913",
"", "2563290", "2563350"),  row.names = c(NA,
10L), class = "data.frame")

形态数据框（morph.data） #

structure(list(population = c("pir", "pir", "pir", "pir", "pir", "pir","pir", "pir", "pir", "pir"), year = c(1992L, 1993L, 1992L, 1993L,1992L, 1985L, 1985L, 1983L, 1985L, 1985L), nest = c("48", "41",  "78", "4", "107", "14", "112", "114", "108", "114"), date_mesure = c("1992-06-11","1993-06-12", "1992-06-11", "1993-06-10", "1992-06-05", "1985-03-25", "1985-01-17", "1983-06-08", "1985-01-17", "1985-01-10"), `band#` = c("2221144","2221144", "2221147", "2221147", "2221152", "2546646", "2546658", "2546660", "2563352", "2563384"), sex = c(1L, 1L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L), tarsus = c(15.66, 15.82, 15.8, 15.9, 15.63, 13.6, 14.4, 16.2, 14.1, 15.05), mass = c(8.9, 8.6, 9.6, 9.1,  9.2, 9, 12, 9.2, 10.7, 11.1)), row.names = c(NA, 10L), class = "data.frame")

雏鸟数据框（Chick.data） #

structure(list(population = c("pir", "pir", "pir", "pir", "pir", "pir",  "pir", "pir", "pir", "pir"), year = c(1991L, 1991L, 1991L, 1991L,  1991L, 1991L, 1992L, 1992L, 1990L, 1990L),  nest = c("59", "59","59", "59", "59", "59", "20", "30", "57", "57"),  date_mesure = c("1991-06-07", "1991-06-07", "1991-06-07", "1991-06-07", "1991-06-07", "1991-06-07", "1992-06-09", "1992-06-09", "1990-06-06", "1990-06-06"),  `Band#` = c("2221124","2221125", "2221126", "2221127", "2221128", "2221129", "3364375","3364376", "3475222", "3475223"),  tarsus = c(16.14, 16.81, 15.18,15.52, 16.23, 15.61, 15.53, 16.7, 15.9, 14.68), mass = c(9.9, 10.9, 9.4, 9.9, 10.1, 10.5, 10.4, 10.7, 10.1, 9.9)),row.names = c(NA,10L), class = "data.frame")

数据合并与预处理步骤 #

1. 统一脚环号列名与连接键 #

由于脚环号跨年份重复，必须结合年份+脚环号作为唯一连接键：

• 对morph.data和Chick.data，新增year_band列，格式为paste(year, band#, sep = "_")（先将Chick.data的Band#重命名为band#统一列名）
• 对Demo.data，分别为雌雄亲鸟生成male_year_band和female_year_band，即paste(year_num, maleBand#, sep = "_")和paste(year_num, femaleband#, sep = "_")（先将Demo.data的因子型year转为数值型year_num）

2. 整理亲鸟形态数据 #

将morph.data按year_band分组，取每个个体每年的形态测量均值（若同一年有多次测量），得到每个亲鸟每年的mass和tarsus数据。

3. 连接Demo.data与亲鸟形态数据 #

• 通过male_year_band连接Demo.data和形态均值数据，获取雄鸟的形态数据并命名为male_mass、male_tarsus
• 通过female_year_band连接Demo.data和形态均值数据，获取雌鸟的形态数据并命名为female_mass、female_tarsus
• 计算亲鸟体重均值Prnt_mass = (male_mass + female_mass)/2，以及亲鸟跗骨长度均值Mean_Prnt_tarsus = (male_tarsus + female_tarsus)/2

4. 整理雏鸟数据并计算巢水平均值 #

• 将Chick.data中的雏鸟脚环与Demo.data中的chickband1/chickband2/chickband3匹配，结合year_band确保对应关系
• 按nest和year分组，计算每个巢的雏鸟跗骨长度均值Mean_Off_tarsus和体重均值Mean_Off_mass

5. 合并所有数据到巢水平 #

将预处理后的Demo.data（含亲鸟形态均值）与雏鸟巢水平均值数据，通过population、year和nest连接，得到最终用于建模的数据集。

示例代码片段 #

# 加载所需包
library(dplyr)
library(tidyr)
library(lme4)

# 统一Chick.data的脚环列名
Chick.data <- Chick.data %>% rename(`band#` = Band#)

# 生成year_band连接键
morph.data <- morph.data %>% mutate(year_band = paste(year, `band#`, sep = "_"))
Chick.data <- Chick.data %>% mutate(year_band = paste(year, `band#`, sep = "_"))
Demo.data <- Demo.data %>% 
  mutate(year_num = as.numeric(as.character(year)),
         male_year_band = paste(year_num, `maleBand#`, sep = "_"),
         female_year_band = paste(year_num, `femaleband#`, sep = "_"))

# 处理亲鸟形态数据（取年度均值）
morph_summary <- morph.data %>% 
  group_by(year_band) %>% 
  summarise(mass = mean(mass, na.rm = TRUE),
            tarsus = mean(tarsus, na.rm = TRUE))

# 连接雄鸟形态数据
Demo_with_male <- Demo.data %>% 
  left_join(morph_summary, by = c("male_year_band" = "year_band")) %>% 
  rename(male_mass = mass, male_tarsus = tarsus)

# 连接雌鸟形态数据并计算亲鸟均值
Demo_with_parents <- Demo_with_male %>% 
  left_join(morph_summary, by = c("female_year_band" = "year_band")) %>% 
  rename(female_mass = mass, female_tarsus = tarsus) %>% 
  mutate(Prnt_mass = (male_mass + female_mass)/2,
         Mean_Prnt_tarsus = (male_tarsus + female_tarsus)/2)

# 整理雏鸟数据到巢水平
chick_nest_summary <- Chick.data %>% 
  group_by(population, year, nest) %>% 
  summarise(Mean_Off_tarsus = mean(tarsus, na.rm = TRUE),
            Mean_Off_mass = mean(mass, na.rm = TRUE))

# 合并最终数据集
final_data <- Demo_with_parents %>% 
  left_join(chick_nest_summary, by = c("population", "year_num" = "year", "nest"))

# 构建目标模型
model_tarsus <- lmer(Mean_Off_tarsus ~ `#offspring` + Prnt_mass + Mean_Prnt_tarsus + (1|`maleBand#`) + (1|`femaleband#`) + (1|nest) + (1|year_num), data = final_data)
model_mass <- lmer(Mean_Off_mass ~ `#offspring` + Prnt_mass + Mean_Prnt_tarsus + (1|`maleBand#`) + (1|`femaleband#`) + (1|nest) + (1|year_num), data = final_data)

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