How to insert missing data in?

I carried out an experiment and automatically got readings.

However the apparatus was supposed to take a reading every 0.1 seconds, and sometimes it was offline but the apparatus kept counting but didn't print these in the. For example:

It would go 98.5, 98.6, 99.0. I'm looking a way to put blank rows in where there is missing data, such as it'll read 98.5, 98.6, 98.7, 98.8, 98.9, 99.0. And to give values of Nan for these. Doing it individually isn't an option, as there is a vast amount of data (864,00 for a day).

Answer 1

[Adam]

times = [98.5 98.6 99.0];
readings = [123 345 567];

expectedTimes = 98.5:0.1:99.0;
newReadings = NaN( size( expectedTimes ) );
newReadings( ismember( expectedTimes, times ) ) = readings;

is one method. Obviously extendible to larger data read in rather than my small hard-coded example.

[Jaffatron]

When I try this for my data sets it's giving an error message of:

"In an assignment A(I) = B, the number of elements in B and I must be the same"

When I put in

newReadings( ismember( expectedTimes, times ) ) = readings1;

[Adam]

Do you have the same number of readings as you do times? That seemed to be a sensible assumption for this method. There are also other assumptions embedded in it based on the information you gave - e.g. every value in the 'times' vector should exist in the 'expectedTimes' vector. Being doubles this may be what is causing a problem due to the inexact nature of floating point comparisons. It worked in my example, but if your data is a little different that part may not work.

If you want to break it down for debugging then put:

idx = ismember( expectedTimes, times );

and compare

numel( readings1 )

and

nnz( idx )

[Guillaume]

What is the size of times and readings1?. If the two don't match up then you'll get this error.

[Jaffatron]

Maybe I should have given some example data, I have say:

A=

98.5 50.5
98.6 50.4
99.0 51.5

And I want:

B=

98.5 50.5
98.6 50.4
98.7 NaN
98.8 NaN
98.9 NaN
99.0 51.5

Hope this helps

[Guillaume]

One slight issue with using pure equality comparison (which ismember use) is that 99.9 in expectedTimes may not be actually equal to the 99.9 in times depending on how the two are generated.

This is due to the finite precision of floating point number, and is not specific to matlab. For example note that:

0.1 + 0.1 + 0.1 == 0.3

returns 0 (false). To make sure that both time arrays are indeed considered equal, I would round them to 0.1, thus:

newReadings(ismember(round(expectedTimes, -1), round(times, -1))) = readings; %in R2014b or later

newReadings(ismember(round(expectedTimes*10), round(times*10))) = readings; %in earlier versions

[Adam]

That works for me, assuming I pull out column 1 of A into what I called 'times' and column 2 of A into what I called 'readings'

times = A(:,1);
readings = A(:,2);

Obviously in your code you don't have to create these new variables. You can just plugin A(:,1) and A(:,2) directly.

[Jaffatron]

I'm still getting the same error message.

Not quite sure what is wrong

[Adam]

What is the result then of doing

idx = ismember( expectedTimes, times );

and compare

numel( readings1 )

and

nnz( idx )

?

If it is the inexactness of the equality measure then Guillaume's solution should work. I didn't actually realise round took an argument like that so I thought it would require more work to solve that problem, hence me not doing so until it was confirmed if it is the problem. Since it is just a one line addition though it will make it more robust at very little extra cost.

[Jaffatron]

I can get your example working.

In my case my times and readings1 are 1x853292 arrays, and my newReadings and expectedTimes are 1x864000 arrays. Therefore they are going to be of different size?

[Adam]

newReadings and expectedTimes should be different from times and readings, but times and readings must be the same length as each other and the result of that ismember call must be a logical array with a number of true values equal to the length of times and readings.

If it isn't that means one or more of the times is not found in your expectedTimes array which is likely where you would need Guillaume's fix, though I think you need:

newReadings(ismember(round(expectedTimes, 1), round(times, 1))) = readings;

if you use the R2014b version unless I am much mistaken (note the 1 instead of -1).

[Jaffatron]

Still getting the same error message sadly.

[Adam]

even with the rounding included? And have you checked what lengths are being reported for the two things I mentioned above (noting that it is the number of ones - i.e. nnz of the ismember result that should match the number of readings and times you have. The length of the ismember result itself will match that of your expected times).

[Jaffatron]

Yes, I included the rounding.

For

