Merge 3 rows into one - grouping by row number

Here’s the input data frame:

df_in <- data.frame(ID = c(1, 1, 1, 1, 1, 1), Block = c(1, 1, 1, 1, 1, 1), value = c("item1", 1, 500, "item2", 1, 600))
df_in
  ID Block value
1  1     1 item1
2  1     1     1
3  1     1   500
4  1     1 item2
5  1     1     1
6  1     1   600

I would like to transform this data frame into one of the two following two options:

df_merged <- data.frame(ID = c(1, 1), Block = c(1, 1), value = c("item1, 1, 500", "item2, 1, 600"))
df_merged
  ID Block         value
1  1     1 item1, 1, 500
2  1     1 item2, 1, 600

Or even better:

df_merged2 <- data.frame(ID = c(1, 1), Block = c(1, 1), Item = c("item1", "item2"), Classification = c(1, 1), Time = c(500, 600))
df_merged2
  ID Block  Item Classification Time
1  1     1 item1              1  500
2  1     1 item2              1  600

I am not trying to group the different rows by a particular variable (ID or Block or item nr etc.) but solely by using the row numbers (rows 1-3, 4-6, 7-9, etc.) to group them into triplets.

Is there any way to let R automatically merge every 3 rows into 1, while combining the values of the value column either into one character string per row or assign them to 3 new columns? The order within the different rows of the value column is hereby the same throughout the whole data frame (Item, Classification, Time).

For the first option I was thinking of using the data_merge function as following, but I am currently unsure what to set as group_by:

data_merged <- df_in %>%
+     dplyr::group_by(???) %>%
+     dplyr::summarise(value = paste(value, collapse = ","))

I hope you can help me with this issue! Thank you in advance :slight_smile:

df_in <- data.frame(ID = c(1, 1, 1, 1, 1, 1),
                    Block = c(1, 1, 1, 1, 1, 1), 
                    value = c("item1", 1, 500, "item2", 1, 600))
library(tidyverse)
mutate(df_in,
  rn = ceiling(row_number() / 3)
) %>%
  group_by(ID, Block, rn) %>%
  summarise(vlist = paste0(value, collapse = ",")) %>%
  ungroup()
# A tibble: 2 x 4
     ID Block    rn vlist      
  <dbl> <dbl> <dbl> <chr>      
1     1     1     1 item1,1,500
2     1     1     2 item2,1,600

This worked perfectly. Thank you for your quick solution!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.