How to have a loop-for to filling NaN rows?
2 views (last 30 days)
Show older comments
I want to fill NaNs in a certain column of tables in C.mat, using a linear regression equation with regards to the information that stored in stationList.mat and other C.mat tables. In fact, I require to fill NaN rows in the tmax_m column (at C.mat tables which I attached it) of each station, using a similar row in other tables in C that closest_station (second column of stationList.mat) visualized it and corresponding slope and y-intercept in a similar month (that presents in stationList.mat).
For example:
in C{1, 1}, which station_name is Abadan, All NaN rows should be filled using similar row in Bandare-E-Mahshahr (as it displayed in stationList -second column, first row-) and this linear regression coefficients:
rowIdx = strcmp(stationList.station_name,'Abadan') & strcmp(stationList.closest_station,'Bandar-E-Mahshahr');
coeffs = stationList.Feb(rowIdx,:); %output is m (slope) and intercept respectively
NaN in Ahvaz = slope x similar row in Bandar-E-Mahshahr + intercept
I saw that this item can be done manually that I achieve linear regression coefficients using above-mentioned code then look at a corresponding row in nearest_station and use NaN in Ahvaz = slope x similar row in Bandar-E-Mahshahr + intercept to manually fill NaN, but as you know this process is so many time-consuming
I want to run this process for all C.mat tables. I was searching for a solution for about 3 hours until now but I can't do it. So any help, any advice is appreciated.
Thank you in advance.
2 Comments
Adam Danz
on 28 Jan 2020
C is a 1x71 cell array of tables. Did you mean to provide the nx12 cell array of tables broken down by month? Otherwise, which coefficients are we supposed to use (from which month)?
Accepted Answer
Adam Danz
on 28 Jan 2020
Edited: Adam Danz
on 28 Jan 2020
The best approach would be to do this within the j-loop from this answer (you just need to change 1 line and add 3 lines).
The top part of the loop below is unchanged. The bottom part computes the regression coefficients and then computes the estimate of the missing values in table T1 based on the values in T2. This assumes that the rows in T2 correspond with the rows in T1. Fortunately all of the pairs of tables in your data have matching date columns so this simple method is safe. An assumption-check is performed to verify this and if you ever run any data where the date columns between the two tables do not match, an error will be thrown.
After filling the missing values in T1, that table is loaded back into the Cmo array.
% Loop through the selected months
for j = 1:numel(months)
% Extract tables for given station and month
T1 = Cmo{rowIdx1, months(j)};
T2 = Cmo{rowIdx2, months(j)};
% Remove missing values and compute linear reg coeffs.
nanIdx = isnan(T1.tmax_m) | isnan(T2.tmax_m);
%********** UPDATED / NEW SECTION BELOW *****************
coefs = polyfit(T1.tmax_m(~nanIdx), T2.tmax_m(~nanIdx), 1);
stationList.(monthNames{j})(i,:) = coefs;
% This simple approach assumes the rows of T1 are from the
% same dates as the rows of T2. Here we check that assumption.
% If this assumption-check ever fails, a more rigorous approach
% will be needed that matches the rows by date.
assert(isequal(T1.date,T2.date),'Assumption violation: dates do not match between tables.')
% Compute missing values (if the values in T2 are also missing, it will return NaN.
T1.tmax_m(nanIdx) = coefs(1) * T2.tmax_m(nanIdx) + coefs(2);
% Update the original Cmo array of nx12 tables.
Cmo{rowIdx1, months(j)} = T1;
end
4 Comments
Adam Danz
on 28 Jan 2020
Edited: Adam Danz
on 28 Jan 2020
I see what you mean; good investigation!
You only want to fill the missing values in T1 if the values aren't missing in T2, correct?
If so, here's what you can do.
1) in addition to the nanIdx variable, you can make a second logical index identifying the rows where T1 has a missing value and T2 does not.
T1FillIdx = isnan(T1.tmax_m) & ~isnan(T2.tmax_m);
2) Then you can use that logical index in this line.
T1.tmax_m(T1FillIdx) = coefs(1) * T2.tmax_m(T1FillIdx) + coefs(2);
% ^^^^^^^^^^ ^^^^^^^^^
Let's see if that does the trick for you.
More Answers (0)
See Also
Categories
Find more on Matrices and Arrays in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!