outer-join.py

Outer-joins delimited files (e.g. CSV) on a shared ID column.

Requirements

• Python 3.9+
• No external dependencies; standard library only

Usage

Supply one or more delimited input files to be joined. Optionally change the delimiter from the default "," with -d; change the ID column from the default "Record_ID" with -id or change the output file from the default "output.csv" with -o.

usage: outer-join.py [-h] [-d DELIMITER] [-id ID_COLUMN] [-o OUTPUT] inputs [inputs ...]

$ python outer-join.py sample-data/*
INFO     Parsed 'sample-data/large_file_1.csv'
INFO     Parsed 'sample-data/large_file_2.csv'
INFO     Parsed 'sample-data/large_file_3.csv'
INFO     Parsed 'sample-data/large_file_4.csv'
INFO     Parsed 'sample-data/large_file_5.csv'
INFO     Joined 5 tables
INFO     Wrote to 'output.csv'
INFO     Completed in 4.198200s

Behaviour

• Performs a full outer join: the output contains one row for every ID that appears in any input file. If a given file doesn't have a row for that ID, its columns are filled with empty strigns ("") for that row.
• Every input file must have a column matching --id-column (Record_ID by default); all files must use the same ID column name.
• Output columns are ordered: the ID column first, followed by each input file's remaining columns, in the order the files were listed on input.
• All output fields are wrapped in double quotes, regardless of whether the corresponding input field was quoted.
• Duplicate IDs within a single file are handles as follows: if the duplicate row is identical to the first, it's skipped and logging (INFO); if it conflicts (same ID, different field values), the script raises an error and stops.

Limitations

• Field parsing assumes double quotes only appear at field boundaries. Escaped quotes inside a quoted field are not supported.
• Input files are assumed to be UTF-8 encoded.
• All parsed data is held in memory before writing, so peak memory usage scales with the combined size of the input files.

Notes

Runtime could be roughly halved by using the built-in split method for strings, but I considered this inappropriate for the exercise, instead opting for custom parsing. split wouldn't take into account quoted delimiters and would then produce incorrect output if such an occurence was present.