Do not know how to convert "dataframe" to class “POSIXct” on uBuntu 16.04

shiny
, , ,
1

hi guys,

this time I tried to run my shiny application from shiny-server environment that i build on my uBuntu 16.04.
the application code are like this:

library(shiny)
library(reshape2)
library(ggplot2)
library(dplyr)
library(grid)
library(gridExtra)
library(RODBC)

variance <- function(x) var(x)*(length(x)-1)/length(x)

source("./src/ETL_DB_conn.R", encoding = "UTF-8")

ketten_mst<-read.csv("data/欠点マスタ_ECPU.csv")

Seisan_Stop<-read.csv("data/成形作業日報_不良履歴_ストップ抜粋.csv")

SeikeiDaily<-sqlQuery(conn,"select * from kashima.IOT_T_SeikeiDailyReport_PartNo_Detail;")
ServiceTank<-sqlQuery(conn,"select * from kashima.IOT_T_ServiceTank;")

SirakijiBad<-sqlQuery(conn,"select * from dbo.ECPU_TH_SirakijiBadDtl;")
SeihinBad<-sqlQuery(conn,"select * from dbo.ECPU_TH_SeihinBadDtl;")

Seikei_huryo<-sqlQuery(conn,"select * from kashima.IOT_T_SeikeiDailyReport_MoldingDefect;")

ServiceTank<-ServiceTank[,c(1,2,4,5,6,7,8,9,10,11,12,13)]
ServiceTank$測定日<-as.POSIXct(ServiceTank$測定日)

SeikeiDaily<-SeikeiDaily[,c(2,3,4,7,8,9,10,11,12,13)]
SeikeiDaily$成形日<-as.POSIXct(SeikeiDaily$成形日)
SeikeiDaily$タンク<-ifelse(SeikeiDaily$ライン==2,"Z2","Z1")

this code run perfectly on my local, but when I released it on shiny-server
it gives this error:

do not know how to convert 'ServiceTank$測定日' to class “POSIXct”

this is the ServiceTank$測定日 should look like

測定日
2016/04/02
2016/04/04
2016/04/05
2016/04/06
2016/04/08
2016/04/09
2016/04/11
2016/04/12
2016/04/13
2016/04/15
2016/04/16
2016/04/18
2016/04/19
2016/04/20

so my questions are:

  1. why do POSIXct could run perfectly on windows 10 but not on shiny-server. my shiny-server run on ubuntu 16.04.
    some difference between this 2 environment beside OS are on my windows 10 my app connect to DB using SQL server ODBC why on ubuntu it connect through tdsodbc.
  2. I got this error info from my shiny-server log only. I wanted to try and debug it using rstudio on my uBuntu server. but, everytime I try to click on the rstudio GUI it always crash for some reason. do anyone have any good way to debugging application on uBuntu server?

best regards

2

Hi there! I'm not too familiar with Shiny debugging resources, unfortunately, but here's a page to help you get started. I think the staff would probably need to know a bit more about the crash you're experiencing with RStudio in order to help you with that (and debugging the app in RStudio locally is probably the way to go).

If you're otherwise stuck for debugging ideas, you might slip print(class(ServiceTank$測定日)) in to your code just before you do the date conversion to see what type it is beforehand. It's possible that, due to either platform differences or some other problem, your SQL query is bringing the 測定日 column in as some weird type that can't be coerced to POSIXct.

In particular, I think this section of the sqlQuery documentation is relevant—particularly as, IIRC, date and date-time objects in R are essentially numeric underneath:

Where possible sqlGetResults transfers data in binary form: this happens for columns of (ODBC) SQL types double , real , integer and smallint , and for binary SQL types (which are transferred as lists of raw vectors, given class "ODBC_binary" ). All other SQL data types are converted to character strings by the ODBC interface.

This paragraph applies only to SQL data types which are returned by ODBC as character vectors. If when creating the connection (see odbcConnect) DBMSencoding was set to a non-empty value, the character strings are re-encoded. Then if as.is is true for a column, it is returned as a character vector. Otherwise (where detected) date , datetime and timestamp values are converted to the "Date" or "POSIXct" class. (Some drivers seem to confuse times with dates, so times may get converted too. Also, some DBMSs (e.g. Oracle's) idea of date is a date-time.) Remaining cases are converted by R using type.convert . When character data are to be converted to numeric data, the setting of options("dec") is used to map the character used by the ODBC driver in setting decimal points---this is set to a locale-specific value when RODBC is initialized if it is not already set.

When I work with flat files like CSVs, for example, I often use readr over the base functions because they allow me to easily and explicitly set expected types for columns. That helps cut down on a lot of errors where I assumed my data was one type but it was actually another!

1 Like
3

hi Rensa,

thank you for your reply.
after tweaking my code a little bit. my sql query result returning dataset with japanese character column name in it.
those column name are not being encoded correctly and turn makes my dataframe column name go weird.
I've tried to set my db encoding into "UTF-8" when making the connection but it just don't work.

so for now I just re setting my dataframe column name after I got the query result and POSIXct works fine.
do you have any suggestion regarding query result encoding?

as for the debugging problem, I used log4r to write my dataset value so I could confirm whether there's a problem with my data or not. still not close to debugging from Rstudio but at least it give me some info...

4

Yeah, encoding issues come up a lot, especially when you're working across platforms :frowning: I'd have a look at some of the previous threads on this topic.

Unfortunately, debugging an RStudio crash is out of my skillset :frowning: Hopefully one of the staff can help you with that!

5

Are you able to enter RStudio Server's IDE at all? (e.g. it sounds like you can, presumably to start the shiny app?) You might diagnose the crash by sequentially pulling elements out of your shiny app (or building it up) until the crash stops (or starts if rebuilding the app).

A possible lead on this, that error may occur when as.POSIXct is given an object it is not able to deal with, for example, a data.frame in the example below. If it were an encoding or formatting issue, I think you'd receive the "character string is not in a standard unambiguous format" error.

So, this might error might have to do with shiny-sever having issues connecting to your DB, and not so much a windows vs. Linux thing.

as.POSIXct("2016/04/20")
#> [1] "2016-04-20 BST"
as.POSIXct("測定日")
#> Error in as.POSIXlt.character(x, tz, ...): character string is not in a standard unambiguous format

some_unknown_object <- data.frame(a = c(1,2,3,3))
as.POSIXct(some_unknown_object)
#> Error in as.POSIXct.default(some_unknown_object): do not know how to convert 'some_unknown_object' to class "POSIXct"

Created on 2018-08-13 by the reprex package (v0.2.0.9000).

In your lines of code, you might consider applying as.POSIXct to ServiceTank$測定日 via a tidyverse select and mutate_all operation.

ServiceTank$測定日 <- ServiceTank %>%
  filter(...Columns...) %>%
  mutate_all(as.POSIXct)

I think this will give a more useful error message.