How to merge rows in a dataframe and combine factor-values in cells

1
votes

I have a dataframe in R that in which I want to merge certain rows and combine the values of certain cells in these rows. Imagine the following data frame:

Col.1<-c("a","b","b","a","c","c","c","d") Col.2<-c("mouse", "cat", "dog", "bird", "giraffe", "elephant", "zebra", "worm") df<-data.frame(Col.1, Col.2) df

Col.1 Col.2 a mouse b cat b dog a bird c giraffe c elephant c zebra d worm

I would like to merge all adjacent rows in which the values in Col.1 are the same and combine the values in Col.2 accordingly.

The final result should look like this:

Col.1 Col.2 a mouse b cat dog a bird c giraffe elephant zebra d worm

I have tried to use a dplyr-solution (like:ddply(df, .(Col.1), summarize, Col.2 = sum(Col.2))), but the sum-command doesn't work for factor-values.

2
rdataframemergerows

2 Answers

1
votes

We can do a group by paste. To do the grouping for adjacent similar elements, rleid from data.table can be used, and then summarise the values of 'Col.2' by pasteing

library(dplyr)
library(data.table)
library(stringr)
df %>%
    group_by(Col.1, grp = rleid(Col.1)) %>% 
    summarise(Col.2 = str_c(Col.2, collapse=' ')) %>%
    ungroup %>%
    select(-grp)
# A tibble: 5 x 2
#  Col.1 Col.2                 
#  <fct> <chr>                 
#1 a     mouse                 
#2 a     bird                  
#3 b     cat dog               
#4 c     giraffe elephant zebra
#5 d     worm

NOTE: This matches the output showed in the OP's post

1
votes

EDIT: missed the "adjacent" bit. See the solution using base function rle below from this question.

Col.1 <- c("a","b","b","a","c","c","c","d")
Col.2 <- c("mouse", "cat", "dog", "bird", "giraffe", "elephant", "zebra", "worm")
df <- tibble(Col.1, Col.2)

rlel <- rle(df$Col.1)$length
df %>% 
  mutate(adj = unlist(lapply(1:length(rlel), function(i) rep(i, rlel[i])))) %>%
  group_by(Col.1, adj) %>% 
  summarize(New.Col.2 = paste(Col.2, collapse = " ")) %>%
  ungroup %>% arrange(adj) %>% select(-adj)

# A tibble: 5 x 2
  Col.1 New.Col.2             
  <chr> <chr>                 
1 a     mouse                 
2 b     cat dog             
3 a     bird             
4 c     giraffe elephant zebra
5 d     worm