""" Phase 4: Root Cause Analytics Pareto analysis, T-code extraction, Resolution scoring, Reopen prediction """ import pandas as pd import numpy as np import plotly.express as px import plotly.graph_objects as go from pathlib import Path import re from collections import Counter import warnings warnings.filterwarnings('ignore') # Set display options pd.set_option('display.max_columns', 50) pd.set_option('display.width', 200) # Define paths OUTPUT_PATH = Path('/app/workspace/temp_files') IMAGES_PATH = Path('/app/workspace/assets/images') print("=" * 80) print("PHASE 4: ROOT CAUSE ANALYTICS") print("=" * 80) # Load cleaned data df = pd.read_csv(OUTPUT_PATH / 'cleaned_incidents_2025.csv') print(f"\nLoaded {len(df):,} records from cleaned dataset") # ============================================================================= # PARETO ANALYSIS OF CLOSURE CODES # ============================================================================= print("\n--- PARETO ANALYSIS: CLOSURE CODES ---") # Calculate closure code frequencies closure_counts = df['Closure_Code'].value_counts() closure_pct = (closure_counts / len(df) * 100).round(2) cumulative_pct = closure_pct.cumsum() pareto_df = pd.DataFrame({ 'Closure_Code': closure_counts.index, 'Count': closure_counts.values, 'Percentage': closure_pct.values, 'Cumulative_Pct': cumulative_pct.values }) print("\nPareto Analysis - Top Closure Codes:") print(pareto_df.head(15).to_string(index=False)) # Save pareto data pareto_df.to_csv(OUTPUT_PATH / 'closure_code_pareto.csv', index=False) # ============================================================================ # VISUALIZATION 1: Pareto Chart for Closure Codes # ============================================================================ fig1 = go.Figure() # Bar chart for counts fig1.add_trace(go.Bar( x=pareto_df['Closure_Code'][:10], y=pareto_df['Count'][:10], name='Incident Count', marker_color='steelblue', yaxis='y1' )) # Line chart for cumulative percentage fig1.add_trace(go.Scatter( x=pareto_df['Closure_Code'][:10], y=pareto_df['Cumulative_Pct'][:10], name='Cumulative %', line=dict(color='red', width=3), yaxis='y2' )) fig1.update_layout( title='Pareto Analysis: Top 10 Closure Codes', xaxis_title='Closure Code', yaxis=dict(title='Incident Count', side='left'), yaxis2=dict(title='Cumulative %', side='right', overlaying='y', range=[0, 110]), legend=dict(x=0.5, y=1.1, orientation='h', xanchor='center'), showlegend=True ) # Add 80% threshold line fig1.add_hline(y=80, line_dash="dash", line_color="green", annotation_text="80% Threshold", yref='y2') fig1.write_html(IMAGES_PATH / 'html/19_closure_pareto.html') fig1.write_image(IMAGES_PATH / 'png/19_closure_pareto.png', scale=2) print("Saved: 19_closure_pareto") # ============================================================================= # T-CODE EXTRACTION FROM DESCRIPTION AND SHORT DESCRIPTION # ============================================================================= print("\n--- T-CODE EXTRACTION ---") # SAP T-code patterns (VA02, VL02N, etc.) tcode_pattern = r'\b([A-Z]{1,2}\d{2,5}[A-Z]?\d*)\b' # Combine text fields for extraction df['Full_Text'] = df['Short_description'].fillna('') + ' ' + df['Description'].fillna('') # Extract T-codes df['T_Codes'] = df['Full_Text'].apply(lambda x: re.findall(tcode_pattern, x.upper())) # Count T-code occurrences tcode_list = [] for codes in df['T_Codes']: tcode_list.extend(codes) tcode_counts = Counter(tcode_list) tcode_df = pd.DataFrame(tcode_counts.most_common(20), columns=['T_Code', 'Frequency']) print("\nTop 20 SAP Transaction Codes Found:") print(tcode_df.to_string(index=False)) # Save T-code data tcode_df.to_csv(OUTPUT_PATH / 'sap_tcode_frequency.csv', index=False) # ============================================================================ # VISUALIZATION 2: T-Code Frequency Chart # ============================================================================ fig2 = px.bar(tcode_df[:15], x='T_Code', y='Frequency', color='Frequency', title='Top 15 SAP Transaction Codes in Incident Descriptions', labels={'T_Code': 'Transaction Code', 'Frequency': 'Occurrence Count'}, color_continuous_scale='Viridis') fig2.update_layout(xaxis_title='SAP Transaction Code') fig2.write_html(IMAGES_PATH / 'html/20_tcode_frequency.html') fig2.write_image(IMAGES_PATH / 'png/20_tcode_frequency.png', scale=2) print("Saved: 20_tcode_frequency") # ============================================================================ # T-Code to Closure Code Mapping # ============================================================================ print("\n--- T-CODE CLUSTER ANALYSIS ---") # For incidents with T-codes, analyze closure patterns df_with_tcodes = df[df['T_Codes'].apply(len) > 0].copy() print(f"Incidents with identifiable T-codes: {len(df_with_tcodes):,} ({len(df_with_tcodes)/len(df)*100:.1f}%)") # Primary T-code per incident df_with_tcodes['Primary_TCode'] = df_with_tcodes['T_Codes'].apply(lambda x: x[0] if x else None) # T-code vs Closure Code analysis tcode_closure = pd.crosstab(df_with_tcodes['Primary_TCode'], df_with_tcodes['Closure_Code'], normalize='index') * 100 print("\nTop T-Codes by Closure Code Pattern:") for tcode in tcode_df['T_Code'][:5]: if tcode in tcode_closure.index: top_closure = tcode_closure.loc[tcode].nlargest(3) print(f" {tcode}: {dict(top_closure.round(1))}") # ============================================================================= # RESOLUTION NOTES COMPLETENESS SCORING # ============================================================================= print("\n--- RESOLUTION NOTES COMPLIANCE SCORING ---") # Define completeness criteria df['Resolution_Notes_Complete'] = df['Resolution_notes'].notna().astype(int) df['Resolution_Notes_Length'] = df['Resolution_notes'].fillna('').str.len() # Define compliance thresholds def score_completeness(row): score = 0 if pd.notna(row['Resolution_notes']): notes = row['Resolution_notes'].lower() # Check for key elements in resolution notes if any(term in notes for term in ['issue', 'problem', 'real issue']): score += 25 # Issue documented if any(term in notes for term in ['resolution', 'steps', 'resolved', 'fix']): score += 25 # Resolution steps documented if any(term in notes for term in ['root cause', 'corrective', 'preventive']): score += 25 # Root cause analysis if row['Resolution_Notes_Length'] > 100: score += 25 # Sufficient detail return score df['Resolution_Score'] = df.apply(score_completeness, axis=1) # Score distribution score_dist = df['Resolution_Score'].value_counts().sort_index() print("\nResolution Notes Completeness Score Distribution:") print(f" Score 0 (No notes): {score_dist.get(0, 0):,}") print(f" Score 25-50 (Partial): {score_dist[score_dist.index.isin([25, 50])].sum():,}") print(f" Score 75-100 (Complete): {score_dist[score_dist.index.isin([75, 100])].sum():,}") # Average score by Assignment Group resolution_by_group = df.groupby('Assignment_group').agg({ 'Resolution_Score': 'mean', 'Resolution_Notes_Complete': 'mean' }).round(2) resolution_by_group.columns = ['Avg_Score', 'Completion_Rate'] resolution_by_group = resolution_by_group.sort_values('Avg_Score', ascending=False) print("\nResolution Documentation by Assignment Group:") print(resolution_by_group.to_string()) # ============================================================================ # VISUALIZATION 3: Resolution Completeness Distribution # ============================================================================ fig3 = px.histogram(df, x='Resolution_Score', title='Resolution Notes Completeness Score Distribution', labels={'Resolution_Score': 'Completeness Score (0-100)', 'count': 'Number of Incidents'}, color_discrete_sequence=['#2E86AB'], nbins=20) fig3.update_layout(showlegend=False) fig3.write_html(IMAGES_PATH / 'html/21_resolution_completeness.html') fig3.write_image(IMAGES_PATH / 'png/21_resolution_completeness.png', scale=2) print("Saved: 21_resolution_completeness") # ============================================================================ # VISUALIZATION 4: Resolution Score by Assignment Group # ============================================================================ fig4 = px.bar(resolution_by_group.reset_index(), x='Assignment_group', y='Avg_Score', color='Avg_Score', title='Resolution Documentation Quality by Assignment Group', labels={'Assignment_group': 'Assignment Group', 'Avg_Score': 'Avg Completeness Score'}, color_continuous_scale='RdYlGn') fig4.add_hline(y=75, line_dash="dash", line_color="green", annotation_text="Target: 75") fig4.update_layout(yaxis_range=[0, 100]) fig4.write_html(IMAGES_PATH / 'html/22_resolution_by_group.html') fig4.write_image(IMAGES_PATH / 'png/22_resolution_by_group.png', scale=2) print("Saved: 22_resolution_by_group") # ============================================================================= # REOPEN COUNT ANALYSIS # ============================================================================= print("\n--- REOPEN COUNT ANALYSIS ---") # Reopen statistics reopen_stats = df['Reopen_count'].value_counts().sort_index() print(f"\nReopen Distribution:") print(f" Zero reopens: {reopen_stats.get(0, 0):,} ({reopen_stats.get(0, 0)/len(df)*100:.1f}%)") print(f" One reopen: {reopen_stats.get(1, 0):,} ({reopen_stats.get(1, 0)/len(df)*100:.1f}%)") print(f" Multiple reopens: {len(df[df['Reopen_count'] > 1]):,} ({len(df[df['Reopen_count'] > 1])/len(df)*100:.1f}%)") # Incidents that were reopened reopened = df[df['Reopen_count'] > 0] print(f"\nTotal Reopened Incidents: {len(reopened):,}") # Analyze characteristics of reopened incidents if len(reopened) > 0: print("\nReopened Incident Characteristics:") print(f" - Avg MTTR: {reopened['MTTR_hours'].mean():.2f} hours") print(f" - Avg Resolution Score: {reopened['Resolution_Score'].mean():.1f}") print(f" - Top Closure Codes:") for code, count in reopened['Closure_Code'].value_counts().head(5).items(): print(f" {code}: {count} ({count/len(reopened)*100:.1f}%)") # ============================================================================ # VISUALIZATION 5: Reopen Rate by Closure Code # ============================================================================ reopen_by_closure = df.groupby('Closure_Code').agg({ 'Reopen_count': lambda x: (x > 0).mean() * 100 }).round(2) reopen_by_closure.columns = ['Reopen_Rate_%'] reopen_by_closure = reopen_by_closure.sort_values('Reopen_Rate_%', ascending=False) fig5 = px.bar(reopen_by_closure.reset_index()[:10], x='Closure_Code', y='Reopen_Rate_%', color='Reopen_Rate_%', title='Reopen Rate by Closure Code (Top 10)', labels={'Closure_Code': 'Closure Code', 'Reopen_Rate_%': 'Reopen Rate (%)'}, color_continuous_scale='RdYlGn_r') fig5.update_layout(yaxis_title='Reopen Rate (%)') fig5.write_html(IMAGES_PATH / 'html/23_reopen_by_closure.html') fig5.write_image(IMAGES_PATH / 'png/23_reopen_by_closure.png', scale=2) print("Saved: 23_reopen_by_closure") # ============================================================================ # VISUALIZATION 6: Reopen Rate vs Resolution Quality # ============================================================================ resolution_bins = [0, 25, 50, 75, 100] resolution_labels = ['Poor (0-25)', 'Fair (26-50)', 'Good (51-75)', 'Excellent (76-100)'] df['Resolution_Quality'] = pd.cut(df['Resolution_Score'], bins=resolution_bins, labels=resolution_labels) reopen_by_quality = df.groupby('Resolution_Quality', observed=True).agg({ 'Reopen_count': lambda x: (x > 0).mean() * 100, 'Number': 'count' }).round(2) reopen_by_quality.columns = ['Reopen_Rate_%', 'Incident_Count'] fig6 = px.scatter(reopen_by_quality.reset_index(), x='Incident_Count', y='Reopen_Rate_%', size='Incident_Count', color='Resolution_Quality', title='Impact of Resolution Quality on Reopen Rate', labels={'Resolution_Quality': 'Resolution Quality', 'Incident_Count': 'Number of Incidents', 'Reopen_Rate_%': 'Reopen Rate (%)'}) fig6.write_html(IMAGES_PATH / 'html/24_resolution_vs_reopen.html') fig6.write_image(IMAGES_PATH / 'png/24_resolution_vs_reopen.png', scale=2) print("Saved: 24_resolution_vs_reopen") # ============================================================================= # SUMMARY METRICS # ============================================================================= print("\n" + "=" * 80) print("PHASE 4 COMPLETE: Root Cause Analytics") print("=" * 80) print(f"\nKey Findings:") print(f" - Top Closure Code: '{pareto_df.iloc[0]['Closure_Code']}' ({pareto_df.iloc[0]['Percentage']:.1f}%)") print(f" - Incidents with T-codes: {len(df_with_tcodes):,} ({len(df_with_tcodes)/len(df)*100:.1f}%)") print(f" - Top T-Code: '{tcode_df.iloc[0]['T_Code']}' ({tcode_df.iloc[0]['Frequency']} occurrences)") print(f" - Avg Resolution Score: {df['Resolution_Score'].mean():.1f}/100") print(f" - Reopen Rate: {(df['Reopen_count'] > 0).mean()*100:.2f}%") # Save summary data summary_df = pd.DataFrame({ 'Metric': ['Total Incidents', 'Top Closure Code', 'Top Closure %', 'Incidents with T-Codes', 'Top T-Code', 'Top T-Code Count', 'Avg Resolution Score', 'Reopen Rate %'], 'Value': [len(df), pareto_df.iloc[0]['Closure_Code'], pareto_df.iloc[0]['Percentage'], len(df_with_tcodes), tcode_df.iloc[0]['T_Code'], tcode_df.iloc[0]['Frequency'], round(df['Resolution_Score'].mean(), 1), round((df['Reopen_count'] > 0).mean() * 100, 2)] }) summary_df.to_csv(OUTPUT_PATH / 'root_cause_summary.csv', index=False) print(f"\nSummary saved to {OUTPUT_PATH / 'root_cause_summary.csv'}")