pamap2.R

library(MonetDB.R)

files <- dir("/Users/hannes/Desktop/PAMAP2_Dataset/Protocol/", pattern="*.dat", full.names=T)
tnames <- paste0("pamap2_", sub("^([^.]*).*", "\\1", basename(files)))
snames <- sub(".*(\\d{3})$", "\\1", tnames)

loadsql <- c("DROP VIEW pamap2", paste0("DROP TABLE ",tnames,"; "), paste0("CREATE TABLE ",tnames," (timestamp float, activityID int, heart_rate int, hand_temp float, hand_acc_16_1 float,  hand_acc_16_2 float, hand_acc_16_3 float, hand_acc_6_1 float, hand_acc_6_2 float, hand_acc_6_3 float, hand_gyro_1 float, hand_gyro_2 float, hand_gyro_3 float, hand_mag_1 float, hand_mag_2 float, hand_mag_3 float, hand_or_1 float, hand_or_2 float, hand_or_3 float, hand_or_4 float, chest_temp float, chest_acc_16_1 float,  chest_acc_16_2 float, chest_acc_16_3 float, chest_acc_6_1 float, chest_acc_6_2 float, chest_acc_6_3 float, chest_gyro_1 float, chest_gyro_2 float, chest_gyro_3 float, chest_mag_1 float, chest_mag_2 float, chest_mag_3 float, chest_or_1 float, chest_or_2 float, chest_or_3 float, chest_or_4 float, ankle_temp float, ankle_acc_16_1 float,  ankle_acc_16_2 float, ankle_acc_16_3 float, ankle_acc_6_1 float, ankle_acc_6_2 float, ankle_acc_6_3 float, ankle_gyro_1 float, ankle_gyro_2 float, ankle_gyro_3 float, ankle_mag_1 float, ankle_mag_2 float, ankle_mag_3 float, ankle_or_1 float, ankle_or_2 float, ankle_or_3 float, ankle_or_4 float)"), paste0("COPY INTO ",tnames," FROM '",files,"' USING DELIMITERS ' ','\\n','' NULL AS 'NaN'"), paste0("CREATE VIEW pamap2 AS ", paste0("SELECT '",snames,"' AS subject, ",tnames,".* FROM ", tnames, collapse=" UNION ALL "), ";"))

con <- dbConnect(dbDriver("MonetDB"), dbname="pamap2")
system.time({
	dbBegin(con)
	lapply(loadsql, function(s) dbSendQuery(con, s))
	dbCommit(con)
})