Hello R-Experts,
I've got a question, concerning the automation of a number of regressions
(lm) with the help of a loop (for i in ....).
The situation is as follows (the code follows after that):
I have my data in an access database. I have historical data for 2000 parts,
for each of this parts I want to do a regression analysis, so I need to do
2000 regressions (just for one country, there are also more countries). So
this would be a lot of manual effort.
What I want to do is to automate this routine. I have already established a
database connection via RODBC.
So I can acces every part with its distinct code by a SQL Query. For every
part, the SQL Query has to be adapted for the parts name.
My idea was to generate the SQL Code in Excel for ervery part and save this
as an txt-file. So I can define the SQL-Codes as an object (SQL_Code <-
load.table("...txt, header = FALSE).
In the next step I can build a loop, which tells R to go through the object
SQL_Code line for line, using the text for the SQL Query.
The code is as follows:
SQL Code in the sqlparts.txt. file: "SELECT table.* FROM table WHERE
((table.part) = '2929AAD766')" (2000 lines for all parts, each part
has a
distinct code)
SQL_Code <- load.table("sqlparts.txt", header = FALSE)
Loop:
for (c in a(1:2000)) {
Dataset <- sqlQuery(Database, SQL_Code[a,1])
print(summary(lm(Quant~ UPE + Mon, data = Dataset)))
}
Unfortunately, this loop doesn't work. I think, it's because R does not
interpret the object SQL_Code as text, so the sqlQuery is incomplete.
Can anybody help me with that problem?
Thank you in advance.
Greets
Ryan
--
View this message in context:
http://r.789695.n4.nabble.com/Automated-Regressions-tp4186280p4186280.html
Sent from the R help mailing list archive at Nabble.com.
Hi Ryan,
My name is Frederic Andrieu and I work for Mango Solutions in the UK. It looks
like you have swapped the a and c in this for loop statement:
This "for (c in a(1:2000)) {" should be this "for (a in
c(1:2000)) {"
Which is why the for loop is not running, I would suggest something like this:
#################################################################################################################################################
Require(RODBC)
myChannel <- odbcConnect(dsn = "myDSN")
sqlString1 <- "SELECT myTable.Contries FROM myTable GROUP BY
myTable.Countries;"
myContries <- sqlQuery(channel = myChannel, query = sqlString1)
# myContries is now a table of counties with 1 column and as many rows as there
are countries
# Then this is the function that will do the legwork myLm <-
function(myCountry = myCountries[1]){
print(myCountry)
sqlString2 <- paste("SELECT myTable.* FROM myTable WHERE
(((myTable.Countries)=", myCountry,")); ", sep = "")
myData <- sqlQuery(channel = myChannel, query = sqlString2)
myOut <- summary(lm(Quant ~ UPE + Mon, data = myData))$coeff
myOut <- data.frame(myOut)
colnames(myOut) <- c("Estimate", "Std. Error", "
tvalue", " Pr(>|t|)")
myOut <- data.frame(myCountry, myOut)
return(myOut)
}
# Applying the function
myAnalysis <- lapply(myCountries, myLm)
myAnalysis <- do.call(rbind, myAnalysis)
#################################################################################################################################################
Let me know if this helps.
Best regards,
Frederic Andrieu
M: +44(0)07813 526 123
T: +44 (0)1249 766 801?
F: +44 (0)1249 767 707
www.mango-solutions.com
Unit 2 Greenways Business Park
Bellinger Close
Chippenham
Wilts
SN15 1BN
UK
-----Original Message-----
From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org]
On Behalf Of ryanSt
Sent: 12 December 2011 14:59
To: r-help at r-project.org
Subject: [R] Automated Regressions
Hello R-Experts,
I've got a question, concerning the automation of a number of regressions
(lm) with the help of a loop (for i in ....).
The situation is as follows (the code follows after that):
I have my data in an access database. I have historical data for 2000 parts,
for each of this parts I want to do a regression analysis, so I need to do
2000 regressions (just for one country, there are also more countries). So
this would be a lot of manual effort.
What I want to do is to automate this routine. I have already established a
database connection via RODBC.
So I can acces every part with its distinct code by a SQL Query. For every
part, the SQL Query has to be adapted for the parts name.
My idea was to generate the SQL Code in Excel for ervery part and save this
as an txt-file. So I can define the SQL-Codes as an object (SQL_Code <-
load.table("...txt, header = FALSE).
In the next step I can build a loop, which tells R to go through the object
SQL_Code line for line, using the text for the SQL Query.
The code is as follows:
SQL Code in the sqlparts.txt. file: "SELECT table.* FROM table WHERE
((table.part) = '2929AAD766')" (2000 lines for all parts, each part
has a
distinct code)
SQL_Code <- load.table("sqlparts.txt", header = FALSE)
Loop:
for (c in a(1:2000)) {
Dataset <- sqlQuery(Database, SQL_Code[a,1])
print(summary(lm(Quant~ UPE + Mon, data = Dataset)))
}
Unfortunately, this loop doesn't work. I think, it's because R does not
interpret the object SQL_Code as text, so the sqlQuery is incomplete.
Can anybody help me with that problem?
Thank you in advance.
Greets
Ryan
--
View this message in context:
http://r.789695.n4.nabble.com/Automated-Regressions-tp4186280p4186280.html
Sent from the R help mailing list archive at Nabble.com.
______________________________________________
R-help at r-project.org mailing list
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.
LEGAL NOTICE
This message is intended for the use o...{{dropped:10}}
I don't know why you had "c in a(1:2000))"
c is a function see ?c ...
and you want "a" (the row number in SQL_Code) to change with each
iteration
in the loop.
Perhaps this might work (I'm not saying this is the best option, just a
potential fix for what you have):
for (a in 1:2000) {
Dataset <- sqlQuery(Database, SQL_Code[a,1])
print(summary(lm(Quant~ UPE + Mon, data = Dataset)))
}
ryanSt wrote>
> Hello R-Experts,
>
> I've got a question, concerning the automation of a number of
regressions
> (lm) with the help of a loop (for i in ....).
>
> The situation is as follows (the code follows after that):
> I have my data in an access database. I have historical data for 2000
> parts, for each of this parts I want to do a regression analysis, so I
> need to do 2000 regressions (just for one country, there are also more
> countries). So this would be a lot of manual effort.
> What I want to do is to automate this routine. I have already established
> a database connection via RODBC.
> So I can acces every part with its distinct code by a SQL Query. For every
> part, the SQL Query has to be adapted for the parts name.
> My idea was to generate the SQL Code in Excel for ervery part and save
> this as an txt-file. So I can define the SQL-Codes as an object (SQL_Code
> <- load.table("...txt, header = FALSE).
> In the next step I can build a loop, which tells R to go through the
> object SQL_Code line for line, using the text for the SQL Query.
>
>
> The code is as follows:
> SQL Code in the sqlparts.txt. file: "SELECT table.* FROM table WHERE
> ((table.part) = '2929AAD766')" (2000 lines for all parts, each
part has a
> distinct code)
>
> SQL_Code <- load.table("sqlparts.txt", header = FALSE)
>
> Loop:
> for (c in a(1:2000)) {
>
> Dataset <- sqlQuery(Database, SQL_Code[a,1])
>
> print(summary(lm(Quant~ UPE + Mon, data = Dataset)))
> }
>
>
> Unfortunately, this loop doesn't work. I think, it's because R does
not
> interpret the object SQL_Code as text, so the sqlQuery is incomplete.
>
> Can anybody help me with that problem?
>
> Thank you in advance.
>
> Greets
> Ryan
>
--
View this message in context:
http://r.789695.n4.nabble.com/Automated-Regressions-tp4186280p4187109.html
Sent from the R help mailing list archive at Nabble.com.