CODEWITHSUNDEEP
rdataframedplyr
Vector JX
Vector JX

Reputation: 179

Conditional fill value in Dataframe

I have a dataframe which look like:

Date        Avc1     Xrt2      Var3     Var4
2017-04-04  0        0         0        0
2017-04-04  10       18        22       25
2017-04-04  8        12        16       20
2017-04-04  5        10        13       18

Now i want to conditionally fill Var3 and Var4 value with exact below value whenever its 0.

Output:

Date        Avc1     Xrt2      Var3     Var4
2017-04-04  0        0         22       25
2017-04-04  10       18        22       25
2017-04-04  8        12        16       20
2017-04-04  5        10        13       18

Upvotes: 1

Views: 1023

Answers (2)

Jaap
Jaap

Reputation: 83215

Using:

df[4:5] <- lapply(df[4:5], function(x) {i <- which(x == 0); x[i] <- x[i+1]; x})

gives:

> df
        Date Avc1 Xrt2 Var3 Var4
1 2017-04-04    0    0   22   25
2 2017-04-04   10   18   22   25
3 2017-04-04    8   12   16   20
4 2017-04-04    5   10   13   18

Another possibility with na.locf from the zoo-package:

i <- which(df == 0, arr.ind = TRUE)

df[i[i[,2] %in% 4:5,]] <- NA

df <- zoo::na.locf(df, fromLast = TRUE)

Upvotes: 3

camille
camille

Reputation: 16832

Here are a few options. In the first 2, I used mutate_at to remove 0s from those columns, then use fill from tidyr to fill upwards.

In the third, I used lead to take the value from below if the value in that column is 0.

library(tidyverse)

df %>%
    mutate_at(vars(Var3, Var4), function(x) ifelse(x == 0, NA, x)) %>%
    fill(Var3, Var4, .direction = "up")
#> # A tibble: 4 x 5
#>   Date        Avc1  Xrt2  Var3  Var4
#>   <date>     <int> <int> <int> <int>
#> 1 2017-04-04     0     0    22    25
#> 2 2017-04-04    10    18    22    25
#> 3 2017-04-04     8    12    16    20
#> 4 2017-04-04     5    10    13    18

df %>%
    mutate_at(vars(Var3, Var4), na_if, 0) %>%
    fill(Var3, Var4, .direction = "up")
#> # A tibble: 4 x 5
#>   Date        Avc1  Xrt2  Var3  Var4
#>   <date>     <int> <int> <int> <int>
#> 1 2017-04-04     0     0    22    25
#> 2 2017-04-04    10    18    22    25
#> 3 2017-04-04     8    12    16    20
#> 4 2017-04-04     5    10    13    18

df %>%
    mutate_at(vars(Var3, Var4), function(x) ifelse(x == 0, lead(x), x))
#> # A tibble: 4 x 5
#>   Date        Avc1  Xrt2  Var3  Var4
#>   <date>     <int> <int> <int> <int>
#> 1 2017-04-04     0     0    22    25
#> 2 2017-04-04    10    18    22    25
#> 3 2017-04-04     8    12    16    20
#> 4 2017-04-04     5    10    13    18

Created on 2018-05-06 by the reprex package (v0.2.0).

Upvotes: 2

Related Questions