Importing elapsed time (HH:MM:SS) from excel into R

user_7093643 0
Viewed 596

I'm an R beginner. I have data in an excel workbook which includes some variables containing elapsed time stored as HH:MM:SS. They currently import as dates and times in R, as this is how excel appears to store the underlying data. I want to import these variables into R as durations (in seconds) when I read the sheet in. I've been reading about lubridate but can't figure out how to tell R to import these columns as durations.

Data looks like this:

var1   var2   var3   time1     time2
blue   yes    5      00:00:05  00:00:09
red    no     7      00:04:02  00:05:04
blue   no     9      00:01:06  00:02:05

Imported times look like

1899-12-31 00:00:05

I've read quite a few threads and webpages but I can't seem to find an example that I can recognise as being relevant for my issue. Theres probably a very simple way to do this that I've overlooked. Any help is much appreciated.

Edit: Should have stated I'm using readxl and get this issue.

library(readxl)
df <- read_excel("data.xlsx", sheet = "sheet1")

When I do this the HH:MM:SS columns display like this:

1899-12-31 00:00:05
lubridate r excel time
3 Answers

Got help and came up with this solution:

library(tidyverse)
library(readxl)
library(lubridate)

data.df <- read_excel("data.xlsx", sheet = "sheet1")

# Create new var and format to H:M:S (remove date)
data.df$newvar=format(data.df$time1,format="%H:%M:%S")

# Convert time to minutes and seconds with hms
data.df$newvar <- lubridate::hms(data.df$newvar)

# Convert to total seconds
data.df$newvar <- period_to_seconds(data.df$newvar)
user_70936430

Please try this approach with xlsx package:

library(xlsx)
#Code
data <- read.xlsx('Myfile.xlsx','Sheet1',colClasses = 'character')

When loading to R you will get this:

enter image description here

user_20808480

There is another package openxlsx

library("openxlsx")
    
df <- read.xlsx('my_file.xlsx', sheet = 1, startRow = 1, colNames = TRUE) 
df

  var1 var2 var3    time1    time2
1 blue  yes    5 00:00:05 00:00:09
2  red   no    7 00:04:02 00:05:04
3 blue   no    9 00:01:06 00:02:05
user_36515290
Related