How to count values based on date

조회 수: 2 (최근 30일)
Zekeftw
Zekeftw 2017년 7월 9일
편집: Andrei Bobrov 2017년 7월 10일
I'm trying to count how many orders each customer placed each month. The attached spreadsheet has the data for weekly orders in this format:
Sunday Saturday Week Of Andrew Monica Ana
5/28/17 6/3/17 5/28/17 36 37 10
6/4/17 6/10/17 6/4/17 31 51 17
6/11/17 6/17/17 6/11/17 40 36 11
6/18/17 6/24/17 6/18/17 49 55 21
6/25/17 7/1/17 6/25/17 41 46 21
7/2/17 7/8/17 7/2/17 29 27 12
I need to count how many total orders each user placed in June (for example). I have no experience working with dates. Any help would be greatly appreciated.
Thanks!
  댓글 수: 1
Zekeftw
Zekeftw 2017년 7월 10일
Thank you all for your help. I went with a variation of both and ultimately got what i was looking for.
% Data
Data = readtable('Customer_Orders_Weekly.xls');% Read Data
weeklySum = grpstats(Data(74:78,4:end),[],'sum'); % Sum weekly orders
headers = Data(1,4:end).Properties.VariableNames; % Get headers for xLabels
monthlySum = weeklySum{1,2:end}; % Array of data for plot
% Plot
hbar = bar(monthlySum);
xLabels = headers;
set(gca, 'XTick', 1:21, 'XTickLabel', xLabels);
xtickangle(45);
% Data labels
x = get(hbar,'XData');
y = get(hbar,'YData');
ygap = 0.1;
ylimits = get(gca,'YLim');
set(gca,'YLim',[ylimits(1),ylimits(2)+0.2*max(y)]);
dataLabels = cellstr(num2str(monthlySum')); %//'
for i = 1:length(x) % Loop over each bar
xpos = x(i); % Set x position for the text label
ypos = y(i) + ygap; % Set y position, including gap
htext = text(xpos,ypos,dataLabels{i}); % Add text label
set(htext,'VerticalAlignment','bottom', 'HorizontalAlignment','center')
end

댓글을 달려면 로그인하십시오.

답변 (3개)

Image Analyst
Image Analyst 2017년 7월 9일
Try readtable(), and grpstats() in the Stats tool box.
but some of your start and stop times cross the first day of the month and there is no breakdown of what counts should be allocated to each month. So what do you want to do in that case?
  댓글 수: 1
Zekeftw
Zekeftw 2017년 7월 9일
I'm mostly looking for an average and trend so the overlap is not a big deal. I'm mostly trying to count to totals based on the "Week Of" column. For every week that covers June (for example)

댓글을 달려면 로그인하십시오.


Walter Roberson
Walter Roberson 2017년 7월 9일
t = readtable('Customer_Orders_Weekly.xls');
counts = grpstats(t(:,3:end), 'WeekOf___', @sum);
selected = counts(:,3:end);
selected.Properties.VariableNames = t.Properties.VariableNames(4:end);
selected

Andrei Bobrov
Andrei Bobrov 2017년 7월 10일
편집: Andrei Bobrov 2017년 7월 10일
MATLAB R2016b and later
T = readtable('Customer_Orders_Weekly.xls');
TT = table2timetable(T,'RowTimes','WeekOf___');
TT_out = retime(TT,'monthly','mean');
or MATLAB R2016a and eirliar
T = readtable('Customer_Orders_Weekly.xls');
T.mounth = month(TT.WeekOf___);
T.year = year(TT.WeekOf___);
T_out = varfun(@mean,T,'GroupingVariables',{'mounth','year'});
T_out = T_out(:,[1:2,7:end]);

카테고리

Help CenterFile Exchange에서 Bar Plots에 대해 자세히 알아보기

Community Treasure Hunt

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

Start Hunting!

Translated by