How to insert missing data in?
Show older comments
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).
Answers (2)
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.
26 Comments
Jaffatron
on 14 Apr 2015
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 )
Jaffatron
on 14 Apr 2015
Guillaume
on 14 Apr 2015
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
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
on 14 Apr 2015
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.
Adam
on 15 Apr 2015
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
on 15 Apr 2015
Adam
on 15 Apr 2015
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).
Adam
on 15 Apr 2015
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
on 15 Apr 2015
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
on 15 Apr 2015
Kelly Kearney
on 15 Apr 2015
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));
Kelly Kearney
on 15 Apr 2015
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.
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
on 17 Apr 2015
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.
Adam
on 17 Apr 2015
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
on 17 Dec 2020
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.
Jaffatron
on 17 Apr 2015
0 votes
Categories
Find more on Conversion Between Symbolic and Numeric in Help Center and File Exchange
on 17 Dec 2020
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
