Converting H:M:S character variable to numeric/continuous scale (R)

May 30, 2022

I am interested in visualizing twitter sentiment over a given topic per hour, and my variables are stored as follows:

sapply(valence_hour,class)
      Time          day mean_valence            n 
 "character"    "numeric"    "numeric"    "integer"

Here is a data example:

Time          day   mean_valence            n 
23:59:00     19     0.0909090909            3
23:58:00     19     0.0589743590            3
23:57:00     19     0.49743590             3

I then ran the following code for a graph:

ggplot(valence_hour, aes(x = Time, y = mean_valence)) +
  geom_point() +
  geom_line()+
  scale_x_continuous(breaks=seq(1,30,1)) +
  geom_smooth()

However, I keep receiving this error: "Error: Discrete value supplied to continuous scale"

To resolve this issue which I believe is caused by the "Time" variable stored as character, I tried to implement a solution similar to the one here.
I ran the following, which runs without errors but it’s not solving the issue with my "Time" variable as I still receive the error "Discrete value supplied to continuous scale"

valence_hour <-
  time_to_seconds <- function(time) {
  
  parts <- time %>% 
    strsplit(":|\\.") %>% 
    .[[1]] %>% 
    as.numeric
  
  seconds <- parts[1] * 60 * 60 + parts[2] * 60 + parts[3]
  
  seconds
}

time_to_seconds("00:01:53.910")

>Solution :

Here is a way.
Concatenate the current system date and Time, coerce to "POSIXct" and use this new, temp variable for the x-axis. Set the axis labels in a datetime layer.

The warnings are due to the small data set, loess complains about not having enough data points. Don’t worry about it, it will work with bigger data.

library(dplyr, quietly = TRUE)
library(ggplot2, quietly = TRUE)

x <- '
Time          day   mean_valence            n 
23:59:00     19     0.0909090909            3
23:58:00     19     0.0589743590            3
23:57:00     19     0.49743590             3'
valence_hour <- read.table(textConnection(x), header = TRUE)

valence_hour %>%
  mutate(Time = paste(Sys.Date(), Time),
         Time = as.POSIXct(Time)) %>%
  ggplot(aes(Time, mean_valence)) +
  geom_point() +
  geom_line()+
  scale_x_datetime(
    date_breaks = "1 mins",
    date_labels = "%H:%M:%S"
  ) +
  geom_smooth(formula = y ~ x, method = "loess")
#> Warning in simpleLoess(y, x, w, span, degree = degree, parametric =
#> parametric, : span too small. fewer data values than degrees of freedom.
#> Warning in simpleLoess(y, x, w, span, degree = degree, parametric =
#> parametric, : pseudoinverse used at 1.654e+09
#> Warning in simpleLoess(y, x, w, span, degree = degree, parametric =
#> parametric, : neighborhood radius 60.6
#> Warning in simpleLoess(y, x, w, span, degree = degree, parametric =
#> parametric, : reciprocal condition number 0
#> Warning in simpleLoess(y, x, w, span, degree = degree, parametric =
#> parametric, : There are other near singularities as well. 3672.4
#> Warning in predLoess(object$y, object$x, newx = if
#> (is.null(newdata)) object$x else if (is.data.frame(newdata))
#> as.matrix(model.frame(delete.response(terms(object)), : span too small. fewer
#> data values than degrees of freedom.
#> Warning in predLoess(object$y, object$x, newx = if
#> (is.null(newdata)) object$x else if (is.data.frame(newdata))
#> as.matrix(model.frame(delete.response(terms(object)), : pseudoinverse used at
#> 1.654e+09
#> Warning in predLoess(object$y, object$x, newx = if
#> (is.null(newdata)) object$x else if (is.data.frame(newdata))
#> as.matrix(model.frame(delete.response(terms(object)), : neighborhood radius 60.6
#> Warning in predLoess(object$y, object$x, newx = if
#> (is.null(newdata)) object$x else if (is.data.frame(newdata))
#> as.matrix(model.frame(delete.response(terms(object)), : reciprocal condition
#> number 0
#> Warning in predLoess(object$y, object$x, newx = if
#> (is.null(newdata)) object$x else if (is.data.frame(newdata))
#> as.matrix(model.frame(delete.response(terms(object)), : There are other near
#> singularities as well. 3672.4
#> Warning in max(ids, na.rm = TRUE): no non-missing arguments to max; returning
#> -Inf