How can I aggregate/pivot this non-numeric data to show in a chart?

Question

I'd like to show some data on a line chart. The source data looks like this:

[
        {
          date: 1,
          depth: 1,
          river: 'trent',
        },
        {
          date: 1,
          depth: 7,
          river: 'severn',
        },
        {
          date: 2,
          depth: 5,
          river: 'severn',
        },
        {
          date: 2,
          depth: 3,
          river: 'trent',
        },
]

I'd like the chart to:

• have date as an x axis (1-2)
• have depth as the y axis (1-7)
• have two lines, one for each river ("severn" and "trent")

So, I need to aggregate ("pivot"?) each data point based on the name of the river.

[
      {
        date: 1,
        'trent.depth': 1,
        'severn.depth': 7,
      },
      {
        date: 2,
        'trent.depth': 5,
        'severn.depth': 3,
      },
]

I know some combination of map, filter and reduce to do this, but I'm finding it a little mind-bending.

What's the best way to do this, firstly as a one off, and then in a general, repeatable way that isn't specific to river depth over time, where I might pass in the following as arguments to a function:

• the source data (as above)
• the axis that should be the
• one (or more!) y axis as data series
• one (or more!) axes to break down/aggregate data by

For context, I'm using recharts to display my data if that matters.

So far I've got something like:

const prepareChartData = (sourceData, xAxis, yAxes, aggregateBy) => {

  return entityData.map((row) => {
    // recharts expects x axis to be called 'name'
    const point = {
      name = row[xAxis]
    }

    // fill in remaining data
    yAxes.forEach((axis) => {
      if (!aggregateBy?.length) {
        point[axis] = row[axis]
      } else {
        // for each breakdown field
        aggregateBy.forEach((breakdown) => {
          // all the values this breakdown field holds
          const uniqueVals = new Set(...entityData.map((row) => row[breakdown]))
          // for every single value in the breakdown...
          for (const breakdownVal in uniqueVals) {
                   point[`${breakdown}.${}`] = // ???????
          }
        })
      }
    })
    return point
  })
}

It's a long way from working obviously.

---

For second-order aggregations, input data like:

[
        {
          date: 1,
          depth: 1,
          river: 'trent',
          county: 'south',
        },
        {
          date: 1,
          depth: 7,
          river: 'severn',
          county: 'north',
        },
        {
          date: 2,
          depth: 7,
          river: 'trent',
          county: 'south',
        },
        {
          date: 2,
          depth: 5,
          river: 'severn',
          county: 'north',
        },
        {
          date: 2,
          depth: 3,
          river: 'trent',
          county: 'north',
        },
      ]

Should give an output like:

[
      {
        name: 1,
        'trent.south.depth': 1,
        'severn.north.depth': 7,
      },
      {
        name: 2,
        'trent.north.depth': 3,
        'trent.south.depth': 7,
        'severn.north.depth': 5,
      },
    ]

Answer 1

You can do something like this for your data

const data = [{
    date: 1,
    depth: 1,
    river: 'trent',
  },
  {
    date: 1,
    depth: 7,
    river: 'severn',
  },
  {
    date: 2,
    depth: 5,
    river: 'severn',
  },
  {
    date: 2,
    depth: 3,
    river: 'trent',
  },
]

const result = Object.values(data.reduce((res, {date, depth, river}) => {
   const existing = res[date] || {date}
   res[date] = {...existing, [river+'.depth']: depth}
   return res
}, {}))

console.log(result)

A more general approach could be

const prepareChartData = (sourceData, xAxis, yAxes, aggregateBy) => 
  Object.values(sourceData.reduce((res, item) => {
    const key = item[xAxis]
    const existing = res[key] || {[xAxis]: key}
    return {
     ...res,
     [key]: {
       ...existing,
       ...Object.fromEntries(yAxes.flatMap((valueKey) =>
         aggregateBy.map(prefixKey =>[[item[prefixKey], valueKey].join('.'), item[valueKey] ] )
       ))
     }
    }
  
  }, {}))
  
  
  const data = [{
    date: 1,
    depth: 1,
    river: 'trent',
    length: 12
  },
  {
    date: 1,
    depth: 7,
    river: 'severn',
    length: 13
  },
  {
    date: 2,
    depth: 5,
    river: 'severn',
     length: 47
  },
  {
    date: 2,
    depth: 3,
    river: 'trent',
    length: 63
  },
]
console.log(prepareChartData(data, 'date', ['depth', 'length'], ['river']))