Fill the column with pre-defined time intervals

Hi,

I need to fill the column A below with pre-defined time intervals for each day. For example, Jan 1 1998

1/1/1998 0:00
1/1/1998 3:00
1/1/1998 4:00
1/1/1998 6:00
1/1/1998 9:00
1/1/1998 10:00
1/1/1998 12:00
1/1/1998 15:00
1/1/1998 16:00
1/1/1998 18:00
1/1/1998 21:00
1/1/1998 22:00
1/2/1998 0:00

A=

    1/1/1998 0:00
    1/1/1998 4:00
    1/1/1998 6:00
    1/1/1998 10:00
    1/1/1998 12:00
    1/1/1998 16:00
    1/1/1998 18:00
    1/1/1998 22:00
    1/2/1998 0:00
    1/2/1998 4:00
    1/2/1998 6:00
    1/2/1998 12:00
    1/2/1998 18:00
    1/3/1998 0:00
    1/15/1998 18:00
    1/16/1998 0:00
    1/16/1998 6:00
    1/16/1998 12:00
    1/16/1998 18:00
    1/17/1998 0:00
    1/17/1998 6:00
    1/17/1998 12:00
    1/17/1998 18:00
    1/18/1998 0:00
    1/18/1998 6:00
    1/18/1998 12:00
    1/18/1998 18:00
    1/19/1998 0:00
    1/19/1998 6:00
    1/19/1998 12:00
    1/19/1998 18:00
    1/20/1998 0:00
    1/20/1998 6:00
    1/20/1998 12:00
    1/20/1998 18:00
    1/21/1998 0:00
    1/21/1998 6:00
    1/21/1998 12:00
    1/21/1998 18:00
    1/22/1998 0:00
    1/22/1998 6:00
    1/22/1998 12:00
    1/22/1998 18:00
    1/23/1998 0:00
    1/23/1998 6:00
    1/22/1998 10:00
    1/22/1998 12:00
    1/22/1998 18:00
    1/22/1998 22:00
    1/23/1998 0:00

Is there a way to this MATLAB?

Thanks in advance.

Answer 1

[Star Strider]

One way to do it:

D0 = datenum([1998 01 01]);
DT = 1/24;                          % Days/Hour
DM = eomday(1998,01);               % Days In January
DV = D0 + cumsum([0; ones(24*DM,1)*DT]);
Jan1998 = datestr(DV(1:end-1), 'mm/dd/yyyy HH:MM');
Result = [Jan1998(1:5,:); Jan1998(end-4:end,:)]         % Check Result

produces:

Result =

01/01/1998 00:00
01/01/1998 01:00
01/01/1998 02:00
01/01/1998 03:00
01/01/1998 04:00
01/31/1998 19:00
01/31/1998 20:00
01/31/1998 21:00
01/31/1998 22:00
01/31/1998 23:00

The ‘Result’ variable just looks at the first and last 5 entries.

[Damith]

Thanks again for your quick reply. Your outcome is not the result I want. Sorry may be my exaplanation is not clear.

Actually, I want to insert 0,3,6,9,12,15,18,21 time steps for each day if these time steps are not available in Column A. But also I need to keep the time steps for example, 4 and 10, in column A.

A=

        1/1/1998 0:00
        1/1/1998 4:00
        1/1/1998 6:00
        1/1/1998 10:00
        1/1/1998 12:00
        1/1/1998 16:00
        1/1/1998 18:00
        1/1/1998 22:00
        1/2/1998 0:00

[Star Strider]

I don’t have sufficient energy tonight to copy your ‘A’ matrix, since I would have to put quotes around each row, and either create them as a cell array of strings, or add leading zeros to get all the columns to have the same width to create a character array.

Change the appropriate line to:

Jan1998 = datestr(DV(1:3:end-1), 'mm/dd/yyyy HH:MM');

to get every third hour, then concatenate it with ‘A’ (they have to have the appropriate column dimensions for that to work) and use the unique function to create your output array, something like this:

A = unique([Jan1998; A]);

That sorts them as well, so you would have non-repeating values that combine both arrays.

[Damith]

I have created the sample "A" matrix with quotes.

Now, I need to read first row and end row of "A" matrix and then remove rows which are other than 0,3,6,9,12,15,18,21 from A and if A matrix does not have the above time intervals (0,3,6,9,12,15,18,21) for a given date then include it between the date ranges of first row and end row.

Hope this helps.

A=

    '1/1/1998 0:00'
    '1/1/1998 4:00'
    '1/1/1998 6:00'
    '1/1/1998 10:00'
    '1/1/1998 12:00'
    '1/1/1998 16:00'
    '1/1/1998 18:00'
    '1/1/1998 22:00'
    '1/2/1998 0:00'
    '1/2/1998 4:00'
    '1/2/1998 6:00'
    '1/2/1998 12:00'
    '1/2/1998 18:00'
    '1/3/1998 0:00'

