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 ; You should find a solution there, except if you want to avoid calling dplyr; you can otherwise also call it within DT. — arg0naut91
– arg0naut91, Commented Mar 8, 2020 at 22:59

Uwe · Accepted Answer · 2020-03-08 23:29:04Z

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 8, 2020 at 23:29

Uwe

42.8k13 gold badges97 silver badges143 bronze badges

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

1 Comment

LeGeniusII Over a year ago

Some of my columns have text fields, and this approach works.

dc37 · Accepted Answer · 2020-03-08 23:38:42Z

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 8, 2020 at 23:38

answered Mar 8, 2020 at 23:31

dc37

16.3k4 gold badges19 silver badges35 bronze badges

5 Comments

dc37 Over a year ago

Thanks ! I try but when I called back inputDT I get the former data.table.

Uwe Over a year ago

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.

dc37 Over a year ago

@Uwe, thanks for the tip. I don't know much about purr package, trying to learn it (slowly).

s_baldur Over a year ago

@Uwe the current solution is passing additional arguments to pmax() without having to write an anonymous function.

Uwe Over a year ago

@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().

Collectives™ on Stack Overflow

R data.table merge two columns from the same table

2 Answers 2

1 Comment

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related