for multiple columns in data frame reshaping from wide to long in R

user_9323156 0
Viewed 36

I need to reshape my data (a subset as below) from wide to long format. I have tried reshape() and pivot_longer(), but I could not make what I need. I have added the part of output of long format I would like to make. I really appreciate your help.

structure(list(id = c(1, 2, 3, 4), g6m = c("12", "11", "16.9", 
"17.1"), g12m = c("18", "11", "11.1", "19.5"), g18m = c("29", 
"11.6", "12.3", "17"), g24m = c("12", "13.6", "10", "11"), age = c(45, 
37, 51, 47), gender = c(0, 1, 0, 1), ms = c("11.136", "12.18", 
"19.01", "10.01")), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-4L))

enter image description here

reshape dataframe r
2 Answers

You could do:

library(tidyverse)

pivot_longer(df, matches("g\\d+m"), values_to = "g", names_to = "time") %>%
  mutate(time = match(time, colnames(df)) - 1)
#> # A tibble: 16 x 6
#>       id   age gender ms      time g    
#>    <dbl> <dbl>  <dbl> <chr>  <dbl> <chr>
#>  1     1    45      0 11.136     1 12   
#>  2     1    45      0 11.136     2 18   
#>  3     1    45      0 11.136     3 29   
#>  4     1    45      0 11.136     4 12   
#>  5     2    37      1 12.18      1 11   
#>  6     2    37      1 12.18      2 11   
#>  7     2    37      1 12.18      3 11.6 
#>  8     2    37      1 12.18      4 13.6 
#>  9     3    51      0 19.01      1 16.9 
#> 10     3    51      0 19.01      2 11.1 
#> 11     3    51      0 19.01      3 12.3 
#> 12     3    51      0 19.01      4 10   
#> 13     4    47      1 10.01      1 17.1 
#> 14     4    47      1 10.01      2 19.5 
#> 15     4    47      1 10.01      3 17   
#> 16     4    47      1 10.01      4 11
user_125003150

In principle same as @Allan Cameron. Just adapted to your desired output:

library(tidyr)
library(dplyr)
df %>% 
  pivot_longer(
    cols = -c(id, age, gender, ms),
    names_to = "x",
    values_to = "g"
  ) %>% 
  group_by(id) %>% 
  mutate(time = row_number(), .before=2) %>% 
  select(id, time, g, age, gender, ms)

    id  time g       age gender ms    
   <dbl> <int> <chr> <dbl>  <dbl> <chr> 
 1     1     1 12       45      0 11.136
 2     1     2 18       45      0 11.136
 3     1     3 29       45      0 11.136
 4     1     4 12       45      0 11.136
 5     2     1 11       37      1 12.18 
 6     2     2 11       37      1 12.18 
 7     2     3 11.6     37      1 12.18 
 8     2     4 13.6     37      1 12.18 
 9     3     1 16.9     51      0 19.01 
10     3     2 11.1     51      0 19.01 
11     3     3 12.3     51      0 19.01 
12     3     4 10       51      0 19.01 
13     4     1 17.1     47      1 10.01 
14     4     2 19.5     47      1 10.01 
15     4     3 17       47      1 10.01 
16     4     4 11       47      1 10.01
user_133216470
Related