

"I am a Chartered Accountant doing a bank audit. I have a CSV file called 01_loan_master.csv which has loan data with these columns: loan_id, borrower_name, loan_type, outstanding_bal, overdue_days, bank_asset_category.
Write me simple Python code for Google Colab that does the following:
1. Upload the CSV file by using FILES section where I can upload this file in google colab
2. Read it with pandas and show the first 5 rows
3. Write a function called rbi_classify that takes overdue_days as input and returns the correct RBI category using this rule:

0 days = Standard
1 to 30 days = SMA-0
31 to 60 days = SMA-1
61 to 90 days = SMA-2
91 to 365 days = NPA-Substandard
More than 365 days = NPA-Doubtful

4. Apply this function to every row and create a new column called correct_category
5. Compare bank_asset_category with correct_category and find accounts where the bank's classification is WRONG — especially accounts the bank shows as Standard but should actually be NPA. Print these accounts.
6. Print a summary showing: total accounts checked, how many are correctly classified, how many are misclassified, and the total outstanding balance of hidden NPAs
7. Draw one simple bar chart showing count of accounts by correct_category using matplotlib with different colors for each category
Make the code simple with clear comments on every line so a beginner can understand. Add print statements with emojis to make the output look nice. Also add a few lines of code at the end to export this final report and the list of hidden NPAs into a brand new Excel file that download automatically and I can present it"








https://gemini.google.com/share/3326aa7634c1





# Import the files module from Google Colab to enable file uploading 📁
from google.colab import files
# Import the pandas library to work with our data using DataFrames 🐼
import pandas as pd
# Import the pyplot module from matplotlib to draw our bar chart 📊
import matplotlib.pyplot as plt

# --- Step 1: Upload the CSV file ---
# Print a friendly message asking the user to upload the file 📤
print("🚀 Please upload your '01_loan_master.csv' file below:")
# Call the upload function which opens a file selection dialog in Colab 📂
uploaded = files.upload()

# --- Step 2: Read with pandas and show the first 5 rows ---
# Load the uploaded CSV file into a pandas DataFrame named 'df' 📄
# Note: This assumes the file is named exactly '01_loan_master.csv' 📝
df = pd.read_csv('01_loan_master.csv')
# Print a success message with an emoji ✅
print("\n✅ File loaded successfully! Here are the first 5 rows of your loan data:")
# Display the first 5 rows of the DataFrame to verify the data 👀
display(df.head())

# --- Step 3: Write the rbi_classify function ---
# Define a function named 'rbi_classify' that takes 'overdue_days' as its argument ⚙️
def rbi_classify(overdue_days):
    # Check if the overdue days are exactly 0 🗓️
    if overdue_days == 0:
        # Return 'Standard' if there are no overdue days 🟢
        return 'Standard'
    # Check if the overdue days are between 1 and 30 (inclusive) 🗓️
    elif 1 <= overdue_days <= 30:
        # Return 'SMA-0' for 1 to 30 days overdue 🟡
        return 'SMA-0'
    # Check if the overdue days are between 31 and 60 (inclusive) 🗓️
    elif 31 <= overdue_days <= 60:
        # Return 'SMA-1' for 31 to 60 days overdue 🟠
        return 'SMA-1'
    # Check if the overdue days are between 61 and 90 (inclusive) 🗓️
    elif 61 <= overdue_days <= 90:
        # Return 'SMA-2' for 61 to 90 days overdue 🔴
        return 'SMA-2'
    # Check if the overdue days are between 91 and 365 (inclusive) 🗓️
    elif 91 <= overdue_days <= 365:
        # Return 'NPA-Substandard' for 91 to 365 days overdue 🛑
        return 'NPA-Substandard'
    # If the overdue days are more than 365 🗓️
    else:
        # Return 'NPA-Doubtful' for anything over a year ❌
        return 'NPA-Doubtful'

# --- Step 4: Apply the function to create a new column ---
# Apply our 'rbi_classify' function to every single row in the 'overdue_days' column 🔄
# Save the results into a brand new column called 'correct_category' 🆕
df['correct_category'] = df['overdue_days'].apply(rbi_classify)
# Print a message confirming the new column was created successfully 🛠️
print("\n⚙️ RBI Classification rules applied to all accounts!")

