import pandas as pd

def merge_scores(master_file, sub_file, output_file='results.csv'):
    """
    Merge WebWork scores from sub file into master file based on SIS User ID.
    Rows in sub file with SIS User IDs not present in master file are ignored.

    Parameters:
    master_file (str): Path to the master CSV file
    sub_file (str): Path to the sub CSV file containing new scores
    output_file (str): Path where the resulting CSV will be saved
    """
    try:
        webwork_col = 'WebWork Average (233950)'
        # Read the CSV files
        master_df = pd.read_csv(master_file)
        sub_df = pd.read_csv(sub_file)

        # Verify required columns exist
        required_columns = ['SIS User ID', webwork_col]
        for df, name in [(master_df, 'master'), (sub_df, 'sub')]:
            missing_cols = [col for col in required_columns if col not in df.columns]
            if missing_cols:
                raise ValueError(f"Missing required columns in {name} file: {missing_cols}")

        # Create a copy of master dataframe to preserve original data
        result_df = master_df.copy()

        # Get only the SIS User IDs that exist in master
        valid_ids = set(master_df['SIS User ID']).intersection(set(sub_df['SIS User ID']))

        # Filter sub_df to only include rows with valid IDs
        filtered_sub_df = sub_df[sub_df['SIS User ID'].isin(valid_ids)]

        # Create a dictionary of new scores from filtered sub file
        score_updates = dict(zip(filtered_sub_df['SIS User ID'], filtered_sub_df[webwork_col]))

        # Update scores in result dataframe
        result_df.loc[result_df['SIS User ID'].isin(score_updates.keys()), webwork_col] = \
            result_df.loc[result_df['SIS User ID'].isin(score_updates.keys()), 'SIS User ID'].map(score_updates)

        # Save the result to a new CSV file
        result_df.to_csv(output_file, index=False)

        # Print summary of updates and ignored rows
        num_updates = len(valid_ids)
        num_ignored = len(sub_df) - num_updates
        print(f"Successfully updated {num_updates} scores")
        print(f"Ignored {num_ignored} rows from sub file (IDs not found in master)")
        print(f"Results saved to {output_file}")

    except Exception as e:
        print(f"An error occurred: {str(e)}")

if __name__ == "__main__":
    # Example usage
    merge_scores('master.csv', 'sub.csv', 'results.csv')