Cell and Double Column Vectors

A table consists of a series of Variables (Columns). Let us first create these columns individually.

Names (Cell)

Names=<span style="color: #666699;">{</span><span style="color: #800080;">'</span>Simon<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>Lisa<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>Peter<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>John<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>Claire<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>Ashley<span style="color: #800080;">'</span><span style="color: #666699;">}</span>

$\displaystyle \text{Names}=\left\{ {\begin{array}{*{20}{c}} {\text{ }\!\!'\!\!\text{ Simon }\!\!'\!\!\text{ }} \\ {'\text{Lisa }\!\!'\!\!\text{ }} \\ {'\text{Peter }\!\!'\!\!\text{ }} \\ {'\text{John }\!\!'\!\!\text{ }} \\ {'\text{Claire }\!\!'\!\!\text{ }} \\ {'\text{Ashley }\!\!'\!\!\text{ }} \end{array}} \right\}$

<span style="color: #0000ff;">class(</span>Names<span style="color: #0000ff;">)</span>

Returns a cell.

Test1Results (Double)

Test1Results=<span style="color: #ff0000;">[</span>62<span style="color: #ff00ff;">;</span>73<span style="color: #ff00ff;">;</span>73<span style="color: #ff00ff;">;</span>73<span style="color: #ff00ff;">;</span>55<span style="color: #ff00ff;">;</span>44<span style="color: #ff0000;">]</span>

$\displaystyle \text{Test1Results}=\left[ {\begin{array}{*{20}{c}} {85} \\ {73} \\ {73} \\ {73} \\ {55} \\ {64} \end{array}} \right]$

<span style="color: #0000ff;">class(</span>Test1Results<span style="color: #0000ff;">)</span>

Returns a double meaning each number is stored using 64 bits.

Test2Results

Test2Results=<span style="color: #ff0000;">[</span>62<span style="color: #ff00ff;">;</span>74<span style="color: #ff00ff;">;</span>75<span style="color: #ff00ff;">;</span>74<span style="color: #ff00ff;">;</span>55<span style="color: #ff00ff;">;</span>45<span style="color: #ff0000;">]</span>

$\displaystyle \text{Test2Results}=\left[ {\begin{array}{*{20}{c}} {62} \\ {74} \\ {75} \\ {74} \\ {95} \\ {77} \end{array}} \right]$

<span style="color: #0000ff;">class(</span>Test2Results<span style="color: #0000ff;">)</span>

Returns a double meaning each number is stored using 64 bits.

Test3Results

Test3Results=<span style="color: #ff0000;">[</span>63<span style="color: #ff00ff;">;</span>77<span style="color: #ff00ff;">;</span>77<span style="color: #ff00ff;">;</span>83<span style="color: #ff00ff;">;</span>56<span style="color: #ff00ff;">;</span>40<span style="color: #ff0000;">]</span>

$\displaystyle \text{Test3Results}=\left[ {\begin{array}{*{20}{c}} {85} \\ {77} \\ {77} \\ {83} \\ {90} \\ {67} \end{array}} \right]$

<span style="color: #0000ff;">class(</span>Test3Results<span style="color: #0000ff;">)</span>

Returns a double meaning each number is stored using 64 bits.

DateEnrolled

DateEnrolled=<span style="color: #666699;">{</span><span style="color: #800080;">'</span>11/01/2019<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>21/01/2019<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>15/01/2019<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>17/01/2019<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>22/01/2019<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>24/01/2019<span style="color: #800080;">'</span><span style="color: #666699;">}</span>

$\displaystyle \text{DateEnrolled}=\left[ {\begin{array}{*{20}{c}} {11/01/2019} \\ {21/01/2019} \\ {15/01/2019} \\ {17/01/2019} \\ {22/01/2019} \\ {24/01/2019} \end{array}} \right]$

<span style="color: #0000ff;">class(</span>DateEnrolled<span style="color: #0000ff;">)</span>

Returns a cell. Although this is in a date time format, it is input as a Cell Array (this will be amended later).

Creating a Table

You'll notice that we have created:

• Names (6 by 1 Cell)
• Test1Results (6 by 1 Double)
• Test2Results (6 by 1 Double)
• Test3Results (6 by 1 Double)
• DateEnrolled (6 by 1 Cell)

As these have an equal number of Rows (6) we can create a Table using these variables:

DataTable=<span style="color: #0000ff;">table(</span>Names<span style="color: #808080;">,</span>Test1Results<span style="color: #808080;">,</span>Test2Results<span style="color: #808080;">,</span>Test3Results<span style="color: #808080;">,</span>DateEnrolled<span style="color: #0000ff;">)</span>

