0

I am trying to convert this large dataset date column, which is the the format (YYYY/MM/DD), I am looking to simply just convert any observation within the 'date' variable to the first day of that corresponding month.(2018/02/03 -> 2018/02/01) I'm am very new with data table, so apologies if this a simple question. Here is sample dput() of my dataset.

structure(list(quantity = c(NA_real_, NA_real_, NA_real_, NA_real_, 
 NA_real_, NA_real_), price_mid = c(NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_), value_mid = c(100, 241.85, 241.85, 
241.85, 241.85, 241.85), currency_price = c("GBP", "GBP", "GBP", 
"GBP", "GBP", "GBP"), currency_value = c("GBP", "GBP", "GBP", 
"GBP", "GBP", "GBP"), date = structure(c(13515L, 13516L, 13517L, 
13518L, 13521L, 13522L), class = "Date")), class = c("data.table", 
 "data.frame"), row.names = c(NA, -6L), .internal.selfref = <pointer: 0x15f28c0>)

Let me know if any edits on the question is needed. Thanks

Henrik
  • 65,555
  • 14
  • 143
  • 159
Tykid15
  • 61
  • 5
  • For your 'Monthly' format, see also `zoo::yearmon`, as described e.g. here: [Format dates to Month-Year while keeping class Date](https://stackoverflow.com/questions/11160445/format-dates-to-month-year-while-keeping-class-date) – Henrik Jun 11 '20 at 12:11

1 Answers1

1

you can use the format function like this

data$date <- format(data$date,format  = "%Y/%m/1")


> data
   quantity price_mid value_mid currency_price currency_value      date
1:       NA        NA    100.00            GBP            GBP 2007/01/1
2:       NA        NA    241.85            GBP            GBP 2007/01/1
3:       NA        NA    241.85            GBP            GBP 2007/01/1
4:       NA        NA    241.85            GBP            GBP 2007/01/1
5:       NA        NA    241.85            GBP            GBP 2007/01/1
6:       NA        NA    241.85            GBP            GBP 2007/01/1

If you prefer you could just drop the day entirely, It may be more representative.

data$date <- format(data$date,format  = "%Y/%m")

> data
   quantity price_mid value_mid currency_price currency_value    date
1:       NA        NA    100.00            GBP            GBP 2007/01
2:       NA        NA    241.85            GBP            GBP 2007/01
3:       NA        NA    241.85            GBP            GBP 2007/01
4:       NA        NA    241.85            GBP            GBP 2007/01
5:       NA        NA    241.85            GBP            GBP 2007/01
6:       NA        NA    241.85            GBP            GBP 2007/01
Daniel O
  • 4,258
  • 6
  • 20