Add a new column to CSV file with equations

I have a large data set

17568 rows x 12 columns in an xslx and csv file (1st row header, the rest numbers, dates, year etc)

I need to create a new column that is the cummulative sum of a column in the data set. If possible that also makes Na values 0.

example:

Year       A         B
2001      3         2
2001      4         1

I need to make:

Year       A         B     CummA    CummB
2001      3         2          3              2
2001      4         1          7              3

Answer 1

[dpb]

[tTable array2table(cumsum(tTable{:,{'A','B'}}),'VariableNames',{'cumA','cumB'})];

where tTable is your table variable.

One can get fancy and use the table properties property 'VariableNames' to build the name fields dynamically as well.

As for the NaN, there's an optional flag with cumsum to ignore NaN in the calculations if that's the result you wish; it effectively inserts a zero in place of the value by ignoring it in computing the sums--it does NOT replace the NaN in the data itself.

You've got to decide which way you want to go...it's trivial exercise to just replace NaN values with zeros first in the original data, but then, of course, they're gone and if there are zero values that are inherent in the data to begin with, then you've lost the distinction.

[Stef1998]

thank you!

[Peter Perkins]

It's also possible to avoid horzcat and array2table by assigning directly into tTable, as shown here:

https://www.mathworks.com/matlabcentral/answers/1592309-adding-a-new-column-of-differences-to-a-table#answer_838764

[dpb]

Good point, Peter...I just took the expedient route. The concatenation is probably measurably slower.