numel( readings1 )

ans =

        853292

and

nnz( idx )

ans =

        565267

The size of the ismember result does match the expected times.

[Adam]

And are you sure your actual times are falling at 0.1s intervals?

The above suggests that there are ~300,000 values in your times array that were not found in your expectedTimes array even with rounding.

If you can locate some of those as an example it should help to understand what is happening here. Try taking a much smaller subset of your times (and set up your expectedTimes to match) and check those times which are not being matched to your expectedTimes array.

[Guillaume]

setdiff(round(times, 1), round(expectedTimes, 1))

will show which of the times value are not present in expectedTimes.

The problem is not with Adam's solution but with your assumption that all the times are present in expectedTimes.

So maybe Adam's solution should have startd with this line:

assert(isempty(setdiff(round(times, 1), round(expectedTimes, 1)))

[Jaffatron]

Ok I've spent a bit of time at this. My data was slightly skew for some reason. I'm now getting:

>> numel( readings1 )

ans =

        853292

>> nnz( idx )

ans =

        853291

So it still won't work properly due to one number being missing I'm assuming?

[Adam]

Yeah. Use Guillaume's setdiff command above to find the culprit and hopefully understand why. It may just be the first or last value in expectedTimes that you got wrong by one increment.

[Jaffatron]

When I run

>> setdiff(round(times, 1), round(expectedTimes, 1))

ans =

   Empty matrix: 0-by-1

[Kelly Kearney]

Try setxor instead ( setdiff only returns values in A but not B, not vice versa):

[c,ia,ib] = setxor(round(times, 1), round(expectedTimes, 1));

[Jaffatron]

Ok, I'm assuming using:

[c,ia,ib] = setxor(round(times, 1), round(expectedTimes, 1));

c gives the missing times, ib gives which values that don't occur in times but occur in expectedTimes, and ia gives the values that occur in expectedTimes but not times.

How do I then incorporate this to solve my problem? As in I now know which vaules I'm missing. How can I leave space for the missing values and put in a NaN for the measurement at that value?

[Kelly Kearney]

I suggested setxor based on the comments others had given you, but you should be able to get the same data if you break the ismember call into a separate step:

% Your data (1)

t1 = [98.5 98.6 98.65 99.0];
x1 = [1 2 3 4];

% Full set of times (2)

t2 = 98.5:0.1:99.0;
x2 = nan(size(t2));

% Match up the ones that fit

[tf, loc] = ismember(t1, t2);
x2(loc(tf)) = x1(tf);

% What times are in set 1 but not set 2?

tmiss = t1(~tf);

This snippet will plug the values into the appropriate rows. Then you'll need to look at the times left over in tmiss to figure out why you times that aren't fitting the expected spacing. It might be roundoff error (which you can deal with using round, as suggested above). Or you might have some errant times that you need to deal with in a more hands-on manner.

[Jaffatron]

I got this working up to a certain data point, and then it just stops working. Any idea why that could be the case?

I've attached a screen shot of it. As you can see it works up until 43200 and then it returns a zero for all. I've noticed it reads 43200 as 43200.00000 and not 43200 as it does for 43199. Any ideas what is happening here? And a possible solution?

[Michael Haderlein]

It's always a problem when comparing floating point values with each other. I think it should work when you change everything with a factor of 10. I mean, multiply Data1 with 10, set r to 1, t2 to 1:863999, divide Data1_Freq by 10 and things should be fine.

[Jaffatron]

Still getting the same solution. I think it's something to do with Matlab calling 43200.00000 instead of just 43200. Would this have any affect?

It seems to be in ismember function that is messing up my t2 matrix, any ideas guys?

[Adam]

ismember does act a little suspiciously sometimes, I'm not sure what its underlying algorithm is.

I had to remove its usage from something I was doing with custom classes recently. For ages it was working fine to check if a given object was a member of an array of objects, but then in some circumstances it began to return 0 claiming the object was not a member. An isequal (...) call on the object and the member of the array that I knew matched it returned true, however so I ended up just changing to use a different implementation.

[Amin Rajabi]

Thank you very much, I was looking for a quick way for adding missing records to a vector (a vector that is shorter than the original expected one). It works perfectly.

Answer 2

[Jaffatron]

Got it working fully, I used the round function on my t2 array as well as t1 and it seems to have done the trick.

Thanks to everyone that posted on this over the past few days.