R: merge columns from same data.frame based on NA positions

Question

I have a dataframe like this:

df <- data.frame(theme1=c("hello",NA,NA,NA), theme2=c(NA,"world",NA,NA), theme3=c(NA,NA,"good_morning",NA), theme4=c(NA,NA,NA,"good_evening"))

theme1 theme2 theme3 theme4 1 hello NA NA NA 2 NA world NA NA 3 NA NA good_morning NA 4 NA NA NA good_evening

Now i want to obtain one column with preserving the row order:

**Theme_merged** hello world good_morning good_evening

Tries:

merge_themes <- data.frame(cbind(mycol = na.omit(unlist(data2_tst[18:23]))), stringsAsFactors = F)

The above code works but does not preserve the row order so when i want to place the vector back to the original dataframe it does not match anymore.

Real data:

dput(head(data2_tst[18:23], n = 50))
structure(list(Theme1 = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, "%Bedrukken%", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, "%Bedrukken%", NA, NA, NA, NA, NA, NA, NA, NA), Theme2 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "%Nieuwste|Nieuwe|201[6:7]%", 
"%Nieuwste|Nieuwe|201[6:7]%", "%Nieuwste|Nieuwe|201[6:7]%", NA, 
NA, NA, NA, NA, "%Nieuwste|Nieuwe|201[6:7]%", "%Nieuwste|Nieuwe|201[6:7]%", 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "%Nieuwste|Nieuwe|201[6:7]%", 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "%Nieuwste|Nieuwe|201[6:7]%", 
"%Nieuwste|Nieuwe|201[6:7]%"), Theme3 = c("%Nodig%", NA, "%Nodig%", 
"%Nodig%", "%Nodig%", NA, NA, "%Nodig%", NA, "%Nodig%", NA, NA, 
NA, NA, "%Nodig%", "%Nodig%", "%Nodig%", NA, NA, NA, NA, NA, 
NA, "%Nodig%", "%Nodig%", NA, NA, "%Nodig%", NA, "%Nodig%", "%Nodig%", 
"%Nodig%", NA, "%Nodig%", "%Nodig%", "%Nodig%", NA, NA, NA, "%Nodig%", 
"%Nodig%", NA, "%Nodig%", NA, "%Nodig%", "%Nodig%", NA, "%Nodig%", 
NA, NA), Theme4 = c(NA, "%Kopen%", NA, NA, NA, "%Kopen%", "%Kopen%", 
NA, "%Kopen%", NA, NA, NA, NA, NA, NA, NA, NA, "%Kopen%", "%Kopen%", 
NA, NA, "%Kopen%", "%Kopen%", NA, NA, "%Kopen%", "%Kopen%", NA, 
"%Kopen%", NA, NA, NA, NA, NA, NA, NA, "%Kopen%", "%Kopen%", 
"%Kopen%", NA, NA, NA, NA, "%Kopen%", NA, NA, "%Kopen%", NA, 
NA, NA), Theme5 = c(NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_), Theme6 = c(NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_)), .Names = c("Theme1", 
"Theme2", "Theme3", "Theme4", "Theme5", "Theme6"), row.names = 3:52, class = "data.frame")

(1) Please get rid of `cbind`. (2) In which cases `na.omit(unlist(df, use.names = FALSE))` doesn't preserve the order? I couldn't reproduce the problem. Always works for me as intended. — David Arenburg, Nov 03 '16 at 15:24
Maybe it works if i use the real data. I will ad it. All these things change the row order in alphabetical order in my machine. — Sander Van der Zeeuw, Nov 03 '16 at 15:34

score 1 · Answer 1 · edited Nov 03 '16 at 16:02

In SQL this would be the COALESCE function:

apply(df, 1, function(r) c(na.omit(r), NA)[1])
# [1] "hello"        "world"        "good_morning" "good_evening"

