Make a new column based on values in other rows of other columns

user_14144867 0
Viewed 51

I have a data frame (df) with three columns as below:

A   B   C
1   15  -1.60
15  17  -1.49
3   13  2.59
17  18  3.34

I want to make a new column, "D" in which each row is a copy of a value in column "C," which in its row the column "A" has an equal value to the value of column "B", and if there is not an equal value put "NA"; something like below:

A   B   C   D
1   15  -1.6    -1.49
15  17  -1.49   3.34
3   13  2.59    NA
17  1   3.34    -1.6

I tried to write a loop to make "D" column from "C" column, but it does not work:

for(i in 1:nrow(df)) {
      if (df$B==df$A){
        df$D==df$C
      }else{
        df$D==NA}
    }

Is there any way rather than looping to make this column? If not, how can I use loops?

multiple-columns row r loops
3 Answers

No loops needed here. A lot of times (read: most of the time) you will get better/faster results (in R) if you leave for-loops alone and use vectorised functions.

Here is a data.table approach, using a by reference join

library( data.table )

df <- fread("A   B   C
1   15  -1.60
15  17  -1.49
3   13  2.59
17  1  3.34")

#or use the code:  setDT( df )

df[ df , D := i.C, on = .(B = A)][]
#     A  B     C     D
# 1:  1 15 -1.60 -1.49
# 2: 15 17 -1.49  3.34
# 3:  3 13  2.59    NA
# 4: 17  1  3.34 -1.60
user_63562780

In base R, we can use match

df$D <- with(df, C[match(B, A)])

-output

df
#   A  B     C     D
#1  1 15 -1.60 -1.49
#2 15 17 -1.49  3.34
#3  3 13  2.59    NA
#4 17  1  3.34 -1.60

data

df <- structure(list(A = c(1L, 15L, 3L, 17L), B = c(15, 17, 13, 1), 
    C = c(-1.6, -1.49, 2.59, 3.34)),
    row.names = c(NA, -4L), class = "data.frame")
user_37322710

Using dplyr package, you can do the following:

# data 
df <- 
    data.frame(A = c(1, 15, 3, 17), 
               B = c(15, 17, 13, 1), 
               C = c(-1.6, -1.49, 2.59, 3.34))

# code
library(dplyr)
df %>% 
    left_join(df %>% select(A, C) %>% rename(D = C), 
              by = c("B" = "A"))
user_102145070
Related