Re: [R] sqldf and number of records affected
You're correct. It does work. I was looking at some other result printed. My
bad.
Looks like we can also get the same result using 'SELECT changes()'.
Approach 1:
con <- data.frame(V1 = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))
sqldf()
suppressWarnings(sqldf(c(" update con set V1 = 0 where V1 > 5 ", "select
changes()")))
sqldf("select * from main.con")
sqldf()
Approach 2:
con <- data.frame(V1 = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))
sqldf()
suppressWarnings(sqldf(c("pragma count_changes = 1", "update con set V1 = 0
where 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
There is no real difference between your example and the example I provided.
Both use a data.frame in R and both work for me under R 3.5 with RSQLite 2.2.0
See log below.
Note that there is a bug in R 4.0 related to tcltk that could possibly affect
sqldf as it uses tcltk. A fix has been announced for R 4.0.2.
> library(sqldf)
Loading required package: gsubfn
Loading required package: proto
Loading required package: RSQLite
> con <- data.frame(V1 = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))
> sqldf()
Path: :memory:
Extensions: TRUE
> sqldf(c("pragma count_changes = 1", "update con set V1 = 0 where V1 >
> 5 "))
rows updated
15
Warning message:
In result_fetch(res@ptr, n = n) :
SQL statements must be issued with dbExecute() or dbSendStatement() instead
of dbGetQuery() or dbSendQuery().
> ans <- sqldf("select * from main.con")
> sqldf()
NULL
> ans
V1
1 1
2 2
3 3
4 4
5 5
6 0
7 0
8 0
9 0
10 0
> R.version.string
[1] "R version 3.5.3 (2019-03-11)"
> packageVersion("sqldf")
[1] ‘0.4.11’
> packageVersion("RSQLite")
[1] ‘2.2.0’
> packageVersion("DBI")
[1] ‘1.1.0’
On Thu, Jun 11, 2020 at 10:06 AM Ravi Jeyaraman wrote:
>
> Thanks for the response Gabor. Looks like the below example will work when
> using SQLite, but in my case I'm just creating a dataframe in R and trying to
> update it using sqldf as below and it doesn'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()
>
> -Original Message-
> From: Gabor Grothendieck [mailto:ggrothendi...@gmail.com]
> Sent: Thursday, June 11, 2020 9:12 AM
> To: Ravi Jeyaraman
> Cc: r-help@r-project.org
> Subject: Re: [R] sqldf and number of records affected
>
> 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("select * from main.BOD")
> sqldf()
>
>
> On Thu, Jun 11, 2020 at 9:01 AM Ravi Jeyaraman wrote:
> >
> > 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 database. Any ideas?
> >
> > Thanks
> > Ravi
> >
> >
> > --
> > This email has been checked for viruses by AVG.
> > https://www.avg.com
> >
> > __
> > R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
> > https://stat.ethz.ch/mailman/listinfo/r-help
> > PLEASE do read the posting guide
> > http://www.R-project.org/posting-guide.html
> > and provide commented, minimal, self-contained, reproducible code.
>
>
>
> --
> Statistics & Software Consulting
> GKX Group, GKX Associates Inc.
> tel: 1-877-GKX-GROUP
> email: ggrothendieck at gmail.com
>
>
> --
> This email has been checked for viruses by AVG.
> https://www.avg.com
>
--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Re: [R] sqldf and number of records affected
Thanks for the response Gabor. Looks like the below example will work when
using SQLite, but in my case I'm just creating a dataframe in R and trying to
update it using sqldf as below and it doesn'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()
-Original Message-
From: Gabor Grothendieck [mailto:ggrothendi...@gmail.com]
Sent: Thursday, June 11, 2020 9:12 AM
To: Ravi Jeyaraman
Cc: r-help@r-project.org
Subject: Re: [R] sqldf and number of records affected
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("select * from main.BOD")
sqldf()
On Thu, Jun 11, 2020 at 9:01 AM Ravi Jeyaraman wrote:
>
> 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 database. Any ideas?
>
> Thanks
> Ravi
>
>
> --
> This email has been checked for viruses by AVG.
> https://www.avg.com
>
> __
> R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
--
This email has been checked for viruses by AVG.
https://www.avg.com
__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
[R] sqldf and number of records affected
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 database. Any ideas? Thanks Ravi -- This email has been checked for viruses by AVG. https://www.avg.com __ R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] how to add a calculated column into a data frame
How about something like this?
df <- data.frame(ID=1:3, DTVAL=c("2009-03-21","2010-05-11","2020-05-05"))
df <- df %>% mutate(YEAR = as.numeric(format(as.Date(DTVAL,'%Y-%m-%d'),
'%Y')))
-Original Message-
From: R-help [mailto:r-help-boun...@r-project.org] On Behalf Of Charles Thuo
Sent: Friday, June 05, 2020 12:18 AM
To: r-help@r-project.org
Subject: [R] how to add a calculated column into a data frame
Dear Sirs,
I have a data frame that has a column that shows the transaction date.
How do i add another column that extracts the year of transaction from the
transaction date.
Charles
[[alternative HTML version deleted]]
__
R-help@r-project.org mailing list -- To
UNSUBSCRIBE and more, see https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
--
This email has been checked for viruses by AVG.
https://www.avg.com
[[alternative HTML version deleted]]
__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
[R] Cumulative split of value in data frame column
Assuming, I have a data frame like this ..
df <- data.frame(ID=1:3, FOO=c('A_B','A_B_C','A_B_C_D_E'))
I want to do a 'cumulative split' of the values in column FOO based on the
delimiter '_'. The end result should be like this ..
ID FOO FOO_SPLIT1 FOO_SPLIT2 FOO_SPLIT3
FOO_SPLIT4 FOO_SPLIT5
1 A_B AA_B
2 A_B_C A A_B
A_B_C
3 A_B_C_D_E AA_BA_B_C
A_B_C_D A_B_C_D_E
Any efficient, optimized way to do this?
--
This email has been checked for viruses by AVG.
https://www.avg.com
[[alternative HTML version deleted]]
__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Re: [R] read_excel() ignore case of worksheet name?
Ista, With few tweaks this worked beautifully. Thank you so much.
-Original Message-
From: Ista Zahn [mailto:istaz...@gmail.com]
Sent: Tuesday, May 26, 2020 11:38 PM
To: Ravi Jeyaraman
Cc: Erin Hodgess ; r-help@r-project.org
Subject: Re: [R] read_excel() ignore case of worksheet name?
How about
read_excel_table <- function(x) {
readxl::read_excel(
x,
sheet=grep("tables",
excel_sheets(x),
ignore.case = TRUE,
value = TRUE),
.name_repair = fixColNames
)
}
lapply(SIS$FULL_FILEPATH, read_excel_table)
--Ista
On Tue, May 26, 2020 at 11:05 PM Ravi Jeyaraman wrote:
>
> I’ve already tried that and doesn’t work
>
>
>
> From: Erin Hodgess [mailto:erinm.hodg...@gmail.com]
> Sent: Tuesday, May 26, 2020 10:55 PM
> To: Ravi Jeyaraman
> Cc: r-help@r-project.org
> Subject: Re: [R] read_excel() ignore case of worksheet name?
>
>
>
> Here’s a thought, please. Could you use the tolower function and make them
> all lower case?
>
>
>
> Thanks,
>
> Erin
>
>
>
> On Tue, May 26, 2020 at 8:21 PM Ravi Jeyaraman <mailto:rav...@gmail.com> > wrote:
>
> Hello All, Is there any parameter to make read_excel() ignore the
> case-sensitiveness of the worksheet? I'm using the below to ready in
> multiple spreadsheets and it works perfectly fine if the worksheet is named
> 'Tables', but fails when it's named ' TABLES'. Any thoughts?
>
> lapply(1:nrow(SIS), function(x)
> readxl::read_excel(SIS$FULL_FILEPATH[x], sheet='Tables', .name_repair
> = fixColNames))
>
> Thanks in advance for your response.
>
> Cheers
> Ravi
>
>
>
> --
> This email has been checked for viruses by AVG.
> https://www.avg.com
>
> __
> R-help@r-project.org <mailto:R-help@r-project.org> mailing list -- To
> UNSUBSCRIBE and more, see https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
> --
>
> Erin Hodgess, PhD
>
> mailto: erinm.hodg...@gmail.com <mailto:erinm.hodg...@gmail.com>
>
>
> [[alternative HTML version deleted]]
>
> __
> R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
--
This email has been checked for viruses by AVG.
https://www.avg.com
__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Re: [R] read_excel() ignore case of worksheet name?
I’ve already tried that and doesn’t work From: Erin Hodgess [mailto:erinm.hodg...@gmail.com] Sent: Tuesday, May 26, 2020 10:55 PM To: Ravi Jeyaraman Cc: r-help@r-project.org Subject: Re: [R] read_excel() ignore case of worksheet name? Here’s a thought, please. Could you use the tolower function and make them all lower case? Thanks, Erin On Tue, May 26, 2020 at 8:21 PM Ravi Jeyaraman mailto:rav...@gmail.com> > wrote: Hello All, Is there any parameter to make read_excel() ignore the case-sensitiveness of the worksheet? I'm using the below to ready in multiple spreadsheets and it works perfectly fine if the worksheet is named 'Tables', but fails when it's named ' TABLES'. Any thoughts? lapply(1:nrow(SIS), function(x) readxl::read_excel(SIS$FULL_FILEPATH[x], sheet='Tables', .name_repair = fixColNames)) Thanks in advance for your response. Cheers Ravi -- This email has been checked for viruses by AVG. https://www.avg.com __ R-help@r-project.org <mailto:R-help@r-project.org> mailing list -- To UNSUBSCRIBE and more, see https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code. -- Erin Hodgess, PhD mailto: erinm.hodg...@gmail.com <mailto:erinm.hodg...@gmail.com> [[alternative HTML version deleted]] __ R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
[R] read_excel() ignore case of worksheet name?
Hello All, Is there any parameter to make read_excel() ignore the case-sensitiveness of the worksheet? I'm using the below to ready in multiple spreadsheets and it works perfectly fine if the worksheet is named 'Tables', but fails when it's named ' TABLES'. Any thoughts? lapply(1:nrow(SIS), function(x) readxl::read_excel(SIS$FULL_FILEPATH[x], sheet='Tables', .name_repair = fixColNames)) Thanks in advance for your response. Cheers Ravi -- This email has been checked for viruses by AVG. https://www.avg.com __ R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
[R] Help with Parallel Processing
Dear Friends,
I'm trying to run a bunch of tasks in parallel using 'Future' package and
for some reason, it's not able to find the data frames that I want it to
find. I've created the below sample program to show what I'm doing. Should
I be exporting the Global data to each child process? I am not doing that
currently because I read somewhere that it's automatically done when using
the multisession plan. Any idea what I'm doing wrong?
Thanks
Ravi
if(!require('sqldf')) install.packages('sqldf')
if(!require('future')) install.packages('future')
if(!require('doFuture')) install.packages('doFuture')
if(!require('future.apply')) install.packages('future.apply')
library('sqldf')
library('future')
library("doFuture")
library("future.apply")
registerDoFuture()
plan(multisession, globals = TRUE, workers=5)
options(future.globals.maxSize=+Inf)
DATA_ASIA <- data.frame(c('NAME1', 'NAME2'))
DATA_EUROPE <- data.frame(c('NAME1', 'NAME2', 'NAME3'))
DATA_USA <- data.frame(c('NAME1', 'NAME2', 'NAME3', 'NAME4'))
DATA_AFRICA <- data.frame(c('NAME1'))
LEVEL <- c('ASIA_LEVEL', 'EUROPE_LEVEL', 'USA_LEVEL', 'AFRICA_LEVEL')
R_PROG <- c('SELECT COUNT(*) as COUNT FROM DATA_ASIA',
'SELECT COUNT(*) as COUNT FROM DATA_EUROPE',
'SELECT COUNT(*) as COUNT FROM DATA_USA',
'SELECT COUNT(*) as COUNT FROM DATA_AFRICA')
RULES_ALL <- data.frame(LEVEL, R_PROG)
RULES_ASIA <- subset(RULES_ALL, LEVEL == 'ASIA_LEVEL')
RESULT_ASIA <- future(data.table::rbindlist(lapply(1:nrow(RULES_ASIA),
function(x) sqldf(RULES_ASIA$R_PROG[x])), use.names = TRUE, fill=TRUE))
RULES_EUROPE <- subset(RULES_ALL, LEVEL == 'EUROPE_LEVEL')
RESULT_EUROPE <- future(data.table::rbindlist(lapply(1:nrow(RULES_EUROPE),
function(x) sqldf(RULES_EUROPE$R_PROG[x])), use.names = TRUE, fill=TRUE))
RULES_USA <- subset(RULES_ALL, LEVEL == 'USA_LEVEL')
RESULT_USA <- future(data.table::rbindlist(lapply(1:nrow(RULES_USA),
function(x) sqldf(RULES_USA$R_PROG[x])), use.names = TRUE, fill=TRUE))
RULES_AFRICA <- subset(RULES_ALL, LEVEL == 'AFRICA_LEVEL')
RESULTS_AFRICA <- future(data.table::rbindlist(lapply(1:nrow(RULES_AFRICA),
function(x) sqldf(RULES_AFRICA$R_PROG[x])), use.names = TRUE, fill=TRUE))
RESULT_ASIA <- value(RESULT_ASIA)
RESULT_EUROPE <- value(RESULT_EUROPE)
RESULT_USA <- value(RESULT_USA)
RESULTS_AFRICA <- value(RESULTS_AFRICA)
--
This email has been checked for viruses by AVG.
https://www.avg.com
[[alternative HTML version deleted]]
__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
