Geek Answers Handbook

The problem with filtering data.table for dates - works with a fixed date, but not with a variable

I want to filter the data.table for dates - given start and end dates, I want to have all rows that contain a given date.

Now my problem is: it works when I use a fixed date, but it returns an empty table when I want to save the date in an external variable. Any clues?

dt = data.table(begin=as.Date('2014-01-01'):as.Date('2014-01-10'),
                end=as.Date('2014-01-01'):as.Date('2014-01-10')+c(1,1,1,2:8),
                x=c('A','B','C','D','E','J','J','J','J','J'))
dt[,`:=`(begin_idate=as.IDate(begin, origin='1970-1-1'),
         end_idate=as.IDate(end, origin= '1970-1-1'))]
dt[as.Date('2014-01-09')>begin_idate  & as.Date('2014-01-09')<=end_idate ] # works
x=as.Date('2014-01-09')
dt[x>begin_idate  & x<=end_idate ] #doesnt' work - empty data.table
+4
source share
1 answer

This is because one of your columns data.tableis called x. Because it data.tableevaluates its first argument (the one you are using), it first searches xas a column reference. Thus, it is actually x>begin_idateinterpreted as

dt$x > dt$begin_idate

.

, , dt.

xx = as.Date('2014-01-09')
dt[xx>begin_idate  & xx<=end_idate ]

:

> dt[xx>begin_idate  & xx<=end_idate ]
   begin   end x begin_idate  end_idate
1: 16076 16080 J  2014-01-06 2014-01-10
2: 16077 16082 J  2014-01-07 2014-01-12
3: 16078 16084 J  2014-01-08 2014-01-14

:

all.equal(dt[as.Date('2014-01-09')>begin_idate  & as.Date('2014-01-09')<=end_idate ],
          dt[xx>begin_idate  & xx<=end_idate ]) # TRUE
+7

More articles:


All Articles