AREF-CONTEXT

[gassechen]

(sqldf:read-table *conn* 'df33 )I get this error because there are fields that are nil The value NIL is not of type DOUBLE-FLOAT 'AREF-CONTEXT

in read-table function

(sqlite::with-prepared-statement stmt (db (format nil "select * from ~A" table) nil)
      (loop while (sqlite:step-statement stmt)
        for i = 0 then (1+ i)
        do (loop for j from 0 below ncols
             do (setf (aref (cdr (nth j columns)) i) (sqlite:statement-column-value stmt j)))))
    (data-frame:alist-df columns)))

for this code

(loop while (sqlite:step-statement stmt)
for i = 0 then (1+ i)
do (loop for j from 0 below ncols
do (let ((value (sqlite:statement-column-value stmt j)))
      (if (null value)
          (setf (aref (cdr (nth j columns)) i) 0.00d0) ; Assign a default value (0.0 in this case)
          (setf (aref (cdr (nth j columns)) i) value))) )))

maybe in a sqldf function

;; Copy the data into the data-frame
        do (loop for j below (length column-names)
             do (setf (aref (cdr (nth j data)) i) (sqlite:statement-column-value stmt j)))))

you have to do the same modification

[snunez1]

Good catch! Thank you. Could you prepare a pull request with this fix please?

[gassechen]

;; TODO: handle packages for the keys too
(defun sqldf (sql)
  "Execute SQL (a string) on a data frame and return a new data frame with the results.
The data frame is identified by the word following FROM (case insensitive) in the SQL string.  An in-memory SQLite database is creaetd, the contents of the data frame loaded, the query performed and a new DATA-FRAME returned with the results and the database deleted.  In most cases, using this library is faster, from a developers time perspective, than writing the code to perform the same query.  SQLDF has been tested with data frames of 350K rows with no slow-down noted.  The R documentation for their version of SQLDF suggests that it could be faster than Lisp native queries.  Note that the SQL query must use SQL style names for columns and not the Lisp versions, e.g. flight-time becomes flight_time."

  (let* ((db    (sqlite:connect ":memory:"))
     (words (uiop:split-string sql))
     (table (nth (1+ (position "from" words :test #'string=)) words))
     (df-name (find-symbol (string-upcase table)))
     df nrows data)

    (if (not (and (boundp df-name)
          (typep (symbol-value df-name) 'df:data-frame)))
    (error "Could not find data frame ~A" table)
    (setf df (symbol-value df-name)))

    (create-df-table db table df)
    (write-table     db table df)

    ;; There's no good way to get the number of rows in a query in SQLite
    (setf nrows (sqlite:execute-single db (format nil "select count (1) from (~A)" sql)))

    (sqlite::with-prepared-statement stmt (db sql nil)
      (loop while (sqlite:step-statement stmt)
        for i = 0 then (1+ i)
        ;; with column-names = (sqlite:statement-column-names stmt)
        with column-names = (map 'list
                     #'(lambda (x)
                     (from-sql-name x))
                     (sqlite:statement-column-names stmt))
        for types = (loop for i below (length column-names)
                  collect (statement-column-type stmt i))

        ;; Allocate the column memory & types
        do (if (not data)
           (setf data (loop
                   for name in column-names
                   for type in types
                   collect (cons (if (find-symbol name) (find-symbol name) (intern (string-upcase name)))
                         (alexandria:switch (type :test #'string=)
                           ("REAL"    (make-array nrows :element-type 'double-float))
                           ("INTEGER" (make-array nrows :element-type 'integer))
                           ("TEXT"    (make-array nrows)))))))

        ;; Copy the data into the data-frame
        do (loop for j below (length column-names)
             do (let ((column-value (sqlite:statement-column-value stmt j)))
              (setf (aref (cdr (nth j data)) i)
                (if (null column-value)
                    (alexandria:switch (type :test #'string=)
                    ("REAL"    0.00d0)
                    ("INTEGER" 0)
                    ("TEXT"    "")
                      (:default-value))  ; Cambiar a un valor por defecto adecuado
                    column-value))))))  ; Si no es nulo, usar el valor real

    (sqlite:disconnect db)
    (data-frame:alist-df data)))

1 compiler notes:

../../../quicklisp/local-projects/sqldf/sqldf.lisp:55:37:
  warning: 
    undefined variable: COMMON-LISP:TYPE
    --> IDENTITY 
    ==>
      1

Compilation failed.

[snunez1]

A few comments:

• type is only scoped in the loop. Note how the loop above yours sets it
• It's not a good idea to set missing values to 0. Different uses will need to handle missing values differently.
• I think in this case you're better off excluding the missing values before printing the data frame to HTML.
• The 'right' solution here will need to allow the user to pass a 'missing value map' of sorts, in the same way that dfio csv import does.

If you want a fast temporary fix, correcting the type error you've got above should work.

You might have faster responses if you post on the lisp forums, lists or websites. Github is primarily for Lisp-Stat specific bugs and the forums are great for general lisp.