empty cells that
consumes a great deal more memory.
-Original Message-
From: R-help On Behalf Of Ian Worthington via
R-help
Sent: Monday, June 27, 2022 5:06 PM
Cc: R-help
Subject: Re: [R] sqldf returns Error: database or disk is full
[External Email]
Well, iiuc the df is copied to the
V1 > 5 ")))
sqldf("select * from main.con")
sqldf()
-Original Message-
From: Gabor Grothendieck [mailto:ggrothendi...@gmail.com]
Sent: Thursday, June 11, 2020 10:30 AM
To: Ravi Jeyaraman
Cc: r-help@r-project.org
Subject: Re: [R] sqldf and number of records affected
esn't seem to work ...
>
> con <- data.frame(V1 = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))
> sqldf()
> sqldf(c("pragma count_changes = 1", "update con set V1 = 0 where V1 > 5 "))
> ans <- sqldf("select * from main.con")
> sqldf()
>
> -----Or
ldf()
sqldf(c("pragma count_changes = 1", "update con set V1 = 0 where V1 > 5 "))
ans <- sqldf("select * from main.con")
sqldf()
-Original Message-
From: Gabor Grothendieck [mailto:ggrothendi...@gmail.com]
Sent: Thursday, June 11, 2020 9:12 AM
To: Ravi Je
Here is an example. Ignore the warning or use the workaround discussed here
https://github.com/ggrothendieck/sqldf/issues/40
to avoid the warning.
library(sqldf)
sqldf() # use same connection until next sqldf()
sqldf(c("pragma count_changes = 1", "update BOD set demand = 99
where Time > 4"
sqldf does not use Tk so you can ignore this.
On Fri, Feb 19, 2016 at 12:32 PM, Divakar Reddy
wrote:
> Dear R users,
>
> I'm getting Waring message while trying to load "sqldf" package in R3.2.3
> and assuming that we can ignore this as it's WARNING Message and not an
> error message.
> Can you g
elp@r-project.org
Subject: Re: [R] sqldf() difference between R 3.1.2 and 3.0.1
On Wed, Feb 11, 2015 at 9:45 AM, Doran, Harold wrote:
> I have a function written and tested using R 3.0.1 and sqldf_0.4-7.1 that
> works perfectly. However, using this same code with R 3.1.2 and sqldf_0.4-10
> yi
On Wed, Feb 11, 2015 at 9:45 AM, Doran, Harold wrote:
> I have a function written and tested using R 3.0.1 and sqldf_0.4-7.1 that
> works perfectly. However, using this same code with R 3.1.2 and sqldf_0.4-10
> yields the error below that I am having a difficult time deciphering. Hence,
> same
One doesn't "call" a package... so your description is unclear. One doesn't
load (using the require or library functions) the relevant package AFTER
calling functions in the package. Since it appears you have left out some steps
or described them out of order, please supply a reproducible exampl
2014-07-14 7:52 GMT-04:00 Juan Daniel García :
> Hello:
> I'm trying to run this code
>
> data2 <- sqldf (" SELECT plot, age, avg(N) as N FROM data1 GROUP BY plot,
> t")
>
> The problem is that when calling sqldf with
>>require(sqldf)
>
> appears this message
>
> Warning message:
> In sqliteCloseC
i think this is what you want.. :)
http://stackoverflow.com/questions/4765936/using-joined-tables-to-exclude-certain-records
library(sqldf)
# use the mtcars example table
mtcars
# keep the first eight records in a second, separate data set
x <- mtcars[ 1:8 , ]
# keep all the records from mtca
On Mon, Jan 14, 2013 at 8:36 AM, Ravi Sreedhar wrote:
>
>
> Sent from my iPhone
>
> On Jan 13, 2013, at 9:47 PM, ravsre wrote:
>
>> I am trying to use the sqldf package to create independent data frames from a
>> master dataframe.
>> I want to use sqldf package and perform a simple select stateme
Sent from my iPhone
On Jan 13, 2013, at 9:47 PM, ravsre wrote:
> I am trying to use the sqldf package to create independent data frames from a
> master dataframe.
> I want to use sqldf package and perform a simple select statement. However,
> what I want to do is to create a loop and repeate
Is this what you want:
> m <- read.table(text = "10
+ 15
+ 36
+ 37
+ 38
+ 44
+ 45
+ 57
+ 61
+ 62
+ 69 ")
> n <- read.table(text = "30 38
+ 52 62 ")
>
> require(sqldf)
> sqldf("select m.V1
+ from m, n
+ where m.V1 between n.V1 and n.V2
+ ")
V1
1 36
2 37
3 38
4 57
5 61
6 62
>
Zitat von jim holtman :
Most likely your "Date" is either a character or a factor (you need to
provide an 'str' of the dataframe). You are therefore most likely
doing a character compare and that is the reason for your problem.
You need to convert to a character string of the format -MM-DD
Most likely your "Date" is either a character or a factor (you need to
provide an 'str' of the dataframe). You are therefore most likely
doing a character compare and that is the reason for your problem.
You need to convert to a character string of the format -MM-DD to
do the correct character
Thank you Jim for your reply.
I could figure out that readLines works fine until 35,841,335 lines
(records).
When the next line is read to be read, a window with "R for Windows GUI
front-end has stopped working" message comes, with an option to close
program or checking online for a solution.
T
Hi,
On Fri, Feb 3, 2012 at 1:12 PM, HC wrote:
> Thank you.
>
> The readLines command is working fine and I am able to read 10^6 lines in
> one go and write them using the write.table command.
>
> Does this readLines command using a block concept to optimize or goes line
> by line?
>
> Steve has m
Exactly what does "crashed" mean? What was the error message? How
you tried to put:
rm(Lines)
gc()
at the end of the loop to free up and compact memory? If you watch
the performance, does the R process seem to be growing in terms of the
amount of memory that is being used? You can add:
memor
Bad news!
The readLines command works fine upto a certain limit. Once a few files have
been written the R program crashes.
I used the following code:
*
iFile<-"Test.txt"
con <- file(iFile, "r")
N<-125;
iLoop<-1
while(length(Lines <- readLines(con, n = N)) > 0 & iLo
Thank you.
The readLines command is working fine and I am able to read 10^6 lines in
one go and write them using the write.table command.
Does this readLines command using a block concept to optimize or goes line
by line?
Steve has mentioned about *nix and split commands. Would there be any spee
On Fri, Feb 3, 2012 at 8:08 AM, HC wrote:
> This is a 160 GB tab-separated .txt file. It has 9 columns and 3.25x10^9
> rows.
>
> Can R handle it?
>
You can process a file N lines at time like this:
con <- file("myfile.dat", "r")
while(length(Lines <- readLines(con, n = N)) > 0) {
... whatever.
This is a 160 GB tab-separated .txt file. It has 9 columns and 3.25x10^9
rows.
Can R handle it?
Thank you.
HC
--
View this message in context:
http://r.789695.n4.nabble.com/sqldf-for-Very-Large-Tab-Delimited-Files-tp4350555p4354556.html
Sent from the R help mailing list archive at Nabble.co
On Fri, Feb 3, 2012 at 7:37 AM, Gabor Grothendieck
wrote:
> On Fri, Feb 3, 2012 at 6:03 AM, HC wrote:
>> Thank you for indicating that SQLite may not handle a file as big as 160 GB.
>>
>> Would you know of any utility for *physically splitting *the 160 GB text
>> file into pieces. And if one can
On Fri, Feb 3, 2012 at 6:03 AM, HC wrote:
> Thank you for indicating that SQLite may not handle a file as big as 160 GB.
>
> Would you know of any utility for *physically splitting *the 160 GB text
> file into pieces. And if one can control the splitting at the end of a
> record.
>
If they are c
Thank you for indicating that SQLite may not handle a file as big as 160 GB.
Would you know of any utility for *physically splitting *the 160 GB text
file into pieces. And if one can control the splitting at the end of a
record.
Thank you again.
HC
--
View this message in context:
http://r.789
On Thu, Feb 2, 2012 at 8:07 PM, HC wrote:
> Hi Gabor,
>
> Thank you very much for your guidance and help.
>
> I could run the following code successfully on a 500 mb test data file. A
> snapshot of the data file is attached herewith.
>
> code start***
> library(sqldf)
> library
Hi Gabor,
Thank you very much for your guidance and help.
I could run the following code successfully on a 500 mb test data file. A
snapshot of the data file is attached herewith.
code start***
library(sqldf)
library(RSQLite)
iFile<-"Test100.txt"
con <- dbConnect(SQLite(),db
On Thu, Feb 2, 2012 at 3:11 AM, Gabor Grothendieck
wrote:
> On Wed, Feb 1, 2012 at 11:57 PM, HC wrote:
>> Hi All,
>>
>> I have a very (very) large tab-delimited text file without headers. There
>> are only 8 columns and millions of rows. I want to make numerous pieces of
>> this file by sub-setti
On Wed, Feb 1, 2012 at 11:57 PM, HC wrote:
> Hi All,
>
> I have a very (very) large tab-delimited text file without headers. There
> are only 8 columns and millions of rows. I want to make numerous pieces of
> this file by sub-setting it for individual stations. Station is given as in
> the first
On Mon, Jan 23, 2012 at 12:46 AM, Gabor Grothendieck
wrote:
> On Sun, Jan 22, 2012 at 10:59 PM, Grant Farnsworth wrote:
>> I've been using sqldf heavily lately but have encountered problems
>> with ordering of observations or calculating statistics such as max()
>> and min() when the variable use
On Sun, Jan 22, 2012 at 10:59 PM, Grant Farnsworth wrote:
> I've been using sqldf heavily lately but have encountered problems
> with ordering of observations or calculating statistics such as max()
> and min() when the variable used is of class Date.
>
> For example, if I run the following code:
try this:
> library(sqldf)
> table1 <- read.csv(text = "POSTAL | VALUE
+ 1000|49
+ 1010|100
+ 1020|50", sep="|")
> table2 <- read.csv(text = "INSEE | POSTAL
+ A|1000
+ B|1000
+ C|1010
+ D|1020", sep="|")
> table3 <- sqldf("
+ select table2.INSEE
+ , 1.0 * table1.VALUE / counts.nPos
sqldf uses the SQLite database by default for backend processing. The "iif"
function is specific to the Jet database engine syntax (which underlies MS
Access). You could read up on SQLite syntax, or you could avoid using
nonstandard SQL syntax, retrieve the data into a data frame, and use R code
On Sep 29, 2011, at 10:06 AM, Juliet Hannah wrote:
I am using the example in this post:
https://stat.ethz.ch/pipermail/r-help/2010-October/257204.html
# create a file
write.table(iris,"iris.csv",row.names=FALSE,sep=",",quote=FALSE)
# this does not work
# has the syntax changed or is there
Thanks a lot Gabor. It helped a lot. Appreciate your time and effort.
Thanks
--- On Thu, 7/14/11, Gabor Grothendieck wrote:
> From: Gabor Grothendieck
> Subject: Re: [R] SQldf with sqlite and H2
> To: "Mandans"
> Cc: r-help@r-project.org
> Date: Thursday, July 14, 20
On Thu, Jul 14, 2011 at 10:33 AM, Mandans wrote:
> SQldf with sqlite and H2
>
> I have a large csv file (about 2GB) and wanted to import the file into R and
> do some filtering and analysis. Came across sqldf ( a great idea and product)
> and was trying to play around to see what would be the be
On Wed, Apr 20, 2011 at 12:39 PM, new2R wrote:
> Hi,
>
> I am new to R and trying to migrate from SAS. I am trying to copy data from
> one table to another table which have same columns using sqldf. but not
> working and showing "NULL"
>
> I wrote statement as sqldf("INSERT INTO new select * from
Thank you very much. Its working.
--
View this message in context:
http://r.789695.n4.nabble.com/SQLDF-syntax-tp3458919p3460448.html
Sent from the R help mailing list archive at Nabble.com.
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mail
On Mon, Apr 18, 2011 at 6:34 PM, new2R wrote:
> Hi,
>
> I am new to R and trying to migrate from SAS. I am trying to use sqldf to
> create a new table from existed table and change some of the columns. I have
> table called DataOld with columns commodity, rate and total and I am trying
> to create
You're submitting queries for SQLDF to execute as strings. So, if you want
to use a variable column name, sprintf() or paste() your statement together,
like:
sqldf(sprintf('select sum(%s) as XSUM, Y as Y from testdf group by Y',
var1))
--
Robert Tirrell | r...@stanford.edu | (607) 437-6532
Progra
On Wed, Mar 9, 2011 at 11:41 AM, Mike Schumacher
wrote:
> Fellow R programmers,
>
> I'd like to submit SQLDF statements with R objects as column names.
>
> For example, I want to assign "X" to "var1" (var1<-"X") and then refer to
> "var1" in the SQLDF statement. SQLDF needs to understand that wh
Marc: Installing Simon's package worked perfectly. Thanks so much!
--
View this message in context:
http://r.789695.n4.nabble.com/sqldf-hanging-on-macintosh-works-on-windows-tp3022193p3023736.html
Sent from the R help mailing list archive at Nabble.com.
On Mon, Nov 1, 2010 at 12:10 PM, Marc Schwartz wrote:
> On Nov 1, 2010, at 10:55 AM, Gabor Grothendieck wrote:
>
>> On Mon, Nov 1, 2010 at 10:55 AM, GL wrote:
>>>
>>>
library(sqldf)
>>> Loading required package: DBI
>>> Loading required package: RSQLite
>>> Loading required package: RSQ
On Nov 1, 2010, at 10:55 AM, Gabor Grothendieck wrote:
> On Mon, Nov 1, 2010 at 10:55 AM, GL wrote:
>>
>>
>>>
>>> library(sqldf)
>> Loading required package: DBI
>> Loading required package: RSQLite
>> Loading required package: RSQLite.extfuns
>> Loading required package: gsubfn
>> Loading req
On Mon, Nov 1, 2010 at 10:55 AM, GL wrote:
>
>
>>
>> library(sqldf)
> Loading required package: DBI
> Loading required package: RSQLite
> Loading required package: RSQLite.extfuns
> Loading required package: gsubfn
> Loading required package: proto
> Loading required package: chron
>> debug(sqldf)
>
> library(sqldf)
Loading required package: DBI
Loading required package: RSQLite
Loading required package: RSQLite.extfuns
Loading required package: gsubfn
Loading required package: proto
Loading required package: chron
> debug(sqldf)
> df.final <- sqldf('select Date, Hour, x as RoomsInUse
On Mon, Nov 1, 2010 at 10:32 AM, GL wrote:
>
> added library(RH2)
>
> Still get message:
>
> Loading required package: tcltk
> Loading Tcl/Tk interface
> +
>
> directly after sqldf statement
>
>> df.final <- sqldf('select Date, Hour, x as RoomsInUse from
>> "df.possible.combos"
> + left
added library(RH2)
Still get message:
Loading required package: tcltk
Loading Tcl/Tk interface
+
directly after sqldf statement
> df.final <- sqldf('select Date, Hour, x as RoomsInUse from
> "df.possible.combos"
+ left join "df.aggregate" using (Hour, Date)')
There is no progress s
On Mon, Nov 1, 2010 at 9:59 AM, GL wrote:
>
> Have a long script that runs fine on windows (32 bit). When I try to run in
> on two different macs (64 bit), however, it hangs with identical behavior.
>
> I start with:
> library(sqldf)
>
> This results in messages:
> Loading required package: DBI
>
On Mon, Nov 1, 2010 at 9:28 AM, Alex Bryant wrote:
> Hello Group,
>
> I am having trouble with the sqldf package on unix. The same
> code works fine on windows.
>
> Silly Example script:
>
> # Load the package
> library(sqldf)
>
> # Use the titanic data set
>
> data(women)
> colna
other suggestion about data.table and report.
Cheers everybody.
Stephen B
-Original Message-
From: Gabor Grothendieck [mailto:ggrothendi...@gmail.com]
Sent: Thursday, August 26, 2010 4:26 PM
To: Bond, Stephen
Cc: r-help@r-project.org
Subject: Re: [R] sqldf syntax
On Thu, Aug 26, 2010 at 2:
On Thu, Aug 26, 2010 at 2:31 PM, Bond, Stephen wrote:
> Please correct the following
>
>> sqldf("update esc left join forwagg on esc.ym=forwagg.Date set
>> esc.ri2=forwagg.N1 where esc.age=12","select * from main.esc")
> Error in sqliteExecStatement(con, statement, bind.data) :
> RS-DBI driver:
On Wed, Aug 4, 2010 at 12:29 AM, Suphajak Ngamlak
wrote:
> Dear all,
>
> I would like to do sample statistics, e.g. mean, median from very large
> dataset. This is part of commands I use routinely with several dataset
> so I would like to make it into function. The simplified examples are
>
> Test
On Wed, Jul 28, 2010 at 1:21 AM, wrote:
>
>
>
> This is my first post. I am running Mac OS X version 10.6.3. I am running R
> 2.11.0 GUI 1.33 64 bit.
>
> This may or may not be related to sqldf, but I experienced this problem while
> attempting to use an sqldf query. The same code runs with no
On Fri, Jul 16, 2010 at 2:46 PM, PeterTucker wrote:
>
> Hi - I am something of a newbie and am a little perplexed. When (trying to)
> modify a table I issue the following commands with subsequent errors
>
> sqldf("alter table Korea drop column code", dbname = "mydb")
> error in statement: near "d
They work on any join that is able to make use of them. If you
preface the select statement with explain query plan then it will give
you some info, e.g.
> sqldf('explain query plan select * from main.A natural join main.B')
order from detail
1 00 TABLE
Thank you very much for these clarifying responses, Gabor.
I had mistakenly assumed that creating the index on Tid restricted the
natural join to joining on Tid. Can you describe when and how indices speed
up joins, or can you point me to resources that address this? Is it only for
natural joins o
Although that works I had meant to write:
> names(B)[2] <- "dfNameB"
> # ... other commands
> sqldf('select * from main.A natural join main.B')
so that now only Tid is in common so the natural join just picks it up
and also the heuristic works again since we no longer retrieve
duplicate column na
There are two problems:
1. A natural join will join all columns with the same names in the two
tables and that includes not only Tid but also dfName and since there
are no rows that have the same Tid and dfName the result has zero
rows.
2. the heuristic it uses fails when you retrieve the same co
If I assign the file input to y_data and change you sqldf to
> z <- sqldf("select * from x_data left join y_data using
(item_code)"); z
I can replicate your result. Even after changing the types of the two
item_code fields to match I still get the same result and when I see
to what degre
Sorry! It is the end of the day for me.
So
dput(x)
structure(list(prochi = c("CAO713", "CAO713", "CAO713",
"CAO713", "CAO713", "CAO713", "CAO713", "CAO713",
"CAO713", "CAO713"), prescribed_date = c("22/06/2001",
"28/04/2000", "10/04/2000", "03/07/2000", "
Yes, its not clear what data was used. Please provide a single email
that contains code that can be copied from R and pasted into another
session that will reproduce the problem.
x_data <- ...dput output goes here...
y_data <- dput output goes here ...
library(sqldf)
... rest of code ...
or else
Sorry! It is the end of the day for me.
So
dput(x)
structure(list(prochi = c("CAO713", "CAO713", "CAO713",
"CAO713", "CAO713", "CAO713", "CAO713", "CAO713",
"CAO713", "CAO713"), prescribed_date = c("22/06/2001",
"28/04/2000", "10/04/2000", "03/07/200
You have now given two different assignments to x_data and none to
y_data:
The str( from the file access offering:
> str(x_data)
'data.frame': 2848 obs. of 5 variables:
$ item_code : int 100 110 150 160 161 164 200 210 212 220 ...
$ name: chr "NEONACLEX K" "NEONACLEX"
The y_data file has over 9000 rows in it so I thought it would be more
practical to give you the file to download
--
View this message in context:
http://n4.nabble.com/sqldf-not-joining-all-the-fields-tp1590786p1590833.html
Sent from the R help mailing list archive at Nabble.com.
__
What about y_data?
On Fri, Mar 12, 2010 at 12:14 PM, Newbie19_02 wrote:
>
> dput(x_data)
>
> structure(list(prochi = c("CAO713", "CAO713", "CAO713",
> "CAO713", "CAO713", "CAO713", "CAO713", "CAO713",
> "CAO713", "CAO713"), prescribed_date = c("22/06/2001"
Please provide code that I can just copy from your post and paste into
my session. Either provide dput output as requested or provide the
files on the internet together with code that reads them off the
internet.
On Fri, Mar 12, 2010 at 12:06 PM, Newbie19_02 wrote:
>
> http://n4.nabble.com/file
y_data <- read.table("feb_267_presc_items_tsf.txt", header = TRUE, sep = "|",
quote = "\"'",
dec = ".",as.is = TRUE,na.strings = "NA",colClasses = NA,
nrows = 3864284,
skip = 0, check.names = TRUE,fill=TRUE,
strip.white = TRUE, blank.lines.skip = TRU
dput(x_data)
structure(list(prochi = c("CAO713", "CAO713", "CAO713",
"CAO713", "CAO713", "CAO713", "CAO713", "CAO713",
"CAO713", "CAO713"), prescribed_date = c("22/06/2001",
"28/04/2000", "10/04/2000", "03/07/2000", "09/01/2001", "16/10/2001",
"16/08/20
http://n4.nabble.com/file/n1590804/feb09_267_presc_items_tsf.txt
feb09_267_presc_items_tsf.txt
is the total file for y so if I use the command line with the total data for
y then I get the output specified in z
Thanks,
Natalie
--
View this message in context:
http://n4.nabble.com/sqldf-not-jo
Can you show the output of dput(x_data) and dput(y_data).
On Fri, Mar 12, 2010 at 11:56 AM, Newbie19_02 wrote:
>
> Dear R users,
>
> I have two data frames that were read from text files as follows:
>
> x_data <- read.table("x.txt", header = TRUE, sep = "|", quote = "\"'",
> dec =
Google for sql join and see the examples in Example 4 on the sqldf home page:
http://code.google.com/p/sqldf/#Example_4._Join
On Thu, Nov 19, 2009 at 2:30 PM, JoK LoQ wrote:
>
> Hello,
>
> I would like some help with sqldf syntax.
>
> Suppose I have table 1 and table 2.
> What do I have to
sqldf has some facilities for Date class but has no knowledge of date class.
See example 7b on home page: http://sqldf.googlecode.com
and here is a second example:
> DF <- data.frame(D = as.Date(c("21DEC2006","15DEC2006"), "%d%b%Y"))
> DF
D
1 2006-12-21
2 2006-12-15
> sqldf("select * f
nk you again for your help.
> Peter.
>
>
> -Original Message-
> From: Gabor Grothendieck [mailto:[EMAIL PROTECTED]
> Sent: 3. april 2008 17:08
> To: Peter Jepsen
> Subject: Re: [R] sqldf file specification, non-ASCII
>
> One other thing you could try would be to run it
t: Re: [R] sqldf file specification, non-ASCII
One other thing you could try would be to run it through
ffe (fast file extractor) which is a free utility that you can
find via google. Use the ffe's loose argument. It can find
bad lines and since its not dependent on R would give
you and indepen
On 4/3/2008 10:22 AM, Peter Jepsen wrote:
> Dear R-Listers,
>
> I am a Windows user (R 2.6.2) using the development version of sqldf to
> try to read a 3GB file originally stored in .sas7bdat-format. I convert
> it to comma-delimited ASCII format with StatTransfer before trying to
> import just th
I cleaned up the environment and restarted everything
and it does work. But I have no idea what it is what
is different now. Anyway, it works!
Thanks a lot Gabor!
Best,
Werner
> I can't reproduce your error:
>
> > library(sqldf)
> > sqldf("select * from warpbreaks limit 6")
> breaks woo
I can't reproduce your error:
> library(sqldf)
> sqldf("select * from warpbreaks limit 6")
breaks wool tension
1 26A L
2 30A L
3 54A L
4 25A L
5 70A L
6 52A L
> R.version.string # Vista
[1] "R version 2.6.2 a
79 matches
Mail list logo
