Skip to main content
`Ctrl`+`K`

# Restructuring Data

Restructuring and aggregation are two basic methods for extracting statistical information from data. We start with groupwise aggregation and then discuss several forms of restructuring without and with additional aggregation.

    import pandas as pd

## Grouping

Grouping is the first step in the so-called split-apply-combine procedure in data processing. Data is split into groups by some criterion, then some function is applied to each group, finally results get (re-)combinded. Typical functions in the apply step are sum or mean (more general: aggregation) or any type of transform or filtering functions (drop groups containing `nan` items, for instance).

This chapter follows the structure of the Pandas user guide, but leaves out sections on very specific details. Feel free to have a look at those details later on.

### Splitting into Groups and Basic Usage

Grouping is done by calling the `groupby` method of a series or data frame. It takes a column label or a list of column labels as argument and returns a `SeriesGroupBy` or `DataFrameGroupBy` object. The returned object represents a kind of list of groups, each group being a small series or data frame. All rows in a group have identical values in the columns used for grouping.

The `...GroupBy` object offers several methods for working with the determined groups. Iterating over such objects is possible, too.

Grouping by one column and subsequent aggregation yields an index with values from the column used for grouping:

    df = pd.DataFrame({'age': [2, 3, 3, 2, 4, 5, 5, 5],
                       'score': [2.3, 4.5, 3.4, 2.0, 5.4, 7.2, 2.8, 3.9]})
    display(df)

    g = df.groupby('age')

    for name, group in g:
        print('age:', name)
        display(group)

    df_means  = g.mean()
    df_means

| age | score
--- | --- | ---
0 | 2 | 2.3
1 | 3 | 4.5
2 | 3 | 3.4
3 | 2 | 2.0
4 | 4 | 5.4
5 | 5 | 7.2
6 | 5 | 2.8
7 | 5 | 3.9

    age: 2

| age | score
--- | --- | ---
0 | 2 | 2.3
3 | 2 | 2.0

    age: 3

| age | score
--- | --- | ---
1 | 3 | 4.5
2 | 3 | 3.4

    age: 4

| age | score
--- | --- | ---
4 | 4 | 5.4

    age: 5

| age | score
--- | --- | ---
5 | 5 | 7.2
6 | 5 | 2.8
7 | 5 | 3.9

| score
--- | ---
age |
2 | 2.150000
3 | 3.950000
4 | 5.400000
5 | 4.633333

Grouping by two columns and subsequent aggregation yields a multi-level index:

    df = pd.DataFrame({'age': [2, 3, 3, 2, 4, 5, 5, 5],
                       'answer': ['yes', 'no', 'no', 'no', 'no', 'yes', 'yes', 'no'],
                       'score': [2.3, 4.5, 3.4, 2.0, 5.4, 7.2, 2.8, 3.9]})
    display(df)

    g = df.groupby(['age', 'answer'])

    for name, group in g:
        print('age:', name[0])
        print('answer:', name[1])
        display(group)

    df_means  = g.mean()
    display(df_means)

| age | answer | score
--- | --- | --- | ---
0 | 2 | yes | 2.3
1 | 3 | no | 4.5
2 | 3 | no | 3.4
3 | 2 | no | 2.0
4 | 4 | no | 5.4
5 | 5 | yes | 7.2
6 | 5 | yes | 2.8
7 | 5 | no | 3.9

    age: 2
    answer: no

| age | answer | score
--- | --- | --- | ---
3 | 2 | no | 2.0

    age: 2
    answer: yes

| age | answer | score
--- | --- | --- | ---
0 | 2 | yes | 2.3

    age: 3
    answer: no

| age | answer | score
--- | --- | --- | ---
1 | 3 | no | 4.5
2 | 3 | no | 3.4

    age: 4
    answer: no

| age | answer | score
--- | --- | --- | ---
4 | 4 | no | 5.4

    age: 5
    answer: no

| age | answer | score
--- | --- | --- | ---
7 | 5 | no | 3.9

    age: 5
    answer: yes

| age | answer | score
--- | --- | --- | ---
5 | 5 | yes | 7.2
6 | 5 | yes | 2.8

|  | score
--- | --- | ---
age | answer |
2 | no | 2.00
yes | 2.30
3 | no | 3.95
4 | no | 5.40
5 | no | 3.90
yes | 5.00

Grouping by levels of a multi-level index is possible by providing the `level` argument to `groupby`.

With `get_group` we have access to single groups:

    g.get_group((5, 'yes'))

| age | answer | score
--- | --- | --- | ---
5 | 5 | yes | 7.2
6 | 5 | yes | 2.8

`DataFrameGroupBy` objects allow for column indexing:

    df = pd.DataFrame({'age': [2, 3, 3, 2, 4, 5, 5, 5],
                       'answer': ['yes', 'no', 'no', 'no', 'no', 'yes', 'yes', 'no'],
                       'score': [2.3, 4.5, 3.4, 2.0, 5.4, 7.2, 2.8, 3.9]})
    display(df)

    g = df.groupby('age')

    g['answer'].get_group(5)

| age | answer | score
--- | --- | --- | ---
0 | 2 | yes | 2.3
1 | 3 | no | 4.5
2 | 3 | no | 3.4
3 | 2 | no | 2.0
4 | 4 | no | 5.4
5 | 5 | yes | 7.2
6 | 5 | yes | 2.8
7 | 5 | no | 3.9

    5    yes
    6    yes
    7     no
    Name: answer, dtype: object

