Support non-vectorized syntax in @where

[nalimilan]

Since @where operates by row, I would find it both natural and practical to allow using non-vectorized operators, like == instead of .==. That would be particularly useful for in, which is currently not vectorized and might never be (JuliaLang/julia#5212). For example, it would be great to be able to write:

using DataFrames, DataFramesMeta, RDatasets
iris = dataset("datasets", "iris")
@where iris :Species in ("setosa", "virginica")

instead of as currently:

@where iris (:Species .== "setosa") | (:Species .== "virginica")

Do you think this would be technically possible? (If so, this idea could also be applied to other macros.)

[tshort]

Here's one way:

 macro wherex(df, ex)
     df = esc(df)
     ex = esc(ex)
     quote
        df2 = transform($df, _idx = true)
        $df[(@byrow! df2  :_idx = $ex)[:_idx],:]
     end
 end
 using DataFrames, DataFramesMeta, RDatasets
 iris = dataset("datasets", "iris")
 i2 = @wherex iris :Species in ("setosa", "virginica")

It's definitely worth thinking about expanding this idea. @transform is an interesting one. Somehow we need to have a way to specify the type of the column to be created (or we have to try the first one and use that type for the rest of the entries).

[nalimilan]

Interesting. Though it doesn't look like the most efficient way of doing this: it would be good to avoid creating a temporary data frame.

Also, I wonder whether the non-vectorized form shouldn't be the recommended one (or even the only supported one): vectorized expressions require storing temporaries when combining operators, which is inefficient.

[tshort]

Creating a temporary DataFrame is relatively inexpensive, but you could get around it with more effort.

Another issue with byrow operations is that the following won't work.

@where(df, :colA .> mean(:colB))

Supporting that requires something like Devectorize.jl. Maybe embedding @devec would do the trick.

[Ismael-VC]

I came up with this:

function Base.in{T}(xs::PooledDataArray{T}, ys::AbstractArray{T})
    Bool[any(x in ys) for x in xs]
end

Of course it would need more methods for when xs is a DataArray, and when ys is a Tuple or a Range (or use Unions?).

Some micro benchmarks (after JIT warm up):

julia> @time a = @where iris :Species in ["setosa", "virginica"];
  0.004165 seconds (1.11 k allocations: 56.937 KB)

julia> @time b = @where iris (:Species .== "setosa") | (:Species .== "virginica");
  0.004514 seconds (1.55 k allocations: 84.269 KB)

julia> @time c = @wherex iris :Species in ["setosa", "virginica"];
  0.006636 seconds (2.34 k allocations: 117.952 KB)

julia> a == b == c
true

[nalimilan]

@Ismael-VC The problem with this method for in is that you wouldn't be able to check whether an array is present in another as one of the elements, e.g. [1,2] in Any[[1,2], [3,4]]. This would make in unpredictable depending on the element type of the arrays. That's why we would need a different operator (JuliaLang/julia#5212).

Also, with complex conditions, a non-vectorized form will always be faster because the vectorized form creates temporary arrays for each one.

@tshort Operations relying on aggregate values would indeed no longer be possible with my proposal. Not sure what can be done about it (except having two different forms of @where).

[Ismael-VC]

@nalimilan what about using small in (\smallin: ∊):

julia> function ∊{T}(xs::PooledDataArray{T}, ys::AbstractArray{T})
           Bool[any(x in ys) for x in xs]
       end
∊ (generic function with 1 method)

julia> @where iris :Species ∊ ["setosa", "virginica"];

• https://en.wiktionary.org/wiki/∊
  1. Alternative form of ∈ ‎(“element of”)

[nalimilan]

@Ismael-VC I don't think it's a good idea. The two operators are easily confused, and nothing in the definition of "small in" implies it's vectorized. Anyway, the present issue is not about vectorizing in, even if that question is of course related; let's discuss in in the other issues.

[Ismael-VC]

I would find it both natural and practical to allow using non-vectorized operators, like == instead of .==

---

The two operators are easily confused, and nothing in the definition of "small in" implies it's vectorized.

@nalimilan I think you are contradicting yourself in those statements, also I thought that the idea would be for documentation to explain that and yes I just focused on in because of the example, obviously this is still way out of my league, perhaps some day. 😄

[nalimilan]

@Ismael-VC Sorry, I don't see where I'm contradicting myself. Here I propose to work row-wise, and use only non-vectorized operators. You proposed to add a new vectorized operator which looks closely like the non-vectorized one.

[Ismael-VC]

Oh yeah you are right. I missed the point of @where working by row, I got this all wrong, since the beginning then, well at least now I've learn a lot in the process, thanks!

[bramtayl]

Integrating conditional deletion of rows into @byrow! might work (like how if statements work in SAS).

[davidagold]

EDIT: Nevermind, posted this to the wrong issue.

After JuliaLang/julia#22089, this can be "solved" with

julia> a = [1, 2]; b = [1, 2, 3];

julia> ∈′(a,b) = in.(a, [b])
∈′ (generic function with 1 method)

julia> a ∈′ b
2-element BitArray{1}:
 true
 true

[pdeffebach]

Closed in favor of #165