R: Transforming Columns into Rows Dataset-CodePudding

I have this dataframe:

# A tibble: 6 x 5
  Full.Name            freq_2019 freq_2020 Ra_2019 Ra_2020
  <chr>                    <dbl>     <dbl>   <dbl>   <dbl>
1 A. Patrick Beharelle     1.06      1.43    0.110  -0.116
2 Aaron P. Graft          -1.50     -2.48    0.276   0.376
3 Aaron P. Jagdfeld       -2.42     21.8     0.745   0.889
4 Adam H. Schechter       -2.04     -0.470   0.299   0.296
5 Adam P. Symson           0.563     1.05    0.139   0.292
6 Adena T. Friedman       -0.406    -0.110   0.309   0.242

I want to transform the dataframe to this structure: There should be a relatively easy way to do this, but I cannot do it. It always messes up the Names. So any help is appreciated.

CodePudding user response：

A possible solution:

library(tidyverse)

df %>% 
  pivot_longer(-Full.Name) %>% 
  separate(name, into = c("name", "Y"), sep = "_") %>% 
  pivot_wider(c(Full.Name, Y)) %>% 
  relocate(Y, .after = last_col())

#> # A tibble: 12 × 4
#>    Full.Name              freq     Ra Y    
#>    <chr>                 <dbl>  <dbl> <chr>
#>  1 A. Patrick Beharelle  1.06   0.11  2019 
#>  2 A. Patrick Beharelle  1.43  -0.116 2020 
#>  3 Aaron P. Graft       -1.5    0.276 2019 
#>  4 Aaron P. Graft       -2.48   0.376 2020 
#>  5 Aaron P. Jagdfeld    -2.42   0.745 2019 
#>  6 Aaron P. Jagdfeld    21.8    0.889 2020 
#>  7 Adam H. Schechter    -2.04   0.299 2019 
#>  8 Adam H. Schechter    -0.47   0.296 2020 
#>  9 Adam P. Symson        0.563  0.139 2019 
#> 10 Adam P. Symson        1.05   0.292 2020 
#> 11 Adena T. Friedman    -0.406  0.309 2019 
#> 12 Adena T. Friedman    -0.11   0.242 2020

CodePudding user response：

A slightly simpler solution would be

library(tidyr)

pivot_longer(df, -1, names_sep = "_", names_to = c(".value", "Year"))
#> # A tibble: 12 x 4
#>    Full.Name            Year    freq     Ra
#>    <chr>                <chr>  <dbl>  <dbl>
#>  1 A. Patrick Beharelle 2019   1.06   0.11 
#>  2 A. Patrick Beharelle 2020   1.43  -0.116
#>  3 Aaron P. Graft       2019  -1.5    0.276
#>  4 Aaron P. Graft       2020  -2.48   0.376
#>  5 Aaron P. Jagdfeld    2019  -2.42   0.745
#>  6 Aaron P. Jagdfeld    2020  21.8    0.889
#>  7 Adam H. Schechter    2019  -2.04   0.299
#>  8 Adam H. Schechter    2020  -0.47   0.296
#>  9 Adam P. Symson       2019   0.563  0.139
#> 10 Adam P. Symson       2020   1.05   0.292
#> 11 Adena T. Friedman    2019  -0.406  0.309
#> 12 Adena T. Friedman    2020  -0.11   0.242

^{Created on 2022-06-10 by the reprex package (v2.0.1)}