rdataframepivottranspose

In R, how can specific columns be transposed to rows, aligning with the original values?


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

  • Update: see input of @Onyambu:

    library(dplyr)
    library(tidyr)
    
    A %>%
      pivot_longer(
        cols = c(starts_with("rep"), starts_with("SS")),
        names_to = c(".value", NA),
        names_pattern = "(rep|SS)(\\d+)"
      ) 
    

    First answer: 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