Summarize CSV: Sum Values by Unique Identifier
If I have a .csv file that looks like this:
ID,Number 0001,47 0001,88 0001,33 0002,876 0002,23 0002,66
Can I use CSV: Summarize rows with formulas to sum the Number column for each unique value in the ID column? If yes, what's the formula to use? To be sure, I'm looking for a result like this:
ID,Number 0001,168 0002,965
This should be easy to do with a batch/the below sequence, but not as efficient:
Tables: Create table for each unique value
Tables: Start Workflow for each row
Tables: Sum data in a column
Tables: Update a row
Best Answers
-
@Thomas_937381 I've attached an example Catalytic process that should produce the output you want. The formula to use in the CSV: Summarize formula is:
var id = columns["ID"]; var sums = results["Number"] || {}; var sum = sums[id] || 0; sum += parseInt(columns["Number"]); sums[id] = sum; results["Number"] = sums;
the example takes the JSON output of CSV: Summarize and converts it to a CSV file.
Hope this helps!
7 -
@Thomas_937381 there is! What you want would look something like:
var status = columns["Status"]; var counts = results["Status"] || {}; var count = counts[status] || 0; count += 1; counts[status] = count; results["Status"] = counts;
The output from the summarize action would look like:
{ "Pending": 3, "Approved": 1, "Processing": 2 }
and then you'd just need to transform it to the CSV.
7
Answers
-
Thanks, @Dylan_478989! This is an awesome pattern that many will be able to benefit from.
For anyone looking to reuse the pattern: just keep in mind the size of the dataset you're looking to summarize. The JSON and comma-separated values text will need to be within field limits.
1 -
@Dylan_478989 Is there a simple way to modify this to count the rows against each one of those unique values? For example:
ID,Status 101,Pending 102,Pending 103,Approved 104,Pending 105,Processed 106,Processed
I'd love to have something like this, but suspect the parseInt goes away. What should be used to simply treat the value as 1?
Status,Count Pending,3 Approved,1 Processed,2
0