import pandas as pd import os import glob import sys import numpy as np openpyxl is used…
- 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_widthdef 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?
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:
- 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).
- 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.
- Reads the Excel file using
- 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.
- 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.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.