change in xlrange of xlsread while sing loop
3 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
Hi
I am using xlsread with different ranges. I am trying to read excel data which the ranges differ in each iteration of loop according to the number of days in a month and in each year. How can I define these changes in xlRange? My code snippet is as follows that can show my intention. My excel file is also attached.
Note: I am puting these data to a cell file with struct of the years.
years= 20
Observation = cell(years);
N=31
for yy=1:years
year=2000+yy-1
for month=1:12
for day=1:N
if month==4| month==6| month==9| month==11
N=30
elseif month==2
N=28
else year==2000& month==2| year==2004& month==2| year==2008& month==2| year==2012& month==2| year==2016& month==2
N=29
end
Data= xlsread( Data , 'sheet1' , [range???? ':' range???]); % my question is here?
Observation{yy}.Observation{month, day} =Data
end
end
end
2 comentarios
Jan
el 13 de Nov. de 2021
The determination of the number of days per month is not working corretly: The Februrary gets 28 days in all cases: "if month==2" catchs all Februraries and the following if branch is not entered. The 31 is set in the first iteration only.
Better:
if any(month == [4, 6, 9, 11))
N = 30;
elseif month == 2
N = 28 + (~mod(year, 4) & (mod(year, 100) | ~mod(year, 400)));
else
N = 31;
end
The command cell(years) creates a 20x20 cell matrix. I guess you want cell(1, years).
Respuestas (2)
dpb
el 13 de Nov. de 2021
That's the hard way to go at it...just read the spreadsheet into a table and operate over the regions of interest -- simple example
>> tdData=readtable('Data.xlsx'); % read the spreadsheet to the table
>> head(tData) % sample of what get...
ans =
8×4 table
amount year month day
______ ____ _____ ___
0 2000 6 1
0 2000 6 2
0 2000 6 3
0 2000 6 4
0 2000 6 5
0 2000 6 6
0 2000 6 7
0 2000 6 8
>> format bank, format compact % show two decimal places
>> groupsummary(tData,{'year'},"sum",{'amount'}) % total amounts by year
ans =
18×3 table
year GroupCount sum_amount
____ __________ __________
2000.00 214.00 507.00
2001.00 365.00 1739.00
2002.00 365.00 1113.50
2003.00 365.00 1460.50
2004.00 366.00 1537.00
2005.00 365.00 862.00
2006.00 365.00 1411.00
2007.00 365.00 1054.50
2008.00 366.00 1089.00
2009.00 365.00 1128.50
2010.00 365.00 839.00
2011.00 365.00 1474.50
2012.00 366.00 1297.00
2013.00 365.00 1043.00
2014.00 365.00 1781.00
2015.00 365.00 1592.80
2016.00 366.00 854.30
2017.00 79.00 0.00
>>
I just did by year so output would be relatively short; you can do monthly or daily or combinations at will.
The other is doable although I don't recomend it as the way to deal with the data you have -- the logic can be simplified significantly with builtin MATLAB functions...
>> cumDays=0;
for yy=1:3
for m=1:4
cumDays=cumDays+eomday(yy+1999,m)
end,end
cumDays =
31.00
cumDays =
60.00
cumDays =
91.00
cumDays =
121.00
cumDays =
152.00
cumDays =
180.00
cumDays =
211.00
cumDays =
241.00
cumDays =
272.00
cumDays =
300.00
cumDays =
331.00
cumDays =
361.00
>>
shows how to accumulate the days in a year accounting for leap years and days in each month.
The problem is your file doesn't begin on Jan 1 of a given year and so unless you read it first, you don't know where to begin counting.
It's far easier and better to just use the data itself.
NB: xlsread has been deprecated in favor or readtable and friends; they peform much faster and in general are far better to use with new code.
3 comentarios
dpb
el 14 de Nov. de 2021
Well, you can have your data however you want it, but rearrange it after you read it, not while you're trying to read it.
It's far more complicated to build those range variables than to use the data itself to select from/with what you want and how you want it, particularly given the problem that you don't know a priori from whence to start counting because the data begin at an arbitrary point. Unless, of course, you manually enter the start location every time which pretty-much defeats the point of having a programming tool to help.
dpb
el 15 de Nov. de 2021
And I have no idea what am supposed to make of the empty image you attached -- explain/illustrate what you think that represents in the data file if you want somebody to try to build whatever it is from the given data.
Just looking at your original code, I would submit that almost any use I can imagine of data by year/month/day can't be far more easily processed by a table with rowfun, groupsummary and friends or as a timetable and retime and grouping variables if there are other commensurate variables besides just the one observation in the attached data set.
Will be MUCH easier than iterating through a nested structure array...
Jan
el 13 de Nov. de 2021
Why do you want to import ranges at all? Import the complete file as a matrix or table and filter out the needed values by something like:
year = AllData(:, 2);
month = AllData(:, 3);
thisAmount = AllData(year == 2008 & month == 2, 1);
0 comentarios
Ver también
Categorías
Más información sobre Data Type Conversion en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!