changing categorical binary values to integer 1 and 0 - beginner

I got confused with this task.

I have this table to be unstacked

time        sensors      readings   
____________    _______    _____________

03:09:19.169      401      PAYLOAD=ON   
03:09:28.744      402      PAYLOAD=OPEN 
03:09:37.315      402      PAYLOAD=CLOSE
03:09:48.707      402      PAYLOAD=OPEN 
03:10:04.372      402      PAYLOAD=CLOSE
03:10:16.172      401      PAYLOAD=OFF  
03:10:24.906      601      PAYLOAD=ON   
03:10:36.445      604      PAYLOAD=OPEN 
03:10:50.204      604      PAYLOAD=CLOSE
03:10:59.623      603      PAYLOAD=ON   
03:11:07.427      605      PAYLOAD=OPEN 
03:11:26.276      605      PAYLOAD=CLOSE
03:11:34.341      602      PAYLOAD=ON   
03:11:56.176      602      PAYLOAD=OFF  
03:12:18.705      603      PAYLOAD=OFF  
03:12:28.055      604      PAYLOAD=OPEN 
03:12:35.646      604      PAYLOAD=CLOSE
03:12:47.880      601      PAYLOAD=OFF  

When I unstack it appears like that

time           x401            x402            x601           x602           x603            x604             x605     
____________    ___________    _____________    ___________    ___________    ___________    _____________    _____________

03:09:19.169    PAYLOAD=ON     <undefined>      <undefined>    <undefined>    <undefined>    <undefined>      <undefined>  
03:09:28.744    <undefined>    PAYLOAD=OPEN     <undefined>    <undefined>    <undefined>    <undefined>      <undefined>  
03:09:37.315    <undefined>    PAYLOAD=CLOSE    <undefined>    <undefined>    <undefined>    <undefined>      <undefined>  
03:09:48.707    <undefined>    PAYLOAD=OPEN     <undefined>    <undefined>    <undefined>    <undefined>      <undefined>  
03:10:04.372    <undefined>    PAYLOAD=CLOSE    <undefined>    <undefined>    <undefined>    <undefined>      <undefined>  
03:10:16.172    PAYLOAD=OFF    <undefined>      <undefined>    <undefined>    <undefined>    <undefined>      <undefined>  
03:10:24.906    <undefined>    <undefined>      PAYLOAD=ON     <undefined>    <undefined>    <undefined>      <undefined>  
03:10:36.445    <undefined>    <undefined>      <undefined>    <undefined>    <undefined>    PAYLOAD=OPEN     <undefined>  
03:10:50.204    <undefined>    <undefined>      <undefined>    <undefined>    <undefined>    PAYLOAD=CLOSE    <undefined>  
03:10:59.623    <undefined>    <undefined>      <undefined>    <undefined>    PAYLOAD=ON     <undefined>      <undefined>  
03:11:07.427    <undefined>    <undefined>      <undefined>    <undefined>    <undefined>    <undefined>      PAYLOAD=OPEN 
03:11:26.276    <undefined>    <undefined>      <undefined>    <undefined>    <undefined>    <undefined>      PAYLOAD=CLOSE
03:11:34.341    <undefined>    <undefined>      <undefined>    PAYLOAD=ON     <undefined>    <undefined>      <undefined>  
03:11:56.176    <undefined>    <undefined>      <undefined>    PAYLOAD=OFF    <undefined>    <undefined>      <undefined>  
03:12:18.705    <undefined>    <undefined>      <undefined>    <undefined>    PAYLOAD=OFF    <undefined>      <undefined>  
03:12:28.055    <undefined>    <undefined>      <undefined>    <undefined>    <undefined>    PAYLOAD=OPEN     <undefined>  
03:12:35.646    <undefined>    <undefined>      <undefined>    <undefined>    <undefined>    PAYLOAD=CLOSE    <undefined>  
03:12:47.880    <undefined>    <undefined>      PAYLOAD=OFF    <undefined>    <undefined>    <undefined>      <undefined> ç

I want to replace categorical values (PAYLOAD=ON, PAYLOAD=OPEN) and (PAYLOAD=OFF, PAYLOAD=CLOSE) by values 1 and 0.

Also, I want to replace every undefined cell by 1 or 0 according to the previous cell.

[Image Analyst]

Please attach your data table in a .mat file with the paperclip icon after you read this:

TUTORIAL: How to ask a question (on Answers) and get a fast answer

so that we can try stuff iwth it.

Answer 1

[Cris LaPierre]

First, use mergecats to combine categories that will share the same category name. You can then use renamecats to rename your category labels if necessary. However, calling double returns the category number, not the value. See this answer for a workaround (implemented below): https://www.mathworks.com/matlabcentral/answers/394456-how-to-convert-from-categorical-to-double

You will get an error using the workaround if your categorical contains <undefined> values, so do this before unstacking.

readings = categorical(["PAYLOAD=ON"; "PAYLOAD=OPEN"; "PAYLOAD=CLOSE"; "PAYLOAD=OPEN";"PAYLOAD=CLOSE";"PAYLOAD=OFF";"PAYLOAD=ON";"PAYLOAD=OPEN";"PAYLOAD=CLOSE"])

readings = 9×1 categorical array
     PAYLOAD=ON 
     PAYLOAD=OPEN 
     PAYLOAD=CLOSE 
     PAYLOAD=OPEN 
     PAYLOAD=CLOSE 
     PAYLOAD=OFF 
     PAYLOAD=ON 
     PAYLOAD=OPEN 
     PAYLOAD=CLOSE 

