Forward fill rows in a r data table

user_12807398 0
Viewed 509

I have a large data.table in the format below

Name       Value     1      2     3     4     5 
A           58       1      NA    NA    NA    NA
B           47       NA     1     NA    NA    NA
C           89       NA     NA    1     NA    NA
D           68       NA     NA    NA    1     NA
E           75       NA     NA    NA    NA    1

I would like to forward rows of the data table to achieve below results. I know how to forward fill columns.

Name       Value     1      2     3     4     5 
A           58       1      1     1     1     1
B           47       NA     1     1     1     1
C           89       NA     NA    1     1     1
D           68       NA     NA    NA    1     1
E           75       NA     NA    NA    NA    1

Help!

data.table dataframe r
2 Answers

data.table has it's own nafill function. 

library(data.table) #v>=1.12.8
library(magrittr)

melt(dt, id = 1:2) %>% 
  .[, value := nafill(value, "locf"), by = Name] %>% 
  dcast(., ... ~ variable)

#    Name Value  1  2  3  4 5
# 1:    A    58  1  1  1  1 1
# 2:    B    47 NA  1  1  1 1
# 3:    C    89 NA NA  1  1 1
# 4:    D    68 NA NA NA  1 1
# 5:    E    75 NA NA NA NA 1

Data

dt <- fread("Name       Value     1      2     3     4     5 
A           58       1      NA    NA    NA    NA
B           47       NA     1     NA    NA    NA
C           89       NA     NA    1     NA    NA
D           68       NA     NA    NA    1     NA
E           75       NA     NA    NA    NA    1")
user_30016260

Use fill in tidyr to fill in missing values with previous value.

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(3:7) %>%
  group_by(Name) %>%
  fill(value) %>%
  ungroup() %>% 
  pivot_wider()

# # A tibble: 5 x 7
#   Name  Value   `1`   `2`   `3`   `4`   `5`
#   <fct> <int> <int> <int> <int> <int> <int>
# 1 A        58     1     1     1     1     1
# 2 B        47    NA     1     1     1     1
# 3 C        89    NA    NA     1     1     1
# 4 D        68    NA    NA    NA     1     1
# 5 E        75    NA    NA    NA    NA     1

Note: The output above is the same as

df %>% fill(3:7, .direction = "up")

but the logic is different. The former belongs to "filling rows forward" and the latter is "filling columns backward". They will differ in other cases.

Data

df <- structure(list(Name = structure(1:5, .Label = c("A", "B", "C", 
"D", "E"), class = "factor"), Value = c(58L, 47L, 89L, 68L, 75L
), `1` = c(1L, NA, NA, NA, NA), `2` = c(NA, 1L, NA, NA, NA), 
`3` = c(NA, NA, 1L, NA, NA), `4` = c(NA, NA, NA, 1L, NA), 
`5` = c(NA, NA, NA, NA, 1L)), class = "data.frame", row.names = c(NA, -5L))
user_100689850
Related