$\displaystyle \begin{array}{*{20}{r}} {\text{Names}} & {\text{Test1Results}} & {\text{Test2Results}} & {\text{Test3Results}} & {\text{DateEnrolled}} \\ {\text{ }\!\!'\!\!\text{ Simon }\!\!'\!\!\text{ }} & {85} & {62} & {63} & {11/01/2019} \\ {\text{ }\!\!'\!\!\text{ Lisa }\!\!'\!\!\text{ }} & {73} & {74} & {77} & {21/01/2019} \\ {\text{ }\!\!'\!\!\text{ Peter }\!\!'\!\!\text{ }} & {73} & {75} & {77} & {15/01/2019} \\ {\text{ }\!\!'\!\!\text{ John }\!\!'\!\!\text{ }} & {73} & {74} & {83} & {17/01/2019} \\ {\text{ }\!\!'\!\!\text{ Claire }\!\!'\!\!\text{ }} & {55} & {55} & {56} & {22/01/2019} \\ {\text{ }\!\!'\!\!\text{ Ashley }\!\!'\!\!\text{ }} & {44} & {45} & {40} & {24/01/2019} \end{array}$

Looking Up Properties

We can use dot indexing to lookup the table Properties. To the left hand side we have the Table Name we want to lookup and to the right hand side we have the Properties:

DataTable<span style="color: #ff0000;">.</span>Properties

If we want to lookup the Variable Names we can use:

DataTable<span style="color: #ff0000;">.</span>Properties<span style="color: #ff0000;">.</span>VariableNames

The output is a Cell Array. If we want to update it we can type in the following:

DataTable<span style="color: #ff0000;">.</span>Properties<span style="color: #ff0000;">.</span>VariableNames={'Names','Test1','Test2','Test3','DateStarted'}

$\displaystyle \begin{array}{*{20}{r}} {\text{Names}} & {\text{Test1}} & {\text{Test2}} & {\text{Test3}} & {\text{DateStarted}} \\ {\text{ }\!\!'\!\!\text{ Simon }\!\!'\!\!\text{ }} & {85} & {62} & {63} & {11/01/2019} \\ {\text{ }\!\!'\!\!\text{ Lisa }\!\!'\!\!\text{ }} & {73} & {74} & {77} & {21/01/2019} \\ {\text{ }\!\!'\!\!\text{ Peter }\!\!'\!\!\text{ }} & {73} & {75} & {77} & {15/01/2019} \\ {\text{ }\!\!'\!\!\text{ John }\!\!'\!\!\text{ }} & {73} & {74} & {83} & {17/01/2019} \\ {\text{ }\!\!'\!\!\text{ Claire }\!\!'\!\!\text{ }} & {55} & {55} & {56} & {22/01/2019} \\ {\text{ }\!\!'\!\!\text{ Ashley }\!\!'\!\!\text{ }} & {44} & {45} & {40} & {24/01/2019} \end{array}$

Selecting a Variable (Column)

In a Table, each Variable becomes a Column. The above Table therefore consists of 5 Variables which can be accessed using dot indexing:

DataTable<span style="color: #ff0000;">.</span>Names
DataTable<span style="color: #ff0000;">.</span>Test1
DataTable<span style="color: #ff0000;">.</span>Test2
DataTable<span style="color: #ff0000;">.</span>Test3
DataTable<span style="color: #ff0000;">.</span>DateStarted

Sorting by Value

Let's sort DataTable alphabetically by the Variable 'Names'

DataTable=<span style="color: #0000ff;">sortrows(</span>DataTable<span style="color: #808080;">,</span><span style="color: #800080;">'</span>Names<span style="color: #800080;">'</span><span style="color: #808080;">,</span><span style="color: #800080;">'</span>ascend<span style="color: #800080;">'</span><span style="color: #0000ff;">)</span>

Here we are updating DataTable so it is used both as an input and output of the function

<span style="color: #0000ff;">sortrows</span>

. The second input is the input which we will use to sort the results and the third input is the direction we wish to sort the results out by, in this case we will sort alphabetically with A at the Top and Z at the bottom so we will use 'ascend'

$\displaystyle \begin{array}{*{20}{r}} {\text{Names}} & {\text{Test1}} & {\text{Test2}} & {\text{Test3}} & {\text{DateStarted}} \\ {\text{ }\!\!'\!\!\text{ Ashley }\!\!'\!\!\text{ }} & {44} & {45} & {40} & {24/01/2019} \\ {\text{ }\!\!'\!\!\text{ Claire }\!\!'\!\!\text{ }} & {55} & {55} & {56} & {22/01/2019} \\ {\text{ }\!\!'\!\!\text{ John }\!\!'\!\!\text{ }} & {73} & {74} & {83} & {17/01/2019} \\ {\text{ }\!\!'\!\!\text{ Lisa }\!\!'\!\!\text{ }} & {73} & {74} & {77} & {21/01/2019} \\ {\text{ }\!\!'\!\!\text{ Peter }\!\!'\!\!\text{ }} & {73} & {75} & {77} & {15/01/2019} \\ {\text{ }\!\!'\!\!\text{ Simon }\!\!'\!\!\text{ }} & {62} & {62} & {63} & {11/01/2019} \end{array}$

