I have a data frame "z"
我有一个数据框“z”
letter color
1 a 0
2 e 0
3 b 0
4 b 0
5 d 0
6 d 0
7 a 0
8 b 0
9 c 0
10 d 0
11 c 0
12 c 0
13 c 0
14 c 0
15 e 0
16 e 0
17 a 0
18 d 0
19 e 0
20 b 0
and another data frame "y"
和另一个数据框“y”
letter color
1 a red
2 b blue
3 c green
when the letter in z matches with a letter in y I would like to append the color from y into the corresponding color field in z but I do not want to remove any values from z. If a match doesn't occur, z$color should remain unchanged. I used"0" as a place holder in z$color, this could be text instead.
当z中的字母与y中的字母匹配时,我想将y中的颜色附加到z中的相应颜色字段中,但我不想从z中删除任何值。如果没有匹配,z $ color应保持不变。我使用“0”作为z $颜色的占位符,这可能是文本而不是。
I've been attempting things for loops, the match() command and statements with %in% but I'm not quite achieving the results I'm after.
我一直在尝试for循环,match()命令和%in%的语句,但我还没有完全达到我想要的结果。
Any ideas?
有任何想法吗?
This is the code I used for the data frames
这是我用于数据帧的代码
set.seed(3)
z=data.frame(sample(c("a","b","c","d","e"),20,replace=T))
names(z)="letter"
z$color=rep(0,dim(z)[1])
z
y1=c("a","b","c")
y2=c("red","blue","green")
y=data.frame(cbind(y1,y2))
names(y)=c("letter","color")
y
3 个解决方案
#1
28
you don't need z$color
in the first place if its just place holder, you can replace NA
later with 0
如果它只是占位符,你首先不需要z $颜色,你可以稍后用0替换NA
z$color<-y[match(z$letter, y$letter),2]
#2
6
You can use merge
:
你可以使用合并:
dat <- merge(z, y, by = "letter", all.x = TRUE)
transform(dat, color = ifelse(is.na(color.y),
color.x, as.character(color.y)))[-(2:3)]
letter color
1 a red
2 a red
3 a red
4 b blue
5 b blue
6 b blue
7 b blue
8 c green
9 c green
10 c green
11 c green
12 c green
13 d 0
14 d 0
15 d 0
16 d 0
17 e 0
18 e 0
19 e 0
20 e 0
#3
2
sqldf/sqlite is very flexible:
sqldf / sqlite非常灵活:
library(sqldf)
z$color="0" # to avoid conflicts numeric/characters
z <- sqldf(c("UPDATE z
SET color = (SELECT y.color
FROM y
WHERE z.letter = y.letter
)
WHERE EXISTS (SELECT 1
FROM y
WHERE z.letter = y.letter
)"
, "select * from main.z"
)
)
z
letter color
1 b blue
2 a red
3 d 0.0
4 d 0.0
5 e 0.0
6 a red
7 a red
8 c green
9 b blue
10 c green
11 e 0.0
12 c green
13 b blue
14 d 0.0
15 d 0.0
16 d 0.0
17 c green
18 e 0.0
19 a red
20 c green
#1
28
you don't need z$color
in the first place if its just place holder, you can replace NA
later with 0
如果它只是占位符,你首先不需要z $颜色,你可以稍后用0替换NA
z$color<-y[match(z$letter, y$letter),2]
#2
6
You can use merge
:
你可以使用合并:
dat <- merge(z, y, by = "letter", all.x = TRUE)
transform(dat, color = ifelse(is.na(color.y),
color.x, as.character(color.y)))[-(2:3)]
letter color
1 a red
2 a red
3 a red
4 b blue
5 b blue
6 b blue
7 b blue
8 c green
9 c green
10 c green
11 c green
12 c green
13 d 0
14 d 0
15 d 0
16 d 0
17 e 0
18 e 0
19 e 0
20 e 0
#3
2
sqldf/sqlite is very flexible:
sqldf / sqlite非常灵活:
library(sqldf)
z$color="0" # to avoid conflicts numeric/characters
z <- sqldf(c("UPDATE z
SET color = (SELECT y.color
FROM y
WHERE z.letter = y.letter
)
WHERE EXISTS (SELECT 1
FROM y
WHERE z.letter = y.letter
)"
, "select * from main.z"
)
)
z
letter color
1 b blue
2 a red
3 d 0.0
4 d 0.0
5 e 0.0
6 a red
7 a red
8 c green
9 b blue
10 c green
11 e 0.0
12 c green
13 b blue
14 d 0.0
15 d 0.0
16 d 0.0
17 c green
18 e 0.0
19 a red
20 c green