Chapter 27 pandas Methods Practice
27.1 Sorting
sort_values is a method in the pandas library used to sort the values in a DataFrame or Series.
Purpose:
sort_valuesis used to sort a DataFrame or Series by one or more columns or by the values in the Series.Syntax:
27.1.0.1 Examples:
Sample dataframe
import pandas as pd
data = {
'NAME': ['David', 'Alice', 'Charlie', 'Bob',],
'AGE': [25, 30, 40, 35],
'SALARY': [50000, 600000, 55000, 700000]
}
df = pd.DataFrame(data)
df## NAME AGE SALARY
## 0 David 25 50000
## 1 Alice 30 600000
## 2 Charlie 40 55000
## 3 Bob 35 700000- Sort by a Single Column:
## NAME AGE SALARY
## 1 Alice 30 600000
## 3 Bob 35 700000
## 2 Charlie 40 55000
## 0 David 25 50000- Sort by Multiple Columns:
# Sort by 'age' in ascending order, then by 'salary' in descending order
sorted_df = df.sort_values(by=['AGE', 'SALARY'], ascending=[True, False])
print(sorted_df)## NAME AGE SALARY
## 0 David 25 50000
## 1 Alice 30 600000
## 3 Bob 35 700000
## 2 Charlie 40 55000- Handling
NaNValues:
data = {
'NAME': ['Alice', 'Bob', 'Charlie', 'David'],
'AGE': [25, 30, None, 40]
}
df = pd.DataFrame(data)
# Sort by 'age' and place NaN values at the start
sorted_df = df.sort_values(by='AGE', na_position='first')
print(sorted_df)## NAME AGE
## 2 Charlie NaN
## 0 Alice 25.0
## 1 Bob 30.0
## 3 David 40.027.1.0.2 Key Points:
axis: Axis to be sorted along (0 for index, 1 for columns). Default is 0.
ignore_index: If True, the resulting index will be labeled 0, 1, 2, …, n - 1. Default is False.
inplace: If True, perform the operation in place and return None. Default is False.
27.1.1 groupby operation
The groupby operation in pandas is a powerful tool for aggregating and transforming data.
It allows you to split your DataFrame into groups based on one or more columns, apply functions to each group, and then combine the results back into a DataFrame or Series.
import pandas as pd
data = {
'department': ['Sales', 'Sales', 'HR', 'HR', 'IT', 'IT', 'Finance', 'Finance'],
'employee': ['Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank', 'Grace', 'Hannah'],
'salary': [70000, 80000, 50000, 60000, 90000, 85000, 75000, 65000],
'bonus': [5000, 7000, 4000, 6000, 9000, 8500, 7500, 6500]
}
df_company = pd.DataFrame(data)
df_company## department employee salary bonus
## 0 Sales Alice 70000 5000
## 1 Sales Bob 80000 7000
## 2 HR Charlie 50000 4000
## 3 HR David 60000 6000
## 4 IT Eve 90000 9000
## 5 IT Frank 85000 8500
## 6 Finance Grace 75000 7500
## 7 Finance Hannah 65000 650027.1.1.1 Group by a Single Column
Suppose you want to find the average salary by department.
Final object is a series with index is department.
# Group by 'department' and calculate the mean salary
grouped_df = df_company.groupby('department')['salary'].mean()
print(grouped_df)## department
## Finance 70000.0
## HR 55000.0
## IT 87500.0
## Sales 75000.0
## Name: salary, dtype: float64## <class 'pandas.core.series.Series'># Convert the resulting Series into a DataFrame
grouped_df = grouped_df.reset_index()
# Rename the column for clarity
grouped_df.columns = ['department', 'average_salary']
grouped_df## department average_salary
## 0 Finance 70000.0
## 1 HR 55000.0
## 2 IT 87500.0
## 3 Sales 75000.027.1.1.2 Group by Multiple Columns:
You can group by multiple columns.
For example, find the total compensation (salary + bonus) for each employee in each department.
# Group by 'department' and 'employee', and calculate total compensation
df_company['total_compensation'] = df_company['salary'] + df_company['bonus']
grouped_df = df_company.groupby(['department', 'employee'])['total_compensation'].sum()
print(grouped_df)## department employee
## Finance Grace 82500
## Hannah 71500
## HR Charlie 54000
## David 66000
## IT Eve 99000
## Frank 93500
## Sales Alice 75000
## Bob 87000
## Name: total_compensation, dtype: int64## <class 'pandas.core.series.Series'>27.1.1.3 Aggregation Functions:
You can use multiple aggregation functions on the grouped data.
For example, find the sum and mean of the salary and bonus for each department.
# Group by 'department' and calculate sum and mean of 'salary' and 'bonus'
agg_df = df_company.groupby('department').agg({ 'salary': ['sum', 'mean'],
'bonus': ['sum', 'mean']
})
print(agg_df)## salary bonus
## sum mean sum mean
## department
## Finance 140000 70000.0 14000 7000.0
## HR 110000 55000.0 10000 5000.0
## IT 175000 87500.0 17500 8750.0
## Sales 150000 75000.0 12000 6000.0## <class 'pandas.core.frame.DataFrame'>27.1.1.4 groupby and unnested data
agg_df = df_company.groupby('department').agg(
total_salary = pd.NamedAgg(column='salary', aggfunc='sum'),
avg_salary = pd.NamedAgg(column='salary', aggfunc='mean'),
total_bonus = pd.NamedAgg(column='bonus', aggfunc='sum'),
avg_bonus = pd.NamedAgg(column='bonus', aggfunc='mean')
)
print(agg_df)## total_salary avg_salary total_bonus avg_bonus
## department
## Finance 140000 70000.0 14000 7000.0
## HR 110000 55000.0 10000 5000.0
## IT 175000 87500.0 17500 8750.0
## Sales 150000 75000.0 12000 6000.0agg_df = df_company.groupby('department').agg({
'salary': 'sum', 'bonus': 'sum'
}).rename(columns={
'salary': 'total_salary',
'bonus': 'total_bonus'
})
agg_df['avg_salary'] = df_company.groupby('department')['salary'].mean()
agg_df['avg_bonus'] = df_company.groupby('department')['bonus'].mean()
print(agg_df)## total_salary total_bonus avg_salary avg_bonus
## department
## Finance 140000 14000 70000.0 7000.0
## HR 110000 10000 55000.0 5000.0
## IT 175000 17500 87500.0 8750.0
## Sales 150000 12000 75000.0 6000.027.1.1.5 Transformation
data = {
'department': ['Sales', 'Sales', 'HR', 'HR', 'IT', 'IT', 'Finance', 'Finance'],
'employee': ['Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank', 'Grace', 'Hannah'],
'salary': [70000, 80000, 50000, 60000, 90000, 85000, 95000, 65000],
}
df = pd.DataFrame(data)
# Group by 'department' and calculate sum of 'salary' with transform
df['total_salary'] = df.groupby('department')['salary'].transform('sum')
print(df)## department employee salary total_salary
## 0 Sales Alice 70000 150000
## 1 Sales Bob 80000 150000
## 2 HR Charlie 50000 110000
## 3 HR David 60000 110000
## 4 IT Eve 90000 175000
## 5 IT Frank 85000 175000
## 6 Finance Grace 95000 160000
## 7 Finance Hannah 65000 160000## department employee salary total_salary
## 4 IT Eve 90000 175000
## 5 IT Frank 85000 175000
## 6 Finance Grace 95000 16000027.1.1.6 Example
Lets perform a standardization operation on the salary column within each group defined by the department column
# Group by 'department' and calculate sum of 'salary' with transform
df2 = df.copy()
df2['demeaned_salary'] = df2.groupby('department')['salary'].transform(lambda x: (x - x.mean()))
print(df2)## department employee salary total_salary demeaned_salary
## 0 Sales Alice 70000 150000 -5000.0
## 1 Sales Bob 80000 150000 5000.0
## 2 HR Charlie 50000 110000 -5000.0
## 3 HR David 60000 110000 5000.0
## 4 IT Eve 90000 175000 2500.0
## 5 IT Frank 85000 175000 -2500.0
## 6 Finance Grace 95000 160000 15000.0
## 7 Finance Hannah 65000 160000 -15000.027.1.1.7 Filtering Groups:
You can filter out groups that meet a specific condition.
For example, keep only departments with a total salary of more than $150,000.
# Group by 'department' and filter departments with total salary > 150,000
filtered_df = df.groupby('department').filter(lambda x: x['salary'].sum() > 150000)
print(filtered_df)## department employee salary total_salary
## 4 IT Eve 90000 175000
## 5 IT Frank 85000 175000
## 6 Finance Grace 95000 160000
## 7 Finance Hannah 65000 160000df2['total_salary'] = df.groupby('department')['salary'].transform('sum')
df2 = df2[df2['total_salary'] > 150000]
df2## department employee salary total_salary demeaned_salary
## 4 IT Eve 90000 175000 2500.0
## 5 IT Frank 85000 175000 -2500.0
## 6 Finance Grace 95000 160000 15000.0
## 7 Finance Hannah 65000 160000 -15000.027.1.1.8 Examples
df2 = df.copy()
df2 = df2.groupby('department').agg({'salary': ['sum', 'mean', 'count', 'min', 'max']})
df2## salary
## sum mean count min max
## department
## Finance 160000 80000.0 2 65000 95000
## HR 110000 55000.0 2 50000 60000
## IT 175000 87500.0 2 85000 90000
## Sales 150000 75000.0 2 70000 80000`