Grouping and aggregating your data like a pro 🚀
Tags: react, javascript, webdev, tutorial
Published: 2022-05-26 08:12:21

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](https://infinite-table.com/docs/latest/learn/grouping-and-pivoting/grouping-rows) 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:

```ts
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

```ts
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.

```tsx
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`. 

```tsx
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

{% embed https://codesandbox.io/embed/sandpack-project-forked-3evklb?fontsize=14&hidenavigation=1&theme=dark&view=preview %}

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

```ts
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).

```ts
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. 

{% embed https://codesandbox.io/embed/grouping-avg-salary-by-country-forked-ugqdog?fontsize=14&hidenavigation=1&theme=dark&view=preview %}

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.

```ts
  const numberFormatter = new Intl.NumberFormat();

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

{% embed https://codesandbox.io/embed/grouping-avg-salary-by-country-23cej1?fontsize=14&hidenavigation=1&theme=dark&view=preview %}

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](https://infinite-table.com/docs/latest/learn/grouping-and-pivoting/grouping-rows).

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](https://dev.to/roblotter/multiple-sorting-for-your-react-project-44jh) if you haven't already.