Fastest & most flexible way to chart over 2 million rows of flat file data?

Question

I'm collecting some system data in a flatfile, which has this format:

YYYY-MM-DD-HH24:MI:SS DD1 DD2 DD3 DD4 

Where DD1-DD4 are four items of data. An example of the file is this:

2011-02-01-13:29:53 16 8 7 68 2011-02-01-13:29:58 13 8 6 110 2011-02-01-13:30:03 26 25 1 109 2011-02-01-13:30:08 13 12 1 31 2011-02-01-13:30:14 192 170 22 34 2011-02-01-13:30:19 16 16 0 10 2011-02-01-13:30:24 137 61 76 9 2011-02-01-13:30:29 452 167 286 42 2011-02-01-13:30:34 471 177 295 11 2011-02-01-13:30:39 502 192 309 10 

The file is over 2 million rows, with data points every five seconds.

I need to chart this data to be able to derive meaning from it.

What I've tried

At the moment I've tried gnuplot and rrdtool with a variety of unix tools (awk, sed, etc). Both of these work, but seem to require a lot of chopping and recutting the data each time I want to view it in a different way. My gut feel is that rrdtool is the right way to go, but at the moment I'm struggling to get the data into it fast enough, partly because I have to convert my timestamp into Unix epoch. My understanding is also that if I decide I want a new granularity of aggregation I have to rebuild the rrd (which makes sense for realtime collection, but not retrospective loads like this). These things make me think maybe I'm using the wrong tool.

The collection of the data to flat file is fixed - for example I cannot pipe the collection directly into rrdtool.

My Question

I would like people's opinions on the best way to do charting. I have these requirements:

1. It should be as fast as possible to create a graph (not just render, but set up to render too)
2. It should be as flexible as possible - I need to muck around with the graphs to work out the best granularity for the data (5 seconds is probably too granular)
3. It should be able to aggregate (MAX/AVG/etc) where necessary
4. It should be repeatable and new data files as they come in
5. Ideally I want to be able to overlay DD1 vs DD2, or DD1 last week with DD1 this week
6. Unix or Windows, don't care. Prefer *nix though :-)

Any suggestions?

Answer 1

This is a really good question. I'm glad to see some R folks weighing in. I too think R is the right tool for the job, although it's my main hammer so everything looks a bit like a nail to me.

There are a handful of R concepts needed to tackle this challenge. As I see it, you need the following (references in parens) :

1. Import data into R. (R Import Export Guide)
2. Get the Data into an appropriate time series structure. (XTS Vignette PDF)
3. A little bit of plotting. (Quick-R intro to graphics)

Here's example code using 2mm points. If you notice, I don't illustrate plotting all 2mm points. It's slow and not that informative. But this should give you some ideas on getting started. Feel free to come back with more specific questions if you do decide to jump down the R rabbit hole!

require( xts ) require( lubridate )  ## set up some example data dataLength <- 2e6 startTime <- ymd_hms("2011-02-01-13-29-53") fistFullOfSeconds <- 1:dataLength date <- startTime + fistFullOfSeconds DD1 <- rnorm( dataLength ) DD2 <- DD1 + rnorm(dataLength, 0, .1 ) DD3 <- rnorm( dataLength, 10, 2) DD4 <- rnorm( dataLength )  myXts <- xts(matrix( c( DD1, DD2, DD3, DD4 ), ncol=4 ), date)  ## now all the data are in the myXts object so let's do some ## summarizing and visualization  ## grabbing just a single day from the data ## converted to data.frame to illustrate default data frame plotting oneDay <- data.frame( myXts["2011-02-02"] )  plot( oneDay ) 

The relationship between DD1 and DD2 kinda jumps out

boxplot( oneDay ) 

Boxplot is the piechart of statistical graphics. The plot you love to hate. Might as well link to this while we're here.

## look at the max value of each variable every minute par(mfrow=c(4,1)) ## partitions the graph window ep <- endpoints(myXts,'minutes') plot(period.apply(myXts[,1],INDEX=ep,FUN=max)) plot(period.apply(myXts[,2],INDEX=ep,FUN=max)) plot(period.apply(myXts[,3],INDEX=ep,FUN=max)) plot(period.apply(myXts[,4],INDEX=ep,FUN=max)) 

Even at one minute resolution I'm not sure this is informative. Should probably subset.