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))
CodePudding user response：
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
CodePudding user response：
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