I have a data frame looking like this:
Date Species 00:00 02:00 04:00 06:00 08:00 10:00 12:00 14:00 16:00 18:00 20:00 22:00
01.05.2019 A 0 0 0 0 0 2 5 8 0 0 0 0
02.05.2019 A 0 0 0 8 0 4 3 0 0 0 0 0
The numbers are bihourly values that were recorded during a duration of 2 hours, e.g. between 0 am and 2 am.
For the R package that I want to use the table needs to look like this:
Species from to value
A 01.05.2019 00:00 01.05.2019 02:00 0
A 01.05.2019 02:00 01.05.2019 04:00 0
A 01.05.2019 04:00 01.05.2019 06:00 0
A 01.05.2019 06:00 01.05.2019 08:00 0
A 01.05.2019 08:00 01.05.2019 10:00 0
A 01.05.2019 10:00 01.05.2019 12:00 2
A 01.05.2019 12:00 01.05.2019 14:00 5
A 01.05.2019 14:00 01.05.2019 16:00 8
A 01.05.2019 16:00 01.05.2019 18:00 0
A 01.05.2019 18:00 01.05.2019 20:00 0
A 01.05.2019 20:00 01.05.2019 22:00 0
A 01.05.2019 22:00 02.05.2019 00:00 0
A 02.05.2019 00:00 01.05.2019 02:00 0
A 02.05.2019 02:00 01.05.2019 04:00 0
Basically the same as Ronak Shah, but using lubridate for the date-time-part:
library(tidyr)
library(dplyr)
library(lubridate)
df %>%
pivot_longer(cols=-c("Date", "Species"), names_to="Time") %>%
mutate(From = dmy(Date) + hm(Time),
To = dmy(Date) + hm(Time) + hm("02:00")) %>%
select(Species, From, To, value)
which returns
# A tibble: 24 x 4
Species From To value
<chr> <dttm> <dttm> <dbl>
1 A 2019-05-01 00:00:00 2019-05-01 02:00:00 0
2 A 2019-05-01 02:00:00 2019-05-01 04:00:00 0
3 A 2019-05-01 04:00:00 2019-05-01 06:00:00 0
4 A 2019-05-01 06:00:00 2019-05-01 08:00:00 0
5 A 2019-05-01 08:00:00 2019-05-01 10:00:00 0
6 A 2019-05-01 10:00:00 2019-05-01 12:00:00 2
7 A 2019-05-01 12:00:00 2019-05-01 14:00:00 5
8 A 2019-05-01 14:00:00 2019-05-01 16:00:00 8
9 A 2019-05-01 16:00:00 2019-05-01 18:00:00 0
10 A 2019-05-01 18:00:00 2019-05-01 20:00:00 0
# ... with 14 more rows