I have a Python Pandas dataframe with a column named "codes". This column is very messy and contains the following values:

codes                       output
1234A, C (1AAA1), D, E      1234A 1234C 1234D 1234E
34240 & 1 & 3               34240 34241 34243
PS1A;D;E                    PS1A PS1D PS1E
CE11A, C & 2452D            CE11A CD11C 2352D
EOPSDCE11A, C & 2452D       EOPSDCE11A EOPSDCD11C 2352D

Basically, what we need is to complete the single letter/number (i.e. C) with the full code excluding the last char (i.e. 1234 in the first line), remove the phrase in the parenthesis, keep the code if it's a full code (length between 2-10), and use space as delimiter.

I have tried to use str.replace but was not able to manage to write the regex correctly.

Thanks for the help in advance!

  • $\begingroup$ Can you elaborate more? The explanation is a bit short. Can you give more examples? $\endgroup$ – Shahriyar Mammadli Nov 17 '20 at 10:12

Would something like the following work?

import re
from typing import List

import pandas as pd

df = pd.DataFrame(
        "codes": [
            "1234A, C (1AAA1), D, E",
            "34240 & 1 & 3",
            "CE11A, C & 2452D",
            "EOPSDCE11A, C & 2452D",

def complete_codes(codes: List) -> List:
    line_code = codes[0][0:-1]
    res = []
    for code in codes:
        if len(code) < 2:
            res.append(line_code + code)
    return res

def clean_up(text: str) -> str:
    text = re.sub(r"\(.*\)", "", text)  # Remove parenthesis
    multi_split = re.split(r",|;|&", text)  # Split text
    multi_split = [item.strip() for item in multi_split]
    codes = complete_codes(multi_split)
    res = " ".join(codes)
    return res

df["output"] = df.codes.apply(clean_up)

Which outputs:

                    codes                       output
0  1234A, C (1AAA1), D, E      1234A 1234C 1234D 1234E
1           34240 & 1 & 3            34240 34241 34243
2                PS1A;D;E               PS1A PS1D PS1E
3        CE11A, C & 2452D            CE11A CE11C 2452D
4   EOPSDCE11A, C & 2452D  EOPSDCE11A EOPSDCE11C 2452D

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.