Error in tcltk::as.tclObj(X) : cannot handle object of mode 'list

Question

I'm very new to R studio and Sqldf. I don't know what the issue is even if I wanted to thoroughly explain it. I am trying to use a SQL code to count up all distinct zip codes in the column zip_code. I know I need Distinct and Count somewhere but I don't know how it works with Sqldf. I also don't know why I am getting the following error when doing this basic code:

Det_Crime %>% sqldf("select zip_code, from Det_Crime")

Error in tcltk::as.tclObj(X) : cannot handle object of mode 'list

My data:

Also is there a way to do this on dplyr?

Answer 1

Input

Det_Crime <- tibble(zip_code=c("01001", "01001", "01002"))

Use dplyr::count()

Det_Crime %>% count(zip_code)

Output:

  zip_code     n
  <chr>    <int>
1 01001        2
2 01002        1

Answer 2

sqldf is used in a pipline like this. We use the builtin BOD data frame as an example. The brace brackets prevent it from automatically inserting the left side into the first argument of sqldf and [.] in the SQL string refers to the left hand side of the pipe.

BOD %>% { sqldf("select * from [.]") }
##   Time demand
## 1    1    8.3
## 2    2   10.3
## 3    3   19.0
## 4    4   16.0
## 5    5   15.6
## 6    7   19.8

Alternately use it standalone without the pipe. This gives the same result.

sqldf("select * from BOD")

Regarding how to compute the number of distinct values in a column this gives the number of distinct values in the cyl column of the built-in mtcars data frame. Replace count(...) with count(*) if you want the number of rows instead.

sqldf("select count(distinct cyl) as count from mtcars")
##   count
## 1     3

To get the number of rows for each cyl:

sqldf("select cyl, count(*) as count
       from mtcars
       group by cyl")
##   cyl count
## 1   4    11
## 2   6     7
## 3   8    14