Show this using MATLABs what you see is what you get interface.

Let's first create a new table DataTable2 which sorts the data by three layers the first layer being the value of Test1, the second being the value of Test2 and the third being the value of Test3. If one was using Excel, they would use Sort with the drop downs below.

In MATLAB we can use the

<span style="color: #0000ff;">sortrows</span>

function again but this time with a cell array as the first input. This time we will create a new table as the output:

DataTable2=<span style="color: #0000ff;">sortrows(</span>DataTable<span style="color: #808080;">,</span><span style="color: #666699;">{</span><span style="color: #800080;">'</span>Test1<span style="color: #800080;">'</span><span style="color: #808080;">,</span><span style="color: #800080;">'</span>Test2<span style="color: #800080;">'</span><span style="color: #808080;">,</span><span style="color: #800080;">'</span>Test3<span style="color: #800080;">'</span><span style="color: #666699;">}</span><span style="color: #808080;">,</span><span style="color: #800080;">'</span>descend<span style="color: #800080;">'</span><span style="color: #0000ff;">)</span>

$\displaystyle \begin{array}{*{20}{r}} {\text{Names}} & {\text{Test1}} & {\text{Test2}} & {\text{Test3}} & {\text{DateStarted}} \\ {\text{ }\!\!'\!\!\text{ Peter }\!\!'\!\!\text{ }} & \mathbf{{73}} & \mathbf{{75}} & {77} & {15/01/2019} \\ {\text{ }\!\!'\!\!\text{ John }\!\!'\!\!\text{ }} & \mathbf{{73}} & \mathbf{{74}} & \mathbf{{83}} & {17/01/2019} \\ {\text{ }\!\!'\!\!\text{ Lisa }\!\!'\!\!\text{ }} & \mathbf{{73}} & \mathbf{{74}} & {77} & {21/01/2019} \\ {\text{ }\!\!'\!\!\text{ Simon }\!\!'\!\!\text{ }} & {62} & {72} & {62} & {11/01/2019} \\ {\text{ }\!\!'\!\!\text{ Claire }\!\!'\!\!\text{ }} & {55} & {55} & {56} & {22/01/2019} \\ {\text{ }\!\!'\!\!\text{ Ashley }\!\!'\!\!\text{ }} & {44} & {45} & {40} & {24/01/2019} \end{array}$

Operating on a Variable (Column)

The last column is a series of strings but we want them to be dates, we can convert them to dates using the function

<span style="color: #0000ff;">datetime</span>
DataTable2<span style="color: #ff0000;">.</span>DateStarted=<span style="color: #0000ff;">datetime(</span>DataTable2<span style="color: #ff0000;">.</span>DateStarted<span style="color: #0000ff;">)</span>

$\displaystyle \begin{array}{*{20}{r}} {\text{Names}} & {\text{Test1}} & {\text{Test2}} & {\text{Test3}} & {\text{DateStarted}} \\ {\text{ }\!\!'\!\!\text{ Peter }\!\!'\!\!\text{ }} & {73} & {75} & {77} & {\text{15-Jan-2019}} \\ {\text{ }\!\!'\!\!\text{ John }\!\!'\!\!\text{ }} & {73} & {74} & {83} & {\text{17-Jan-2019}} \\ {\text{ }\!\!'\!\!\text{ Lisa }\!\!'\!\!\text{ }} & {73} & {74} & {77} & {\text{21-Jan-2019}} \\ {\text{ }\!\!'\!\!\text{ Simon }\!\!'\!\!\text{ }} & {62} & {72} & {62} & {\text{11-Jan-2019}} \\ {\text{ }\!\!'\!\!\text{ Claire }\!\!'\!\!\text{ }} & {55} & {55} & {56} & {\text{22-Jan-2019}} \\ {\text{ }\!\!'\!\!\text{ Ashley }\!\!'\!\!\text{ }} & {44} & {45} & {40} & {\text{24-Jan-2019}} \end{array}$

Adding a Column to the Table

Supposing we want to calculate the average of the three tests and add it to a Column at the end of the table:

DataTable2<span style="color: #ff0000;">.</span>MeanTestResults=<span style="color: #0000ff;">mean(</span><span style="color: #ff0000;">[</span>DataTable2<span style="color: #ff0000;">.</span>Test1<span style="color: #808080;">,</span>DataTable2<span style="color: #ff0000;">.</span>Test2<span style="color: #808080;">,</span>DataTable2<span style="color: #ff0000;">.</span>Test3<span style="color: #ff0000;">]</span><span style="color: #808080;">,</span>2<span style="color: #0000ff;">)</span>

The first input of the function mean is the three Variables selected using dot indexing

