How to take a difference within the data of the same city but not across the city

1 次查看（过去 30 天）
alpedhuez
alpedhuez 2020-12-8
评论： Ameer Hamza 2020-12-8
Suppose I have a dataset
City Date Cumulative_Visitors
-----------------------
NY January 1 100
NY January 2 150
NY January 3 200
SF January 1 70
SF January 2 200
SF January 3 400
I want Matlab to calculate new visitors for each city and date
City Date Cumulative_Visitors New_Visitors
---------------------------------------
NY January 1 100 100
NY January 2 150 50
NY January 3 200 50
SF January 1 70 70
SF January 2 200 130
SF January 3 400 200
If I just use diff than the problem is that the first day of each city will be missing or screwed up (take a diff between the last day of NY and the first day of SF)
What can one do about this?
  9 个评论
显示 7更早的评论
Walter Roberson
Walter Roberson 2020-12-8
G = findgroups(T.City);
splitapply(@(CV) { diff([0; CV]) }, T.Cumulative_Visitors, G)
Assuming that no dates are missing
alpedhuez
alpedhuez 2020-12-8
编辑：alpedhuez 2020-12-8
Output are cells for each city. Then maybe need to merge with the orginal table? But how can one outerjoin the orginal table and this cell output?

请先登录，再进行评论。

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

采纳的回答

Ameer Hamza
Ameer Hamza 2020-12-8
Try this
T = readtable('test.xlsx');
New_Visitors = splitapply(@(x) {[x(1); diff(x)]}, T.Cumulative_visitors, findgroups(T.City));
T.New_Visitors = cell2mat(New_Visitors)
Result
>> T
T =
6×4 table
date City Cumulative_visitors New_Visitors
___________ ______ ___________________ ____________
01-Jan-2020 {'NY'} 100 100
02-Jan-2020 {'NY'} 150 50
03-Jan-2020 {'NY'} 200 50
01-Jan-2020 {'SF'} 70 70
02-Jan-2020 {'SF'} 200 130
03-Jan-2020 {'SF'} 400 200

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Calendar 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by