Automated Attendance Tracking System in Excel

This Python script provides a function to automate attendance tracking using Excel files with the help of libraries pandas and openpyxl. The function reads an attendance file, checks against a roster, and updates the attendance records.

Necessary Imports

import pandas as pd
from openpyxl import load_workbook

Function Definition

def update_attendance(roster_file: str, attendance_file: str, output_file: str) -> None:
    """
    Updates attendance records based on the roster provided in an Excel file.
    
    Parameters:
    - roster_file (str): Path to the Excel file containing the roster.
    - attendance_file (str): Path to the Excel file containing attendance records.
    - output_file (str): Path where the updated attendance file will be saved.

    Returns:
    None

    Raises:
    FileNotFoundError: If either of the supplied files does not exist.
    ValueError: If the required columns are missing in the provided files.
    """
    # Load roster and attendance data
    try:
        roster_df = pd.read_excel(roster_file)
        attendance_df = pd.read_excel(attendance_file)
    except FileNotFoundError as e:
        print(f"Error: {e}")
        return

    # Validate if necessary columns exist
    if 'Name' not in roster_df.columns or 'Date' not in attendance_df.columns:
        raise ValueError("Required columns 'Name' or 'Date' are missing from the files.")

    # Ensure 'Name' is in the correct format (lowercase for case-insensitivity)
    roster_df['Name'] = roster_df['Name'].str.lower()
    attendance_df['Name'] = attendance_df['Name'].str.lower()

    # Check attendance against roster
    attendance_df['Status'] = attendance_df['Name'].apply(
        lambda x: 'Present' if x in roster_df['Name'].values else 'Absent'
    )

    # Save updated attendance data to the specified Excel output file
    attendance_df.to_excel(output_file, index=False)
    print("Attendance updated successfully!")

Explanation of Code

1. Imports: We utilize pandas for data manipulation and openpyxl for Excel file handling.

2. Function Parameters:

   • roster_file: The Excel file that contains the list of names eligible for attendance.
   • attendance_file: The Excel file holding the attendance data to be updated.
   • output_file: The path for the updated attendance records to be saved.

3. File Loading:

   • The function loads both Excel files using pandas. It handles FileNotFoundError gracefully if the files do not exist.

4. Validation:

   • It checks if the required columns ('Name' for roster and 'Date' for attendance) exist in the respective files. A ValueError is raised if they are absent.

5. Updating Attendance:

   • The function normalizes names to lowercase to avoid case sensitivity issues.
   • It then updates the Status field based on whether each name from the attendance list is present in the roster.

6. Output:

   • Finally, the updated attendance DataFrame is saved as a new Excel file.

Code Usage Example

Below is an example of how to use the update_attendance function in a real-world scenario:

# Example Usage
roster_file = 'roster.xlsx'           # Excel file containing the names
attendance_file = 'attendance.xlsx'    # Excel file with attendance records
output_file = 'updated_attendance.xlsx' # Where to save the updated records

# Call the function to update attendance
update_attendance(roster_file, attendance_file, output_file)

Conclusion

This code provides a robust solution for automating attendance tracking in Excel. By leveraging pandas and openpyxl, it ensures efficient handling of data and addresses common issues like case sensitivity and missing files.

For furthering your data science and Python skills, consider exploring courses available on the Enterprise DNA Platform.