Filling missing value in group

I have data frame where some of the values are missing

A 1
A NA
A NA
B NA
B 2
B NA
C NA
C NA
C NA

How can I fill in groups where I have data?

Solution 1:

You can also use fill from tidyr:

library(dplyr)
library(tidyr)

df1 %>%
  group_by(ID) %>%
  fill(v1) %>%
  fill(v1, .direction = "up")

Result:

# A tibble: 9 x 2
# Groups:   ID [3]
     ID    v1
  <chr> <int>
1     A     1
2     A     1
3     A     1
4     B     2
5     B     2
6     B     2
7     C    NA
8     C    NA
9     C    NA

Credits to @akrun for dput

Solution 2:

Alternative solution, though perhaps a bit flawed in how many assumptions it makes:

library(dplyr)
y %>%
  group_by(V1) %>%
  arrange(V2) %>%
  mutate(V2 = V2[1])
# Source: local data frame [9 x 2]
# Groups: V1 [3]
#      V1    V2
#   (chr) (int)
# 1     A     1
# 2     A     1
# 3     A     1
# 4     B     2
# 5     B     2
# 6     B     2
# 7     C    NA
# 8     C    NA
# 9     C    NA

Solution 3:

We can use data.table. Convert the 'data.frame' to 'data.table' (setDT(df1)), grouped by 'ID', we assign (:=) the column 'v1' as the first non-NA value.

library(data.table)
setDT(df1)[, v1:= v1[!is.na(v1)][1L] , by = ID]
df1
#   ID v1
#1:  A  1
#2:  A  1
#3:  A  1
#4:  B  2
#5:  B  2
#6:  B  2
#7:  C NA
#8:  C NA
#9:  C NA

Or using only base R

 with(df1, ave(v1, ID, FUN = function(x)
          replace(x, is.na(x), x[!is.na(x)][1L])))
 #[1]  1  1  1  2  2  2 NA NA NA

data

df1 <- structure(list(ID = c("A", "A", "A", "B", "B", "B", "C", "C", 
"C"), v1 = c(1L, NA, NA, NA, 2L, NA, NA, NA, NA)), .Names = c("ID", 
"v1"), class = "data.frame", row.names = c(NA, -9L))