PhD Chapter - from A to Z in importing the data from XLS

Posted by: admin 3 years, 1 month ago

(Comments)

So a couple of question that comes up when we want to deal with the data is how to read and deal with them. 

So I will take the example on how to read it, how to filter them, and how to clean from Nan.

1. How to locate your file

2. How to import in the shape that we want. 

3. The logic behind naming every row or column as the header

4. Use this data and be ready for any statistic that we want. 

How to locate your file. 

1. It's pretty simple actually. Just click on the current matlab directory that you have. The place is in the left panel of Matlab 

 There you can directly type the function to read the xls. 

And here is my data look like.

You can download the data from this link. 

%1. First is clean all the data 
% a clear MATLAB workspace is a clear mental workspace
close all; clear; clc

%2. Import with xls read 
[num,txt,raw]= xlsread("state-marriage.xlsx")
data = raw;
%3. check sizes and outputs
whos

%4. Make the matrix from the data
% initialize matrices
%M is the matrix where there is 51 row with 23 column
M = zeros(51,23);
%statesM is creating the cell with 51 row and 1 column
statesM = cell(51,1);
%the year is creating 23 row with one column 
yearM = zeros(23,1);

% 5. import the data; start with a loop over columns
% for the column i from 1 until 23 
for coli = 1:length(yearM)
 
 % get year (same for all rows...)
 % some columns are text, others are numeric
 % the symbol { meaning the new row
 yearval = data{2,coli+1};
 if isnumeric(yearval)
 yearM(coli) = yearval;
 else
 yearM(coli) = str2double(yearval);
 end
 
 
 
 % loop over rows
 for rowi = 1:51
 
 % get value from this cell and convert to number
 val = data{rowi+3,coli+1};
 if isnumeric(val)
 M(rowi,coli) = val;
 else
 M(rowi,coli) = str2double(val);
 end
 
 
 % get state label (only in first colum)
 if coli==1
 statesM{rowi} = data{rowi+3,1};
 end
 end % end row loop
end % end column loop

%% 6. Check if the data clean the marriage data

figure(1), clf
imagesc(yearM,[],M)
colorbar
set(gca,'clim',[0 10])
xlabel('Year'), ylabel('State')

% 7. Replace the Nan with something, for example median or zero
% replace with column zero
[nanrow,nancol] = find(isnan(M));
for i=1:length(nanrow)
 M(nanrow(i),nancol(i)) = nanmedian(M(:,nancol(i)));
end

% And thats all 

So that's all now the way to import excel file! Hopefully it helps! 

• ← Parenting chapter - Kids say I love you
• PhD Chapter - Going through a stress testing with Matlab example →