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

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?

[Image Analyst]

If you need more help, attach your "data set" variable with the paper clip icon

save('answers.mat', 'yourDataSetVariable');

after you read this link.

P.S. You might like to look up findgroups(), grpstats(), and splitapply() to find groups of just one city and compute means, differences, or whatever.

[alpedhuez]

Attached a spreadsheet in the above post.

[alpedhuez]

splitapply

looks good. But in this case, it is not just take

diff

it is like

vertcat(first element of each city, diff(Cumulative_Visitors))

This "first element in the city" part does not seem to be covered by a simple definition of a function. Thsu one needs to ask questions.

[Walter Roberson]

Insert a fake 0 entry for each city, then diff() will start with the first data entry.

@(CV) diff([0; CV])

[alpedhuez]

"Insert a fake 0 entry for each city" How?

[Walter Roberson]

@(CV) { diff([0; CV]) }

This puts a 0 at the beginning of the data passed, and does the diff with the 0 inserted. The first output would be CV(1)-0 which would be CV(1)

The {} are there because grpstats expects a scalar to be returned. There could be different number of entries for different cities.

You should also consider using timetable objects and retime() with 'previous'. That would allow days to be skipped and would assume that the numbers did not change compared to the last value.

[alpedhuez]

So it will be like

G = findgroups(City);
myfunc = @(x) { diff([0; x]) }
T.new_visitors=splitapply(myfunc,T.CV,G)

?

[Walter Roberson]

G = findgroups(T.City);

splitapply(@(CV) { diff([0; CV]) }, T.Cumulative_Visitors, G)

Assuming that no dates are missing

[alpedhuez]

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?

Answer 1

[Ameer Hamza]

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     

[alpedhuez]

Thank you very much.

[Ameer Hamza]

I am glad to be of help!