categories(readings)

ans = 4×1 cell array
    {'PAYLOAD=CLOSE'}
    {'PAYLOAD=OFF'  }
    {'PAYLOAD=ON'   }
    {'PAYLOAD=OPEN' }

readings = mergecats(readings,{'PAYLOAD=ON', 'PAYLOAD=OPEN'},'1');
readings = mergecats(readings,{'PAYLOAD=OFF', 'PAYLOAD=CLOSE'},'0');
categories(readings)

ans = 2×1 cell array
    {'0'}
    {'1'}

% do not need to use renamecats.
% Convert to double
x = str2double(categories(readings))

x = 2×1

0 1

readings = x(readings)

readings = 9×1

1 1 0 1 0 0 1 1 0

[Cris LaPierre]

Here's an example using the sample data you shared above.

% Load and format data
data = readtable("readings.xlsx");
data.readings = categorical(data.readings);
data.time = duration(data.time,'format','hh:mm:ss.SSS')

data = 18×3 table
        time        sensors      readings   
    ____________    _______    _____________

    03:09:19.169      401      PAYLOAD=ON   
    03:09:28.744      402      PAYLOAD=OPEN 
    03:09:37.315      402      PAYLOAD=CLOSE
    03:09:48.707      402      PAYLOAD=OPEN 
    03:10:04.372      402      PAYLOAD=CLOSE
    03:10:16.172      401      PAYLOAD=OFF  
    03:10:24.906      601      PAYLOAD=ON   
    03:10:36.445      604      PAYLOAD=OPEN 
    03:10:50.204      604      PAYLOAD=CLOSE
    03:10:59.623      603      PAYLOAD=ON   
    03:11:07.427      605      PAYLOAD=OPEN 
    03:11:26.276      605      PAYLOAD=CLOSE
    03:11:34.341      602      PAYLOAD=ON   
    03:11:56.176      602      PAYLOAD=OFF  
    03:12:18.705      603      PAYLOAD=OFF  
    03:12:28.055      604      PAYLOAD=OPEN 

% Convert sensor readings to 0/1
data.readings = mergecats(data.readings,{'PAYLOAD=ON', 'PAYLOAD=OPEN'},'1');
data.readings = mergecats(data.readings,{'PAYLOAD=OFF', 'PAYLOAD=CLOSE'},'0');
x = str2double(categories(data.readings));
data.readings = x(data.readings)

data = 18×3 table
        time        sensors    readings
    ____________    _______    ________

    03:09:19.169      401         1    
    03:09:28.744      402         1    
    03:09:37.315      402         0    
    03:09:48.707      402         1    
    03:10:04.372      402         0    
    03:10:16.172      401         0    
    03:10:24.906      601         1    
    03:10:36.445      604         1    
    03:10:50.204      604         0    
    03:10:59.623      603         1    
    03:11:07.427      605         1    
    03:11:26.276      605         0    
    03:11:34.341      602         1    
    03:11:56.176      602         0    
    03:12:18.705      603         0    
    03:12:28.055      604         1    

% Unstack
unstack(data,"readings","sensors")

Warning: Table variable names that were not valid MATLAB identifiers have been modified. Since table variable names must be unique, any table variable names that happened to match the new identifiers also have been modified.
To use the original INDVAR values as table variable names, set 'VariableNamingRule' to 'preserve'.

ans = 18×8 table
        time        x401    x402    x601    x602    x603    x604    x605
    ____________    ____    ____    ____    ____    ____    ____    ____

    03:09:19.169      1     NaN     NaN     NaN     NaN     NaN     NaN 
    03:09:28.744    NaN       1     NaN     NaN     NaN     NaN     NaN 
    03:09:37.315    NaN       0     NaN     NaN     NaN     NaN     NaN 
    03:09:48.707    NaN       1     NaN     NaN     NaN     NaN     NaN 
    03:10:04.372    NaN       0     NaN     NaN     NaN     NaN     NaN 
    03:10:16.172      0     NaN     NaN     NaN     NaN     NaN     NaN 
    03:10:24.906    NaN     NaN       1     NaN     NaN     NaN     NaN 
    03:10:36.445    NaN     NaN     NaN     NaN     NaN       1     NaN 
    03:10:50.204    NaN     NaN     NaN     NaN     NaN       0     NaN 
    03:10:59.623    NaN     NaN     NaN     NaN       1     NaN     NaN 
    03:11:07.427    NaN     NaN     NaN     NaN     NaN     NaN       1 
    03:11:26.276    NaN     NaN     NaN     NaN     NaN     NaN       0 
    03:11:34.341    NaN     NaN     NaN       1     NaN     NaN     NaN 
    03:11:56.176    NaN     NaN     NaN       0     NaN     NaN     NaN 
    03:12:18.705    NaN     NaN     NaN     NaN       0     NaN     NaN 
    03:12:28.055    NaN     NaN     NaN     NaN     NaN       1     NaN 

[Ahmad]

Thank you, I appreciate it.

just one more question, how can I replace NaN by double(1 and 0) according to the previous reading? I mean that replacing all NaN following a reading of 1 until I reach to a reading of 0.

[Cris LaPierre]

Use fillmissing with this syntax:

• F = fillmissing(A,method)

Specify the method to be "previous"