How do I have matlab read through an excel data sheet?
3 views (last 30 days)
Show older comments
Hello all,
I am fairly new to matlab and I am wondering, how do I have matlab run a loop through an excel data sheet?
Example, I have an excel data sheet and suppose the first row represents date and time starting at 00:00 to 23:59, incrementing by 00:01 minutes and it repeats. Suppose there was a skip in the time, and it went from 00:01, 00:02, 00:44. There is a 00:42 time difference. How do I fill in the row values of 00:03 to 00:43 with NaN values?
Regards, Chris
0 Comments
Answers (4)
Roy Veldhuizen
on 28 Jun 2012
I've found a method which works for the case you're describing, don't know if this is the actual situation or a simplification. Either way:
%load data from xls called Book1.xlsx, which contains: [1 2 44]
data=xlsread('Book1.xlsx')
%determine datastep
datastep=data(2)-data(1);
%Find size of emtpy matrix
EmptyLength=((max(data)-min(data))/datastep)+1
%Generate emtpy matrix
EmptyData=ones(EmptyLength,1).*NaN
%Fill matrix
EmptyData(data)=data
Possibly it can be done faster although this one is hopefully easy to understand for the beginner. Hope it helps, and if you have any problems or questions, let me know.
Roy
0 Comments
Roy Veldhuizen
on 29 Jun 2012
Edited: Roy Veldhuizen
on 29 Jun 2012
The number you get is the time expressed as fraction of the day, thus beginning of the day = 0:00 =0, middle of the day = 12:00, means 0.5 end of the day is 24:00 means 1. I'm not sure if you want to use the timestamps as labels only, or if you want to use them for referencing as well. I think the best thing to do is, to perform all your calculations using the numeric values of time as labels, and then transform them to string timestamps for display at the end. If you want to do so, this might be a way to do it.
clear all
close all
clc
%load data from xls [1 2 44]
data=xlsread('Book1.xlsx')
%convert to number of seconds
data=round(data*(3600*24))
datasec=data
%convert to hours (divide by 3600 and round to lower)
hours=(floor(data/3600))
%Subtract from total
data=data-hours*3600
%convert to minutes (divide by 60 and round to lower)
minutes=(floor(data/60))
%subtract from total
seconds=(data-minutes*60)
%determine datastep
datastep=seconds(2)-seconds(1);
%Find size of emtpy matrix
EmptyLength=round(((max(datasec)-min(datasec))/datastep)+1)
%Generate emtpy matrix
EmptyData=cell(EmptyLength,1)
%Fill matrix with values
EmptyData(data-(min(data))+1)=cellstr(strcat(num2str(hours),':',num2str(minutes),':',num2str(seconds)) )
%Fill empty cells with NaN
EmptyData(cellfun('isempty',EmptyData))=cellstr('NaN')
By the way, a general remakt concerning your piece: started my m-file with:
_"data=xlsread('data.xlsx') %reads the excel data and creates (104737x26) matrix for i=1:length(data) if data(i)-data(i-1)>@ %I'm not sure what to put in the place of the @ since the time read on matlab is in decimals (refer to problem 1)"___
It's best to avoid loops if possible, as Matlab has to go through every cell of matrix individually, which takes a lot of time. As your matrix is quite big, this might give rise to long running times. Matlab is much quicker on matrix operations. For example, compare the following two operations:
clear all
close all
clc
A=rand(1000000,1)*100;
B=rand(1000000,1)*100;
Clong=zeros(length(A),1);
Cshort=zeros(length(A),1);
%loop
tic;
for i=1:length(A)
Clong(i,1)=A(i)-B(i);
end
%log running time 1
t1=toc;
%matrix
tic;
Cshort=A-B;
%log running time 2
t2=toc;
%Comparison of answers
Comparison=Clong==Cshort;
%Comparison of running times
tcompare=t1/t2
_
Even for this simple operation, the running time of the loop is 4 times larger than that of the matrix operations. Think about the difference for more complex operations..
Hope this helps.
Roy
0 Comments
Leonardo
on 29 Jun 2012
Edited: Leonardo
on 29 Jun 2012
Hi Chris,
I am not sure if you are aware but matlab and excel use different time formats. This brings lots of headaches for us multi-platform users but there are ways around. You can either make the time-stamp a string in Excel import it into matlab and covert the string into matlab format, or recalculate the excel values once in matlab using one of the many techniques described.
Regarding your data-gap problem the best is to make a time vector in matlab containing the whole time range in the time step you need. Then you can use the "find" command to compare it with your original time vector and obtained the "locations" where the gaps are. One you have the "Locations" you can easily re-organize your data matrix to include the NaNs.
check
to learn how to use the find command.
There are more sophisticated ways to do this but this one is more comprehensive for new users.
Give it a try and let us know how it went.
Leonardo
0 Comments
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!