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 seems to account for this (like this, for example...I'd really appreciate some help!
CodePudding user response：
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>
CodePudding user response：
Something like this:
library(tidyverse)
df %>%
separate(variable, into = c("Var", "XXX", "Cond"), sep = "_") %>%
pivot_wider(names_from = XXX,
values_from = c(`Mean (SD)`,`Median (IQR)`)) %>%
mutate(x = paste(Var, Cond, sep = "_"), .keep="unused", .before=1) %>%
column_to_rownames("x")
Mean (SD)_VVV Mean (SD)_WWW Mean (SD)_XXX Mean (SD)_YYY Mean (SD)_ZZZ Median (IQR)_VVV Median (IQR)_WWW Median (IQR)_XXX Median (IQR)_YYY Median (IQR)_ZZZ
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) 20 (14)
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) 24 (22.5)
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) 21 (15)
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) 24.5 (21.75)
>
CodePudding user response：
This works too:
df[c('Var', 'Group', 'Cond')] <- str_split_fixed(df$variable, "_", n = Inf)
df %>%
pivot_wider(id_cols = c(Var, Cond),
values_from = c(`Mean (SD)`, `Median (IQR)`),
names_from = Group)
Var Cond `Mean (SD)_VVV` `Mean (SD)_WWW` `Mean (SD)_XXX` `Mean (SD)_YYY` `Mean (SD)_ZZZ` `Median (IQR)_VVV`
<chr> <chr> <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)
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)
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)
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)