Parsing Numbers, Currencies and Dates in Messy Columns
Clean values stored as text.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
Help me parse messy columns. Examples of values in each problem column: [EXAMPLE_VALUES]. Locale and conventions (decimal separators, currency symbols, date order): [LOCALE]. Create a parsing plan per column with the rules, the pandas code using vectorised string methods and to_numeric or to_datetime with errors handled explicitly, and a test list of tricky values (negative numbers in brackets, thousands separators, blank markers, ambiguous dates like 03/04/2025). Show how to log values that could not be parsed rather than silently dropping them, and how to count them. Produce a before-and-after table for the examples I supplied. Add a reusable function design with a test table of at least fifteen tricky inputs and the expected outputs, and a summary report of parse failures by column and reason. Include a short note on keeping the original column next to the parsed one, so that the cleaning can be checked later. Also show how to store the parsing rules in one place so they can be reused on next month's file, and how to review a random sample of twenty parsed values by eye.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.