CODEWITHSUNDEEP
rdataframefor-loopconditional-statements
Maris
Maris

Reputation: 1

Copy the value of a column from the previous row where conditions on other columns are met

I have a dataframe (df) with 3 columns (V1, V2 and V3). I would like to add a column (V4), in which I enter (for each row) the value of V3 from another row in which the value of V1+0.5 equals V1 of row i AND in which the value of V2+0.5 equals V2 of row i.

For the rows where this condition is not met, I want an NA in the column of V4.

V1 <- c(-2.5,-2,-1.5,-1,-0.5,0,0.5,1,1.5,2,2.5,3,3.5)
V2 <- c(14,14.5,15,15.5,1,1.5,2,2.5,8,8.5,9,9.5,10)
V3 <- c(42,42.1,42.2,42.3,42.4,42.5,42.6,42.7,42.8,42.9,43,43.1,43.2)
df <- data.frame(V1,V2,V3)

For this input data:

 V1     V2     V3        
-2.5    14     42
-2      14.5   42.1
-1.5    15     42.2
-1      15.5   42.3
-0.5    1      42.4
 0      1.5    42.5
 0.5    2      42.6
 1      2.5    42.7
 1.5    8      42.8
 2      8.5    42.9 
 2.5    9      43
 3      9.5    43.1  
 3.5    10     43.2

My desired result would be:

 V1     V2     V3     V4   
-2.5    14     42     NA
-2      14.5   42.1   42
-1.5    15     42.2   42.1
-1      15.5   42.3   42.2
-0.5    1      42.4   NA   
 0      1.5    42.5   42.4
 0.5    2      42.6   42.5
 1      2.5    42.7   42.6
 1.5    8      42.8   NA
 2      8.5    42.9   42.8
 2.5    9      43     42.9
 3      9.5    43.1   43
 3.5    10     43.2   43.1

I figured I could use a for-loop and an ifelse statement (using is.na for the NA values), but I do not know how to refer to the rows using something that looks like df$V1(of row i) == df$V1+0.5 (of row x) (and the same for V2).

Upvotes: 0

Views: 670

Answers (1)

M--
M--

Reputation: 28900

Another dplyr solution would be with using ifelse:

library(dplyr)

df1 %>% 
  mutate(V4 = ifelse(V1 == lag(V1) + 0.5 &  V2 == lag(V2) + 0.5, lag(V3), NA))

#>      V1   V2   V3   V4
#> 1  -2.5 14.0 42.0   NA
#> 2  -2.0 14.5 42.1 42.0
#> 3  -1.5 15.0 42.2 42.1
#> 4  -1.0 15.5 42.3 42.2
#> 5  -0.5  1.0 42.4   NA
#> 6   0.0  1.5 42.5 42.4
#> 7   0.5  2.0 42.6 42.5
#> 8   1.0  2.5 42.7 42.6
#> 9   1.5  8.0 42.8   NA
#> 10  2.0  8.5 42.9 42.8
#> 11  2.5  9.0 43.0 42.9
#> 12  3.0  9.5 43.1 43.0
#> 13  3.5 10.0 43.2 43.1

Data:

df1 <- data.frame(V1 = c(-2.5,-2,-1.5,-1,-0.5,0,0.5,1,1.5,2,2.5,3,3.5),
                  V2 = c(14,14.5,15,15.5,1,1.5,2,2.5,8,8.5,9,9.5,10),
                  V3 = c(42,42.1,42.2,42.3,42.4,42.5,42.6,42.7,42.8,42.9,43,43.1,43.2))

Upvotes: 1

Related Questions