Myujth

I have some of these table

 ID V1 V2 V3 V4 V5 V6 V7 V8 V9 V10 limit
 1: 10167638 89 NA 116 102 96 NA 106 116 NA 144 3
 2: 10298462 74 114 NA NA 114 NA 121 111 98 108 6
 3: 10316168 88 78 NA 77 72 96 NA 95 NA NA 4
 4: 10423491 118 77 NA 86 139 127 NA 103 93 84 2
 5: 10497492 12 154 NA 121 121 114 111 NA NA NA 7
 6: 10619463 42 NA NA NA NA NA NA NA NA 141 9
 7: 10631362 174 NA 125 118 117 116 139 116 NA 104 10
 8: 10725490 49 NA 175 NA 176 NA 139 123 140 141 5
 9: 10767348 140 106 174 162 NA 169 140 127 112 NA 6
10: 10832134 10 178 NA NA 116 95 95 125 115 103 3

I try to fill this NAs with previous column value
(if V2 is NA fill with V1 value)

with condition which is limit (if limit is 3 just fill NA until V3 and leave with NAs)

so what I try to do is like this

 ID V1 V2 V3 V4 V5 V6 V7 V8 V9 V10 limit
 1: 10167638 89 89 116 102 96 NA 106 116 NA 144 3
 2: 10298462 74 114 114 114 114 114 121 111 98 108 6
 3: 10316168 88 78 78 77 72 96 NA 95 NA NA 4
 4: 10423491 118 77 NA 86 139 127 NA 103 93 84 2
 5: 10497492 12 154 154 121 121 114 111 NA NA NA 7
 6: 10619463 42 42 42 42 42 42 42 42 42 141 9
 7: 10631362 174 174 125 118 117 116 139 116 116 104 10
 8: 10725490 49 49 175 175 176 NA 139 123 140 141 5
 9: 10767348 140 106 174 162 162 169 140 127 112 NA 6
10: 10832134 10 178 178 NA 116 95 95 125 115 103 3

Actual data is pretty big so it would be nice solve this problem with data.table
but other solution is still okay like dplyr or tidyr or other solution.

Using data.table's set() function:

Code

col <- paste0("V", 1:10)
for (i in 2:length(col)) 
 rows <- which(is.na(dt[[col[i]]]) & dt[["limit"]] >= i)
 set(
 x = dt,
 i = rows,
 j = col[i],
 value = dt[[col[i-1]]][rows]
 )

Results

dt
 ID V1 V2 V3 V4 V5 V6 V7 V8 V9 V10 limit
 1: 10167638 89 89 116 102 96 NA 106 116 NA 144 3
 2: 10298462 74 114 114 114 114 114 121 111 98 108 6
 3: 10316168 88 78 78 77 72 96 NA 95 NA NA 4
 4: 10423491 118 77 NA 86 139 127 NA 103 93 84 2
 5: 10497492 12 154 154 121 121 114 111 NA NA NA 7
 6: 10619463 42 42 42 42 42 42 42 42 42 141 9
 7: 10631362 174 174 125 118 117 116 139 139 139 104 10
 8: 10725490 49 49 175 175 176 NA 139 123 140 141 5
 9: 10767348 140 106 174 162 162 169 140 127 112 NA 6
10: 110832134 10 178 178 NA 116 95 95 125 115 103 3

Data

dt <- fread(" ID V1 V2 V3 V4 V5 V6 V7 V8 V9 V10 limit
 10167638 89 NA 116 102 96 NA 106 116 NA 144 3
 10298462 74 114 NA NA 114 NA 121 111 98 108 6
 10316168 88 78 NA 77 72 96 NA 95 NA NA 4
 10423491 118 77 NA 86 139 127 NA 103 93 84 2
 10497492 12 154 NA 121 121 114 111 NA NA NA 7
 10619463 42 NA NA NA NA NA NA NA NA 141 9
 10631362 174 NA 125 118 117 116 139 116 NA 104 10
 10725490 49 NA 175 NA 176 NA 139 123 140 141 5
 10767348 140 106 174 162 NA 169 140 127 112 NA 6
 110832134 10 178 NA NA 116 95 95 125 115 103 3")

You can try a tidyverse

library(tidyverse)
dt %>% 
 gather(k, v, -ID, -limit) %>% # make df from wide to long
 mutate(k = factor(k, levels = unique(k))) %>% # for correct spreading in the last step
 group_by(ID) %>% 
 mutate(gr=ifelse(is.na(v), 1:n(), 0)) %>% # check where the NA's are
 fill(v) %>% # update the values
 mutate(v = ifelse(limit >= gr, v, NA)) %>% # change to NA back again accrding limit
 select(-gr) %>% 
 spread(k, v) # backtransform to long
# A tibble: 10 x 12
# Groups: ID [10]
 ID limit V1 V2 V3 V4 V5 V6 V7 V8 V9 V10
 <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int>
 1 10167638 3 89 89 116 102 96 NA 106 116 NA 144
 2 10298462 6 74 114 114 114 114 114 121 111 98 108
 3 10316168 4 88 78 78 77 72 96 NA 95 NA NA
 4 10423491 2 118 77 NA 86 139 127 NA 103 93 84
 5 10497492 7 12 154 154 121 121 114 111 NA NA NA
 6 10619463 9 42 42 42 42 42 42 42 42 42 141
 7 10631362 10 174 174 125 118 117 116 139 116 116 104
 8 10725490 5 49 49 175 175 176 NA 139 123 140 141
 9 10767348 6 140 106 174 162 162 169 140 127 112 NA
10 110832134 3 10 178 178 NA 116 95 95 125 115 103