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:
| Function | Description | Example Code | Applicable Formats |
|---|---|---|---|
readtable | Read tabular data (automatically detect column names and types) | T = readtable('data.csv') | CSV, Excel, text files |
readmatrix | Read numerical matrix (ignore text columns) | M = readmatrix('data.xlsx') | Excel, CSV (pure numerical) |
readcell | Read cell array (preserve mixed types) | C = readcell('data.txt') | Text files, CSV |
readexcel | Read Excel file (support sheet selection) | T = readexcel('data.xlsx', 'Sheet2') | Excel (.xlsx/.xls) |
readsql | Read SQL database (requires database connection) | T = readsql(conn, 'SELECT * FROM table') | SQL (MySQL, SQLite, etc.) |
fileread | Read 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:
| Method | Applicable Scenarios | MATLAB Function/Code | Advantages and Limitations |
|---|---|---|---|
| Delete missing rows | Low missing ratio (<5%) | T = rmmissing(T) | Simple and efficient, but loses sample information |
| Mean/median imputation | Numerical variables, symmetric distribution | T.Sales = fillmissing(T.Sales, 'constant', mean(T.Sales, 'omitnan')) | Preserves mean, but may distort distribution |
| Forward/backward fill | Time series data (continuous values) | T.Sales = fillmissing(T.Sales, 'ffill') (forward)T.Sales = fillmissing(T.Sales, 'bfill') (backward) | Preserves trend, but may introduce lag |
| Interpolation | Non-time series, needs smooth fill | T.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 imputation | Missing strongly correlated with other variables | Train regression model (e.g., fitlm) to predict missing values | High 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:
| Method | Principle | MATLAB Function/Code | Applicable Scenarios |
|---|---|---|---|
| Z-score detection | Deviation from mean ( | Z | >3) |
| IQR detection | Points 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 visualization | Visual observation of outliers | boxplot(T.Sales) | Quick screening and intuitive |
| Isolation Forest | Tree-based anomaly detection | model = isolationForest(T.Sales); scores = predict(model, T.Sales); outliers = scores < 0.1 | High-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.
| Function | Description | Example Code | Output Format |
|---|---|---|---|
writetable | Export tabular data (automatically handle column names and types) | writetable(T, 'processed_data.csv') | CSV, Excel, text files |
writematrix | Export numerical matrix (numerical columns only) | writematrix(M, 'numeric_data.xlsx') | Excel, CSV (pure numerical) |
writecell | Export cell array (preserve mixed types) | writecell(C, 'mixed_data.txt') | Text files, CSV |
database+ exec | Export to SQL database | exec(conn, 'INSERT INTO table VALUES (?)', {T.Value}) | SQL database |
save | Export 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.,
dateshiftfunction).
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 ofx = []; 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
clearto 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 .* Binstead offor i=1:n, C(i)=A(i)*B(i); end). - Parallel computing: Use Parallel Computing Toolbox’s
parforfor 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.



