I?m trying to pull data from one table (batting) in the Lahman Baseball
database. Notice X2B for doubles and X3B for triples ? fourth and fifth from
the right.
The dbGetQuery function runs fine when I leave there two out but I get error
messages (in red) when I include 2B/3B or X2B/X3B.
Can anyone give me some direction?
Thanks,
Philip Heinrich
***************************************************************************************************************************************************
tail(dbReadTable(Lahman,"batting"))
ID playerID yearID stint teamID team_ID lgID G
G_batting AB R H X2B X3B HR RBI SB
107414 107414 yastrmi01 2019 1 SFN 2920 NL 107
NA 371 64 101 22 3 21 55 2
107416 107416 yelicch01 2019 1 MIL 2911 NL 130
NA 489 100 161 29 3 44 97 30
107419 107419 youngal01 2019 1 ARI 2896 NL 17
NA 25 1 1 0 0 0 0 0
107420 107420 zagunma01 2019 1 CHN 2901 NL 30
NA 36 2 9 3 0 0 5 0
107422 107422 zavalse01 2019 1 CHA 2900 AL 5
NA 12 1 1 0 0 0 0 0
107427 107427 zimmery01 2019 1 WAS 2925 NL 52
NA 171 20 44 9 0 6 27 0
107428 107428 zobribe01 2019 1 CHN 2901 NL 47
NA 150 24 39 5 0 1 17 0
107429 107429 zuninmi01 2019 1 TBA 2922 AL 90
NA 266 30 44 10 1 9 32 0
Hack11Batting <- dbGetQuery(Lahman,"SELECT
playerID,yearID,AB,R,H,2B,3B,HR,
RBI,SB,CS,BB,SO,IBB,HBP,SH,SF,GIDP FROM batting
WHERE yearID = 2018 AND AB >99")
Error: unrecognized token: "2B"
Hack11Batting <- dbGetQuery(Lahman,"SELECT
playerID,yearID,AB,R,H,X2B,X3B,HR,
RBI,SB,CS,BB,SO,IBB,HBP,SH,SF,GIDP FROM batting
WHERE yearID = 2018 AND AB >99")
Error: no such column: X2B
[[alternative HTML version deleted]]
Have you tried putting double quotes around 2B and 3B: "...2B, 3B, ..." -> "...\"2B\",\"3B\",..."? -Bill On Fri, Oct 2, 2020 at 3:49 PM Philip <herd_dog at cox.net> wrote:> I?m trying to pull data from one table (batting) in the Lahman Baseball > database. Notice X2B for doubles and X3B for triples ? fourth and fifth > from the right. > > The dbGetQuery function runs fine when I leave there two out but I get > error messages (in red) when I include 2B/3B or X2B/X3B. > > Can anyone give me some direction? > > Thanks, > Philip Heinrich > > *************************************************************************************************************************************************** > tail(dbReadTable(Lahman,"batting")) > > ID playerID yearID stint teamID team_ID > lgID G G_batting AB R H X2B X3B HR RBI SB > 107414 107414 yastrmi01 2019 1 SFN 2920 NL > 107 NA 371 64 101 22 3 21 55 2 > 107416 107416 yelicch01 2019 1 MIL 2911 NL > 130 NA 489 100 161 29 3 44 97 30 > 107419 107419 youngal01 2019 1 ARI 2896 NL > 17 NA 25 1 1 0 0 0 0 > 0 > 107420 107420 zagunma01 2019 1 CHN 2901 NL > 30 NA 36 2 9 3 0 0 5 > 0 > 107422 107422 zavalse01 2019 1 CHA 2900 AL > 5 NA 12 1 1 0 0 0 > 0 0 > 107427 107427 zimmery01 2019 1 WAS 2925 NL > 52 NA 171 20 44 9 0 6 27 0 > 107428 107428 zobribe01 2019 1 CHN 2901 NL > 47 NA 150 24 39 5 0 1 17 0 > 107429 107429 zuninmi01 2019 1 TBA 2922 AL > 90 NA 266 30 44 10 1 9 32 0 > > > Hack11Batting <- dbGetQuery(Lahman,"SELECT playerID,yearID,AB,R,H,2B,3B,HR, > RBI,SB,CS,BB,SO,IBB,HBP,SH,SF,GIDP FROM batting > WHERE yearID = 2018 AND AB >99") > Error: unrecognized token: "2B" > > Hack11Batting <- dbGetQuery(Lahman,"SELECT > playerID,yearID,AB,R,H,X2B,X3B,HR, > RBI,SB,CS,BB,SO,IBB,HBP,SH,SF,GIDP FROM batting > WHERE yearID = 2018 AND AB >99") > Error: no such column: X2B > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at 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. >[[alternative HTML version deleted]]
The double quotes are required by SQL if a name is not of the form letter-followed-by-any-number-of-letters-or-numbers or if the name is a SQL keyword like 'where' or 'select'. If you are doing this from a function, you may as well quote all the names. -Bill On Fri, Oct 2, 2020 at 6:18 PM Philip <herd_dog at cox.net> wrote:> The \?2B\? worked. Have no idea why. Can you point me somewhere that can > explain this to me. > > Thanks, > Philip > > *From:* Bill Dunlap > *Sent:* Friday, October 2, 2020 3:54 PM > *To:* Philip > *Cc:* r-help > *Subject:* Re: [R] Lahman Baseball Data Using R DBI Package > > Have you tried putting double quotes around 2B and 3B: "...2B, 3B, ..." > -> "...\"2B\",\"3B\",..."? > > -Bill > > On Fri, Oct 2, 2020 at 3:49 PM Philip <herd_dog at cox.net> wrote: > >> I?m trying to pull data from one table (batting) in the Lahman Baseball >> database. Notice X2B for doubles and X3B for triples ? fourth and fifth >> from the right. >> >> The dbGetQuery function runs fine when I leave there two out but I get >> error messages (in red) when I include 2B/3B or X2B/X3B. >> >> Can anyone give me some direction? >> >> Thanks, >> Philip Heinrich >> >> *************************************************************************************************************************************************** >> tail(dbReadTable(Lahman,"batting")) >> >> ID playerID yearID stint teamID team_ID >> lgID G G_batting AB R H X2B X3B HR RBI SB >> 107414 107414 yastrmi01 2019 1 SFN 2920 >> NL 107 NA 371 64 101 22 3 21 >> 55 2 >> 107416 107416 yelicch01 2019 1 MIL 2911 >> NL 130 NA 489 100 161 29 3 44 97 30 >> 107419 107419 youngal01 2019 1 ARI 2896 >> NL 17 NA 25 1 1 0 0 >> 0 0 0 >> 107420 107420 zagunma01 2019 1 CHN 2901 NL >> 30 NA 36 2 9 3 0 0 >> 5 0 >> 107422 107422 zavalse01 2019 1 CHA 2900 >> AL 5 NA 12 1 1 0 0 >> 0 0 0 >> 107427 107427 zimmery01 2019 1 WAS 2925 NL >> 52 NA 171 20 44 9 0 6 27 0 >> 107428 107428 zobribe01 2019 1 CHN 2901 >> NL 47 NA 150 24 39 5 0 1 >> 17 0 >> 107429 107429 zuninmi01 2019 1 TBA 2922 >> AL 90 NA 266 30 44 10 1 9 >> 32 0 >> >> >> Hack11Batting <- dbGetQuery(Lahman,"SELECT >> playerID,yearID,AB,R,H,2B,3B,HR, >> RBI,SB,CS,BB,SO,IBB,HBP,SH,SF,GIDP FROM >> batting >> WHERE yearID = 2018 AND AB >99") >> Error: unrecognized token: "2B" >> >> Hack11Batting <- dbGetQuery(Lahman,"SELECT >> playerID,yearID,AB,R,H,X2B,X3B,HR, >> RBI,SB,CS,BB,SO,IBB,HBP,SH,SF,GIDP FROM >> batting >> WHERE yearID = 2018 AND AB >99") >> Error: no such column: X2B >> >> [[alternative HTML version deleted]] >> >> ______________________________________________ >> R-help at 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. >> >[[alternative HTML version deleted]]
Hi Philip, You've probably realized by now that R doesn't like column names that start with a number. If you try to access an R-dataframe column named 2B or 3B with the familiar "$" notation, you'll get an error:> library(DBI) > library(RSQLite) > con2 <- dbConnect(SQLite(), "~/R_Dir/lahmansbaseballdb.sqlite") > Hack12Batting <- dbGetQuery(con2,"SELECT * FROM batting WHERE yearID = 2018 AND AB >600 ORDER BY AB DESC") > Hack12Batting$AB[1] 664 661 639 632 632 632 626 623 620 618 617 613 606 605 602> Hack12Batting$3BError: unexpected numeric constant in "Hack12Batting$3" How to handle? You can rename columns on-the-fly by piping. See reference [1] and use either library(magrittr) or library(dplyr) or a combination thereof: library(magrittr) dbGetQuery(con2,"SELECT * FROM batting WHERE yearID = 2018 AND AB >600 ORDER BY AB DESC") %>% set_colnames(make.names(colnames(.))) #OR one of the following: library(dplyr) dbGetQuery(con2,"SELECT * FROM batting WHERE yearID = 2018 AND AB >600 ORDER BY AB DESC") %>% rename(X2B = `2B`, X3B = `3B`) library(dplyr) dbGetQuery(con2,"SELECT * FROM batting WHERE yearID = 2018 AND AB >600 ORDER BY AB DESC") %>% `colnames<-`(make.names(colnames(.))) library(dplyr) dbGetQuery(con2,"SELECT * FROM batting WHERE yearID = 2018 AND AB >600 ORDER BY AB DESC") %>% magrittr::set_colnames(make.names(colnames(.))) Best, Bill. W. Michels, Ph.D. [1] https://stackoverflow.com/questions/28100780/use-with-replacement-functions-like-colnames On Fri, Oct 2, 2020 at 7:34 PM Bill Dunlap <williamwdunlap at gmail.com> wrote:> > The double quotes are required by SQL if a name is not of the form > letter-followed-by-any-number-of-letters-or-numbers or if the name is a SQL > keyword like 'where' or 'select'. If you are doing this from a function, > you may as well quote all the names. > > -Bill > > On Fri, Oct 2, 2020 at 6:18 PM Philip <herd_dog at cox.net> wrote: > > > The \?2B\? worked. Have no idea why. Can you point me somewhere that can > > explain this to me. > > > > Thanks, > > Philip > > > > *From:* Bill Dunlap > > *Sent:* Friday, October 2, 2020 3:54 PM > > *To:* Philip > > *Cc:* r-help > > *Subject:* Re: [R] Lahman Baseball Data Using R DBI Package > > > > Have you tried putting double quotes around 2B and 3B: "...2B, 3B, ..." > > -> "...\"2B\",\"3B\",..."? > > > > -Bill > > > > On Fri, Oct 2, 2020 at 3:49 PM Philip <herd_dog at cox.net> wrote: > > > >> I?m trying to pull data from one table (batting) in the Lahman Baseball > >> database. Notice X2B for doubles and X3B for triples ? fourth and fifth > >> from the right. > >> > >> The dbGetQuery function runs fine when I leave there two out but I get > >> error messages (in red) when I include 2B/3B or X2B/X3B. > >> > >> Can anyone give me some direction? > >> > >> Thanks, > >> Philip Heinrich > >> > >> *************************************************************************************************************************************************** > >> tail(dbReadTable(Lahman,"batting")) > >> > >> ID playerID yearID stint teamID team_ID > >> lgID G G_batting AB R H X2B X3B HR RBI SB > >> 107414 107414 yastrmi01 2019 1 SFN 2920 > >> NL 107 NA 371 64 101 22 3 21 > >> 55 2 > >> 107416 107416 yelicch01 2019 1 MIL 2911 > >> NL 130 NA 489 100 161 29 3 44 97 30 > >> 107419 107419 youngal01 2019 1 ARI 2896 > >> NL 17 NA 25 1 1 0 0 > >> 0 0 0 > >> 107420 107420 zagunma01 2019 1 CHN 2901 NL > >> 30 NA 36 2 9 3 0 0 > >> 5 0 > >> 107422 107422 zavalse01 2019 1 CHA 2900 > >> AL 5 NA 12 1 1 0 0 > >> 0 0 0 > >> 107427 107427 zimmery01 2019 1 WAS 2925 NL > >> 52 NA 171 20 44 9 0 6 27 0 > >> 107428 107428 zobribe01 2019 1 CHN 2901 > >> NL 47 NA 150 24 39 5 0 1 > >> 17 0 > >> 107429 107429 zuninmi01 2019 1 TBA 2922 > >> AL 90 NA 266 30 44 10 1 9 > >> 32 0 > >> > >> > >> Hack11Batting <- dbGetQuery(Lahman,"SELECT > >> playerID,yearID,AB,R,H,2B,3B,HR, > >> RBI,SB,CS,BB,SO,IBB,HBP,SH,SF,GIDP FROM > >> batting > >> WHERE yearID = 2018 AND AB >99") > >> Error: unrecognized token: "2B" > >> > >> Hack11Batting <- dbGetQuery(Lahman,"SELECT > >> playerID,yearID,AB,R,H,X2B,X3B,HR, > >> RBI,SB,CS,BB,SO,IBB,HBP,SH,SF,GIDP FROM > >> batting > >> WHERE yearID = 2018 AND AB >99") > >> Error: no such column: X2B > >> > >> [[alternative HTML version deleted]] > >> > >> ______________________________________________ > >> R-help at 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. > >> > > > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at 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.