3
$\begingroup$

I have a column of integers "Points" and I want to add another column that is a running total of that column. Here is my data:

<|"Date" -> "2011-10-09", "OT" -> "", "GoalDiff" -> -4, "Points" -> 0|>, 
<|"Date" -> "2011-10-13", "OT" -> "", "GoalDiff" -> -1, "Points" -> 0|>,
<|"Date" -> "2011-10-15", "OT" -> "", "GoalDiff" -> -3, "Points" -> 0|>, 
<|"Date" -> "2011-10-17", "OT" -> "", "GoalDiff" -> 1, "Points" -> 2|>,
<|"Date" -> "2011-10-19", "OT" -> "SO", "GoalDiff" -> -1, "Points" -> 0|>,
<|"Date" -> "2011-10-20", "OT" -> "", "GoalDiff" -> -3, "Points" -> 0|>,
<|"Date" -> "2011-10-22", "OT" -> "", "GoalDiff" -> 2,  "Points" -> 2|>,
<|"Date" -> "2011-10-24", "OT" -> "", "GoalDiff" -> -1, "Points" -> 0|>,
<|"Date" -> "2011-10-27", "OT" -> "", "GoalDiff" -> 1, "Points" -> 2|>,
<|"Date" -> "2011-10-29", "OT" -> "", "GoalDiff" -> -1, "Points" -> 0|>,
<|"Date" -> "2011-10-31", "OT" -> "SO", "GoalDiff" -> 1, "Points" -> 2|>,
<|"Date" -> "2011-11-03", "OT" -> "", "GoalDiff" -> 3, "Points" -> 2|>,

So my running total column would be

0
0
0
2
2
2
4
4
6
6
8
10

