Python Automation and Machine Learning for EM and ICs

This Python script (Code) automates the process of merging data between one special CSV file and multiple other CSV files stored in a folder. The special CSV contains the key columns A–E, while the folder CSVs may contain corresponding keys in either the group F–J or K–O, along with an optional column P. The script standardizes and compares the key values across these files, ensuring that rows are matched correctly even if they come from different key groups. Once a match is found, the associated value from column P in the folder CSV is appended into a new P column in the special CSV. If multiple matches exist, the first available non-empty value of P is used. The final result is saved as a new CSV file that preserves all columns of the special CSV and includes the merged P values. This approach ensures consistency in handling multiple input formats and automates a potentially complex and error-prone manual task.

If the folder CSV is missing one or more of those 5 columns, then the script ignores that file completely, because the line "if all(c in cols for c in GROUP_FGHIJ)" requires all 5 columns to exist before proceeding. On the other hand, we can adjust the check to allow fewer columns by replacing "if set(GROUP_FGHIJ).issubset(cols):" with "if len(set(GROUP_FGHIJ).intersection(cols)) >= 4: # requires at least 4 columns " .

This scripts (Code) consolidate all .csv, .xlsx, and .xls files in a folder into one output.csv by building a five-slot canonical key (K1–K5) for every row and then writing those values into the G1 key columns A–E—always—using the first non-empty value among corresponding columns from any group (A/F/M/R/W → A, B/I/N/S/Y → B, C/J/O/T/X → C, D/K/P/U/X → D, E/L/Q/V/Z → E; with X split into two parts for C and D). Rows whose five canonical key slots are fully specified are merged across files on that key (so G1/G2/G3/G4/G5 that represent the same entity collapse into one row), while rows with incomplete keys are appended as unique entries (no row is ever dropped). For all non-key columns, the script coalesces values via “first non-empty wins,” and it ignores Excel-only properties such as hyperlinks. The delimiter used to split X and the precedence of candidate columns can be customized. For G5.X, the script splits the cell on X_SPLIT_SEP (default ","). Note that if different split is needed, e.g. for split with ".", then two code lines can be used to replace the original code in the script: X_SPLIT_SEP = "." and STRIP_SPACE_AROUND_SPLIT = True. The first piece becomes X → K3 and the second piece becomes X → K4 (whitespace optionally stripped). Within each slot, the code picks the first non-empty value in the order shown (e.g., for K1 it tries A, then F, then M, then R, then W). Two rows are merged only if all five slots end up non-empty and identical; that tuple becomes the master key K|K1|K2|K3|K4|K5. Otherwise the row is kept as a unique RAW|file|row (no cross-file merging).

This backfill function in the script (Code) guarantees that no records from a designated “source of truth” file are lost during merging. It scans a specific CSV (INPUT_CSV_1) and uses the hello column as a unique key: any non-empty hello value not already present in the merged dataset is appended verbatim before the final output is written. The routine is resilient to casing and extra whitespace, aligns columns automatically to the merged schema, and deduplicates within INPUT_CSV_1. A single toggle (APPEND_MISSING_FROM_INPUT1) enables or disables the behavior, and a summary message reports how many rows were appended.