-1

I want to take a column in excel with 

1   1/1/19 5:20
2   1/1/19 5:30 
3   1/1/19 5:38 
4   1/1/19 5:52 
5   1/1/19 6:00 
6   1/1/19 6:00
7   1/1/19 6:00 
8   1/1/19 6:15

into a duration column with the first number being 

0 
.167 
.3
.53
.66
.66
.66
.91

as 10 minutes that have passed since my first time I have a whole column i want to convert which goes onto different days and different months and then when we reach the bottom i want it to say 90000 hours or however many hours that have passed since 5:20 am on the 1st

Arica Christensen
  • 123
  • 1
  • 10
  • 1
    Please make this question *reproducible*. This includes sample code (including listing non-base R packages), sample *unambiguous* data (e.g., `dput(head(x))` or `data.frame(x=...,y=...)`), and expected output. Refs: https://stackoverflow.com/questions/5963269, https://stackoverflow.com/help/mcve, and https://stackoverflow.com/tags/r/info. – r2evans Oct 31 '19 at 18:14

1 Answers1

2

A couple of steps. First, some sample data:

dat <- read.csv(header = FALSE, stringsAsFactors = FALSE, text="
1/1/19 5:20
1/1/19 5:30
1/1/19 5:38
1/1/19 5:52
1/1/19 6:00
1/1/19 6:00
1/1/19 6:00
1/1/19 6:15")
dat
#            V1
# 1 1/1/19 5:20
# 2 1/1/19 5:30
# 3 1/1/19 5:38
# 4 1/1/19 5:52
# 5 1/1/19 6:00
# 6 1/1/19 6:00
# 7 1/1/19 6:00
# 8 1/1/19 6:15

Second, convert that to a "proper" POSIXt (timestamp) object:

dat$V1 <- as.POSIXct(dat$V1, format = "%m/%d/%y %H:%M", tz = "UTC")
dat
#                    V1
# 1 2019-01-01 05:20:00
# 2 2019-01-01 05:30:00
# 3 2019-01-01 05:38:00
# 4 2019-01-01 05:52:00
# 5 2019-01-01 06:00:00
# 6 2019-01-01 06:00:00
# 7 2019-01-01 06:00:00
# 8 2019-01-01 06:15:00

Next, find the differences. Note that the differencing might return units other than seconds, so defensive programming might force that:

d <- dat$V1 - dat$V1[1]
d
# Time differences in secs
# [1]    0  600 1080 1920 2400 2400 2400 3300

units(d) <- "hours"
d
# Time differences in hours
# [1] 0.0000000 0.1666667 0.3000000 0.5333333 0.6666667 0.6666667 0.6666667
# [8] 0.9166667

or based on @akrun's suggestion, a single-call of

difftime(dat$V1, dat$V1[1], units="hours")
# Time differences in hours
# [1] 0.0000000 0.1666667 0.3000000 0.5333333 0.6666667 0.6666667 0.6666667
# [8] 0.9166667
r2evans
  • 141,215
  • 6
  • 77
  • 149
  • 1
    Another option with `difftime` `library(dplyr);library(lubridate);> dat %>% mutate(V1 = mdy_hm(V1), V2 = difftime(V1, first(V1), unit = 'hour'))` – akrun Oct 31 '19 at 18:31