how to replace #NA with previous value or average of past 5 value?

How can any replace how to replace #NA with the previous value or average of the past 5 value?

Thanks in advance

Data

63 02-04-2015 0.120 0.035 0.352 0.613 0.435 -0.384 #N/A 0.893 0.336 -0.050
64 06-04-2015 #N/A 0.943 0.000 1.156 #N/A -0.168 0.512 0.534 0.763 #N/A
65 07-04-2015 0.878 -1.636 1.878 -0.419 0.127 -0.584 -0.278 0.520 -0.214 1.589
66 08-04-2015 -0.193 -0.976 -0.350 0.868 -0.706 0.513 0.178 0.305 0.239 0.460
67 09-04-2015 1.024 -1.126 1.124 -0.553 0.395 -0.069 0.252 0.436 0.475 -1.213
68 10-04-2015 0.311 1.192 1.061 0.638 -0.051 1.038 0.068 -0.410 0.504 2.199
69 13-04-2015 0.344 -1.347 -0.360 -0.008 0.890 0.230 0.366 -0.310 -0.457 1.203
70 14-04-2015 -0.655 -0.520 0.156 0.422 -0.795 0.474 #N/A 0.404 0.170 0.237
71 15-04-2015 0.447 -0.486 0.304 -0.926 0.307 0.255 -1.069 0.157 0.420 -1.245
72 16-04-2015 -0.424 -1.256 -0.511 0.949 -1.364 0.270 -0.575 0.117 -0.046 3.425
73 17-04-2015 -1.363 -1.173 -0.933 -1.768 -1.782 0.036 -1.058 -0.795 -1.018 0.766
74 20-04-2015 0.634 0.938 0.822 -2.261 -0.137 0.948 -1.981 0.685 0.782 -1.959
75 21-04-2015 -0.239 0.427 0.153 1.048 -0.146 -0.401 -0.456 0.900 -0.263 1.933
76 22-04-2015 0.007 -0.213 -0.491 -0.828 -0.463 -0.126 0.349 0.670 0.462 2.181
77 23-04-2015 -0.732 0.179 0.363 -0.026 0.073 0.764 -0.942 -0.196 0.271 0.001
78 24-04-2015 -0.023 0.576 0.241 -0.570 0.312 -0.983 -1.195 -0.608 0.231 0.866
79 27-04-2015 1.349 1.024 0.471 0.304 0.938 -0.535 -1.401 -0.401 -0.489 1.924
80 28-04-2015 -1.587 -1.427 -1.035 1.522 -0.414 -0.686 0.853 -0.048 0.304 -1.136
81 29-04-2015 -2.652 -0.606 -1.198 -1.101 -2.066 -0.574 -0.424 #N/A -0.267 0.686
82 30-04-2015 0.119 -0.863 0.206 -2.782 0.299 -0.364 -0.523 -1.650 -0.950 -0.923
83 01-05-2015 #N/A -0.177 0.365 0.357 #N/A #N/A #N/A 0.107 1.002 #N/A
84 04-05-2015 0.424 -0.840 0.000 0.585 0.457 -0.107 1.238 #N/A 0.202 0.881
85 05-05-2015 -1.978 -2.393 -0.836 -2.458 -2.635 #N/A -0.163 #N/A -1.097 -4.043
86 06-05-2015 0.271 0.535 0.088 0.221 0.569 -0.822 -2.641 #N/A -0.531 -1.911
87 07-05-2015 0.433 -0.970 -0.674 1.452 0.496 -0.356 -0.244 -0.333 0.387 -2.041
88 08-05-2015 1.818 0.771 2.320 1.216 1.455 -0.223 0.738 0.329 1.146 1.275
89 11-05-2015 -0.456 -1.165 -0.240 0.039 0.106 -0.415 0.995 -0.085 -0.484 2.418
90 12-05-2015 -0.285 0.192 -1.367 -0.332 -0.083 -0.184 -2.392 0.085 -0.178 1.355

do you mean rowwise or columnwise ?

I need help for row wise.
I would be highly thankful if you can let me know how to do this column wise too.

