Compare columns of different tables and extract value

1 view (last 30 days)
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

Accepted Answer

Peter Perkins
Peter Perkins on 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 Comment
Daphne PARLIARI
Daphne PARLIARI on 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!!

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!

Translated by