Combining rows to form new columns in matrix

Lets say i have the following matrix

1 10
1 11
1 12
2 13
3 14
3 15
4 16
5 17
5 18
5 19
6 20

Can someone suggest a function that will combine rows with same first value such that

1 10 11 12
2 13
3 14 15
4 16
5 17 18 19
6 20

It is not pretty, but it gets there.

library(dplyr, warn.conflicts = FALSE)
library(tidyr)
MAT <- matrix(c(1,1,1,2,3,3,4,5,5,5,6,10:20), ncol = 2)
DF <- as.data.frame(MAT)
DF <- DF %>% group_by(V1) %>% 
  mutate(Index = row_number(),
         ColName = LETTERS[Index]) %>% 
  select(-Index)
DF
#> # A tibble: 11 x 3
#> # Groups:   V1 [6]
#>       V1    V2 ColName
#>    <dbl> <dbl> <chr>  
#>  1     1    10 A      
#>  2     1    11 B      
#>  3     1    12 C      
#>  4     2    13 A      
#>  5     3    14 A      
#>  6     3    15 B      
#>  7     4    16 A      
#>  8     5    17 A      
#>  9     5    18 B      
#> 10     5    19 C      
#> 11     6    20 A
DF <- DF %>% pivot_wider(names_from = ColName, values_from = V2)
as.matrix(DF)
#>      V1  A  B  C
#> [1,]  1 10 11 12
#> [2,]  2 13 NA NA
#> [3,]  3 14 15 NA
#> [4,]  4 16 NA NA
#> [5,]  5 17 18 19
#> [6,]  6 20 NA NA

Created on 2020-12-14 by the reprex package (v0.3.0)

If you want them as text you can also paste() them together:

library(dplyr, warn.conflicts = FALSE)
library(tidyr)
MAT <- matrix(c(1,1,1,2,3,3,4,5,5,5,6,10:20), ncol = 2)
DF <- as.data.frame(MAT)

DF %>%
  group_by(V1) %>%
  summarize(V2_concat = paste(V2, collapse=" "))
#> `summarise()` ungrouping output (override with `.groups` argument)
#> # A tibble: 6 x 2
#>      V1 V2_concat
#>   <dbl> <chr>    
#> 1     1 10 11 12 
#> 2     2 13       
#> 3     3 14 15    
#> 4     4 16       
#> 5     5 17 18 19 
#> 6     6 20

Created on 2020-12-15 by the reprex package (v0.3.0)

Or if you need to reuse it later you can also store them as a list-column (not intuitive if you never saw it before, but very powerful, since you can just loop on the list as needed):

DF %>%
  group_by(V1) %>%
  summarize(V2_concat = list(V2))
#> `summarise()` ungrouping output (override with `.groups` argument)
#> # A tibble: 6 x 2
#>      V1 V2_concat
#>   <dbl> <list>   
#> 1     1 <dbl [3]>
#> 2     2 <dbl [1]>
#> 3     3 <dbl [2]>
#> 4     4 <dbl [1]>
#> 5     5 <dbl [3]>
#> 6     6 <dbl [1]>

Created on 2020-12-15 by the reprex package (v0.3.0)

This topic was automatically closed 21 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.