R data.table merge two columns from the same table

Question

I have:

inputDT <- data.table(COL1 = c(1, NA, NA), COL1 = c(NA, 2, NA), COL1 = c(NA, NA, 3))
inputDT
   COL1 COL1 COL1
1:    1   NA   NA
2:   NA    2   NA
3:   NA   NA    3

I want

outputDT <- data.table(COL1 = c(1,2,3))
outputDT
   COL1
1:    1
2:    2
3:    3

Essentially, I have a data.table with multiple columns whose names are the same (values are mutually exclusive), and I need to generate just one column to combine those.

How to achieve it?

Does this answer your question? [R: coalescing a large data frame](https://stackoverflow.com/questions/44851873/r-coalescing-a-large-data-frame) ; You should find a solution there, except if you want to avoid calling `dplyr`; you can otherwise also call it within `DT`. — arg0naut91, Mar 08 '20 at 22:59

score 7 · Accepted Answer · answered Mar 08 '20 at 23:29

7

The OP is asking for a data.table solution. As of version v1.12.4 (03 Oct 2019), the fcoalesce() function is available:

library(data.table)
inputDT[, .(COL1 = fcoalesce(.SD))]

answered Mar 08 '20 at 23:29

Uwe

41,420
11
90
134

Some of my columns have text fields, and this approach works. – LeGeniusII Mar 10 '20 at 00:03

dc37 · Answer 2 · 2020-03-08T23:38:42.373

1

Alternatively (less elegant than @Uwe's answer), if you have only numbers and NA, you can calculate the max of each row while removing NA:

library(data.table)
inputDT[, .(COL2 = do.call(pmax, c(na.rm=TRUE, .SD)))]

  COL2
1:    1
2:    2
3:    3

edited Mar 08 '20 at 23:38

answered Mar 08 '20 at 23:31

dc37

15,840
4
15
32

Thanks ! I try but when I called back `inputDT` I get the former data.table. – dc37 Mar 08 '20 at 23:37
An alternative to `do.call()` is `reduce()`: `inputDT[, .(COL2 = purrr::reduce(.SD, pmax, na.rm = TRUE))]`. `purrr::reduce()` is like base R's `Reduce()` but has the advantage that you can pass additional arguments to the function without having to write an anonymous function. – Uwe Mar 08 '20 at 23:43
@Uwe, thanks for the tip. I don't know much about `purr` package, trying to learn it (slowly). – dc37 Mar 08 '20 at 23:45
@Uwe the current solution *is* passing additional arguments to pmax() without having to write an anonymous function. – s_baldur Mar 09 '20 at 08:23
@sindri_baldur, Yes, indeed. My comment concerning passing additional arguments does _not_ refer to dc37's answer but refers solely to my suggestion of an alternative approach to use `purrr::reduce()` instead of `do.call()`. I was discussing the advantages of `purrr::reduce()` vs. `Reduce()`. – Uwe Mar 09 '20 at 08:31

R data.table merge two columns from the same table

2 Answers2