Htykuut

Given the following data frame, I need to be able to fill out values in each row to the right until the next value is encountered in which case I need to fill that value out etc. until I reach the end of the row.

# load data

id <- LETTERS[1:7]

X2000 <-  c(NA,NA,NA,NA,100,NA,NA)

X2001 <- c(NA,200,80,NA,205,50,NA)

X2002 <- c(NA,300,NA,300,NA,NA,NA)

X2003 <- c(400,NA,70,NA,NA,NA,600)

X2004 <- c(NA,500,NA,NA,NA,NA,NA)

dat <- data.frame(id,X2000,X2001,X2002,X2003,X2004)



id X2000 X2001 X2002 X2003 X2004

A    NA    NA    NA   400    NA

B    NA   200   300    NA   500

C    NA    80    NA    70    NA

D    NA    NA   300    NA    NA

E   100   205    NA    NA    NA

F    NA    50    NA    NA    NA

G    NA    NA    NA   600    NA

The resulting dataframe should look like this:

id X2000 X2001 X2002 X2003 X2004

A    NA    NA    NA   400   400

B    NA   200   300   300   500

C    NA    80    80    70    70

D    NA    NA   300   300   300

E   100   205   205   205   205

F    NA    50    50    50    50

G    NA    NA    NA   600   600

Any clever way of doing this? Thanks.

Here's one way with dplyr and tidyr -

dat %>%

  gather(year, value, -id) %>% 

  group_by(id) %>% 

  arrange(id, year) %>% 

  fill(value, .direction = "down") %>% 

  ungroup() %>% 

  spread(year, value)



# A tibble: 7 x 6

  id    X2000 X2001 X2002 X2003 X2004

  <fct> <dbl> <dbl> <dbl> <dbl> <dbl>

1 A        NA  NA    NA   400   400  

2 B        NA 200   300   300   500  

3 C        NA  80.0  80.0  70.0  70.0

4 D        NA  NA   300   300   300  

5 E       100 205   205   205   205  

6 F        NA  50.0  50.0  50.0  50.0

7 G        NA  NA    NA   600   600 

We could apply with na.locf

library(zoo)

dat[-1] <- t(apply(dat[-1], 1, na.locf, na.rm = FALSE))

dat

#   id X2000 X2001 X2002 X2003 X2004

#1  A    NA    NA    NA   400   400

#2  B    NA   200   300   300   500

#3  C    NA    80    80    70    70

#4  D    NA    NA   300   300   300

#5  E   100   205   205   205   205

#6  F    NA    50    50    50    50

#7  G    NA    NA    NA   600   600