transpose time to columns from date time rows-CodePudding

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...

CodePudding user response：

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))

CodePudding user response：

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
>