Inserting data into matrices where gaps exist.

I have a matrix that will be different depending on the data set I'm working with. It will always have a few columns where the first column is a reference column which starts are some arbitrary (but meaningful) value and increases by 1 for each row. The set of data will almost always be missing values and I can find the location of these missing points by building a perfect reference column and comparing it to the original data set (see below).

ref = [data(1):data(end)]' %rebuilding the reference column which will be equal to or larger than the original data set.
gaps = find(~ismember(ref, data(:,1))) %locates missing values in the original data set, based on the reference column.

From here I am trying to find an elegant solution to filling in the missing data by inserting rows. I've looked at close to 15 solutions so far and none really work for what I am trying to do. Hope someone can help before I finish option 2 (which sounds like a lot of work to me).

Option 1: I'm hoping there is an easy way to insert the missing reference values and NaNs into the desired matrix rows, before use fillmissing() to finish the job, but I don't know if this is even possible.

Options 2: Split the matrix at an identified gap, build a new matrix by appending a bottom row to the top half of the original matrix, and then using vertcat() to rebuild the matrix. Repeat for each instance until no gaps are identified. I guess this would need a while loop.

I am using R2017a.

Answer 1

[Cris LaPierre]

We don't have much to work with, but perhaps you can join your tables? If you want to try this interactively, use the Join Table task in the live editor.

[maffew]

Sorry, I didn't meant to be too vague. I just think it's not that useful for others when someone asks a very specific question and gets a very specific answer. But I've make a specific example right now.

Here's an example of what my data looks like:

data = [1 1; 2 3; 3 5; 5 9; 6 9; 7 10; 9 11; 10 10] 

In the end, I would like it to look like this:

data_fixed = [1 1; 2 3; 3 5; 4 7; 6 9; 7 10; 8 10.5; 9 11; 10 10]

In this case, there are two gaps in the data. Could be more, or less, or none at all (wouldn't that be nice). The gaps might be greater than a single row too!

I am looking into join tables now, but I am mediocre at matlab and don't entirely see why I would have these as a table vs a matrix (but I will read into this too!). Thanks for your help :)

[Cris LaPierre]

The details matter because it ensures we provide a solution that works for you. Here is how I might solve the problem based on the sample data given.

data = [1 1; 2 3; 3 5; 5 7; 6 9; 7 10; 9 11; 10 10];

% Define the desired data points and interpolate the missing values
xq = 1:10;
yq = interp1(data(:,1),data(:,2),1:10);

newData = [xq; yq]'

newData = 10×2

1.0000 1.0000 2.0000 3.0000 3.0000 5.0000 4.0000 6.0000 5.0000 7.0000 6.0000 9.0000 7.0000 10.0000 8.0000 10.5000 9.0000 11.0000 10.0000 10.0000

●

Note that this is slightly different from your data_fixed, but I'm assuming there is a mistake in filling the gap between 3 and 5.

[maffew]

That's fair. I did make a mistake, yes. It looks like the interpl() function is what I needed. Thank you!