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"
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With