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
2	yes	2.30
3	no	3.95
4	no	5.40
5	no	3.90
5	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

  yes
  yes
   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.

Restructuring Data

Contents