David Reiner
2012-Sep-05 21:31 UTC
[R] using sqldf's read.csv.sql to read a file with "NA" for missing
I'm trying to use sqdf's function read.csv.sql to read CSV files in
which the missing values are represented by NA's.
Plain old read.csv works fine on these files, but they are rather large and
I'd like to filter using sql-like statements.
However, even if I specify field.types correctly and nrows=-1, it still turns
the columns with NA's into chars or 0.
I'm trying to make this OS independent, so I don't think I can use a
filter to convert the NA's to NULL's or whatever SQLite would
understand.
I can accept it everything has to be read in as char and then convert to doubles
with as.numeric, but I'm looking for speed.
Here is code I thought would read the file (I've attached a small sample.)
It almost works if there are no NA's in the initial rows, but it still turns
NA's into 0's instead of NA or something I can change into NA;
and it returns characters if there are NA's in the initial rows.
(0 is a possible value so I can't filter out the 0's.)
field.types <- list(V1='char', V2='char', V3='real',
V4='int', V5='real', V6='int', V7='real')
dtst <- read.csv.sql("./tmp.csv", header=FALSE,
field.types=field.types, nrows=-1)
str(dtst)
'data.frame': 32 obs. of 7 variables:
$ V1: chr "2012-07-01" "2012-07-01" "2012-07-01"
"2012-07-01" ...
$ V2: chr "15:50:00" "15:51:00" "15:52:00"
"15:53:00" ...
$ V3: chr "NA" "NA" "NA" "NA" ...
$ V4: int 0 0 0 0 0 0 0 0 0 0 ...
$ V5: chr "NA" "NA" "NA" "NA" ...
$ V6: int 0 0 0 0 0 0 0 0 0 0 ...
$ V7: chr "NA" "NA" "NA" "NA" ...
Any suggestions?
Thanks,
-- David L. Reiner
This e-mail and any materials attached hereto, including, without limitation,
all content hereof and thereof (collectively, "XR Content") are
confidential and proprietary to XR Trading, LLC ("XR") and/or its
affiliates, and are protected by intellectual property laws. Without the prior
written consent of XR, the XR Content may not (i) be disclosed to any third
party or (ii) be reproduced or otherwise used by anyone other than current
employees of XR or its affiliates, on behalf of XR or its affiliates.
THE XR CONTENT IS PROVIDED AS IS, WITHOUT REPRESENTATIONS OR WARRANTIES OF ANY
KIND. TO THE MAXIMUM EXTENT PERMISSIBLE UNDER APPLICABLE LAW, XR HEREBY
DISCLAIMS ANY AND ALL WARRANTIES, EXPRESS AND IMPLIED, RELATING TO THE XR
CONTENT, AND NEITHER XR NOR ANY OF ITS AFFILIATES SHALL IN ANY EVENT BE LIABLE
FOR ANY DAMAGES OF ANY NATURE WHATSOEVER, INCLUDING, BUT NOT LIMITED TO, DIRECT,
INDIRECT, CONSEQUENTIAL, SPECIAL AND PUNITIVE DAMAGES, LOSS OF PROFITS AND
TRADING LOSSES, RESULTING FROM ANY PERSON'S USE OR RELIANCE UPON, OR
INABILITY TO USE, ANY XR CONTENT, EVEN IF XR IS ADVISED OF THE POSSIBILITY OF
SUCH DAMAGES OR IF SUCH DAMAGES WERE FORESEEABLE.
David Reiner
2012-Sep-05 21:40 UTC
[R] using sqldf's read.csv.sql to read a file with "NA" for missing
I see the attachment didn't go through.
here is the text:
2012-07-01,15:50:00,NA,0,NA,0,NA
2012-07-01,15:51:00,NA,0,NA,0,NA
2012-07-01,15:52:00,NA,0,NA,0,NA
2012-07-01,15:53:00,NA,0,NA,0,NA
2012-07-01,15:54:00,NA,0,NA,0,NA
2012-07-01,15:55:00,NA,0,NA,0,NA
2012-07-01,15:56:00,NA,0,NA,0,NA
2012-07-01,15:57:00,NA,0,NA,0,NA
2012-07-01,15:58:00,NA,0,NA,0,NA
2012-07-01,15:59:00,NA,0,NA,0,NA
2012-07-01,16:00:00,1358.5,1,1358.5,4,NA
2012-07-01,16:01:00,NA,0,1358.25,2,NA
2012-07-01,16:02:00,NA,0,1358.25,2,NA
2012-07-01,16:03:00,NA,0,1358.25,2,NA
2012-07-01,16:04:00,NA,0,1358.25,2,NA
2012-07-01,16:05:00,NA,0,1358.25,2,NA
2012-07-01,16:06:00,1358.25,2,1358.25,2,NA
2012-07-01,16:07:00,1358.25,2,1358.25,2,NA
2012-07-01,16:08:00,1358,3,1358,2,NA
2012-07-01,16:09:00,1358.25,2,1358.25,2,NA
2012-07-01,16:10:00,1358.25,2,1358.25,2,NA
2012-07-01,16:11:00,1358.25,2,1358.25,2,NA
2012-07-01,16:12:00,1357.5,3,1357.5,3,NA
2012-07-01,16:13:00,1357.25,10,1357.25,3,1357.25
2012-07-01,16:14:00,NA,0,1357.25,10,1357.125
2012-07-01,16:15:00,1357,20,1357,18,1357
2012-07-01,16:16:00,1357,20,1357,18,1357
2012-07-01,16:17:00,1357,20,1357,18,1357
2012-07-01,16:18:00,1357,20,1357,18,1357
2012-07-01,16:19:00,1357,20,1357,18,1357
2012-07-01,16:20:00,1357,20,1357,18,1357
2012-07-01,16:21:00,1357,20,1357,18,1357
-- David
-----Original Message-----
From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org]
On Behalf Of David Reiner
Sent: Wednesday, September 05, 2012 4:32 PM
To: r-help at r-project.org
Subject: [R] using sqldf's read.csv.sql to read a file with "NA"
for missing
I'm trying to use sqdf's function read.csv.sql to read CSV files in
which the missing values are represented by NA's.
Plain old read.csv works fine on these files, but they are rather large and
I'd like to filter using sql-like statements.
However, even if I specify field.types correctly and nrows=-1, it still turns
the columns with NA's into chars or 0.
I'm trying to make this OS independent, so I don't think I can use a
filter to convert the NA's to NULL's or whatever SQLite would
understand.
I can accept it everything has to be read in as char and then convert to doubles
with as.numeric, but I'm looking for speed.
Here is code I thought would read the file (I've attached a small sample.)
It almost works if there are no NA's in the initial rows, but it still turns
NA's into 0's instead of NA or something I can change into NA;
and it returns characters if there are NA's in the initial rows.
(0 is a possible value so I can't filter out the 0's.)
field.types <- list(V1='char', V2='char', V3='real',
V4='int', V5='real', V6='int', V7='real')
dtst <- read.csv.sql("./tmp.csv", header=FALSE,
field.types=field.types, nrows=-1)
str(dtst)
'data.frame': 32 obs. of 7 variables:
$ V1: chr "2012-07-01" "2012-07-01" "2012-07-01"
"2012-07-01" ...
$ V2: chr "15:50:00" "15:51:00" "15:52:00"
"15:53:00" ...
$ V3: chr "NA" "NA" "NA" "NA" ...
$ V4: int 0 0 0 0 0 0 0 0 0 0 ...
$ V5: chr "NA" "NA" "NA" "NA" ...
$ V6: int 0 0 0 0 0 0 0 0 0 0 ...
$ V7: chr "NA" "NA" "NA" "NA" ...
Any suggestions?
Thanks,
-- David L. Reiner
This e-mail and any materials attached hereto, including, without limitation,
all content hereof and thereof (collectively, "XR Content") are
confidential and proprietary to XR Trading, LLC ("XR") and/or its
affiliates, and are protected by intellectual property laws. Without the prior
written consent of XR, the XR Content may not (i) be disclosed to any third
party or (ii) be reproduced or otherwise used by anyone other than current
employees of XR or its affiliates, on behalf of XR or its affiliates.
THE XR CONTENT IS PROVIDED AS IS, WITHOUT REPRESENTATIONS OR WARRANTIES OF ANY
KIND. TO THE MAXIMUM EXTENT PERMISSIBLE UNDER APPLICABLE LAW, XR HEREBY
DISCLAIMS ANY AND ALL WARRANTIES, EXPRESS AND IMPLIED, RELATING TO THE XR
CONTENT, AND NEITHER XR NOR ANY OF ITS AFFILIATES SHALL IN ANY EVENT BE LIABLE
FOR ANY DAMAGES OF ANY NATURE WHATSOEVER, INCLUDING, BUT NOT LIMITED TO, DIRECT,
INDIRECT, CONSEQUENTIAL, SPECIAL AND PUNITIVE DAMAGES, LOSS OF PROFITS AND
TRADING LOSSES, RESULTING FROM ANY PERSON'S USE OR RELIANCE UPON, OR
INABILITY TO USE, ANY XR CONTENT, EVEN IF XR IS ADVISED OF THE POSSIBILITY OF
SUCH DAMAGES OR IF SUCH DAMAGES WERE FORESEEABLE.
Gabor Grothendieck
2012-Sep-05 22:19 UTC
[R] using sqldf's read.csv.sql to read a file with "NA" for missing
On Wed, Sep 5, 2012 at 5:31 PM, David Reiner <David.Reiner at xrtrading.com> wrote:> I'm trying to use sqdf's function read.csv.sql to read CSV files in which the missing values are represented by NA's. > Plain old read.csv works fine on these files, but they are rather large and I'd like to filter using sql-like statements. > However, even if I specify field.types correctly and nrows=-1, it still turns the columns with NA's into chars or 0. > I'm trying to make this OS independent, so I don't think I can use a filter to convert the NA's to NULL's or whatever SQLite would understand. > I can accept it everything has to be read in as char and then convert to doubles with as.numeric, but I'm looking for speed. > > Here is code I thought would read the file (I've attached a small sample.) > It almost works if there are no NA's in the initial rows, but it still turns NA's into 0's instead of NA or something I can change into NA; > and it returns characters if there are NA's in the initial rows. > (0 is a possible value so I can't filter out the 0's.) > > field.types <- list(V1='char', V2='char', V3='real', V4='int', V5='real', V6='int', V7='real') > dtst <- read.csv.sql("./tmp.csv", header=FALSE, field.types=field.types, nrows=-1) > str(dtst) > > 'data.frame': 32 obs. of 7 variables: > $ V1: chr "2012-07-01" "2012-07-01" "2012-07-01" "2012-07-01" ... > $ V2: chr "15:50:00" "15:51:00" "15:52:00" "15:53:00" ... > $ V3: chr "NA" "NA" "NA" "NA" ... > $ V4: int 0 0 0 0 0 0 0 0 0 0 ... > $ V5: chr "NA" "NA" "NA" "NA" ... > $ V6: int 0 0 0 0 0 0 0 0 0 0 ... > $ V7: chr "NA" "NA" "NA" "NA" ... >See FAQ#14 on the sqldf home page noting the part at the end of the answer about csvfix. -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com