0

This is the sample of my dataset:

> sample
        ORDER_DATE   SHIPMENT_DATE
 1:     2021-08-01    2022-12-31
 2:     2021-08-01    2024-06-30
 3:     2021-08-01    2022-12-31
 4:     2021-08-01    2022-12-31
 5:     2021-08-01    2021-08-31
 6:     2021-08-01    2024-06-30
 7:     2021-08-01    2021-08-31
 8:     2021-08-01    2021-08-31
 9:     2021-08-01    2021-08-31
10:     2021-08-01    2021-08-31

My goal is to subtract one day from SHIPMENT_DATE when it is the same as ORDER_DATE using data.table package. The problem is that whatever I try, the format of ORDER_DATE changes to numeric.

For example

>   sample[, SHIPMENT_DATE := ifelse(SHIPMENT_DATE==ORDER_DATE, SHIPMENT_DATE-1, SHIPMENT_DATE)]
> head(sample$SHIPMENT_DATE)
[1] 19357 19904 19357 19357 18870 19904

Then, when I try to convert it back to date, the values in SHIPMENT_DATEcolumn, which should have been changed to 2021-07-31 are 2021-08-31.

This problem also arose when I tried to create a reproductible example:

> dput(sample)
structure(list(ORDER_DATE = structure(c(18840, 18840, 18840, 
18840, 18840, 18840, 18840, 18840, 18840, 18840), class = "Date"), 
    SHIPMENT_DATE = structure(c(19357, 19904, 19357, 19357, 18870, 
    19904, 18870, 18870, 18870, 18870), class = "Date")), row.names = c(NA, 
-10L), class = c("data.table", "data.frame"), .internal.selfref = <pointer: 0x37dead8>, .Names = c("ORDER_DATE", 
"SHIPMENT_DATE"))
Julia
  • 241
  • 1
  • 8
  • I think the issue is the `ifelse` - not data.table as such. I think you can avoid it like: `dt[SHIPMENT_DATE == ORDER_DATE, SHIPMENT_DATE := SHIPMENT_DATE - 1]` - also see https://stackoverflow.com/questions/6668963/how-to-prevent-ifelse-from-turning-date-objects-into-numeric-objects – thelatemail Aug 25 '21 at 05:05
  • try using as.Date while subtracting e.g. `as.Date(SHIPMENT_DATE -1)` – Ahmar Shoeb Aug 25 '21 at 05:06
  • Also related - https://stackoverflow.com/questions/24536771/conditionally-replacing-column-values-with-data-table – thelatemail Aug 25 '21 at 05:10
  • 2
    Since you are using `data.table` use `fifelse` instead of `ifelse`. – Ronak Shah Aug 25 '21 at 05:16

0 Answers0