%% Clean Messy and Missing Data in Tables
% This example shows how to find, clean, and delete table rows with missing data.
%% Load Sample Data
% Load sample data from a comma-separated text file, messy.csv. The file contains many different missing data indicators:
% Empty character vector ('')
% period (.)
% NA
% NaN
% -99
% To specify the character vectors to treat as empty values, use the 'TreatAsEmpty' name-value pair argument with the readtable function. (Use the disp function to display all 21 rows, even when running this example as a live script.)

T = readtable('messy.csv','TreatAsEmpty',{'.','NA'});
disp(T)

% T is a table with 21 rows and five variables. 'TreatAsEmpty' only applies to numeric columns in the file and cannot handle numeric values specified as text, such as '-99'.
%
%% Summarize Table
% View the data type, description, units, and other descriptive statistics for each variable by creating a table summary using the summary function.

summary(T)

% When you import data from a file, the default is for readtable to read any variables with nonnumeric elements as a cell array of character vectors.
%
%% Find Rows with Missing Values
% Display the subset of rows from the table, T, that have at least one missing value.

TF = ismissing(T,{'' '.' 'NA' NaN -99});
rowsWithMissing = T(any(TF,2),:);
disp(rowsWithMissing)

% readtable replaced '.' and 'NA' with NaN in the numeric variables,  B, D, and E.
%
%% Replace Missing Value Indicators
% Clean the data so that the missing values indicated by code -99 have the standard MATLAB® numeric missing value indicator, NaN.

T = standardizeMissing(T,-99);
disp(T)

%
% standardizeMissing replaces three instances of -99 with NaN.
% Create a new table, T2, and replace missing values with values from previous rows of the table. fillmissing provides a number of ways to fill in missing values.

T2 = fillmissing(T,'previous');
disp(T2)

%
%% Remove Rows with Missing Values
% Create a new table, T3, that contains only the rows from T without missing values. T3 has only 16 rows.

T3 = rmmissing(T);
disp(T3)

% T3 contains 16 rows and five variables.
%
%% Organize Data
% Sort the rows of T3 in descending order by C, and then sort in ascending order by A.

T3 = sortrows(T2,{'C','A'},{'descend','ascend'});
disp(T3)

%
% In C, the rows are grouped first by 'yes', followed by 'no'. Then in A, the rows are listed alphabetically.
% Reorder the table so that A and C are next to each other.

T3 = T3(:,{'A','C','B','D','E'});
disp(T3)

% Copyright 2012 The MathWorks, Inc.