How do I reshape this table?

user_12063623 0
Viewed 57

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
reshape2 reshape r
2 Answers

Perhaps, you can try :

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(cols = -(1:2), names_to = 'From') %>%
  unite(From, Date, From, sep = " ") %>%
  group_by(Species) %>%
  mutate(To = lead(From)) %>%
  select(Species, From, To, value)


#   Species From             To               value
#   <chr>   <chr>            <chr>            <int>
# 1 A       01.05.2019 00:00 01.05.2019 02:00     0
# 2 A       01.05.2019 02:00 01.05.2019 04:00     0
# 3 A       01.05.2019 04:00 01.05.2019 06:00     0
# 4 A       01.05.2019 06:00 01.05.2019 08:00     0
# 5 A       01.05.2019 08:00 01.05.2019 10:00     0
# 6 A       01.05.2019 10:00 01.05.2019 12:00     2
# 7 A       01.05.2019 12:00 01.05.2019 14:00     5
# 8 A       01.05.2019 14:00 01.05.2019 16:00     8
# 9 A       01.05.2019 16:00 01.05.2019 18:00     0
#10 A       01.05.2019 18:00 01.05.2019 20:00     0
# … with 14 more rows

data

df <- structure(list(Date = c("01.05.2019", "02.05.2019"), Species = c("A", 
"A"), `00:00` = c(0L, 0L), `02:00` = c(0L, 0L), `04:00` = c(0L, 
0L), `06:00` = c(0L, 8L), `08:00` = c(0L, 0L), `10:00` = c(2L, 
4L), `12:00` = c(5L, 3L), `14:00` = c(8L, 0L), `16:00` = c(0L, 
0L), `18:00` = c(0L, 0L), `20:00` = c(0L, 0L), `22:00` = c(0L, 
0L)), class = "data.frame", row.names = c(NA, -2L))
user_39629140

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
user_125052510
Related