如何切片Pandas多级索引DataFrame？提取指定子列方法咨询

Got it, let's break this down into two clear parts: first extracting the exact columns you need from your multi-level Excel data, then walking through common slicing operations for Pandas MultiIndex DataFrames.

Assuming you've loaded your Excel file into a Pandas DataFrame with a multi-level column index (using header=[0,1] if your Excel has two rows of headers), here are two straightforward ways to get your desired columns:

Method 1: Explicit Column List #

First, identify which columns belong to the name level, then add the specific contact_info > mobile column:

import pandas as pd

# Load your Excel file (adjust header based on your actual data structure)
df = pd.read_excel("your_file.xlsx", header=[0, 1])

# Get all columns under the 'name' level
name_columns = [col for col in df.columns if col[0] == "name"]
# Add the 'mobile' column under 'contact_info'
target_columns = name_columns + [("contact_info", "mobile")]

# Filter the DataFrame to only keep these columns
filtered_df = df.loc[:, target_columns]

Method 2: Using pd.IndexSlice (Cleaner for MultiIndex) #

For more concise code, use IndexSlice to directly specify the levels you want:

import pandas as pd

idx = pd.IndexSlice
df = pd.read_excel("your_file.xlsx", header=[0, 1])

# Select all columns under 'name', plus 'contact_info > mobile'
filtered_df = df.loc[:, idx["name", :].union(idx["contact_info", "mobile"])]

MultiIndex slicing works for both rows and columns—here are the most common use cases:

Slicing Columns #

• Get all columns from a single level:

  # All columns under 'name'
  name_only_df = df.loc[:, idx["name", :]]
  # Or using xs (cross-section) for a cleaner result
  name_only_df = df.xs("name", axis=1, level=0)
  

• Get specific columns across multiple levels:

  # Get 'name > first_name' and 'contact_info > mobile'
  specific_cols_df = df.loc[:, [("name", "first_name"), ("contact_info", "mobile")]]
  

Slicing Rows (If your DataFrame has a MultiIndex index) #

Suppose your rows have a two-level index (e.g., ['region', 'user_id']):

• Slice by the first level:

  # All rows where first level is 'North'
  north_rows = df.loc[idx["North", :], :]
  

• Slice by both levels:

  # Rows where first level is 'North' and second level ranges from 100 to 200
  filtered_rows = df.loc[idx["North", 100:200], :]
  

Mixed Row + Column Slicing #

Combine row and column slicing in one step:

# Get all 'name' columns for rows where first index level is 'South'
mixed_slice = df.loc[idx["South", :], idx["name", :]]

Pro Tip: Using xs for Quick Cross-Sections #

The xs method is perfect for extracting a single level without keeping the hierarchy (set keep_levels=False to flatten the result):

# Extract 'mobile' column and drop the multi-level header
mobile_series = df.xs(("contact_info", "mobile"), axis=1, level=[0,1], keep_levels=False)

内容的提问来源于stack exchange，提问作者Gaurang Shah