How to fill NA with values by group and conditioning

Question

I'm struggling to figure out how I can repeat the value for id2 when it has value = NA, and also have the same date1.

I have the following data frame:

test = data.frame(date1 = c("2020-11-02", "2020-11-02","2020-11-02", "2020-11-03","2020-11-04","2020-11-04"),
                  value = c(676, 676, NA, 400, 505, NA),
                  id1 = c("16-4-L", "16-3-L", "16-3-R", "16-3-F", "17-6-L", "17-1-L"),
                  id2 = c("16", "16", "16", "16", "17", "17"))

#        date1 value    id1 id2
# 1 2020-11-02   676 16-4-L  16
# 2 2020-11-02   676 16-3-L  16
# 3 2020-11-02    NA 16-3-R  16
# 4 2020-11-03   400 16-3-F  16
# 5 2020-11-04   505 17-6-L  17
# 6 2020-11-04    NA 17-1-L  17

#and I need the following:
#> result
#        date1 value    id1 id2
# 1 2020-11-02   676 16-4-L  16
# 2 2020-11-02   676 16-3-L  16
# 3 2020-11-02   676 16-3-R  16
# 4 2020-11-03   400 16-3-F  16
# 5 2020-11-04   505 17-6-L  17
# 6 2020-11-04   505 17-1-L  17

Note that id2 are different, but I'm looking to the same id1 at the same date to use the same value.

I'd like to make something like: if value is an NA, then we look to rows that have the same id2 and date1, then just copy that value.

Any hint on how can I do that?

Your description is confusing because you are referring to id2, but actually do things on id1. — deschen, Nov 18 '21 at 14:02
exactly @deschen, I need to set the value of the same `id1` and `date1` to `id2`, when its value is an `NA`. — DR15, Nov 18 '21 at 14:04
Does this answer your question? [Replace NA with previous or next value, by group, using dplyr](https://stackoverflow.com/questions/40040834/replace-na-with-previous-or-next-value-by-group-using-dplyr) — Wilson Souza, Nov 18 '21 at 14:08

score 2 · Accepted Answer · answered Nov 18 '21 at 14:04

2

Are you looking for sth. like:

library(tidyverse)
test %>%
  group_by(date1, id2) %>%
  fill(value, .direction = 'updown') %>%
  ungroup()

which gives:

# A tibble: 6 x 4
  date1      value id1    id2  
  <chr>      <dbl> <chr>  <chr>
1 2020-11-02   676 16-4-L 16   
2 2020-11-02   676 16-3-L 16   
3 2020-11-02   676 16-3-R 16   
4 2020-11-03   400 16-3-F 16   
5 2020-11-04   505 17-6-L 17   
6 2020-11-04   505 17-1-L 17

answered Nov 18 '21 at 14:04

deschen

10,012
3
27
50

the direction 'updown' affects if I have an NA first, and then a value? – DR15 Nov 18 '21 at 14:10
Yes, see the help file for `?fill`. Feel free to change to your needs if that‘s not what you want. – deschen Nov 18 '21 at 14:19

How to fill NA with values by group and conditioning

1 Answers1