Extracting values corresponding to exact date from a table and update it in another table on a same date

Hello,

I have two tables with different number of rows.

I want to extract the values from table "ntt" from "VarName4" column as shown below.

and paste it in column 2 of table "C" as shown below corresponding to the exact date as in table "ntt". As table "C" have other dates for which I don't have data and want to leave it blank or with zeros.

I treid to use for loop for updating a table "C"

for k = 1:height(ntt)

p = ntt{k,"Rainfallmm"};

C(p,2) = ntt{k,"VarName4"};

end

but it gives me this error

"A table row subscript must be a numeric array containing real positive integers, a logical array, a character vector, a string array, or a cell array of character vectors"

any help would be appreciated.

Thanking you in anticipation

Answer 1

[Seth Furman]

1) Convert your tables to timetables

This isn't strictly necessary to answer your question, but is generally a good idea when working with tabular timestamped data.

% Example tables
Date = datetime(2021, 1, 1:10:100)';
t1 = table(Date, (1:10)')

t1 = 10×2 table
       Date        Var2
    ___________    ____

    01-Jan-2021      1 
    11-Jan-2021      2 
    21-Jan-2021      3 
    31-Jan-2021      4 
    10-Feb-2021      5 
    20-Feb-2021      6 
    02-Mar-2021      7 
    12-Mar-2021      8 
    22-Mar-2021      9 
    01-Apr-2021     10 

Date = datetime(2021, 1, 1:5:100)';
t2 = table(Date)

t2 = 20×1 table
       Date    
    ___________

    01-Jan-2021
    06-Jan-2021
    11-Jan-2021
    16-Jan-2021
    21-Jan-2021
    26-Jan-2021
    31-Jan-2021
    05-Feb-2021
    10-Feb-2021
    15-Feb-2021
    20-Feb-2021
    25-Feb-2021
    02-Mar-2021
    07-Mar-2021
    12-Mar-2021
    17-Mar-2021

% Convert tables to timetables
t1 = table2timetable(t1)

t1 = 10×1 timetable
       Date        Var2
    ___________    ____

    01-Jan-2021      1 
    11-Jan-2021      2 
    21-Jan-2021      3 
    31-Jan-2021      4 
    10-Feb-2021      5 
    20-Feb-2021      6 
    02-Mar-2021      7 
    12-Mar-2021      8 
    22-Mar-2021      9 
    01-Apr-2021     10 

t2 = table2timetable(t2)

t2 =

  20×0 empty timetable

https://www.mathworks.com/help/matlab/ref/table2timetable.html

2) Take a look at the functions (join, innerjoin, and outerjoin)

I suspect that you'll want outerjoin in your case.

outerjoin(t1, t2, "Keys", "Date")

ans = 20×1 timetable
       Date        Var2
    ___________    ____

    01-Jan-2021      1 
    06-Jan-2021    NaN 
    11-Jan-2021      2 
    16-Jan-2021    NaN 
    21-Jan-2021      3 
    26-Jan-2021    NaN 
    31-Jan-2021      4 
    05-Feb-2021    NaN 
    10-Feb-2021      5 
    15-Feb-2021    NaN 
    20-Feb-2021      6 
    25-Feb-2021    NaN 
    02-Mar-2021      7 
    07-Mar-2021    NaN 
    12-Mar-2021      8 
    17-Mar-2021    NaN 

https://www.mathworks.com/help/matlab/ref/outerjoin.html

[Muhammad Haris Siddiqui]

Thank you so much.

worked flawless.

much appreciated