Programming for Everyone

Code: Select all

import pandas as pd
import logging
from openpyxl import load_workbook

# Set up logging
logging.basicConfig(filename='data_transfer.log', level=logging.INFO, format='%(asctime)s - %(message)s')

# Function to load and clean data
def load_and_clean_data(requests_file, services_file):
    # Load data from Excel
    try:
        requests_df = pd.read_excel(requests_file)
        services_df = pd.read_excel(services_file)
        logging.info(f'Data successfully loaded from {requests_file} and {services_file}.')
    except FileNotFoundError as e:
        logging.error(f'Error loading files: {e}')
        return None, None

    # Clean up empty rows
    requests_df.dropna(how='all', inplace=True)
    services_df.dropna(how='all', inplace=True)

    # Check for missing values in important columns
    requests_df = requests_df[requests_df['request_id'].notnull()]
    services_df = services_df[services_df['ticket_id'].notnull()]

    logging.info(f'After cleaning: {len(requests_df)} requests and {len(services_df)} services.')

    return requests_df, services_df

# Function to merge data
def merge_data(requests_df, services_df):
    # Merge data on unique identifiers
    final_df = pd.merge(requests_df, services_df, left_on='request_id', right_on='ticket_id', how='outer')

    # Fill empty values with "Not Provided"
    final_df.fillna('Not Provided', inplace=True)

    logging.info(f'Merged {len(final_df)} records.')
    return final_df

# Function to save final data
def save_final_data(final_df, final_file):
    # Save merged data to a new Excel file
    try:
        final_df.to_excel(final_file, index=False)
        logging.info(f'Final file successfully saved to {final_file}.')
    except Exception as e:
        logging.error(f'Error saving file: {e}')

# Function to automatically update the final file
def update_final_data(requests_file, services_file, final_file):
    # Load data
    requests_df, services_df = load_and_clean_data(requests_file, services_file)
    if requests_df is None or services_df is None:
        logging.error('Failed to load data. Terminating process.')
        return

    # Merge data
    final_df = merge_data(requests_df, services_df)

    # Try adding new data to the existing final file
    try:
        wb = load_workbook(final_file)
        sheet = wb.active
        for row in final_df.values:
            sheet.append(row)
        wb.save(final_file)
        logging.info(f'Final file successfully updated.')
    except FileNotFoundError:
        # If file not found, save as new
        save_final_data(final_df, final_file)

# Function to check data integrity
def check_data_integrity(requests_df, services_df):
    # Check for matches between request_id and ticket_id
    missing_in_services = requests_df[~requests_df['request_id'].isin(services_df['ticket_id'])]
    missing_in_requests = services_df[~services_df['ticket_id'].isin(requests_df['request_id'])]

    if not missing_in_services.empty:
        logging.warning(f'Found {len(missing_in_services)} request records without corresponding services.')
    if not missing_in_requests.empty:
        logging.warning(f'Found {len(missing_in_requests)} service records without corresponding requests.')

# Main function to run the process
def main():
    requests_file = 'requests.xlsx'  # Path to the requests file
    services_file = 'services.xlsx'  # Path to the services file
    final_file = 'final_output.xlsx'  # Path to the final output file

    # Update final file
    update_final_data(requests_file, services_file, final_file)

    # Check data integrity
    requests_df, services_df = load_and_clean_data(requests_file, services_file)
    if requests_df is not None and services_df is not None:
        check_data_integrity(requests_df, services_df)

if __name__ == "__main__":
    main()