# 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.