IF else by group - check value in next row

Question

Here is a df for example:

test_df <- structure(list(plant_sp = c("plant_1", "plant_1", "plant_2", "plant_2", "plant_3",
                                       "plant_3", "plant_3", "plant_3", "plant_3", "plant_4", 
                                       "plant_4", "plant_4", "plant_4", "plant_4", "plant_4",
                                       "plant_5", "plant_5", "plant_5", "plant_5", "plant_5"), 
                          sp_rich = c(1, 1, NA, 1, NA, 
                                      1, 0, 0, NA, 0,
                                      0, 1, 0, 0, 1, 
                                      0, NA, NA, 0,NA)), 
                     row.names = c(NA, -20L), class = "data.frame", 
                     .Names = c("plant_sp", "sp_rich"))

I'm trying to create an ifelse statemant using tidyverse by the following: for each group by the column "plant_sp" check if the value in column "sp_rich" is.na if it does, set value "1" in a new column called "is_na_nest_row". I managed to do the following:

test_df %>%
group_by(plant_sp) %>%
mutate(is_na_nest_row = ifelse(???,1,0))

but I don't know how to refer a value in column sp_rich but in the next row (in a group)

For example:

I want the value "1" to be under is_na_nest_row in row 3, if is there is empty row under sp_rich at row 4.

Thanks a lot, Ido

(1) If you're using `dplyr`, use `if_else` instead, since `base::ifelse` has some [issues](https://stackoverflow.com/q/6668963/3358272). (2) Frankly, you don't need it here, though, as you can say `mutate(is_na_nest_row = +is.na(sp_rich))` for the same effect (though it'll be integers, not floating-point as you have here). — r2evans, Feb 01 '21 at 15:35
wouldn't it check if there na.value in the same row? foe example - I want the value "1" to be in row 3 if is there is empty row under "sp_rich" at row 4. I've added an example since I wasn't clear before, Thankk! — Ido, Feb 01 '21 at 15:38

r2evans · Answer 1 · 2021-02-01T16:53:57.137

test_df %>%
  group_by(plant_sp) %>%
  mutate(is_na_nest_row = +any(is.na(sp_rich)))
# # A tibble: 20 x 3
# # Groups:   plant_sp [5]
#    plant_sp sp_rich is_na_nest_row
#    <chr>      <dbl>          <int>
#  1 plant_1        1              0
#  2 plant_1        1              0
#  3 plant_2       NA              1
#  4 plant_2        1              1
#  5 plant_3       NA              1
#  6 plant_3        1              1
#  7 plant_3        0              1
#  8 plant_3        0              1
#  9 plant_3       NA              1
# 10 plant_4        0              0
# 11 plant_4        0              0
# 12 plant_4        1              0
# 13 plant_4        0              0
# 14 plant_4        0              0
# 15 plant_4        1              0
# 16 plant_5        0              1
# 17 plant_5       NA              1
# 18 plant_5       NA              1
# 19 plant_5        0              1
# 20 plant_5       NA              1

or if it is just the next row,

test_df %>%
  group_by(plant_sp) %>%
  mutate(is_na_nest_row = +(lead(is.na(sp_rich), default = FALSE)))
# # A tibble: 20 x 3
# # Groups:   plant_sp [5]
#    plant_sp sp_rich is_na_nest_row
#    <chr>      <dbl>          <int>
#  1 plant_1        1              0
#  2 plant_1        1              0
#  3 plant_2       NA              0
#  4 plant_2        1              0
#  5 plant_3       NA              0
#  6 plant_3        1              0
#  7 plant_3        0              0
#  8 plant_3        0              1
#  9 plant_3       NA              0
# 10 plant_4        0              0
# 11 plant_4        0              0
# 12 plant_4        1              0
# 13 plant_4        0              0
# 14 plant_4        0              0
# 15 plant_4        1              0
# 16 plant_5        0              1
# 17 plant_5       NA              1
# 18 plant_5       NA              0
# 19 plant_5        0              1
# 20 plant_5       NA              0

wouldn't it check if there na.value in the same row? for example - I want the value "1" to be in row 3 if is there is empty row under "sp_rich" at row 4. I've added an example since I wasn't clear before, Thanks! — Ido, Feb 01 '21 at 15:40
You know, it would really help if you provided actual expected output given your sample data. It removes most of the ambiguity. — r2evans, Feb 01 '21 at 15:40

Taufi · Accepted Answer · 2021-02-01T17:38:25.213

Edit: Now it should work properly.

You can access the next row by using row_number() within mutate. So I think this is the correct solution.

test_df %>% group_by(plant_sp) %>% mutate(Test = ifelse(is.na(sp_rich[row_number() + 1]), 1, 0),  Test = c(Test[-n()], 0)))

with output

    # A tibble: 20 x 3
# Groups:   plant_sp [5]
   plant_sp sp_rich  Test
   <chr>      <dbl> <dbl>
 1 plant_1        1     0
 2 plant_1        1     0
 3 plant_2       NA     0
 4 plant_2        1     0
 5 plant_3       NA     0
 6 plant_3        1     0
 7 plant_3        0     0
 8 plant_3        0     1
 9 plant_3       NA     0
10 plant_4        0     0
11 plant_4        0     0
12 plant_4        1     0
13 plant_4        0     0
14 plant_4        0     0
15 plant_4        1     0
16 plant_5        0     1
17 plant_5       NA     1
18 plant_5       NA     0
19 plant_5        0     1
20 plant_5       NA     0

thank you! The thing is that I do need it to be by group. for example at row 2 the value is 1 even if the NA row is in another group - "plant_2". Is there a way to make it by group? — Ido, Feb 01 '21 at 16:26
Yes, there is and now I think it should work! See the new code and output. — Taufi, Feb 01 '21 at 17:38

IF else by group - check value in next row

2 Answers2