通过将列名称与数据帧中的变量进行匹配，将数据帧中的值分配给另一个数据框

Question

我有一个这样的数据帧：

data <- data.frame(Time = rep(c("Jan 1999", "Feb 1999", "Mar 1999"), each = 3), Country = rep(c("Australia", "Brazil", "Canada"), 3), rep(Group = c("A", "B", "A"), 3), Intercept = NA)

以及具有来自回归的系数的另一个数据帧，其中A和B是不同组的截取。

coeffs <- data.frame(Time = c("Jan 1999", "Feb 1999", "Mar 1999"), A = c(1,2,3), B = c(3,2,1))

现在我想把来自coeffs dataframe的截取放到dataframe的intercept列中。我是通过以下方式完成的：

l <- length(unique(data[,"Country"]))
data[,"Intercept"] <- ifelse(data_1[,"Group_1"] == "A", rep(coeffs_1[,"A"], each = l), rep(coeffs_1[,"B"], each = l))

这似乎对两个组都很有效，但现在我需要对7个组做同样的事情，我不知道如何推广上面的方法。我想我可以使用7层嵌套的ifelse语句或for循环，但必须有一种更优雅的方式。

谢谢你的帮忙!

Answer 1

获取长格式的coeffs并与data连接：

library(dplyr)

coeffs %>%
  tidyr::pivot_longer(cols = -Time, names_to = 'Group', 
                      values_to = 'Intercept') %>%
  right_join(data, by = c('Time', 'Group'))

# A tibble: 9 x 4
#  Time     Group Intercept Country  
#  <chr>    <chr>     <dbl> <chr>    
#1 Jan 1999 A             1 Australia
#2 Jan 1999 A             1 Canada   
#3 Jan 1999 B             3 Brazil   
#4 Feb 1999 A             2 Australia
#5 Feb 1999 A             2 Canada   
#6 Feb 1999 B             2 Brazil   
#7 Mar 1999 A             3 Australia
#8 Mar 1999 A             3 Canada   
#9 Mar 1999 B             1 Brazil   

将此数据帧用于data：

data <- data.frame(Time = rep(c("Jan 1999", "Feb 1999", "Mar 1999"), each = 3),
                   Country = rep(c("Australia", "Brazil", "Canada"), 3), 
                   Group = rep(c("A", "B", "A"), 3))