# Advanced SQL-type select statements (filtering) on Datasets

I'm trying to improve on mathematica's handling of datasets. I'm currently writing this function that should do the following.

Suppose I have a dataset with columns, e.g.

cols = {col1, col2, col3}


and I want to remove all NA type entries from a subset of these columns, say

subcols = {col1, col2}


To create an example dataset, let's condider:

rows = {
{0.09, 0.53, 0.32},
{"NA", "NA", 0.19},
{0.52, 0.38, "NA"},
{"NA", 0.25, 0.20},
{0.03, 0.61, 0.52}
};

dataset = Dataset[Inner[Rule, cols, #, Association]& /@ rows];


Now, I know that I can select the rows I want by the selection rule:

dataset[Select[NumberQ[#"col1"] && NumberQ[#"col2"]&]]


but I would like to create a short-hand notation for this, so that it applies to bigger datasets (with many columns). In particular, I'd like write a function DropNA that takes a dataset and a list of columns as input and outputs a new dataset with all unwanted rows removed. For instance, a test could be:

DropNA[dataset, subcols] == dataset[Select[NumberQ[#"col1"] && NumberQ[#"col2"]&]]

True


This is what I have so far:

DropNA[dataset_, cols_] := Module[{allCols, GoodRow},

(* get the list of all columns: *)
allCols = dataset[All, Keys] // Union // Normal;

(* some precaution: *)
dropna::badcolumn = "Dataset has an ambiguous set of columns.";
If[Length[allCols] != 1, Message[dropna::ambiguouscolumns]; Abort[]];

dropna::subsetcolumns = "One or more columns provided do not appear in the dataset.";
If[Not[SubsetQ[allCols[[1]], cols]], Message[dropna::subsetcolumns];Abort[]];

(* returns True if it encounters good row: *)
GoodRow[row_] := And @@ (NumberQ[row[#]] & /@ cols);

(* select only the good rows: *)
dataset[Select[GoodRow]]
]


As it stands now, DropNA only returns the entire dataset. Does anyone have any idea what I'm missing here?

The exhibited definition of DropNA already functions correctly:

DropNA[dataset, subcols] // Normal

(* { <| col1 -> 0.09, col2 -> 0.53, col3 -> 0.32 |>
, <| col1 -> 0.52, col2 -> 0.38, col3 -> NA |>
, <| col1 -> 0.03, col2 -> 0.61, col3 -> 0.52 |>
}
*)


(The result is shown here in normal form because neither dataset nor its derivatives render well within the V10.0.0 front-end due to the use of symbols for keys.)

The test expression, on the other hand, contains errors. First, key references such as #col1 select string keys instead of the symbols used in dataset. Second, datasets cannot be compared for deep equality using ==. They must be converted to normal form before comparing.

If we fix both problems, then DropNA passes the test:

Normal @ DropNA[dataset, subcols] ==
Normal @ dataset[Select[NumberQ[#[col1]] && NumberQ[#[col2]] &]]

(* True *)


An Alternate Approach: Custom Search Operators

Instead of defining a function that transforms one dataset into another, we can define a custom search operator:

noNAs[cols_] := Select[FreeQ[Lookup[#, cols], "NA"]&]


For the case at hand, it is used like this:

dataset[noNAs[subcols]] // Normal

(* { <| col1 -> 0.09, col2 -> 0.53, col3 -> 0.32 |>
, <| col1 -> 0.52, col2 -> 0.38, col3 -> NA |>
, <| col1 -> 0.03, col2 -> 0.61, col3 -> 0.52 |>
}
*)


But the advantage of using a custom search operator is that it can be composed with other search operators within the confines of a single query, e.g.

dataset[noNAs[{col1, col2}] /* Total, Key[col3]]

(* 0.84 + NA *)


NoNAs checks for the absence of the string "NA". If prefered, a similar search operator could be defined that ensures that all values are numbers instead:

allNumbers[cols_] := Select[AllTrue[Lookup[#, cols], NumberQ]&]

dataset[allNumbers[subcols]] // Normal

(* { <| col1 -> 0.09, col2 -> 0.53, col3 -> 0.32 |>
, <| col1 -> 0.52, col2 -> 0.38, col3 -> NA |>
, <| col1 -> 0.03, col2 -> 0.61, col3 -> 0.52 |>
}
*)

• Wow, I'm very impressed WReach! Thanks so much for taking the time to write this!! I especially like your allNumbers alternative. That's the one I'll use from now on. I've really learned a lot from this, I didn't know about either AllTrue, FreeQ or the LookUp function (even though I was already using it through the single bracket notation). Anyway, thanks! – Kris Sep 1 '14 at 5:54
• @WReach customer operators are emerging as quite a powerful idiom! I wonder how we can better support them in Query/Dataset. – Taliesin Beynon Sep 18 '14 at 5:58
• @TaliesinBeynon That's a good question, and this comment box feels very constraining :D Random thoughts: I think that ascending operators are pretty much covered since we have the full power of the language available to them. For descending operators, it would be interesting to come up with some kind of conditional syntax to be able to select a different operator based upon some computation. Given the nature of descending operators, especially with an eye to any future compilation to SQL et al, that syntax would have to strike a careful balance between expressiveness and compilability. – WReach Sep 18 '14 at 12:52
• @TaliesinBeynon OK, I'll spill a little bit into a second comment box :) It would be nice if there were a way to define some kind of macro facility for the existing descending operators so that common expressions could be named and parameterized. – WReach Sep 18 '14 at 12:56

I found a way to define a version of DropNA, but it's not very pretty. Perhaps someone else will come up with a better way.

keys = {"col1", "col2", "col3"};
rows =
{{0.09, 0.53, 0.32}, {"NA", "NA", 0.19}, {0.52, 0.38, "NA"},
{"NA", 0.25, 0.20}, {0.03, 0.61, 0.52}};
ds = Dataset[Inner[Rule, keys, #, Association] & /@ rows];

dropNonNum[dataset_, cols : {__String} /; SubsetQ[keys, cols]] :=
dataset[Select[
With[{q = Inactivate[And @@ Function[x, NumberQ[#[x]]] /@ cols, NumberQ]},
Function[q]] // Activate]]

dropNonNum[ds, {"col1", "col2"}]


dropNonNum[ds, {"col3"}]


• Ah thanks, I believe WReach came up with a nice concise solution. Thanks for the suggestion though, I didn't know about the Inactivate / Activate functions.. looks like a pretty handy tool! – Kris Sep 1 '14 at 5:57