Compare columns of different tables and extract value

1 次查看(过去 30 天)
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

采纳的回答

Peter Perkins
Peter Perkins 2022-3-9
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 个评论
Daphne PARLIARI
Daphne PARLIARI 2022-3-10
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!!

请先登录,再进行评论。

更多回答(0 个)

类别

Help CenterFile Exchange 中查找有关 Tables 的更多信息

Community Treasure Hunt

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

Start Hunting!

Translated by