""" Phase 7: Problem Management Analysis Top-10 Most Recurring Issues with Keyword Clustering and Probable Cause Extraction """ 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, defaultdict 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 7: PROBLEM MANAGEMENT ANALYSIS") print("Top-10 Most Recurring Issues with Keyword Clustering") print("=" * 80) # Load cleaned data df = pd.read_csv(OUTPUT_PATH / 'cleaned_incidents_2025.csv') print(f"\nLoaded {len(df):,} records from cleaned dataset") # ============================================================================= # DATA PREPARATION - EXCLUDE DATA ISSUES # ============================================================================= print("\n--- DATA PREPARATION ---") # Filter out Data Issues as specified df_ndi = df[~df['Closure_Code'].str.contains('Data Issue', case=False, na=False)].copy() print(f"Total Incidents (excluding Data Issues): {len(df_ndi):,}") print(f"Percentage of total: {len(df_ndi)/len(df)*100:.1f}%") # ============================================================================= # KEYWORD EXTRACTION AND CLUSTERING # ============================================================================= print("\n--- KEYWORD EXTRACTION & CLUSTERING ---") # Clean and normalize text def clean_text(text): if pd.isna(text): return "" text = str(text).lower() # Remove special characters but keep alphanumeric text = re.sub(r'[^\w\s]', ' ', text) # Remove extra whitespace text = ' '.join(text.split()) return text df_ndi['Clean_Short_Desc'] = df_ndi['Short_description'].apply(clean_text) df_ndi['Clean_Description'] = df_ndi['Description'].fillna('').apply(clean_text) # Define common SAP-related stop words to filter out stop_words = { 'issue', 'problem', 'error', 'not', 'working', 'unable', 'unable', 'facing', 'getting', 'help', 'need', 'request', 'support', 'sap', 'honeywell', 'user', 'please', 'thanks', 'regards', 'dear', 'hi', 'hello', 'the', 'is', 'are', 'was', 'were', 'been', 'being', 'have', 'has', 'had', 'do', 'does', 'did', 'will', 'would', 'could', 'should', 'may', 'might', 'to', 'of', 'in', 'for', 'on', 'with', 'at', 'by', 'from', 'as', 'into', 'a', 'an', 'and', 'or', 'but', 'if', 'so', 'that', 'this', 'these', 'those', 'we', 'they', 'you', 'it', 'its', 'our', 'their', 'my', 'your', 'one', 'two', 'three', 'first', 'following', 'while' } # Additional business context words business_stop_words = { 'order', 'sales', 'delivery', 'pricing', 'customer', 'material', 'vendor', 'invoice', 'payment', 'purchase', 'stock', 'inventory', 'batch' } # Extract keywords from Short Description def extract_keywords(text, min_len=4): words = text.split() keywords = [w for w in words if len(w) >= min_len and w not in stop_words] return keywords df_ndi['Keywords'] = df_ndi['Clean_Short_Desc'].apply(extract_keywords) # Count overall keyword frequency all_keywords = [] for kw_list in df_ndi['Keywords']: all_keywords.extend(kw_list) keyword_counts = Counter(all_keywords) print(f"\nTotal unique keywords extracted: {len(keyword_counts):,}") print(f"\nTop 30 Keywords:") for kw, count in keyword_counts.most_common(30): print(f" {kw}: {count}") # ============================================================================= # KEYWORD-BASED ISSUE CLUSTERING # ============================================================================= print("\n--- KEYWORD-BASED ISSUE CLUSTERING ---") # Define issue pattern rules based on common SAP patterns issue_patterns = { 'Pricing/Cost Issue': ['price', 'pricing', 'cost', 'rate', 'value', 'amount', 'currency', 'valuation'], 'Order Creation Failure': ['create', 'creation', 'va01', 'va02', 'sales order', 'order entry', 'enter order'], 'Order Modification': ['change', 'modify', 'change order', 'va02', 'update order', 'amend'], 'Order Display/Inquiry': ['display', 'view', 'show', 'va03', 'check order', 'inquiry'], 'Delivery Issue': ['delivery', 'vl01', 'vl02', 'vl10', 'shipping', 'shipment', 'outbound'], 'Billing/Invoice Issue': ['invoice', 'billing', 'vf01', 'vf02', 'vf03', 'bill'], 'Purchase Order Issue': ['purchase order', 'po', 'me21', 'me22', 'me23', 'procurement'], 'Material/Product Issue': ['material', 'product', 'item', 'material master', 'material group'], 'Stock/Inventory Issue': ['stock', 'inventory', 'availability', 'mb1b', 'mb51', 'migo'], 'Customer Master Issue': ['customer', 'sold to', 'ship to', 'kdun', 'xd03'], 'Vendor Master Issue': ['vendor', 'vendor master', 'bk03', 'create vendor'], 'Report/Output Issue': ['report', 'output', 'print', 'output determination', 'nast'], 'Authorization/Access Issue': ['authorization', 'access', 'permission', 'role', 's_tcode', 'su53'], 'Workflow/Approval Issue': ['workflow', 'approval', 'me21n', 'approval workflow', 'ws'], 'Configuration Error': ['configuration', 'config', 'setup', 'spro', 'customizing'], 'Interface/Integration Issue': ['interface', 'integration', 'idoc', 'bapi', 'ale'], 'Data Migration Issue': ['migration', 'upload', 'transfer', 'interface'], 'Master Data Issue': ['master data', 'md', 'customer data', 'vendor data'], 'Goods Receipt Issue': ['goods receipt', 'gr', 'mb01', 'mbl', '101'], 'Payment Issue': ['payment', 'payment run', 'f110', 'dme', 'bank'], } def assign_issue_cluster(text): if pd.isna(text): return 'Other' text_lower = text.lower() for cluster_name, patterns in issue_patterns.items(): for pattern in patterns: if pattern in text_lower: return cluster_name return 'Other' df_ndi['Issue_Cluster'] = df_ndi['Short_description'].apply(assign_issue_cluster) # Count clusters cluster_counts = df_ndi['Issue_Cluster'].value_counts() print("\nIssue Cluster Distribution:") print(cluster_counts.to_string()) # ============================================================================= # PROBABLE CAUSE EXTRACTION FROM RESOLUTION NOTES # ============================================================================= print("\n--- PROBABLE CAUSE EXTRACTION ---") # Define probable cause patterns cause_patterns = { 'Missing/Correct Master Data': ['missing master data', 'master data missing', 'no master data', 'customer not created', 'vendor not created', 'material not created'], 'Incorrect Configuration': ['incorrect configuration', 'wrong config', 'config error', 'configuration missing', 'configuration issue'], 'User Training Gap': ['user training', 'training required', 'user error', 'not trained', 'human error', 'user mistake'], 'Data Entry Error': ['data entry error', 'wrong entry', 'incorrect data entry', 'data typo', 'data error', 'entered wrong'], 'Authorization Issue': ['authorization', 'no authorization', 'missing authorization', 'access issue', 'no access', 'permission denied'], 'Interface Failure': ['interface error', 'interface issue', 'idoc error', 'interface failed', 'integration error', 'transfer failed'], 'System Bug/Defect': ['system bug', 'system error', 'software bug', 'defect', 'sap bug', 'standard error'], 'Process Not Followed': ['process not followed', 'wrong process', 'incorrect process', 'procedure not followed', 'not as per process'], 'Integration Point Failure': ['integration failed', 'integration issue', 'upstream system', 'downstream system', 'linked system'], 'Master Data Inconsistency': ['master data inconsistency', 'data mismatch', 'inconsistent data', 'duplicate data', 'data conflict'], 'Workflow Stuck': ['workflow stuck', 'pending approval', 'waiting for approval', 'workflow not moving', 'approval pending'], 'Document Blocked': ['document blocked', 'order blocked', 'customer blocked', 'vendor blocked', 'material blocked', 'billing block'], 'Version/Upgrade Issue': ['version issue', 'upgrade', 'sp version', 'patch', 'release upgrade', 'version mismatch'], 'Template/Form Issue': ['template', 'form', 'print template', 'output form', 'smartform', 'sapscript'], 'Third-Party Integration': ['third party', 'external system', 'edi', 'external interface'], } def extract_probable_causes(resolution_notes): if pd.isna(resolution_notes): return [] notes_lower = str(resolution_notes).lower() causes = [] for cause_name, patterns in cause_patterns.items(): for pattern in patterns: if pattern in notes_lower: causes.append(cause_name) break return causes if causes else ['Unspecified'] df_ndi['Probable_Causes'] = df_ndi['Resolution_notes'].apply(extract_probable_causes) # Count causes all_causes = [] for cause_list in df_ndi['Probable_Causes']: all_causes.extend(cause_list) cause_counts = Counter(all_causes) print("\nProbable Cause Frequency (All Incidents):") for cause, count in cause_counts.most_common(15): print(f" {cause}: {count}") # ============================================================================= # TOP-10 RECURRING ISSUES REPORT # ============================================================================= print("\n" + "=" * 80) print("TOP-10 MOST RECURRING ISSUES REPORT") print("=" * 80) # Get top 10 clusters top_10_clusters = cluster_counts.head(10) problem_report = [] for rank, (cluster_name, count) in enumerate(top_10_clusters.items(), 1): cluster_df = df_ndi[df_ndi['Issue_Cluster'] == cluster_name] # Get common keywords in this cluster cluster_keywords = [] for kw_list in cluster_df['Keywords']: cluster_keywords.extend(kw_list) top_keywords = Counter(cluster_keywords).most_common(5) # Get probable causes cluster_causes = [] for cause_list in cluster_df['Probable_Causes']: cluster_causes.extend(cause_list) top_causes = Counter(cluster_causes).most_common(3) # Calculate metrics avg_mttr = cluster_df['MTTR_hours'].mean() sla_rate = (cluster_df['SLA_Status'] == 'Met').mean() * 100 problem_entry = { 'Rank': rank, 'Issue_Cluster': cluster_name, 'Incident_Count': count, 'Pct_of_Non_Data_Issues': round(count / len(df_ndi) * 100, 1), 'Avg_MTTR_Hours': round(avg_mttr, 1), 'SLA_Compliance_%': round(sla_rate, 1), 'Top_Keywords': ', '.join([f"{kw}({c})" for kw, c in top_keywords]), 'Probable_Causes': ', '.join([f"{cause}({c})" for cause, c in top_causes]) } problem_report.append(problem_entry) print(f"\n{rank}. {cluster_name}") print(f" Incidents: {count} ({problem_entry['Pct_of_Non_Data_Issues']}%)") print(f" Avg MTTR: {avg_mttr:.1f} hours | SLA Compliance: {sla_rate:.1f}%") print(f" Top Keywords: {problem_entry['Top_Keywords']}") print(f" Probable Causes: {problem_entry['Probable_Causes']}") # Create report DataFrame problem_report_df = pd.DataFrame(problem_report) print("\n" + "-" * 80) print("Problem Management Summary Table:") print(problem_report_df.to_string(index=False)) # Save report problem_report_df.to_csv(OUTPUT_PATH / 'top_10_problems_report.csv', index=False) print(f"\nReport saved to {OUTPUT_PATH / 'top_10_problems_report.csv'}") # ============================================================================= # VISUALIZATIONS # ============================================================================ # VISUALIZATION 1: Top-10 Issues Bar Chart fig1 = px.bar(problem_report_df, x='Issue_Cluster', y='Incident_Count', color='SLA_Compliance_%', title='Top 10 Most Recurring Issues (Excluding Data Issues)', labels={'Issue_Cluster': 'Issue Category', 'Incident_Count': 'Number of Incidents', 'SLA_Compliance_%': 'SLA Compliance (%)'}, color_continuous_scale='RdYlGn') fig1.update_layout(xaxis_title='Issue Category', yaxis_title='Incident Count', yaxis={'categoryorder': 'total descending'}) fig1.write_html(IMAGES_PATH / 'html/33_top_10_problems.html') fig1.write_image(IMAGES_PATH / 'png/33_top_10_problems.png', scale=2) print("Saved: 33_top_10_problems") # VISUALIZATION 2: Issue Cluster Treemap fig2 = px.treemap(df_ndi, path=['Issue_Cluster'], title='Issue Cluster Distribution (Treemap)', color='Issue_Cluster', color_discrete_sequence=px.colors.qualitative.Set3) fig2.write_html(IMAGES_PATH / 'html/34_issue_cluster_treemap.html') fig2.write_image(IMAGES_PATH / 'png/34_issue_cluster_treemap.png', scale=2) print("Saved: 34_issue_cluster_treemap") # VISUALIZATION 3: Probable Cause Distribution cause_df = pd.DataFrame(list(cause_counts.items()), columns=['Cause', 'Count']) cause_df = cause_df.sort_values('Count', ascending=True) fig3 = px.bar(cause_df[cause_df['Count'] >= 10], y='Cause', x='Count', orientation='h', color='Count', title='Probable Causes Distribution (Incidents ≥10)', labels={'Cause': 'Probable Cause', 'Count': 'Occurrence Count'}, color_continuous_scale='Viridis') fig3.update_layout(yaxis={'categoryorder': 'total ascending'}) fig3.write_html(IMAGES_PATH / 'html/35_probable_causes.html') fig3.write_image(IMAGES_PATH / 'png/35_probable_causes.png', scale=2) print("Saved: 35_probable_causes") # VISUALIZATION 4: Issue Cluster vs Probable Cause Heatmap print("\n--- CREATING ISSUE-CAUSE HEATMAP ---") issue_cause_matrix = pd.DataFrame(index=df_ndi['Issue_Cluster'].unique()) for cause in list(cause_counts.keys())[:8]: # Top 8 causes issue_cause_matrix[cause] = df_ndi.groupby('Issue_Cluster').apply( lambda x: sum(cause in c for c in x['Probable_Causes']) ) issue_cause_matrix = issue_cause_matrix.loc[cluster_counts.head(10).index] issue_cause_matrix = issue_cause_matrix.loc[:, (issue_cause_matrix.sum() > 5)] fig4 = px.imshow(issue_cause_matrix, labels=dict(x="Probable Cause", y="Issue Cluster", color="Count"), title='Issue Cluster × Probable Cause Matrix', color_continuous_scale='YlOrRd') fig4.write_html(IMAGES_PATH / 'html/36_issue_cause_heatmap.html') fig4.write_image(IMAGES_PATH / 'png/36_issue_cause_heatmap.png', scale=2) print("Saved: 36_issue_cause_heatmap") # VISUALIZATION 5: MTTR by Issue Cluster (Box Plot) fig5 = px.box(df_ndi[df_ndi['Issue_Cluster'].isin(cluster_counts.head(8).index)], x='Issue_Cluster', y='MTTR_hours', color='Issue_Cluster', title='MTTR Distribution by Top 8 Issue Clusters', labels={'Issue_Cluster': 'Issue Category', 'MTTR_hours': 'MTTR (Hours)'}) fig5.update_layout(showlegend=False, xaxis={'categoryorder': 'total descending'}) fig5.write_html(IMAGES_PATH / 'html/37_mttr_by_issue.html') fig5.write_image(IMAGES_PATH / 'png/37_mttr_by_issue.png', scale=2) print("Saved: 37_mttr_by_issue") # ============================================================================= # DETAILED PROBLEM STATEMENTS FOR EACH TOP ISSUE # ============================================================================= print("\n" + "=" * 80) print("DETAILED PROBLEM STATEMENTS") print("=" * 80) for _, row in problem_report_df.iterrows(): cluster_name = row['Issue_Cluster'] cluster_df = df_ndi[df_ndi['Issue_Cluster'] == cluster_name] # Sample descriptions sample_descs = cluster_df['Short_description'].dropna().head(3).tolist() print(f"\n{'='*60}") print(f"PROBLEM #{row['Rank']}: {cluster_name}") print(f"{'='*60}") print(f"Impact: {row['Incident_Count']} incidents ({row['Pct_of_Non_Data_Issues']}% of non-data issues)") print(f"Average Resolution Time: {row['Avg_MTTR_Hours']} hours") print(f"SLA Compliance Rate: {row['SLA_Compliance_%']}%") print(f"\nProbable Root Causes:") for cause in row['Probable_Causes'].split(', '): print(f" • {cause}") print(f"\nRepresentative Issue Descriptions:") for i, desc in enumerate(sample_descs, 1): desc_str = str(desc)[:100] + "..." if len(str(desc)) > 100 else str(desc) print(f" {i}. {desc_str}") # ============================================================================= # FINAL SUMMARY # ============================================================================= print("\n" + "=" * 80) print("PHASE 7 COMPLETE: Problem Management Analysis") print("=" * 80) print(f"\nKey Findings:") print(f" - Total Non-Data Issue Incidents: {len(df_ndi):,}") print(f" - Unique Issue Clusters Identified: {len(cluster_counts)}") print(f" - Most Common Issue: '{cluster_counts.idxmax()}' ({cluster_counts.max()} incidents)") print(f" - Highest MTTR Issue: {problem_report_df.loc[problem_report_df['Avg_MTTR_Hours'].idxmax(), 'Issue_Cluster']} ({problem_report_df['Avg_MTTR_Hours'].max()} hours)") print(f" - Lowest SLA Compliance: {problem_report_df.loc[problem_report_df['SLA_Compliance_%'].idxmin(), 'Issue_Cluster']} ({problem_report_df['SLA_Compliance_%'].min()}%)") print(f"\nTop Probable Causes Across All Issues:") for cause, count in cause_counts.most_common(5): print(f" - {cause}: {count} occurrences") print(f"\nAll visualizations saved to {IMAGES_PATH}") print(f"Report saved to {OUTPUT_PATH / 'top_10_problems_report.csv'}")