Noobie Noobie - 1 month ago 7
R Question

R: how to propagate a single value to an entire column?

Consider the following df,

group = c('A','A','A','B','B','B','C','C')
time = c(-1,0,2,0,4,0,3,5)
value = seq(1:8)

df <- data.frame(group,time,value)

> df
group time value
1 A -1 1
2 A 0 2
3 A 2 3
4 B 0 4
5 B 4 5
6 B 0 6
7 C 3 7
8 C 5 8


I would like, for each
group
, create a column that contains the first-nonmissing value of
value
when
time == 0
. That value will, of course, will be repeated for every row in each group. In other words, expected output is

> df
group time value output
1 A -1 1 2
2 A 0 2 2
3 A 2 3 2
4 B 0 4 4
5 B 4 5 4
6 B 0 6 4
7 C 3 7 NA
8 C 5 8 NA


How can I do that using
dplyr
? Something like:

df %>%
mutate(output = coalesce(ifelse(time== 0, value, NA)) )


does not do the trick.

Many thanks!

Answer
df%>%
  group_by(group)%>%
  mutate(output=value[time==0][1])
  #we take the first non missing match
   group  time value output
  <fctr> <dbl> <dbl>  <dbl>
1      A    -1    10     23
2      A     0    23     23
3      A     2     5     23
4      B     0    22     22
5      B     4    11     22

Edit: If there is no time==0

  group time value
1     A   -1    10
2     A    0    23
3     A    2     5
4     B    0    22
5     B    4    11
6     C    1    10

We use

df%>%group_by(group)%>%
  mutate(output=ifelse(length(value[time==0][1])>0,value[time==0][1],NA))
   group  time value output
  <fctr> <dbl> <dbl>  <dbl>
1      A    -1    10     23
2      A     0    23     23
3      A     2     5     23
4      B     0    22     22
5      B     4    11     22
6      C     1    10     NA

Edit 2: multiple 0's in value for the same group

  group  time value output
  <fctr> <dbl> <int>  <int>
1      A    -1     1      2
2      A     0     2      2
3      A     2     3      2
4      B     0     4      4
5      B     4     5      4
6      B     0     6      4
7      C     3     7     NA
8      C     5     8     NA