df <- data.frame(
    theme1=c("hello",NA,NA,NA), 
    theme2=c(NA,"world",NA,NA), 
    theme3=c(NA,NA,"good_morning",NA), 
    theme4=c(NA,NA,NA,"good_evening"),
    stringsAsFactors = FALSE
)

On your example data na.omit(unlist(df2, use.names = FALSE)) will work fine, but it will fail if there is a row of only NA values:

df2 <- data.frame(
    theme1=c("hello",NA,NA,NA,NA), 
    theme2=c(NA,"world",NA,NA,NA), 
    theme3=c(NA,NA,"good_morning",NA,NA), 
    theme4=c(NA,NA,NA,"good_evening",NA),
    theme5=c(NA_character_,NA_character_,NA_character_,
             NA_character_,NA_character_),
    stringsAsFactors = FALSE
)

df2$X <- na.omit(unlist(df2, use.names = FALSE))
# Error in `$<-.data.frame`(`*tmp*`, "X", value = c("hello", "world", "good_morning",  : 
#   replacement has 4 rows, data has 5

df2$X <- apply(df2, 1, function(r) c(na.omit(r), NA)[1])
#   theme1 theme2       theme3       theme4 theme5            X
# 1  hello   <NA>         <NA>         <NA>   <NA>        hello
# 2   <NA>  world         <NA>         <NA>   <NA>        world
# 3   <NA>   <NA> good_morning         <NA>   <NA> good_morning
# 4   <NA>   <NA>         <NA> good_evening   <NA> good_evening
# 5   <NA>   <NA>         <NA>         <NA>   <NA>         <NA>

Another option could be df2$X <- df2[cbind(1:nrow(df2), max.col(!is.na(df2)))]

@DavidArenburg I want to get rid of the theme columns and create only a merged column. — Sander Van der Zeeuw, Nov 03 '16 at 15:39
@DavidArenburg I took *"[...] when i want to place the vector back to the original dataframe it does not match anymore."* to mean that the result should have the same length as the number of rows in the input data. — nrussell, Nov 03 '16 at 15:41

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Version 0.5.0 of dplyr introduced the coalesce function:

This version of dplyr gains a number of vector functions inspired by SQL. Two functions make it a little easier to eliminate or generate missing values:

Given a set of vectors, coalesce() finds the first non-missing value in each position.

To apply this to the sample data frame you can use:

df <- mutate_all(df, .funs = as.character)
df$merged <- with(df, coalesce(theme1, theme2, theme3, theme4))

I found it necessary to convert from factors to character to avoid an 'invalid factor levels' error.

On your real data no conversion is necessary:

df$merged <- with(df, coalesce(Theme1, Theme2, Theme3, Theme4, Theme5, Theme6)

score 0 · Answer 3 · answered Nov 03 '16 at 15:20

0

Here's a tidyverse solution (uses dplyr and tidyr or just tidyverse)

library(tidyverse)

> df <- df %>% 
    gather("theme", "theme_merged", 1:4) %>%
    filter(!is.na(theme_merged)) %>% 
    select(theme_merged)

> df
  theme_merged
1        hello
2        world
3 good_morning
4 good_evening

answered Nov 03 '16 at 15:20

yeedle

4,918
1
22
22

thanks for your answer, but in my real data the order is not preserved. – Sander Van der Zeeuw Nov 03 '16 at 15:41
Is it ordered according to some other variable? Or alphabetically? Or is it a factor with ordered levels? – yeedle Nov 03 '16 at 15:44
the order should be preserved since this is only a short part of my big DF. And i need to add the merged columns back to the DF – Sander Van der Zeeuw Nov 03 '16 at 15:50

score 0 · Answer 4 · answered Nov 03 '16 at 16:27

This should work with your data:

new_df = c(as.matrix(df))

This line first converts the df to a matrix and binds all the columns in one vector with c().

new_df <- new_df[!is.na(new_df)]

And now we keep only the non-NA entries. If you want you can convert it back to a dataframe:

new_df <- data.frame(new_df);names(new_df) <- "Themes"

R: merge columns from same data.frame based on NA positions

4 Answers4