Error: No Such Column Using Sqldf
Below Are the Scripts > Library(Sqldf) > Turnover = Read. Csv("Turnover. Csv") > Names(Turnover) [1] "Report. Date" "Persid" "Status" "Dob" [5] "Age" "Tenure"...
Below are the scripts
> library(sqldf)
> turnover = read.csv("turnover.csv")
> names(turnover)
[1] "Report.Date" "PersID" "Status" "DOB"
[5] "Age" "Tenure" "Current.Hire.Date" "Term.Date"
[9] "Gender" "Function" "Grade" "Job.Category"
[13] "City" "State" "Retiree" "Race"
> turnover_hiredate = sqldf("select Status, Current.Hire.Date from turnover")
I get an error msg: no such column: Current.Hire.Date. But this variable is listed as the 7th variable.
What did I do wrong?
3 Answers
There is no need to change column names.
Starting with RSQLite 1.0.0 and sqldf 0.4-9 dots in column names are no longer translated to underscores.
We only need to write the SQL statement between single quotes, and the column names including dots between double quotes or backticks/backquotes interchangeably.
Two examples:
require(sqldf)
# 1
turnover <- data.frame(Status = c("A", "B", "C"),
Current.Hire.Date = c("4/10/10", "13/11/10", "1/7/13"))
sqldf('select Status, "Current.Hire.Date" from turnover')
#2. Double quotes and backticks interchangeably
sqldf('select Species, avg("Sepal.Length") `Sepal.Length`,
avg("Sepal.Width") `Sepal.Width` from iris group by Species')
One more way to achieve the solution
#3 Using square brackets
sqldf('select Species, avg([Sepal.Length]) `Sepal.Length`,
avg([Sepal.Width]) `Sepal.Width` from iris group by Species')
sqldf(...) does not like . (period) in column names, so you need to change it to something else. Try this:
library(sqldf)
turnover = read.csv("turnover.csv")
colnames(turnover) <- gsub("\\.","_",colnames(turnover))
turnover_hiredate = sqldf("select Status, Current_Hire_Date from turnover")
The reason is that the period is used in SQL to indicate a table column, e.g. turnover.Status.
You can also use
library(sqldf)
turnover <- read.csv("turnover.csv", header=TRUE, check.names=FALSE) #Leave Names Alone
names(turnover) <- gsub(x = names(turnover),
pattern = " ",
replacement = "_") # Replace Var Name blank with _
Robert Thorne covers electric vehicle innovations, autonomous driving systems, global mobility trends, and automotive engineering developments.
