Extract multiple values from a string via regex

I have a number of strings from a 3rd party data source that are in various lengths that contain both underscore and spaces. Each portion of the string is important and I am trying to break it apart into various fields via python. The string does not have special characters (\n, \t, etc.) – should just be spaces, underscores, and parentheses are used to break the data parts.

I could split the string by the underscore, then split the last field by a space but that seems error-prone. Regex is likely the best approach.

I can match the year using this: ^[1-9]\d{3,}$. However, when trying to add an OR operator, it will only find the underscore.

Is there a way to extract this data when I know a pattern exists?

>Solution :

You could try using str.extract with the regex pattern:

^(\d{4})_([^_]+)_([^_]+)_([^_ ]+)(?: \((\S+)(?: (\S+))?\))?$

Note that this pattern assumes that there would only be three variants in the string column, namely no extras, one extra, or at most two extras. For arbitrary number of words in parentheses, we would need a different approach.

Python script:

df[["Year", "State", "ID", "Sub ID", "Extra1", "Extra2"]] = df["String"].str.extract(r'^(\d{4})_([^_]+)_([^_]+)_([^_ ]+)(?: \((\S+)(?: (\S+))?\))?$')

Here is a regex demo showing that the pattern is working for all variants of your string column.