Is there a tidyr equivalent way of doing this? What I have works, but I'm just trying to learn the tidyr functions. Spent about an hour trying to coerce chatGPT into showing me how to do it and nothing worked. I have it broken out into multiple steps just to help me troubleshoot. I will eventually recombine into one step using pipes...
Note: The dataframe itself is pretty massive with 56000 rows and 1000 columns. I don't have a way to share it so including head() output below. I'm not sure what the minimum information needed is to help.
# Step 1: Create the key column
temp1 <- BRes3 %>%
mutate(key1 = paste(Group, n, sep = "-"))
# Step 2: Remove unnecessary columns
temp2 <- temp1 %>%
select(key1, everything(), -mean, -`mean CI`, -sdev, -UCI, -LCI, -CIR, -n, -Group)
# Step 3: Transpose the data and set column names
temp3 <- setNames(data.frame(t(temp2[-1])), temp2$key1)
Structure of data frame at step2:
> head(temp2[1:5])
key1 est1 est2 est3 est4
X2 X1.A-2 10.799982 10.304256 10.20124 9.550119
X3 X1.A-3 10.3659866666667 10.465434 11.1673413333333 10.8014426666667
X4 X1.A-4 10.679331 10.781562 10.025603 10.1510665
X5 X1.A-5 11.4224648 11.6025256 10.5983192 11.618744
X6 X1.A-6 10.1707603333333 11.0518533333333 10.416587 10.18374
X7 X1.A-7 10.590982 10.6979774285714 10.6989508571429 10.1854577142857
Desired final structure:
> head(temp3[1:5])
X1.A-2 X1.A-3 X1.A-4 X1.A-5 X1.A-6
est1 10.799982 10.3659866666667 10.679331 11.4224648 10.1707603333333
est2 10.304256 10.465434 10.781562 11.6025256 11.0518533333333
est3 10.20124 11.1673413333333 10.025603 10.5983192 10.416587
est4 9.550119 10.8014426666667 10.1510665 11.618744 10.18374
est5 10.800006 9.970464 11.5241455 11.6005004 10.9990463333333
est6 12.30192 11.0995906666667 10.449673 10.2591216 10.7492366666667
If all columns except key1 are the same class (numeric here), the we can do (starting with temp2):
library(tidyr)
temp2 |>
pivot_longer(cols = where(is.numeric)) |>
pivot_wider(id_cols = name, names_from = "key1")
# # A tibble: 4 × 7
# name `X1.A-2` `X1.A-3` `X1.A-4` `X1.A-5` `X1.A-6` `X1.A-7`
# <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
# 1 est1 10.8 10.4 10.7 11.4 10.2 10.6
# 2 est2 10.3 10.5 10.8 11.6 11.1 10.7
# 3 est3 10.2 11.2 10.0 10.6 10.4 10.7
# 4 est4 9.55 10.8 10.2 11.6 10.2 10.2
Data
temp2 <- structure(list(key1 = c("X1.A-2", "X1.A-3", "X1.A-4", "X1.A-5", "X1.A-6", "X1.A-7"), est1 = c(10.799982, 10.3659866666667, 10.679331, 11.4224648, 10.1707603333333, 10.590982), est2 = c(10.304256, 10.465434, 10.781562, 11.6025256, 11.0518533333333, 10.6979774285714), est3 = c(10.20124, 11.1673413333333, 10.025603, 10.5983192, 10.416587, 10.6989508571429), est4 = c(9.550119, 10.8014426666667, 10.1510665, 11.618744, 10.18374, 10.1854577142857)), class = "data.frame", row.names = c("X2", "X3", "X4", "X5", "X6", "X7"))