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