Skip to content

large dataset import, aggregation and reshape

2 messages · Christoph Lehmann, Renaud Lancelot

Christoph Lehmann
# 
Dear useRs

We have a data-set (comma delimited) with 12Millions of rows, and 5 
columns (in fact many more, but we need only 4 of them): id, factor 'a' 
(5 levels), factor 'b' (15 levels), date-stamp, numeric measurement. We 
run R on suse-linux 9.1 with 2GB RAM, (and a 3.5GB swap file).

on average we have 30 obs. per id. We want to aggregate (eg. sum of the 
measuresments under each factor-level of 'a' and the same for factor 
'b') and reshape the data so that for each id we have only one row in 
the final data.frame, means finally we have roughly 400000 lines.

I tried read.delim, used the nrows argument, defined colClasses (with an 
as.Date class) - memory problems at the latests when calling reshape and 
aggregate. Also importing the date column as character and then 
converting the dates column using 'as.Date' didn't succeed.

It seems the problematic, memory intesive parts are:
a) importing the huge data per se (but the data with dim c(12,5) << 2GB?)
b) converting the time-stamp to a 'Date' class
c) aggregate and reshape task

What are the steps you would recommend?

(i) using scan, instead of read.delim (with or without colClasses?)
(ii) importing blocks of data (eg 1Million lines once), aggregating 
them, importing the next block, so on?
(iii) putting the data into a MySQL database, importing from there and 
doing the reshape and aggregation in R for both factors separately

thanks for hints from your valuable experience
cheers
christoph
Renaud Lancelot
# 
Christoph Lehmann a ??crit :
I would try the latter and use and SQL interface such as RODBC or 
RMySQL. You can send your aggregation and reshape commands to the 
external database as an SQL query.

Example with a database I have at hand. The table "datemesu" has 640,000 
rows and 5 columns, the field "mesure" being a factor with 2 levels, "N" 
and "P".

 > library(RODBC)
 > fil <- "C:/Archives/Baobab/Baobab2000.mdb"
 > chann <- odbcConnectAccess(fil)
 > quer <- paste("SELECT numani, SUM(IIF(mesure = 'P', 1, 0)) AS wt,",
+                      "SUM(IIF(mesure = 'N', 1, 0)) AS bcs,",
+                      "MIN(date) AS minDate",
+               "FROM datemesu",
+               "GROUP BY numani")
 > system.time(tab <- sqlQuery(chann, quer), gcFirst = TRUE)
[1] 11.16  0.19 11.54    NA    NA
 > odbcCloseAll()
 >
 > dim(tab)
[1] 69360     4
 > head(tab)
        numani wt bcs    minDate
1 SNFLCA00001  1   0 1987-01-23
2 SNFLCA00002  2   0 1987-01-10
3 SNFLCA00004  1   0 1987-01-10
4 SNFLCA00006  4   0 1987-02-02
5 SNFLCA00007  4   0 1987-02-18
6 SNFLCA00008  3   0 1987-01-09


Best,

Renaud