Creating "Formula" Columns

Community Help
1

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
2

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
3

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.

4

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):

5

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.

6

Hi both.

I am pretty new to Observable and JavaScript. I am trying to do something similar to this problem, and a search brought me here. I’m hoping you could help.

I have a data table and I want to add four columns that do calculations with two sliders’ values as input based on what is in the other columns (like formulas in Excel, but with dynamic inputs). I.e. I have true_positive, false_positive, false_negative columns and I want to calculate the costs associated with these as follows:

fp_cost = false_positive * replace(slider value)

fn_cost = false_negative * repair(slider value)

tp_cost = true_positive * replace(slider value)

total_savings = (true_positive * repair(slider value)) - tp_cost - fp_cost

Is that achievable using the index, as above? Should I use a different approach? Any help or suggestions would be welcomed.

Here is a notebook with the data and graphs I’m using: Test / Nico's Workspace | Observable