如何基于另一数据框为目标DataFrame新增stock与status列？

现有两个DataFrame：
主数据框：

df <- structure(list(id = c("id1", "id1", "id2", "id2", "id3", "id3"),
                     title_num = c(1, 2, 1, 2, 1, 2),
                     title_name = c("amazon1", "yahoo2", "google1", NA, "yahoo1", "amazon2")),
                row.names = c(NA, -6L),
                class = c("tbl_df", "tbl", "data.frame"))

匹配规则数据框：

dfcheck <- structure(list(status = c("open/close", "close", "open"),
                          stock = c("company energy", "goods and books", "other"),
                          name = c("amazon1;google1", "google3;yahoo1", "yahoo2;amazon2;google2")),
                     class = "data.frame",
                     row.names = c(NA, -3L))

需求是得到如下输出结果：

id title_num title_name   stock           status
id1         1    amazon1 company energy open/close
id1         2     yahoo2 other          open
id2         1    google1 company energy open/close
id2         2       <NA> <NA>           <NA>
id3         1     yahoo1 goods and books close
id3         2    amazon2 other           open

解决方案 #

通过拆分匹配规则数据框的name列，再与主数据框左连接即可实现，步骤如下：

1. 加载tidyverse工具包（若未安装需先执行install.packages("tidyverse")）：

library(tidyverse)

2. 将dfcheck中用分号分隔的name列拆分为多行，保留对应stock和status：

dfcheck_long <- dfcheck %>%
  separate_rows(name, sep = ";")

3. 用title_name作为连接键，将主数据框与拆分后的规则数据框左连接：

result <- df %>%
  left_join(dfcheck_long, by = c("title_name" = "name"))

4. 查看最终结果：

print(result)

此方法会自动将主数据框中title_name为NA的行对应的stock和status设为NA，完全匹配需求。

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