Error running a SQL query in R with sqldf

Question

I want to make a summary of a larger table using SQL query with sqldf package in R.

The larger table iterationresults has following columns: Truck_ID, Latitude, Longitude, Speed, Idle_Events, Date_Time, state, od, trip_id.

Sample table

Truck_ID Latitude Longitude Speed Idle_Events Date_Time           state od trip_id
TTI 039  31.70117 -106.3685 0     NA          2017-03-29 14:37:30 stop  0  217
TTI 039  31.70119 -106.3685 0     0           2017-03-29 14:37:31 stop  0  217
TTI 039  31.70120 -106.3685 0     0           2017-03-29 14:37:32 stop  0  217
TTI 039  31.70120 -106.3685 0     0           2017-03-29 14:37:33 stop  0  217
TTI 039  31.70119 -106.3685 0     1           2017-03-29 14:37:34 stop  0  217
TTI 039  31.70120 -106.3685 0     1           2017-03-29 14:37:35 stop  0  217
TTI 039  31.70120 -106.3685 0     1           2017-03-29 14:37:36 stop  0  217
TTI 039  31.70121 -106.3685 0     1           2017-03-29 14:37:37 stop  0  217
TTI 039  31.70121 -106.3685 0     1           2017-03-29 14:37:38 stop  0  217
TTI 039  31.70122 -106.3685 0     1           2017-03-29 14:37:39 stop  0   217

The row count is 49258. I need to make a summary table based on trip_id. I am trying to run the following SQL query with sqldf package in R to make a new summary table trips.

SQL <- "SELECT Avg(speed) as [Average Speed]
        FROM iterationresults
        GROUP BY trip_id
        ORDER BY trip_id"
trips <-sqldf(SQL)

I am getting a error saying:

Error in rsqlite_bind_rows(rs@ptr, value) : Parameter 6 does not have length 49258.

I am not sure whats wrong here. I am new to using this package.

nothing wrong with your query. dput(iterationresults) and share output. — MKR
– MKR, Commented Jan 30, 2018 at 16:06
dput(iteration results) .Names = c("Truck_ID", "Latitude", "Longitude", "Speed", "Idle_Events", "Date_Time", "state", "od", "trip_id"), row.names = c(NA, -49258L), class = c("data.table", "data.frame"), .internal.selfref = <pointer: 0x0000000002590788>) — rohit j
– rohit j, Commented Jan 30, 2018 at 16:16
There is a big difference between mysql and sql-server but you taged both — B001ᛦ
– B001ᛦ, Commented Jan 30, 2018 at 16:33
Hard to tell based on the info given, but it looks like the number of rows in your summary table is 49258 based on the error but your SQL Query result has fewer rows because of the aggregation function, which will throw an error when using the '<-' assignment operator to create a new column on a data frame — Martin Boros
– Martin Boros, Commented Jan 30, 2018 at 16:55

M-- · Accepted Answer · 2020-01-09 19:27:09Z

3

It's because the data.frame contains POSIXlt type (Date_Time column). I started to see this bug after adding POSIXlt to my data.frame as well.

I am not exactly sure if it's a bug or a "feature"; but I found this bug-report which explains it: https://github.com/r-dbi/RSQLite/issues/246

I posted there with a follow-up question about the problem.

edited Jan 9, 2020 at 19:27

M--

33.6k12 gold badges74 silver badges115 bronze badges

answered Jan 8, 2020 at 13:39

Tomas

60.2k54 gold badges251 silver badges386 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error running a SQL query in R with sqldf

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related