How to replace NA values in one column with values from another column?

Question

I merged two data tables. In result I have two factor columns model (with custom suffixes used - model.p and model.a). Now, I would like to move model.a value to model.p columns, if model.p columns is empty (i.e. equal to NA) and model.a value is not NA.

Here is what I tried:

zz1 <- "or,d,ddate,rdate,changes,class,price,fdate,company,number,minutes,model,added,source
VA1,VA2,2014-05-24,,0,0,2124,2014-05-22 15:50:16,,B3970,,,2014-05-22 12:20:03,ss
VA1,VA2,2014-05-26,,0,0,2124,2014-05-22 15:03:44,,B1234,,A1,2014-05-22 12:20:03,s1
VA1,VA2,2014-06-05,,0,0,2124,2014-05-22 15:48:24,,,,,2014-05-22 12:20:03,s1
VA1,VA2,2014-06-09,,0,0,2124,2014-05-22 15:37:35,,,,,2014-05-22 12:20:03,s2
VA1,VA2,2014-06-16,,0,0,2124,2014-05-22 14:17:33,,,,,2014-05-22 12:20:03,ss"

zz3 <- "number,ddate,model,model_id
B3970,2014-05-24,320,AB-DVH
B1234,2014-05-26,319,BA-DVH
"

columnClasses <- c("factor", "factor", "POSIXct", "factor", "integer", "factor", "integer", "factor", "factor", "factor", "integer", "factor", "factor", "factor")
dt1 <- read.table(text=zz1, header = TRUE, sep = ",", na.strings = c(""), colClasses = columnClasses)

columnClasses <- c("factor", "POSIXct", "factor", "factor")
dt3 <- read.table(text=zz3, header = TRUE, sep = ",", comment.char = "", quote = "", na.strings = c(""), colClasses = columnClasses)

dt13 <- merge(dt1, dt3, by = c("number", "ddate"), all.x = TRUE, suffixes = c(".p", ".a"))

dt13$model.p <- as.character(dt13$model.p)
dt13$model.a <- as.character(dt13$model.a)

dt13$model.p[is.na(dt13$model.p) & !is.na(dt13$model.a)] <- dt13$model.a[is.na(dt13$model.p) & !is.na(dt13$model.a)] # (1)

dt13$model.a <- NULL
setnames(dt13, "model.p", "model")

Looks like I am doing something wrong (don't like line (1) - should I have the same condition given twice?). Is there any way to simplify that?

Can you maybe accept an answer in order to mark the question as solved? — MERose
– MERose, Commented Jan 23, 2016 at 22:25

Colonel Beauvel · Accepted Answer · 2015-04-17 20:48:37Z

2

You can do:

dt13$model.p = with(dt13, ifelse(is.na(model.p) & !is.na(model.a), model.a, model.p)

answered Apr 17, 2015 at 20:48

Colonel Beauvel

31.3k11 gold badges49 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jangorecki · Accepted Answer · 2019-06-05 05:52:07Z

NOTE this answer is going to be outdated soon, it will work of course, but preferred way will be to use coalesce, a dedicated function to solve exactly the problem in the question. Coalesce should be released in data.table 1.12.4.

I saw this question visible on data.table tag, also you used setnames which is data.table function. So will leave a data.table solution, quite straightforward.

library(data.table)
DT1 <- as.data.table(dt1)[, model := as.character(model)]
DT3 <- as.data.table(dt3)[, model := as.character(model)]
setkeyv(DT1, c("number", "ddate"))
setkeyv(DT3, c("number", "ddate"))
DT1[DT3, `:=`(model_id = i.model_id, model = ifelse(is.na(model),i.model,model))][]
#     or   d      ddate rdate changes class price               fdate company number minutes model               added source model_id
# 1: VA1 VA2 2014-06-05    NA       0     0  2124 2014-05-22 15:48:24      NA     NA      NA    NA 2014-05-22 12:20:03     s1       NA
# 2: VA1 VA2 2014-06-09    NA       0     0  2124 2014-05-22 15:37:35      NA     NA      NA    NA 2014-05-22 12:20:03     s2       NA
# 3: VA1 VA2 2014-06-16    NA       0     0  2124 2014-05-22 14:17:33      NA     NA      NA    NA 2014-05-22 12:20:03     ss       NA
# 4: VA1 VA2 2014-05-26    NA       0     0  2124 2014-05-22 15:03:44      NA  B1234      NA    A1 2014-05-22 12:20:03     s1   BA-DVH
# 5: VA1 VA2 2014-05-24    NA       0     0  2124 2014-05-22 15:50:16      NA  B3970      NA   320 2014-05-22 12:20:03     ss   AB-DVH

Collectives™ on Stack Overflow

How to replace NA values in one column with values from another column?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related