how to restructure date format to another format to get new columns

user_4529548 0
Viewed 34

I have such dataset

DT=structure(list(date = c("01.01.2021", "01.01.2021", "01.01.2021", 
"01.01.2021", "01.01.2021", "01.01.2021", "01.01.2021", "01.01.2021", 
"01.01.2021", "01.01.2021", "01.01.2021", "01.02.2021", "01.02.2021", 
"01.02.2021", "01.02.2021", "01.02.2021", "01.02.2021", "01.02.2021", 
"01.02.2021", "01.02.2021"), sales_count = c(10L, 4L, 8L, 6L, 
4L, 4L, 4L, 3L, 1L, 5L, 2L, 10L, 5L, 9L, 2L, 8L, 5L, 6L, 8L, 
3L)), class = "data.frame", row.names = c(NA, -20L))

it looks something like this

         date sales_count
1  01.01.2021          10
2  01.01.2021           4
3  01.01.2021           8
4  01.01.2021           6

How can I remake the dataset so that it has exactly this input format

      sales_count    date_time       date   week_num weekday
   1: 1        2016-05-02 00:00:00 2016-05-02  1     Monday
   2: 10       2016-05-02 00:00:00 2016-05-02  1     Monday
   3: 14       2016-05-02 00:00:00 2016-05-02  1     Monday
   4: 15       2016-05-02 00:00:00 2016-05-02  1     Monday
   5: 20       2016-05-02 00:00:00 2016-05-02  1     Monday
data-wrangling dplyr lubridate dataframe r
1 Answers

Using lubridate. I'm not sure you expect different dates in your expected output compared to your original dataframe.

library(lubridate)

tibble(DT) %>% 
  mutate(date = mdy(date),
         date_time = ymd_hms(format(date, "%Y-%m-%d 00:00:00")),
         week_num = week(date),
         weekday = weekdays(date)) %>% 
  select(sales_count, date_time, date, week_num, weekday)

# A tibble: 20 x 5
   sales_count date_time           date       week_num weekday 
         <int> <dttm>              <date>        <dbl> <chr>   
 1          10 2021-01-01 00:00:00 2021-01-01        1 Friday  
 2           4 2021-01-01 00:00:00 2021-01-01        1 Friday  
 3           8 2021-01-01 00:00:00 2021-01-01        1 Friday  
 4           6 2021-01-01 00:00:00 2021-01-01        1 Friday  
 5           4 2021-01-01 00:00:00 2021-01-01        1 Friday  
 6           4 2021-01-01 00:00:00 2021-01-01        1 Friday  
 7           4 2021-01-01 00:00:00 2021-01-01        1 Friday  
 8           3 2021-01-01 00:00:00 2021-01-01        1 Friday  
 9           1 2021-01-01 00:00:00 2021-01-01        1 Friday  
10           5 2021-01-01 00:00:00 2021-01-01        1 Friday  
11           2 2021-01-01 00:00:00 2021-01-01        1 Friday  
12          10 2021-01-02 00:00:00 2021-01-02        1 Saturday
13           5 2021-01-02 00:00:00 2021-01-02        1 Saturday
14           9 2021-01-02 00:00:00 2021-01-02        1 Saturday
15           2 2021-01-02 00:00:00 2021-01-02        1 Saturday
16           8 2021-01-02 00:00:00 2021-01-02        1 Saturday
17           5 2021-01-02 00:00:00 2021-01-02        1 Saturday
18           6 2021-01-02 00:00:00 2021-01-02        1 Saturday
19           8 2021-01-02 00:00:00 2021-01-02        1 Saturday
20           3 2021-01-02 00:00:00 2021-01-02        1 Saturday
user_134606020
Related