Linear regression in R with data from Sql server

Question

I want to import data from MS Sql Server and apply linear regression on the data in R. But i am not sure how i can manipulate the data from sql server so that i can do a regression. My table in sql server looks like this,

Pack    Cubes   Name    Sales
1001    1.2      A      10
1001    1.2      B      12
1002    0.9      A      8
1002    0.9      B      5
1002    0.9      C      12
1003    1.5      A      5
1003    1.5      C      10
1004    0.8      B      8
1004    0.8      C      10
1005    1.3      A      5
1005    1.3      B      8
1005    1.3      C      12

If i would manipulate the data in excel for a regression model it would looks like this,

Cubes   A   B   C
1.2    10   12  0
0.9    8    5   12
1.5    5    0   10
0.8    0    8   10
1.3    5    8   12

The A, B, C is my dependent variables and Cubes my independent variable. The Pack in my sql table is just a reference. My Sql connection to a DSN looks like this (which works perfectly),

library(RODBC)
myconn <- odbcConnect("sqlserver")
data <- sqlQuery(myconn,"select Cubes,Name,Sales from mytable")

With the regression i tried (which is wrong),

summary(data)
reg<-lm(Cubes~Sales,data)
summary(reg)

How can i manipulate the data from sql server as i would if i did it in excel?

klash · Accepted Answer · 2014-01-08 07:09:56Z

2

Try reshape or the reshape package:

wide <- reshape(data, v.names = "Sales", idvar = "Cubes",
            timevar = "Name", direction = "wide")

answered Jan 8, 2014 at 7:09

klash

3411 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Paul Hiemstra Over a year ago

+1, although to get exactly what the OP asked for, you need to replace the NA by 0.

Jason Samuels Over a year ago

Thanks, reshape works, i just added wide[is.na(wide)]=0 that makes the NAs equal to 0.

Paul Hiemstra · Accepted Answer · 2014-01-08 07:19:30Z

2

I would use dcast from the reshape2 package. Note that dcast leads to NA for non-existing combinations of Name and Sales. You need to manually change this to 0:

res = dcast(df, Cubes ~ Name, value.var = 'Sales')
res[is.na(res)] = 0
res
  Cubes  A  B  C
1   0.8  0  8 10
2   0.9  8  5 12
3   1.2 10 12  0
4   1.3  5  8 12
5   1.5  5  0 10

answered Jan 8, 2014 at 7:19

Paul Hiemstra

61.2k12 gold badges146 silver badges151 bronze badges

Comments

Daniel B · Accepted Answer · 2014-01-08 07:17:30Z

0

You can get the data from SQL Server directly in the format you need by using an SQL as follows:

   SELECT Cubes,
    SUM(CASE WHEN Name='A' then Sales else 0 END) A,
    SUM(CASE WHEN Name='B' then Sales else 0 END) B,
    SUM(CASE WHEN Name='C' then Sales else 0 END) C
    FROM mytable
    GROUP BY Cubes

edited Jan 8, 2014 at 7:17

answered Jan 8, 2014 at 7:11

Daniel B

8075 silver badges13 bronze badges

Collectives™ on Stack Overflow

Linear regression in R with data from Sql server

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related