Clean up a pandas column using regex I have a Python Pandas dataframe with a column named &quot;codes&quot;. This column is very messy and contains the following values:
codes                       output
1234A, C (1AAA1), D, E      1234A 1234C 1234D 1234E
34240 &amp; 1 &amp; 3               34240 34241 34243
PS1A;D;E                    PS1A PS1D PS1E
CE11A, C &amp; 2452D            CE11A CD11C 2352D
EOPSDCE11A, C &amp; 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!
Would something like the following work?
import re
from typing import List

import pandas as pd

df = pd.DataFrame(
    {
        &quot;codes&quot;: [
            &quot;1234A, C (1AAA1), D, E&quot;,
            &quot;34240 &amp; 1 &amp; 3&quot;,
            &quot;PS1A;D;E&quot;,
            &quot;CE11A, C &amp; 2452D&quot;,
            &quot;EOPSDCE11A, C &amp; 2452D&quot;,
        ]
    }
)


def complete_codes(codes: List) -&gt; List:
    line_code = codes[0][0:-1]
    res = []
    for code in codes:
        if len(code) &lt; 2:
            res.append(line_code + code)
        else:
            res.append(code)
    return res


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


df[&quot;output&quot;] = df.codes.apply(clean_up)
print(df)

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