[Star Strider]

I need leading zeros on everything, but once I supplied them, my idea works:

D0 = datenum([1998 01 01]);
DT = 1/24;                          % Days/Hour
% DM = eomday(1998,01);               % Days In January
DM = 2;
DV = D0 + cumsum([0; ones(24*DM,1)*DT]);
Jan1998 = datestr(DV(1:3:end-1), 'mm/dd/yyyy HH:MM');
% Result = [Jan1998(1:5,:); Jan1998(end-4:end,:)]         % Check Result

A= ['01/01/1998 00:00'
    '01/01/1998 04:00'
    '01/01/1998 06:00'
    '01/01/1998 10:00'
    '01/01/1998 12:00'
    '01/01/1998 16:00'
    '01/01/1998 18:00'
    '01/01/1998 22:00'
    '01/02/1998 00:00'
    '01/02/1998 04:00'
    '01/02/1998 06:00'
    '01/02/1998 12:00'
    '01/02/1998 18:00'
    '01/03/1998 00:00'];

A = unique([Jan1998; A], 'rows');

produces:

A =

01/01/1998 00:00
01/01/1998 03:00
01/01/1998 04:00
01/01/1998 06:00
01/01/1998 09:00
01/01/1998 10:00
01/01/1998 12:00
01/01/1998 15:00
01/01/1998 16:00
01/01/1998 18:00
01/01/1998 21:00
01/01/1998 22:00
01/02/1998 00:00
01/02/1998 03:00
01/02/1998 04:00
01/02/1998 06:00
01/02/1998 09:00
01/02/1998 12:00
01/02/1998 15:00
01/02/1998 18:00
01/02/1998 21:00
01/03/1998 00:00

that I believe is what you want.

You will have to adapt it to your particular application, supplying ‘A’ with leading zeros where necessary, so each numeric field is of the appropriate length. Otherwise, it won’t work.

[Damith]

Thanks. How can I change my "A" matrix to double. It's a char now.

[Sad Grad Student]

str2double(A) won't work?

[Damith]

@ Sad Grad Student. Nope it doesn't work.

[Star Strider]

@Damith — I would use the datenum function to convert them to double. My ‘DV’ variable already does that, so you can use it with the datenum output of your dates, and the unique call in my code should work the same. (There is always the potential problem of slight mismatches due to approximation errors in floating-point operations, but with the dates as you have defined them, that should not be a problem. The datenum representations of the hours should be unambiguous.)

My code then becomes:

D0 = datenum([1998 01 01]);
DT = 1/24;                          % Days/Hour
% DM = eomday(1998,01);               % Days In January
DM = 2;
DV = D0 + cumsum([0; ones(24*DM,1)*DT]);
DV = DV(1:3:end-1);

A= ['01/01/1998 00:00'
    '01/01/1998 04:00'
    '01/01/1998 06:00'
    '01/01/1998 10:00'
    '01/01/1998 12:00'
    '01/01/1998 16:00'
    '01/01/1998 18:00'
    '01/01/1998 22:00'
    '01/02/1998 00:00'
    '01/02/1998 04:00'
    '01/02/1998 06:00'
    '01/02/1998 12:00'
    '01/02/1998 18:00'
    '01/03/1998 00:00'];

A = unique([DV; datenum(A, 'mm/dd/yyyy HH:MM')]);
Result = datestr(A, 'mm/dd/yyyy HH:MM')

with the same ‘Result’ as before:

Result =

01/01/1998 00:00
01/01/1998 03:00
01/01/1998 04:00
01/01/1998 06:00
01/01/1998 09:00
01/01/1998 10:00
01/01/1998 12:00
01/01/1998 15:00
01/01/1998 16:00
01/01/1998 18:00
01/01/1998 21:00
01/01/1998 22:00
01/02/1998 00:00
01/02/1998 03:00
01/02/1998 04:00
01/02/1998 06:00
01/02/1998 09:00
01/02/1998 12:00
01/02/1998 15:00
01/02/1998 18:00
01/02/1998 21:00
01/03/1998 00:00

[Damith]

Star,

Thanks. I realized that there are lat,long coordinates attached to time.

Can you find me a way to do this,

https://www.mathworks.com/matlabcentral/answers/179517-read-excel-file-and-fill-rows

Thanks again.

[Star Strider]

My pleasure.

I considered that and I downloaded and tried to deal with the times and other data in that file, in the context of this question, but gave up. I wasn’t able to do it.

[Damith]

No worries. Thanks.

[Damith]

Star,

I have simplified the table now. Can you help me a way to this. I think it should be easy now.

https://www.mathworks.com/matlabcentral/answers/180349-select-rows-from-table-and-interpolate

Thanks in advance.