Assign NaN to specific rows(based on criteria) for multiple table variables

I want to assign NaN to (time-)table rows that match a criteria (T.Var1~=1). I would like to do this for specific variables in the table.

But instead of doing

T.Var2(T.Var1~=1)=NaN; % Note: My variables are not called Var1, Var2, ... but I simplified it here to these names ;-)
T.Var5(T.Var1~=1)=NaN;
%...   
T.Var10(T.Var1~=1)=NaN;
%...

I would like to do this in a shorter code. Is this possible?

I look for something like this:

T.{'Var2','Var5', 'Var7', 'Var10'}(T.Var1~=1)=NaN;

Answer 1

[Turlough Hughes]

You can do that as follows:

% Firstly some sample data
T = array2timetable(randi(3,10,5),'Rowtimes',datetime() + seconds(0:9))

T = 10×5 timetable
            Time            Var1    Var2    Var3    Var4    Var5
    ____________________    ____    ____    ____    ____    ____

    01-Feb-2022 16:14:43     2       2       1       1       1  
    01-Feb-2022 16:14:44     2       1       2       3       3  
    01-Feb-2022 16:14:45     3       2       2       2       3  
    01-Feb-2022 16:14:46     1       3       1       3       2  
    01-Feb-2022 16:14:47     1       3       2       2       3  
    01-Feb-2022 16:14:48     1       3       3       3       2  
    01-Feb-2022 16:14:49     3       3       1       1       1  
    01-Feb-2022 16:14:50     1       3       3       1       3  
    01-Feb-2022 16:14:51     2       2       1       1       3  
    01-Feb-2022 16:14:52     1       1       1       3       2  

% You can do it using column numbers
T{T.Var1==1,[2 4]} = nan 

T = 10×5 timetable
            Time            Var1    Var2    Var3    Var4    Var5
    ____________________    ____    ____    ____    ____    ____

    01-Feb-2022 16:14:43     2        2      1        1      1  
    01-Feb-2022 16:14:44     2        1      2        3      3  
    01-Feb-2022 16:14:45     3        2      2        2      3  
    01-Feb-2022 16:14:46     1      NaN      1      NaN      2  
    01-Feb-2022 16:14:47     1      NaN      2      NaN      3  
    01-Feb-2022 16:14:48     1      NaN      3      NaN      2  
    01-Feb-2022 16:14:49     3        3      1        1      1  
    01-Feb-2022 16:14:50     1      NaN      3      NaN      3  
    01-Feb-2022 16:14:51     2        2      1        1      3  
    01-Feb-2022 16:14:52     1      NaN      1      NaN      2  

% or using variable names
T{T.Var1==1,["Var3", "Var5"]} = nan

T = 10×5 timetable
            Time            Var1    Var2    Var3    Var4    Var5
    ____________________    ____    ____    ____    ____    ____

    01-Feb-2022 16:14:43     2        2       1       1       1 
    01-Feb-2022 16:14:44     2        1       2       3       3 
    01-Feb-2022 16:14:45     3        2       2       2       3 
    01-Feb-2022 16:14:46     1      NaN     NaN     NaN     NaN 
    01-Feb-2022 16:14:47     1      NaN     NaN     NaN     NaN 
    01-Feb-2022 16:14:48     1      NaN     NaN     NaN     NaN 
    01-Feb-2022 16:14:49     3        3       1       1       1 
    01-Feb-2022 16:14:50     1      NaN     NaN     NaN     NaN 
    01-Feb-2022 16:14:51     2        2       1       1       3 
    01-Feb-2022 16:14:52     1      NaN     NaN     NaN     NaN 

[Marcel345614]

Thanks a lot for this great answer!

One question: When I tried this by using ' instead of " Matlab created a variable with the name Var1Var3Var5 in a new column.

What is the difference between " and ' in Matlab??

[Turlough Hughes]

No problem. The single quotes are type char and the double quotes are type string. You can see why Var1Var3Var5 came up just by typing it into the command window:

idx = ['Var1' 'Var3' 'Var5']

idx = 'Var1Var3Var5'

whereas when you concatenate strings the result is different:

idx = ["Var1" "Var3" "Var5"]

idx = 1×3 string array
    "Var1"    "Var3"    "Var5"

You could also use a cell array of char's to index by the variable names:

T = array2timetable(randi(3,10,5),'Rowtimes',datetime() + seconds(0:9));
T{T.Var1==1,{'Var3', 'Var5'}} = nan

T = 10×5 timetable
            Time            Var1    Var2    Var3    Var4    Var5
    ____________________    ____    ____    ____    ____    ____

    01-Feb-2022 17:00:03     2       1        3      3        3 
    01-Feb-2022 17:00:04     2       3        1      2        1 
    01-Feb-2022 17:00:05     1       1      NaN      3      NaN 
    01-Feb-2022 17:00:06     3       1        3      2        2 
    01-Feb-2022 17:00:07     2       3        1      2        3 
    01-Feb-2022 17:00:08     2       1        1      2        1 
    01-Feb-2022 17:00:09     1       1      NaN      1      NaN 
    01-Feb-2022 17:00:10     1       3      NaN      3      NaN 
    01-Feb-2022 17:00:11     1       2      NaN      3      NaN 
    01-Feb-2022 17:00:12     1       1      NaN      1      NaN 

[Marcel345614]

Thanks a lot! Now I understand it.

Answer 2

[Benjamin Thompson]

If you are assigning from a scalar it seems you must do this column by column. Use an index vector to select which rows to assign:

T.Var1 = [1 2 3 4 1]'

T.Var2 = 2*ones(5,1)

T.Var3 = 5*ones(5,1)

T.Var4 = 10*ones(5,1)

I = T.Var1 ~= 1

T2.Var2(I) = NaN

T2.Var3(I) = NaN

T2.Var4(I) = NaN

See the MATLAB help article "Access Data in Tables" for more data reading and writing examples.

[Marcel345614]

Thanks for your answer!