Help: cumulative sum line vs areaY mark?

[fundef1]

Hi,
I've been trying to build up a plot of some (i thought to be) fairly straightforward data:
a ts(timestamp) and amount(float) and a category(varchar), orderered by ts asc in duckdb.
not all timestamps have data, and some timestamps have more than 1.

I've managed to show a cumulative sum of all amounts as a line, but when rendering the same as a areaY it's off:

when setting fill to "Category" all hell breaks loose:

my .json file looks like this:

	{
		"plot": [
			{
				"mark": "areaY",
				"data":  { "from": "parsedandcleaned"},
				"x": "ts",
				"y": {
					"sum": "Amount",
					"rows": [null, 0]
				},
				"fill": "Category",
				"curve": "step-after"
			},
			{
				"mark": "line",
				"data":  { "from": "parsedandcleaned"},
				"x": "ts",
				"y": {
					"sum": "Amount",
					"rows": [null, 0]
				},
				"stroke": "#919191",
				"curve": "step-after"
			}
		],
		"height": 300,
		"xLabel": "timestamp ts"
	}

probably I'm missing something with the cumulative sum, but I can't figure it out.
any help/pointers much appreciated!

[jheer]

Hmm, I can't tell for sure without seeing underlying data. I might start by looking at the logs for the generated queries and making sure nothing looks off and/or looking at the returned query results (inspectable via plot.marks[i].data) to see if the correct data is being queried. If so, then we can see what is happening with subsequent plotting.

(For more detailed help in the future, please include a small dataset that replicates the issue!)

[fundef1]

Hi, I couldn't figure out how to get plot.marks[i].data as you suggested. (The plot resulting from parseSpec is HTML?)
anyway, I created a json with the data embedded - thanks for looking into this btw.

{
	"meta": {
		"title": "Table + plot",	
		"description": "Stacked area plot... tobe\n"
  },
  "data": { 
	"parsedandcleaned": 
			[
				{"Amount":1843,"Category":"A","ts":"2013-09-22 22:00:00+00"},
				{"Amount":1937,"Category":"A","ts":"2013-09-22 22:00:00+00"},
				{"Amount":515,"Category":"B","ts":"2013-09-22 22:00:00+00"},
				{"Amount":2300,"Category":"D","ts":"2013-09-22 22:00:00+00"},
				{"Amount":798,"Category":"C","ts":"2013-09-22 22:00:00+00"},
				{"Amount":718,"Category":"C","ts":"2013-09-22 22:00:00+00"},
				{"Amount":5160,"Category":"C","ts":"2013-09-22 22:00:00+00"},
				{"Amount":1620,"Category":"A","ts":"2013-09-22 22:00:00+00"},
				{"Amount":2053,"Category":"A","ts":"2013-09-24 22:00:00+00"},
				{"Amount":2926,"Category":"A","ts":"2013-09-29 22:00:00+00"},
				{"Amount":165,"Category":"B","ts":"2013-09-30 22:00:00+00"},
				{"Amount":5034,"Category":"A","ts":"2013-10-01 22:00:00+00"},
				{"Amount":690,"Category":"C","ts":"2013-10-02 22:00:00+00"},
				{"Amount":4216,"Category":"A","ts":"2013-10-07 22:00:00+00"},
				{"Amount":6052,"Category":"A","ts":"2013-10-13 22:00:00+00"},
				{"Amount":1094,"Category":"A","ts":"2013-10-13 22:00:00+00"},
				{"Amount":2597,"Category":"C","ts":"2013-10-13 22:00:00+00"},
				{"Amount":6217,"Category":"A","ts":"2013-10-16 22:00:00+00"},
				{"Amount":635,"Category":"A","ts":"2013-10-17 22:00:00+00"},
				{"Amount":3083,"Category":"A","ts":"2013-10-21 22:00:00+00"}
			]		
  },
  "vconcat" : [
	{
		"plot": [
			{
				"mark": "areaY",
				"data":  { "from": "parsedandcleaned"},
				"x": "ts",
				"y": {
					"sum": "Amount",
					"rows": [null, 0]
				},
				"fill": "#c0ffee",
				"curve": "step-after"
			},
			{
				"mark": "line",
				"data":  { "from": "parsedandcleaned"},
				"x": "ts",
				"y": {
					"sum": "Amount",
					"rows": [null, 0]
				},
				"stroke": "#919191",
				"curve": "step-after"
			}
		],
		"height": 300,
		"xLabel": "timestamp ts"

	},{
  		"input": "table",
  		"from": "parsedandcleaned",
  		"height": 500  }]
}

ps: I multiplied the values from the original graph by 100 because of the Amounts being detected as DECIMAL(...) so I made all values whole numbers, chart still looks the same but with bigger numbers (and ts is rendered differently too).

[jheer]

The generated HTML element for a plot includes a value property that references the backing JS object instance. So for a vgplot plot(...) you can use plot.value.marks[0].data, for example.

[jheer]

It appears the problem is due to repeated timestamp values. For a line mark this does not create a visible problem as the line segments are connected vertically. Area marks (which perform stacking by default) lead to displacements when given duplicate values.

One solution is to pre-aggregate the data to suppress duplicates. For example, in a Mosaic YAML spec it might look something like this, when ignoring categories:

