R根据列值从两个表中向表中添加列

I have three tables that I'm attempting to merge into one.

我有三个表，我试图合并为一个。

The main table is similar to:

主表类似于：

Table1 <- data.frame("Data" = c(1, 2, 3, 4, 5), "Desc" = c("A", "A", "A", "B", "B"))

TableA <- data.frame("Values" = c(6, 2, 3))

TableB <- data.frame("Values" = c(2, 7))

I want to add another column to Table1 with the values from TableA and TableB, but Values coming from TableA must be placed in a row containing "A" in the "Desc" column and TableB values in rows containing "B" in the "Desc" column. The number of rows in Table A equal the number of rows Table1 with "A" and same for TableB.

我想使用TableA和TableB中的值向Table1添加另一列，但是来自TableA的值必须放在“Desc”列中包含“A”的行中，而在“Desc”中包含“B”的行中的TableB值“专栏。表A中的行数等于Table1与“A”的行数，而对于TableB则相同。

The resulting Table should look like:

结果表应如下所示：

Table1 <- data.frame("Data" = c(1, 2, 3, 4, 5), "Desc" = c("A", "A", "A", "B", "B"), "Values" = c(6, 2, 3, 2, 7))

> Table1
  Data Desc Values
1    1    A      6
2    2    A      2
3    3    A      3
4    4    B      2
5    5    B      7

2 个解决方案

#1

First note that these are "data.frames", not "tables". A "table" is actually a different class in R and they aren't the same thing. This strategy should work

首先请注意，这些是“data.frames”，而不是“表格”。 “表”实际上是R中的另一个类，它们不是同一个东西。这个策略应该有效

Table1$Values <- NA

Table1$Values[Table1$Desc=="A"] <- TableA$Value
Table1$Values[Table1$Desc=="B"] <- TableB$Value

Table1
#   Data Desc Values
# 1    1    A      6
# 2    2    A      2
# 3    3    A      3
# 4    4    B      2
# 5    5    B      7

#2

If you have multiple Table (TableA, TableB, TableC,...etc) and if you need to match the suffix of Table. to Table1 column Desc

如果你有多个表（TableA，TableB，TableC，...等），如果你需要匹配表的后缀。到表1列描述

ls1 <- ls(pattern="Table")
ls1
#[1] "Table1" "TableA" "TableB"
library(stringr)
indx <- str_extract(ls1[-1], perl('(?<=Table)[A-Z]'))
lst1 <- mget(ls1[-1])

do.call(rbind,
  lapply(seq_along(lst1),function(i) {
               x1 <- lst1[[i]]
               x2 <- Table1[!is.na(match(Table1$Desc, indx[i])),]
               x2$Values <- x1$Values
               x2}
  ))
 #  Data Desc Values
 #1    1    A      6
 #2    2    A      2
 #3    3    A      3
 #4    4    B      2
 #5    5    B      7

In the first step, after I created the objects (Table.), looked for the object names ls(pattern="Table")
在第一步中，在我创建对象（表。）之后，查找对象名称ls（pattern =“Table”）
Extracted the suffix LETTERS A, B from the objects that needs to be matched. Used regex lookbehind i.e. (?<=Table)[A-Z] matches a substring (uppercase letter) preceded by the string Table and extract the substring.
从需要匹配的对象中提取后缀LETTERS A，B。使用正则表达式lookbehind即（？<=表）[A-Z]匹配前面带有字符串Table的子字符串（大写字母）并提取子字符串。
mget returns the value of the objects as a list
mget以列表形式返回对象的值
Loop using lapply. Match the Desc column in Table1 with the extracted suffix and created a new column
使用lapply循环。将Table1中的Desc列与提取的后缀匹配，并创建一个新列

#1