0

Let my time series data be

df <- data.frame(timestamp = c('2021-08-11 12:00:00', '2021-08-11 01:00:00', '2021-08-11 02:00:00', '2021-08-11 03:00:00', '2021-08-11 04:00:00', '2021-08-11 05:00:00', '2021-08-11 06:00:00', '2021-08-11 07:00:00', '2021-08-11 08:00:00', '2021-08-11 09:00:00', '2021-08-11 10:00:00', '2021-08-11 11:00:00', '2021-08-11 12:00:00', '2021-08-11 01:00:00', '2021-08-11 02:00:00', '2021-08-11 03:00:00', '2021-08-11 04:00:00', '2021-08-11 05:00:00', '2021-08-11 06:00:00', '2021-08-11 07:00:00', '2021-08-11 08:00:00', '2021-08-11 09:00:00', '2021-08-11 10:00:00', '2021-08-11 11:00:00' ),
value = c(77,190,17,-4,163,158,25,-2,132,119,-35,165,99,-21,199,62,-1,30,123,42,56,-36,123,24))

My requirement data frame

df <- data.frame(timestamp = c('2021-08-11 00:00:00', '2021-08-11 01:00:00', '2021-08-11 02:00:00', '2021-08-11 03:00:00', '2021-08-11 04:00:00', '2021-08-11 05:00:00', '2021-08-11 06:00:00', '2021-08-11 07:00:00', '2021-08-11 08:00:00', '2021-08-11 09:00:00', '2021-08-11 10:00:00', '2021-08-11 11:00:00', '2021-08-11 12:00:00', '2021-08-11 13:00:00', '2021-08-11 14:00:00', '2021-08-11 15:00:00', '2021-08-11 16:00:00', '2021-08-11 17:00:00', '2021-08-11 18:00:00', '2021-08-11 19:00:00', '2021-08-11 20:00:00', '2021-08-11 21:00:00', '2021-08-11 22:00:00', '2021-08-11 23:00:00' ),
                 value = c(77,190,17,-4,163,158,25,-2,132,119,-35,165,99,-21,199,62,-1,30,123,42,56,-36,123,24))

The original data is of the interval per minute or per second.

here the first

df$timestamp[1] = [1] "2021-08-11 12:00:00"

is of AM, i.e. 00 hrs after night.

I want to convert the timestamp column to 24 hours format.

I want to have my timestamp like this

camille
  • 16,432
  • 18
  • 38
  • 60
Avijit Mallick
  • 37
  • 1
  • 8
  • How do you know what's AM or PM? – camille Nov 16 '21 at 17:15
  • Because I know the dataset, It will always generate and start from 00 hrs after night. so it always starts from AM. – Avijit Mallick Nov 16 '21 at 17:21
  • Okay but how do the rest of us know which ones are AM and which are PM? You might know your dataset, but *I* don't know why one `'2021-08-11 01:00:00'` becomes `'2021-08-11 13:00:00'` while another one doesn't. Will you always have every single hour in the data, so you can count through 0–11 and then 12–23 reliably? – camille Nov 16 '21 at 17:30
  • You can take it as surety because whenever the date changes the first data has to start from AM or after 00 hrs. Before PM or noon, you can take it for granted that there will be always data of AM. – Avijit Mallick Nov 16 '21 at 17:47

1 Answers1

1

A tidyverse option.

library(tidyverse)

df %>%
  mutate(id = str_sub(timestamp, 1, 10),
         timestamp = ymd_hms(timestamp)) %>%
  group_by(id) %>%
  mutate(timestamp = date(timestamp)+hours(row_number()-1)) %>%
  ungroup() %>%
  select(-id)
  
# # A tibble: 24 x 2
#     timestamp           value
#     <dttm>              <dbl>
#  1  2021-08-11 00:00:00    77
#  2  2021-08-11 01:00:00   190
#  3  2021-08-11 02:00:00    17
#  4  2021-08-11 03:00:00    -4
#  5  2021-08-11 04:00:00   163
#  6  2021-08-11 05:00:00   158
#  7  2021-08-11 06:00:00    25
#  8  2021-08-11 07:00:00    -2
#  9  2021-08-11 08:00:00   132
# 10  2021-08-11 09:00:00   119
# # … with 14 more rows
rjen
  • 1,938
  • 1
  • 7
  • 19