CODEWITHSUNDEEP
rmissing-data
mkrasmus
mkrasmus

Reputation: 131

replace missing column values with values from 'matching' columns

This is a follow-up question from replace missing values with a value from another column which was adequately solved. My problem is with regards to multiple matching columns.

Example dataset:

s <- data.frame(ID=c(191, 282, 202, 210), 
            Group.1=c(NA, "A", NA, "B"), 
            Back.1=c("DD", "AA", "DD", "BB"), 
            Group.2=c("D","A", NA, "B"),
            Back.2=c("DD", "BB", "CC", "AA"),
            stringsAsFactors=FALSE)

   ID Group.1 Back.1 Group.2 Back.2
1 191    <NA>     DD       D     DD
2 282       A     AA       A     BB
3 202    <NA>     DD    <NA>     CC
4 210       B     BB       B     AA

If I wanted to replace the NAs with matching 'Back' columns I would use:

s$Group.1 <- ifelse(test = !is.na(s$Group.1), yes = s$Group.1, no = s$Back.1)
s$Group.2 <- ifelse(test = !is.na(s$Group.2), yes = s$Group.2, no = s$Back.2)
s

   ID Group.1 Back.1 Group.2 Back.2
1 191      DD     DD       D     DD
2 282       A     AA       A     BB
3 202      DD     DD      CC     CC
4 210       B     BB       B     AA

As posted by Akrun, another approach would be:

library(data.table)
setDT(s)[is.na(Group.1), Group.1:= Back.1]
setDT(s)[is.na(Group.2), Group.2:= Back.2]

So if I have many matching columns I want to be able to map, loop or apply or whatever across them. Trying out a loop functions yields:

for (i in 1:2){
  s[paste0("Group.", i)] <- ifelse(test = !is.na(s[paste0("Group.", i)]), 
                                   yes = s[paste0("Group.", i)], 
                                   no = s[paste0("Back.", i)])
}

Warning messages:
1: In `[<-.data.frame`(`*tmp*`, paste0("Group.", i), value = list(c("DD",  :
  provided 4 variables to replace 1 variables
2: In `[<-.data.frame`(`*tmp*`, paste0("Group.", i), value = list(c("D",  :
  provided 4 variables to replace 1 variables
> s
   ID Group.1 Back.1 Group.2 Back.2
1 191      DD     DD       D     DD
2 282      AA     AA       A     BB
3 202      DD     DD    <NA>     CC
4 210      BB     BB       B     AA

Which appears to work for Group.1 and Back.1 but not Group.2, and the warning message is difficult to understand from my angle.

If someone can solve this with an appropriate loop would be most grateful. Even more helpful would be the ability to generalize to other named columns so that the numerically matching columns to Back.x can also have missing values imputed by Back.x. i.e.

s <- data.frame(ID=c(191, 282, 202, 210), 
            Group.1=c(NA, "A", NA, "B"), 
            Back.1=c("DD", "AA", "DD", "BB"), 
            Group.2=c("D","A", NA, "B"),
            Back.2=c("DD", "BB", "CC", "AA"),
            Donk.1 =c("PP", "ZZ", NA, "QQ"),
            stringsAsFactors=FALSE)

Upvotes: 1

Views: 193

Answers (1)

akrun
akrun

Reputation: 886938

We can use

gr1 <- grep("Group", names(s), value = TRUE)
bc1 <- grep("Back", names(s), value = TRUE)
setDT(s)
for(j in seq_along(gr1)){
    s[is.na(get(gr1[j])), (gr1[j]) := get(bc1[j])]
}

s
#    ID Group.1 Back.1 Group.2 Back.2
#1: 191      DD     DD       D     DD
#2: 282       A     AA       A     BB
#3: 202      DD     DD      CC     CC
#4: 210       B     BB       B     AA

For the updated dataset

gr1 <- names(s)[seq(2, ncol(s), by = 2)]
bc1 <- names(s)[seq(3, ncol(s), by = 2)]

setDT(s)
for(j in seq_along(gr1)){
    s[is.na(get(gr1[j])), (gr1[j]) := get(bc1[j])][]
}
s
#    ID Group.1 Back.1 Group.2 Back.2 Donk.1 Back.1.1
#1: 191      DD     DD       D     DD     PP       DD
#2: 282       A     AA       A     BB     ZZ       AA
#3: 202      DD     DD      CC     CC     DD       DD
#4: 210       B     BB       B     AA     QQ       BB

data

s <- data.frame(ID=c(191, 282, 202, 210), 
        Group.1=c(NA, "A", NA, "B"), 
        Back.1=c("DD", "AA", "DD", "BB"), 
        Group.2=c("D","A", NA, "B"),
        Back.2=c("DD", "BB", "CC", "AA"),
        Donk.1 =c("PP", "ZZ", NA, "QQ"),
        Back.1=c("DD", "AA", "DD", "BB"), 
        stringsAsFactors=FALSE)

Upvotes: 1

Related Questions