13
$\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$
5
  • $\begingroup$ Like this? Query[Values, Select[NumericQ]][ds] or your expression with NumericQ instead of NumberQ. $\endgroup$
    – halirutan
    Feb 27, 2018 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, 2018 at 13:14
  • $\begingroup$ @halirutan "how you can convert the Missing to a number to make a complete matrix." Correct. $\endgroup$ Feb 27, 2018 at 14:54
  • $\begingroup$ Can't you just do ds /. _Missing->0? $\endgroup$
    – Carl Woll
    Feb 27, 2018 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$ Feb 27, 2018 at 17:41

5 Answers 5

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$
1
  • $\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$ Feb 28, 2018 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$
1
  • $\begingroup$ I like the use of Query too. $\endgroup$ Feb 27, 2018 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$
1
  • $\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$ Feb 27, 2018 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.