I am looking at monthly storage data for a watershed. The data is from each month from 2005-2015. I have a variable with the datetimes, 'MonthDates', for the storage (i.e. Jan, Feb, Mar), and I have a variable with the storage, 'S', for each month. I created a table, 'storage', that has both of these variables. I am trying to find out which month has the lowest storage on average. This would be a lot easier for me to find if I could format the table so that the storage for each month is condensed into one place, rather than each month repeating ten times throughout the column in the table. I attached a photo of the table that I created in Matlab and a photo of what I would like the table format to look like in Excel. I am new to Matlab and am not very familiar with all of the commands, so any help is useful if you think there is a better way to find the average for each month.
MATLAB: Condense repeating values in table into one cell
averagemonthly averagerepeating termtable
Related Solutions
John, I'll assume you have the Financial Toolbox. I think what you're looking to do is to calculate mean price, by ticker and week, with the exemplar date for each week being the last business day. There are several ways you could do that. Since you already have a table, varfun is one easy way.
First cook up some fake data.
>> Date = datetime(2016,3,[22;23;24;28;29;21;22;23;24;28;29],'Format','eee dd-MMM-yyyy');>> Ticker = categorical({'A'; 'A'; 'A'; 'A'; 'A'; 'B'; 'B'; 'B'; 'B'; 'B'; 'B'});>> Price = rand(size(Ticker));>> TS = table(Date,Ticker,Price)TS = Date Ticker Price _______________ ______ ________ Tue 22-Mar-2016 A 0.13197 Wed 23-Mar-2016 A 0.94205 Thu 24-Mar-2016 A 0.95613 Mon 28-Mar-2016 A 0.57521 Tue 29-Mar-2016 A 0.05978 Mon 21-Mar-2016 B 0.23478 Tue 22-Mar-2016 B 0.35316 Wed 23-Mar-2016 B 0.82119 Thu 24-Mar-2016 B 0.015403 Mon 28-Mar-2016 B 0.043024 Tue 29-Mar-2016 B 0.16899
Now find the last business day in each week, by first finding the end of the week and then stepping back.
>> hol = holidays(datetime(2016,1,1),datetime(2016,12,31))>> EOWDate = dateshift(TS.Date,'end','week');>> TS.BusDate = busdate(EOWDate,'previous',hol);>> TS.BusDate.Format = ['eee ' TS.BusDate.Format]TS = Date Ticker Price BusDate _______________ ______ _______ ___________________ Tue 22-Mar-2016 A 0.64912 Thu Thu 24-Mar-2016 Wed 23-Mar-2016 A 0.73172 Thu Thu 24-Mar-2016 Thu 24-Mar-2016 A 0.64775 Thu Thu 24-Mar-2016 Mon 28-Mar-2016 A 0.45092 Fri Fri 01-Apr-2016 Tue 29-Mar-2016 A 0.54701 Fri Fri 01-Apr-2016 Mon 21-Mar-2016 B 0.29632 Thu Thu 24-Mar-2016 Tue 22-Mar-2016 B 0.74469 Thu Thu 24-Mar-2016 Wed 23-Mar-2016 B 0.18896 Thu Thu 24-Mar-2016 Thu 24-Mar-2016 B 0.68678 Thu Thu 24-Mar-2016 Mon 28-Mar-2016 B 0.18351 Fri Fri 01-Apr-2016 Tue 29-Mar-2016 B 0.36848 Fri Fri 01-Apr-2016
Finally, apply mean to the prices, grouping by ticker and week.
>> varfun(@mean,TS,'GroupingVariables',{'Ticker' 'BusDate'},'InputVariables','Price')ans = Ticker BusDate GroupCount mean_Price ______ ___________ __________ __________ A 24-Mar-2016 3 0.67672 A 01-Apr-2016 2 0.31749 B 24-Mar-2016 4 0.35613 B 01-Apr-2016 2 0.10601
Here's a demo that identies exact datetime matches. It assumes you've already converted the timestamps to datetime values instead of char using t = datetime(DateStrings,'InputFormat',infmt).
% Create demo data - all column vectors
A.timestamp = datetime(2020,1,1)+days(1:10)';A.waveheight = rand(10,1); B.timestamp = datetime(2020,1,1)+days(1:15)';B.waveheight = rand(15,1); C.timestamp = datetime(2020,1,1)+days(1:5)';C.waveheight = rand(5,1); % Find which datetime values *exactly* match in all 3 structures.
% dtInAll is a vector of datetime values that exist in all 3 strcutures.
dtInAll = intersect(intersect(A.timestamp,B.timestamp),C.timestamp);
Now you can get the indices of datetime matches for each structure and extract the waveheight values.
% waves is a nx3 matrix of waveheights where columns are defined by
% [A,B,C] structures and rows defined by deInAll.
waves = [A.waveheight(ismember(A.timestamp, dtInAll)), ... B.waveheight(ismember(B.timestamp, dtInAll)), ... C.waveheight(ismember(C.timestamp, dtInAll))];
Summarize the resuls in a table (or timetable)
T = table(); T.TimeStamp = dtInAllT = [T, array2table(waves,'VariableNames',{'A','B','C'})];% 5×4 table
% TimeStamp A B C
% ___________ _______ ________ _______
% 02-Jan-2020 0.94479 0.40391 0.64775
% 03-Jan-2020 0.49086 0.096455 0.45092
% 04-Jan-2020 0.48925 0.13197 0.54701
% 05-Jan-2020 0.33772 0.94205 0.29632
% 06-Jan-2020 0.90005 0.95613 0.74469
Best Answer