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$3B Error: 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 <williamwdun...@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_...@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_...@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@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@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@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.