Replace values from dataframe where vector values match indexes in another dataframe

Perhaps the question and answer are already posted, but I can't find it. Besides, is there any optimal approach to this problem? Because this is just an example of some rows, but I'll apply it to a data frame of about 1 million rows.

I'm kind of new to R.

I have two data frames

DF1:
   a b
1  1 0
2  2 0
3  2 0
4  3 0
5  5 0

and

DF2
  l
1 A
2 B
3 C
4 D
5 E

What I try to do, is to match the values in DF1$a with the indexes of DF2 and assign those values to DF1$b so my result would be the following way.

DF1:
   a b
1  1 A
2  2 B
3  2 B
4  3 C
5  5 E

I've coded a for loop to do this, but it seems that I'm missing something

for(i in 1:length(df1$a)){
  df1$b[i] <- df2$l[df1$a[i]]
}

Which throws the following result:

DF1:
   a b
1  1 1
2  2 2
3  2 2
4  3 3
5  5 5

Thanks in advance :)

1 answer

  • answered 2018-01-06 22:30 www

    We can use merge to merge two data frame based on row id and a.

    # Create example data frame
    DF1 <- data.frame(a = c(1, 2, 2, 3, 5))
    DF2 <- data.frame(l = c("A", "B", "C", "D", "E"),
                      stringsAsFactors = FALSE)
    
    # Create a column called a in DF2 shows the row id
    DF2$a <- row.names(DF2)
    
    # Merge DF1 and DF2 by a
    DF3 <- merge(DF1, DF2, by = "a", all.x = TRUE)
    
    # Change the name of column l to be b
    names(DF3) <- c("a", "b")
    
    DF3
    #   a b
    # 1 1 A
    # 2 2 B
    # 3 2 B
    # 4 3 C
    # 5 5 E