explore_data.py

import pandas as pd
import os
import json

data_dir = r"c:\Files\VibeCoding\ReportalRMS\data"
target_columns = [
    'IdCode', 'OrgNameInReport', 'ReportYear', 'FVYear', 
    'CategoryMain', 'StartDate', 'EndDate', 'FormName', 
    'SheetName', 'LineItemGEO', 'LineItemENG', 'Value', 
    'GEL'
]

files = [f for f in os.listdir(data_dir) if f.endswith('.xlsx')]
files.sort()

header_mapping = {}

print(f"Checking {len(files)} files in {data_dir}...\n")

for filename in files:
    file_path = os.path.join(data_dir, filename)
    try:
        # Just read the header
        df = pd.read_excel(file_path, nrows=0)
        cols = df.columns.tolist()
        header_mapping[filename] = cols
        
        missing = [c for c in target_columns if c not in cols]
        extra = [c for c in cols if c not in target_columns]
        
        print(f"--- {filename} ---")
        if not missing and not extra:
            print("  Status: Perfect Match")
        else:
            if missing:
                print(f"  Missing: {missing}")
            if extra:
                print(f"  Extra:   {extra}")
        print(f"  Total Columns: {len(cols)}")
    except Exception as e:
        print(f"  Error reading {filename}: {e}")

# Check for consistency across all files
print("\n--- Consistency Check ---")
all_header_sets = {}
for filename, cols in header_mapping.items():
    header_tuple = tuple(sorted(cols))
    if header_tuple not in all_header_sets:
        all_header_sets[header_tuple] = []
    all_header_sets[header_tuple].append(filename)

if len(all_header_sets) == 1:
    print("All files have the same set of columns.")
else:
    print(f"Found {len(all_header_sets)} different column sets:")
    for i, (header_tuple, filenames) in enumerate(all_header_sets.items(), 1):
        print(f"\nSet {i} ({len(filenames)} files): {filenames[:3]}..." if len(filenames) > 3 else f"\nSet {i} ({len(filenames)} files): {filenames}")
        print(f"Columns: {list(header_tuple)}")