How do I map 1 column to 2 columns?
I am trying to match numbers from one column to numbers in two other columns. I can do it just fine if I only match one column but have problems with two columns. That's what I'm doing:
I have 2 data frames, df1:
number value
1
2
3
4
5
and df2:
number_a number_b value
3 3
1 5
5 1
4 2
2 4
What I want to do is match the "number" column from df1 to "number_a" or number_b "in df2, then insert the" value "from df2 to the" value "df1 to give the df1 result as:
number value
1 5
2 4
3 3
4 2
5 1
My approach is to use
df1$value <- df2$value[match(df1$number, df2$number_a)]
or
df1$value <- df2$value[match(df1$number, df2$number_b)]
which gives, respectively, for df1
number value
1 NA
2 NA
3 3
4 NA
5 1
and
number value
1 5
2 4
3 NA
4 2
5 NA
However, I cannot populate the entire "value" column in df1 using this approach. How can I match "number" to "number_a" and "number_b" in one fell swoop. I tried
df1$value <- df2$value[match(df1$number, df2$number_a:number_b)]
but it didn't work.
Thank!
+3
source to share
2 answers
I am new to R (from several years with C). Tried suggestions and I thought I would embed what I came up with:
// Assuming either 'number_a' or 'number_b' is valid
// Combine into new column 'number' and delete them original columns
df2 <- transform(df2, number = ifelse(is.na(df2$number_a), df2$number_b,
df2$number_a))[-c(1:2)]
// Combine the two data frames by the column 'number'
df <- merge(df1, df2, by = "number")
number value
15
2 4
3 3
4 2
5 1
+1
source to share