I have a dataframe in R formatted as shown in the first table below. I would like to merge columns "M1.1," "M1.2," and "M1.3" into a single column "M1" so that entries are on their own rows (id and values in other columns would be repeated) as shown in the second table. What function(s) could I use to accomplish this?
id | M1.1 | M1.2 | M1.3 | M2 | M3 | M4 | M5 | M6 |
---|---|---|---|---|---|---|---|---|
test a | test t | test a | test y | test | test t | test y | test u | test w |
test s | test r | test a | test h | test r | test j | test j | test w | test d |
id | M1 | M2 | M3 | M4 | M5 | M6 |
---|---|---|---|---|---|---|
test a | test t | test | test t | test y | test u | test w |
test a | test a | test | test t | test y | test u | test w |
test a | test y | test | test t | test y | test u | test w |
test s | test r | test r | test j | test j | test w | test d |
test s | test a | test r | test j | test j | test w | test d |
test s | test h | test r | test j | test j | test w | test d |
We could use pivot_longer
:
library(dplyr)
library(tidyr)
df %>%
pivot_longer(
cols = c(M1.1, M1.2, M1.3),
names_to = "names",
values_to = "M1"
) %>%
select(id, M1, M2:M6)
A tibble: 6 x 7
id M1 M2 M3 M4 M5 M6
<chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 test a test t test test t test y test u test w
2 test a test a test test t test y test u test w
3 test a test y test test t test y test u test w
4 test s test r test r test j test j test w test d
5 test s test a test r test j test j test w test d
6 test s test h test r test j test j test w test d
data:
structure(list(id = c("test a", "test s"), M1.1 = c("test t",
"test r"), M1.2 = c("test a", "test a"), M1.3 = c("test y", "test h"
), M2 = c("test", "test r"), M3 = c("test t", "test j"), M4 = c("test y",
"test j"), M5 = c("test u", "test w"), M6 = c("test w", "test d"
)), row.names = c(NA, -2L), class = c("tbl_df", "tbl", "data.frame"
))