Cumulative sum per ID

Hello all,

I have a large dataset like these:

YTD=

ID Variable1 Variable2

'2019-01-07T06:53:32.000Z' 1738 0 1 100 -4.04000000000000 10000

'2019-01-07T07:00:06.000Z' 1738 0 1 100 1.58000000000000 10000

'2019-01-07T08:34:42.000Z' 1738 0 1 100 0.810000000000000 10000

'2019-01-07T09:02:30.000Z' 1738 1 1 100 0.910000000000000 10000

'2019-01-07T14:13:09.000Z' 1738 0 1 100 2.01000000000000 10000

'2019-01-07T14:27:46.000Z' 1738 0 1 100 0.670000000000000 10000

'2019-01-07T14:43:58.000Z' 1738 0 1 100 -4.10000000000000 10000

'2019-01-07T14:56:56.000Z' 1738 0 1 100 0.200000000000000 10000

'2019-01-07T15:02:59.000Z' 1738 0 1 100 0.440000000000000 10000

'2019-01-07T15:44:32.000Z' 1738 1 1 100 0.150000000000000 10000

'2019-01-07T16:12:53.000Z' 1738 0 1 100 1.11000000000000 10000

'2019-01-07T16:18:56.000Z' 1738 1 1 100 1.51000000000000 10000

'2019-01-07T16:28:29.000Z' 1738 0 1 100 0.430000000000000 10000

'2019-01-09T08:53:28.000Z' 1738 1 3 100 0 10000

'2019-01-09T08:53:28.000Z' 1738 0 3 100 -8.48000000000000 10000

'2019-01-09T08:53:34.000Z' 1738 0 3 100 -6.20000000000000 10000

'2019-01-09T08:53:34.000Z' 1738 1 3 100 0 10000

'2019-01-19T08:53:19.000Z' 1746 1 1 5000 -0.0500000000000000 10000

'2019-01-19T08:53:20.000Z' 1746 0 1 100 0 10000

'2019-01-19T08:59:38.000Z' 1746 1 1 100 -5.51000000000000 10000

'2019-01-19T08:59:40.000Z' 1746 1 1 100 -1.87000000000000 10000

'2019-01-19T08:59:40.000Z' 1746 1 1 100 -3.28000000000000 10000

'2019-01-19T08:59:42.000Z' 1746 1 1 100 -1.86000000000000 10000

'2019-01-19T08:59:43.000Z' 1746 1 1 100 -1.86000000000000 10000

'2019-01-19T08:59:44.000Z' 1746 1 1 100 -2.30000000000000 10000

'2019-01-19T09:04:34.000Z' 1746 0 1 100 -2.54000000000000 10000

'2019-01-19T09:04:42.000Z' 1746 0 1 100 -2.60000000000000 10000

'2019-01-19T09:04:42.000Z' 1746 0 1 100 -2.60000000000000 10000

'2019-01-19T09:04:44.000Z' 1746 0 1 100 -2.84000000000000 10000

'2019-01-19T09:04:48.000Z' 1746 0 1 100 -2.95000000000000 10000

'2019-01-19T09:04:50.000Z' 1746 0 1 100 -3.28000000000000 10000

'2019-01-19T09:06:22.000Z' 1746 0 1 100 -4.15000000000000 10000

'2019-01-02T07:22:30.000Z' 1759 1 1 100000 -63.5300000000000 10000

'2019-01-03T04:11:54.000Z' 1759 1 1 100000 -94.2100000000000 10000

'2019-01-03T08:33:10.000Z' 1759 1 1 100000 5.67000000000000 10000

'2019-01-03T08:34:30.000Z' 1759 1 1 10000 -0.100000000000000 10000

'2019-01-04T07:06:48.000Z' 1759 1 1 300000 192.030000000000 10000

'2019-01-04T08:40:09.000Z' 1759 1 1 10000 -339.750000000000 10000

'2019-01-04T20:09:31.000Z' 1759 1 1 300000 -207.870000000000 10000

'2019-01-05T00:24:21.000Z' 1759 1 1 300000 -12.2400000000000 10000

'2019-01-05T00:25:32.000Z' 1759 1 1 10000 -101.030000000000 10000

'2019-01-05T00:41:51.000Z' 1759 1 1 10000 -17.3300000000000 10000

