Pandas Advanced Features

Pandas provides extremely powerful data manipulation functions, suitable for complex tasks such as data cleaning, analysis, aggregation, and time series processing. Mastering the advanced features of Pandas can greatly improve the efficiency of data processing and analysis.

1. Data Merging and Joining

Pandas provides multiple methods to merge and concatenate different DataFrames, such asmerge()、concat()andjoin(). These methods are often used to handle multiple datasets and complex merging tasks.

1. `merge()`— Database-style Joining

merge()method allows merging two DataFrames based on certain columns, similar to SQL'sJOINoperation. It supports inner joins, outer joins, left joins, and right joins.

Parameters	Description
`left`	Left DataFrame
`right`	Right DataFrame
`how`	Merge method, supports`'inner'`, `'outer'`, `'left'`, `'right'`
`on`	Column name(s) for joining (if the column names differ on both sides, you can use`left_on`and`right_on`）
`left_on`	Column(s) in the left DataFrame to join on
`right_on`	Column(s) in the right DataFrame to join on
`suffixes`	Add suffixes to distinguish duplicate column names

Example

import pandas as pd

# Sample data
left = pd.DataFrame({'ID': [1, 2, 3], 'Name': ['Alice', 'Bob', 'Charlie']})
right = pd.DataFrame({'ID': [1, 2, 4], 'Age': [24, 27, 22]})

# Use merge for inner join
result = pd.merge(left, right, on='ID', how='inner')
print(result)

Output:

   ID     Name  Age
0   1    Alice   24
1   2      Bob   27

2. `concat()`— Concatenating Along Axis

concat()It is used to concatenate multiple DataFrames along a specified axis (rows or columns), commonly used for row-wise merging (vertical concatenation) or column-wise merging (horizontal concatenation).

Parameters	Description
`objs`	List of DataFrames to concatenate
`axis`	Axis of concatenation,`0`indicates merging by rows,`1`indicates merging by columns
`ignore_index`	Whether to ignore the index and regenerate the index (default is`False`）
`keys`	Provide a hierarchical index for the concatenated objects

Example

import pandas as pd

# Sample data
df1 = pd.DataFrame({'A': [1, 2, 3]})
df2 = pd.DataFrame({'A': [4, 5, 6]})

# Row-wise concatenation
result = pd.concat([df1, df2], axis=0, ignore_index=True)
print(result)

Output:

3. `join()`— Index-based Joining

join()method is a simplified joining operation in Pandas, commonly used to join multiple DataFrames based on the index.

Parameters	Description
`other`	Another DataFrame to join
`how`	Merge method, supports`'left'`, `'right'`, `'outer'`, `'inner'`
`on`	Join column to use, defaults to the index

Example

import pandas as pd

# Sample data
left = pd.DataFrame({'A': [1, 2, 3]}, index=[1, 2, 3])
right = pd.DataFrame({'B': [4, 5, 6]}, index=[1, 2, 4])

# Use join for joining
result = left.join(right, how='inner')
print(result)

Output:

   A  B
1  1  4
2  2  5

2. Pivot Tables and Cross-tabulations

Pandas providespivot_table()method to create pivot tables, andcrosstab()method to compute cross-tabulations. Both pivot tables and cross-tabulations are well suited for data summarization and rearrangement.

1. `pivot_table()`— Creating Pivot Tables

Parameters	Description
`data`	Input data
`values`	Column to be summarized
`index`	Column to be used as the row index
`columns`	Column to be used as the column index
`aggfunc`	Aggregation function, default is`mean`, can be`sum`, `count`wait
`fill_value`	Fill missing values

Example

import pandas as pd

# Sample data
data = {'Date': ['2024-01-01', '2024-01-02', '2024-01-03', '2024-01-04'],
'Category': ['A', 'B', 'A', 'B'],
'Sales': [100, 150, 200, 250]}
df = pd.DataFrame(data)

# Create a pivot table
pivot_table = pd.pivot_table(df, values='Sales', index='Date', columns='Category', aggfunc='sum', fill_value=0)
print(pivot_table)

Output:

Category         A    B
Date                    
2024-01-01     100    0
2024-01-02       0  150
2024-01-03     200    0
2024-01-04       0  250

2. `crosstab()`— Creating Cross-tabulations

Parameters	Description
`index`	Row labels
`columns`	Column labels
`values`	Data used for calculation (optional)
`aggfunc`	Aggregation function, default`count`

Example

import pandas as pd

# Sample data
data = {'Category': ['A', 'B', 'A', 'B', 'A', 'B'],
'Region': ['North', 'South', 'North', 'South', 'West', 'East']}
df = pd.DataFrame(data)

# Create a cross-tabulation
cross_table = pd.crosstab(df['Category'], df['Region'])
print(cross_table)

Output:

Region    East  North  South  West
Category                         
A           0      2      0     1
B           1      0      1     0

3. Applying Custom Functions

Pandas provides multiple ways to apply custom functions for data cleaning and transformation.

1. `apply()`— Applying Functions to DataFrame or Series

apply()method allows applying custom functions on DataFrame or Series, supporting operations on rows or columns.

Parameters	Description
`func`	Function to apply
`axis`	Default is`0`, indicates applying by column;`1`indicates applying by row
`raw`	Whether to pass the original data (default is`False`）
`result_type`	Define the type of output, such as`expand`, `reduce`, `broadcast`

Example

import pandas as pd

# Sample data
df = pd.DataFrame({'A': [1, 2, 3, 4], 'B': [10, 20, 30, 40]})

# Define a custom function
def custom_func(x):
return x * 2

# Apply the function on columns
df['A'] = df['A'].apply(custom_func)
print(df)

Output:

2. `applymap()`— Applying a Function to the Entire DataFrame

applymap()It can only be applied to a DataFrame, and it acts on each element in the DataFrame.

Parameters	Description
`func`	Function to apply

Example

import pandas as pd

# Sample data
df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})

# Apply a custom function to the DataFrame
df = df.applymap(lambda x: x ** 2)
print(df)

Output:

3. `map()`— Applying a Function to a Series

map()You can apply a function or a mapping to each element in a Series.

Parameters	Description
`arg`	Function, dictionary, or Series to apply

Example

import pandas as pd

# Sample data
df = pd.DataFrame({'A': ['cat', 'dog', 'rabbit']})

# Use a dictionary for mapping
df['A'] = df['A'].map({'cat': 'kitten', 'dog': 'puppy'})
print(df)

Output:

       A
0  kitten
1   puppy
2     NaN

4. Grouping Operations and Aggregation

In Pandas, thegroupby()method is very powerful and can be used for grouped aggregation, data transformation, and data filtering. Throughgroupby(), you can group data according to certain conditions and perform aggregation operations such as sum, mean, count, etc.

1. `groupby()`— Data Grouping

Parameters	Description
`by`	Group by which column or index
`axis`	Axis of grouping, default is`0`, that is, grouping by rows
`level`	Group by the level of the index (applicable to MultiIndex)

Example

import pandas as pd

# Sample data
df = pd.DataFrame({
'Category': ['A', 'B', 'A', 'B', 'A', 'B'],
'Value': [10, 20, 30, 40, 50, 60]
})

# Group by the Category column and calculate the sum for each group
grouped = df.groupby('Category')['Value'].sum()
print(grouped)

Output:

Category
A    90
B    120
Name: Value, dtype: int64

2. Aggregation Operations (`agg()`）

agg()Used to perform complex aggregation operations; multiple functions can be passed to compute multiple aggregate values at the same time.

Parameters	Description
`func`	Aggregation function, can be a string or a custom function

Example

import pandas as pd

# Sample data
df = pd.DataFrame({
'Category': ['A', 'B', 'A', 'B', 'A', 'B'],
'Value': [10, 20, 30, 40, 50, 60]
})

# Use agg() to perform multiple aggregation operations
grouped = df.groupby('Category')['Value'].agg([sum, min, max])
print(grouped)

Output:

          sum  min  max
Category                
A          90   10   50
B         120   20   60

5. Time Series Processing

Pandas provides powerful time series processing capabilities, including date parsing, frequency conversion, date range generation, time window operations, etc.

1. `date_range()`— Generating Time Series

Parameter	Description
`start`	Start date
`end`	End date
`periods`	Number of time points generated
`freq`	Frequency (e.g.,`D`means days,`H`means hours, etc.)

Example

import pandas as pd

# Generate time series
date_range = pd.date_range(start='2024-01-01', periods=5, freq='D')
print(date_range)

Output:

DatetimeIndex(['2024-01-01', '2024-01-02', '2024-01-03', '2024-01-04', '2024-01-05'], dtype='datetime64[ns]', freq='D')

2. Date and Time Offsets

Usingpd.Timedelta()you can perform time addition and subtraction operations.

Example

import pandas as pd

# Date offset
date = pd.to_datetime('2024-01-01')
new_date = date + pd.Timedelta(days=10)
print(new_date)

Output:

2024-01-11 00:00:00

3. Time Window Operations (Rolling, Expanding)

Usingrolling()andexpanding()methods to perform rolling and expanding window operations, commonly used in calculations such as moving averages in time series.

Method	Description
`rolling()`	Calculate rolling window operations, commonly used for moving averages, etc.
`expanding()`	Calculate expanding window operations, cumulative values

Example

import pandas as pd

# Example data
df = pd.DataFrame({'Value': [10, 20, 30, 40, 50]})

# Calculate 3-day rolling average
df['Rolling_Mean'] = df['Value'].rolling(window=3).mean()
print(df)

Output:

   Value  Rolling_Mean
0     10            NaN
1     20            NaN
2     30     20.000000
3     40     30.000000
4     50     40.000000

6. Handling Missing Values

Pandas provides multiple methods to handle missing values (such as NaN). Common operations include filling missing values, deleting missing values, etc.

Method	Description
`isna()`	Check for missing values, return boolean values
`fillna()`	Fill missing values
`dropna()`	Delete rows or columns containing missing values

Example

import pandas as pd
import numpy as np

# Example data
df = pd.DataFrame({
'A': [1, 2, np.nan, 4],
'B': [5, np.nan, 7, 8]
})

# Fill missing values
df_filled = df.fillna(0)
print(df_filled)

Output:

7. MultiIndex

Pandas provides the MultiIndex feature, making it possible to handle complex data structures in DataFrame or Series, especially suitable for hierarchical data. With MultiIndex, we can perform operations such as grouping, selecting, slicing, and aggregating data.

1. Creating a MultiIndex

Can be created usingpd.MultiIndex.from_tuples()、pd.MultiIndex.from_product()orset_index()methods to create a MultiIndex.

Method 1:`pd.MultiIndex.from_tuples()`

Use tuples to create a MultiIndex, where each tuple corresponds to one index level.

Parameter	Description
`tuples`	Each tuple corresponds to one index value
`names`	Name of each index level (optional)

Example

import pandas as pd

# Create tuples
index_tuples = [('A', 1), ('A', 2), ('B', 1), ('B', 2)]

# Create MultiIndex
multi_index = pd.MultiIndex.from_tuples(index_tuples, names=['Letter', 'Number'])

# Create DataFrame
df = pd.DataFrame({'Value': [10, 20, 30, 40]}, index=multi_index)
print(df)

Output:

               Value
Letter Number       
A      1          10
       2          20
B      1          30
       2          40

Method 2: pd.MultiIndex.from_product()

Use the Cartesian product of multiple lists to create a MultiIndex, suitable for cases with many data dimensions.

Parameter	Description
`iterables`	Multiple lists or arrays
`names`	Name of each index level (optional)

Example

import pandas as pd

# Create multiple lists
index_values = [['A', 'B'], [1, 2]]

# Create MultiIndex
multi_index = pd.MultiIndex.from_product(index_values, names=['Letter', 'Number'])

# Create DataFrame
df = pd.DataFrame({'Value': [10, 20, 30, 40]}, index=multi_index)
print(df)

Output:

               Value
Letter Number       
A      1          10
       2          20
B      1          30
       2          40

Method 3: Using`set_index()`Creating a MultiIndex

set_index()The method can convert the columns of a DataFrame into a MultiIndex, suitable for creating a MultiIndex from existing data.

Parameter	Description
`keys`	Column names used as the index (can be a single column or multiple columns)

Example

import pandas as pd

# Example data
data = {
'Letter': ['A', 'A', 'B', 'B'],
'Number': [1, 2, 1, 2],
'Value': [10, 20, 30, 40]
}

df = pd.DataFrame(data)

# Set MultiIndex
df.set_index(['Letter', 'Number'], inplace=True)
print(df)

Output:

               Value
Letter Number       
A      1          10
       2          20
B      1          30
       2          40

2. Operations on MultiIndex

1. Accessing MultiIndex Data

Data can be accessed through hierarchical indexing. Usingloc[]orxs()(cross-section) makes data selection convenient.

Usingloc[]to select data:

Example

Output:

Value    10
Name: (A, 1), dtype: int64

Usingxs()to obtain cross-sectional data:

xs()The method can select slices at specified levels in a MultiIndex.

Example

# Use xs to get all data where 'Number' is 1
print(df.xs(1, level='Number'))

Output:

        Value
Letter       
A          10
B          30

2. Slicing a MultiIndex

Pandas supports slicing operations on a MultiIndex, allowing different subsets to be selected by index level.

Example

# Select all data where Letter is 'A'
print(df.loc['A'])

Output:

        Value
Number       
1          10
2          20

3. Sorting a MultiIndex

Pandas'sort_index()method supports sorting a MultiIndex.

Example

# Sort by MultiIndex
df_sorted = df.sort_index(level=['Letter', 'Number'], ascending=[True, False])
print(df_sorted)

Output:

               Value
Letter Number       
A      2          20
       1          10
B      2          40
       1          30

4. Aggregation Operations

MultiIndex combined withgroupby()can perform powerful aggregation operations, suitable for statistical analysis of complex data.

Example

# Use groupby to aggregate on MultiIndex
df_grouped = df.groupby(['Letter', 'Number']).sum()
print(df_grouped)

Output:

               Value
Letter Number       
A      1          10
       2          20
B      1          30
       2          40

5. Resetting the Index

You can use thereset_index()method to reset the MultiIndex to regular columns.

Example

# Reset index
df_reset = df.reset_index()
print(df_reset)

Output:

  Letter  Number  Value
0      A       1     10
1      A       2     20
2      B       1     30
3      B       2     40

6. Missing Values in MultiIndex

Missing values in a MultiIndex can be handled usingfillna()ordropna()similar to a regular index.

Example

# Introduce missing values in example data
data = {
'Letter': ['A', 'A', 'B', 'B'],
'Number': [1, 2, 1, 2],
'Value': [10, None, 30, 40]
}

df = pd.DataFrame(data)
df.set_index(['Letter', 'Number'], inplace=True)

# Fill missing values
df_filled = df.fillna(0)
print(df_filled)

Output:

               Value
Letter Number       
A      1          10
       2           0
B      1          30
       2          40

Other Extensions

Data Reading and Writing

Data Processing Core

Reference Manual

Statistics and Cases

Pandas Advanced Features

1. Data Merging and Joining

1. merge()— Database-style Joining

Example

2. concat()— Concatenating Along Axis

Example

3. join()— Index-based Joining

Example

2. Pivot Tables and Cross-tabulations

1. pivot_table()— Creating Pivot Tables

Example

2. crosstab()— Creating Cross-tabulations

Example

3. Applying Custom Functions

1. apply()— Applying Functions to DataFrame or Series

Example

2. applymap()— Applying a Function to the Entire DataFrame

Example

3. map()— Applying a Function to a Series

Example

4. Grouping Operations and Aggregation

1. groupby()— Data Grouping

Example

2. Aggregation Operations (agg()）

Example

5. Time Series Processing

1. date_range()— Generating Time Series

Example

2. Date and Time Offsets

Example

3. Time Window Operations (Rolling, Expanding)

Example

6. Handling Missing Values

Example

7. MultiIndex

1. Creating a MultiIndex

Method 1:pd.MultiIndex.from_tuples()

Example

Method 2: pd.MultiIndex.from_product()

Example

Method 3: Usingset_index()Creating a MultiIndex

Example

2. Operations on MultiIndex

1. Accessing MultiIndex Data

Example

Example

2. Slicing a MultiIndex

Example

3. Sorting a MultiIndex

Example

4. Aggregation Operations

Example

5. Resetting the Index

Example

6. Missing Values in MultiIndex

Example

1. `merge()`— Database-style Joining

2. `concat()`— Concatenating Along Axis

3. `join()`— Index-based Joining

1. `pivot_table()`— Creating Pivot Tables

2. `crosstab()`— Creating Cross-tabulations

1. `apply()`— Applying Functions to DataFrame or Series

2. `applymap()`— Applying a Function to the Entire DataFrame

3. `map()`— Applying a Function to a Series

1. `groupby()`— Data Grouping

2. Aggregation Operations (`agg()`）

1. `date_range()`— Generating Time Series

Method 1:`pd.MultiIndex.from_tuples()`

Method 3: Using`set_index()`Creating a MultiIndex