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

1 次查看（过去 30 天）

显示更早的评论

alpedhuez 2020-12-8

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/685423-how-to-take-a-difference-within-the-data-of-the-same-city-but-not-across-the-city

评论： Ameer Hamza 2020-12-8

采纳的回答： Ameer Hamza

test.xlsx

在 MATLAB Online 中打开

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更早的评论隐藏 7更早的评论

Walter Roberson 2020-12-8

在 MATLAB Online 中打开

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

Assuming that no dates are missing

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 2020-12-8

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/685423-how-to-take-a-difference-within-the-data-of-the-same-city-but-not-across-the-city#answer_568913

在 MATLAB Online 中打开

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     