I’m trying to pivot multiple values at a time.
- I have this:
> head(data)
# A tibble: 6 x 3
variable `Mean (SD)` `Median (IQR)`
<chr> <glue> <glue>
1 VarA_VVV_Cond1 268.59 (80.6) 276 (86)
2 VarA_WWW_Cond1 149.07 (39.79) 155 (40.5)
3 VarA_XXX_Cond1 147.71 (39.65) 155 (41)
4 VarA_YYY_Cond1 18.85 (10.76) 18 (15.5)
5 VarA_ZZZ_Cond1 20.98 (11.34) 20 (14)
6 VarA_VVV_Cond2 228.49 (83.77) 241 (116)
- the desired output is this:
VVV(Mean/SD) VVV(Median/IQR) XXX(Mean/SD) XXX(Median/IQR)...
VAR_A_Cond_1
VAR_A_Cond_2
VAR_B_Cond_1
VAR_B_Cond_2
- this is my dataset:
> dput(data)
structure(list(variable = c("VarA_VVV_Cond1", "VarA_WWW_Cond1",
"VarA_XXX_Cond1", "VarA_YYY_Cond1", "VarA_ZZZ_Cond1", "VarA_VVV_Cond2",
"VarA_WWW_Cond2", "VarA_XXX_Cond2", "VarA_YYY_Cond2", "VarA_ZZZ_Cond2",
"VarB_VVV_Cond1", "VarB_WWW_Cond1", "VarB_XXX_Cond1", "VarB_YYY_Cond1",
"VarB_ZZZ_Cond1", "VarB_VVV_Cond2", "VarB_WWW_Cond2", "VarB_XXX_Cond2",
"VarB_YYY_Cond2", "VarB_ZZZ_Cond2"), `Mean (SD)` = structure(c("268.59 (80.6)",
"149.07 (39.79)", "147.71 (39.65)", "18.85 (10.76)", "20.98 (11.34)",
"228.49 (83.77)", "113.66 (35.91)", "112.64 (35.75)", "24.07 (15.79)",
"26.36 (16.51)", "250.72 (61.53)", "140.71 (30.52)", "138.93 (30.37)",
"21.02 (10.46)", "22.72 (11.05)", "225.98 (81.32)", "112.43 (36.09)",
"111.1 (36.41)", "24.71 (16.77)", "26.59 (17.49)"), class = c("glue",
"character")), `Median (IQR)` = structure(c("276 (86)", "155 (40.5)",
"155 (41)", "18 (15.5)", "20 (14)", "241 (116)", "116 (51)",
"116 (48)", "23 (21.5)", "24 (22.5)", "259 (60)", "142 (36)",
"142 (34)", "21 (15)", "21 (15)", "244.5 (93.5)", "107.5 (51.5)",
"107 (50.75)", "24 (20.75)", "24.5 (21.75)"), class = c("glue",
"character"))), row.names = c(NA, -20L), class = c("tbl_df",
"tbl", "data.frame"))
- I’ve tried many things, but nothing had solved the issue:
like this:
data1 <- data %>%
tidyr::pivot_wider(.,
names_from = "variable",
values_from = c("Mean (SD)", "Median (IQR)")) %>%
pivot_longer(cols = 1:40,
names_to = c("Names"),
values_to = c("Mean_SD", "Median_IQR"))
-
Any thoughts??
Thanks in advance!!!!!! -
additional info:
I have 20 rows/obs in the original "data". Each is called: VarA_VVV_Cond1, which means that I have 2 variables: (A and B), 5 tests (VVV, WWW, XXX, YYY, ZZZ) and 2 conditions (Cond1, Cond2) . Given that, I also have the tests Mean (SD) and Median (IQR). That’s the idea.
Obs: I’ve seen many posts here concerning pivoting, but none seemes to account for this (like this, for example…I’d really appreaciate some help!
>Solution :
We may need to separate the column ‘variable’ before we do the pivoting to ‘wide’
library(dplyr)
library(tidyr)
library(stringr)
data %>%
mutate(variable = str_replace(variable, "^(\\w+)_(\\w+)_(\\w+)",
"\\1_\\3,\\2")) %>%
separate(variable, into = c("variable", "newcol"), sep = ",") %>%
pivot_wider(names_from = newcol, values_from = c(`Mean (SD)`,
`Median (IQR)`), names_glue = "{newcol}({.value})")%>%
rename_with(~ str_remove(str_replace(.x, "\\s+\\(", "/"), "\\)"), -variable)
-output
# A tibble: 4 × 11
variable `VVV(Mean/SD)` `WWW(Mean/SD)` `XXX(Mean/SD)` `YYY(Mean/SD)` `ZZZ(Mean/SD)` `VVV(Median/IQR)` `WWW(Median/IQR)` `XXX(Median/IQR)` `YYY(Median/IQR)`
<chr> <glue> <glue> <glue> <glue> <glue> <glue> <glue> <glue> <glue>
1 VarA_Cond1 268.59 (80.6) 149.07 (39.79) 147.71 (39.65) 18.85 (10.76) 20.98 (11.34) 276 (86) 155 (40.5) 155 (41) 18 (15.5)
2 VarA_Cond2 228.49 (83.77) 113.66 (35.91) 112.64 (35.75) 24.07 (15.79) 26.36 (16.51) 241 (116) 116 (51) 116 (48) 23 (21.5)
3 VarB_Cond1 250.72 (61.53) 140.71 (30.52) 138.93 (30.37) 21.02 (10.46) 22.72 (11.05) 259 (60) 142 (36) 142 (34) 21 (15)
4 VarB_Cond2 225.98 (81.32) 112.43 (36.09) 111.1 (36.41) 24.71 (16.77) 26.59 (17.49) 244.5 (93.5) 107.5 (51.5) 107 (50.75) 24 (20.75)
# … with 1 more variable: `ZZZ(Median/IQR)` <glue>