<span style="color: #ff0000;">[</span>DataTable2<span style="color: #ff0000;">.</span>Test1<span style="color: #808080;">,</span>DataTable2<span style="color: #ff0000;">.</span>Test2<span style="color: #808080;">,</span>DataTable2<span style="color: #ff0000;">.</span>Test3<span style="color: #ff0000;">]</span>

and the second input is the direction 2 meaning we act on Rows opposed to Columns.

The output is a new column

DataTable2<span style="color: #ff0000;">.</span>MeanTestResults

which by default is added to the end of the Table.

$\displaystyle \begin{array}{*{20}{r}} {\text{Names}} & {\text{Test1}} & {\text{Test2}} & {\text{Test3}} & {\text{DateStarted}} & {\text{MeanTestResults}} \\ {\text{ }\!\!'\!\!\text{ Peter }\!\!'\!\!\text{ }} & {73} & {75} & {77} & {\text{15-Jan-2019}} & {75} \\ {\text{ }\!\!'\!\!\text{ John }\!\!'\!\!\text{ }} & {73} & {74} & {83} & {\text{17-Jan-2019}} & {76.667} \\ {\text{ }\!\!'\!\!\text{ Lisa }\!\!'\!\!\text{ }} & {74.667} & {74} & {77} & {\text{21-Jan-2019}} & {62.333} \\ {\text{ }\!\!'\!\!\text{ Simon }\!\!'\!\!\text{ }} & {62} & {62} & {63} & {\text{11-Jan-2019}} & {62.333} \\ {\text{ }\!\!'\!\!\text{ Claire }\!\!'\!\!\text{ }} & {55} & {55} & {56} & {\text{22-Jan-2019}} & {55.333} \\ {\text{ }\!\!'\!\!\text{ Ashley }\!\!'\!\!\text{ }} & {44} & {45} & {40} & {\text{24-Jan-2019}} & {43} \end{array}$

Rearranging Variables (Columns)

Assuming we want the DateStarted to be the second column opposed to the last. We can rearrange the Variables and save the output to a new table using the Variable (column) numbers. The first input being

<span style="color: #ff0000;">:</span>

meaning all rows are selected and the second input being the reordered variable (column) numbers

DataTable3=<span style="color: #0000ff;">DataTable2(</span><span style="color: #ff0000;">:</span><span style="color: #808080;">,</span><span style="color: #ff0000;">[</span>1<span style="color: #808080;">,</span>5<span style="color: #808080;">,</span>2<span style="color: #808080;">,</span>3<span style="color: #808080;">,</span>4<span style="color: #808080;">,</span>6<span style="color: #ff0000;">]</span><span style="color: #0000ff;">)</span>

$\displaystyle \begin{array}{*{20}{r}} {\text{Names}} & {\text{DateStarted}} & {\text{Test1}} & {\text{Test2}} & {\text{Test3}} & {\text{MeanTestResults}} \\ {\text{ }\!\!'\!\!\text{ Peter }\!\!'\!\!\text{ }} & {\text{15-Jan-2019}} & {73} & {75} & {77} & {75} \\ {\text{ }\!\!'\!\!\text{ John }\!\!'\!\!\text{ }} & {\text{17-Jan-2019}} & {73} & {74} & {\text{83}} & {76.667} \\ {\text{ }\!\!'\!\!\text{ Lisa }\!\!'\!\!\text{ }} & {\text{21-Jan-2019}} & {73} & {74} & {\text{77}} & {74.667} \\ {\text{ }\!\!'\!\!\text{ Simon }\!\!'\!\!\text{ }} & {\text{11-Jan-2019}} & {62} & {62} & {\text{63}} & {62.333} \\ {\text{ }\!\!'\!\!\text{ Claire }\!\!'\!\!\text{ }} & {\text{22-Jan-2019}} & {55} & {55} & {\text{56}} & {55.333} \\ {\text{ }\!\!'\!\!\text{ Ashley }\!\!'\!\!\text{ }} & {\text{24-Jan-2019}} & {44} & {45} & {\text{40}} & {43} \end{array}$

We can also rearrange the Variables and save the output to a new table using the Variable (column) names. Once again the first input being

<span style="color: #ff0000;">:</span>

meaning all rows are selected and the second input being the reordered variable (column) names as a cell array

DataTable4=<span style="color: #0000ff;">DataTable2(</span><span style="color: #ff0000;">:</span><span style="color: #808080;">,</span><span style="color: #666699;">{</span><span style="color: #800080;">'</span>Names<span style="color: #800080;">'</span><span style="color: #808080;">,</span><span style="color: #800080;">'</span>DateStarted<span style="color: #800080;">'</span><span style="color: #808080;">,</span><span style="color: #800080;">'</span>Test1<span style="color: #800080;">'</span><span style="color: #808080;">,</span><span style="color: #800080;">'</span>Test2<span style="color: #800080;">'</span><span style="color: #808080;">,</span><span style="color: #800080;">'</span>Test3<span style="color: #800080;">'</span><span style="color: #808080;">,</span><span style="color: #800080;">'</span>MeanTestResults<span style="color: #800080;">'</span><span style="color: #666699;">}</span><span style="color: #0000ff;">)</span>

