# Automatic process to replace missing values, coming from a second matrix

I have a large starting matrix with missing values in different columns. I want to replace this missing data as follow.

First I have to check the group (last column in starting matrix) and go to a second matrix, which has no missing values, and take there the same group as in the starting matrix. Then take a random value out of this choice from the corresponding column, where the missing value was located.

I came up with a manual solution, since the matrix is very large i need this automatic.

startingMatrix = {{1, 2, 3, 4, 5, "Null", 6, 7, 10}, {1, 2, "Null", 4,
5, "Null", 6, 7, 11}, {"Null", 1, 2, 3, 4, 5, 6, 7, 10}, {1, 2, 3,
4, 5, "Null", 6, 7, 12}}

lookupMatrix = {{1, 2, 3, 4, 5, 5.5, 6, 7, 10}, {1, 2, 3, 4, 5, 6, 7,
9, 11}, {1, 2, 3, 4, 5, 8, 7, 9, 12}, {1, 2, 3, 4, 5, 6, 7, 9,
10}, {1, 3, 3, 4, 5, 6, 7, 9, 12}, {1, 2, 3, 4, 5, 6, 7, 9,
11}, {1, 2, 5, 4, 5, 6, 7, 9, 10}, {1, 2, 3, 4, 1, 6, 7, 9,
10}, {1, 2, 3, 4, 5, 4, 7, 9, 10}, {1, 2, 3, 2, 5, 4, 7, 9, 10}}


Example for the first vector of the starting matrix:

1.Find the position of the missing value:

p = Position[startingMatrix, "Null"]


2.Subset lookupMatrix:

ubset = Select[lookupMatrix, #[[9]] == 10 &] (*Here the 10 should come from the inputvector*)


3.Desired Input for the missing value:

replaceValue = RandomChoice[subset[[All, 6]]] (*Here the 6 should come from the position*)


4.Final updaterule:

ReplacePart[startingMatrix, p -> replaceValue] (*Here the replaceValue should only be used for the first vector and not for all p*)


How can I link all these things?

This isn't much different than JasonB's answer in substance, but it's easier to debug and it's usually how I first approach problems with lots of steps:

 Module[
{nullPos, nullGroup, nullReplacements},
nullPos = Position[startingMatrix, "Null"];
nullGroup = Last@startingMatrix[[First@#]] & /@ nullPos;
nullReplacements = Cases[lookupMatrix, {___, x_} /; x == #1][[All,
Last@#2]] & @@@ (Transpose@{nullGroup, nullPos});
nullReplacements = RandomChoice /@ nullReplacements;
]

• There is a small mistake since "nullReplacements" lists the entire vector and not only the results from the specific position of "nullPos". May 4, 2016 at 17:02
• See, totally easy to debug! :) May 4, 2016 at 17:17

This looks convoluted, but it matches the requirements you set up

Fold[
Function[{matrix, index},
ReplacePart[matrix, index -> RandomChoice[
Select[
lookupMatrix, (Last@# ==
startingMatrix[[First@index, -1]] &)][[All, Last@index]]
]]],
startingMatrix, Position[startingMatrix, "Null"]]
(* {{1, 2, 3, 4, 5, 5.5, 6, 7, 10}, {1, 2, 3, 4, 5, 6, 6, 7,
11}, {1, 1, 2, 3, 4, 5, 6, 7, 10}, {1, 2, 3, 4, 5, 8, 6, 7, 12}} *)


At the heart of this is a function that takes in input matrix, and an index as arguments. Then it uses ReplacePart[matrix, index -> ...] where it uses your rules to figure out what to replace that part with. Then after that replacement, the resulting matrix is fed back to the function with the next index.