transpose time to columns from date time rows

user_16872494 0
Viewed 83

I have a following data table.

Timestamp Energy_KWh
2021-01-01 00:00:00 10
2021-01-01 00:30:00 11
2021-01-01 01:00:00 9
2021-01-01 01:30:00 8
2021-01-01 02:00:00 9
2021-01-01 02:30:00 13
2021-01-02 00:00:00 16
2021-01-02 00:30:00 19
2021-01-02 01:00:00 90
2021-01-02 01:30:00 81
2021-01-02 02:00:00 1
2021-01-02 02:30:00 11

I want to change time part into columns.. like this..

DATE 00:00:00 00:30:00 01:00:00 01:30:00 02:00:00 02:30:00 03:00:00 03:30:00...
2021-01-01 10 11 9 8
2021-01-02 16 19 90 81

The time goes on for all day.. there should be 48 columns (24 hours x 2)

I would be thankful if anyone can help with this issue.

Thanks...

dataframe r datetime
2 Answers

We may split the 'Timestamp' at the space (with separate), create two columns ('Date', 'Time') and use pivot_wider for reshaping

library(dplyr)
library(tidyr)
df1 %>% 
   separate(Timestamp, into = c("Date", "Time"), sep="\\s+") %>% 
   pivot_wider(names_from = Time, values_from = Energy_KWh)
# A tibble: 2 × 7
  Date       `00:00:00` `00:30:00` `01:00:00` `01:30:00` `02:00:00` `02:30:00`
  <chr>           <int>      <int>      <int>      <int>      <int>      <int>
1 2021-01-01         10         11          9          8          9         13
2 2021-01-02         16         19         90         81          1         11

data

df1 <- structure(list(Timestamp = c("2021-01-01 00:00:00", "2021-01-01 00:30:00", 
"2021-01-01 01:00:00", "2021-01-01 01:30:00", "2021-01-01 02:00:00", 
"2021-01-01 02:30:00", "2021-01-02 00:00:00", "2021-01-02 00:30:00", 
"2021-01-02 01:00:00", "2021-01-02 01:30:00", "2021-01-02 02:00:00", 
"2021-01-02 02:30:00"), Energy_KWh = c(10L, 11L, 9L, 8L, 9L, 
13L, 16L, 19L, 90L, 81L, 1L, 11L)), class = "data.frame", row.names = c(NA, 
-12L))
user_37322710

Here is an alternative approach, allthough I would prefer akrun's approach!

library(dplyr)
library(tidyr)
library(lubridate)
library(hms)
df %>% 
  mutate(Timestamp = ymd_hms(Timestamp)) %>% 
  mutate(date = as_date(Timestamp),
         time = as_hms(Timestamp), .keep="unused"
         ) %>% 
  pivot_wider(names_from = time, values_from = Energy_KWh)

date       `00:00:00` `00:30:00` `01:00:00` `01:30:00` `02:00:00` `02:30:00`
  <date>          <int>      <int>      <int>      <int>      <int>      <int>
1 2021-01-01         10         11          9          8          9         13
2 2021-01-02         16         19         90         81          1         11
>
user_133216470
Related