Rearranging Rows and Columns R

Question

I have tried looking up solutions using reshape, but I can't figure out how to rearrange my table to exactly how I want it.

My table looks something like this:

myTable<-data.table("AA-TotalPop-Mean"=c(1), "AA-Fr-Mean"=c(33), "AA-SW-Mean"=c(2), 
                    "AA-TotalPop-Median" = c(11), "AA-Fr-Median"= c(3), "AA-SW-Median" = c(22))

I don't know how to write R code for how I want my R table to look in the end, but here is a screenshot of an excel table version:

enter image description here

score 2 · Answer 1 · edited Dec 03 '19 at 22:07

An option is melt from data.table, which can also take multiple patterns of column in measure argument

library(data.table)
nm1 <- unique(sub('.*-', '', names(myTable)))
melt(myTable, measure = patterns('Total', 'Fr', 'SW'), 
  value.name = c('AA_TotalPop', 'AA_Fr', 'AA_SW'), variable.name = 'Function')[, 
             Function := nm1[Function]][]
#    Function AA_TotalPop AA_Fr AA_SW
#1:     Mean           1    33     2
#2:   Median          11     3    22

score 0 · Answer 2 · answered Dec 03 '19 at 22:18

A tidyr solution would be:

library(tidyr)

gather(myTable) %>%
  separate(key, into = c('Var', 'Function'), sep="-(?=[^-]+$)") %>% 
  spread(Var, value)

#>   Function AA-Fr AA-SW AA-TotalPop
#> 1     Mean    33     2           1
#> 2   Median     3    22          11

Rearranging Rows and Columns R

2 Answers2