$\displaystyle \begin{array}{*{20}{r}} {\text{Names}} & {\text{DateStarted}} & {\text{Test1}} & {\text{Test2}} & {\text{Test3}} & {\text{MeanTestResults}} \\ {\text{ }\!\!'\!\!\text{ Peter }\!\!'\!\!\text{ }} & {\text{15-Jan-2019}} & {73} & {75} & {77} & {75} \\ {\text{ }\!\!'\!\!\text{ John }\!\!'\!\!\text{ }} & {\text{17-Jan-2019}} & {73} & {74} & {\text{83}} & {76.667} \\ {\text{ }\!\!'\!\!\text{ Lisa }\!\!'\!\!\text{ }} & {\text{21-Jan-2019}} & {73} & {74} & {\text{77}} & {74.667} \\ {\text{ }\!\!'\!\!\text{ Simon }\!\!'\!\!\text{ }} & {\text{11-Jan-2019}} & {62} & {62} & {\text{63}} & {62.333} \\ {\text{ }\!\!'\!\!\text{ Claire }\!\!'\!\!\text{ }} & {\text{22-Jan-2019}} & {55} & {55} & {\text{56}} & {55.333} \\ {\text{ }\!\!'\!\!\text{ Ashley }\!\!'\!\!\text{ }} & {\text{24-Jan-2019}} & {44} & {45} & {\text{40}} & {43} \end{array}$

Categorical Variable (Column)

DataTable4<span style="color: #ff0000;">.</span>FinalGrades=<span style="color: #666699;">{</span><span style="color: #800080;">'</span>A<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>B<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>C<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>D<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>E<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>A<span style="color: #800080;">'</span><span style="color: #666699;">}</span>

$\displaystyle \begin{array}{*{20}{r}} {\text{Names}} & {\text{DateStarted}} & {\text{Test1}} & {\text{Test2}} & {\text{Test3}} & {\text{MeanTestResults}} & {\text{FinalGrades}} \\ {\text{ }\!\!'\!\!\text{ Peter }\!\!'\!\!\text{ }} & {\text{15-Jan-2019}} & {73} & {75} & {77} & {75} & {\text{ }\!\!'\!\!\text{ A }\!\!'\!\!\text{ }} \\ {\text{ }\!\!'\!\!\text{ John }\!\!'\!\!\text{ }} & {\text{17-Jan-2019}} & {73} & {74} & {83} & {76.667} & {\text{ }\!\!'\!\!\text{ B }\!\!'\!\!\text{ }} \\ {\text{ }\!\!'\!\!\text{ Lisa }\!\!'\!\!\text{ }} & {\text{21-Jan-2019}} & {73} & {74} & {77} & {74.667} & {\text{ }\!\!'\!\!\text{ C }\!\!'\!\!\text{ }} \\ {\text{ }\!\!'\!\!\text{ Simon }\!\!'\!\!\text{ }} & {\text{11-Jan-2019}} & {62} & {62} & {63} & {62.3333} & {\text{ }\!\!'\!\!\text{ D }\!\!'\!\!\text{ }} \\ {\text{ }\!\!'\!\!\text{ Claire }\!\!'\!\!\text{ }} & {\text{22-Jan-2019}} & {55} & {55} & {56} & {55.333} & {\text{ }\!\!'\!\!\text{ E }\!\!'\!\!\text{ }} \\ {\text{ }\!\!'\!\!\text{ Ashley }\!\!'\!\!\text{ }} & {\text{24-Jan-2019}} & {44} & {45} & {40} & {43} & {\text{ }\!\!'\!\!\text{ F }\!\!'\!\!\text{ }} \end{array}$

$\displaystyle \begin{array}{*{20}{r}} {\text{Names}} & {\text{DateStarted}} & {\text{Test1}} & {\text{Test2}} & {\text{Test3}} & {\text{MeanTestResults}} & {\text{FinalGrades}} \\ {\text{ }\!\!'\!\!\text{ Peter }\!\!'\!\!\text{ }} & {\text{15-Jan-2019}} & {73} & {75} & {77} & {75} & \text{A} \\ {\text{ }\!\!'\!\!\text{ John }\!\!'\!\!\text{ }} & {\text{17-Jan-2019}} & {73} & {74} & {83} & {76.667} & \text{B} \\ {\text{ }\!\!'\!\!\text{ Lisa }\!\!'\!\!\text{ }} & {\text{21-Jan-2019}} & {73} & {74} & {77} & {74.667} & \text{C} \\ {\text{ }\!\!'\!\!\text{ Simon }\!\!'\!\!\text{ }} & {\text{11-Jan-2019}} & {62} & {62} & {63} & {62.333} & \text{D} \\ {\text{ }\!\!'\!\!\text{ Claire }\!\!'\!\!\text{ }} & {\text{22-Jan-2019}} & {55} & {55} & {56} & {55.333} & \text{F} \\ {\text{ }\!\!'\!\!\text{ Ashley }\!\!'\!\!\text{ }} & {\text{24-Jan-2019}} & {44} & {45} & {40} & {43} & \text{A} \end{array}$

