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
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)