R convert column names into rows

user_12247311 0
Viewed 150

This is my data frame:

    structure(list(alpha = c(0.1, 0.5), w = c(-0.00274974306224061, 
-0.000196166328114611), w_sd = c(0.0551810481758757, 0.0578592391790738
), x = c(33.1362337290378, 39.5506108643837), x_sd = c(3.1665690944253, 
3.326430870477), y = c(-5.31333502177223e-05, 0.000993858599634651
), y_sd = c(0.00281795557473473, 0.00521256102447982), z = c(7.36644492272648, 
0.948467218571388), z_sd = c(3.51648260725539, 3.66294238952056
)), row.names = c(NA, -2L), class = c("tbl_df", "tbl", "data.frame"
))

I would like it to look like this:

alpha | stock | mean | sd
0.1   |   x   
0.5   |   x
0.1   |   y
0.5   |   y
0.1   |   z
0.5   |   z
0.1   |   z
0.5   |   z

Where the mean and sd columns contain the values from w, x, etc and w_sd, x_sd, etc, respectively.

EDIT: Note that "alpha" is the input into a function through the argument parameter.

tidyr r
1 Answers

I hope this is what you are looking for:

  • I first had to make a slight modification to your column names which where merely a single letter as we needed them for mean values and they would then resemble your other columns
  • I then created new column names, one stock to store all the prefix letters and the other two specified by .value argument so that part of the name sd or mean will be defined as the variable containing the cell values
library(tidyr)

df %>%
  rename_with(~ gsub("([[:alpha:]])", "\\1_mean", .), !contains("sd") & !c(1)) %>%
  pivot_longer(!alpha, names_to = c("stock", ".value"), 
               names_pattern = "([[:alpha:]])_(.*)")

# A tibble: 8 x 4
  alpha stock       mean      sd
  <dbl> <chr>      <dbl>   <dbl>
1   0.1 w     -0.00275   0.0552 
2   0.1 x     33.1       3.17   
3   0.1 y     -0.0000531 0.00282
4   0.1 z      7.37      3.52   
5   0.5 w     -0.000196  0.0579 
6   0.5 x     39.6       3.33   
7   0.5 y      0.000994  0.00521
8   0.5 z      0.948     3.66
user_143145200
Related