library(RMySQL)
library(glmnet)
library(nnet)
drv=dbDriver("MySQL")
con <- dbConnect(drv, user="root", password="root",
                 dbname="PitchFX",
                 unix.socket="/Applications/MAMP/tmp/mysql/mysql.sock")

data2003<-dbGetQuery(con, "SELECT a.* FROM RidgebaseSBA2003 a") #pull query with year of interest
CS<-data2003$CS # create vector with predictor variable
data2003<-with(data2003, data.frame(class.ind(pitcher), class.ind(catcher), class.ind(baserunner), INN_CT, P_hand_R, P_hand_L, BS1__, BS_2_,BS1_3,PK, scoremargin)) # create design matrix of independent variables
data2003<-as.matrix(data2003) # convert in data matrix
lambda2003=cv.glmnet(data2003, CS) # cross validation to find lambda value that min(error)
lambda.min.2003 <- lambda2003$lambda.min # store lambda value that yield min(error)
ridge2003=glmnet(data2003, CS, family=c("gaussian"), alpha=0, lambda=lambda.min) #run ridge regression
ridge2003<-data.matrix(coef(ridge2003)) # pull coef for each variable
write.csv(ridge2003, "~/Desktop/ridgeCS2003.csv") # write table on Desktop of results

# loop through each year, or chose to query multiple at a time
data2004<-dbGetQuery(con, "SELECT a.* FROM RidgebaseSBA2004 a")
CS<-data2004$CS
data2004<-with(data2004, data.frame(class.ind(pitcher), class.ind(catcher), class.ind(baserunner), INN_CT, P_hand_R, P_hand_L, BS1__, BS_2_,BS1_3,PK, scoremargin))
data2004<-as.matrix(data2004)
lambda2004=cv.glmnet(data2004, CS)
lambda.min.2004 <- lambda2004$lambda.min
ridge2004=glmnet(data2004, CS, family=c("gaussian"), alpha=0, lambda=lambda.min)
ridge2004<-data.matrix(coef(ridge2004))
write.csv(ridge2004, "~/Desktop/ridgeCS2004.csv")

data2005<-dbGetQuery(con, "SELECT a.* FROM RidgebaseSBA2005 a")
CS<-data2005$CS
data2005<-with(data2005, data.frame(class.ind(pitcher), class.ind(catcher), class.ind(baserunner), INN_CT, P_hand_R, P_hand_L, BS1__, BS_2_,BS1_3,PK, scoremargin))
data2005<-as.matrix(data2005)
lambda2005=cv.glmnet(data2005, CS)
lambda.min.2005 <- lambda2005$lambda.min
ridge2005=glmnet(data2005, CS, family=c("gaussian"), alpha=0, lambda=lambda.min)
ridge2005<-data.matrix(coef(ridge2005))
write.csv(ridge2005, "~/Desktop/ridgeCS2005.csv")

data2006<-dbGetQuery(con, "SELECT a.* FROM RidgebaseSBA2006 a")
CS<-data2006$CS
data2006<-with(data2006, data.frame(class.ind(pitcher), class.ind(catcher), class.ind(baserunner), INN_CT, P_hand_R, P_hand_L, BS1__, BS_2_,BS1_3,PK, scoremargin))
data2006<-as.matrix(data2006)
lambda2006=cv.glmnet(data2006, CS)
lambda.min.2006 <- lambda2006$lambda.min
ridge2006=glmnet(data2006, CS, family=c("gaussian"), alpha=0, lambda=lambda.min)
ridge2006<-data.matrix(coef(ridge2006))
write.csv(ridge2006, "~/Desktop/ridgeCS2006.csv")

data2007<-dbGetQuery(con, "SELECT a.* FROM RidgebaseSBA2007 a")
CS<-data2007$CS
data2007<-with(data2007, data.frame(class.ind(pitcher), class.ind(catcher), class.ind(baserunner), INN_CT, P_hand_R, P_hand_L, BS1__, BS_2_,BS1_3,PK, scoremargin))
data2007<-as.matrix(data2007)
lambda2007=cv.glmnet(data2007, CS)
lambda.min.2007 <- lambda2007$lambda.min
ridge2007=glmnet(data2007, CS, family=c("gaussian"), alpha=0, lambda=lambda.min)
ridge2007<-data.matrix(coef(ridge2007))
write.csv(ridge2007, "~/Desktop/ridgeCS2007.csv")

