Sum of previous data

Question

Mirko Piccolo 2017-9-2

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/354965-sum-of-previous-data

评论： Image Analyst 2017-9-3

This is an excel file, an exported db, where each row is a tennis match, you can read ID_Tournament, ID_Round, DATE_Game, ID1 Player1, ID2 Player2, FirstServe Player1, DoubleFault Player1, FirstServe Player2, DoubleFault Player2. I have imported the file in mathlab and now I want crerate new columns next to the last one (column I)

The new column should be the SUM of the previous match statics of that player. I'll make an example referred to the image. I would know in the row11 how many FS had player1 (ID1 488) previously. So in J11 I'll read 47 (only one match before row11 occurred). Instead in row16 in J16 the player ID1 488 had 2 matches previously, so the correct data should be 47+52=99.

How could be script? How can I set the parameter of "how many days look back"?

2 个评论
显示无隐藏无

John D'Errico 2017-9-2

Why are you doing this in MATLAB? You are THINKING in terms of a spreadsheet. So do it using a spreadsheet.

Mirko Piccolo 2017-9-2

the image that I have attached is only an example of few row and few columns. We are talking about 160k of row and each row has 20 columns less or more, so no... I can't use spreadsheet.

请先登录，再进行评论。

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

Answer 1

Andrei Bobrov 2017-9-2

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/354965-sum-of-previous-data#answer_280088

编辑：Andrei Bobrov 2017-9-2

在 MATLAB Online 中打开

load('demo.mat');
d = demoS1{:,[4,6]};
[a,ii] = sortrows(d,1);
[~,~,c] = unique(a(:,1));
d1 = accumarray(c,a(:,2),[],@(x){cumsum(x(:))});
d1 = cat(1,d1{:});
[~,i1] = sort(ii);
demoS1.last_column = d1(i1);

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Mirko Piccolo 2017-9-3

This works perfectly! could you please help me to understand the code? how I can set "how many days look back"? for example I want to sum only previous 10 days or 3 matches before that.

Thank you so much!

请先登录，再进行评论。

Answer 2

Image Analyst 2017-9-2

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/354965-sum-of-previous-data#answer_280072

在 MATLAB Online 中打开

Read in the workbook with readtable() then use cumsum():

t = readtable(filename);
% Extract all rows where player = 488
thisPlayersRows = t{:, 'ID1'} == 488;
% Get those rows into a new table.
thisPlayersTable = t(thisPlayersRows, :);
% Do a cumulative sum on FS1
sums488 = cumsum(thisPlayersTable{:, 'FS1'});

Or something like that. It's just a guess since you forgot to attach your workbook file.

4 个评论
显示 2更早的评论隐藏 2更早的评论

Mirko Piccolo 2017-9-2

demo.mat

This is my demo.mat (workspace with table and columns)

Image Analyst 2017-9-3

I think the table method would have been simpler to understand, but it looks like you've already accepted an answer that works for you, so I'm not going to work on this anymore.

请先登录，再进行评论。