Lesson 03-Data Import and Preprocessing

Data Import Techniques

The core of data import is loading external data (such as CSV, Excel, SQL, JSON, etc.) into analysis tools (such as MATLAB). The key lies in format compatibility, reading efficiency, and data integrity.

Basic Data Import Functions

MATLAB provides a rich set of functions to handle data in different formats, covering structured (tables, matrices) and unstructured (text, images) data.

Common Functions and Application Scenarios:

FunctionDescriptionExample CodeApplicable Formats
readtableRead tabular data (automatically detect column names and types)T = readtable('data.csv')CSV, Excel, text files
readmatrixRead numerical matrix (ignore text columns)M = readmatrix('data.xlsx')Excel, CSV (pure numerical)
readcellRead cell array (preserve mixed types)C = readcell('data.txt')Text files, CSV
readexcelRead Excel file (support sheet selection)T = readexcel('data.xlsx', 'Sheet2')Excel (.xlsx/.xls)
readsqlRead SQL database (requires database connection)T = readsql(conn, 'SELECT * FROM table')SQL (MySQL, SQLite, etc.)
filereadRead raw text file (line by line)text = fileread('log.txt')Plain text (.txt/.log)

Reading CSV and converting to table

% Read CSV file (automatically recognize first row as column names)
T = readtable('sales_data.csv');
disp(T.Properties.VariableNames);  % Display column names: {'Date', 'Product', 'Sales'}
disp(head(T));  % Display first 5 rows

Table data reading:

% Read from CSV file as table
tbl = readtable('data.csv', 'Delimiter', ',', 'ReadVariableNames', true);

% Specify column types and variable names
opts = detectImportOptions('data.csv');
opts.VariableTypes = {'double', 'categorical', 'datetime'};
opts.VariableNames = {'ID', 'Category', 'Timestamp'};
tbl = readtable('data.csv', opts);

Numerical array reading:

% Traditional way to read matrix
data = load('data.mat');  % Load MAT file
matrix = csvread('data.csv');  % Deprecated, recommend readmatrix

% Modern alternatives
matrix = readmatrix('data.csv');  % Numerical matrix
array = readcell('data.txt');     % Cell array

Structured data import:

% Import Excel file
opts = detectImportOptions('data.xlsx');
data = readtable('data.xlsx', opts);

% Import mixed data from text file
data = importdata('mixed_data.txt', '\t', 1);  % Skip first row header

Advanced Import Techniques

For large-scale, non-standard format, or data requiring custom parsing, advanced import techniques are necessary.

Key Techniques and Implementations:

Chunked reading of large files:

Use the 'Range' parameter of readtable for chunked loading to avoid memory overflow.

% Chunked reading of 1 million row CSV (10,000 rows at a time)
chunk_size = 1e4;
total_rows = 1e6;
T = table();
for i = 0:chunk_size:total_rows
	range = sprintf('A%d:D%d', i+1, min(i+chunk_size, total_rows));
	chunk = readtable('large_data.csv', 'Range', range);
	T = [T; chunk];  % Accumulate data
end

Custom delimiters and encoding:

Handle non-standard delimiters (e.g., tab \t) or special encodings (e.g., UTF-8).

% Read tab-delimited TSV file (UTF-8 encoding)
T = readtable('data.tsv', 'Delimiter', '\t', 'Encoding', 'UTF-8');

Skip headers and comment lines:

