How to count values based on date

Question

Zekeftw 2017-7-9

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/347908-how-to-count-values-based-on-date

编辑： 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 个评论
显示 -1更早的评论隐藏 -1更早的评论

Zekeftw 2017-7-10

在 MATLAB Online 中打开

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

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Image Analyst 2017-7-9

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/347908-how-to-count-values-based-on-date#answer_273419

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 个评论
显示 -1更早的评论隐藏 -1更早的评论

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)

请先登录，再进行评论。

Answer 2

Walter Roberson 2017-7-9

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/347908-how-to-count-values-based-on-date#answer_273465

在 MATLAB Online 中打开

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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

Answer 3

Andrei Bobrov 2017-7-10

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/347908-how-to-count-values-based-on-date#answer_273504

编辑：Andrei Bobrov 2017-7-10

在 MATLAB Online 中打开

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]);