user3022875 user3022875 - 3 months ago 7
R Question

column to rows but holding on to common column names

I have a data frame that looks like this:

data.frame(group1_a_mu = 10, group1_b_sd = 4, group1_c_xx = 5, group2_a_mu=1, group2_b_sd=2, gorup2_c_xx = 14, stringsAsFactors = FALSE)

group1_a_mu group1_b_sd group1_c_xx group2_a_mu group2_b_sd gorup2_c_xx
1 10 4 5 1 2 14


and I would like to transform it to this:

mu sd xx
group1 10 4 5
group2 1 2 14


how can one do that?

Answer

You could try the following:

library(dplyr)
library(tidyr)
data.frame(group1_a = 10, group1_b = 4, group1_c = 5, group2_a=1, group2_b=2, group2_c = 14, stringsAsFactors = FALSE) %>%
    gather(key, val) %>%
    separate(key, c('group_name', 'subgroup_name'), sep = '_') %>%
    spread(subgroup_name, val)

##   group_name  a b  c
## 1     group1 10 4  5
## 2     group2  1 2 14