### Aggregation

To apply a function to each column of each group use `aggregate`. It takes a function or a list of functions as argument. Providing a dictionary of `column: function` pairs allows for column specific functions.

    import numpy as np

    df = pd.DataFrame({'age': [2, 3, 3, 2, 4, 5, 5, 5],
                       'answer': ['yes', 'no', 'no', 'no', 'no', 'yes', 'yes', 'no'],
                       'score': [2.3, 4.5, 3.4, 2.0, 5.4, 7.2, 2.8, 3.9]})
    display(df)

    g = df.groupby('age')

    display(g.aggregate(np.min))
    display(g.aggregate([np.min, np.max]))
    display(g.aggregate({'answer': np.min, 'score': np.mean}))

| age | answer | score
--- | --- | --- | ---
0 | 2 | yes | 2.3
1 | 3 | no | 4.5
2 | 3 | no | 3.4
3 | 2 | no | 2.0
4 | 4 | no | 5.4
5 | 5 | yes | 7.2
6 | 5 | yes | 2.8
7 | 5 | no | 3.9

| answer | score
--- | --- | ---
age |  |
2 | no | 2.0
3 | no | 3.4
4 | no | 5.4
5 | no | 2.8

| answer | score
--- | --- | ---
| amin | amax | amin | amax
age |  |  |  |
2 | no | yes | 2.0 | 2.3
3 | no | no | 3.4 | 4.5
4 | no | no | 5.4 | 5.4
5 | no | yes | 2.8 | 7.2

| answer | score
--- | --- | ---
age |  |
2 | no | 2.150000
3 | no | 3.950000
4 | no | 5.400000
5 | no | 4.633333

With `size` we get group sizes.

    g.size()

    age
    2    2
    3    2
    4    1
    5    3
    dtype: int64

Many aggregation functions are directly accessible from the `...GroupBy` object. Examples are `...GroupBy.sum` and `...GroupBy.mean`. See Computations / descriptive stats for a complete list.

### Transformation

The `transform` method allows to transform rows groupwise resulting in a data frame with same shape as the original one.

    df = pd.DataFrame({'age': [2, 3, 3, 2, 4, 5, 5, 5],
                       'answer': ['yes', 'no', 'no', 'no', 'no', 'yes', 'yes', 'no'],
                       'score': [2.3, 4.5, 3.4, 2.0, 5.4, 7.2, 2.8, 3.9]})
    display(df)

    g = df.groupby('age')

    # substract the groups mean score in each age group
    df['score'] = g['score'].transform(lambda score: score - score.mean())
    df

| age | answer | score
--- | --- | --- | ---
0 | 2 | yes | 2.3
1 | 3 | no | 4.5
2 | 3 | no | 3.4
3 | 2 | no | 2.0
4 | 4 | no | 5.4
5 | 5 | yes | 7.2
6 | 5 | yes | 2.8
7 | 5 | no | 3.9

| age | answer | score
--- | --- | --- | ---
0 | 2 | yes | 0.150000
1 | 3 | no | 0.550000
2 | 3 | no | -0.550000
3 | 2 | no | -0.150000
4 | 4 | no | 0.000000
5 | 5 | yes | 2.566667
6 | 5 | yes | -1.833333
7 | 5 | no | -0.733333

### Filtering

To remove groups use `filter` method. It takes a function as argument and returns a data frame with rows belonging to removed groups removed. The passed function gets the group (series or data frame) and has to return `True` (keep group) or `False` (remove group).

    df = pd.DataFrame({'age': [2, 3, 3, 2, 4, 5, 5, 5],
                       'answer': ['yes', 'no', 'no', 'no', 'no', 'yes', 'yes', 'no'],
                       'score': [2.3, 4.5, 3.4, 2.0, 5.4, 7.2, 2.8, 3.9]})
    display(df)

    g = df.groupby('age')

    g.filter(lambda dfg: dfg['score'].mean() > 4)

| age | answer | score
--- | --- | --- | ---
0 | 2 | yes | 2.3
1 | 3 | no | 4.5
2 | 3 | no | 3.4
3 | 2 | no | 2.0
4 | 4 | no | 5.4
5 | 5 | yes | 7.2
6 | 5 | yes | 2.8
7 | 5 | no | 3.9

| age | answer | score
--- | --- | --- | ---
4 | 4 | no | 5.4
5 | 5 | yes | 7.2
6 | 5 | yes | 2.8
7 | 5 | no | 3.9

## Restructuring Without Aggregation

There are three basic techniques for restructuring data in a data frame:

- `pivot` (interprets two specified columns as row and column index)

- `stack`/`unstack` (move (level of) column index to (level of) row index and vice versa)

- `melt` (create new column from some column labels)

Details and graphical illustrations of these technique may be found in Pandas’ user guide (first three sections).

## Restructuring With Aggregation

Pandas supports pivot tables via `pivot_table` function. Pivot tables are almost the same as pivoting with `pivot` but allow for multiple values per data cell, which then are aggregated to one value.

Details may be found in Pandas’ user guide.

Similar functionality is provided by `crosstab`. See Pandas user guide, too.