如何基于单列实现关联列透视？并按单列筛选行（附数据示例）

Alright, let's break down how to handle both your requests using Python's Pandas library—perfect for tabular data manipulation like this. First, let's get our sample dataset into a DataFrame so we can work with it:

import pandas as pd

# Create the sample dataset
data = [
    [1, 'x1', 'y1', 'Manager', 'x2', 'y2', 1, 'Role1'],
    [1, 'x1', 'y1', 'User', 'x3', 'y3', 0, 'Role2'],
    [2, 'x4', 'y4', 'Admin', 'x2', 'y2', 0, 'Role1'],
    [2, 'x4', 'y4', 'User', 'x6', 'y6', 0, 'Role2'],
    [2, 'x4', 'y4', 'Manager', 'x7', 'y7', 0, 'Role3'],
    [3, 'b1', 'd1', None, None, None, None, None]
]

columns = [
    'PersonID', 'PersonDep', 'PersonBranch', 
    'RoleName', 'RoleDep', 'RoleBranch', 'IsPriority', 'RoleLevel'
]

df = pd.DataFrame(data, columns=columns)

1. Pivot Associated Columns Based on RoleLevel #

It looks like you want to transform the data into a wide format where each RoleLevel (like Role1, Role2) has its own set of columns for the role-related fields (RoleName, RoleDep, etc.). Here's how to do that with pivot:

# Pivot the data: use person identifiers as index, RoleLevel as columns, and role fields as values
pivoted_df = df.pivot(
    index=['PersonID', 'PersonDep', 'PersonBranch'],
    columns='RoleLevel',
    values=['RoleName', 'RoleDep', 'RoleBranch', 'IsPriority']
)

# Flatten the multi-level column names for readability
pivoted_df.columns = ['_'.join(col).strip() for col in pivoted_df.columns.values]

# Reset index to make person columns regular columns again
pivoted_df = pivoted_df.reset_index()

Result of the Pivot: #

This gives you the wide-format table you described, with each role's attributes grouped under its RoleLevel label.

2. Filter Rows Based on a Column Value #

Filtering rows is straightforward in Pandas—you just create a boolean mask based on your column condition and apply it to the DataFrame. Here are common examples using your dataset:

Example 1: Filter rows where IsPriority is 1 #

priority_rows = df[df['IsPriority'] == 1]

Result:

Example 2: Filter rows where RoleName is not null (exclude the empty role row) #

non_null_roles = df[df['RoleName'].notna()]

Example 3: Filter rows for a specific PersonID (e.g., PersonID=2) #

person_2_rows = df[df['PersonID'] == 2]

Example 4: Filter rows where RoleLevel is 'Role1' #

role1_rows = df[df['RoleLevel'] == 'Role1']

You can also combine conditions using & (AND) or | (OR) if needed—just wrap each condition in parentheses. For example:

# Filter rows where IsPriority=1 AND PersonDep='x1'
filtered = df[(df['IsPriority'] == 1) & (df['PersonDep'] == 'x1')]

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