[R] Data type problem when extract data from SQLite to R by using RSQLite

[Seth Falcon]

On Tue, Mar 1, 2011 at 10:06 AM, chen jia <chen_1002 at fisher.osu.edu> wrote:
> Hi Seth,
>
> Thanks so much for identifying the problem and explaining everything.
> I think the first solution that you suggest--make sure the schema has
> well defined types--would work the best for me. But, I have one
> question about how to implement it, which is more about sqlite itself.
>
> First, I found out that the columns that don't have the expected data
> types in the table annual_data3 are created by aggregate functions in
> a separate table. These columns are later combined with other columns
> that do.
>
> I read the link that you provide,
> http://www.sqlite.org/datatype3.html. One paragraph says "When
> grouping values with the GROUP BY clause values with different storage
> classes are considered distinct, except for INTEGER and REAL values
> which are considered equal if they are numerically equal. No
> affinities are applied to any values as the result of a GROUP by
> clause."
>
> If I understand it correctly, the columns created by aggregate
> functions with a GROUP by clause do not have any expected data types.
>
> My solution is to use CREATE TABLE clause to declare the expected
> datatype and then insert the values of columns created by the
> aggregate functions with the GROUP by clause. However, this solution
> requires a CREATE TABLE cause every time the aggregate function and
> the GROUP by clause is used.
>
> My question is: Is this the best way to make sure that the columns as
> a result of a GROUP by clause have the expected data types? Thanks.

That might be a good question to post to the SQLite user's list :-)

I don't have an answer off the top of my head.  My reading of the
SQLite docs would lead me to expect that a GROUP BY clause would not
change/remove type if the column being grouped contains all the same
declared type affinity.

+ seth

>
> Best,
> Jia
>
> On Tue, Mar 1, 2011 at 1:16 AM, Seth Falcon <seth at userprimary.net> wrote:
>> Hi Jia,
>>
>> On Mon, Feb 28, 2011 at 6:57 PM, chen jia <chen_1002 at fisher.osu.edu> wrote:
>>> The .schema of table annual_data3 is
>>> sqlite> .schema annual_data3
>>> CREATE TABLE "annual_data3"(
>>>  PERMNO INT,
>>>  DATE INT,
>>>  CUSIP TEXT,
>>>  EXCHCD INT,
>>>  SICCD INT,
>>>  SHROUT INT,
>>>  PRC REAL,
>>>  RET REAL,
>>>  ...
>>>  pret_var,
>>>  pRET_sd,
>>>  nmret,
>>>  pya_var,
>>
>> [snip]
>>
>> Is there a reason that you've told SQLite the expected data type for
>> only some of the columns?
>>
>>> Interestingly, I find that the problem I reported does not for columns
>>> labeled real in the schema info. For example, the type of column RET
>>> never changes no matter what the first observation is.
>>
>> Yes, that is expected and I think it is the solution to your problem:
>> setup your schema so that all columns have a declared type.  For some
>> details on SQLite's type system see
>> http://www.sqlite.org/datatype3.html.
>>
>> RSQLite currently maps NA values to NULL in the database.  Pulling
>> data out of a SELECT query, RSQLite uses the sqlite3_column_type
>> SQLite API to determine the data type and map it to an R type.  If
>> NULL is encountered, then the schema is inspected using
>> sqlite3_column_decltype to attempt to obtain a type.  If that fails,
>> the data is mapped to a character vector at the R level.  The type
>> selection is done once after the first row has been fetched.
>>
>> To work around this you can:
>>
>> - make sure your schema has well defined
>>  types (which will help SQLite perform its operations);
>>
>> - check whether the returned column has the expected type and convert
>>  if needed at the R level.
>>
>> - remove NA/NULL values from the db or decide on a different way of
>>  encoding them (e.g you might be able to use -1 in the db in some
>>  situation to indicate missing).  Your R code would then need to map
>>  these to proper NA.
>>
>> Hope that helps.
>>
>> + seth
>>
>>
>>
>> --
>> Seth Falcon | @sfalcon | http://userprimary.net/
>>
>
>
>
> --
> 700 Fisher Hall
> 2100 Neil Ave.
> Columbus, Ohio  43210
> http://www.fisher.osu.edu/~chen_1002/
>



-- 
Seth Falcon | @sfalcon | http://userprimary.net/