🏠 back to Observable

Creating "Formula" Columns

hi,
with a data Array, how do i add “calculated”/“Formula” columns as i would in Excel?

in https://observablehq.com/@lukasgrebe/a4-avant i’ve inlined a TSV of Expenses, Date and Odometer of my car. I’d like to, for example, calculate the difference in “odometer” between two of the same types of expenses. for example the “fuel” expense to calcualte an mpg column.

  • Lukas
1 Like

The d3.tsvParse command creates an array of objects and you can act on that array using standard Javascript techniques. To compute mpg for each entry, you might do something like

a4.map(d => parseFloat(d.odometer)/parseFloat(d.price))

If you want to add this as a column to a4 for ease of use with Vega Lite, you could do something like:

a4 = {
  let a4 = d3.tsvParse(...),
  a4.forEach(function(d) {
    d.mpg = parseFloat(d.odometer)/parseFloat(d.price))
  }
  return a4
}

I forked your code to illustrate. It doesn’t look particularly nice so I’m not sure I’ve done the desired calculation but I think it at least illustrates how to do these types of manipulations.

1 Like

Thanks for your reply! I had looked into the Map function, however for a correct calculation, I’d need to reference previous and next array elements.

So if i is the current array index, of mpg[i]=(odometer[i+1]-odometer[i])/amount[i]
Wich i think isn’t possible with a map function.

And this gets more complicated since index i+1 Might not be the „next“ entity. I suppose this could be solved by sorting the array.

I’d probably use d3.pairs for this, like so:

d3.pairs(
  a4.filter(d => d.type === "fuel")
    .sort((a, b) => d3.ascending(a.date, b.date)),
  (a, b) => ({
    ...b,
    odometer_delta: b.odometer - a.odometer
  })
)

Here’s a suggestion (also switching to a file attachment):

thank you!
incidentally i had stumbled upon pairs last night but not published my changes in the meantime. It seems to cover exactly this use case.
Adding a filter for fuel is an elegant way to handling the different types of expenses. I had been messing with group and mapping the grouped types with pairs.