Sum of previous data
1 次查看(过去 30 天)
显示 更早的评论
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.
采纳的回答
Andrei Bobrov
2017-9-2
编辑:Andrei Bobrov
2017-9-2
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 个)
Image Analyst
2017-9-2
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 个评论
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.
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!