Dplyr filter dataset based on values referenced from another dataset, returns all or no rows

Question

I am having an issue filtering a dataset based on values referenced from another dataset.

I have two datasets. The first dataset, comparison_dt, contains all the comparisons I should make in the form of rows with location1, location2. The second dataset, rain_values_dt, contains the values collected from those locations at different times. My goal is to, for each row in comparison_dt, filter out rows of rain_values_dt collected from location1, filter out rows of rain_values_dt collected from location2, inner join those rows, run a paired t-test, and return the test statistic to a column appended to comparison_dt.

The issue I'm having is that I'm unable to filter rows of rain_values_dt based on the location name referenced from comparison_dt. Asking to filter based on the name stored in row one of the comparison table returns all rows of rain_values_dt. Asking to filter based on the name stored in higher row numbers returns nothing. I would like to only receive rows from the site I am referencing in the filter.


library(data.table)
library(dplyr)

comparison_dt <- data.table(
  location1= c('austin_tx','austin_tx','austin_tx','boston_ma','boston_ma','boston_ma','chicago_il','chicago_il','chicago_il'),
  location2= c('austin_tx','boston_ma','chicago_il','austin_tx','boston_ma','chicago_il','austin_tx','boston_ma','chicago_il'),
  test_statistic= c()
)

rain_values_dt <- data.table(
  location=c('austin_tx','austin_tx','austin_tx','boston_ma','boston_ma','boston_ma','chicago_il','chicago_il','chicago_il'),
  month=c('march','april','may','march','april','may','march','april','may'),
  rainfall=c(1:9)
)

row_n=1

#my intended result, works as expected v
dplyr::filter(rain_values_dt, location == 'austin_tx')

#is pulling the correct name from the comparison table to filter on
comparison_dt[row_n,'location1']

#these are equivalent to each other, so I should be able to substitute, right?
'austin_tx' == comparison_dt[row_n,'location1']

#does not work, returns all values instead of filtering
dplyr::filter(rain_values_dt, location == comparison_dt[row_n,'location1'])

This is a simplification of a larger dataset where not all site comparisons are valid, trials must be matched based on a number of different conditions, and there are an uneven number of trials per site.

This was previously working as expected. I restarted the R session and it no longer works as expected.

I have tried changing the location names in either dataset to character or function type based on the idea that I might have imported my datasets differently. I tried referencing the location column as a vector or in quotes. I tried unloading and reloading dplyr and checking whether R was using the base stats version of filter or the dplyr version. This seems like a simple problem, but I searched this site and the filter() documentation and haven't found an answer for why the function might be behaving this way.

user_3732271 · Answer

The object on the rhs of == is a data.table.

class(comparison_dt[row_n,'location1'])
[1] "data.table" "data.frame"

We need to extract the column as a vector. Either use $ or [[

dplyr::filter(rain_values_dt, location == 
            comparison_dt[row_n,'location1']$location1)
     location month rainfall
1: austin_tx march        1
2: austin_tx april        2
3: austin_tx   may        3

or even unlist to create a vector

dplyr::filter(rain_values_dt, location == 
            unlist(comparison_dt[row_n,'location1']))
    location month rainfall
1: austin_tx march        1
2: austin_tx april        2
3: austin_tx   may        3

Regarding why we are getting all the rows of the dataset - the first element of 'location1' is 'austin_tx' which is also the first element of 'location' from 'rank_values_dt'. Thus, it is a TRUE from ==, which gets recycled

comparison_dt[row_n,'location1']
location1
1: austin_tx

Suppose, if the column value was 'boston_ma' as first element, it will return 0 rows because the elementwise comparison with the first element comparison returns FALSE

dplyr::filter(rain_values_dt, location == data.table(location1 = 'boston_ma'))
Empty data.table (0 rows and 3 cols): location,month,rainfall
dplyr::filter(rain_values_dt, location == comparison_dt[row_n,'location1'])
     location month rainfall
1:  austin_tx march        1
2:  austin_tx april        2
3:  austin_tx   may        3
4:  boston_ma march        4
5:  boston_ma april        5
6:  boston_ma   may        6
7: chicago_il march        7
8: chicago_il april        8
9: chicago_il   may        9

i.e. if we take the expression out of the filter, it becomes more clear - single TRUE/FALSE output, which is recycled

rain_values_dt$location == data.table(location1 = 'boston_ma')
     location1
[1,]     FALSE
rain_values_dt$location == comparison_dt[row_n,'location1']
     location1
[1,]      TRUE

For data.frame/data.table/tibble, the unit is a column. Thus, the length of the comparison_dt[, 'location1'] is 1. The elementwise comparison behavior is more pronounced if we add more rows to 'comparison_dt'

rain_values_dt$location == comparison_dt[3:5,'location1']
     location1
[1,]      TRUE
[2,]     FALSE
[3,]     FALSE

i.e. the first element is TRUE, because it compares the first element of 'location' from rain_values_dt' with 3rd element of comparison, but the next element is FALSE because it is 'boston_ma' compared with 2nd element of rain_values_dt$location which is again 'austin_tx'

Dplyr filter dataset based on values referenced from another dataset, returns all or no rows

1 Answers