[R] RSQLite: rounding

[Leonardo Miceli]

Hi Sarah

I didn't know the function dput!

I checked the formatting issue by doing simple arithmetic operations on the
data. But with the dput function these issue turned much more clear.

Here goes the result... I just change the "head" by the "tail" function, so
we must have exactly the same data. Unfortunately It is not what happened!

I turned very uncomfortable with the RSQLite package, I can not work with
these mistrustfulness... Think about building a R function to break RSQLite
querys results in small pieces and stack all again in a data.frame! It's
really not something I wish

thank you

summarizing, for example:

High = c(15L     , 15L,    15L,   16L,    16L,    16L) # from dput(tail(x1))
High = c(15.77, 15.89, 15.94, 16.06, 16.05, 16.29) # from dput(tail(x2))

> dput(tail(x1))
structure(list(Dt = c("2016-02-23", "2016-02-23", "2016-02-23",
"2016-02-23", "2016-02-23", "2016-02-23"), FK_tbFutureContracts_PK =
c("DI1 at BVMF@2022 at 1",
"DI1 at BVMF@2023 at 1", "DI1 at BVMF@2024 at 1", "DI1 at BVMF@2025 at 1", "DI1 at BVMF@2026 at 1",
"DI1 at BVMF@2027 at 1"), Sequence = c("F22 ", "F23 ", "F24 ", "F25 ",
"F26 ", "F27 "), Serie = c(33L, 35L, 37L, 39L, 40L, 41L), DaysToMaturity =
c(2141L,
2505L, 2870L, 3236L, 3601L, 3968L), BusinessDayToMaturity = c(1471L,
1722L, 1971L, 2225L, 2478L, 2728L), Open = c(15L, 15L, 15L, 15L,
16L, 16L), High = c(15L, 15L, 15L, 16L, 16L, 16L), Low = c(15L,
15L, 15L, 15L, 16L, 16L), Last = c(15L, 15L, 15L, 16L, 16L, 16L
), Settle = c(42730.93, 36721, 31529.52, 26990.28, 23137.84,
19629.27), Average = c(15.656, 15.775, 15.88, 15.923, 16.028,
16.159), Open_Interest = c(27856L, 261570L, 40600L, 340472L,
53640L, 80156L), Traded_Contracts = c(1025L, 18467L, 820L, 28044L,
720L, 12570L), Trades = c(77L, 1645L, 61L, 2073L, 23L, 140L),
    Volume = c(43851219, 678739310, 25892492, 760790054, 16690664,
    248374870), ContractSize = c(1e+07, 1e+07, 1e+07, 1e+07,
    1e+07, 1e+07)), .Names = c("Dt", "FK_tbFutureContracts_PK",
"Sequence", "Serie", "DaysToMaturity", "BusinessDayToMaturity",
"Open", "High", "Low", "Last", "Settle", "Average", "Open_Interest",
"Traded_Contracts", "Trades", "Volume", "ContractSize"), row.names =
92513:92518, class = "data.frame")

> dput(tail(x2))
structure(list(Dt = c("2016-02-23", "2016-02-23", "2016-02-23",
"2016-02-23", "2016-02-23", "2016-02-23"), FK_tbFutureContracts_PK =
c("DI1 at BVMF@2022 at 1",
"DI1 at BVMF@2023 at 1", "DI1 at BVMF@2024 at 1", "DI1 at BVMF@2025 at 1", "DI1 at BVMF@2026 at 1",
"DI1 at BVMF@2027 at 1"), Sequence = c("F22 ", "F23 ", "F24 ", "F25 ",
"F26 ", "F27 "), Serie = c(33L, 35L, 37L, 39L, 40L, 41L), DaysToMaturity =
c(2141L,
2505L, 2870L, 3236L, 3601L, 3968L), BusinessDayToMaturity = c(1471L,
1722L, 1971L, 2225L, 2478L, 2728L), Open = c(15.53, 15.61, 15.66,
15.75, 16.01, 16.12), High = c(15.77, 15.89, 15.94, 16.06, 16.05,
16.29), Low = c(15.53, 15.55, 15.66, 15.7, 16.01, 16.11), Last = c(15.68,
15.82, 15.88, 16.02, 16.03, 16.2), Settle = c(42730.93, 36721,
31529.52, 26990.28, 23137.84, 19629.27), Average = c(15.656,
15.775, 15.88, 15.923, 16.028, 16.159), Open_Interest = c(27856L,
261570L, 40600L, 340472L, 53640L, 80156L), Traded_Contracts = c(1025L,
18467L, 820L, 28044L, 720L, 12570L), Trades = c(77L, 1645L, 61L,
2073L, 23L, 140L), Volume = c(43851219, 678739310, 25892492,
760790054, 16690664, 248374870), ContractSize = c(10000000L,
10000000L, 10000000L, 10000000L, 10000000L, 10000000L)), .Names = c("Dt",
"FK_tbFutureContracts_PK", "Sequence", "Serie", "DaysToMaturity",
"BusinessDayToMaturity", "Open", "High", "Low", "Last", "Settle",
"Average", "Open_Interest", "Traded_Contracts", "Trades", "Volume",
"ContractSize"), row.names = 66:71, class = "data.frame")

2016-02-24 12:06 GMT-03:00 Sarah Goslee <sarah.goslee at gmail.com>:

> How did you "check it out"? I still suspect a formatting issue.
>
> Please use dput() to provide a bit of data from each, eg
> dput(head(x1))
> dput(head(x2))
>
> Sarah
>
> On Tue, Feb 23, 2016 at 5:31 PM, Leonardo Miceli
> <miceli.leonardo at gmail.com> wrote:
> >
> > Does anybody here had any problem with rounding using RSQLite?
> >
> > I have a query which return around 100 thousands records of double
> > precision numeric values.
> >
> > The query returns the numbers with 1 digit precision. But when I run the
> > same query but constrains the number of recorded values returned, by
> > around 30 records, the precision of the the values is the correct 3
> digits!
> >
> > It is not a matter of formatting, I checked it out. It's really a
> > different number returned by the same query only the amount of returned
> > data was set different.
> >
> > Any tips? Something like that had happened to you?
> >
> > By the way, I put the query below just to see Its simplicity...
> >
> >
> >  > library(RSQLite)
> >
> >
> >  > qry1 <- "SELECT * FROM tbFutcotes WHERE Dt >= '1996-01-01' AND
> > FK_tbFutureContracts_PK LIKE '%DI1%'"
> >
> >  > qry2 <- "SELECT * FROM tbFutcotes WHERE Dt >= '2016-02-22' AND
> > FK_tbFutureContracts_PK LIKE '%DI1%'"
> >
> >
> >  > x1 <- dbGetQuery(con, qry1)
> >
> >  > x2 <- dbGetQuery(con, qry2)
> >
>

	[[alternative HTML version deleted]]