如何修改Pandas透视表中Comment子列的数值（转Yes/No）

Got it, let's adjust those Comment values in your pivot table to show 'Yes' or 'No' based on the threshold. Here's the complete, runnable code including the fix, right after your existing pivot table setup:

import pandas as pd
import numpy as np

# Sample input data (matching your provided structure)
data_frame = pd.DataFrame({
    'PC': ['A', 'A', 'A'],
    'Geo': ['Ind', 'Ind', 'Ind'],
    'Comp': ['OS', 'OS', 'OS'],
    'Month': ['01/10/2019', '01/11/2019', '01/12/2019'],
    'Bill1': [1, 1, 1],
    'Bill2': [1.28, 1.28, 1.28],
    'Comment': [1, 2, 0]
})

# Your existing pivot table code
data_frame1 = pd.pivot_table(data_frame, index=['PC', 'Geo', 'Comp'], values=['Bill1', 'Bill2', 'Comment'], columns=['Month'], fill_value=0)
data_frame1 = data_frame1.swaplevel(0,1, axis=1).sort_index(axis=1)
# Fixed month formatting (convert string to datetime first)
tuples = [(pd.to_datetime(a).strftime('%b-%y'), b) if a!= 'All' else (a,b) for a,b in data_frame1.columns]
data_frame1.columns = pd.MultiIndex.from_tuples(tuples)

# The fix for Comment values
# Step 1: Create a mask to target only Comment sub-columns
comment_mask = data_frame1.columns.get_level_values(1) == 'Comment'
# Step 2: Replace values conditionally
data_frame1.loc[:, comment_mask] = np.where(data_frame1.loc[:, comment_mask] >= 1, 'Yes', 'No')

print(data_frame1)

How this works: #

• Target specific columns: data_frame1.columns.get_level_values(1) == 'Comment' creates a boolean mask that picks out only the sub-columns labeled 'Comment' across all months. This ensures we don't accidentally modify Bill1 or Bill2 values.
• Conditional replacement: np.where() checks each value in the selected Comment columns. If the value is >=1, it replaces it with 'Yes'; otherwise, it uses 'No'. This keeps the rest of your pivot table intact.

Expected Output: #

Oct-19                Nov-19                Dec-19        
               Bill1 Bill2 Comment Bill1 Bill2 Comment Bill1 Bill2 Comment
PC Geo Comp                                                               
A  Ind OS          1  1.28     Yes     1  1.28     Yes     1  1.28      No

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