R collapse rows by group with non-missing values when values are character

user_12672193 0
Viewed 860

I'm trying to collapse/aggregate/summarise rows by group keeping only non-missing values, where values are characters. Here's a reproducible example.

df = data.frame(store = c("A","A", "B","B"),
                item1=c("apple","","milk",""),
                item2=c("","pear","","bread"))

df
  store item1 item2
1     A apple      
2     A        pear
3     B  milk      
4     B       bread

I hope to change df as the following

df2
  store item1 item2
1     A apple  pear
2     B  milk bread

I've tried using summarise_all with nchar(.) > 0as the following, but it doesn't seem to work. 

df %>%
  group_by(store) %>%
  summarise_all( ~ + any(nchar(.) > 0))

Any comments would be appreciated!

dplyr r
2 Answers

You can do:

df %>%
 group_by(store) %>%
 summarise_all(~ .[nchar(.) > 1])

  store item1 item2
  <chr> <chr> <chr>
1 A     apple pear 
2 B     milk  bread
user_59645570

1) pivot Reshape to long form, remove the "" elements and reshape back.

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(-1) %>%
  filter(value != "") %>%
  pivot_wider

giving:

# A tibble: 2 x 3
  store item1 item2
  <fct> <fct> <fct>
1 A     apple pear 
2 B     milk  bread

2) max Another approach is to take the maximum value within group excluding NA's. This converts the item columns to character whereas the output of (1) is factor columns.

library(dplyr)

df %>%
  group_by(store) %>%
  summarize_all(~ max(as.character(.), na.rm = TRUE)) %>%
  ungroup

giving:

# A tibble: 2 x 3
  store item1 item2
  <fct> <chr> <chr>
1 A     apple pear 
2 B     milk  bread
user_5165480
Related