Excel
Converting Excel to JSON With Python for Clean Output
Convert an Excel sheet to JSON in Python: records, nested groups and JSON Lines, with typed values, ISO dates, nulls and a check that nothing was lost.
6 min read
Tag
python
134 posts
CSV
Standardizing Phone Numbers in a CSV File With Python
Normalize phone numbers to E.164 with a regex and the phonenumbers library; the regex accepted 43.8% more numbers than the library calls valid.
8 min read
CSV
Handling Missing Values in a CSV File Without Guessing
A CSV has no NULL, so missing arrives as empty, a dash, N/A or null. Inventory the tokens, convert to real NA, keep the reason and see what fillna(0) does.
8 min read
CSV
Turning Currency Strings Into Numbers in a CSV Exactly
Parse $1,234.50, (12.50), 1.234,50 € and 7,00 into exact Decimals, find the cases where stripping symbols is wrong, and spot a column that mixes currencies.
8 min read
CSV
Fixing Mixed Date Formats in a CSV Column Without Guessing
Four formats in one date column, 959 values that are not dates and 15,642 that could be read two ways. Parse with explicit formats and report what you cannot.
9 min read
JSON
Using DynamoDB Maps and Lists as JSON Documents Safely
Store JSON in DynamoDB map and list attributes, update nested paths in place and avoid float, reserved-word and size errors, tested on DynamoDB Local.
7 min read
CSV
Removing Duplicate Rows From a CSV and Choosing the Keeper
Deduplicating a CSV is easy; deciding which duplicate to keep is not. Compare first, last, latest-signup and highest-balance keepers on 200,000 rows.
9 min read
CSV
Trimming Whitespace in Every Field of a CSV Safely
Trim padded CSV fields with pandas, the csv module, DuckDB and qsv, and see why strip() misses the zero-width space that still breaks an email match.
9 min read
Editor's pick
CSVGuide
A Repeatable Way to Clean a Messy CSV File With Python
A cleaning pipeline that never edits the raw file, counts rows at every step and keeps rejects: 200,000 rows in 3.85 s, identical output twice.
25 min read
YAML
Flattening Nested YAML Into Rows of Path and Value
Turn a nested YAML document into path, type and value rows with a short recursive generator, then load the rows into SQL to search and compare configs.
6 min read
YAML
Reading YAML Into SQLite for Quick Queries With json_extract
Load a YAML list into an in-memory SQLite table as JSON text and query it with json_extract and json_each, with a json_valid check that rejects bad rows.
6 min read
YAML
Fixtures in YAML for Database Tests With pytest and Rollback
Keep test data in YAML files, load it inside a transaction per test and roll back after, with pytest and PostgreSQL. Three tests ran in 0.13 seconds.
7 min read