Supposing we are interested in those who have a Grade

<span style="color: #800080;">'</span>A<span style="color: #800080;">'</span>

then we can created a Variable:

Aselect=<span style="color: #666699;">{</span><span style="color: #800080;">'</span>A<span style="color: #800080;">'</span><span style="color: #666699;">}</span>

$\displaystyle \text{Aselect}=\text{ }\!\!'\!\!\text{ A }\!\!'\!\!\text{ }$

In order to use it with the Categorical Column, it itself must be Categorical:

Aselect=<span style="color: #0000ff;">categorical(</span>Aselect<span style="color: #0000ff;">)</span>

$\displaystyle \text{Aselect}=\text{A}$

Now we can construct a Column Vector which will be 1 (true) when

and 0 (false) when

$\displaystyle \text{LogicalRowSelect}=\left[ {\begin{array}{*{20}{r}} 1 \\ 0 \\ 0 \\ 0 \\ 0 \\ 1 \end{array}} \right]$

We can then use this to Logically select the Rows where the

are

<span style="color: #800080;">'</span>A<span style="color: #800080;">'</span>

and all Variables (Columns):

DataTableA=<span style="color: #0000ff;">DataTable4(</span>LogicalRowSelect<span style="color: #800080;">,</span><span style="color: #ff0000;">:</span><span style="color: #0000ff;">)</span>

$\displaystyle \begin{array}{*{20}{r}} {\text{Names}} & {\text{DateStarted}} & {\text{Test1}} & {\text{Test2}} & {\text{Test3}} & {\text{MeanTestResults}} & {\text{FinalGrades}} \\ {\text{ }\!\!'\!\!\text{ Peter }\!\!'\!\!\text{ }} & {\text{15-Jan-2019}} & {73} & {75} & {77} & {75} & \text{A} \\ {\text{ }\!\!'\!\!\text{ Ashley }\!\!'\!\!\text{ }} & {\text{24-Jan-2019}} & {44} & {45} & {40} & {43} & \text{A} \end{array}$

DataTable4<span style="color: #ff0000;">.</span>IsA=LogicalRowSelect

$\displaystyle \begin{array}{*{20}{r}} {\text{Names}} & {\text{DateStarted}} & {\text{Test1}} & {\text{Test2}} & {\text{Test3}} & {\text{MeanTestResults}} & {\text{FinalGrades}} & {\text{IsA}} \\ {\text{ }\!\!'\!\!\text{ Peter }\!\!'\!\!\text{ }} & {\text{15-Jan-2019}} & {73} & {75} & {77} & {75} & \text{A} & 1 \\ {\text{ }\!\!'\!\!\text{ John }\!\!'\!\!\text{ }} & {\text{17-Jan-2019}} & {73} & {74} & {83} & {76.667} & \text{B} & 0 \\ {\text{ }\!\!'\!\!\text{ Lisa }\!\!'\!\!\text{ }} & {\text{21-Jan-2019}} & {73} & {74} & {77} & {74.667} & \text{C} & 0 \\ {\text{ }\!\!'\!\!\text{ Simon }\!\!'\!\!\text{ }} & {\text{11-Jan-2019}} & {62} & {62} & {63} & {62.333} & \text{D} & 0 \\ {\text{ }\!\!'\!\!\text{ Claire }\!\!'\!\!\text{ }} & {\text{22-Jan-2019}} & {55} & {55} & {56} & {55.333} & \text{E} & 0 \\ {\text{ }\!\!'\!\!\text{ Ashley }\!\!'\!\!\text{ }} & {\text{24-Jan-2019}} & {44} & {45} & {40} & {43} & \text{A} & 1 \end{array}$

Class of Variable (Column)

We can now check the class of each Variables:

<span style="color: #0000ff;">class(</span>DataTable4<span style="color: #ff0000;">.</span>Names<span style="color: #0000ff;">)</span>

cell

<span style="color: #0000ff;">class(</span>DataTable4<span style="color: #ff0000;">.</span>Test1<span style="color: #0000ff;">)</span>

double

<span style="color: #0000ff;">class(</span>DataTable4<span style="color: #ff0000;">.</span>Test2<span style="color: #0000ff;">)</span>

double

<span style="color: #0000ff;">class(</span>DataTable4<span style="color: #ff0000;">.</span>Test3<span style="color: #0000ff;">)</span>

