make calculations with excel file data
Show older comments
I'm a newbie in matlab, so can anyone help me in the following question?
I want to use the data of a excel file in a matlab script.
I already import data with the following code (weather data file):
[fileName,pathname] = uigetfile({'*.xlsx'},'Select Location');
nomeficheiro=strcat(pathname,fileName);
[a,b,c]=xlsread(nomeficheiro, 'A2:AJ8762');
location = c(1,2);
set(handles.location_text,'String',location);
In this example I get the location of the weather data using the value store in row 1 and column 2.
In this file in the column 8, we have 8760 hourly values of ambient temperature.
I need to do a calculation with all the values.
For example, import value row 1 and column 8, make calculation in matlab script, next import value row 2 and column 8, make calculation in matlab script, next import value row 3 and column 8, make calculation in matlab script,... and so on....
I want to automate this calculation
I want to store the results of the calculations in textbox in gui.
Thanks
2 Comments
All the numeric data are in the numeric array a; in Matlab one generally tries to write code to make use of the fact that it can handle full arrays at a time.
What are the calculations you wish to perform on the data in column 8? If it is the same for each, then it will be quite simple; if there's something different for various ones, you'll have to have a way to know which and what do do for each...
Joaquim Monteiro
on 10 Sep 2015
Accepted Answer
dpb
on 10 Sep 2015
"_Is the same calculation for all...of the column 8."_
Tf=a(:,8)*1.8+32; % convert column 8 (assume T in C) to F
Done for the whole year in one swell foop...that's the power of Matlab; no loops needed most of the time and are to be avoided when not.
Above gives you a new variable; if you don't need to keep the original but want to overwrite, then simply
a(:,8)=a(:,8)*1.8+32; % convert column 8 (assume T in C) to F
Since you still haven't made us privy to the actual calculation, I just made up an example; simply replace the specific with whatever yours is.
19 Comments
Joaquim Monteiro
on 11 Sep 2015
dpb
on 11 Sep 2015
Indeed, it is easy..
Pc=a(:,8)+253.2+a(:,10);
I think you need to open the documentation and begin at the "Getting Started" section and work through the tutorial sections on the basics of Matlab syntax, array addressing and so on. It's a pretty thorough introduction done by example.
Joaquim Monteiro
on 11 Sep 2015
PcTot=sum(reshape(Pc,24,[])).';
I'll leave it as "exercise for the student" to use a smaller case of a vector of length N to be summed over a subset of M (N divisible by M) groups. Try, for example, N=8 and M=2 so you can test the result and see the values to see the "how" and "why" it works. The data could be x=rand(8,1);, for example.
HINT: The key is in the Matlab array storage order of being column major and that sum and other Matlab functions by default work over the first dimension or by column. Again, this is outlined in the "Getting Started" tutorial (albeit the use of storage order which the above "trick" uses isn't explicitly demonstrated but that is simply a utilization of understanding memory order).
Joaquim Monteiro
on 14 Sep 2015
Joaquim Monteiro
on 15 Sep 2015
dpb
on 15 Sep 2015
NO!!! Try it and see...Matlab is not Excel.
Now, if you want to add the result to a spreadsheet you'll have to write it as at the moment of calculation the result exists only in memory...
Joaquim Monteiro
on 15 Sep 2015
dpb
on 15 Sep 2015
I don't understand what's not to understand... :)
Have you done the first step? If so, you should have the newly-computed arrays in memory you can see at the command line with
whos Pc*
which will result in showing you the type and size of the arrays. They should be 8760 rows long for Pc and 365 for PcTot--that happens automagically in Matlab by virtue of its array syntax. Unlike Excel wherein you have to copy every result into a separate cell, Matlab deals with all values in an array in one expression.
Again I refer you to the "Getting Started" section of the documentation for details of Matlab syntax and examples of how it works in tutorial form...
After rereading your original question to refresh my memory on what you had done so far, I noticed the last comment of "store the results of the calculations in textbox in gui." That's likely going to be a difficult user interface -- a text box box with 8760 or even the 365 lines is just too much data to look at conveniently that way. Is there really any reason someone will want to see all those individual values, even? I'd suggest plotting them instead or summarizing even further to a set of descriptive statistics or the like such as [mean, mode, min, max, ...] along with the associated date or similar. That is succinct enough to show and likely will have all the info someone will need on the process interactively.
However, whether you choose the latter or think you must go with the full monte, use a table not a text box.
doc uitable
But, "I don't do windows" so I'm not the best person to ask about gui components in depth altho I think the general precept above is definitely true that large lists of values in a gui are, generally speaking, useless. Doing such is the result of the unfortunate ubiquitousness of the spreadsheet paradigm...
Joaquim Monteiro
on 15 Sep 2015
dpb
on 15 Sep 2015
"use the result of a row calculation as input of the follow row."
Pc1=a(:,8)+253.2+a(:,10);
Pc2=Pc1-a(:,8)+253.2+a(:,10);
The above reduces to
Pc2=a(:,8)+253.2+a(:,10)-(a(:,8)+253.2+a(:,10));
which is identically zero.
Show a (short) segment of the data in the two columns of interest and what the actual calculation you want to be for a few rows--three or four should be enough.
It seems to me the result is already calculated but perhaps I indeed don't follow what you're trying to actually compute.
Joaquim Monteiro
on 16 Sep 2015
dpb
on 16 Sep 2015
Again, without a specific computation I don't know but reading between the lines of your previous computation where you wrote
Pc2=Pc1-a(:,8)+253.2+a(:,10);
mayhaps what you're looking for is
PcDelt=diff(Pc);
which will be the differential between observations. Note, of course, as a difference there will be one less observation so you may want to augment the result with a dummy value at the beginning to keep the length consistent with the raw data--
PcDelt=[0; diff(Pc)];
Joaquim Monteiro
on 17 Sep 2015
dpb
on 17 Sep 2015
No, it's still just a link, not an attachment...but it really isn't the file that's important, it's the description of what the calculation you're stumped at really is...I was just suggesting you take a few lines and demonstrate specifically what you are trying to calculate and post that as a workaround the attempt to only describe the desired result.
Did my above guess regarding diff hit the mark?; you didn't comment at all on it.
Joaquim Monteiro
on 21 Sep 2015
Joaquim Monteiro
on 23 Sep 2015
Edited: dpb
on 24 Sep 2015
Joaquim Monteiro
on 24 Sep 2015
More Answers (1)
OK, that's one interpretation but seems unlikely that that's what you really, really intend...that's going to "blow up" to an extremely large number in the end...
But, for the above as written simplest coding is in a loop although there are ways to vectorize it, will leave those for more advanced lesson particularly as I don't expect you're going to like the result as it will overflow in all likelihood by the time you reach the end...
>> pc=d(:,8)+253.2+d(:,16);
>> for i=2:length(pc),pc(i)=pc(i-1).*pc(i)-d(i,20),end
pc =
1.0e+05 *
0.0089
8.3553
0.0100
0.0100
0.0096
pc =
1.0e+08 *
0.0000
0.0084
8.3443
0.0000
0.0000
pc =
1.0e+11 *
0.0000
0.0000
0.0083
8.3401
0.0000
pc =
1.0e+14 *
0.0000
0.0000
0.0000
0.0083
8.0132
>>
Above shows the same results as your calculation with the entry of each loop in range of the display each iteration.
I don't understand what it is you're actually physically trying to compute so can't judge the appropriateness of the formulation for the purpose. BUT, note that pc(1) has units of whatever d(8) and d(20) are but you've then squared that value and added another term in only units(d). Unless these are dimensionless, that's clearly in error for a physical system computation.
4 Comments
Joaquim Monteiro
on 25 Sep 2015
Hmmm....does it stay bounded over the array length?
>> p=pc(1);
for i=1:8760
p=p*975;
if (realmax/p)<975,break,end
end
fprintf('%d %e\n',i, p)
102 6.691326e+307
>>
So, assuming the rest of the data are similar to those provided, 975 is roughly the average of the first half-dozen or so values and hence is the ratio of pc(N+1)/pc(N) and so the estimate of how large a value you can compute is given by the above at only a few over 100 observations.
Methinks, somehow, this can't be correct, whatever it is.
Joaquim Monteiro
on 26 Sep 2015
dpb
on 26 Sep 2015
OK, just so's you recognize the issue...
Categories
Find more on String Parsing in Help Center and File Exchange
Products
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
