Compare columns of different tables and extract value

3 visualizzazioni (ultimi 30 giorni)
Hello everyone.
I want to make a loop that reads all rows of a column in my first table (Tappmax_20062100.Daily_Tappmax_Rounded) and checks when a value becomes equal to a value in a column of my second table (RR.Tappmax). When this happens, I want to extract the respective RR value of the second table (RR.RR). Eg., the 1st value of Daily_Tappmax_Rounded (10.5) is equal to the 18th row of RR.Tappmax, and I want to extract the respetive RR (1.0237). In the opposite case, I want to move on to the next row of RR.Tappmax.
This is my script so far:
data = readtable('C:\PhD\ELSTAT\Data\Keppas future run\Thess_20062100.csv');
nearestValue = 0.5;
data.Daily_Tappmax_Rounded = round(data.Daily_Tappmax/nearestValue)*nearestValue;
Tappmax_20062100 = table(data.Daily_DATE, data.Daily_Tappmax_Rounded);
Tappmax_20062100.Properties.VariableNames{1} = 'Daily_DATE'; Tappmax_20062100.Properties.VariableNames{2} = 'Daily_Tappmax_Rounded';
RR = readtable('C:\PhD\ELSTAT\Data\Keppas future run\9.2 CLIMPACT\RR assessment.xlsx');
m=1;
for i = 1: length( Tappmax_20062100.Daily_Tappmax_Rounded)
if Tappmax_20062100.Daily_Tappmax_Rounded(i) == RR.Tappmax(m)
Tappmax_20062100.RR(i) = RR.RR(m)
else
m=m+1;
end
end
What is wrong with the loop? I was expecting it to work but I cannot find the mistake behind it...
PS. R2019a

Risposta accettata

Peter Perkins
Peter Perkins il 9 Mar 2022
This sounds like you need a join. Also, probably a timetable:
>> data = readtimetable('Thess_20062100.csv');
>> data.Daily_Tappmax_Rounded = round(2*data.Daily_Tappmax)/2 % NOT in your file
data =
3652×8 timetable
Daily_DATE time year month doy dow Daily_Tmean Daily_Tappmax Daily_Tappmax_Rounded
___________ ____ ____ _____ ___ _____________ ___________ _____________ _____________________
01-Jan-2006 NaN 2006 1 1 {'Sunday' } 11.469 10.325 10.5
02-Jan-2006 NaN 2006 1 2 {'Monday' } 9.288 9.9023 10
[snip]
30-Dec-2100 NaN 2100 12 364 {'Thursday' } 5.2816 9.6456 9.5
31-Dec-2100 NaN 2100 12 364 {'Friday' } 5.2816 9.6456 9.5
>> RR = readtable('RR assessment.xlsx')
RR =
84×2 table
Tappmax RR
_______ ______
2 1.0533
2.5 1.0516
[snip]
43 2.2321
43.5 2.3422
outerjoin makes what you want a one-liner. It sorts the result by the key, but easy to undo that:
>> data2 = outerjoin(data,RR,"LeftKeys","Daily_Tappmax_Rounded","RightKeys","Tappmax","RightVariables","RR");
>> data2 = sortrows(data2)
data2 =
3653×9 timetable
Daily_DATE time year month doy dow Daily_Tmean Daily_Tappmax Daily_Tappmax_Rounded RR
___________ ____ ____ _____ ___ _____________ ___________ _____________ _____________________ ______
01-Jan-2006 NaN 2006 1 1 {'Sunday' } 11.469 10.325 10.5 1.0237
02-Jan-2006 NaN 2006 1 2 {'Monday' } 9.288 9.9023 10 1.0254
[snip]
30-Dec-2100 NaN 2100 12 364 {'Thursday' } 5.2816 9.6456 9.5 1.0271
31-Dec-2100 NaN 2100 12 364 {'Friday' } 5.2816 9.6456 9.5 1.0271
NaT NaN NaN NaN NaN {0×0 char } NaN NaN NaN 2.3422
  1 Commento
Daphne PARLIARI
Daphne PARLIARI il 10 Mar 2022
That is a very elegant and effective way to do exactly what I want with much less fuss than my script! Thank you so much!!

Accedi per commentare.

Più risposte (0)

Categorie

Scopri di più su Tables in Help Center e File Exchange

Community Treasure Hunt

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

Start Hunting!

Translated by