How to have a loop-for to filling NaN rows?

9 vues (au cours des 30 derniers jours)
BN
BN le 28 Jan 2020
Commenté : BN le 29 Jan 2020
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 commentaires
Adam Danz
Adam Danz le 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)?
BN
BN le 28 Jan 2020
Dear Dr. Adam Danz,
I'm very embarrassed about taking your time here. You are right. Is there any way to fill NaNs in Cmo (instead of C), and then converting the Cmo to 1x71 cell array of tables (just like C)?
Thank you

Connectez-vous pour commenter.

Réponse acceptée

Adam Danz
Adam Danz le 28 Jan 2020
Modifié(e) : Adam Danz le 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 commentaires
Adam Danz
Adam Danz le 28 Jan 2020
Modifié(e) : Adam Danz le 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.
BN
BN le 29 Jan 2020
Thank you.

Connectez-vous pour commenter.

Plus de réponses (0)

Catégories

En savoir plus sur Matrices and Arrays dans Help Center et File Exchange

Produits


Version

R2018b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by