Pandas Data Sorting and Aggregation

Data sorting and aggregation are very common and important operations in data analysis, especially when analyzing data in large datasets.

Sorting helps us arrange data according to specific criteria, while aggregation allows us to summarize data and calculate various statistics.

Pandas provides powerful sorting and aggregation functions that help analysts process data efficiently.

Operation Method Description Common Functions/Methods
Sorting sort_values(by, ascending) Sort according to the values of a column,ascendingControl ascending/descending order df.sort_values(by='column')
Sorting sort_index(axis) Sort according to row or column index df.sort_index(axis=0)
Grouped Aggregation groupby(by) After grouping by a column, apply aggregation functions df.groupby('column')
Aggregation Functions agg() Aggregation functions, such assum()、mean()、count()wait df.groupby('column').agg({'value': 'sum'})
Multiple Aggregation agg([func1, func2]) Apply multiple aggregation functions to the same column df.groupby('column').agg({'value': ['mean', 'sum']})
Sorting After Grouping apply(lambda x: x.sort_values(...)) Sort after grouping df.groupby('column').apply(lambda x: x.sort_values(...))
Pivot Table pivot_table() Create a pivot table to summarize data based on rows and columns

1. Data Sorting

Sortingrefers to arranging data in ascending or descending order according to the values of a column. Pandas provides two main methods for sorting:sort_values()andsort_index()。

Sorting Methods

  • sort_values(): Sort according to the values of a column.
  • sort_index(): Sort according to the index of rows or columns.

Examples

Operation Method Description Example
Sort by Value df.sort_values(by, ascending) According to the specified column (by) sort,ascendingControl ascending or descending order, default is ascending df.sort_values(by='Age', ascending=False)
Sort by Index df.sort_index(axis) Sort according to row or column index,axisControl sorting by row or column df.sort_index(axis=0)

sort_values()Example:

Examples

import pandas as pd

# Example data
data = {'Name': ['Alice', 'Bob', 'Charlie', 'David'],
        'Age': [25, 30, 35, 40],
        'Salary': [50000, 60000, 70000, 80000]}

df = pd.DataFrame(data)

# Sort in descending order by the value of the "Age" column
df_sorted = df.sort_values(by='Age', ascending=False)
print(df_sorted)

Output:

Name  Age  Salary
3    David   40   80000
2  Charlie   35   70000
1      Bob   30   60000
0    Alice   25   50000

sort_index()Example:

Examples

# Sort by row index
df_sorted_by_index = df.sort_index(axis=0)
print(df_sorted_by_index)

Output:

      Name  Age  Salary
0    Alice   25   50000
1      Bob   30   60000
2  Charlie   35   70000
3    David   40   80000

2. Data Aggregation

Aggregationis summarizing data according to certain rules, usually performing operations such as summing, averaging, finding the maximum, finding the minimum, etc. on data in certain columns. Pandas provides thegroupby()method to group data, and then apply different aggregation functions.

Aggregation Methods

  • groupby(): Group by certain columns.
  • Aggregation functions: such assum(), mean(), count(), min(), max(), std()etc.

Examples

Operation Method Description Example
Group by Column and Aggregate df.groupby(by).agg() Group by the specified column (by) and group,agg()Different aggregation functions can be passed in to perform multiple operations df.groupby('Department').agg({'Salary': 'mean'})
Multiple Aggregation Function Application df.groupby(by).agg([func1, func2]) Multiple aggregation functions can be applied to the same column, returning multiple aggregation results df.groupby('Department').agg({'Salary': ['mean', 'sum']})

groupby()Example:

Examples

import pandas as pd

# Example data
data = {'Department': ['HR', 'Finance', 'HR', 'IT', 'IT'],
        'Employee': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],
        'Salary': [50000, 60000, 55000, 70000, 75000]}

df = pd.DataFrame(data)

# Group by department and calculate the average salary of each department
grouped = df.groupby('Department')['Salary'].mean()
print(grouped)

Output:

Department
Finance    60000.0
HR         52500.0
IT         72500.0
Name: Salary, dtype: float64

Multiple Aggregation Function Application:

Examples

# Group by department and calculate the average and sum of salaries for each department
grouped_multiple = df.groupby('Department').agg({'Salary': ['mean', 'sum']})
print(grouped_multiple)

Output:

              Salary           
               mean    sum
Department                  
Finance    60000.0  60000
HR         52500.0  105000
IT         72500.0  145000

3. Sorting After Grouping

The aggregated data can be further sorted by the value of a certain column, which helps find the most important values in specific groups.

Sorting After Grouping

Operation Method Description Example
Sorting After Grouping df.groupby(by).apply(lambda x: x.sort_values(by='col')) Sort according to the value of a column within each group. df.groupby('Department').apply(lambda x: x.sort_values(by='Salary', ascending=False))

Example of Sorting After Grouping:

Examples

# After grouping by department, sort by salary in descending order
grouped_sorted = df.groupby('Department').apply(lambda x: x.sort_values(by='Salary', ascending=False))
print(grouped_sorted)

Output:

    Department Employee  Salary
Department                     
Finance     Bob   60000
HR          Charlie  55000
HR          Alice  50000
IT          Eve   75000
IT          David  70000

4. Pivot Tables

A pivot table (Pivot Table) is a special aggregation method that allows us to quickly summarize data through rows, columns, and aggregation functions, similar to pivot tables in Excel.

Examples

Operation Method Description Example
Create Pivot Table df.pivot_table(values, index, columns, aggfunc) Use specified columns for row and column categorization and summarization,valuesis the value to be aggregated,aggfuncis the aggregation function df.pivot_table(values='Salary', index='Department', aggfunc='mean')

Pivot Table Example：

Examples

# Use pivot_table to calculate the average salary of each department
pivot_table = df.pivot_table(values='Salary', index='Department', aggfunc='mean')
print(pivot_table)

Output:

Department
Finance    60000.0
HR         52500.0
IT         72500.0
Name: Salary, dtype: float64
Other Extensions