How to make two uneven sized tables of even length and check and fill in missing data with NaN?
8 次查看(过去 30 天)
显示 更早的评论
Hi all,
So I have two tables with each three columns; Date, Time and a Variable.
Date Time Variable
31-05-15 01:00:00.000 78.6706619262695
31-05-15 01:00:01.000 79.6703614265667
31-05-15 01:00:02.000 80.7806623452695
.. .. ..
I want to plot the variables of each table against each other, but unfortunately table 1 has more rows than table 2. They have the same start- and end date/time, but at some days table 1 contains more rows (more data points) than table 2 which results in two tables of uneven length.
I would like to add the missing date and time rows to table 2 to make the tables of even length. For these added rows there is no variable value present in the third column, thus NaN should be entered here. I should probably add that the tables contain around 38 million rows, so an if loop that checks each separate row is not doable.
I'm hoping you can help me out with this, thanks in advance!
0 个评论
回答(1 个)
Edric Ellis
2017-2-22
编辑:Edric Ellis
2017-2-22
If you're using MATLAB R2016b, you can use the new timetable class to do this. In particular, there's the synchronize method which is designed for exactly this use case. synchronize allows you to specify how to deal with the mismatch in time vectors.
You'll first need to convert your Date and Time variables into a single datetime variable - if your existing variables are char, then you'll need to use something like this:
times = datetime(strcat(t.Date, '-', t.Time), 'InputFormat', 'dd-MM-yy-HH:mm:ss.SSS')
Putting it all together, I think you want something like this:
t1 = table({'01-01-2017'; '01-01-2017'; '01-01-2017'}, ...
{'01:00:00.000'; '01:02:00.000'; '01:04:00.000'}, ...
[1; 2; 4], 'VariableNames', {'Date', 'Time', 'Variable'});
t2 = table({'01-01-2017'; '01-01-2017'; '01-01-2017'}, ...
{'01:00:00.000'; '01:01:00.000'; '01:02:00.000'}, ...
[3; 4; 5], 'VariableNames', {'Date', 'Time', 'Variable'});
% Get the datetimes for each table
d1 = datetime(strcat(t1.Date, '-', t1.Time), ...
'InputFormat', 'dd-MM-yy-HH:mm:ss.SSS');
d2 = datetime(strcat(t2.Date, '-', t2.Time), ...
'InputFormat', 'dd-MM-yy-HH:mm:ss.SSS');
% Build timetables
tt1 = timetable(d1, t1.Variable);
tt2 = timetable(d2, t2.Variable);
% Synchronize timetables - the default behaviour is probably acceptable
% here because it inserts NaN where the times dont match
tt = synchronize(tt1, tt2);
% Plot the result
plot(tt.d1, tt.Var1_tt1, 'ro', ...
tt.d1, tt.Var1_tt2, 'bx');
0 个评论
另请参阅
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!