Search code examples
rreshapereshape2dcast

Formula for dcast'ing a data.frame

I have a data.frame with colnames: A01, A02, ..., A25, ..., Z01, ..., Z25 (altogether 26*25). For example:

set.seed(1)
df <- data.frame(matrix(rnorm(26*25),ncol=26*25,nrow=1))
cols <- c(paste("0",1:9,sep=""),10:25)
colnames(df) <- c(sapply(LETTERS,function(l) paste(l,cols,sep="")))

and I want to dcast it to a data.frame of 26x25 (rows will be A-Z and columns 01-25). Any idea what would be the formula for this dcast?

Solution

  • We can use tidyverse

    library(tidyverse)
res <- gather(df) %>%
           group_by(key = sub("\\D+", "", key))  %>% 
           mutate(n = row_number()) %>%
           spread(key, value) %>%
           select(-n)
dim(res)
#[1] 26 25