How to convert multiple rows with same ID to single rows in R Programming-CodePudding

I have a dataset1 which is as follows (table 1) :

structure(list(TCODE = c("T1", "T1", "T1", "T2", "T2", "T2", 
"T2", "T2", "T3", "T4", "T4", "T4", "T4"), MATERIAL = c("M105", 
"M100", "M102", "M345", "M400", "M100", "M220", "M260", "M400", 
"M390", "M400", "M100", "M700")), class = "data.frame", row.names = c(NA, 
-13L))

   TCODE MATERIAL
1     T1     M105
2     T1     M100
3     T1     M102
4     T2     M345
5     T2     M400
6     T2     M100
7     T2     M220
8     T2     M260
9     T3     M400
10    T4     M390
11    T4     M400
12    T4     M100
13    T4     M700

How to convert multiple rows into a single row based on first column? The desired output is : table 2

1 2 3 4 5

1 M105, M100, M102

2 M345, M400, M100, M220, M260

3 M400

4 M390, M400, M100, M700

Thanks.

CodePudding user response：

Using toString in aggregate.

aggregate(. ~ TCODE, dat, toString)
#   TCODE                     MATERIAL
# 1    T1             M105, M100, M102
# 2    T2 M345, M400, M100, M220, M260
# 3    T3                         M400
# 4    T4       M390, M400, M100, M700

Data:

dat <- structure(list(TCODE = c("T1", "T1", "T1", "T2", "T2", "T2", 
"T2", "T2", "T3", "T4", "T4", "T4", "T4"), MATERIAL = c("M105", 
"M100", "M102", "M345", "M400", "M100", "M220", "M260", "M400", 
"M390", "M400", "M100", "M700")), class = "data.frame", row.names = c(NA, 
-13L))

CodePudding user response：

You can do:

df <- data.frame(x = c(1, 1, 2, 2),
                 y = letters[1:4])

library(tidyverse)
df %>%
  group_by(x) %>%
  summarize(y = paste0(y, collapse = ', '))

# A tibble: 2 x 2
      x y    
  <dbl> <chr>
1     1 a, b 
2     2 c, d

CodePudding user response：

We could do it this way:

library(dplyr)
library(tidyr)

df %>% 
  group_by(TCODE) %>% 
  mutate(MATERIAL = toString(MATERIAL)) %>% 
  slice(1) %>% 
  mutate(MATERIAL = str_split(MATERIAL, ', ')) %>% 
  unnest_wider(MATERIAL)

  TCODE ...1  ...2  ...3  ...4  ...5 
  <chr> <chr> <chr> <chr> <chr> <chr>
1 T1    M105  M100  M102  NA    NA   
2 T2    M345  M400  M100  M220  M260 
3 T3    M400  NA    NA    NA    NA   
4 T4    M390  M400  M100  M700  NA