pdf_parser.py

#!/usr/bin/env python3
"""
PDF to Excel Parser - Maharashtra Engineering CAP 2024 Cutoff Data
Extracts college, branch, and cutoff data from PDF and exports to Excel.
"""

import pdfplumber
import pandas as pd
from pathlib import Path
import re


class PDFParser:
    def __init__(self, pdf_path: str):
        self.pdf_path = pdf_path
        self.all_data = []

    def normalize_category(self, category: str) -> str:
        """Normalize category abbreviations to full names."""
        if not category:
            return category
        category = str(category).strip().upper()

        mapping = {
            'GOPENS': 'OPEN (General State)',
            'GSCS': 'SC (General State)',
            'GSTS': 'ST (General State)',
            'GVJS': 'VJ (General State)',
            'GNT1S': 'NT1 (General State)',
            'GNT2S': 'NT2 (General State)',
            'GNT3S': 'NT3 (General State)',
            'GOBCS': 'OBC (General State)',
            'GSEBCS': 'SEBC (General State)',
            'LOPENS': 'OPEN (Ladies State)',
            'LSCS': 'SC (Ladies State)',
            'LSTS': 'ST (Ladies State)',
            'LVJS': 'VJ (Ladies State)',
            'LNT1S': 'NT1 (Ladies State)',
            'LNT2S': 'NT2 (Ladies State)',
            'LNT3S': 'NT3 (Ladies State)',
            'LOBCS': 'OBC (Ladies State)',
            'LSEBCS': 'SEBC (Ladies State)',
            'PWDOPENS': 'OPEN (PWD)',
            'PWDOBCS': 'OBC (PWD)',
            'PWDRSCS': 'SC (PWD Rural)',
            'PWDROBC': 'OBC (PWD Rural)',
            'DEFOPENS': 'OPEN (Defense)',
            'DEFOBCS': 'OBC (Defense)',
            'DEFROBCS': 'OBC (Defense Rural)',
            'DEFRSEBCS': 'SEBC (Defense Rural)',
            'DEFRNT1S': 'NT1 (Defense Rural)',
            'TFWS': 'TFWS (Tuition Fee Waiver)',
            'ORPHAN': 'Orphan',
            'EWS': 'EWS (Economically Weaker Section)',
            'GOPENH': 'OPEN (General Home)',
            'GSCH': 'SC (General Home)',
            'GSTH': 'ST (General Home)',
            'GVJH': 'VJ (General Home)',
            'GNT2H': 'NT2 (General Home)',
            'GNT3H': 'NT3 (General Home)',
            'GOBCH': 'OBC (General Home)',
            'GSEBCH': 'SEBC (General Home)',
            'LOPENH': 'OPEN (Ladies Home)',
            'LSCH': 'SC (Ladies Home)',
            'LSEBCH': 'SEBC (Ladies Home)',
            'LNT2H': 'NT2 (Ladies Home)',
            'GSCO': 'SC (General Other)',
            'GVJO': 'VJ (General Other)',
            'LOPENO': 'OPEN (Ladies Other)',
            'LOBCO': 'OBC (Ladies Other)',
            'GOPENO': 'OPEN (General Other)',
            'GOBCO': 'OBC (General Other)',
            'GSEBCO': 'SEBC (General Other)',
        }
        return mapping.get(category, category)

    def extract_round_number(self, text: str) -> int:
        """Extract round number from page text."""
        match = re.search(r'CAP\s+Round\s+([IVXLCDM]+|\d+)', text, re.IGNORECASE)
        if match:
            roman = match.group(1).upper()
            roman_map = {'I': 1, 'II': 2, 'III': 3, 'IV': 4, 'V': 5, 'VI': 6}
            return roman_map.get(roman, 1)
        return None

    def parse_cutoff_cell(self, cell: str) -> tuple:
        """Parse cutoff cell to extract seat number and cutoff percentage."""
        if not cell:
            return None, None

        cell = str(cell).strip()

        # Format: "34240\n(88.5013511)" or just "34240"
        seat_match = re.search(r'^(\d+)', cell)
        seat_number = int(seat_match.group(1)) if seat_match else None

        cutoff_match = re.search(r'\(([\d.]+)\)', cell)
        cutoff_percentage = float(cutoff_match.group(1)) if cutoff_match else None

        return seat_number, cutoff_percentage

    def process_page(self, page_num: int, page_text: str, tables: list):
        """Process a single page and extract all cutoff data."""
        round_num = self.extract_round_number(page_text)
        if not round_num:
            round_num = page_num

        #print(f"  Round: {round_num}, {len(tables)} tables")

        # Extract all college-branch pairs from text
        lines = page_text.split('\n')

        current_college_code = None
        current_college_name = None
        current_city = None

        college_branch_pairs = []

        for line in lines:
            line = line.strip()
            if not line:
                continue

            # Check for college line (5-digit code)
            college_match = re.match(r'^(\d{5})\s*-\s*(.+)$', line)
            if college_match:
                current_college_code = college_match.group(1)
                college_info = college_match.group(2)
                if ',' in college_info:
                    parts = college_info.rsplit(',', 1)
                    current_college_name = parts[0].strip()
                    current_city = parts[1].strip()
                else:
                    current_college_name = college_info.strip()
                    current_city = ''
                continue

            # Check for branch line (10-digit code)
            branch_match = re.match(r'^(\d{10})\s*-\s*(.+)$', line)
            if branch_match:
                branch_code = branch_match.group(1)
                branch_name = branch_match.group(2).strip()
                college_branch_pairs.append({
                    'college_code': current_college_code,
                    'college_name': current_college_name,
                    'city': current_city,
                    'branch_code': branch_code,
                    'branch_name': branch_name
                })

        #print(f"  Found {len(college_branch_pairs)} college-branch pairs")

        # Handle the case where we have more tables than branches
        # This happens when a branch's data is split across multiple tables
        if len(tables) > len(college_branch_pairs):
            # Distribute tables among branches
            table_idx = 0
            for i, pair in enumerate(college_branch_pairs):
                if table_idx >= len(tables):
                    break

                # Calculate how many tables this branch should get
                # Distribute remaining tables evenly
                remaining_tables = len(tables) - table_idx
                remaining_branches = len(college_branch_pairs) - i
                tables_for_this_branch = max(1, (remaining_tables + remaining_branches - 1) // remaining_branches)

                for _ in range(tables_for_this_branch):
                    if table_idx >= len(tables):
                        break
                    table = tables[table_idx]
                    self.process_table(table, round_num, pair)
                    table_idx += 1
        else:
            # One table per branch
            for i, pair in enumerate(college_branch_pairs):
                if i >= len(tables):
                    break
                table = tables[i]
                self.process_table(table, round_num, pair)

    def process_table(self, table: list, round_num: int, pair: dict):
        """Process a single table and extract cutoff data."""
        if not table or len(table) < 2:
            return

        # Get categories from first row
        header_row = table[0]
        categories = []
        for cell in header_row[1:]:  # Skip first column
            if cell:
                cat = str(cell).strip().upper().replace('\n', '')
                if cat and cat not in ['STAGE', 'S', 'I', 'II', 'III']:
                    categories.append(cat)

        # Process all data rows
        for row_idx in range(1, len(table)):
            data_row = table[row_idx]

            if not data_row:
                continue

            for cat_idx, category in enumerate(categories):
                cell_idx = cat_idx + 1

                if cell_idx < len(data_row):
                    seat_number, cutoff_percentage = self.parse_cutoff_cell(data_row[cell_idx])

                    if cutoff_percentage is not None:
                        self.all_data.append({
                            'Round': round_num,
                            'College Code': str(pair['college_code']),
                            'Institute Name': pair['college_name'],
                            'City': pair['city'],
                            'Branch Code': str(pair['branch_code']),
                            'Branch Name': pair['branch_name'],
                            'Seat Number': seat_number,
                            'Category': self.normalize_category(category),
                            'Category Code': category,
                            'Cut Off Percentage': cutoff_percentage
                        })

    def parse_pdf(self) -> pd.DataFrame:
        """Parse PDF and return a DataFrame with all data."""
        print(f"Parsing PDF: {self.pdf_path}\n")

        with pdfplumber.open(self.pdf_path) as pdf:
            print(f"Total pages: {len(pdf.pages)}\n")

            for i, page in enumerate(pdf.pages, 1):
                #print(f"Processing page {i}...")

                text = page.extract_text() or ''
                tables = page.extract_tables()

                if text and tables:
                    self.process_page(i, text, tables)
                else:
                    print(f"  No data found on page {i}")

                print()

        print(f"Total data rows extracted: {len(self.all_data)}")

        if self.all_data:
            df = pd.DataFrame(self.all_data)

            # Reorder columns
            column_order = [
                'Round', 'College Code', 'Institute Name', 'City',
                'Branch Code', 'Branch Name', 'Seat Number',
                'Category', 'Category Code', 'Cut Off Percentage'
            ]
            df = df.reindex(columns=column_order)

            # Convert code columns to string type
            df['College Code'] = df['College Code'].astype(str)
            df['Branch Code'] = df['Branch Code'].astype(str)

            return df
        else:
            print("Warning: No data was extracted from PDF")
            return pd.DataFrame()

    def export_to_excel(self, df: pd.DataFrame, output_path: str):
        """Export DataFrame to Excel with formatting."""
        if df.empty:
            print("No data to export")
            return

        print(f"\nExporting to Excel: {output_path}")

        with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
            # Main data sheet
            df.to_excel(writer, sheet_name='Cutoff Data', index=False)

            # Get worksheet for formatting
            workbook = writer.book
            worksheet = writer.sheets['Cutoff Data']

            # Set column widths
            column_widths = {
                'A': 8,   # Round
                'B': 15,  # College Code
                'C': 40,  # Institute Name
                'D': 20,  # City
                'E': 15,  # Branch Code
                'F': 35,  # Branch Name
                'G': 12,  # Seat Number
                'H': 30,  # Category
                'I': 15,  # Category Code
                'J': 18,  # Cut Off Percentage
            }

            for col, width in column_widths.items():
                worksheet.column_dimensions[col].width = width

            # Format code columns as text to preserve leading zeros
            for row in range(2, len(df) + 2):
                cell_college = worksheet[f'B{row}']  # College Code
                cell_branch = worksheet[f'E{row}']   # Branch Code
                cell_college.number_format = '@'
                cell_branch.number_format = '@'

            # Format cutoff column
            for row in range(2, len(df) + 2):
                cell = worksheet[f'J{row}']
                cell.number_format = '0.00#######'

            # Freeze header row
            worksheet.freeze_panes = 'A2'

            # Create summary sheet
            summary_data = []
            summary_data.append(['Metric', 'Value'])
            summary_data.append(['Total Records', len(df)])
            summary_data.append(['Total Rounds', df['Round'].nunique()])
            summary_data.append(['Total Colleges', df['College Code'].nunique()])
            summary_data.append(['Total Branches', df['Branch Code'].nunique()])
            summary_data.append(['Categories', df['Category'].nunique()])
            summary_data.append(['Total Cities', df['City'].nunique()])

            # Cutoff range by round
            summary_data.append([''])
            summary_data.append(['Cutoff Statistics by Round'])
            for round_num in sorted(df['Round'].dropna().unique()):
                round_df = df[df['Round'] == round_num]
                if not round_df['Cut Off Percentage'].isna().all():
                    min_cutoff = round_df['Cut Off Percentage'].min()
                    max_cutoff = round_df['Cut Off Percentage'].max()
                    avg_cutoff = round_df['Cut Off Percentage'].mean()
                    summary_data.append([
                        f'Round {int(round_num)}',
                        f'Min: {min_cutoff:.2f}%, Max: {max_cutoff:.2f}%, Avg: {avg_cutoff:.2f}%'
                    ])

            # Top colleges by average cutoff
            summary_data.append([''])
            summary_data.append(['Top 10 Colleges by Average Cutoff'])
            college_cutoffs = df.groupby(['College Code', 'Institute Name'])['Cut Off Percentage'].mean().sort_values(ascending=False).head(10)
            for (code, name), avg_cutoff in college_cutoffs.items():
                summary_data.append([f'{name} ({code})', f'{avg_cutoff:.2f}%'])

            # Write summary sheet
            summary_df = pd.DataFrame(summary_data[1:], columns=['Metric', 'Value'])
            summary_df.to_excel(writer, sheet_name='Summary', index=False)

            # Format summary sheet
            summary_worksheet = writer.sheets['Summary']
            summary_worksheet.column_dimensions['A'].width = 40
            summary_worksheet.column_dimensions['B'].width = 50

        print(f"Export complete: {output_path}")


def main():
    pdf_path = '2024ENGG_CAP1_CutOff-1-6.pdf'
    output_path = 'college_cutoff_data1.xlsx'

    if not Path(pdf_path).exists():
        print(f"Error: PDF file not found: {pdf_path}")
        return

    parser = PDFParser(pdf_path)
    df = parser.parse_pdf()

    if not df.empty:
        parser.export_to_excel(df, output_path)
        print("\n✓ Parsing and export completed successfully!")
        print(f"  Output file: {output_path}")
    else:
        print("\n✗ No data was extracted. Please check PDF format.")


if __name__ == '__main__':
    main()