Pivoting a large data set

Question

I have a csv that looks a bit like this (tabs added for readability):

Dimension,    Date,    Metric
A,            Mon,     23
A,            Tues,    25
B,            Mon,     7
B,            Tues,    9

I want to run some distance + hclust analysis, which I've done before. But I like (and perhaps need) it in this format:

Dimension,    Mon,    Tues
A,            23,     25
B,            7,      9

I could do this pretty easily in Excel with a pivot. The problem is I have ~10,000 dimensions and ~1,200 dates - so the source CSV is about 12M rows by 3 columns. I want ~10,000 rows by ~1,200 columns.

Is there a way I can do this transform in R? The logic of a little Python script to do this is simple, but I'm not sure how it'll handle such a large CSV - and I can't imagine this is a new issue. Don't want to reinvent the wheel!

Thanks for any tips :)

Answer 1

Or just a spread:

library(tidyr)
spread(df, Date, Metric)
  Dimension Mon Tues
1         a  23   25
2         b   7    9

Benchmarks

 library(microbenchmark)
 microbenchmark(spread(df, Date, Metric))
Unit: milliseconds
                     expr      min       lq     mean   median       uq      max neval
 spread(df, Date, Metric) 1.461595 1.491919 1.628366 1.566753 1.635374 2.606135   100
 microbenchmark(suppressMessages(dcast(dt, Dimension~Date)))
Unit: milliseconds
                                          expr      min       lq     mean   median       uq      max neval
 suppressMessages(dcast(dt, Dimension ~ Date)) 3.365726 3.416384 3.770659 3.471678 4.011316 7.235719   100

microbenchmark(suppressMessages(dcast.data.table(dt, Dimension~Date)))
Unit: milliseconds
                                                 expr      min      lq   

mean   median       uq
 suppressMessages(dcast.data.table(dt, Dimension ~ Date)) 2.375445 2.52218 2.7684 2.614706 2.703075
      max neval
 15.96149   100

and here data table without sppressMessages

Unit: milliseconds
                                   expr      min       lq     mean median       uq     max neval
 dcast.data.table(dt, Dimension ~ Date) 2.667337 3.428127 4.749301 4.0476 5.289618 14.3823   100

and here data table does not have to guess:

 microbenchmark(dcast.data.table(dt, Dimension ~ Date, value.var = "Metric"))
Unit: milliseconds
                                                         expr      min       lq    mean   median
 dcast.data.table(dt, Dimension ~ Date, value.var = "Metric") 2.077276 2.118707 2.28623 2.168667
       uq      max neval
 2.320579 5.780479   100

Answer 2

Just try:

library(data.table)
dt = data.table(Dimension=rep(letters[1:2], each=2), Date=c('Mon','Tues'), Metric=c(23,25,7,9))

dcast(dt, Dimension~Date)
#  Dimension Mon Tues
#1         a  23   25
#2         b   7    9