Grouping and aggregating your data like a pro 🚀

In most modern apps, you need to be able to visualise your data in multiple different ways. One useful approach to seeing data is by grouping it. What is the average salary by country? How many sales did we have this year? What was the biggest sale we had last quarter? All are common questions that can be easily answered by grouping and aggregating data.

Infinite Table can help you group your data easily, both locally and with a remote data-source.

Let's suppose your app is a developer marketplace and you have a list of developers defined by this TypeScript type:

type Developer = {
  id: number;
  firstName: string;
  lastName: string;
  country: string;
  city: string;
  currency: string;
  preferredLanguage: string;
  stack: string;
  canDesign: 'yes' | 'no';
  hobby: string;
  salary: number;
  age: number;
};

let's define a function to load the data source from the server

const dataSource = () => {
  return fetch(
   'https://infinite-table.com/.netlify/functions/' + 
   'json-server/developers1k-sql'
  )
    .then((r) => r.json())
    .then((data: Developer[]) => data);
};

Ok, so let's define our DataSource component to use the data function above - we'll use the id property as the primary key.

import { DataSource } from '@infinite-table/infinite-react'

<DataSource<Developer>
  data={dataSource}
  primaryKey="id"
/>

But wait, we need to actually render the table, inside the DataSource.

const columns: InfiniteTablePropColumns<Developer> = {
  firstName: {
    field: "firstName"
  },
  preferredLanguage: {
    field: 'preferredLanguage'
  },
  salary: {
    field: 'salary'
  },
  country: {
    field: 'country'
  }
};

<DataSource<Developer>
  data={dataSource}
  primaryKey="id"
>
  <InfiniteTable<Developer> columns={columns} />
</DataSource>

Let's visualise what we have to this point

Now let's add the interesting part - the grouping. Grouping is a prop of the DataSource

const groupBy = [{ field: "country" }];

<DataSource groupBy={groupBy} ... />

This would indeed group all the data, but in addition to that, we want to have some aggregation - let's find out the average salary by country. For this, we'll use aggregation reducers, defined as an object with multiple keys, on the DataSource. If you're familiar with Array.reduce, those should sound familiar - the job of those aggregation reducers is to ...well, reduce 😉 ... the whole datasource to a single value (also the groups inside the datasource to single values).

function sum(a: number, b: number) {
  return a + b;
}
const aggregations = {
  avgSalary: {
    field: "salary",
    initialValue: 0,
    reducer: sum,
    done: (total, arr) => Math.floor(
      arr.length ? total / arr.length : 0
    )
  }
} as DataSourcePropAggregationReducers<Developer>;

In the above snippet, we're saying we have an aggregation, with the id of avgSalary, that's computed based on the salary field. The initial value of the aggregation is 0, and the reducer function is the sum function. We also have a last step, described by the done function, which does some post-processing, computing the average.

Also, because the country column is grouped by, we'll remove it from the list of columns that we want to show.

We're almost there, so hang on - we'll only add 1 more thing - let's try to format the salary column a bit better. We can use the valueFormatter property for the salary column, and we want to format the numbers by using Intl.NumberFormat. Also, for group rows, we want the value to have Avg: ... prepended, while non-group rows will remain untouched.

  const numberFormatter = new Intl.NumberFormat();

  // the salary column
  {
    field: "salary",
    valueFormatter: ({ value, rowInfo }) => {
      value = numberFormatter.format(value);
      return rowInfo.isGroupRow ? `Avg: ${value}` : value;
    }
  }

We finally got to the end, ... that was quite a ride, so thank you for following along. For more in-depth documentation, see Infinite Table grouping page.

Follow us along our infinite journey (pun intended) as in the next posts we'll be exploring more ways to group data, strategies for grouping, remote integration, pivoting and more, to make visualising data easier than never.

Oh, and by the way, check out Multiple sorting for your React project if you haven't already.