mutate for variable number of columns

user_10357699 0
Viewed 59

I have a data frame with a variable number of columns (.csv import). Column 1 is always the same (timecodes), column 2 always exists and the other columns may exist or not (so there's either 2 or i columns). Columns 2 - n show meter readings of maschine energy consumption. Sometimes values are missing. I want to interpolate those missing values.

df: 

time     maschine1     maschine 2    maschine 3    ...
16:15    7960          8237          9475          ...     
16:20    10480         10757         11995         ...       
16:25    NA            NA            NA            ...
16:30    15520         15797         17035         ...
16:35    18160         18437         19675         ...

Here's what I'm trying:

for(i in 2:ncol(df)) {
  df <- df %>%
    mutate(maschine_[i]_interpolated = (ifelse(is.na(.[[i]]),(lag(df[[i]])+lead(df[[i]))/2, .[[i]])))
}

So I want to interpolate and write the interpolated values in a new column for each of the i to n maschines.

Any ideas? Thanks a lot in advance!

dplyr interpolation r for-loop
1 Answers

You can use mutate_at as suggested in the comment. For linear interpolation, you can use na_interpolation from the imputeTS package.

library(dplyr)
library(imputeTS)

dat2 <- dat %>% mutate_at(vars(-time), funs(Inter = na_interpolation(.)))
dat2
#    time maschine1 maschine2 maschine3 maschine1_Inter maschine2_Inter maschine3_Inter
# 1 16:15      7960      8237      9475            7960            8237            9475
# 2 16:20     10480     10757     11995           10480           10757           11995
# 3 16:25        NA        NA        NA           13000           13277           14515
# 4 16:30     15520     15797     17035           15520           15797           17035
# 5 16:35     18160     18437     19675           18160           18437           19675

Or you can use lapply.

dat3 <- dat
dat3[-1] <- lapply(dat[-1], na_interpolation) 
dat4 <- dat3[-1]
names(dat4) <- paste(names(dat4), "Inter", sep = "_")
dat5 <- cbind(dat, dat4)
dat5
#    time maschine1 maschine2 maschine3 maschine1_Inter maschine2_Inter maschine3_Inter
# 1 16:15      7960      8237      9475            7960            8237            9475
# 2 16:20     10480     10757     11995           10480           10757           11995
# 3 16:25        NA        NA        NA           13000           13277           14515
# 4 16:30     15520     15797     17035           15520           15797           17035
# 5 16:35     18160     18437     19675           18160           18437           19675

DATA

dat <- read.table(text = "time     maschine1     maschine2    maschine3
'16:15'    7960          8237          9475  
'16:20'    10480         10757         11995
'16:25'    NA            NA            NA
'16:30'    15520         15797         17035
'16:35'    18160         18437         19675",
                  header = TRUE, stringsAsFactors = FALSE)
user_76698090
Related