Search code examples
rreshapeunpivotdata-wrangling

How to unpivot specific columns in R dataframe


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

Solution

  • 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"
    ))