When I organize data sheet, I made this way.
A <- data.frame(
Season = rep(2023, 5),
crop = rep("Soybean", 5),
treatment = rep("Inside panel", 5),
plot = rep(6, 5),
row = 1:5,
rep1 = c(24.30, 30.30, 21.80, 22.90, 20.60),
rep2 = c(10.30, 0.00, 29.30, 0.00, 30.80),
rep3 = c(30.70, 28.40, 15.50, 22.30, 17.40),
SS1 = c("tr1", "tr1", "tr1", "tr3", "tr1"),
SS2 = c("tr3", "tr2", "tr2", "tr2", "tr3"),
SS3 = c("tr2", "tr3", "tr3", "tr1", "tr2")
)
Season crop treatment plot row rep1 rep2 rep3 SS1 SS2 SS3
1 2023 Soybean Inside panel 6 1 24.3 10.3 30.7 tr1 tr3 tr2
2 2023 Soybean Inside panel 6 2 30.3 0.0 28.4 tr1 tr2 tr3
3 2023 Soybean Inside panel 6 3 21.8 29.3 15.5 tr1 tr2 tr3
4 2023 Soybean Inside panel 6 4 22.9 0.0 22.3 tr3 tr2 tr1
5 2023 Soybean Inside panel 6 5 20.6 30.8 17.4 tr1 tr3 tr2
SS1 align with rep1, and SS2 align with rep2, and SS3 align with rep3. For example, in the first row, 24.3 is for tr1, and 10.3 is for tr3, and 30.7 is for tr2.
What I want is to transpose SS columns into rows.
B=data.frame(
Season = rep(2023, 15),
crop = rep("Soybean", 15),
treatment = rep("Inside panel", 15),
plot = rep(6, 15),
row = rep(1:5, each = 3),
rep = rep(1:3, times = 5),
SS = c("tr1", "tr3", "tr2", "tr1", "tr2", "tr3", "tr1", "tr2", "tr3", "tr3", "tr2", "tr1", "tr1", "tr3", "tr2"),
yield = c(24.30, 10.30, 30.70, 30.30, 0.00, 28.40, 21.80, 29.30, 15.50, 22.90, 0.00, 22.30, 20.60, 30.80, 17.40))
Season crop treatment plot row rep SS yield
1 2023 Soybean Inside panel 6 1 1 tr1 24.3
2 2023 Soybean Inside panel 6 1 2 tr3 10.3
3 2023 Soybean Inside panel 6 1 3 tr2 30.7
4 2023 Soybean Inside panel 6 2 1 tr1 30.3
5 2023 Soybean Inside panel 6 2 2 tr2 0.0
6 2023 Soybean Inside panel 6 2 3 tr3 28.4
7 2023 Soybean Inside panel 6 3 1 tr1 21.8
8 2023 Soybean Inside panel 6 3 2 tr2 29.3
9 2023 Soybean Inside panel 6 3 3 tr3 15.5
10 2023 Soybean Inside panel 6 4 1 tr3 22.9
11 2023 Soybean Inside panel 6 4 2 tr2 0.0
12 2023 Soybean Inside panel 6 4 3 tr1 22.3
13 2023 Soybean Inside panel 6 5 1 tr1 20.6
14 2023 Soybean Inside panel 6 5 2 tr3 30.8
15 2023 Soybean Inside panel 6 5 3 tr2 17.4
Could you let me know how to convert transpose columns to rows, aligning with the original values?
>Solution :
We could do it this way: It is the compact mode of using pivot_longer twice:
We use names_to = c(".value", "set") and names_pattern = "(rep|SS)(\\d+)" to separate the column names into two parts. .value keeps the common part of the column names (rep and SS), and set keeps the numeric suffix (and is removed afterwords).
library(dplyr)
library(tidyr)
A %>%
pivot_longer(
cols = c(starts_with("rep"), starts_with("SS")),
names_to = c(".value", "set"),
names_pattern = "(rep|SS)(\\d+)"
) %>%
select(-set)
Season crop treatment plot row rep SS
<dbl> <chr> <chr> <dbl> <int> <dbl> <chr>
1 2023 Soybean Inside panel 6 1 24.3 tr1
2 2023 Soybean Inside panel 6 1 10.3 tr3
3 2023 Soybean Inside panel 6 1 30.7 tr2
4 2023 Soybean Inside panel 6 2 30.3 tr1
5 2023 Soybean Inside panel 6 2 0 tr2
6 2023 Soybean Inside panel 6 2 28.4 tr3
7 2023 Soybean Inside panel 6 3 21.8 tr1
8 2023 Soybean Inside panel 6 3 29.3 tr2
9 2023 Soybean Inside panel 6 3 15.5 tr3
10 2023 Soybean Inside panel 6 4 22.9 tr3
11 2023 Soybean Inside panel 6 4 0 tr2
12 2023 Soybean Inside panel 6 4 22.3 tr1
13 2023 Soybean Inside panel 6 5 20.6 tr1
14 2023 Soybean Inside panel 6 5 30.8 tr3
15 2023 Soybean Inside panel 6 5 17.4 tr2