# importing the example data .....

 exampledf <- function(intext) {
 tf <- tempfile()
 writeLines(intext, con = tf)
 require(tidyverse)
 read.delim(tf,sep=" ",row.names = NULL)
 }
 (df <- exampledf("date a b c d e f g h i j
 02-04-2015 0.120 0.035 0.352 0.613 0.435 -0.384  0.893 0.336 -0.050
 06-04-2015  0.943 0.000 1.156  -0.168 0.512 0.534 0.763 
 07-04-2015 0.878 -1.636 1.878 -0.419 0.127 -0.584 -0.278 0.520 -0.214 1.589
 08-04-2015 -0.193 -0.976 -0.350 0.868 -0.706 0.513 0.178 0.305 0.239 0.460
 09-04-2015 1.024 -1.126 1.124 -0.553 0.395 -0.069 0.252 0.436 0.475 -1.213
 10-04-2015 0.311 1.192 1.061 0.638 -0.051 1.038 0.068 -0.410 0.504 2.199
 13-04-2015 0.344 -1.347 -0.360 -0.008 0.890 0.230 0.366 -0.310 -0.457 1.203
 14-04-2015 -0.655 -0.520 0.156 0.422 -0.795 0.474  0.404 0.170 0.237
 15-04-2015 0.447 -0.486 0.304 -0.926 0.307 0.255 -1.069 0.157 0.420 -1.245
 16-04-2015 -0.424 -1.256 -0.511 0.949 -1.364 0.270 -0.575 0.117 -0.046 3.425
 17-04-2015 -1.363 -1.173 -0.933 -1.768 -1.782 0.036 -1.058 -0.795 -1.018 0.766
 20-04-2015 0.634 0.938 0.822 -2.261 -0.137 0.948 -1.981 0.685 0.782 -1.959
 21-04-2015 -0.239 0.427 0.153 1.048 -0.146 -0.401 -0.456 0.900 -0.263 1.933
 22-04-2015 0.007 -0.213 -0.491 -0.828 -0.463 -0.126 0.349 0.670 0.462 2.181
 23-04-2015 -0.732 0.179 0.363 -0.026 0.073 0.764 -0.942 -0.196 0.271 0.001
 24-04-2015 -0.023 0.576 0.241 -0.570 0.312 -0.983 -1.195 -0.608 0.231 0.866
 27-04-2015 1.349 1.024 0.471 0.304 0.938 -0.535 -1.401 -0.401 -0.489 1.924
 28-04-2015 -1.587 -1.427 -1.035 1.522 -0.414 -0.686 0.853 -0.048 0.304 -1.136
 29-04-2015 -2.652 -0.606 -1.198 -1.101 -2.066 -0.574 -0.424  -0.267 0.686
 30-04-2015 0.119 -0.863 0.206 -2.782 0.299 -0.364 -0.523 -1.650 -0.950 -0.923
 01-05-2015  -0.177 0.365 0.357    0.107 1.002 
 04-05-2015 0.424 -0.840 0.000 0.585 0.457 -0.107 1.238  0.202 0.881
 05-05-2015 -1.978 -2.393 -0.836 -2.458 -2.635  -0.163  -1.097 -4.043
 06-05-2015 0.271 0.535 0.088 0.221 0.569 -0.822 -2.641  -0.531 -1.911
 07-05-2015 0.433 -0.970 -0.674 1.452 0.496 -0.356 -0.244 -0.333 0.387 -2.041
 08-05-2015 1.818 0.771 2.320 1.216 1.455 -0.223 0.738 0.329 1.146 1.275
 11-05-2015 -0.456 -1.165 -0.240 0.039 0.106 -0.415 0.995 -0.085 -0.484 2.418
 12-05-2015 -0.285 0.192 -1.367 -0.332 -0.083 -0.184 -2.392 0.085 -0.178 1.35") %>%
     select(-row.names))
# a function to process a column/vector 

 process_a_column <- function(r){
   r2 <- ifelse(is.na(r),
                  ifelse(is.na(lag(r)),
                        rowMeans(cbind(lag(r,2),
                                       lag(r,3),
                                       lag(r,4),
                                       lag(r,5)),na.rm=TRUE),
                        lag(r)
                        )
                ,r)
   round(r2,digits=3)
 }

#apply our function on all numeric variables for 1st solution
mutate(df,across(where(is.numeric),process_a_column))

# for second solution 
# our function works for columns so for apply it by row on our dataframe, lets just put the frame on its side,
# run it, and rotate it back
t(select_if(df,is.numeric)) -> transposed_df 
mutate(transposed_df %>% 
         as.data.frame(),across(where(is.numeric),process_a_column)) %>%
  t() %>% as.data.frame() %>% setNames(setdiff(names(df),"date"))
1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.