基于Python 3.8、Pandas 1.2.0和Plotly 4.14.1实现可切片风险矩阵热力图及索引错误排障

Great question! Let's break down how to solve this efficiently, avoiding loops, fixing that type error you ran into, and enabling easy slicing by business unit or other dimensions.

Step 1: Clean and Normalize Your Data Properly #

First, let's fix the data type issue without inefficient loops. Pandas has built-in tools to handle this cleanly:

import pandas as pd

# Select relevant columns and drop rows with missing impact/likelihood values
df = (raca_df[['risk_id', 'gross_impact', 'gross_likelihood', 'business_unit']]
      .dropna(subset=['gross_impact', 'gross_likelihood']))

# Convert impact/likelihood to integers (handles string-formatted numbers too)
for col in ['gross_impact', 'gross_likelihood']:
    df[col] = pd.to_numeric(df[col], errors='coerce').astype(int)

This approach safely converts any numeric values (even if stored as strings) to integers, and drops any rows that can't be converted (thanks to errors='coerce' and the earlier dropna). No messy loops required.

Step 2: Efficiently Count Risk Combinations with Pivot Tables #

Forget manual numpy array assignments—Pandas' pivot_table or crosstab are optimized for exactly this kind of cross-tabulation, and they're far more readable and efficient.

Option 1: Using pivot_table (most flexible for slicing) #

# Create a pivot table: rows = Likelihood, columns = Impact, values = risk count
risk_counts = df.pivot_table(
    index='gross_likelihood',
    columns='gross_impact',
    values='risk_id',
    aggfunc='count',
    fill_value=0
)

# Ensure we have all 1-5 levels for both axes (fills missing levels with 0)
risk_counts = risk_counts.reindex(index=range(1,6), columns=range(1,6), fill_value=0)

This gives you a 5x5 DataFrame where each cell contains the number of risks for that Impact×Likelihood combination—exactly matching your heatmap requirements.

Option 2: Using pd.crosstab (shortcut for frequency counts) #

risk_counts = pd.crosstab(
    index=df['gross_likelihood'],
    columns=df['gross_impact'],
    rownames=['Likelihood'],
    colnames=['Impact']
)

# Fill in missing 1-5 levels
risk_counts = risk_counts.reindex(index=range(1,6), columns=range(1,6), fill_value=0)

Step 3: Slice by Business Unit (or Other Dimensions) #

To easily generate heatmaps for specific business units, wrap the pivot logic in a reusable function:

def get_risk_counts_by_dimension(df, business_unit=None):
    # Filter data if a business unit is specified
    filtered_df = df[df['business_unit'] == business_unit] if business_unit else df
    
    # Generate and return the completed pivot table
    counts = filtered_df.pivot_table(
        index='gross_likelihood',
        columns='gross_impact',
        values='risk_id',
        aggfunc='count',
        fill_value=0
    )
    return counts.reindex(index=range(1,6), columns=range(1,6), fill_value=0)

# Example: Get risk counts for the "Retail" business unit
retail_risk_counts = get_risk_counts_by_dimension(df, business_unit="Retail")

You can adapt this function to slice by any other column (e.g., department, risk_category) by modifying the filter logic.

Step 4: Generate Plotly Heatmaps #

Now feed the pivot table data into Plotly—both Figure Factory and Express work seamlessly with this structure.

Using Plotly Express #

import plotly.express as px

# Define your axis labels
x_labels = [
    'Minor [Very Low]', 
    'Important [Low]', 
    'Significant [Moderate]', 
    'Major [High]', 
    'Critical [Very High]'
]
y_labels = [
    'Very Remote', 
    'Remote', 
    'Unlikely', 
    'Possible', 
    'Highly Possible'
]

fig = px.imshow(
    retail_risk_counts.values,
    labels=dict(x="Impact", y="Likelihood", color="Number of Risks"),
    x=x_labels,
    y=y_labels
)
fig.update_xaxes(side="bottom")
fig.show()

Using Plotly Figure Factory (with cell annotations) #

import plotly.figure_factory as ff

# Convert pivot table values to lists and prepare annotation text
z = retail_risk_counts.values.tolist()
annotations = [[str(val) for val in row] for row in z]

fig = ff.create_annotated_heatmap(
    z,
    x=x_labels,
    y=y_labels,
    annotation_text=annotations,
    colorscale='Magma',
    showscale=True
)
fig['layout']['xaxis']['side'] = 'bottom'
fig.show()

Why Your Original Numpy Approach Failed #

The IndexError occurred because your gross_impact and gross_likelihood columns were stored as float64—numpy can't use float values as array indices. By properly converting these columns to integers during preprocessing, you eliminate this issue entirely.

Additionally, Pandas' pivot tables leverage optimized C-based operations under the hood, making them far faster than manual loops or numpy array assignments—even for your 1000-row dataset.

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