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

Question

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?

Answer 1

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