Help with Date Variable

tidyverse
#1

Hi,

My Date column is of "Date" type and I want it that way. However, the Date variable shows January values for all years first, then Februray values for all the years, and so on. I would like to have it in the sequesnce of all months in the same year first, then all months in the next year. For example, 2015 -01-01, 2015-02-01, and so on all the way to 2015-12-01 and then similar details for 2016. Can you please help? I have shown a small data sample below. Thank you!

data.frame(
                        Date = c("2015-01-01","2016-01-01","2017-01-01","2018-01-01",
                                 "2019-01-01","2020-01-01","2015-02-01",
                                 "2016-02-01","2017-02-01","2018-02-01","2019-02-01",
                                 "2020-02-01"),
                       Sales = c(768954,
                                 814523,881179,944901,1071196,1093808,738399,
                                 822000,766659,845464,994354,867130)
                )
#>          Date   Sales
#> 1  2015-01-01  768954
#> 2  2016-01-01  814523
#> 3  2017-01-01  881179
#> 4  2018-01-01  944901
#> 5  2019-01-01 1071196
#> 6  2020-01-01 1093808
#> 7  2015-02-01  738399
#> 8  2016-02-01  822000
#> 9  2017-02-01  766659
#> 10 2018-02-01  845464
#> 11 2019-02-01  994354
#> 12 2020-02-01  867130

Created on 2020-07-31 by the reprex package (v0.3.0)

#2

Like this?

library(dplyr)
df %>% 
  mutate(Date = as.Date(Date)) %>% 
  arrange(Date)

   Date         Sales
 1 2015-01-01  768954
 2 2015-02-01  738399
 3 2016-01-01  814523
 4 2016-02-01  822000
 5 2017-01-01  881179
 6 2017-02-01  766659
 7 2018-01-01  944901
 8 2018-02-01  845464
 9 2019-01-01 1071196
10 2019-02-01  994354
11 2020-01-01 1093808
12 2020-02-01  867130
1 Like
#3

In the data.frame shown, the Date variable is a string, not a date. Best way to deal with this would be to use the {lubridate} package and the ymd() function to convert it before sorting with arrange()

library(lubridate)
df %>%
  mutate(Date = ymd(Date)) %>%
  arrange(Date)
#4

Thanks @williaml!
Its strange that arrange works fine after the date is converted with as.Date. Earlier I was trying to use arrange before the Date was converted in its right data type and it wouldn't work.

But thank you!

#5

Thanks @MyKo101! Yes, reprex somehow didn't take the already converted Date type. But thank you so much! Its all good now. I learnt today that arrange on date works better if applied after converting date into its date type. I was applying arrange before its conversion.

Thank you!

#6

Strings are generally not read in as dates. You have to covert them.

Good to have the data frames set as tibbles as you can see what format they are.

e.g.

library(tidyverse)
library(lubridate)
as_tibble(df) %>% 
  head(5)

# A tibble: 5 x 2
  Date         Sales
  <chr>        <dbl>
1 2015-01-01  768954
2 2016-01-01  814523
3 2017-01-01  881179
4 2018-01-01  944901
5 2019-01-01 1071196

Then converted using lubridate as mentioned by @MyKo101

library(tidyverse)
library(lubridate)
as_tibble(df) %>% 
  head(5) %>% 
  mutate(Date = ymd(Date)) 

# A tibble: 5 x 2
  Date         Sales
  <date>       <dbl>
1 2015-01-01  768954
2 2016-01-01  814523
3 2017-01-01  881179
4 2018-01-01  944901
5 2019-01-01 1071196
1 Like
#7

This is great information on setting dataframes as tibbles. Thanks @williaml