Sum of previous data

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"?

[John D'Errico]

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

[Mirko Piccolo]

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]

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);

[Mirko Piccolo]

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]

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.

[Mirko Piccolo]

I want to see the same table with ALL the matches, without filter on id player but with extra columns with the sum. I think a CICLO FOR could works but I'm not sure.

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.

[Image Analyst]

thisPlayersTable does have all the matches and all columns for the player you specify.

Sorry, can't help without the data file.

[Mirko Piccolo]

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

[Image Analyst]

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.