How to fill missing values from merge(..., all = TRUE, ...) with a value other than NA?

Question

In brief: I'm looking for a general way to fill the missing values in merge(..., all = TRUE, ...) with a constant other than NA.

Suppose that

z <- merge(x, y, all = TRUE, ...)

...and that I want all missing values in z (resulting from missing keys in either x or y) to be filled with the (non-NA) constant FILL_VALUE.

First, the easy case:

FILL_VALUE <- "-"

x <- data.frame(K=1001:1005,
                I=3:7,
                R=c(0.1, 0.2, 0.3, 0.4, 0.5),
                B=c(TRUE, FALSE, TRUE, FALSE, TRUE),
                C=c(0.1+0.2i, 0.3+0.4i, 0.5+0.6i, 0.7+0.8i, 0.9+1.0i))

y <- data.frame(K=1001:1003,
                S1=c("a", "b", "c"),
                S2=c("d", "e", "f"),
                stringsAsFactors = FALSE)

z <- merge(x, y, all = TRUE, by = "K")

## > z
##      K I   R     B        C   S1   S2
## 1 1001 3 0.1  TRUE 0.1+0.2i    a    d
## 2 1002 4 0.2 FALSE 0.3+0.4i    b    e
## 3 1003 5 0.3  TRUE 0.5+0.6i    c    f
## 4 1004 6 0.4 FALSE 0.7+0.8i <NA> <NA>
## 5 1005 7 0.5  TRUE 0.9+1.0i <NA> <NA>

In this case, the only NA entries in the result are those introduced by the merge, therefore the following does the job:

z[is.na(z)] <- FILL_VALUE

## > z
##      K I   R     B        C S1 S2
## 1 1001 3 0.1  TRUE 0.1+0.2i  a  d
## 2 1002 4 0.2 FALSE 0.3+0.4i  b  e
## 3 1003 5 0.3  TRUE 0.5+0.6i  c  f
## 4 1004 6 0.4 FALSE 0.7+0.8i  -  -
## 5 1005 7 0.5  TRUE 0.9+1.0i  -  -

Now a case where this solution fails.

xna <- data.frame(K=1001:1005,
                  I=c(NA, 4:7),
                  R=c(0.1, NA, 0.3, 0.4, 0.5),
                  B=c(TRUE, FALSE, NA, FALSE, TRUE),
                  C=c(0.1+0.2i, 0.3+0.4i, 0.5+0.6i, NA, 0.9+1.0i))

yna <- data.frame(K=1001:1003,
                  S1=c(NA, "b", "c"),
                  S2=c("d", NA, "f"),
                  stringsAsFactors = FALSE)

zna <- merge(xna, yna, all = TRUE, by = "K")
## > zna
##      K  I   R     B        C   S1   S2
## 1 1001 NA 0.1  TRUE 0.1+0.2i <NA>    d
## 2 1002  4  NA FALSE 0.3+0.4i    b <NA>
## 3 1003  5 0.3    NA 0.5+0.6i    c    f
## 4 1004  6 0.4 FALSE       NA <NA> <NA>
## 5 1005  7 0.5  TRUE 0.9+1.0i <NA> <NA>

The desired value for zna is the one in which the NA values that were introduced by the merge are replaced by FILL_VALUE; IOW:

## > zna
##      K  I   R     B        C   S1   S2
## 1 1001 NA 0.1  TRUE 0.1+0.2i <NA>    d
## 2 1002  4  NA FALSE 0.3+0.4i    b <NA>
## 3 1003  5 0.3    NA 0.5+0.6i    c    f
## 4 1004  6 0.4 FALSE       NA    -    -
## 5 1005  7 0.5  TRUE 0.9+1.0i    -    -

Therefore, this won't do:

zna[is.na(zna)] <- FILL_VALUE
## > zna
##      K I   R     B        C S1 S2
## 1 1001 - 0.1  TRUE 0.1+0.2i  -  d
## 2 1002 4   - FALSE 0.3+0.4i  b  -
## 3 1003 5 0.3     - 0.5+0.6i  c  f
## 4 1004 6 0.4 FALSE        -  -  -
## 5 1005 7 0.5  TRUE   0.9+1i  -  -

Note that this assignment does a lot more than inappropriately replace a few values with "-"; it also changes the types of several columns:

## > zna[, "I"]
## [1] "-" "4" "5" "6" "7"
## > zna[, "B"]
## [1] "TRUE"  "FALSE" "-"     "FALSE" "TRUE" 
## > zna[, "R"]
## [1] "0.1" "-"   "0.3" "0.4" "0.5"
## > zna[, "C"]
## [1] "0.1+0.2i" "0.3+0.4i" "0.5+0.6i" "-"        "0.9+1i"

Perhaps you can find the rows with `!zna$K %in% yna$K` along with `is.na` on the columns 'S1' and 'S2' to assign — akrun, Apr 24 '17 at 16:04
The second answer to this question looks like it provides one solution to your problem: http://stackoverflow.com/questions/28992362/dplyr-join-define-na-values — ulfelder, Apr 24 '17 at 16:16
What about `merge(x = replace(x = xna, list = is.na(xna), values = "XNA"), y = replace(x = yna, list = is.na(yna), values = "YNA"), by = "K", all = TRUE)` — d.b, Apr 27 '17 at 16:13
@d.b: unfortunately, this technique will coerce the type of all the affected columns to `character`. — kjo, Apr 28 '17 at 11:47
@d.b: Interesting idea, but it has similar problems. E.g., it coerces integers to doubles, for example. Plus (suprisingly enough), `is.infinite` is not implemented for DataFrames, so one can't restore the original `NA`s with `zna[is.infinite(zna)] <- NA`, but even if this expression were valid, it would fail to fix those `Inf`s that were automatically coerced to `character`. Etc., etc. After thinking about this problem some more, I've come to the conclusion that it's too difficult to solve it downstream; this is really a job for the implementers of `merge`. — kjo, Apr 28 '17 at 14:35
A workaround is to define dummy columns with a constant value other than `NA` in both `data.frames`, then set other values wherever the dummy columns are `NA`. — snaut, Jul 07 '17 at 08:53

score 1 · Answer 1 · answered Oct 20 '17 at 16:30

You can do as follows

> FILL_VALUE <- "-"
> 
> xna <- data.frame(K=1001:1005,
+                   I=c(NA, 4:7),
+                   R=c(0.1, NA, 0.3, 0.4, 0.5),
+                   B=c(TRUE, FALSE, NA, FALSE, TRUE),
+                   C=c(0.1+0.2i, 0.3+0.4i, 0.5+0.6i, NA, 0.9+1.0i))
> 
> yna <- data.frame(K=1001:1003,
+                   S1=c(NA, "b", "c"),
+                   S2=c("d", NA, "f"),
+                   stringsAsFactors = FALSE)
> 
> 
> # add bools
> xna$has_xna <- TRUE
> yna$has_yna <- TRUE
> 
> # merge
> zna <- merge(xna, yna, all = TRUE, by = "K")
> zna
     K  I   R     B        C has_xna   S1   S2 has_yna
1 1001 NA 0.1  TRUE 0.1+0.2i    TRUE <NA>    d    TRUE
2 1002  4  NA FALSE 0.3+0.4i    TRUE    b <NA>    TRUE
3 1003  5 0.3    NA 0.5+0.6i    TRUE    c    f    TRUE
4 1004  6 0.4 FALSE       NA    TRUE <NA> <NA>      NA
5 1005  7 0.5  TRUE 0.9+1.0i    TRUE <NA> <NA>      NA
> 
> # fill in for NAs due to merge
> yna_cols <- colnames(zna) %in% colnames(yna)
> zna[, yna_cols][is.na(zna[, yna_cols]) & is.na(zna$has_yna)] <- FILL_VALUE
> zna$has_yna <- NULL # remove column
> 
> # do the same for xna
> xna_cols <- colnames(zna) %in% colnames(xna)
> zna[, xna_cols][is.na(zna[, xna_cols]) & is.na(zna$has_xna)] <- FILL_VALUE
> zna$has_yna <- NULL # remove column
> 
> # Final results
> zna
     K  I   R     B        C has_xna   S1   S2
1 1001 NA 0.1  TRUE 0.1+0.2i    TRUE <NA>    d
2 1002  4  NA FALSE 0.3+0.4i    TRUE    b <NA>
3 1003  5 0.3    NA 0.5+0.6i    TRUE    c    f
4 1004  6 0.4 FALSE       NA    TRUE    -    -
5 1005  7 0.5  TRUE 0.9+1.0i    TRUE    -    -

The above can easily be rewritten to a generic merge function wrapper. Another option is to use data.table with the nomatch and on arguments to [.data.table function.

How to fill missing values from merge(..., all = TRUE, ...) with a value other than NA?

1 Answers1