Finding out the missing dates and times from the given time series data

I'm having an .csv file (5minutes interval rainfall data) comprises of dates and times (eg. 01-01-2015 00:00:00,01-01-2015 00:00:05,01-01-2015 00:00:10) with an interval of 5 minutes arranged in the column. The problem is there are some dates and times missing in that column. I need to determine those missing dates & times and save it in another .csv file in ascending order. I'm attaching the .csv file.

Answer 1

[Imran]

Hello Manikandan,

I understand that you want to determine the missing dates & times from your .csv file and save it in another .csv file in ascending order.

The algorithm is as follows:

Algorithm:

1. Import the content of the file in a table.
2. Traverse the table and at every iteration consider the current and the next timestamp.
3. If the difference between two consecutive timestamps is not equals to 5 minutes, there are some missing timestamps. So, keep incrementing by 5 minutes and store the missing timestamps in an array.
4. Finally create a .csv file with the array that contains missing timestamps.

The following code implements the algorithm:-

% Import the csv file 
h = readtable('Rainfall.csv');

% Create a string array which will store the missing date and time
missingData = strings(1,1);

% Traverse the whole table
for i = 1:size(h,1)-1
    % After storing the content of csv file in table format, the first
    % column of every row gets stored as cell array.
    % In order to perform operation, we need to convert it to datetime
    % format
    currTime = datetime(h{i,1},'InputFormat','dd-MM-yyyy HH:mm');
    nextTime = datetime(h{i+1,1},'InputFormat','dd-MM-yyyy HH:mm');
    
    % If the difference between two consecutive timestamps is not equals to
    % 5 minutes, there are missing date and time according to the
    % definition
    if nextTime - currTime ~= duration(0,5,0)
        currTime = currTime + duration(0,5,0);
        % So keep incrementing by 5 minutes until the next timestamp is
        % reached and store them in missingData array.
        while currTime ~= nextTime
            missingData(end+1) = string(currTime);
            currTime = currTime + duration(0,5,0);
        end
    end
end

% Remove the first element as it is an empty string and unnecessary
missingData(1) = [];

% Finally create a csv file from the missing data
writematrix(missingData','ultimate.csv')

I hope this helps.