Skip to content
TheChatArchiveThe Chat Archive
Metallama-3.3-70b-instruct· MetaHugging Face LMArena Human Preference 140K

import pandas as pd import os import glob import sys import numpy as np openpyxl is used…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Qwenqwen-max-2025-01-25’s answer.
  1. User

    import pandas as pd import os import glob import sys import numpy as np

    openpyxl is used by Pandas for .xlsx files, and we'll use its utility for column letters

    from openpyxl.utils import get_column_letter

    def auto_adjust_column_width(worksheet, df, start_col_offset=0): """ Auto-adjusts the column widths for a given worksheet. :param worksheet: The openpyxl worksheet object. :param df: The DataFrame that was written to this part of the sheet. :param start_col_offset: The starting column index (0-based) where df was written. """ for col_idx, column in enumerate(df.columns): column_letter = get_column_letter(col_idx + 1 + start_col_offset) # openpyxl is 1-indexed max_length = 0

        # Check header length
        column_header = str(column)
        if len(column_header) > max_length:
            max_length = len(column_header)
            
        # Check cell content lengths
        # Need to handle potential NaNs or non-string types gracefully
        for cell_value in df[column]:
            try:
                cell_str = str(cell_value)
                if len(cell_str) > max_length:
                    max_length = len(cell_str)
            except:
                pass # Ignore errors in converting cell value to string for length check
                
        adjusted_width = (max_length + 2) # Add a little padding
        worksheet.column_dimensions[column_letter].width = adjusted_width
    

    def process_excel_file(input_filename): print(f"\n--- Processing file: {input_filename} ---") sys.stdout.flush() # Ensure this print is displayed immediately try: xls = pd.ExcelFile(input_filename) sheet_names = xls.sheet_names except FileNotFoundError: print(f"Error: The file '{input_filename}' was not found.") sys.stdout.flush() return except Exception as e: print(f"Error reading Excel file '{input_filename}': {e}") sys.stdout.flush() return

    all_data_frames = []
    header_row_index = 6
    key_column_name = 'PRODUCTION ORDER' # This is the clean, stripped version
    column_to_drop = "S.No" # This is the clean, stripped version
    
    if not sheet_names:
        print(f"No sheets found in '{input_filename}'. Skipping this file.")
        sys.stdout.flush()
        return
    
    for sheet_name in sheet_names:
        try:
            df = pd.read_excel(xls, sheet_name=sheet_name, header=header_row_index)
            # Strip whitespace from column names for the current sheet's DataFrame
            df.columns = [str(col).strip() for col in df.columns]
    
            if df.empty:
                print(f"Sheet '{sheet_name}' in '{input_filename}' is empty. Skipping this sheet.")
                sys.stdout.flush()
                continue
    
            if all(str(col).startswith('Unnamed:') for col in df.columns):
                print(f"Warning: Sheet '{sheet_name}' in '{input_filename}' seems to have no valid header at row {header_row_index + 1}, or is empty below it. Columns found: {df.columns.tolist()}. Skipping this sheet.")
                sys.stdout.flush()
                continue
    
            if key_column_name not in df.columns:
                print(f"Warning: Column '{key_column_name}' not found in sheet '{sheet_name}' of file '{input_filename}' (available columns: {df.columns.tolist()}). Skipping data from this sheet.")
                sys.stdout.flush()
                continue
    
            original_row_count = len(df)
            # Ensure key column is treated as string for stripping, then check notna on original
            condition = df[key_column_name].notna() & (df[key_column_name].astype(str).str.strip() != '')
            df_filtered = df[condition]
            filtered_row_count = len(df_filtered)
    
            if filtered_row_count == 0:
                print(f"Sheet '{sheet_name}': No rows found with a value in '{key_column_name}' column (originally {original_row_count} rows). Skipping data from this sheet.")
                sys.stdout.flush()
                continue
    
            print(f"Sheet '{sheet_name}': Kept {filtered_row_count} rows out of {original_row_count} based on '{key_column_name}' column.")
            sys.stdout.flush()
    
            df_to_append = df_filtered.copy()
            df_to_append.insert(0, "DATE", sheet_name)
            all_data_frames.append(df_to_append)
            print(f"Successfully processed and added data from sheet: {sheet_name}")
            sys.stdout.flush()
    
        except Exception as e:
            print(f"Error processing sheet '{sheet_name}' in file '{input_filename}': {e}")
            sys.stdout.flush()
    
    if not all_data_frames:
        print(f"No data was successfully processed and filtered from any sheet in '{input_filename}'. No output file will be generated for this input.")
        sys.stdout.flush()
        return
    
    try:
        combined_df = pd.concat(all_data_frames, ignore_index=True)
        # Strip whitespace from column names for the combined DataFrame as a safeguard
        combined_df.columns = [str(col).strip() for col in combined_df.columns]
        print(f"Normalized column names for combined_df from '{input_filename}'.")
        sys.stdout.flush()
    
    except Exception as e:
        print(f"Error combining dataframes for '{input_filename}': {e}")
        sys.stdout.flush()
        return
    
    if column_to_drop in combined_df.columns:
        try:
            combined_df.drop(columns=[column_to_drop], inplace=True)
            print(f"Successfully dropped column: '{column_to_drop}' from the combined data.")
            sys.stdout.flush()
        except Exception as e:
            print(f"An error occurred while trying to drop column '{column_to_drop}': {e}")
            sys.stdout.flush()
    else:
        print(f"Column '{column_to_drop}' not found in the combined data (available: {combined_df.columns.tolist()}). No column dropped.")
        sys.stdout.flush()
    
    
    pivot_table_df_1_output = None 
    pivot_table_df_2_output = None 
    
    scrap_perct_col_name = "scrap perct" 
    scrap_item_perct_col_name = "Scrap_item_perct"
    
    
    base_filename = os.path.basename(input_filename)
    if "mecatherm".lower() in base_filename.lower():
        print(f"File '{base_filename}' identified as a Mecatherm file. Proceeding with scrap calculations and pivot tables.")
        sys.stdout.flush()
    
    
        cutting_scrap_col = "Cutting scrap"
        other_scrap_col = "Other scrap"
        total_batch_weight_col = "Total Batch Weight (Kg)"
        item_code_col = "ITEM CODE"
        item_desc_col = "ITEM DESCRIPTION"
    
    
        required_cols_for_scrap_calc = [cutting_scrap_col, other_scrap_col, total_batch_weight_col]
        missing_scrap_calc_cols = [col for col in required_cols_for_scrap_calc if col not in combined_df.columns]
    
        if missing_scrap_calc_cols:
            print(f"Warning: For Mecatherm file '{base_filename}', missing columns for '{scrap_perct_col_name}' calculation: {missing_scrap_calc_cols} (available: {combined_df.columns.tolist()}). '{scrap_perct_col_name}' will be 0.")
            sys.stdout.flush()
            combined_df[scrap_perct_col_name] = 0.0 
        else:
            combined_df[cutting_scrap_col] = pd.to_numeric(combined_df[cutting_scrap_col], errors='coerce').fillna(0)
            combined_df[other_scrap_col] = pd.to_numeric(combined_df[other_scrap_col], errors='coerce').fillna(0)
            combined_df[total_batch_weight_col] = pd.to_numeric(combined_df[total_batch_weight_col], errors='coerce')
    
            sum_scraps_row_level = combined_df[cutting_scrap_col] + combined_df[other_scrap_col]
            combined_df[scrap_perct_col_name] = np.where(
                (combined_df[total_batch_weight_col].isna()) | (combined_df[total_batch_weight_col] == 0),
                0.0, 
                sum_scraps_row_level / combined_df[total_batch_weight_col]
            )
            combined_df[scrap_perct_col_name] = combined_df[scrap_perct_col_name].fillna(0.0) 
            print(f"Successfully calculated raw numeric '{scrap_perct_col_name}' column (0-1 range) for '{base_filename}'.")
            sys.stdout.flush()
    
    
        pivot1_row_columns = ['PRODUCTION ORDER', item_code_col, item_desc_col, 'MIXES DETAILS']
        valid_pivot1_row_columns = [col for col in pivot1_row_columns if col in combined_df.columns]
    
        if scrap_perct_col_name not in combined_df.columns:
            print(f"Warning: Numeric '{scrap_perct_col_name}' column not found in '{base_filename}' for Pivot Table 1. Skipping Pivot Table 1 generation.")
            sys.stdout.flush()
        elif not valid_pivot1_row_columns or len(valid_pivot1_row_columns) != len(pivot1_row_columns):
            print(f"Warning: For Pivot Table 1 in '{base_filename}', one or more defined row columns are missing from the data. Expected: {pivot1_row_columns}, Found in data: {valid_pivot1_row_columns}. Skipping Pivot Table 1 generation.")
            sys.stdout.flush()
        else:
            try:
                temp_pivot_table_df_1 = pd.pivot_table(combined_df, index=valid_pivot1_row_columns, values=scrap_perct_col_name, aggfunc='mean', fill_value=0)
                pivot_table_df_1_output = temp_pivot_table_df_1.reset_index()
                
                if scrap_perct_col_name in pivot_table_df_1_output.columns:
                    pivot_table_df_1_output[scrap_perct_col_name] = (pivot_table_df_1_output[scrap_perct_col_name] * 100).round(2)
                    print(f"Converted '{scrap_perct_col_name}' to percentage value in Pivot Table 1 data.")
                    sys.stdout.flush()
                
                print(f"Successfully created data for Pivot Table 1 part of 'Scrap_Pivot' sheet for '{base_filename}'.")
                sys.stdout.flush()
            except Exception as e:
                print(f"Error creating or converting data for Pivot Table 1 part of 'Scrap_Pivot' sheet for '{base_filename}': {e}")
                sys.stdout.flush()
                pivot_table_df_1_output = None
    
    
        # --- Create Pivot Table 2 (Item level sums for Scrap_item_perct) for the second part of "Scrap_Pivot" sheet ---
        pivot2_row_columns = [item_code_col, item_desc_col]
        pivot2_value_columns = [total_batch_weight_col, cutting_scrap_col, other_scrap_col]
        
        valid_pivot2_row_columns = [col for col in pivot2_row_columns if col in combined_df.columns]
        valid_pivot2_value_columns = [col for col in pivot2_value_columns if col in combined_df.columns]
    
        if len(valid_pivot2_row_columns) != len(pivot2_row_columns) or len(valid_pivot2_value_columns) != len(pivot2_value_columns):
            print(f"Warning: For Pivot Table 2 in '{base_filename}', missing columns. Expected rows: {pivot2_row_columns} (found: {valid_pivot2_row_columns}), Expected values: {pivot2_value_columns} (found: {valid_pivot2_value_columns}). Skipping Pivot Table 2 generation.")
            sys.stdout.flush()
        else:
            try:
                temp_pivot_table_df_2 = pd.pivot_table(combined_df, index=valid_pivot2_row_columns, values=valid_pivot2_value_columns, aggfunc='sum', fill_value=0)
                
                sum_cutting_scrap = temp_pivot_table_df_2[cutting_scrap_col]
                sum_other_scrap = temp_pivot_table_df_2[other_scrap_col]
                sum_total_batch_weight = temp_pivot_table_df_2[total_batch_weight_col]
    
                temp_pivot_table_df_2[scrap_item_perct_col_name] = np.where(
                    sum_total_batch_weight == 0, 0.0, (sum_cutting_scrap + sum_other_scrap) / sum_total_batch_weight
                )
                temp_pivot_table_df_2[scrap_item_perct_col_name] = temp_pivot_table_df_2[scrap_item_perct_col_name].fillna(0.0)
                
                if scrap_item_perct_col_name in temp_pivot_table_df_2.columns:
                    temp_pivot_table_df_2[scrap_item_perct_col_name] = (temp_pivot_table_df_2[scrap_item_perct_col_name] * 100).round(2)
                    print(f"Converted '{scrap_item_perct_col_name}' to percentage value in Pivot Table 2 data.")
                    sys.stdout.flush()
    
                pivot_table_df_2_output = temp_pivot_table_df_2.reset_index()[[item_code_col, item_desc_col, scrap_item_perct_col_name]]
                print(f"Successfully created data for Pivot Table 2 part of 'Scrap_Pivot' sheet for '{base_filename}'.")
                sys.stdout.flush()
    
            except Exception as e:
                print(f"Error creating or converting data for Pivot Table 2 part of 'Scrap_Pivot' sheet for '{base_filename}': {e}")
                sys.stdout.flush()
                pivot_table_df_2_output = None
        
        if pivot_table_df_1_output is None and pivot_table_df_2_output is None and ("mecatherm".lower() in base_filename.lower()):
            print(f"Neither part of the 'Scrap_Pivot' sheet could be prepared for Mecatherm file '{base_filename}'.")
            sys.stdout.flush()
    
    else: # Not a Mecatherm file
        print(f"File '{base_filename}' is not a Mecatherm file. Skipping scrap calculations and pivot tables.")
        sys.stdout.flush()
        if scrap_perct_col_name in combined_df.columns:
            is_numeric_and_small = pd.api.types.is_numeric_dtype(combined_df[scrap_perct_col_name]) and \
                                   (combined_df[scrap_perct_col_name].max() if combined_df[scrap_perct_col_name].notna().any() else 0) < 2 
            if not is_numeric_and_small:
                print(f"Note: Potentially modified '{scrap_perct_col_name}' column was found in non-Mecatherm file '{base_filename}'. This is unexpected.")
                sys.stdout.flush()
    
    
    # --- Saving to Excel ---
    base_output_name, ext = os.path.splitext(input_filename)
    output_filename = f"{base_output_name}_clubbed{ext}"
    
    try:
        with pd.ExcelWriter(output_filename, engine='openpyxl') as writer:
            # Write Clubbed_Data sheet
            combined_df.to_excel(writer, index=False, sheet_name="Clubbed_Data")
            print(f"Successfully wrote 'Clubbed_Data' sheet to {output_filename}")
            sys.stdout.flush()
            # Auto-adjust columns for Clubbed_Data sheet
            worksheet_clubbed = writer.sheets["Clubbed_Data"]
            auto_adjust_column_width(worksheet_clubbed, combined_df)
            print(f"Auto-adjusted column widths for 'Clubbed_Data' sheet.")
            sys.stdout.flush()
    
    
            # Write the two parts to "Scrap_Pivot" sheet if they exist
            sheet_written_scrap_pivot = False
            if pivot_table_df_1_output is not None:
                pivot_table_df_1_output.to_excel(writer, sheet_name="Scrap_Pivot", index=False, startcol=0) 
                print(f"Successfully wrote Pivot Table 1 data to 'Scrap_Pivot' sheet (cols A-...) for {output_filename}")
                sys.stdout.flush()
                # Auto-adjust columns for the first part of Scrap_Pivot
                worksheet_scrap_pivot = writer.sheets["Scrap_Pivot"]
                auto_adjust_column_width(worksheet_scrap_pivot, pivot_table_df_1_output, start_col_offset=0)
                print(f"Auto-adjusted column widths for Pivot Table 1 part of 'Scrap_Pivot' sheet.")
                sys.stdout.flush()
                sheet_written_scrap_pivot = True
            
            if pivot_table_df_2_output is not None:
                pivot_table_df_2_output.to_excel(writer, sheet_name="Scrap_Pivot", index=False, startcol=6) # Start at column G (0-indexed 6)
                print(f"Successfully wrote Pivot Table 2 data to 'Scrap_Pivot' sheet (cols G-I) for {output_filename}")
                sys.stdout.flush()
                # Auto-adjust columns for the second part of Scrap_Pivot
                # If the sheet was already created by pivot_table_df_1_output, we use the existing worksheet object
                if "Scrap_Pivot" not in writer.sheets: # Should not happen if pivot_table_df_1_output was written
                    print("Error: 'Scrap_Pivot' sheet not found for adjusting Pivot Table 2 columns.")
                    sys.stdout.flush()
                else:
                    worksheet_scrap_pivot = writer.sheets["Scrap_Pivot"]
                    auto_adjust_column_width(worksheet_scrap_pivot, pivot_table_df_2_output, start_col_offset=6)
                    print(f"Auto-adjusted column widths for Pivot Table 2 part of 'Scrap_Pivot' sheet.")
                    sys.stdout.flush()
                sheet_written_scrap_pivot = True # Mark as written even if only this part is written
            
            if not sheet_written_scrap_pivot and "mecatherm".lower() in base_filename.lower(): 
                print(f"'Scrap_Pivot' sheet was not generated because no pivot data was available for Mecatherm file '{base_filename}'.")
                sys.stdout.flush()
        
        print(f"Successfully created/updated the output file: {output_filename}")
        sys.stdout.flush()
    
    except Exception as e:
        print(f"Error saving the Excel file '{output_filename}' or adjusting column widths: {e}")
        sys.stdout.flush()
    

    if name == "main": if getattr(sys, 'frozen', False): application_path = os.path.dirname(sys.executable) elif file: application_path = os.path.dirname(os.path.abspath(file)) else: application_path = os.getcwd()

    print(f"Scanning for .xlsx files in directory: {application_path}")
    sys.stdout.flush() # Crucial for first output in frozen app
    
    excel_files = glob.glob(os.path.join(application_path, "*.xlsx"))
    processed_count = 0
    
    if not excel_files:
        print("No .xlsx files found in the application's directory.")
        sys.stdout.flush()
    else:
        excel_files.sort() 
        print(f"Found {len(excel_files)} .xlsx file(s).")
        sys.stdout.flush()
    
        files_to_process = []
        for file_path in excel_files:
            filename = os.path.basename(file_path)
            if "_clubbed.xlsx" in filename.lower():
                print(f"Identified as an output file, skipping: {filename}")
                sys.stdout.flush()
                continue
            if filename.startswith("~$"): 
                print(f"Skipping temporary Excel file: {filename}")
                sys.stdout.flush()
                continue
            files_to_process.append(file_path)
    
        if not files_to_process:
            print("No suitable .xlsx files to process after filtering out temporary/output files.")
            sys.stdout.flush()
        else:
            print(f"Files to be processed: {[os.path.basename(f) for f in files_to_process]}")
            sys.stdout.flush()
            for file_path in files_to_process:
                process_excel_file(file_path)
                processed_count += 1
    
    if processed_count > 0:
        print(f"\n--- Finished processing {processed_count} file(s). ---")
        sys.stdout.flush()
    elif not excel_files:
        pass # Message already printed
    elif not files_to_process:
        pass # Message already printed
    else:
        print("\n--- No new files were processed. ---")
        sys.stdout.flush()
    
    if getattr(sys, 'frozen', False):
        print("Processing complete. Press Enter to exit...") # This print should also be flushed if it were not followed by input()
        sys.stdout.flush() # Good practice, though input() might flush implicitly.
        input() # No need to flush after input(), as it waits for user interaction.
    

    WHAT DO THIS CODE DO?

  2. Meta Assistant

    Overview

    This code is a Python script that processes Excel files (.xlsx) in a specified directory. It appears to be designed for data analysis and reporting, specifically for files related to production orders and scrap calculations.

    Main Functionality

    The script performs the following tasks:

    1. File Selection: It scans the directory for .xlsx files, excluding temporary files and files with "_clubbed.xlsx" in their name (which are likely output files generated by this script).
    2. Data Processing: For each selected file, it:
      • Reads the Excel file using pd.ExcelFile.
      • Extracts data from each sheet, filtering out empty sheets and sheets without a specific column ("PRODUCTION ORDER").
      • Combines data from all sheets into a single DataFrame.
      • Drops a specific column ("S.No") if present.
    3. Scrap Calculations: If the file is identified as a "Mecatherm" file (based on the file name), it:
      • Calculates scrap percentages for each row.
      • Creates two pivot tables:
        • Pivot Table 1: averages scrap percentages by production order, item code, and item description.
        • Pivot Table 2: sums scrap quantities by item code and item description.
    4. Output: The script writes the processed data to a new Excel file with the same name as the input file, but with "_clubbed.xlsx" appended to the end. The output file contains two sheets:
      • "Clubbed_Data": the combined data from all sheets.
      • "Scrap_Pivot": the two pivot tables (if generated).

    Additional Features

    • The script auto-adjusts column widths in the output Excel file to fit the data.
    • It handles errors and exceptions during file processing and output generation, printing error messages to the console.
    • If the script is run as a frozen executable, it waits for the user to press Enter before exiting.

    Context

    The script appears to be designed for a specific industry or company, likely in the manufacturing sector, where production orders and scrap calculations are relevant. The "Mecatherm" file identification suggests that the script is tailored for files from a specific system or software.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.