Clean messy text columns

Pandas data engineering interview problem. Difficulty: beginner. Pattern: String Functions. About 12 minutes. Free to practice.

Trim, lowercase and collapse spaces in names, and pull a numeric code out of a reference string. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

Text from real systems is messy. Names can have extra spaces or tabs, and a reference code can be hidden inside a longer text. df has id, name and ref (a reference such as INV-0042). Return two cleaned columns. clean_name is the name with the spaces at the start and end removed, any run of spaces or tabs inside collapsed to a single space, and everything in lowercase. code is the first run of digits found in ref, as a number (INV-0042 gives 42), or missing when ref has no digits. A missing name stays missing. Return id, clean_name and code, keep the row order, and reset the index. The editor builds df and ends with result = df. Change the last line. Use str.strip, str.replace with a regex, str.lower and str.extract.

Requirements

• Strip the spaces at both ends of name.
• Collapse any run of whitespace (including tabs) inside name to one space, then lowercase it.
• Extract the first run of digits from ref with str.extract(r"(\d+)") and convert it to a number.
• Return id, clean_name, code, in the original order, with a clean index, in result.

Constraints

• A missing name stays missing. It must not become an empty text.
• code is the first group of digits only. A7B912 gives 7, not 912.
• A ref with no digits gives a missing code.

Examples

Input: df id | name | ref 1 | Alice SMITH | INV-0042 2 | bob Jones | no code 3 | NULL | A7B912 4 | CY | X-100 Output: id | clean_name | code 1 | alice smith | 42 2 | bob jones | NULL 3 | NULL | 7 4 | cy | 100 Alice Smith loses the padding and the repeated spaces; the tab in bob\tJones counts as whitespace. A7B912 yields the first digit run, 7. The reference without digits has no code.

Topics: lakebench, pandas, str accessor, regex, cleaning.

More interview problems · All interview problems · Learn data engineering