Merging and Aggregating Data

Some common NumPy aggregation functions include

• np.mean(), np.median() for the mean and median
• np.max(), np.min() for maximum and minimum values
• np.sum() to sum all the values in an array

For example, let’s use NumPy to calculate the mean test score in scores:

Pandas provides built-in methods to aggregate DataFrame columns.

Common built-in summary methods include:

• .mean() returns the mean
• .median() returns the median
• .std() returns the standard deviation
• .max() and .min() return the maximum and minimum values respectively
• .nunique() returns the count of unique values
• .count() returns the count of non-null values
• .sum() returns the sum

The pandas .groupby() method splits a DataFrame into groups corresponding to the unique values in a column.

Here is the df referred to in the code snippet:

Calling an aggregation method in Pandas after performing a groupby will apply that aggregation to the individual groups in the groupby.

For example, the code snippet uses .sum() to sum the total number of home_score goals by each home_team in df:

Pandas’ .agg() method is a flexible way to apply aggregation functions to groups of a groupby.

The input to .agg() is a dictionary where

• the keys specify which columns to aggregate
• the values are the aggregation functions to apply

For example, the code snippet applies sum to compute the total number of home_score goals by each home_team in df:

The pandas method pd.pivot_table() transforms a DataFrame into wide format that is more human-readable using the input parameters:

• index is used to label the rows of the table
• columns is used to label the columns of the table
• values is used to fill the table
• aggfunc is the aggregation function applied to values

For example, say df contains sales data for two products in two regions:

The code snippet pivots df to calculate the mean sales of each product in each region:

An inner merge combines two DataFrames by identifying matching values. For example, here are two DataFrames

In an inner merge on the Category columns, only the matching category B would be retained, together with the corresponding data from both DataFrames:

The pandas code is

pd.merge(left = X,
         right = Y,
         left_on = 'Category',
         right_on = 'Category',
         how = 'inner')

In Pandas, a left or right merge combines two DataFrames, preserving all rows on either the left or the right DataFrame respectively.

The type is specified using the how keyword:

pd.merge(left = X,
         right = Y,
         left_on = 'Category',
         right_on = 'Category',
         how = 'right')

For example, here are two DataFrames:

Performing a right merge, we maintain all rows of the right DataFrame:

and then add on any existing data from the left DataFrame:

In Pandas, an outer merge combines two (or more) DataFrames, maintaining all rows from both DataFrames. To perform an outer merge, specify outer as the how argument:

pd.merge(left = X,
         right = Y,
         left_on = 'Category',
         right_on = 'Category',
         how = 'outer')

For example, here are two DataFrames

To outer merge them, we match values to the different categories, filling in NaNs where no value exists: