conditionally merge columns, r

Question

I have a data frame that contains patient survival data. I have a column for time to last follow up and a column for time to death. If the patient died, the numerical value of time will be listed in the time to death column, and not in the time to last follow up column; and vice versa if the patient is still alive. The opposite column, so if the patient is alive I am referring to the death column, there is a "[Not Available]" character string instead of a time component. Here is an example:

    follow up           death
       100         [Not Available]
 [Not Available]         300
      2000         [Not Available]

I want to conditionally merge the two columns into a single column keeping just the numerical values like this:

Time
1000
300
2000

EDIT

To make this more broadly applicable, and applicable to some other datasets I have, imagine if the "[Not Available]" is not consistent. In that it could be NA, na, [Not available], null, etc. How would I write a conditional statement to merge the columns in this case? Im imagining an if statement that will keep numerical values and ignore the various character strings. Of course, in a column of a dataframe, both the numerical and character values will be classified as characters, making this just a little bit harder. Ideas?

Check out `dplyr::coalesce()`. In the future it's easier to help you if you provide a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample data that can be copy/pasted into R to test. — MrFlick, Sep 20 '17 at 15:10

score 4 · Accepted Answer · answered Sep 20 '17 at 15:09

4

We can use coalesce from the dplyr package.

library(dplyr)

dt <- data_frame("follow up" = c(1000, NA, 2000),
                 "death" = c(NA, 300, NA))

dt2 <- dt %>%
  mutate(Time = coalesce(.$`follow up`, .$death))

dt2
# A tibble: 3 x 3
  `follow up` death  Time
        <dbl> <dbl> <dbl>
1        1000    NA  1000
2          NA   300   300
3        2000    NA  2000

answered Sep 20 '17 at 15:09

www

38,575
12
48
84

In this answer would I have to turn all of the "[Not Available]" strings to NAs first? – Phil_T Sep 20 '17 at 15:13
@Phil_T Yes. Something like `dt[dt == "[Not Available]"] <- NA` – www Sep 20 '17 at 15:14
I prefer `tidyr::gather(dt, event, time, na.rm=TRUE)` -- less hassle to write and a more usable format. – Frank Sep 20 '17 at 15:37

score 3 · Answer 2 · answered Sep 20 '17 at 15:14

3

Here is an option with base R

dt$Time <- do.call(pmax, c(dt, na.rm = TRUE))
dt$Time
#[1] 1000  300 2000

answered Sep 20 '17 at 15:14

akrun

874,273
37
540
662

Preston · Answer 3 · 2020-11-12T09:46:49.537

You can use dplyr's vectorized if_else function to acheive the effect that you need. Here is the doc page.

Try the below:

library(tidyverse)

t1 <- data_frame("follow up" = c(1000, NA, 2000),
             "death" = c(NA, 300, NA))

t2 <- t1 %>%
  mutate(Time = if_else(death != 'NA', death, follow_up))

Result:
  follow_up death Time
      <chr> <chr>  <chr>
1       100    NA    100
2        NA   300    300
3      2000    NA   2000

score 0 · Answer 4 · answered Sep 20 '17 at 15:11

This answer does not use logical operators or if statements (if you can provide an answer that does, I would greatly appreciate it), but it works:

Data2$followup <- gsub("[Not Available]", "", Data2$followup)
Data2$death <- gsub("[Not Available]", "", Data2$death)
Data2$time <- paste(Data2$followup, Data2$death, sep = "")
Data2$time <- gsub("\\[", "", gsub("\\]", "", Data2$time))

score 0 · Answer 5 · answered Sep 20 '17 at 15:11

Converting them to numeric and replacing NA with 0 and an arithmetic sum should give the desired output.

> ss <- data.frame(follow_up = c('100','[Not Available]','2000'),death = c('[Not Available]','300','[Not Available]'))
> 
> ss <- lapply(ss, function(x){ifelse(x == '[Not Available]', 0, as.numeric(x))})
Warning messages:
1: In ifelse(x == "[Not Available]", 0, as.numeric(x)) :
  NAs introduced by coercion
2: In ifelse(x == "[Not Available]", 0, as.numeric(x)) :
  NAs introduced by coercion
> 
> ss$new <- ss$follow_up + ss$death
> 
> data.frame(ss)
  follow_up death  new
1       100     0  100
2         0   300  300
3      2000     0 2000
>

BFL · Answer 6 · 2017-09-20T17:06:27.163

0

Use apply:

df <- data.frame("follow up" = c("1000", "[Not Available]", "2000"),
                 "death"     = c("[Not Available]", "300", "[Not Available]"))

df$Time <- apply(df, 1, function(row) as.numeric(row[row!="[Not Available]"]))

edited Sep 20 '17 at 17:06

answered Sep 20 '17 at 16:06

BFL

1
3

conditionally merge columns, r

6 Answers6