data:
  init: |
    (SELECT 1843::DOUBLE AS "Amount", 'A' AS "Category", '2013-09-22 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 1937::DOUBLE AS "Amount", 'A' AS "Category", '2013-09-22 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 515::DOUBLE AS "Amount", 'B' AS "Category", '2013-09-22 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 2300::DOUBLE AS "Amount", 'D' AS "Category", '2013-09-22 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 798::DOUBLE AS "Amount", 'C' AS "Category", '2013-09-22 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 718::DOUBLE AS "Amount", 'C' AS "Category", '2013-09-22 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 5160::DOUBLE AS "Amount", 'C' AS "Category", '2013-09-22 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 1620::DOUBLE AS "Amount", 'A' AS "Category", '2013-09-22 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 2053::DOUBLE AS "Amount", 'A' AS "Category", '2013-09-24 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 2926::DOUBLE AS "Amount", 'A' AS "Category", '2013-09-29 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 165::DOUBLE AS "Amount", 'B' AS "Category", '2013-09-30 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 5034::DOUBLE AS "Amount", 'A' AS "Category", '2013-10-01 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 690::DOUBLE AS "Amount", 'C' AS "Category", '2013-10-02 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 4216::DOUBLE AS "Amount", 'A' AS "Category", '2013-10-07 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 6052::DOUBLE AS "Amount", 'A' AS "Category", '2013-10-13 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 1094::DOUBLE AS "Amount", 'A' AS "Category", '2013-10-13 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 2597::DOUBLE AS "Amount", 'C' AS "Category", '2013-10-13 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 6217::DOUBLE AS "Amount", 'A' AS "Category", '2013-10-16 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 635::DOUBLE AS "Amount", 'A' AS "Category", '2013-10-17 22:00:00+00'::TIMESTAMP AS "ts") UNION ALL
    (SELECT 3083::DOUBLE AS "Amount", 'A' AS "Category", '2013-10-21 22:00:00+00'::TIMESTAMP AS "ts")
  mydata: SELECT SUM(Amount) AS Amount, ts FROM init GROUP BY ts

In addition, I recommend including more detail in your cumulative sum. To avoid unexpected errors, specify the order criteria:

y: { sum: Amount, order: ts, rows: [null, 0] }

And if you want cumulative sums on a per-category basis, include the category column as partition criteria:

y: { sum: Amount, partition: Category, order: ts, rows: [null, 0] }

[fundef1]

Hi, thanks so much for that. Yes, it makes sense that two datapoints for the same ts and category would lead to problems.

But I'm not out of the woords yet, I've given up for now trying to do this in the json spec so I created a view over the original data in duckdb that merged duplicates and created a cumulative sum per category and use that. FWIW I'd much prefer to do this in the json, but one problem at a time.

This dataset has no duplicate ts AND category, but obviously still multipe values for the same ts but different category.

{
	"meta": {
		"title": "Table + plot",	
		"description": "Stacked area plot \n"
  },
  "data": { 
	"parsedandcleaned": 
	[
		{"CategoryRunningSum":6676,"Category":"B","ts":"2013-09-22 22:00:00+00"},
		{"CategoryRunningSum":5400,"Category":"A","ts":"2013-09-22 22:00:00+00"},
		{"CategoryRunningSum":515,"Category":"D","ts":"2013-09-22 22:00:00+00"},
		{"CategoryRunningSum":2300,"Category":"C","ts":"2013-09-22 22:00:00+00"},
		{"CategoryRunningSum":7453,"Category":"A","ts":"2013-09-24 22:00:00+00"},
		{"CategoryRunningSum":10379,"Category":"A","ts":"2013-09-29 22:00:00+00"},
		{"CategoryRunningSum":680,"Category":"D","ts":"2013-09-30 22:00:00+00"},
		{"CategoryRunningSum":15413,"Category":"A","ts":"2013-10-01 22:00:00+00"},
		{"CategoryRunningSum":690,"Category":"E","ts":"2013-10-02 22:00:00+00"},
		{"CategoryRunningSum":19629,"Category":"A","ts":"2013-10-07 22:00:00+00"}
	]
  },
  "vconcat" : [
	{
		"plot": [
			{
				"mark": "areaY",
				"data":  { "from": "parsedandcleaned"},
				"x": "ts",
				"y": "CategoryRunningSum",
				"fill": "Category",
				"curve": "step-after"
			}
		],
		"height": 300,
		"xLabel": "timestamp ts"
	},{
  		"input": "table",
  		"from": "parsedandcleaned",
  		"height": 500  }]
}

Thanks again.

[jheer]

I can't look at this more closely until later, but I do notice that some categories only appear once. An area mark will not work particularly well in those cases. For debugging, you might also remove the curve option, as a different curve rendering might better reveal what is happening with the areas.

[fundef1]

Okay, thanks for all the help so far - I've gotten more than I could ask for ;-)

AFAICT there might be two issues at play:

• areaY doesn't like sparse data - when adding a cumulative sum for each category on every ts the stacking behaves better
• but not perfect, sometimes it still orders the stacks differently between data points.

for now, I'll see if I can work around it by manually stacking area marks by computing y1 in SQL and adding them as seperate area plots.

[jheer]

Yes, the areaY mark is sensitive to the data sort order. One option is to query your data with a desired order. You should be able to indicate this via a mark order option ("order": "k"). Note that the areaY mark will already sort along the x-axis for you.

Sorting won't help with the sparse data issue, though. One option is to impute missing values for each timestamp you want to sample, though I don't think Observable Plot provides any support for this at the plotting level. You could do this in DuckDB using CTEs and appropriate joins, but it's a bit involved...

[fundef1]

Alright, Imputing the values was indeed 'involved', involving some pivot-unpivotting but we got there in the end.
Only issue left is that the M4 optimization creates artefacts - with more than 2320 samples it seems - but with optimization switched off it renders fine.

Many thanks for all the help.