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

1 view (last 30 days)
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 Comments
alpedhuez
alpedhuez on 8 Dec 2020
Edited: alpedhuez on 8 Dec 2020
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?

Sign in to comment.

Accepted Answer

Ameer Hamza
Ameer Hamza on 8 Dec 2020
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

More Answers (0)

Tags

Community Treasure Hunt

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

Start Hunting!