Inserting missing dates and values

I have two column matrix representing a time series data with daily interval. But I have few missing data so some of the dates are not shown in the matrix. But I need to insert all possible dates in the data series where the missing values should be equal to the previous day’s value. Can I do it in Matlab? For example the matrix looks like x=[Jan1 10; Jan2 12; Jan4 16] but it should be x=[Jan1 10; Jan2 12; Jan3 12; Jan4 16]. It looks very simple in this example but I have millions of data so it is not possible to correct manually. Anyone can help please?

[Breylla Carvalho]

Hey everyone! I have the same problem as Mohammad. Has anyone who could help me?

[Ana Soph]

Yeah. I have the same problem

[Image Analyst]

There are two Answers offering help below. Just scroll down. If they don't work then read this link.

[Ana Soph]

sorry i don't understand the answer, i think doesn't apply to my problem

[Image Analyst]

You said you have the same problem as Mohammad. The two answers below offered a solution so there is a chance they may work for you also. If you already read them and you say it does not apply to your problem, then evidently you do not have the same problem (kind of a contradiction). So we're now not sure if you have the same problem or not. Anyway...

If you have a different problem, read this link first, and then post your own new question with your own data attached.

[Ana Soph]

i already read the link and put my answer. Have a great day

Answer 1

[Steven Lord]

If you're using a release of MATLAB that includes timetable, create a timetable with your data then use retime on that timetable.

% Create some time and data variables
>> t = datetime(2017, 1, [1; 2; 4]);
>> x = [10; 12; 16];

% Assemble the timetable and display it
>> tt = timetable(t, x)
tt =
  3×1 timetable
         t         x 
    ___________    __
    01-Jan-2017    10
    02-Jan-2017    12
    04-Jan-2017    16

% Generate daily data in your timetable, filling in new rows with
% the previous day's data
>> tt2 = retime(tt, 'daily', 'previous')
tt2 =
  4×1 timetable
         t         x 
    ___________    __
    01-Jan-2017    10
    02-Jan-2017    12
    03-Jan-2017    12
    04-Jan-2017    16

% Generate daily data in your timetable, but fill new rows with missing data
% For a double variable, 'missing' means NaN
>> tt3 = retime(tt, 'daily', 'fillwithmissing')
tt3 =
  4×1 timetable
         t          x 
    ___________    ___
    01-Jan-2017     10
    02-Jan-2017     12
    03-Jan-2017    NaN
    04-Jan-2017     16

Answer 2

[Image Analyst]

I don't know what type of data x is, but convert them to a number with sprintf() and datenum(). Then use interp1() to fill in the missing dates.

[Mohammad Sayeed]

Dear Image Analyst, Here is a very sample of my data. It contains data of one minutes interval. In the first Sheet of the EXCEL file I have shown the data that I downloaded. It does not have all the one minute rows that I should have because may be there was no trade during those missing minutes. But for applying some mathematical formulas I need to insert the rows with missing minutes. So I need to have data that I have shown in the second Sheet. Can you please Help?

[Mohammad Sayeed]

Or is it possible to replace the first three columns of the original dataset with the three columns in the above file so that I will get the intended output. Thanks.

[Image Analyst]

When you say "Here is ....." - exactly where is "here"?

[Mohammad Sayeed]

Sorry for that. I have clearly attached EXCEL files in my comments but I don't know why these files are not shown.

[Mohammad Sayeed]

I have attached them again. Hope this time it is okay. In the 1st sheet of the first file I have a very small sample of the original data that I have downloaded. In the second Sheet of this file I have shown how I need the data set to be. The second file shows how the first three time columns should look like in my intended data set. Thank you.