How can I convert Ymd date to Ym format?

[cinek047]

How can I convert Ymd date to Ym format?

for ex. I have the date format "2000-01-15" and I would like to change it to "2000-01"? I tried with
as.Date(x, "%Y-%m") function but it doesn't work. Could someone advise?

[CALUM_POLWART]

as.Date converts text to as date. It's not clear if your data is already a date or is text (character).

2021-10 is not a date. So you may need to convert text to a date then convert it back to text.

format(
   as.Date(
     "2021-10-01", 
     "%Y-%m-%d" #Not strictly required in this case
    ), 
  "%Y-%m"
)

[cinek047]

I tried both . The first text to date but didn't work. Currently, it's in date format. So I would like to have it in year-month format. Is there any chance to convert it?

[CALUM_POLWART]

If it is a date then you should be able to format it

format(x , "%Y-%m")

[cinek047]

It gets formatted in a console only. Data frame/table still has YMD format. Anyway thank you for your help Calum.

[FJCC]

You need to store the result of the function in the data frame. If your data frame is named DF and the column containing the dates is named DATE, then you can make a new column with the date as Y-m with

DF$NewDATE <- format(DF$DATE , "%Y-%m")

You could also overwrite the original DATE column instead of making a new column. I would recommend against that because the values returned by format are characters, not dates, so you will lose the ability to manipulate the dates numerically.

[cinek047]

I tried this way as well, however, the column changing from the Date format to Character.I also tried with as:

DF$NewDATE <- format(as.Date(DF$DATE , "%Y-%m"))

but then all values in the column changing to NA's

[FJCC]

@cinek047

however, the column changing from the Date format to Character

Yes, the format function returns characters. A date cannot have only a year and a month. What are you trying to achieve that you need to have the date as Y-m? Depending on what that is, there is probably another solution.

[cinek047]

I need to analyse sales base on the date/month, so I try to figure out how to do it as it's my 1st project. I just think that maybe characters convert to numeric values may work for this purpose.

[FJCC]

Here is an example of how I would calculate sales grouped by year and month. I leave the Date column as a date, I make columns to label each row with its month and year, and I group by those new columns to calculate the average sales. Is that the sort of thin you need to do?

library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#> 
#>     date, intersect, setdiff, union
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
#Invent Data
StartDate <- ymd("2020-01-01")
EndDate <- ymd("2021-08-31")
DF <- data.frame(Date = seq.Date(from = StartDate,to = EndDate,by=14),
                 Sales=runif(n=44,min=1000,max=5000))
head(DF)
#>         Date    Sales
#> 1 2020-01-01 4801.526
#> 2 2020-01-15 4575.294
#> 3 2020-01-29 4302.132
#> 4 2020-02-12 1727.589
#> 5 2020-02-26 3844.938
#> 6 2020-03-11 3539.066
#Label rows with month and year
DF <- DF %>% mutate(Month = month(Date), Year = year(Date))
head(DF)
#>         Date    Sales Month Year
#> 1 2020-01-01 4801.526     1 2020
#> 2 2020-01-15 4575.294     1 2020
#> 3 2020-01-29 4302.132     1 2020
#> 4 2020-02-12 1727.589     2 2020
#> 5 2020-02-26 3844.938     2 2020
#> 6 2020-03-11 3539.066     3 2020
#Calculate average sales for each year and month
STATS <- DF %>% group_by(Year,Month) %>% 
  summarize(Avg = mean(Sales))
#> `summarise()` has grouped output by 'Year'. You can override using the `.groups` argument.
STATS
#> # A tibble: 20 x 3
#> # Groups:   Year [2]
#>     Year Month   Avg
#>    <dbl> <dbl> <dbl>
#>  1  2020     1 4560.
#>  2  2020     2 2786.
#>  3  2020     3 3042.
#>  4  2020     4 4205.
#>  5  2020     5 2746.
#>  6  2020     6 3617.
#>  7  2020     7 2081.
#>  8  2020     8 3388.
#>  9  2020     9 2067.
#> 10  2020    10 3131.
#> 11  2020    11 2885.
#> 12  2020    12 3209.
#> 13  2021     1 3300.
#> 14  2021     2 3324.
#> 15  2021     3 3424.
#> 16  2021     4 3200.
#> 17  2021     5 3520.
#> 18  2021     6 3028.
#> 19  2021     7 1420.
#> 20  2021     8 4292.

^{Created on 2021-10-09 by the reprex package (v2.0.1)}

[cinek047]

yes, that would work for me.Thank you