Think of a check book where one column is the credit/debit and the next column is the balance (I know, we don't balance check books any more but a spreadsheet will do the same thing).

The Total won't work because it sums the whole column whereas I want the sum only up to the current row.

It's simple to do this in Excel, but is there some magic to do this in Mathematica?

$\endgroup$

4 Answers 4

4
$\begingroup$

Here is one way

ds = Dataset[{<|"Date" -> "2011-10-09", "OT" -> "", "GoalDiff" -> -4, 
    "Points" -> 0|>, <|"Date" -> "2011-10-13", "OT" -> "", 
    "GoalDiff" -> -1, "Points" -> 0|>, <|"Date" -> "2011-10-15", 
    "OT" -> "", "GoalDiff" -> -3, "Points" -> 0|>, <|
    "Date" -> "2011-10-17", "OT" -> "", "GoalDiff" -> 1, 
    "Points" -> 2|>, <|"Date" -> "2011-10-19", "OT" -> "SO", 
    "GoalDiff" -> -1, "Points" -> 0|>, <|"Date" -> "2011-10-20", 
    "OT" -> "", "GoalDiff" -> -3, "Points" -> 0|>, <|
    "Date" -> "2011-10-22", "OT" -> "", "GoalDiff" -> 2, 
    "Points" -> 2|>, <|"Date" -> "2011-10-24", "OT" -> "", 
    "GoalDiff" -> -1, "Points" -> 0|>, <|"Date" -> "2011-10-27", 
    "OT" -> "", "GoalDiff" -> 1, "Points" -> 2|>, <|
    "Date" -> "2011-10-29", "OT" -> "", "GoalDiff" -> -1, 
    "Points" -> 0|>, <|"Date" -> "2011-10-31", "OT" -> "SO", 
    "GoalDiff" -> 1, "Points" -> 2|>, <|"Date" -> "2011-11-03", 
    "OT" -> "", "GoalDiff" -> 3, "Points" -> 2|>}]

Module[{acc = 0}, ds[All, <|#, "CumulativePoints" -> (acc += #Points)|> &]]

enter image description here

$\endgroup$
3
  • $\begingroup$ I like your solution, +1. Please forgive me, but I have taken the liberty to write a response which generalizes from this starting point (it was too large to fit into a comment). $\endgroup$
    – WReach
    Oct 27, 2021 at 5:45
  • $\begingroup$ @WReach I have learned so much about the internals of Dataset from your very detailed answers. Thank you so much for that. $\endgroup$ Oct 27, 2021 at 16:05
  • $\begingroup$ Thanks for the kind words. $\endgroup$
    – WReach
    Oct 27, 2021 at 18:08
3
$\begingroup$

I, too, would likely use the solution given by Rohit Namjoshi.

This can also be written in a slightly modified yet equivalent form:

dataset[All, Module[{t = 0}, <|#, "Running" -> (t += #Points)|> &]]

This alternate form allows us to define a generalized operator...

running[init_, accumulate_, update_] :=
  Module[{r = init}, (r = accumulate[#, r]; update[#, r])&]

and then use it:

dataset[All, running[0, #Points + #2 &, <|#, "Running" -> #2|> &]]

Given...

dataset = {
  <|"Date" -> "2011-10-09", "OT" -> "", "GoalDiff" -> -4, "Points" -> 0|>, 
  <|"Date" -> "2011-10-13", "OT" -> "", "GoalDiff" -> -1, "Points" -> 0|>,
  <|"Date" -> "2011-10-15", "OT" -> "", "GoalDiff" -> -3, "Points" -> 0|>, 
  <|"Date" -> "2011-10-17", "OT" -> "", "GoalDiff" -> 1, "Points" -> 2|>,
  <|"Date" -> "2011-10-19", "OT" -> "SO", "GoalDiff" -> -1, "Points" -> 0|>,
  <|"Date" -> "2011-10-20", "OT" -> "", "GoalDiff" -> -3, "Points" -> 0|>,
  <|"Date" -> "2011-10-22", "OT" -> "", "GoalDiff" -> 2,  "Points" -> 2|>,
  <|"Date" -> "2011-10-24", "OT" -> "", "GoalDiff" -> -1, "Points" -> 0|>,
  <|"Date" -> "2011-10-27", "OT" -> "", "GoalDiff" -> 1, "Points" -> 2|>,
  <|"Date" -> "2011-10-29", "OT" -> "", "GoalDiff" -> -1, "Points" -> 0|>,
  <|"Date" -> "2011-10-31", "OT" -> "SO", "GoalDiff" -> 1, "Points" -> 2|>,
  <|"Date" -> "2011-11-03", "OT" -> "", "GoalDiff" -> 3, "Points" -> 2|>
} // Dataset

... both of these variations return:

dataset snapshot

Some other possibilities...

dataset[Transpose /* (<|#, "Running" -> Accumulate[#Points]|> &) /* Transpose]
dataset[{All, Query[Accumulate, "Points"]} /* MapThread[<|#, "Running" -> #2|> &]]
dataset[{All, Query[Accumulate, <|"Running" -> #Points|> &]} /* MapThread[Join]]
$\endgroup$
2
  • $\begingroup$ This is one of those cases where it would be nice if SubsetMap worked on associations. $\endgroup$
    – WReach
    Oct 27, 2021 at 5:46
  • $\begingroup$ +1 Very nice generalization and alternatives. $\endgroup$ Oct 27, 2021 at 16:13
3
$\begingroup$
SubsetMap[Accumulate, {All, -1}]  @ ds[All, <|#, "CumulativePoints" -> #Points|> &]

enter image description here

Some variations that give the same result:

SubsetMap[Accumulate, {All, -1}]  @* Map[<|#, "CumulativePoints" -> #Points|> &] @ ds

ds // Map[<|#, "CumulativePoints" -> #Points|> &] // SubsetMap[Accumulate, {All, -1}] 
$\endgroup$
0
$\begingroup$

Use Accumulate:

output = Accumulate[input]
$\endgroup$
1
  • $\begingroup$ In a Dataset it would be like this: ds2 = ds[All, <|#, "TotalPoints" -> Accumulate["Points"]|> &]. But that fails with Nonatomic expression expected at position 1 in Accumulate[Points]. $\endgroup$ Oct 27, 2021 at 2:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.