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"
Hello all, When I execute a SQL using SQLDF, how do I get the number of
records affected? I mean, if I run an UPDATE on a data frame, it doesn't
tell me if and how many records got updated. I've read through the
documentation and there don't seem to be a way to get this info unless it's
done on a
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
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 guide me if my assumption is wrong?
> library(sqldf);
Loading required package: gsubfn
Loading required package:
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
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 code
behaves differently on different versions of R and sqldf()
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
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 sqliteCloseConnection(conn, ...) :
RS-DBI driver warning: (closing pending res
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
I am trying to exclude integer values from a small data frame 1, d1 that
have matching hits in data frame 2, d2 (Very big) which involves matching
those hits first. I am trying to use sqldf on the df's in the following
fashion:
df1:
V1
12675
14753
16222
18765
df2: head(df2)
V1 V2
13
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
>
Hi all:
I have two data sets. Set A includes a long list of hits in a single
column, say:
m$V1
10
15
36
37
38
44
45
57
61
62
69 ...and so on
Set B includes just a few key ranges set up by way of a minimum in column X
and a maximum in column Y. Say,
n$X n$Y
30 38 # range from 30 to 38
52 6
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
Dear R-help readers,
i've created a database for quotes data (for 4 years; 2007 -- 2010)
with the sqldf package. This database contains a column "Date" in the
format mm/dd/.
The table in the database is called "main.data" and the database
itself "Honda". I tried to get the Data just f
Dear R-help readers,
i've created a database for quotes data (for 4 years; 2007 -- 2010)
with the sqldf package. This database contains a column "Date" in the
format mm/dd/.
The table in the database is called "main.data" and the database
itself "Honda". I tried to get the Data just
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
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 column. I am trying to learn and use sqldf package for
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:
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:
=== begin code =
library(sqldf)
A<-data
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
Hi,
I have following 2 tables:
Table 1:
POSTAL | VALUE
1000|49
1010|100
1020|50
Table 2:
INSEE | POSTAL
A|1000
B|1000
C|1010
D|1020
I would like to convert this to the following:
INSEE | VALUE_SPREAD
A|24.5
B|24.5
C|100
D|50
I can achieve this with a nested SQL query (through counting the
num
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
Dear all,
I have problems with iif function using sqldf library.
I counted abundance (Num) of different "SPECIES" in two moments (esf) saving
the information in two Tables (esf50, esf100):
esf50
SAMPLE SPECIES Num esf
1289diso1 44
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
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 a mistake in my usage?
# the line from the post above i
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
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 best method of doing this.
csv file is comma delimited with
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
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 data") but showing
NULL
Please help me in this regard.
Th
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
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 new table called DataNew with columns commodity, ratenew
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
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 when I
reference "var1", it should look for "X" in the dataframe.
Th
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
>
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
Loading required package: RSQLite
Loading required package:
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
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)
colnames(women)
head(women)
sqldf('select height, count(*) from women where he
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:
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: (error in statement: near "left": syntax error)
Thanks.
Step
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
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<-data.frame(A=c('a','b','c','a','b','c'),B=c(1,2,3,4,5,6))
#Create f
The same code runs with no problem on my
> Windows machine. Here is what happens:
>
>> r=sqldf("select ... ")
> Loading required package: tcltk
> Loading Tcl/Tk interface ...
>
> Then it never loads.
>
> I have X11 open.
>
> I have all the latest
happens:
> r=sqldf("select ... ")
Loading required package: tcltk
Loading Tcl/Tk interface ...
Then it never loads.
I have X11 open.
I have all the latest versions of all the necessary packages for sqldf 0.3-5:
DBI 0.2-5
RSQLite 0.9-1
RSQLite.extfuns 0.0.1
gsubfn 0.5-3
proto 0.3-8
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
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 "drop": syntax error
or
sqldf("alter table Korea rename column
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
Hello,
I'm having trouble discovering what's going wrong with my use of natural
joins via sqldf.
Following the instructions under 4i at http://code.google.com/p/sqldf/,
which discusses creating indices to speed joins, I have been only unreliably
able to get natural joins to work.
For example,
>
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 =
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 = ".",as.is = TRUE,na.strings = "NA",colClasses = NA,
nrows = 3864284,
skip = 0, check.names = TRUE,fill=TRUE
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
Hello,
I would like some help with sqldf syntax.
Suppose I have table 1 and table 2.
What do I have to do to generate a table with columns 2,5,6 from table 1
(for example), and columns 3,4,5,9 from table 2, but only when values in
column 2 from table 1 are equal to values in column 5
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
1 - 100 of 108 matches
Mail list logo
