CODEWITHSUNDEEP
rtidyr
Nicola Gambaro
Nicola Gambaro

Reputation: 53

R convert column names into rows

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.

Upvotes: 1

Views: 227

Answers (1)

Anoushiravan R
Anoushiravan R

Reputation: 21908

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

Upvotes: 2

Related Questions