'2019-01-05T01:11:36.000Z' 1759 1 1 100000 -112.020000000000 10000

'2019-01-05T01:54:24.000Z' 1759 1 1 100000 -11.3300000000000 10000

'2019-01-05T07:08:28.000Z' 1759 1 1 300000 -19.2300000000000 10000

'2019-01-08T08:23:48.000Z' 1759 1 1 50000 -0.840000000000000 10000

'2019-01-08T14:54:06.000Z' 1759 1 1 10000 101.490000000000 10000

'2019-01-09T00:11:14.000Z' 1759 1 1 50000 -49.2400000000000 10000

'2019-01-09T00:11:28.000Z' 1759 1 1 100000 -404.560000000000 10000

'2019-01-09T00:12:16.000Z' 1759 1 1 300000 477.570000000000 10000

'2019-01-09T01:18:49.000Z' 1759 1 1 300000 -30.2400000000000 10000

'2019-01-09T03:38:41.000Z' 1759 1 1 10000 -11.9200000000000 10000

'2019-01-09T05:42:41.000Z' 1759 1 1 10000 -11.6000000000000 10000

'2019-01-09T05:46:19.000Z' 1759 1 1 200000 16.7200000000000 10000

'2019-01-09T07:46:59.000Z' 1759 1 1 200000 -10.4400000000000 10000

'2019-01-09T10:14:06.000Z' 1759 1 1 10000 9.52000000000000 10000

'2019-01-09T10:24:57.000Z' 1759 1 1 10000 -10.9500000000000 10000

'2019-01-09T11:38:35.000Z' 1759 1 1 200000 -25.9000000000000 10000

'2019-01-09T19:21:41.000Z' 1759 1 1 300000 -22.3800000000000 10000

'2019-01-09T22:07:18.000Z' 1759 1 1 10000 -78.4300000000000 10000

'2019-01-09T22:10:56.000Z' 1759 1 1 200000 -18.4000000000000 10000

'2019-01-09T22:11:17.000Z' 1759 1 1 13000 -0.130000000000000 10000

'2019-01-10T00:32:31.000Z' 1759 1 1 300000 -51 10000

'2019-01-10T00:37:40.000Z' 1759 1 1 30000 -0.370000000000000 10000

'2019-01-10T00:54:55.000Z' 1759 1 1 10000 -21.4000000000000 10000

'2019-01-10T01:15:30.000Z' 1759 1 1 300000 -24.3900000000000 10000

'2019-01-10T02:27:35.000Z' 1759 1 1 300000 -25.2300000000000 10000

'2019-01-10T04:34:52.000Z' 1759 1 1 100000 -1.97000000000000 10000

'2019-01-11T03:04:42.000Z' 1759 1 1 300000 36.1800000000000 10000

'2019-01-11T03:08:23.000Z' 1759 1 1 100000 15.7800000000000 10000

'2019-01-11T03:13:07.000Z' 1759 1 1 300000 -22.2300000000000 10000

'2019-01-11T04:25:06.000Z' 1759 1 1 300000 -34.7100000000000 10000

'2019-01-11T04:36:10.000Z' 1759 1 1 200000 -8.08000000000000 10000

'2019-01-11T04:56:57.000Z' 1759 1 1 10000 48.7500000000000 10000

'2019-01-11T05:10:02.000Z' 1759 1 1 200000 -14.1000000000000 10000

'2019-01-11T05:56:06.000Z' 1759 1 1 10000 -11.4100000000000 10000

'2019-01-11T06:46:39.000Z' 1759 1 1 100 0.110000000000000 10000

'2019-01-11T06:59:17.000Z' 1759 1 1 10000 -12.9000000000000 10000

'2019-01-11T07:26:25.000Z' 1759 1 1 10000 -17.2100000000000 10000

'2019-01-11T08:00:41.000Z' 1759 1 1 10000 -4.63000000000000 10000

'2019-01-11T08:54:18.000Z' 1759 1 1 10000 -2.16000000000000 10000

'2019-01-11T11:04:10.000Z' 1759 1 1 10000 2.66000000000000 10000

'2019-01-11T11:04:46.000Z' 1759 1 1 100000 9.63000000000000 10000

