In R is there a regular expression to direct columns to specific rows when using pivot_wider()?

Question

The first three rows of my real data look like this;

# Real data example
fullname<-c("Argemone glauca", "Bacopa monnieri", "Brighamia insignis")
Mean.Germ.coef_0<-c(0.31, 0.768015267, 0.555758514)
Mean.Germ.coef_10<-c(0.119356725, 0.624444444, 0.479357585)
Mean.Germ.coef_20<-c(0.01, 0.202431661, 0.01)
Mean.Germ.coef_35<-c(0.01, 0.021111111, 0.01)
sd.germ.coef_0<-c(0.055079106, 0.148040638, 0.199485791)
sd.germ.coef_10<-c(0.15341342, 0.079546759, 0.068405754)
sd.germ.coef_20<-c(0, 0.059160256, 0)
sd.germ.coef_35<-c(0, 0.022308189, 0)
n_0<-c(5, 5, 5)
n_10<-c(5, 5, 5)    
n_20<-c(5, 5, 5)    
n_35<-c(5, 5, 5)
LRR10<-c(-0.954455598, -0.206947247, -0.147887029)
LRR_var10<-c(0.336731047, 0.010676627, 0.029840885)
LRR20<-c(-3.433987204, -1.333407261, -4.017748779)
LRR_var20<-c(0.006313648, 0.024512868, 0.025768057)
LRR35<-c(-3.433987204, -3.594010117, -4.017748779)
LRR_var35<-c(0.006313648, 0.230755613, 0.025768057)
df<-data.frame(fullname, Mean.Germ.coef_0, Mean.Germ.coef_10, Mean.Germ.coef_20, Mean.Germ.coef_35,
     sd.germ.coef_0, sd.germ.coef_10, sd.germ.coef_20, sd.germ.coef_35,
     LRR10, LRR_var10, LRR20, LRR_var20, LRR35, LRR_var35)

I need a long data.frame (or tibble) where the specific columns go into specific rows like this;

# Example output
fullname<-c(rep("Argemone glauca", 4), rep("Bacopa monnieri", 4), rep("Brighamia insignis", 4))
Treat<-rep(c(0, 10, 20, 35), 3)
Mean.Germ.coef<-c(0.31, 0.768015267, 0.555758514, 0.01, 0.202431661, 0.01,
                  0.01, 0.202431661, 0.01, 0.01, 0.021111111, 0.01)
sd.germ.coef<-c(0.055079106, 0.148040638, 0.199485791, 0.15341342, 0.079546759, 0.068405754,
               0, 0.059160256, 0, 0, 0.022308189, 0)
n<-rep(5, 12)
LRR<-c("NA", -0.954455598, -0.206947247, -0.147887029, 
       "NA", -3.433987204, -1.333407261, -4.017748779, 
       "NA", -3.433987204, -3.594010117, -4.017748779)
LRR_var<-c("NA", 0.336731047, 0.010676627, 0.029840885, 
           "NA", 0.006313648, 0.024512868, 0.025768057,
           "NA", 0.006313648, 0.230755613, 0.02576805)
output<-data.frame(fullname, Treat, Mean.Germ.coef, sd.germ.coef,
                   n, LRR, LRR_var)

I am trying to use tidyr::pivot_longer() but I think my problem is lacking an understanding of regular expressions.

This is a pretty common question. It's not clear what your `pivot_longer` attempt looked like but this should work: `df %>% pivot_longer(-Name, names_pattern="(\\w)(\\d+)", names_to = c(".value", "Treat"))` — MrFlick, Mar 04 '22 at 03:10
Thank you @MrFlick! This works on my dummy data but not on my real data. My column names are like "Mean.Germ.coef_0", "sd.germ.coef_10", etc. I having trouble with the regular expression. Any suggestions? Either way I will mark this as answered as it did work. — Dustin, Mar 04 '22 at 21:38
Maybe `df %>% pivot_longer(-Name, names_pattern="(.*?)_(\\d+)", names_to = c(".value", "Treat"))`. Hard to test without a proper reproducible example. — MrFlick, Mar 04 '22 at 21:43
@MrFlick, you are right. I have edited my question so that the first three rows of my real data are presented. Thank you so much! — Dustin, Mar 05 '22 at 01:10
If you have a new question after the first one as been answered, it's better to open a new question rather than changing it after it's been closed. But this can be done with `df %>% pivot_longer(-fullname, names_pattern="(.*?)_?(\\d+)", names_to=c(".value", "Treat"))`. I don't know where those first names came from but it's not helpful that they are so messy. — MrFlick, Mar 05 '22 at 05:54
Thank you @MrFlick. I have asked the new question at, https://stackoverflow.com/questions/71488411/is-there-a-regular-expression-to-direct-columns-to-specific-rows-when-using-pivo — Dustin, Mar 15 '22 at 20:09

In R is there a regular expression to direct columns to specific rows when using pivot_wider()?

0 Answers0