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.