Home read xlsx in R with one column about Time. How to delete the date R added additionally?

Questions

read xlsx in R with one column about Time. How to delete the date R added additionally?

May 4, 2022

I have one excel table and would like to use read_xlsx to convert it as an R dataframe.

Originally, in excel, the data looks like this:

dep_time
15:37:48
04:05:14

However, after I use the read_xlsx, the time column is something like below:

dep_time
1899-12-31 15:37:48
1899-12-31 04:05:14

I don’t want to have the date in my future figure plotting. How can I avoid this?

Thanks!

>Solution :

Here are two alternative ways to solve the problem.
They both use an auxiliary function to_times to extract the time part of the column value.

Base R

# sample data
x <- '1899-12-31 15:37:48
1899-12-31 04:05:14'

dep_time <- scan(text = x, what = character(), sep = "\n")
df1 <- data.frame(dep_time)

# code
to_times <- function(x) {
  y <- as.POSIXct(x)
  format(y, "%H:%M:%S")
}

df1$dep_time <- to_times(df1$dep_time)
df1
#>   dep_time
#> 1 15:37:48
#> 2 04:05:14

str(df1)
#> 'data.frame':    2 obs. of  1 variable:
#>  $ dep_time: chr  "15:37:48" "04:05:14"

^{Created on 2022-05-04 by the reprex package (v2.0.1)}

Package `chron`

to_times <- function(x) {
  y <- sub("^[[:digit:]\\-]+ ([[:digit:]\\:]+)$", "\\1", x)
  chron::times(y)
}

df1$dep_time <- to_times(df1$dep_time)
df1
#>   dep_time
#> 1 15:37:48
#> 2 04:05:14

str(df1)
#> 'data.frame':    2 obs. of  1 variable:
#>  $ dep_time: 'times' num  15:37:48 04:05:14
#>   ..- attr(*, "format")= chr "h:m:s"

^{Created on 2022-05-04 by the reprex package (v2.0.1)}