CODEWITHSUNDEEP
rdataframedplyrreshape
brucezepplin
brucezepplin

Reputation: 9782

Collapse all cells in each column to one cell and ignore NA

If I have a dataframe as follows:

a <- data.frame(col1 = c("A",NA,NA),
                col2 = c(NA,"B","C"),
                col3 = c(NA,NA,"D"))

  col1 col2 col3
1    A <NA> <NA>
2 <NA>    B <NA>
3 <NA>    C    D

How can I collapse to a dataframe like:

col1   col2   col3
----   ----   ----
A      B,C    D

I know I can do something like:

gsub("NA,","",paste(a,1],collapse=","))

To collapse one column at a time and then remove the remaining NA, then cbind each column together, but there must be an easier way....

Upvotes: 1

Views: 300

Answers (4)

akrun
akrun

Reputation: 887541

An option with pivot_longer and summarise

library(dplyr)
library(tidyr)
a  %>% 
   pivot_longer(cols = everything(), values_drop_na = TRUE) %>% 
   group_by(name) %>% 
   summarise(value = toString(value))
# A tibble: 3 x 2
#  name  value
#  <chr> <chr>
#1 col1  A    
#2 col2  B, C 
#3 col3  D

Upvotes: 0

Georgery
Georgery

Reputation: 8117

The tidyverse version:

If you wanna have a clean workspace like me, might wanna write a function and apply it to all columns.

library(dplyr)

a <- data.frame(col1 = c("A",NA,NA),
                col2 = c(NA,"B","C"),
                col3 = c(NA,NA,"D"))

collapseColumn <- function(x){
    paste(
        x[!is.na(x)]
        , collapse = ",")
}


a %>%
    summarise_all(collapseColumn)

Upvotes: 0

NicolasH2
NicolasH2

Reputation: 804

apply(a, 2, function(x) paste(na.omit(x), collapse=","))

Upvotes: 1

Sotos
Sotos

Reputation: 51592

You can simply do,

sapply(a, function(i)toString(i[!is.na(i)]))

Upvotes: 2

Related Questions