📊 Unit 1 – DataFrame · Lesson 13
Sorting, GROUP BY & Aggregation
Sample data
Python
import pandas as pd
sales = pd.DataFrame({
'Rep': ['Anil', 'Bina', 'Chetan', 'Anil', 'Bina', 'Divya', 'Chetan', 'Divya'],
'Region': ['North', 'South', 'North', 'North', 'South', 'East', 'North', 'East'],
'Product': ['TV', 'AC', 'AC', 'Fridge', 'TV', 'TV', 'TV', 'Fridge'],
'Units': [5, 3, 4, 2, 6, 3, 1, 4],
'Amount': [150000, 105000, 140000, 64000, 180000, 90000, 30000, 128000]})
sales.to_csv('sales.csv', index=False)
print(sales) Rep Region Product Units Amount
0 Anil North TV 5 150000
1 Bina South AC 3 105000
2 Chetan North AC 4 140000
3 Anil North Fridge 2 64000
4 Bina South TV 6 180000
5 Divya East TV 3 90000
6 Chetan North TV 1 30000
7 Divya East Fridge 4 128000
sort_values()
Sort rows by one or more columns. ascending=True is the default.
Python
import pandas as pd
sales = pd.read_csv('sales.csv')
print(sales.sort_values('Amount', ascending=False).head(3))
print(sales.sort_values(['Region', 'Units'])[['Region', 'Rep', 'Units']]) Rep Region Product Units Amount
4 Bina South TV 6 180000
0 Anil North TV 5 150000
2 Chetan North AC 4 140000
Region Rep Units
5 East Divya 3
7 East Divya 4
6 North Chetan 1
3 North Anil 2
2 North Chetan 4
0 North Anil 5
1 South Bina 3
4 South Bina 6
sort_index()
Python
import pandas as pd
sales = pd.read_csv('sales.csv')
s = sales.set_index('Rep').sort_index() # sort by index labels
print(s[['Units', 'Amount']]) Units Amount
Rep
Anil 5 150000
Anil 2 64000
Bina 3 105000
Bina 6 180000
Chetan 4 140000
Chetan 1 30000
Divya 3 90000
Divya 4 128000
Aggregation
aggregate() / agg() applies one or more statistical functions at once.
Python
import pandas as pd
sales = pd.read_csv('sales.csv')
print(sales[['Units', 'Amount']].aggregate(['sum', 'max', 'min']))
print(sales['Amount'].agg(['mean', 'count'])) Units Amount
sum 28 887000
max 6 180000
min 1 30000
mean 110875.0
count 8.0
Name: Amount, dtype: float64
groupby()
Python
import pandas as pd
sales = pd.read_csv('sales.csv')
g = sales.groupby('Region')
print(g['Amount'].sum())
print(g.size()) # rows in each group
print(g.get_group('East'))Region
East 218000
North 384000
South 285000
Name: Amount, dtype: int64
Region
East 2
North 4
South 2
dtype: int64
Rep Region Product Units Amount
5 Divya East TV 3 90000
7 Divya East Fridge 4 128000
More groupby examples
Python
import pandas as pd
sales = pd.read_csv('sales.csv')
print(sales.groupby('Rep')[['Units', 'Amount']].sum())
print(sales.groupby('Product')['Amount'].agg(['count', 'sum', 'mean']))
print(sales.groupby(['Region', 'Product'])['Units'].sum()) Units Amount
Rep
Anil 7 214000
Bina 9 285000
Chetan 5 170000
Divya 7 218000
count sum mean
Product
AC 2 245000 122500.0
Fridge 2 192000 96000.0
TV 4 450000 112500.0
Region Product
East Fridge 4
TV 3
North AC 4
Fridge 2
TV 6
South AC 3
TV 6
Name: Units, dtype: int64
Python
import pandas as pd
sales = pd.read_csv('sales.csv')
total = sales.groupby('Rep')['Amount'].sum().sort_values(ascending=False)
print(total)
print('Best salesperson:', total.index[0], 'with', total.iloc[0])Rep
Bina 285000
Divya 218000
Anil 214000
Chetan 170000
Name: Amount, dtype: int64
Best salesperson: Bina with 285000
pandas vs SQL
| Task | pandas | SQL |
|---|---|---|
| Sort | df.sort_values('Amount', ascending=False) | ORDER BY Amount DESC |
| Group total | df.groupby('Region')['Amount'].sum() | SELECT Region, SUM(Amount) … GROUP BY Region |
| Count per group | df.groupby('Region').size() | SELECT Region, COUNT(*) … GROUP BY Region |
Frequently Asked Questions
How do I sort in descending order?
Pass ascending=False, e.g. df.sort_values("Amount", ascending=False).
What is the difference between size() and count() after groupby?
size() counts all rows in each group; count() counts non-NaN values in each column of each group.
How is groupby in pandas related to GROUP BY in SQL?
They do the same job: split rows into groups by a column and apply an aggregate function to each group.
