Cut day into multiple parts

user_10035626 0
Viewed 66

To find the right aggregation level for my data, I have to split the day into frames of different sizes.

Example data:

da = data.frame(timestamp = c("2016-01-24 01:17:37 GMT" ,"2016-01-24 02:09:41 GMT", "2016-01-24 13:34:35 GMT", "2016-01-24 15:17:56 GMT", "2016-01-24 18:14:55 GMT"))

da
                timestamp
1 2016-01-24 01:17:37 GMT
2 2016-01-24 02:09:41 GMT
3 2016-01-24 13:34:35 GMT
4 2016-01-24 15:17:56 GMT
5 2016-01-24 18:14:55 GMT

For example, I could start cutting the day in 24 parts. Then 0:00 to 1:00 is part 1, 1:00 to 2:00 is part 2 etc.

da2 = data.frame(timestamp = c("2016-01-24 01:17:37 GMT" ,"2016-01-24 02:09:41 GMT", "2016-01-24 13:34:35 GMT", "2016-01-24 15:17:56 GMT", "2016-01-24 18:14:55 GMT"),
                 daypart = c(2, 3, 14, 16, 19))
da2
                timestamp daypart
1 2016-01-24 01:17:37 GMT       2
2 2016-01-24 02:09:41 GMT       3
3 2016-01-24 13:34:35 GMT      14
4 2016-01-24 15:17:56 GMT      16
5 2016-01-24 18:14:55 GMT      19

Or into 48 parts. Then 0:00 to 0:30 is part 1, 0:30 to 1:00 part 2 etc:

da48 = data.frame(timestamp = c("2016-01-24 01:17:37 GMT" ,"2016-01-24 02:09:41 GMT", "2016-01-24 13:34:35 GMT", "2016-01-24 15:17:56 GMT", "2016-01-24 18:14:55 GMT"),
+                  daypart = c(3, 5, 28, 31, 37))
da48
                timestamp daypart
1 2016-01-24 01:17:37 GMT       3
2 2016-01-24 02:09:41 GMT       5
3 2016-01-24 13:34:35 GMT      28
4 2016-01-24 15:17:56 GMT      31
5 2016-01-24 18:14:55 GMT      37

I found this post Pos on how to convert time to categorical variable, which has already helpes, but how can I code this in such a way that I only have to change the number of parts I want to cut the day into?

dplyr transform r timestamp time
2 Answers

Turn timestamp to the POSIXlt object and extract hour of time by the $ symbol.

da$daypart <- as.POSIXlt(da$timestamp)$hour + 1

#                 timestamp daypart
# 1 2016-01-24 01:17:37 GMT       2
# 2 2016-01-24 02:09:41 GMT       3
# 3 2016-01-24 13:34:35 GMT      14
# 4 2016-01-24 15:17:56 GMT      16
# 5 2016-01-24 18:14:55 GMT      19

Edit: Split time into 48 parts

time <- as.POSIXlt(da$timestamp)
da$daypart48 <- time$hour * 2 + (time$min > 30) + 1
da

#                 timestamp daypart daypart48
# 1 2016-01-24 01:17:37 GMT       2         3
# 2 2016-01-24 02:09:41 GMT       3         5
# 3 2016-01-24 13:34:35 GMT      14        28
# 4 2016-01-24 15:17:56 GMT      16        31
# 5 2016-01-24 18:14:55 GMT      19        37
user_100689850

You could strsplit the time at :,

(tmp <- do.call(rbind, 
                strsplit(strftime(as.POSIXct(da$timestamp), "%H:%M:%S"), ":")))
#      [,1] [,2] [,3]
# [1,] "01" "17" "37"
# [2,] "02" "09" "41"
# [3,] "13" "34" "35"
# [4,] "15" "17" "56"
# [5,] "18" "14" "55"

calculate in seconds,

(sec <- apply(tmp, 2, as.numeric) %*% c(60*60, 60, 1))
#       [,1]
# [1,]  4657
# [2,]  7781
# [3,] 48875
# [4,] 55076
# [5,] 65695

and match with daily seconds cut into 24 pieces.

da$daypart <- cut(1:(24*60*60), 24, labels=1:24)[match(sec, 1:(24*60*60))]

Result

da
#              timestamp daypart
# 1 2016-01-24 01:17:37 GMT    2
# 2 2016-01-24 02:09:41 GMT    3
# 3 2016-01-24 13:34:35 GMT   14
# 4 2016-01-24 15:17:56 GMT   16
# 5 2016-01-24 18:14:55 GMT   19
user_65740380
Related