我有一个数据表，想使用查找表修改它。我想遍历数据中的代码列，并value根据datayear匹配的列和与查找表field列中正确行值匹配的代码列的名称，为每个列添加一个新的对应列。
我试过将 lapply 与 left_join 一起使用，但我无法练习如何使用数据列名称来引用查找field列中的正确值。我还考虑过查找表在宽格式下是否更好，因此您至少可以有匹配的列名，但我仍然无法生成可用的函数。
示例数据和所需的输出：
数据（编辑：实际数据将包含更多代码列）：
structure(list(id = 1:10, datayear = c(2007L, 2007L, 2007L, 2007L,
2007L, 2008L, 2008L, 2008L, 2008L, 2008L), nationalitycode = c(1L,
1L, 1L, 2L, 3L, 5L, 4L, 3L, 2L, 1L), subjectcode = c(2L, 5L,
5L, 5L, 2L, 5L, 4L, 2L, 1L, 4L)), .Names = c("id", "datayear",
"nationalitycode", "subjectcode"), class = "data.frame", row.names = c(NA,
-10L))
id datayear nationalitycode subjectcode
1 1 2007 1 2
2 2 2007 1 5
3 3 2007 1 5
4 4 2007 2 5
5 5 2007 3 2
6 6 2008 5 5
7 7 2008 4 4
8 8 2008 3 2
9 9 2008 2 1
10 10 2008 1 4
查找表：
structure(list(datayear = c(2007L, 2007L, 2007L, 2007L, 2007L,
2007L, 2007L, 2007L, 2007L, 2007L, 2008L, 2008L, 2008L, 2008L,
2008L, 2008L, 2008L, 2008L, 2008L, 2008L), field = structure(c(1L,
1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L, 2L, 2L,
2L, 2L, 2L), .Label = c("nationalitycode", "subjectcode"), class = "factor"),
code = c(1L, 2L, 3L, 4L, 5L, 1L, 2L, 3L, 4L, 5L, 1L, 2L,
3L, 4L, 5L, 1L, 2L, 3L, 4L, 5L), lookupvalue = structure(c(10L,
16L, 9L, 4L, 5L, 2L, 7L, 13L, 1L, 14L, 5L, 16L, 4L, 6L, 11L,
17L, 3L, 15L, 8L, 12L), .Label = c("Algebra", "Art", "Beekeeping",
"Chinese", "English", "French", "Geography", "H.E.", "Indian",
"Irish", "Italian", "Latin", "Maths", "P.E.", "Rivetting",
"Scottish", "Sewing"), class = "factor")), class = "data.frame", row.names = c(NA,
-20L), .Names = c("datayear", "field", "code", "lookupvalue"))
datayear field code lookupvalue
1 2007 nationalitycode 1 Irish
2 2007 nationalitycode 2 Scottish
3 2007 nationalitycode 3 Indian
4 2007 nationalitycode 4 Chinese
5 2007 nationalitycode 5 English
6 2007 subjectcode 1 Art
7 2007 subjectcode 2 Geography
8 2007 subjectcode 3 Maths
9 2007 subjectcode 4 Algebra
10 2007 subjectcode 5 P.E.
11 2008 nationalitycode 1 English
12 2008 nationalitycode 2 Scottish
13 2008 nationalitycode 3 Chinese
14 2008 nationalitycode 4 French
15 2008 nationalitycode 5 Italian
16 2008 subjectcode 1 Sewing
17 2008 subjectcode 2 Beekeeping
18 2008 subjectcode 3 Rivetting
19 2008 subjectcode 4 H.E.
20 2008 subjectcode 5 Latin
期望的输出：
id datayear nationalitycode subjectcode nationalityvalue subjectvalue
1 1 2007 1 2 Irish Geography
2 2 2007 1 5 Irish P.E.
3 3 2007 1 5 Irish P.E.
4 4 2007 2 5 Scottish P.E.
5 5 2007 3 2 Indian Geography
6 6 2008 5 5 Italian Latin
7 7 2008 4 4 French H.E.
8 8 2008 3 2 Chinese Beekeeping
9 9 2008 2 1 Scottish Sewing
10 10 2008 1 4 English H.E.
非常感谢任何帮助！
诀窍是根据查找表的适当子集进行连接。这是通过使用正确的字段值进行子集化。
library(dplyr)
dt1 = structure(list(id = 1:10, datayear = c(2007L, 2007L, 2007L, 2007L,
2007L, 2008L, 2008L, 2008L, 2008L, 2008L), nationalitycode = c(1L,
1L, 1L, 2L, 3L, 5L, 4L, 3L, 2L, 1L), subjectcode = c(2L, 5L,
5L, 5L, 2L, 5L, 4L, 2L, 1L, 4L)), .Names = c("id", "datayear",
"nationalitycode", "subjectcode"), class = "data.frame", row.names = c(NA, -10L))
dt2 = structure(list(datayear = c(2007L, 2007L, 2007L, 2007L, 2007L,
2007L, 2007L, 2007L, 2007L, 2007L, 2008L, 2008L, 2008L, 2008L,
2008L, 2008L, 2008L, 2008L, 2008L, 2008L), field = structure(c(1L,
1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L, 2L, 2L,
2L, 2L, 2L), .Label = c("nationalitycode", "subjectcode"), class = "factor"),
code = c(1L, 2L, 3L, 4L, 5L, 1L, 2L, 3L, 4L, 5L, 1L, 2L,
3L, 4L, 5L, 1L, 2L, 3L, 4L, 5L), lookupvalue = structure(c(10L,
16L, 9L, 4L, 5L, 2L, 7L, 13L, 1L, 14L, 5L, 16L, 4L, 6L, 11L,
17L, 3L, 15L, 8L, 12L), .Label = c("Algebra", "Art", "Beekeeping",
"Chinese", "English", "French", "Geography", "H.E.", "Indian",
"Irish", "Italian", "Latin", "Maths", "P.E.", "Rivetting",
"Scottish", "Sewing"), class = "factor")), class = "data.frame", row.names = c(NA,
-20L), .Names = c("datayear", "field", "code", "lookupvalue"))
dt1 %>%
left_join(dt2 %>% filter(field == "nationalitycode"), by=c("datayear"="datayear","nationalitycode"="code")) %>%
left_join(dt2 %>% filter(field == "subjectcode"), by=c("datayear"="datayear","subjectcode"="code")) %>%
rename(nationalityvalue = lookupvalue.x,
subjectvalue = lookupvalue.y) %>%
select(-field.x, -field.y)
# id datayear nationalitycode subjectcode nationalityvalue subjectvalue
# 1 1 2007 1 2 Irish Geography
# 2 2 2007 1 5 Irish P.E.
# 3 3 2007 1 5 Irish P.E.
# 4 4 2007 2 5 Scottish P.E.
# 5 5 2007 3 2 Indian Geography
# 6 6 2008 5 5 Italian Latin
# 7 7 2008 4 4 French H.E.
# 8 8 2008 3 2 Chinese Beekeeping
# 9 9 2008 2 1 Scottish Sewing
# 10 10 2008 1 4 English H.E.
对于您使用循环询问的更一般情况，我需要重塑您的查找表，以便我可以使用列名。该过程将自动检测您在查找表中有多少唯一字段，并将使用 for 循环执行连接（顺序）。
library(dplyr)
library(tidyr)
dt1 = structure(list(id = 1:10, datayear = c(2007L, 2007L, 2007L, 2007L,
2007L, 2008L, 2008L, 2008L, 2008L, 2008L), nationalitycode = c(1L,
1L, 1L, 2L, 3L, 5L, 4L, 3L, 2L, 1L), subjectcode = c(2L, 5L,
5L, 5L, 2L, 5L, 4L, 2L, 1L, 4L)), .Names = c("id", "datayear",
"nationalitycode", "subjectcode"), class = "data.frame", row.names = c(NA, -10L))
dt2 = structure(list(datayear = c(2007L, 2007L, 2007L, 2007L, 2007L,
2007L, 2007L, 2007L, 2007L, 2007L, 2008L, 2008L, 2008L, 2008L,
2008L, 2008L, 2008L, 2008L, 2008L, 2008L), field = structure(c(1L,
1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L, 2L, 2L,
2L, 2L, 2L), .Label = c("nationalitycode", "subjectcode"), class = "factor"),
code = c(1L, 2L, 3L, 4L, 5L, 1L, 2L, 3L, 4L, 5L, 1L, 2L,
3L, 4L, 5L, 1L, 2L, 3L, 4L, 5L), lookupvalue = structure(c(10L,
16L, 9L, 4L, 5L, 2L, 7L, 13L, 1L, 14L, 5L, 16L, 4L, 6L, 11L,
17L, 3L, 15L, 8L, 12L), .Label = c("Algebra", "Art", "Beekeeping",
"Chinese", "English", "French", "Geography", "H.E.", "Indian",
"Irish", "Italian", "Latin", "Maths", "P.E.", "Rivetting",
"Scottish", "Sewing"), class = "factor")), class = "data.frame", row.names = c(NA,
-20L), .Names = c("datayear", "field", "code", "lookupvalue"))
# reshape your lookup data
dt2 %>%
spread(field, code) -> dt2_reshaped
# start dataset (to join every field you have)
dt_temp = dt1
# for every field you have do the join
for (fld in as.character(unique(dt2$field))) {
dt_temp %>% left_join(dt2_reshaped %>% select_("datayear", "lookupvalue", fld), by=c("datayear",fld)) -> dt_temp
names(dt_temp)[names(dt_temp) == "lookupvalue" ] = gsub("code","value",fld)
}
dt_temp
# id datayear nationalitycode subjectcode nationalityvalue subjectvalue
# 1 1 2007 1 2 Irish Geography
# 2 2 2007 1 5 Irish P.E.
# 3 3 2007 1 5 Irish P.E.
# 4 4 2007 2 5 Scottish P.E.
# 5 5 2007 3 2 Indian Geography
# 6 6 2008 5 5 Italian Latin
# 7 7 2008 4 4 French H.E.
# 8 8 2008 3 2 Chinese Beekeeping
# 9 9 2008 2 1 Scottish Sewing
# 10 10 2008 1 4 English H.E.
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句