Skip comment lines at the beginning of the file (e.g., starting with #) or redundant headers.

% Skip first 2 comment lines, use line 3 as column names
T = readtable('log.txt', 'CommentStyle', '#', 'HeaderLines', 2);

Direct database connection import:

Directly read SQL data via Database Toolbox, avoiding intermediate export files.

% Connect to MySQL database
conn = database('mydb', 'user', 'password', 'Host', 'localhost', 'Port', 3306);
% Execute SQL query and import
query = 'SELECT * FROM sales WHERE date >= "2023-01-01"';
T = fetch(conn, query);
close(conn);  % Close connection

Chunked reading of big data files:

% Use custom read function for large files
fid = fopen('large_data.csv');
header = fgetl(fid);  % Read header line
chunkSize = 10000;    % Rows per read
data = [];

while ~feof(fid)
    chunk = textscan(fid, '%f%f%f', chunkSize, 'Delimiter', ',');
    data = [data; cell2mat(chunk)];  % Merge chunks
end
fclose(fid);

Memory-mapped files:

% Create memory-mapped file
m = memmapfile('large_array.dat', 'Format', 'double', 'Size', [1e6 10]);

% Access data subset
subset = m.Data(1:10000, 1:3);  % No need to load entire file

Data Cleaning and Preprocessing

Missing Value Handling

Missing values (NaN, missing) can cause model bias and need to be handled based on business scenarios.

Handling Methods and MATLAB Implementations:

MethodApplicable ScenariosMATLAB Function/CodeAdvantages and Limitations
Delete missing rowsLow missing ratio (<5%)T = rmmissing(T)Simple and efficient, but loses sample information
Mean/median imputationNumerical variables, symmetric distributionT.Sales = fillmissing(T.Sales, 'constant', mean(T.Sales, 'omitnan'))Preserves mean, but may distort distribution
Forward/backward fillTime series data (continuous values)T.Sales = fillmissing(T.Sales, 'ffill') (forward)
T.Sales = fillmissing(T.Sales, 'bfill') (backward)
Preserves trend, but may introduce lag
InterpolationNon-time series, needs smooth fillT.Sales = interp1(find(~isnan(T.Sales)), T.Sales(~isnan(T.Sales)), 1:numel(T.Sales), 'spline')Suitable for continuous data, high computational complexity
Model-based imputationMissing strongly correlated with other variablesTrain regression model (e.g., fitlm) to predict missing valuesHigh accuracy, but requires additional modeling

Impute missing sales data with median

% Check missing values
missing_cols = ismissing(T);
disp(sum(missing_cols.Sales));  % Output: 20 (assume 20 missing in Sales)

% Impute with median
median_sales = median(T.Sales(~isnan(T.Sales)));
T.Sales(missing_cols.Sales) = median_sales;

% Verify imputation
disp(sum(ismissing(T.Sales)));  % Output: 0 (no missing)

Missing value detection and replacement:

% Detect missing values
tbl = readtable('data.csv');
missingValues = ismissing(tbl);

% Count missing per column
missingStats = sum(missingValues);

% Replacement strategies
tbl.Cleaned = fillmissing(tbl.NoisyData, 'constant', 0);  % Constant fill
tbl.Interpolated = fillmissing(tbl.TimeSeries, 'linear');  % Linear interpolation
tbl.Nearest = fillmissing(tbl.DiscreteData, 'nearest');    % Nearest neighbor fill

Advanced missing value handling:

% Group-based filling
tbl.GroupFilled = varfun(@(x) fillmissing(x, 'constant', groupsummary(x, 'Category')), ...
    tbl, 'InputVariables', 'Value', 'GroupingVariables', 'Category');

% Multiple imputation
imp = regressionImp;
imputedData = fillmissing(tbl, 'regression', 'PredictorVariables', {'Var1','Var2'}, ...
    'ResponseVariable', 'Target', 'Method', 'baggedtree');

Outlier Detection and Handling

Outliers may result from measurement errors or true extreme events and need to be handled with statistical methods or domain knowledge.

Detection Methods and MATLAB Implementations:

MethodPrincipleMATLAB Function/CodeApplicable Scenarios
Z-score detectionDeviation from mean (Z>3)
IQR detectionPoints beyond 1.5×IQR`q = quantile(T.Sales, [0.25, 0.75]); iqr = q(2)-q(1); outliers = T.Sales < (q(1)-1.5*iqr)T.Sales > (q(2)+1.5*iqr)`
Boxplot visualizationVisual observation of outliersboxplot(T.Sales)Quick screening and intuitive
Isolation ForestTree-based anomaly detectionmodel = isolationForest(T.Sales); scores = predict(model, T.Sales); outliers = scores < 0.1High-dimensional data, nonlinear relationships

Detect and correct outliers using IQR

% Calculate quartiles and IQR
q = quantile(T.Sales, [0.25, 0.75]);
iqr_val = q(2) - q(1);
lower_bound = q(1) - 1.5*iqr_val;
upper_bound = q(2) + 1.5*iqr_val;

% Mark outliers
outliers = T.Sales < lower_bound | T.Sales > upper_bound;

% Replace outliers with bounds (Winsorization)
T.Sales(outliers & T.Sales < lower_bound) = lower_bound;
T.Sales(outliers & T.Sales > upper_bound) = upper_bound;

% Visualize before/after
subplot(1,2,1); boxplot(T.Sales_before); title('Original Data');
subplot(1,2,2); boxplot(T.Sales_after); title('After IQR Handling');

Statistical methods for outlier detection:

% Z-score method
zScores = zscore(tbl.NumericData);
outliers = abs(zScores) > 3;  % Threshold 3 standard deviations

% IQR method
Q1 = prctile(tbl.NumericData, 25);
Q3 = prctile(tbl.NumericData, 75);
IQR = Q3 - Q1;
outliers = (tbl.NumericData < Q1 - 1.5*IQR) | (tbl.NumericData > Q3 + 1.5*IQR);

Outlier handling strategies:

% Winsorization
winsorizedData = wblfit(tbl.NumericData);  % Fit Weibull distribution
tbl.Winsorized = wblinv(tbl.NumericData, winsorizedData(1), winsorizedData(2));

% Truncation
tbl.Truncated = min(max(tbl.NumericData, lowerBound), upperBound);

% Replace with group median
tbl.GroupMedian = varfun(@(x) fillmissing(x, 'constant', median(x(~outliers))), ...
    tbl, 'InputVariables', 'NumericData', 'GroupingVariables', 'Group');

Data Statistical Analysis

Statistical analysis is the core of data preprocessing, revealing data characteristics through descriptive statistics and correlation analysis.

Descriptive Statistics

Descriptive statistics include mean, median, standard deviation, quantiles, etc., to summarize basic data properties.

Basic statistical calculations:

% Univariate statistics
meanVal = mean(tbl.NumericData);
stdDev = std(tbl.NumericData);
skewnessVal = skewness(tbl.NumericData);
kurtosisVal = kurtosis(tbl.NumericData);

% Multivariate statistics
covMatrix = cov(tbl{:,'Var1','Var2','Var3'});
corrMatrix = corr(tbl{:,'Var1','Var2','Var3'});

% Grouped statistics
groupStats = groupsummary(tbl, 'Category', ...
    {'mean', 'std', 'min', 'max'}, 'NumericData');

Advanced statistical analysis:

% Hypothesis testing
[h,p] = ttest(tbl.Group1, tbl.Group2);  % Independent samples t-test
[h,p] = ttest(tbl.Before, tbl.After);   % Paired samples t-test

% ANOVA
[p,tbl,stats] = anova1(tbl.NumericData, tbl.Group, 'off');

% Non-parametric test
[h,p] = ranksum(tbl.Group1, tbl.Group2);  % Wilcoxon rank-sum test

Data Sorting and Grouping

Sorting and grouping are key operations for data organization, facilitating subsequent analysis (e.g., time series, grouped aggregation).

Data Sorting Techniques

  • Single-variable sorting: Sort ascending/descending by one column.
T_sorted = sortrows(T, 'Sales', 'ascend');  % Sort by Sales ascending
  • Multi-variable sorting: Sort by multiple columns with priority (e.g., first by Date, then by Product).
T_sorted = sortrows(T, {'Date', 'Product'}, {'ascend', 'descend'});

Single-variable sorting:

% Sort ascending by numerical column
sortedTbl = sortrows(tbl, 'NumericColumn');

% Sort by multiple columns
sortedTbl = sortrows(tbl, {'Category', 'NumericColumn'}, {'ascend', 'descend'});

% Custom sort order
[~,idx] = ismember(tbl.Category, {'High','Medium','Low'});
sortedTbl = tbl(idx,:);

Complex sorting logic:

% Sort based on computed column
tbl.Score = tbl.Value1 ./ tbl.Value2;
sortedTbl = sortrows(tbl, 'Score', 'descend');

% Conditional sorting
tbl.Priority = tbl.Value > threshold;
sortedTbl = sortrows(tbl, {'Priority', 'Value'}, {'descend', 'ascend'});

Data Grouping Operations

  • Grouped aggregation: Group by one column, compute statistics for others (e.g., mean, sum).
% Group by Product, compute sum and mean of Sales
grouped = groupsummary(T, 'Product', {'sum', 'mean'}, 'Sales');
disp(grouped(:, {'Product', 'sum_Sales', 'mean_Sales'}));
  • Grouped filtering: Filter groups meeting conditions (e.g., products with sales > 1000).
high_sales_products = T.Product(groupsummary(T, 'Product', 'sum', 'Sales') >= 1000);

Basic grouped statistics:

% Group by category and compute mean
groupedMean = groupsummary(tbl, 'Category', 'mean', 'NumericData');

% Multi-variable grouping
groupedStats = groupsummary(tbl, {'Category', 'Region'}, ...
    {'mean', 'sum'}, 'NumericData');

% Group filtering
highValueGroups = groupfilter(tbl, 'Category', @(x) mean(x) > 100);

Advanced grouping techniques:

% Group aggregation with multiple statistics
aggTbl = groupsummary(tbl, 'Category', ...
    {@mean, @median, @std}, 'NumericData');

% Group transformation (normalization)
tbl.ZScore = grouptransform(tbl, 'Category', 'normalize', 'NumericData');

% Pivot table
pivotTbl = unstack(tbl, 'NumericData', 'Quarter');

Data Storage and Export

Processed data needs to be stored in reusable formats. MATLAB supports multiple export methods for different scenarios.

Data Export Functions

Choose export functions based on data type and target format to ensure integrity and compatibility.

FunctionDescriptionExample CodeOutput Format
writetableExport tabular data (automatically handle column names and types)writetable(T, 'processed_data.csv')CSV, Excel, text files
writematrixExport numerical matrix (numerical columns only)writematrix(M, 'numeric_data.xlsx')Excel, CSV (pure numerical)
writecellExport cell array (preserve mixed types)writecell(C, 'mixed_data.txt')Text files, CSV
database+ execExport to SQL databaseexec(conn, 'INSERT INTO table VALUES (?)', {T.Value})SQL database
saveExport as MAT file (MATLAB-specific)save('data.mat', 'T', '-v7.3')MAT binary file

Example: Export grouped statistics to Excel

% Export after grouped statistics
grouped_stats = groupsummary(T, 'Product', 'mean', 'Sales');
writetable(grouped_stats, 'product_sales_summary.xlsx', 'Sheet', 'Summary');

Table data export:

% Export as CSV
writetable(tbl, 'output.csv', 'WriteVariableNames', true, 'Delimiter', ',');

% Export as Excel
writetable(tbl, 'output.xlsx', 'Sheet', 'Data', 'WriteVariableNames', true);

% Export as text
writematrix(tbl.NumericData, 'output.txt', 'Delimiter', '\t');

Structured data export:

% Save as MAT file
save('data.mat', 'tbl', '-v7.3');  % Supports files >2GB

% Export as JSON
jsonwrite('data.json', struct(tbl));

% Export as HDF5
h5create('data.h5', '/dataset', size(tbl.NumericData));
h5write('data.h5', '/dataset', tbl.NumericData);

Big Data Processing Techniques

For TB-level data, use distributed storage (e.g., Hadoop HDFS) and columnar storage (e.g., Parquet) to optimize read/write efficiency.

Key Techniques:

  • Chunked storage: Split large files into smaller ones (e.g., by time), process in parallel.
  • Columnar storage: Parquet format stores by column, high compression, suitable for analytical queries (MATLAB supports parquetread/parquetwrite).
  • Database sharding: Shard data across multiple instances by rules to improve performance.

Export as Parquet format (columnar storage)

% Generate large table (1 million rows)
T = table(rand(1e6,1), randi(100,1e6,1), 'VariableNames', {'Value', 'Category'});

% Export as Parquet (higher compression, suitable for big data)
writetable(T, 'large_data.parquet', 'Compression', 'SNAPPY');

Tall array operations:

% Create tall array
tt = tall(readtable('very_large_data.csv'));

% Perform deferred computation
meanVal = mean(tt.NumericColumn);
stdDev = std(tt.NumericColumn);

% Trigger actual computation
gather(meanVal);

% Tall array grouped statistics
groupedMean = groupsummary(tt, 'Category', 'mean', 'NumericColumn');

Memory-mapped file application:

% Create memory map
m = memmapfile('large_array.dat', 'Format', 'double', 'Size', [1e8 1]);

% Process data in chunks
chunkSize = 1e6;
results = zeros(1, ceil(1e8/chunkSize));
for i = 1:chunkSize:1e8
    idx = i:min(i+chunkSize-1, 1e8);
    chunk = m.Data(idx);
    results((i-1)/chunkSize+1) = mean(chunk);
end

Data Transformation Techniques

Data transformation converts raw data into model-usable features, including type conversion, date handling, text vectorization, etc.

Data Standardization and Normalization

Standardization (Z-score) and normalization (Min-Max) eliminate scale effects and improve model training efficiency.

Standardization methods:

% Z-score standardization
zScoreData = zscore(tbl.NumericData);

% Min-Max normalization
minMaxData = normalize(tbl.NumericData, 'range');

% Robust standardization (outlier-resistant)
robustData = normalize(tbl.NumericData, 'robust');

% Custom standardization
customMean = mean(tbl.NumericData);
customStd = std(tbl.NumericData);
customData = (tbl.NumericData - customMean) / customStd;

Discretization techniques:

% Equal-width binning
binnedData = discretize(tbl.NumericData, 10);  % 10 equal-width bins

% Equal-frequency binning
binnedData = discretize(tbl.NumericData, linspace(min(tbl.NumericData), ...
    max(tbl.NumericData), 10));  % ~10% data per bin

% Quantile-based binning
quantiles = prctile(tbl.NumericData, [0 25 50 75 100]);
binnedData = discretize(tbl.NumericData, quantiles);

Feature Engineering

Feature engineering is key to improving model performance, including feature extraction, selection, and generation.

Common Techniques:

  • Time features: Extract year, month, day, weekday from dates (e.g., dateshift function).
T.Date = datetime(T.Date, 'InputFormat', 'yyyy-MM-dd');
T.Year = year(T.Date);
T.Month = month(T.Date);
  • Text features: Convert text to bag-of-words or word embeddings (Word2Vec).
% Use textscan to extract text keywords
fid = fopen('reviews.txt');
C = textscan(fid, '%s', 'Delimiter', '\n');
fclose(fid);
bag = bagOfWords(C{1});
  • Interaction features: Generate combinations (e.g., Sales * Product_Category).
T.Sales_Product = T.Sales .* T.Product_Code;  % Product interaction

Polynomial feature generation:

% Generate polynomial features
[polyFeatures, names] = polyfeatures(tbl.NumericData, 2);  % Quadratic

% Interaction terms
interactionData = tbl.Var1 .* tbl.Var2;  % Create interaction
tbl = [tbl, array2table(interactionData, 'VariableNames', {'Interaction'})];

Time feature extraction:

% Extract features from datetime
tbl.DayOfWeek = weekday(tbl.Timestamp);
tbl.HourOfDay = hour(tbl.Timestamp);
tbl.Month = month(tbl.Timestamp);
tbl.IsWeekend = ismember(tbl.DayOfWeek, [1 7]);  % Weekend flag

Performance Optimization Techniques

In data processing, performance optimization is crucial, involving memory management, computation acceleration, and GPU parallelism.

Memory Management Strategies

  • Preallocate memory: Avoid dynamic array expansion (e.g., x = zeros(1e6,1) instead of x = []; x(end+1) = 5).
  • Use appropriate data types: Single precision (single) saves 50% memory vs. double (double), suitable for lower precision needs.
  • Release unused variables: Use clear to free memory occupied by unused variables.

Efficient data storage:

% Use appropriate data types
tbl.ID = uint32(tbl.ID);  % 32-bit integer instead of default double
tbl.Flag = logical(tbl.Flag);  % Logical type for flags

% Sparse matrix application
sparseMatrix = sparse(tbl.RowIdx, tbl.ColIdx, tbl.Values, nRows, nCols);

% Chunked processing of big data
chunkSize = 1e6;
for i = 1:chunkSize:size(data,1)
    chunk = data(i:min(i+chunkSize-1,end),:);
    processChunk(chunk);
end

Computation Acceleration Techniques

  • Vectorization: Replace loops with matrix operations (e.g., A .* B instead of for i=1:n, C(i)=A(i)*B(i); end).
  • Parallel computing: Use Parallel Computing Toolbox’s parfor for parallel loops.
parfor i = 1:1000
	% Parallel computation of time-consuming operations
	result(i) = computationally_intensive_function(i);
end

Vectorized operations:

% Replace loop computation
% Inefficient way
result = zeros(size(data,1),1);
for i = 1:size(data,1)
    result(i) = sum(data(i,:));
end

% Efficient vectorized
result = sum(data, 2);

Parallel computing application:

% Start parallel pool
parpool('local',4);

% Parallel statistics
parfor i = 1:nGroups
    groupData = tbl(tbl.Group==i, :);
    stats(i) = mean(groupData.NumericColumn);
end

% Use parallel toolbox functions
parfevalOnAll(@mean, 0, tbl.NumericColumn);

GPU Acceleration

MATLAB’s gpuArray supports migrating data to NVIDIA GPUs for accelerated matrix operations (e.g., multiplication, SVD).

GPU computation example:

% Check GPU availability
if gpuDeviceCount > 0
    % Transfer data to GPU
    gpuData = gpuArray(tbl.NumericData);
    
    % Perform computation on GPU
    gpuMean = mean(gpuData);
    gpuStd = std(gpuData);
    
    % Transfer results back to CPU
    result = gather([gpuMean, gpuStd]);
else
    warning('GPU unavailable, using CPU');
    result = [mean(tbl.NumericData), std(tbl.NumericData)];
end

GPU-accelerated matrix multiplication

% Generate large matrices (10k×10k)
n = 10000;
A = randn(n);
B = randn(n);

% CPU time
tic;
C_cpu = A * B;
t_cpu = toc;

% GPU time
gpuDevice(1);  % Select GPU 1
A_gpu = gpuArray(A);
B_gpu = gpuArray(B);
tic;
C_gpu = A_gpu * B_gpu;
t_gpu = toc;

disp(['GPU speedup: ', num2str(t_cpu/t_gpu)]);  % Typically 10-100x faster

Data import techniques form the foundation of data-driven decision-making, covering from basic functions to advanced techniques, from data cleaning to performance optimization, encompassing the full process from “data ingestion” to “value extraction.” The key is to select appropriate methods based on data scale, format, and business needs (e.g., chunked reading for large files, IQR for outliers, GPU for acceleration), and continuously optimize through practice. Mastering these techniques significantly improves data analysis efficiency and result reliability, providing solid data support for machine learning and business intelligence.

Share your love