R Date Internal integer storage Has "L" - Can it be removed?
I have an API that returns
str(test)
'data.frame': 35 obs. of 2 variables:
$ date : Date, format: "2017-05-23" "2017-05-24" "2017-05-25" "2017-05-26" ...
$ PX_LAST: num 52.3 52.1 49.8 50.6 50.5 ...
However, looking more closely at the internal storage of a date variable ... dates are stored with an "L" appended to the end.
dput(test)
structure(list(date = structure(c(17309L, 17310L, 17311L, 17312L,
17316L, 17317L, 17318L, 17319L, 17322L, 17323L, 17324L, 17325L,
17326L, 17329L, 17330L, 17331L, 17332L, 17333L, 17336L, 17337L,
17338L, 17339L, 17340L, 17343L, 17344L, 17345L, 17346L, 17347L,
17350L, 17352L, 17353L, 17354L, 17357L, 17358L, 17359L), class = "Date"),
PX_LAST = c(52.3, 52.09, 49.76, 50.59, 50.48, 49.12, 49.22,
48.51, 48.22, 48.88, 46.87, 46.85, 46.97, 47.15, 47.45, 45.82,
45.67, 45.94, 45.46, 44.58, 43.51, 43.74, 44.08, 44.4, 45.31,
45.81, 46.02, 47.05, 48.01, 46.1, 46.4, 45.07, 45.32, 45.92,
46.64)), class = "data.frame", .Names = c("date", "PX_LAST"
), row.names = c(NA, 35L))
Is it possible to change the way the date is stored to get r = get rid of the L at the end? This extra L is causing an error when I try to write data to the sql database.
UPDATE
Thanks for the comments, rich, db and Marius. Here is the SQL I am using to write to the database.
OK in the spirit of trying to reproduce this very convoluted problem. I did it. Here's a single row data table structure that gives a limited data type problem:
> oneLine <- flatFrame[1, 1-4]
> str(oneLine)
'data.frame': 1 obs. of 4 variables:
$ Ticker : Factor w/ 1 level "CLU7 Comdty": 1
$ date : Date, format: "2017-05-18"
$ VOLUME : num 44674
$ OPEN_INT: int 188049
Then I try to write that one row to the database in a new table and I get an attribute violation error.
dbWriteTable(con, "new7", oneLine, verbose=TRUE, overwrite=TRUE)
Error in result_insert_dataframe(rs@ptr, values) :
nanodbc/nanodbc.cpp:1791: 07006: [Microsoft][ODBC Driver 13 for SQL
Server]Restricted data type attribute violation
So now I'm trying to clone a dataframe:
rep_data <- data.frame(Ticker=as.factor("CLU7 Comdty"), date = as.Date("2017-05-18"), VOLUME=44674, OPEN_INT =as.integer(188049))
> str(rep_data)
'data.frame': 1 obs. of 4 variables:
$ Ticker : Factor w/ 1 level "CLU7 Comdty": 1
$ date : Date, format: "2017-05-18"
$ VOLUME : num 44674
$ OPEN_INT: int 188049
Exactly the same .... But this write function does not throw errors.
dbWriteTable(con, "new8", rep_data, verbose=TRUE, overwrite=TRUE)
What's happening? Is there any phantom attribute on the data table that I can't see?
Someone from github suggested that I am using the command dput()
to view the internal data structure.
dput(oneLine)
structure(list(Ticker = structure(1L, .Label = "CLU7 Comdty", class = "factor"),
date = structure(17304L, class = "Date"), VOLUME = 44674,
OPEN_INT = 188049L), .Names = c("Ticker", "date", "VOLUME",
"OPEN_INT"), row.names = 1L, class = "data.frame")
dput(rep_data)
structure(list(Ticker = structure(1L, .Label = "CLU7 Comdty", class = "factor"),
date = structure(17304, class = "Date"), VOLUME = 44674,
OPEN_INT = 188049L), .Names = c("Ticker", "date", "VOLUME",
"OPEN_INT"), row.names = c(NA, -1L), class = "data.frame")
The significant difference in the date structure is that in what failed oneLine, the internally stored date 17304 L has an "L" appended to it. The replicated dataset does not work.
source to share
Well, it seems like the function needs the internal representation to Date
be a number instead of an integer; if so, we just need to convert existing integers to numeric and then to Dates.
Note that the problem is not that "L" exists; that as soon as an integer is outputted for display to tell you that it is an integer, it is not used at all. So if your other function is not parsing the output from dput
(very unlikely), the problem is converting to an integer without removing L.
I'll check the regular view first; it uses numerical values, not integers (note no L).
> dput(as.Date("2017-07-01"))
structure(17348, class = "Date")
Now I will create a version with a whole series, it seems to work fine for this purpose, but apparently not for you.
> (foo <- structure(17348L, class="Date"))
[1] "2017-07-01"
> dput(foo)
structure(17348L, class = "Date")
So here's how you would convert it to numeric and then back to Date. R start date 1970-01-01 but instead of hardcoding what I did 0 to be Date.
> (foo2 <- as.Date(as.numeric(foo), origin=structure(0, class="Date")))
[1] "2017-07-01"
> dput(foo2)
structure(17348, class = "Date")
I bet if you do this on your date column it will work.
Interestingly, it's just a rework since the new date doesn't change to a numeric value.
> dput(as.Date(foo, origin="1970-01-01"))
structure(17348L, class = "Date")
source to share