# --- Step 5: Find misclassified accounts and hidden NPAs ---
# Create a filter (mask) where the bank's category does NOT match our correct category 🔍
misclassified_mask = df['bank_asset_category'] != df['correct_category']
# Use the mask to create a new DataFrame containing only the wrong classifications ⚠️
misclassified_df = df[misclassified_mask]

# Create another filter specifically for hidden NPAs (Bank says Standard, we say NPA) 🕵️‍♂️
# We check if the bank category is 'Standard' AND our correct category contains 'NPA' 🚨
hidden_npa_mask = (df['bank_asset_category'] == 'Standard') & (df['correct_category'].str.contains('NPA'))
# Use this specific mask to filter the data for hidden NPAs 🚩
hidden_npas = df[hidden_npa_mask]

# Print a header for our hidden NPA findings 🚨
print("\n🚨 HIDDEN NPAs DETECTED (Bank classified as Standard, but should be NPA):")
# Check if we actually found any hidden NPAs 🧐
if len(hidden_npas) > 0:
    # If we found some, display them nicely on the screen 📋
    display(hidden_npas[['loan_id', 'borrower_name', 'outstanding_bal', 'overdue_days', 'bank_asset_category', 'correct_category']])
# If we didn't find any 😌
else:
    # Print a reassuring message that no hidden NPAs were found 🎉
    print("🎉 Great news! No hidden NPAs found in this dataset.")

# --- Step 6: Print a summary ---
# Calculate the total number of accounts by getting the length of the main DataFrame 🧮
total_accounts = len(df)
# Calculate the total number of misclassified accounts 🧮
total_misclassified = len(misclassified_df)
# Calculate the total number of correctly classified accounts by subtracting the wrong ones 🧮
total_correct = total_accounts - total_misclassified
# Calculate the total outstanding balance of the hidden NPAs by summing their balances 💰
hidden_npa_balance = hidden_npas['outstanding_bal'].sum()

# Print a separator line for the summary section ➖
print("\n" + "="*45)
# Print the summary header 📊
print("📊 AUDIT SUMMARY REPORT 📊")
# Print the separator line again ➖
print("="*45)
# Print the total number of accounts checked 📝
print(f"Total accounts checked: {total_accounts}")
# Print the number of correctly classified accounts ✅
print(f"Correctly classified: {total_correct} ✅")
# Print the number of misclassified accounts ⚠️
print(f"Misclassified accounts: {total_misclassified} ⚠️")
# Print the total financial exposure from hidden NPAs formatted with commas 💵
print(f"Total outstanding balance of Hidden NPAs: ₹ {hidden_npa_balance:,.2f} 💸")
# Print a bottom separator line ➖
print("="*45)

# --- Step 7: Draw a simple bar chart ---
# Count how many accounts fall into each 'correct_category' 📈
category_counts = df['correct_category'].value_counts()

# Create a new figure for our chart, setting the size to 10 inches wide by 6 inches tall 🖼️
plt.figure(figsize=(10, 6))
# Define a list of colors to use for the different bars in the chart 🎨
colors = ['#2ecc71', '#f1c40f', '#e67e22', '#e74c3c', '#c0392b', '#8e44ad']
# Draw the actual bar chart using the category counts and our chosen colors 📊
category_counts.plot(kind='bar', color=colors, edgecolor='black')

# Add a descriptive title to the top of the chart 🏷️
plt.title('Distribution of Loan Accounts by Correct RBI Category 📈', fontsize=14)
# Add a label to the horizontal X-axis 📏
plt.xlabel('Asset Category', fontsize=12)
# Add a label to the vertical Y-axis 📏
plt.ylabel('Number of Accounts', fontsize=12)
# Rotate the X-axis labels slightly so they are easier to read 📐
plt.xticks(rotation=45)
# Add a subtle grid behind the bars to make reading values easier 🕸️
plt.grid(axis='y', linestyle='--', alpha=0.7)
# Adjust the layout so nothing gets cut off at the edges ✂️
plt.tight_layout()

# Print a message letting the user know the chart is ready 📉
print("\n📉 Generating Asset Classification Chart...")
# Display the finished chart on the screen 🖼️
plt.show()