6
$\begingroup$

Given a dataset with many columns, how can apply a function (in this case Count[Missing_]) to each column?

datasets["MSFT"][Count[_Missing], "PX_VOLUME"]

will work, but only for the "PX_VOLUME" column.

For example,

datasets["MSFT"][Count[_Missing], #]& /@ cols

would do the trick, but is there a more built-in way of doing it?

Edit. What I'd like to end up with is something similar to

{keys,
 datasets["MSFT"][Count[_Missing], #] & /@ keys/Length[datasets["MSFT"]] // N} 
    // Transpose // TableForm

Result

$\endgroup$
  • 1
    $\begingroup$ Please provide some minimal, but complete enough for readers to test, set of example data. In the meantime, read the documentation, have a look at Map and Transpose. $\endgroup$ – ciao Apr 5 '15 at 18:53
  • $\begingroup$ Ok I could map a function that would loop on all columns, but that's not very elegant. $\endgroup$ – Literal Apr 5 '15 at 19:00
  • 1
    $\begingroup$ What's "not very elegant" about Count[#,_Missing]&/@Transpose@<your matrix here>? $\endgroup$ – ciao Apr 5 '15 at 19:01
  • $\begingroup$ I just have the feeling that Dataset would provide an interface to do it by itself. If I simply map the function, then I lose Dataset environment I was working on (ie. no more column names, just a plain list) $\endgroup$ – Literal Apr 5 '15 at 19:06
7
$\begingroup$

In Mathematica version 10.0 this operation is awkward, but version 10.1 offers an improvement.

I will use the Titanic dataset in following examples:

$ds = ExampleData[{"Dataset", "Titanic"}];

Version 10.0

In version 10.0, it is awkward to apply a subquery to every column in a rectangular dataset. Here is one way to apply the All operator to each column:

$ds[Normal @ $ds[1 /* AssociationMap[#[[1]] -> Query[All, #[[1]]] &]]]

dataset screenshot

Alternatively, we can replace All with the desired Count aggregation:

$ds[Normal @ $ds[1 /* AssociationMap[#[[1]] -> Query[Count[_Missing], #[[1]]] &]]]

dataset screenshot

This same pattern can be used for any columnar aggregation. Here is CountDistinct:

$ds[Normal @ $ds[1 /* AssociationMap[#[[1]] -> Query[CountDistinct, #[[1]]]&]]]

dataset screenshot

Version 10.1

Fortunately, the situation has improved somewhat in version 10.1:

$ds[Transpose]

dataset screenshot

$ds[Transpose /* Query[All, CountDistinct]]

dataset screenshot

$ds[Transpose /* Query[All, Count[_Missing]]]

dataset screenshot

This last can also be expressed using Map:

$ds[Transpose /* Map[Count[_Missing]]]

Or it can be expressed in a slightly more succinct form at the cost of two separate query executions:

$ds[Transpose][All, Count[_Missing]]

Incidentally, the Transpose query operator is compiled down to the undocumented but useful function GeneralUtilities`AssociationTranspose which can transpose a list of associations.

Tricks in the Style of SQL

In the SQL world, aggregations like this are sometimes performed by first transforming the raw data elements and then applying some aggregation operator to those transformed results. Such a trick can be used to good effect to count the missing values:

$ds[Total, All, Replace[{_Missing -> 1, _ -> 0}]]

dataset screenshot

The disadvantage of tricks like these is that they must be designed on a case-by-case basis.

$\endgroup$
3
$\begingroup$

Illustrating Rasher's suggestions for your convenience with some data set. Just create anything for the sake to demonstrate counting:

dset = ElementData[#, "SoundSpeed"] & /@ ElementData[] // 
  ArrayReshape[#, {10, 4}] &

this looks like this dset // TableForm

Count[#, _Missing] & /@ Transpose @ dset

(*{3, 1, 2, 1}*)

Cannot think of anything simpler than this. This answer only for purpose of illustration at your convenience, please vote for Rasher.

$\endgroup$
  • $\begingroup$ This works, but the "dset" is not a Dataset. $\endgroup$ – mgamer Apr 5 '15 at 21:24
  • 2
    $\begingroup$ @mgamer Count[_Missing] /@ Transpose[dataset] or equivalent Transpose[dataset][All, Count[_Missing]] are slightly shorter. These will both work on Dataset objects $\endgroup$ – Taliesin Beynon Apr 7 '15 at 21:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.