Sum of previous data
1 view (last 30 days)
Show older comments
Mirko Piccolo
on 2 Sep 2017
Commented: Image Analyst
on 3 Sep 2017
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/167009/image.png)
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 Comments
John D'Errico
on 2 Sep 2017
Why are you doing this in MATLAB? You are THINKING in terms of a spreadsheet. So do it using a spreadsheet.
Accepted Answer
Andrei Bobrov
on 2 Sep 2017
Edited: Andrei Bobrov
on 2 Sep 2017
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);
More Answers (1)
Image Analyst
on 2 Sep 2017
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 Comments
Image Analyst
on 3 Sep 2017
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.
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!