12
$\begingroup$

What is the best way (simplest, fastest) to convert the numerical columns of a dataset into a matrix? The numerical columns can have missing values.

For example for this dataset:

ds = Dataset@{<|"passengerClass" -> "3rd", "passengerAge" -> 36.5`, 
"passengerSex" -> "male", "passengerSurvival" -> "survived", 
"passengerWeight" -> 186.5`|>, <|"passengerClass" -> "3rd", 
"passengerAge" -> 9.`, "passengerSex" -> "female", 
"passengerSurvival" -> "survived", 
"passengerWeight" -> 90.`|>, <|"passengerClass" -> "1st", 
"passengerAge" -> 35.`, "passengerSex" -> "male", 
"passengerSurvival" -> "survived", 
"passengerWeight" -> Missing[]|>, <|"passengerClass" -> "1st", 
"passengerAge" -> 60.`, "passengerSex" -> "male", 
"passengerSurvival" -> "survived", 
"passengerWeight" -> 160.`|>, <|"passengerClass" -> "3rd", 
"passengerAge" -> 23.`, "passengerSex" -> "female", 
"passengerSurvival" -> "survived", 
"passengerWeight" -> Missing[]|>, <|"passengerClass" -> "3rd", 
"passengerAge" -> 22.`, "passengerSex" -> "female", 
"passengerSurvival" -> "survived", 
"passengerWeight" -> 122.`|>, <|"passengerClass" -> "3rd", 
"passengerAge" -> Missing[], "passengerSex" -> "female", 
"passengerSurvival" -> "survived", 
"passengerWeight" -> 120|>, <|"passengerClass" -> "3rd", 
"passengerAge" -> Missing[], "passengerSex" -> "female", 
"passengerSurvival" -> "survived", 
"passengerWeight" -> Missing[]|>, <|"passengerClass" -> "1st", 
"passengerAge" -> 17.`, "passengerSex" -> "female", 
"passengerSurvival" -> "survived", "passengerWeight" -> 170.`|>}

enter image description here

This does not work:

ds[All, Select[NumberQ]][Values]

enter image description here

Some clarifying points follow.

  • I would like the _Missing entries to be replaced with $0$'s.

  • I do not know in advance the columns of interest. I want to pick the numerical columns of the dataset and then make a matrix with them.

(If I knew how to replace the missing values in a dataset with $0$s I would have solved this problem already.)

$\endgroup$
  • $\begingroup$ Like this? Query[Values, Select[NumericQ]][ds] or your expression with NumericQ instead of NumberQ. $\endgroup$ – halirutan Feb 27 '18 at 13:10
  • 1
    $\begingroup$ Ahh, I guess your real question is how you can convert the Missing to a number to make a complete matrix. $\endgroup$ – halirutan Feb 27 '18 at 13:14
  • $\begingroup$ @halirutan "how you can convert the Missing to a number to make a complete matrix." Correct. $\endgroup$ – Anton Antonov Feb 27 '18 at 14:54
  • $\begingroup$ Can't you just do ds /. _Missing->0? $\endgroup$ – Carl Woll Feb 27 '18 at 16:45
  • $\begingroup$ @CarlWoll Of course. Combining your answer with @halirutan I came up with Query[All, Values@*Select[NumberQ]]@ ReplaceAll[Normal[ds2], _Missing -> 0]. $\endgroup$ – Anton Antonov Feb 27 '18 at 17:41
1
$\begingroup$
Normal @ ds[All, Values@*Select[NumericQ]@*ReplaceAll[_Missing->0]]

{{36.5, 186.5}, {9., 90.}, {35., 0}, {60., 160.}, {23., 0}, {22., 122.}, {0, 120}, {0, 0}, {17., 170.}}

$\endgroup$
  • $\begingroup$ Right, very similar to my response to Carl Woll in the comments yesterday: Query[All, Values@*Select[NumberQ]]@ ReplaceAll[Normal[ds2], _Missing -> 0]. I end up using that line in my package code, so I am going to accept this answer. $\endgroup$ – Anton Antonov Feb 28 '18 at 20:01
6
$\begingroup$

This works:

Block[{Missing},
 Missing[] = 0;
 ds[All, Select[NumberQ]][Values]]

enter image description here

$\endgroup$
6
$\begingroup$

It's not entirely clear if you want to replace the rows that contain a Missing or if you want to leave them out. When you want to leave them out, the very verbose way would be to write it out like this:

Query[
  Select[!MissingQ[#"passengerAge"] && ! MissingQ[#"passengerWeight"] &], 
  {#"passengerAge", #"passengerWeight"} &
][ds]

and then normalizing.

Mathematica graphics

Query is not strictly necessary, but due to the good advertisement of WReach, I learned to prefer it. This is possible too

ds[Select[! MissingQ[#"passengerAge"] && !MissingQ[#"passengerWeight"] &],
 {#"passengerAge", #"passengerWeight"} &]

Edit

Thank you for your answer! Preferably, I would like the Missing to be replaced by 0's.

Then probably:

Query[ReplaceAll[Missing[] -> 0], {#"passengerAge", #"passengerWeight"} &][ds]
$\endgroup$
  • $\begingroup$ I like the use of Query too. $\endgroup$ – Anton Antonov Feb 27 '18 at 14:57
3
$\begingroup$
ds[associationIndex][Transpose][
  GroupBy[Query[DeleteMissing /* Query[Apply[And], NumericQ]]] /* 
   Query[{Key[True] -> Query[All, All, Replace[_Missing -> 0.]]}] /* 
   Values /* Apply[Join]][Transpose]

enter image description here

Where the utility associationIndex is one of dozens one-liners included in the API of my forthcoming book "Functional Data Workflow".

associationIndex[a_Association]:=Query[Normal/*MapIndexed[First[#2]->#1&]/*Association][a]

associationIndex[l_List]:=Query[MapIndexed[First[#2]->#1&]/*Association][l]

Without it, after the Apply[Join] you'd have to thread keys back in - note the use of bracketing Transpose. Such commutation is a common pattern, (btw Transpose will also impute Missing values to normalize ragged data.) - but careful as the op-form is buggy (I avoided RightComposition)

You might want to mod the logic below to suit various applications:

Query[DeleteMissing /* Query[Apply[And], NumericQ]]

If you just want the numeric matrix, there's no need for the Join:

ds[associationIndex][Transpose][
  GroupBy[Query[DeleteMissing /* Query[Apply[And], NumericQ]]] /* 
   Key[True] /* 
   Query[Values, Values, Replace[_Missing -> 0.]]][Transpose]

{{36.5, 186.5}, {9., 90.}, {35., 0.}, {60., 160.}, {23., 0.}, {22., 122.}, {0., 120}, {0., 0.}, {17., 170.}}

$\endgroup$
2
$\begingroup$

Can't you just pick the columns of interest, and then replace the missing objects?

ReplaceAll[_Missing->0]@Values@ds[All, {"passengerAge","passengerWeight"}]

enter image description here

$\endgroup$
  • $\begingroup$ "Can't you just pick the columns of interest, and then replace the missing objects?" -- Generally, I do not know in advance the columns of interest. I want to pick the numerical columns of the dataset and then make a matrix with them. $\endgroup$ – Anton Antonov Feb 27 '18 at 16:17

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.