'2019-01-11T13:46:32.000Z' 1759 1 1 100000 -10.3000000000000 10000

'2019-01-11T13:47:49.000Z' 1759 1 1 10000 1.36000000000000 10000

'2019-01-12T04:37:23.000Z' 1759 1 1 10000 342.730000000000 10000

'2019-01-12T07:32:27.000Z' 1759 1 1 10000 65.1300000000000 10000

'2019-01-12T07:33:09.000Z' 1759 1 1 10000 -3.24000000000000 10000

'2019-01-12T09:50:07.000Z' 1759 1 1 100000 -60.5200000000000 10000

'2019-01-12T11:04:08.000Z' 1759 1 1 10000 -1.18000000000000 10000

'2019-01-12T12:05:47.000Z' 1759 1 1 10000 -39.2100000000000 10000

'2019-01-12T12:16:37.000Z' 1759 1 1 100000 90.9600000000000 10000

'2019-01-12T12:20:49.000Z' 1759 1 1 10000 -15.6900000000000 10000

'2019-01-12T14:33:23.000Z' 1759 1 1 100000 -19.4300000000000 10000

'2019-01-12T14:33:52.000Z' 1759 1 1 10000 -41.2200000000000 10000

'2019-01-12T16:32:48.000Z' 1759 0 1 100 0.850000000000000 10000

'2019-01-13T09:09:52.000Z' 1759 1 1 100000 -107.990000000000 10000

'2019-01-13T10:24:40.000Z' 1759 0 1 100 -5.02000000000000 10000

'2019-01-13T14:41:24.000Z' 1759 1 1 100000 56.4800000000000 10000

'2019-01-13T20:57:29.000Z' 1759 1 1 100000 -90.6100000000000 10000

'2019-01-14T03:43:44.000Z' 1759 1 1 10000 26.9200000000000 10000

'2019-01-14T04:15:00.000Z' 1759 1 1 10000 -15.6100000000000 10000

'2019-01-14T04:39:01.000Z' 1759 0 1 10000 20.1600000000000 10000

'2019-01-14T04:46:09.000Z' 1759 1 1 10000 -1.75000000000000 10000

'2019-01-14T05:08:17.000Z' 1759 0 1 10000 -1.41000000000000 10000

'2019-01-14T05:16:31.000Z' 1759 1 1 100000 31.6600000000000 10000

'2019-01-14T05:21:41.000Z' 1759 1 1 10000 -17.4700000000000 10000

'2019-01-14T05:30:47.000Z' 1759 0 1 10000 -21.6300000000000 10000

'2019-01-14T05:49:12.000Z' 1759 1 1 10000 -15.1300000000000 10000

for each ID I want to create a new column which will be the cumulative sum of Variable 1.

I'm using

[ids, ~, rows] = unique(YTD(:, 2));

to get the unique IDs and indices of those ID

Then I would like to calculate the cumulative sum for each ID.

Seems that cumsun doesn't work on an index.

Is there a way to use it or no?

Thanks in advance

[madhan ravi]

You forgot how your expected result should look like and upload your data as .mat file. Which version of matlab are you using, 2018b?

Answer 1

[KSSV]

Say you have this data as a variable seperately....Let ID, var1, var2 be your variables. Do the following:

[C,ia,ib] = unique(ID) ;
N = length(C) ;
iwant = cell(N,1) ;
for i = 1:N
     iwant{i} = cumsum(var1(ib==i)) ;
end

[aggelos]

Thanks a lot.

Answer 2

[Andrei Bobrov]

T = readtable('now1.txt','HeaderLines',1,'ReadRowNames',0);
T2 = varfun(@cumsum,T,'GroupingVariable',2,'InputVariables' ,3:7);
out = [T(:,1:2),T2(:,3:end)];

Here now1.txt - file with your data.

Answer 3

[Kaspar Bachmann]

You could also use a combination of unique() and accumarray():

Data.ID = [1;1;2;3;4;5;6;6;7;7];
Data.Data = rand(length(Data.ID),1);

[C,ia,ic] = unique(Data.ID);
NewData.ID = C;
NewData.CumData = accumarray(ic,Data.Data);

% Output
OriginalTable = struct2table(Data)
OutputTable = struct2table(NewData)

Hope this helps.

Best regards