PhD Chapter - from A to Z in importing the data from XLS
Posted by: admin 2 years, 6 months 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!
3 months, 1 week ago
A reflection of using kanban flow and being minimalist
Recent newsToday is the consecutive day I want to use and be consistent with the Kanban flow! It seems it's perfect to limit my parallel and easily distractedness.
read more3 months, 3 weeks ago
3 months, 3 weeks ago
Podcast Bapak Dimas 2 - pindahan rumah
Recent newsVlog kali ini adalah terkait pindahan rumah!
read more3 months, 3 weeks ago
Podcast Bapak Dimas - Bapaknya Jozio dan Kaziu - ep 1
Recent newsSeperti yang saya cerita kan sebelumnya, berikut adalah catatan pribadi VLOG kita! Bapak Dimas
read more3 months, 3 weeks ago
Happy new year 2024 and thank you 2023!
Recent newsAs the new year starts, I want to revisit what has happened in 2023.
read more3 months, 3 weeks ago
Some notes about python and Zen of Python
Recent newsExplore Python syntax
Python is a flexible programming language used in a wide range of fields, including software development, machine learning, and data analysis. Python is one of the most popular programming languages for data professionals, so getting familiar with its fundamental syntax and semantics will be useful for your future career. In this reading, you will learn about Python’s syntax and semantics, as well as where to find resources to further your learning.
5 months, 1 week ago
Collaboratively administrate empowered markets via plug-and-play networks. Dynamically procrastinate B2C users after installed base benefits. Dramatically visualize customer directed convergence without
Comments