Best way to convert numerical columns of a dataset with missing values into a matrix

Question

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.`|>}

This does not work:

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

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.)

Answer 1

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.}}

Answer 2

This works:

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

Answer 3

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.

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]

Answer 4

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

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.}}

Answer 5

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

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