Info

This question is closed. Reopen it to edit or answer.

Creating a Loop to Incorporate all Tables into One Code

1 view (last 30 days)
Hi, I am trying to create loops.
I have a code, where I am summing weekly complaints:
%2010
tt0 = readtable('BackUpbySS1.xlsx');
tt1=table2timetable(tt0)
tt1.ComplaintLocation = categorical(tt1.ComplaintLocation);
tt1.Dummy = ones(height(tt1),1);
tt2 = unstack(tt1,'Dummy','ComplaintLocation');
tt2 = fillmissing(tt2,'Constant',0);
caldiff(tt1.Date([1 end]),'days')
t = datetime(2010,1,1):caldays(1):datetime(2010,12,31);
tt3 = retime(tt2,t,'FillWithConstant','Constant',0);
tt3.DoY = day(tt3.Date,'dayofyear');
tt4 = timetable2table(tt3,'ConvertRowTimes',false);
tt4 = varfun(@sum,tt4,'GroupingVariable','DoY');
tt3.MoY = month(tt3.Date);
tt3.DoM = day(tt3.Date);
tt3.WoY = week(tt3.Date);
tt4 = timetable2table(tt3,'ConvertRowTimes',false);
tt4 = varfun(@sum,tt4,'GroupingVariables',{'WoY' 'MoY' 'DoM'});
leapDayRows = (tt4.MoY==2) & (tt4.DoM==29);
tt4(leapDayRows,:) = [];
tt5 = varfun(@sum,tt4,'GroupingVariable','WoY')
%2011
tt6 = readtable('BackUpbySS1.xlsx');
tt7=table2timetable(tt6)
tt7.ComplaintLocation = categorical(tt7.ComplaintLocation);
tt7.Dummy = ones(height(tt7),1);
tt8 = unstack(tt7,'Dummy','ComplaintLocation');
tt8 = fillmissing(tt8,'Constant',0);
caldiff(tt7.Date([1 end]),'days')
t0 = datetime(2011,1,1):caldays(1):datetime(2011,12,31);
tt9 = retime(tt8,t0,'FillWithConstant','Constant',0);
tt9.DoY = day(tt9.Date,'dayofyear');
tt10 = timetable2table(tt9,'ConvertRowTimes',false);
tt10 = varfun(@sum,tt10,'GroupingVariable','DoY');
tt9.MoY = month(tt9.Date);
tt9.DoM = day(tt9.Date);
tt9.WoY = week(tt9.Date);
tt10 = timetable2table(tt9,'ConvertRowTimes',false);
tt10 = varfun(@sum,tt10,'GroupingVariables',{'WoY' 'MoY' 'DoM'});
leapDayRows = (tt10.MoY==2) & (tt10.DoM==29);
tt10(leapDayRows,:) = [];
tt11 = varfun(@sum,tt10,'GroupingVariable','WoY')
%2012
tt12 = readtable('BackUpbySS1.xlsx');
tt13=table2timetable(tt12)
tt13.ComplaintLocation = categorical(tt13.ComplaintLocation);
tt13.Dummy = ones(height(tt13),1);
tt14 = unstack(tt13,'Dummy','ComplaintLocation');
tt14 = fillmissing(tt14,'Constant',0);
caldiff(tt13.Date([1 end]),'days')
t1 = datetime(2012,1,1):caldays(1):datetime(2012,12,31);
tt15 = retime(tt14,t1,'FillWithConstant','Constant',0);
tt15.DoY = day(tt15.Date,'dayofyear');
tt16 = timetable2table(tt15,'ConvertRowTimes',false);
tt16 = varfun(@sum,tt16,'GroupingVariable','DoY');
tt15.MoY = month(tt15.Date);
tt15.DoM = day(tt15.Date);
tt15.WoY = week(tt15.Date);
tt16 = timetable2table(tt15,'ConvertRowTimes',false);
tt16 = varfun(@sum,tt16,'GroupingVariables',{'WoY' 'MoY' 'DoM'});
leapDayRows = (tt16.MoY==2) & (tt16.DoM==29);
tt16(leapDayRows,:) = [];
tt17 = varfun(@sum,tt16,'GroupingVariable','WoY')
Time tables tt5, tt11, and tt17 are weekly sums for 2010, 2011, and 2012, respectively. On the columns, it lists each locations, which are named S1, S2, S3, ..., S14
I want to create a table, where these sums are all in one table, where it reads from week 1 to 159, chronologically, from the first week of 2010 to the last week of 2012.
Before, I have been doing it manually.
After I compile the weeks where they are sequential over the course of the years, I then do the same thing for a different variable. The code I showed is for Back Ups. I'll run the code with a similar spreadsheet for Catch, Manhole, and Street.
Then, I input them on the same spreadsheet for each location.
Finally, I end up with a spreadsheet, "S1forPoissonW.xlsx". For each location, I'll have the other spreadsheets, "S2forPoissonW.xlsx", "S3 for PoissonW.xlsx"... etc.
Ulitmately, I'll input the spreadsheet into this code for a linear regression model:
tt1 = readtable('S1forPoissonW.xlsx');
dsa = tt1;
modelspec = 'Street ~ Catch*Back*Break*Manhole*PRCP - Catch:Back:Break:Manhole:PRCP';
mdl = fitglm(dsa,modelspec,'Distribution','poisson')
For each spreadsheet, I'll run the code and get the results.
However, I am wondering if there is one way to loop all this together into one code.
Or, if I still need two codes, if someone can just help condense any part of this, it would be greatly appreciated.
Thanks,
C

Answers (0)

Products

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!