I’m new to R, I was wondering what is the most efficient way to convert my data into my Desired_Output below?
m="
n min max median q1 q3 group m SD
1 30 30 55 44.5 43.25 49.75 treat 45.38524 5.593169
2 30 31 55 47.0 44.00 49.00 treat 46.11951 4.886821
3 30 40 55 48.0 45.00 51.00 treat 47.92676 4.173242
4 15 30 51 44.0 42.50 45.50 treat 43.21604 4.245150
5 15 31 54 46.0 42.50 48.50 treat 44.94723 5.759449
6 15 44 55 48.0 45.00 48.50 treat 47.66393 3.012334
7 15 39 55 49.0 44.00 52.00 treat 48.01439 5.571240
8 15 41 55 48.0 44.50 50.00 treat 47.59677 4.261415
9 15 40 55 47.0 45.00 50.00 treat 47.38081 4.200670
10 18 42 55 46.0 44.00 49.50 cont 46.91764 3.996259
11 18 40 55 44.0 43.00 47.00 cont 45.25704 3.667377
12 18 41 55 44.5 44.00 50.00 cont 46.58674 4.334604
13 9 42 49 46.0 43.00 48.00 cont 45.60879 3.357931
14 9 42 48 44.0 43.00 45.00 cont 44.29745 1.878592
15 9 41 55 44.0 44.00 45.00 cont 45.43229 2.779801
16 9 43 55 50.0 44.00 52.00 cont 48.73261 5.506545
17 9 43 55 46.0 44.00 51.00 cont 47.61981 5.069204
18 9 41 55 50.0 44.00 51.00 cont 48.19267 5.403842"
data <- read.table(text=m, h=T)
Desired_Output="
nT mT sdT nC mC sdC
30 45.38524 5.593169 18 46.91764 3.996259
30 46.11951 4.886821 18 45.25704 3.667377
30 47.92676 4.173242 18 46.58674 4.334604
. . . . . .
. . . . . .
. . . . . .
15 47.38081 4.200670 9 48.19267 5.403842"
>Solution :
It’s little messy, but you may try using dplyr
library(dplyr)
data <- data %>%
group_by(group) %>%
mutate(idx = 1:n()) %>%
select(group, n, idx, m, SD) %>%
ungroup
df1 <- data %>%
filter(group == "treat") %>%
select(-group)
df2 <- data %>%
filter(group == "cont") %>%
select(-group)
df <- df1 %>%
left_join(df2, by = "idx") %>%
select(-idx)
names(df) <- c("nT", "mT", "sdT", "nC", "mC", "sdC")
df
nT mT sdT nC mC sdC
<int> <dbl> <dbl> <int> <dbl> <dbl>
1 30 45.4 5.59 18 46.9 4.00
2 30 46.1 4.89 18 45.3 3.67
3 30 47.9 4.17 18 46.6 4.33
4 15 43.2 4.25 9 45.6 3.36
5 15 44.9 5.76 9 44.3 1.88
6 15 47.7 3.01 9 45.4 2.78
7 15 48.0 5.57 9 48.7 5.51
8 15 47.6 4.26 9 47.6 5.07
9 15 47.4 4.20 9 48.2 5.40
Or using tidyr::pivot_wider,
library(tidyr)
data %>%
group_by(group) %>%
mutate(idx = 1:n()) %>%
select(group, n, idx, m, SD) %>%
ungroup %>%
pivot_wider(id_cols = idx, values_from = c(n, m, SD), names_from = group) %>%
select(-idx)
n_treat n_cont m_treat m_cont SD_treat SD_cont
<int> <int> <dbl> <dbl> <dbl> <dbl>
1 30 18 45.4 46.9 5.59 4.00
2 30 18 46.1 45.3 4.89 3.67
3 30 18 47.9 46.6 4.17 4.33
4 15 9 43.2 45.6 4.25 3.36
5 15 9 44.9 44.3 5.76 1.88
6 15 9 47.7 45.4 3.01 2.78
7 15 9 48.0 48.7 5.57 5.51
8 15 9 47.6 47.6 4.26 5.07
9 15 9 47.4 48.2 4.20 5.40