# How to perform multi-columns selection and/or !MemberQ on large lists with speed?

This question (2 subquestions) is connected to How to speed up Select with MemberQ for large lists?

Q1: I would like to have an alternate version of the form below to do a multi-columns selection on large data sets. Speed is still very important. I tried to do ...&[list1, list2, {1,3}, {2,3}] and With[{t = Transpose[#1][[#3,#4]]... but I wasn't successful

Q2: Also, I would like to use the form for a pattern similar to ! MemberQ. I tried ...,False]]] but nothing happened. I am kind of clueless how I should go about this one..

Form: With[{t = Transpose[#1][[#3]], t2 = Transpose[#2][[#4]]}, Pick[#1, Replace[t, Dispatch[ Thread[Rule[Intersection[t, t2], True]]], {1}]]] &[list1, list2, 2, 3]

Any tipp is welcome!

This should do for part 1 ( so in this example, returns list 1 where cols. 1&2 match cols 2&3 in list 2):

With[{s1 = #1[[All, #3]], s2 = #2[[All, #4]]},
Pick[#1,
Replace[s1,
Dispatch[
Append[Thread[Rule[Intersection[s1, s2], True]], _ ->
False]], {1}], True]] &[list1, list2, {1,2}, {2,3}]


Should be comparable in speed to original (perhaps a tiny bit slower), and can be used on single columns also (same format as prior).

Not clear on part 2 requirements, might you give an example?

I think you're after something like this (in this example, giving list 1 where cols 1,2, and 3 do not match any of the sets of cols 2,3, and 4 in list 2):

With[{s1 = #1[[All, #3]], s2 = #2[[All, #4]]},
Pick[#,
Replace[s1,
Dispatch@
Append[Thread[Rule[Complement[s1, s2], True]], _ ->
False], {1}]]] &[list1, list2, {1, 2, 3}, {2, 3, 4}]


Note I used Complement to do the selection en masse - this is equivalent to !MemberQ[<cols of list 2>, <element of cols of list 1>] mapped over the elements, but much faster. Let me know if that's the intent.

The same can be had simply reversing the pick condition with the intersection:

With[{s1 = #1[[All, #3]], s2 = #2[[All, #4]]},
Pick[#1,
Replace[s1,
Dispatch[
Append[Thread[Rule[Intersection[s1, s2], True]], _ ->
False]], {1}], False]] &[list1, list2, {1, 2, 3}, {2, 3, 4}]


Might be a little faster - have not tested...