double

<span style="color: #0000ff;">class(</span>DataTable4<span style="color: #ff0000;">.</span>MeanTestResults<span style="color: #0000ff;">)</span>

double

<span style="color: #0000ff;">class(</span>DataTable4<span style="color: #ff0000;">.</span>DateStarted<span style="color: #0000ff;">)</span>

datetime

<span style="color: #0000ff;">class(</span>DataTable4<span style="color: #ff0000;">.</span>FinalGrades<span style="color: #0000ff;">)</span>

categorical

<span style="color: #0000ff;">class(</span>DataTable4<span style="color: #ff0000;">.</span>IsA<span style="color: #0000ff;">)</span>

logical

Categorical Variable (Column) Dependent on Criteria

And supposing we have the following criteria:

$\displaystyle \begin{array}{*{20}{r}} {\text{Marks}} & {\text{Grade}} \\ {\text{0-49}} & \text{F} \\ {\text{50-54}} & \text{D} \\ {\text{55-69}} & \text{C} \\ {\text{70-79}} & \text{B} \\ {\text{80-100}} & \text{A} \end{array}$

Note that there are 5 Grades and 6 Levels (number of Grades plus 1) because each Grade has a lower and upper limit

Levels=<span style="color: #ff0000;">[</span>0<span style="color: #ff00ff;">;</span>50<span style="color: #ff00ff;">;</span>55<span style="color: #ff00ff;">;</span>70<span style="color: #ff00ff;">;</span>80<span style="color: #ff00ff;">;</span>100<span style="color: #ff0000;">]</span>
Grades=<span style="color: #666699;">{</span><span style="color: #800080;">'</span>F<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>D<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>C<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>B<span style="color: #800080;">'</span><span style="color: #ff00ff;">;</span><span style="color: #800080;">'</span>A<span style="color: #800080;">'</span><span style="color: #666699;">}</span>

We can specify the FinalGrades as a categorical Array of the Grades which correspond to the Levels in the MeanTestResults.

DataTable4<span style="color: #ff0000;">.</span>FinalGrades=<span style="color: #0000ff;">discretize(</span>DataTable4<span style="color: #ff0000;">.</span>MeanTestResults<span style="color: #808080;">,</span>Levels<span style="color: #808080;">,</span><span style="color: #800080;">'</span>categorical<span style="color: #800080;">'</span><span style="color: #808080;">,</span>Grades<span style="color: #0000ff;">)</span>

$\displaystyle \begin{array}{*{20}{r}} {\text{Names}} & {\text{DateStarted}} & {\text{Test1}} & {\text{Test2}} & {\text{Test3}} & {\text{MeanTestResults}} & {\text{FinalGrades}} & {\text{IsA}} \\ {\text{ }\!\!'\!\!\text{ Peter }\!\!'\!\!\text{ }} & {\text{15-Jan-2019}} & {73} & {75} & {77} & {75} & \text{B} & 1 \\ {\text{ }\!\!'\!\!\text{ John }\!\!'\!\!\text{ }} & {\text{17-Jan-2019}} & {73} & {74} & {83} & {76.667} & \text{B} & 0 \\ {\text{ }\!\!'\!\!\text{ Lisa }\!\!'\!\!\text{ }} & {\text{21-Jan-2019}} & {73} & {74} & {77} & {74.667} & \text{B} & 0 \\ {\text{ }\!\!'\!\!\text{ Simon }\!\!'\!\!\text{ }} & {\text{11-Jan-2019}} & {62} & {62} & {63} & {62.333} & \text{C} & 0 \\ {\text{ }\!\!'\!\!\text{ Claire }\!\!'\!\!\text{ }} & {\text{22-Jan-2019}} & {55} & {55} & {56} & {55.333} & \text{C} & 0 \\ {\text{ }\!\!'\!\!\text{ Ashley }\!\!'\!\!\text{ }} & {\text{24-Jan-2019}} & {44} & {45} & {40} & {43} & \text{F} & 1 \end{array}$

Removing a Variable (Column) from a Table

Since the Logical Column IsA, no longer makes sense we can remove it using:

DataTable4<span style="color: #ff0000;">.</span>IsA=<span style="color: #ff6600;">[]</span>

