How to make two uneven sized tables of even length and check and fill in missing data with NaN?

Question

Matthijs Priester 2017-2-21

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/326249-how-to-make-two-uneven-sized-tables-of-even-length-and-check-and-fill-in-missing-data-with-nan

编辑： Edric Ellis 2017-2-22

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 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Edric Ellis 2017-2-22

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/326249-how-to-make-two-uneven-sized-tables-of-even-length-and-check-and-fill-in-missing-data-with-nan#answer_255842

编辑：Edric Ellis 2017-2-22

在 MATLAB Online 中打开

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');