如何比较Pandas多级透视表中的值？查询执行报错求助

Hey there! The issue you're hitting comes down to your pivot table having a multi-level column index (MultiIndex)—and your current query isn't accounting for that nested structure when trying to access columns like prop1 or prop0.

Why the Error Happens #

When you create your combined pivot table, the columns are nested (e.g., prop1 lives under the Dose > 1 level, not as a top-level column). Using Output.prop1 tells Pandas to look for a top-level column named prop1, which doesn't exist—hence the error.

Fix 1: Access Columns via Full MultiIndex Path #

The most direct fix is to reference the full nested path of each column using tuple notation:

# Use tuple syntax to target the exact nested columns
filtered_df = Output[
    (Output[('Dose', '1', 'prop1')] > Output[('Dose', '0', 'prop0')]) 
    | 
    (Output[('Dose', '2', 'prop2')] > Output[('Dose', '0', 'prop0')])
]

Fix 2: Flatten the MultiIndex Columns (Simpler for Future Queries) #

If you don't need to keep the nested column structure, you can flatten it into single-level names first:

# Merge the multi-level column names into a single string
Output.columns = ['_'.join(str(col) for col in col_tuple) for col_tuple in Output.columns]

# Now your original-style query will work
filtered_df = Output[(Output.Dose_1_prop1 > Output.Dose_0_prop0) | (Output.Dose_2_prop2 > Output.Dose_0_prop0)]

After flattening, your columns will look like Dose_0_dose0, Dose_0_prop0, Dose_1_dose1, etc.

Fix 3: Use xs to Extract Specific Column Levels #

If you want to keep the multi-level structure, use Pandas' xs (cross-section) method to pull out the specific prop columns:

# Extract each prop column from the multi-level index
prop0 = Output.xs('prop0', level=2, axis=1)
prop1 = Output.xs('prop1', level=2, axis=1)
prop2 = Output.xs('prop2', level=2, axis=1)

# Apply your filter condition
filtered_df = Output[(prop1 > prop0) | (prop2 > prop0)]

Quick Check to Confirm the Column Structure #

First, run this to verify your multi-level columns:

print(Output.columns)

You'll see output like:

MultiIndex([('Dose', '0', 'dose0'),
            ('Dose', '0', 'prop0'),
            ('Dose', '1', 'dose1'),
            ('Dose', '1', 'prop1'),
            ('Dose', '2', 'dose2'),
            ('Dose', '2', 'prop2')],
           )

This confirms exactly how your columns are nested, which helps validate the fixes above.

Your Provided Data for Reference #

Pivot Table Output:

Dose
            0        1        2
          dose0 prop0 dose1 prop1 dose2 prop2
Organ  Diagnosis
heart  xyz     1    0.05     0    0.00     0    0.00
Lung   ghi     0    0.00     0    0.00     1    0.03
Kidney def     0    0.00     1    0.03     0    0.00
skin   jkl     0    0.00     5    0.16     0    0.00
liver  abc     8    0.42     6    0.19     6    0.19

Original Sample Data:

Organ  Diagnosis  Dose
heart  xyz          0
kidney abc          1
liver  def          2
kidney qrs          1
liver  dfj          2
heart  gdh          0
heart  hdh          1
kidney edr          2

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