$\displaystyle \begin{array}{*{20}{r}} {\text{Names}} & {\text{DateStarted}} & {\text{Test1}} & {\text{Test2}} & {\text{Test3}} & {\text{MeanTestResults}} & {\text{FinalGrades}} \\ {\text{ }\!\!'\!\!\text{ Peter }\!\!'\!\!\text{ }} & {\text{15-Jan-2019}} & {73} & {75} & {77} & {52.6667} & \text{B} \\ {\text{ }\!\!'\!\!\text{ John }\!\!'\!\!\text{ }} & {\text{17-Jan-2019}} & {73} & {74} & {83} & {61.3333} & \text{B} \\ {\text{ }\!\!'\!\!\text{ Lisa }\!\!'\!\!\text{ }} & {\text{21-Jan-2019}} & {73} & {74} & {77} & {76.6667} & \text{B} \\ {\text{ }\!\!'\!\!\text{ Simon }\!\!'\!\!\text{ }} & {\text{11-Jan-2019}} & {62} & {62} & {63} & {71} & \text{C} \\ {\text{ }\!\!'\!\!\text{ Claire }\!\!'\!\!\text{ }} & {\text{22-Jan-2019}} & {55} & {55} & {56} & {69} & \text{C} \\ {\text{ }\!\!'\!\!\text{ Ashley }\!\!'\!\!\text{ }} & {\text{24-Jan-2019}} & {44} & {45} & {40} & {56.3333} & \text{F} \end{array}$

Merging Categories

Supposing Grades A, B and C correspond to a Pass and Grades D and F correspond to a Fail. We can use the Merge Categories Column to:

Creates a New Column from the merged categories:

DataTable4<span style="color: #ff0000;">.</span>Status=<span style="color: #0000ff;">mergecats(</span>DataTable4<span style="color: #ff0000;">.</span>FinalGrades<span style="color: #808080;">,</span><span style="color: #666699;">{</span><span style="color: #800080;">'</span>A<span style="color: #800080;">'</span><span style="color: #808080;">,</span><span style="color: #800080;">'</span>B<span style="color: #800080;">'</span><span style="color: #808080;">,</span><span style="color: #800080;">'</span>C<span style="color: #800080;">'</span><span style="color: #666699;">}</span><span style="color: #808080;">,</span><span style="color: #800080;">'</span>Pass<span style="color: #800080;">'</span><span style="color: #0000ff;">)</span>

Merges the remaining categories:

DataTable4<span style="color: #ff0000;">.</span>Status=<span style="color: #0000ff;">mergecats(</span>DataTable4<span style="color: #ff0000;">.</span>Status<span style="color: #808080;">,</span><span style="color: #666699;">{</span><span style="color: #800080;">'</span>D<span style="color: #800080;">'</span><span style="color: #808080;">,</span><span style="color: #800080;">'</span>F<span style="color: #800080;">'</span><span style="color: #666699;">}</span><span style="color: #808080;">,</span><span style="color: #800080;">'</span>Fail<span style="color: #800080;">'</span><span style="color: #0000ff;">)</span>

Note the line would update the values 'Pass' withe the corresponding 'A', 'B' and 'C' values:

DataTable4<span style="color: #ff0000;">.</span>Status=<span style="color: #0000ff;">mergecats(</span>DataTable4<span style="color: #ff0000;">.</span>FinalGrades<span style="color: #808080;">,</span><span style="color: #666699;">{</span><span style="color: #800080;">'</span>D<span style="color: #800080;">'</span><span style="color: #808080;">,</span><span style="color: #800080;">'</span>F<span style="color: #800080;">'</span><span style="color: #666699;">}</span><span style="color: #808080;">,</span><span style="color: #800080;">'</span>Fail<span style="color: #800080;">'</span><span style="color: #0000ff;">)</span>

$\displaystyle \begin{array}{*{20}{r}} {\text{Names}} & {\text{DateStarted}} & {\text{Test1}} & {\text{Test2}} & {\text{Test3}} & {\text{MeanTestResults}} & {\text{FinalGrades}} & {\text{Status}} \\ {\text{ }\!\!'\!\!\text{ Peter }\!\!'\!\!\text{ }} & {\text{15-Jan-2019}} & {73} & {75} & {77} & {75} & \text{B} & {\text{Pass}} \\ {\text{ }\!\!'\!\!\text{ John }\!\!'\!\!\text{ }} & {\text{17-Jan-2019}} & {73} & {74} & {83} & {76.667} & \text{B} & {\text{Pass}} \\ {\text{ }\!\!'\!\!\text{ Lisa }\!\!'\!\!\text{ }} & {\text{21-Jan-2019}} & {73} & {74} & {77} & {74.667} & \text{B} & {\text{Pass}} \\ {\text{ }\!\!'\!\!\text{ Simon }\!\!'\!\!\text{ }} & {\text{11-Jan-2019}} & {62} & {62} & {63} & {62.333} & \text{C} & {\text{Pass}} \\ {\text{ }\!\!'\!\!\text{ Claire }\!\!'\!\!\text{ }} & {\text{22-Jan-2019}} & {55} & {55} & {56} & {55.333} & \text{C} & {\text{Pass}} \\ {\text{ }\!\!'\!\!\text{ Ashley }\!\!'\!\!\text{ }} & {\text{24-Jan-2019}} & {44} & {45} & {40} & {43} & \text{F} & {\text{Fail}} \end{array}$

This site uses Akismet to reduce spam. Learn how your comment data is processed.