data2008<-dbGetQuery(con, "SELECT a.* FROM RidgebaseSBA2008 a")
CS<-data2008$CS
data2008<-with(data2008, data.frame(class.ind(pitcher), class.ind(catcher), class.ind(baserunner), INN_CT, P_hand_R, P_hand_L, BS1__, BS_2_,BS1_3,PK, scoremargin))
data2008<-as.matrix(data2008)
lambda2008=cv.glmnet(data2008, CS)
lambda.min.2008 <- lambda2008$lambda.min
ridge2008=glmnet(data2008, CS, family=c("gaussian"), alpha=0, lambda=lambda.min)
ridge2008<-data.matrix(coef(ridge2008))
write.csv(ridge2008, "~/Desktop/ridgeCS2008.csv")

data2009<-dbGetQuery(con, "SELECT a.* FROM RidgebaseSBA2009 a")
CS<-data2009$CS
data2009<-with(data2009, data.frame(class.ind(pitcher), class.ind(catcher), class.ind(baserunner), INN_CT, P_hand_R, P_hand_L, BS1__, BS_2_,BS1_3,PK, scoremargin))
data2009<-as.matrix(data2009)
lambda2009=cv.glmnet(data2009, CS)
lambda.min.2009 <- lambda2009$lambda.min
ridge2009=glmnet(data2009, CS, family=c("gaussian"), alpha=0, lambda=lambda.min)
ridge2009<-data.matrix(coef(ridge2009))
write.csv(ridge2009, "~/Desktop/ridgeCS2009.csv")

data2010<-dbGetQuery(con, "SELECT a.* FROM RidgebaseSBA2010 a")
CS<-data2010$CS
data2010<-with(data2010, data.frame(class.ind(pitcher), class.ind(catcher), class.ind(baserunner), INN_CT, P_hand_R, P_hand_L, BS1__, BS_2_,BS1_3,PK, scoremargin))
data2010<-as.matrix(data2010)
lambda2010=cv.glmnet(data2010, CS)
lambda.min.2010 <- lambda2010$lambda.min
ridge2010=glmnet(data2010, CS, family=c("gaussian"), alpha=0, lambda=lambda.min)
ridge2010<-data.matrix(coef(ridge2010))
write.csv(ridge2010, "~/Desktop/ridgeCS2010.csv")

data2011<-dbGetQuery(con, "SELECT a.* FROM RidgebaseSBA2011 a")
CS<-data2011$CS
data2011<-with(data2011, data.frame(class.ind(pitcher), class.ind(catcher), class.ind(baserunner), INN_CT, P_hand_R, P_hand_L, BS1__, BS_2_,BS1_3,PK, scoremargin))
data2011<-as.matrix(data2011)
lambda2011=cv.glmnet(data2011, CS)
lambda.min.2011 <- lambda2011$lambda.min
ridge2011=glmnet(data2011, CS, family=c("gaussian"), alpha=0, lambda=lambda.min)
ridge2011<-data.matrix(coef(ridge2011))
write.csv(ridge2011, "~/Desktop/ridgeCS2011.csv")

data2012<-dbGetQuery(con, "SELECT a.* FROM RidgebaseSBA2012 a")
CS<-data2012$CS
data2012<-with(data2012, data.frame(class.ind(pitcher), class.ind(catcher), class.ind(baserunner), INN_CT, P_hand_R, P_hand_L, BS1__, BS_2_,BS1_3,PK, scoremargin))
data2012<-as.matrix(data2012)
lambda2012=cv.glmnet(data2012, CS)
lambda.min.2012 <- lambda2012$lambda.min
ridge2012=glmnet(data2012, CS, family=c("gaussian"), alpha=0, lambda=lambda.min)
ridge2012<-data.matrix(coef(ridge2012))
write.csv(ridge2012, "~/Desktop/ridge/ridgeCS2012.csv")

data2013<-dbGetQuery(con, "SELECT a.* FROM RidgebaseSBA2013 a")
CS<-data2013$CS
data2013<-with(data2013, data.frame(class.ind(pitcher), class.ind(catcher), class.ind(baserunner), INN_CT, P_hand_R, P_hand_L, BS1__, BS_2_,BS1_3,PK, scoremargin))
data2013<-as.matrix(data2013)
lambda2013=cv.glmnet(data2013, CS)
lambda.min.2013 <- lambda2013$lambda.min
ridge2013=glmnet(data2013, CS, family=c("gaussian"), alpha=0, lambda=lambda.min)
ridge2013<-data.matrix(coef(ridge2013))
write.csv(ridge2013, "~/Desktop/ridge/ridgeCS2013.csv")