How to use cumsum for data in a table which is not NaN?
4 views (last 30 days)
Show older comments
Kasih Ditaningtyas Sari Pratiwi
on 1 Dec 2017
Commented: Jan
on 6 Oct 2018
Hi. I have a question about how to use cumsum function for data in a table which is not NaN.
'02-Nov-2017 10:50:49' NaN
'02-Nov-2017 10:50:53' NaN
'03-Nov-2017 00:00:00' NaN
'03-Nov-2017 08:00:00' NaN
'04-Nov-2017 00:00:00' NaN
'04-Nov-2017 08:00:00' NaN
'05-Nov-2017 00:00:00' NaN
'05-Nov-2017 08:00:00' NaN
'05-Nov-2017 14:00:41' 0.100000000000000
'05-Nov-2017 14:04:08' 0.100000000000000
'05-Nov-2017 14:06:40' 0.100000000000000
'05-Nov-2017 14:10:00' NaN
'05-Nov-2017 14:12:58' NaN
'05-Nov-2017 14:13:24' NaN
'05-Nov-2017 14:14:00' NaN
'05-Nov-2017 14:15:58' 0.100000000000000
'05-Nov-2017 14:16:24' 0.100000000000000
For example I have this above data. I want the cumsum function only calculate the value which is not NaN. The result should be like this:
'02-Nov-2017 10:50:49' NaN
'02-Nov-2017 10:50:53' NaN
'03-Nov-2017 00:00:00' NaN
'03-Nov-2017 08:00:00' NaN
'04-Nov-2017 00:00:00' NaN
'04-Nov-2017 08:00:00' NaN
'05-Nov-2017 00:00:00' NaN
'05-Nov-2017 08:00:00' NaN
'05-Nov-2017 14:00:41' 0.100000000000000
'05-Nov-2017 14:04:08' 0.200000000000000
'05-Nov-2017 14:06:40' 0.300000000000000
'05-Nov-2017 14:10:00' NaN
'05-Nov-2017 14:12:58' NaN
'05-Nov-2017 14:13:24' NaN
'05-Nov-2017 14:14:00' NaN
'05-Nov-2017 14:15:58' 0.100000000000000
'05-Nov-2017 14:16:24' 0.200000000000000
I try to find the example for cumsum, but I found nothing. Could you please hep me? Thank you very much for your help.
0 Comments
Accepted Answer
Jan
on 2 Dec 2017
Edited: Jan
on 3 Dec 2017
idx = ~isnan(Val);
Val2 = Val;
Val2(idx) = cumsum(Val(idx));
Or:
Val2 = cumsum(Val, 'omitnan');
Val2(isnan(Val)) = NaN;
[EDITED] And with a reset of the sum after each NaN block:
Data = [NaN, NaN, 0.5, 0.1, 0.4, NaN, 0.1, 0.2];
X = Data;
idx = isnan(X);
new = strfind(idx(:).', [true, false]); % (:).' because STRFIND needs a row
Y = cumsum(X, 'omitnan');
X(new) = [0; -diff(Y(new))]; % [EDITED 2] ROUNDING PROBLEMS!!!
R = cumsum(X, 'omitnan');
R(idx) = NaN;
[EDITED 2] I've fixed the code, but it suffers from rounding problems. After a certain number of elements you get e.g. 0.0999999999997975 instead of 0.1 . So let's try a simply loop:
function R = cumsumResetNaN(R)
c = 0;
for k = 1:numel(R)
if isnan(R(k))
c = 0;
else
c = c + R(k);
R(k) = c;
end
end
end
This needs 0.00068 sec for input data with 64700 elements, which is faster than the vectorized method above (which has the severe rounding problem).
[EDITED 3] And for completeness a C-Mex function:
#include "mex.h"
void mexFunction(int nlhs, mxArray *plhs[], int nrhs, const mxArray *prhs[])
{
// Jan Simon, 2017, License: CC BY-SA 3.0
// Create cumulative sum of a double vector, which is reset to 0
// at every NaN.
double *x, *xf, c;
if (!mxIsDouble(prhs[0]) || mxIsSparse(prhs[0]) || mxIsComplex(prhs[0]) ||
(mxGetM(prhs[0]) != 1 && mxGetM(prhs[0]) != 1)) {
mexErrMsgIdAndTxt("JSimon:cumsumRestartNaN:BadInput",
"Input must be a real full double vector.");
}
plhs[0] = mxDuplicateArray(prhs[0]);
x = mxGetPr(plhs[0]);
xf = x + mxGetNumberOfElements(plhs[0]);
c = 0;
while (x < xf) {
if (*x == *x) { // NaN==NaN is FALSE by defintion
c += *x;
*x++ = c;
} else {
c = 0;
x++;
}
}
}
This needs 0.26 sec compared to 0.91 sec for the M-version [EDITED 2] (input data: [64700] elements, 1000 iterations, MSCV2012, Win7/64, Matlab 2016b)
6 Comments
More Answers (2)
Akira Agata
on 2 Dec 2017
How about keeping position of NaN, applying cumsum with 'omitnan' option and finally putting NaN for the position, like:
Val = [NaN NaN 0.1 0.1 0.1 NaN]';
idx = isnan(Val);
Val2 = cumsum(Val,'omitnan');
Val2(idx) = NaN;
3 Comments
Akira Agata
on 3 Dec 2017
If you have Image Processing Toolbox, you can simply do that by using bwlabel function. Here is my second try!
Val = [NaN NaN 0.1 0.1 0.1 NaN NaN 0.1 0.1 0.1 NaN]';
idx = isnan(Val);
group = bwlabel(~idx);
tmp = splitapply(@(x) {cumsum(x)},Val(~idx), group(~idx));
Val2 = nan(size(Val));
for kk = 1:max(group)
Val2(group == kk) = tmp{kk};
end
Pai-Feng Teng
on 5 Oct 2018
How to find the sum of every single number in a table? I searched every board I can find and all they had is the sum of rows or columns.
1 Comment
Jan
on 6 Oct 2018
Please do not attach a new question in the section for answers of another question. Such thread-hijacking produces confusions, because it is not clear, to which question an answer belongs. Create a new question in your own thread and delete this "answer". Thanks.
By the way: "The sum of each single number" is not clear and should be elaborated.
See Also
Categories
Find more on Software Development Tools 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!