Juiced Group in Pandas

It is generally considered that pandas is one of the most popular python libraries for data science. The first and most important thing is understanding the syntax of the package.

Pandas package has a number of aggregating functions that reduce the dimension of the initial dataset. It goes with a set of SQL-like aggregation functions you can apply when grouping data during feature engineering step. Here’s a quick example of how to group on multiple columns and summarise data by applying multiple aggregation functions using Pandas.

Create a dataset

import pandas as pd

data = {
    "State": ["Alabama", "Alabama", 
              "Arizona", "Arizona", 
              "California", "California", 
              "Colorado", "Colorado", 
              "Florida", "Florida"],
              
    "City": ["Montgomery", "Birmingham", 
             "Phoenix", "Tucson", 
             "Los Angeles", "Sacramento", 
             "Denver", "Colorado Springs",
             "Tallahassee", "Miami"],
             
    "Population": [198218, 209880, 1660272, 545975, 3990456, 
                   508529, 716492, 472688, 193551, 470914],
                   
    'Real-Estate Tax': [0.42, 0.42, 0.69, 0.69, 0.76, 
                        0.76, 0.53, 0.53, 0.93, 0.93]}
    
df = pd.DataFrame(data)
print(df)

Output:

	State	City	Population	Real-Estate Tax
0	Alabama	Montgomery	198218	0.42
1	Alabama	Birmingham	209880	0.42
2	Arizona	Phoenix	1660272	0.69
3	Arizona	Tucson	545975	0.69
4	California	Los Angeles	3990456	0.76
5	California	Sacramento	508529	0.76
6	Colorado	Denver	716492	0.53
7	Colorado	Colorado Springs	472688	0.53
8	Florida	Tallahassee	193551	0.93
9	Florida	Miami	470914	0.93

Grouping by specific columns with aggregation functions

To group in pandas use the .groupby() method.

The following code will group by 'State' and 'Real-Estate Tax'. To apply aggregation functions, simply add key:value pairs as dictionary to .agg() method.

I’d recommend setting specific prefix over resulting columns to avoid possible duplicates and make your code more coherent.

Don't forget to reset index – multi-index notation isn't sklearn friendly.

grouped_df = df.groupby(['State', 'Real-Estate Tax']).agg({'Population': ['mean', 'min', 'max']})
grouped_df = grouped_df.add_prefix('population_')
grouped_df = grouped_df.reset_index()
print(grouped_df)

The full list of aggregation functions

mean(): Compute mean of groups
sum(): Compute sum of group values
size(): Compute group sizes
count(): Compute count of group
std(): Standard deviation of groups
var(): Compute variance of groups
sem(): Standard error of the mean of groups
describe(): Generates descriptive statistics
first(): Compute first of group values
last(): Compute last of group values
nth() : Take nth value, or a subset if n is a list
min(): Compute min of group values
max(): Compute max of group values

In Conclusion

In light of the above use pandas group by method, apply aggregation functions as many as possible. You can easily drop highly correlated columns afterwards.

If you are interested in another example of group by, check this guide on custom aggregation functions for pandas.