Interpolate missing hourly and daily data

Hello everyone,

I need your help because i'm stuck in this step. I want to do interpolation on my data. I have 16 years data and it's arranged like this:

Col 1: Year; Col 2: Month; Col 3: Day; Col 4: Hour; Col 5: Minutes; Col 6: Second; Col 7: Data

Since my data are missing both in Col 3 (Day) and Col 4 (Hour), I tried used interp1 (hour) but no return, but if i did interp1 (day) the results return, since i want to interpolate both of missing hour data and missing day data how can i do it?

I'm new in matlab, so i hope your answer will help me a lot, thank you!

Answer 1

[Star Strider]

Usee the retime function with a timetable —

filename = "Missing_data.xlsx";
T1 = readtable(filename);
T1.Properties.VariableNames = {'Year','Month','Day','Hour','Minute','Second','Data'}

T1 = 136755x7 table
    Year    Month    Day    Hour    Minute    Second     Data 
    ____    _____    ___    ____    ______    ______    ______

    2004      1       1       1       0         0       2.6066
    2004      1       1       2       0         0        2.602
    2004      1       1       3       0         0       2.5964
    2004      1       1       4       0         0       2.6042
    2004      1       1       5       0         0       2.6075
    2004      1       1       7       0         0       2.6224
    2004      1       1       8       0         0       2.6254
    2004      1       1       9       0         0        2.626
    2004      1       1      10       0         0       2.6165
    2004      1       1      11       0         0       2.6131
    2004      1       1      12       0         0       2.5956
    2004      1       1      13       0         0        2.584
    2004      1       1      14       0         0       2.5718
    2004      1       1      16       0         0       2.5286
    2004      1       1      17       0         0       2.5223
    2004      1       1      18       0         0       2.5122

CheckMinSec = [nnz(T1.Minute) nnz(T1.Second)]

CheckMinSec = 1×2

0 0

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

DateTime = datetime(T1{:,1:6}, Format="yyyy-MM-dd HH");
TT1 = timetable(DateTime,T1.Data);
TT1.Properties.VariableNames = {'Data'}

TT1 = 136755x1 timetable
      DateTime        Data 
    _____________    ______

    2004-01-01 01    2.6066
    2004-01-01 02     2.602
    2004-01-01 03    2.5964
    2004-01-01 04    2.6042
    2004-01-01 05    2.6075
    2004-01-01 07    2.6224
    2004-01-01 08    2.6254
    2004-01-01 09     2.626
    2004-01-01 10    2.6165
    2004-01-01 11    2.6131
    2004-01-01 12    2.5956
    2004-01-01 13     2.584
    2004-01-01 14    2.5718
    2004-01-01 16    2.5286
    2004-01-01 17    2.5223
    2004-01-01 18    2.5122

TT1r = retime(TT1,"hourly")

TT1r = 149033x1 timetable
      DateTime        Data 
    _____________    ______

    2004-01-01 01    2.6066
    2004-01-01 02     2.602
    2004-01-01 03    2.5964
    2004-01-01 04    2.6042
    2004-01-01 05    2.6075
    2004-01-01 06       NaN
    2004-01-01 07    2.6224
    2004-01-01 08    2.6254
    2004-01-01 09     2.626
    2004-01-01 10    2.6165
    2004-01-01 11    2.6131
    2004-01-01 12    2.5956
    2004-01-01 13     2.584
    2004-01-01 14    2.5718
    2004-01-01 15       NaN
    2004-01-01 16    2.5286

HourRowsAdded = height(TT1r) - height(TT1)

HourRowsAdded = 12278

figure
tiledlayout(2,1)
nexttile
plot(TT1.DateTime, TT1.Data)
grid
nexttile
plot(TT1r.DateTime, TT1r.Data)
grid

There are no minutes or seconds in the original, so I left them out.

.

[Adib Muhammad]

Thank you for your answer, i know the logic

[Star Strider]

My pleasure!

Answer 2

[Hitesh]

Hi @Adib Muhammad

You can use the“interp1”function to interpolate data at both hourly and daily intervals. The below-mentioned steps can help in achieving the same:

• Convert the Excel data into“datetime”format using the“datetime”function.
• Create a variable named“interpolationTimeRange”using the“datetime”function, which spans from January 1, 2004, to December 31, 2020, with hourly intervals, according to the data provided in the Excel file.
• Apply the“interp1”function with the formatted dates, formatted values, and the“interpolationTimeRange”to calculate interpolated values for the missing data.

Please refer to the below code and attached “Interpolated_Data.xlsx” file:

% Load the data from the Excel file
dataTable = readtable('Missing_data.xlsx');

% Create a datetime array using the specified columns
% Ensure that the column names match exactly with those in the Excel file
dateTimeArray = datetime(dataTable.Year, dataTable.Month, dataTable.Day, dataTable.Hour, dataTable.Minute, dataTable.Second);
valueArray = dataTable.Values;

% Create a new table with datetime and values
formattedData = table(dateTimeArray, valueArray, 'VariableNames', {'Time', 'Values'});

% Plot original data
plot(formattedData.Time, formattedData.Values, '*');

% Define the query time range for interpolation
interpolationTimeRange = (datetime(2004,1,1):hours(1):datetime(2020,12,31))';

% Perform interpolation using spline method
interpolatedValues = interp1(formattedData.Time, formattedData.Values, interpolationTimeRange, 'spline');

% Plot the interpolated data
hold on;
plot(interpolationTimeRange, interpolatedValues, 'r');

% Extract components from the interpolation time range
yearArray = year(interpolationTimeRange);
monthArray = month(interpolationTimeRange);
dayArray = day(interpolationTimeRange);
hourArray = hour(interpolationTimeRange);
minuteArray = minute(interpolationTimeRange);
secondArray = second(interpolationTimeRange);

% Create a new table with the extracted components and interpolated values
interpolatedData = table(yearArray, monthArray, dayArray, hourArray, minuteArray, secondArray, interpolatedValues, ...
                         'VariableNames', {'Year', 'Month', 'Day', 'Hour', 'Minute', 'Second', 'InterpolatedValues'});

% Write the new table to an Excel file
writetable(interpolatedData, 'Interpolated_Data.xlsx');

For more information on “interp1” function, refer to the below MATLAB documentation:

https://www.mathworks.com/help/matlab/ref/double.interp1.html#bvkp4l9:~:text=%27%2Db%27)%3B-,Interpolation%20of%20Dates%20and%20Times,-Extrapolation%20Using%20Two

[Adib Muhammad]

Thank you very much for your response, i know the mistake at my code!