Compare columns of different tables and extract value

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

Answer 1

[Peter Perkins]

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

[Daphne PARLIARI]

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!!