Split Columns in Pandas

user_9270664 0
Viewed 58

So I have a pandas column that looks like this:

full_name = pd.Series([
    'Reservoir 1 Compartment 1', 
    'Reservoir 1 Common Inlet', 
    'Reservoir 2 Compartment 1',
    'Vyrnwy Line 2 Balancing Tank 1', 
    'Reservoir 1'
])

I am trying to split it into two columns. The expected output should look like this:

[['Reservoir 1', 'Compartment 1'],
 ['Reservoir 1', 'Common Inlet'],
 ['Reservoir 2', 'Compartment 1'],
 ['Vyrnwy Line 2', 'Balancing Tank 1'],
 ['Reservoir 1', None]]

I have tried this:

res_compartment_split = pd.concat([full_name.str.split(r'\s\s*?(?=[A-Z])', expand=True)])

but I get this output

[['Reservoir 1', 'Compartment 1', None, None],
 ['Reservoir 1', 'Common', 'Inlet', None],
 ['Reservoir 2', 'Compartment 1', None, None],
 ['Vyrnwy', 'Line 2', 'Balancing', 'Tank 1'],
 ['Reservoir 1', None, None, None]]

Thanks for any help.

pandas python split regex
3 Answers

Try the following:

import pandas as pd

full_name = pd.Series([
    'Reservoir 1 Compartment 1', 
    'Reservoir 1 Common Inlet', 
    'Reservoir 2 Compartment 1',
    'Vyrnwy Line 2 Balancing Tank 1', 
    'Reservoir 1'
])

res = full_name.str.split('(?<=\d)\s+(?=[A-Z])', expand=True)

Output:

>>> res
               0                 1
0    Reservoir 1     Compartment 1
1    Reservoir 1      Common Inlet
2    Reservoir 2     Compartment 1
3  Vyrnwy Line 2  Balancing Tank 1
4    Reservoir 1              None

Explanation of the regex pattern:

  • (?<=\d) - positive lookbehind: ensures that there is a digit right before the separator, without consuming it
  • \s+ - separator: matches one or more whitespace
  • (?=[A-Z]) - positive lookahead: ensures that there is a letter (A to Z) right after, without consuming it

See it in action using regex101.com.

Also, you can see here why your pattern doesn't work: https://regex101.com/r/nSmEEs/1 .

user_171206920

You can extract strings that consist of any non-digits ending with one or more digits or end of string:

full_name.str.findall(r"\s*(\D+(?:\d+|$))")

Details:

  • \s* - zero or more whitespaces
  • ( - start of a capturing group:
    • \D+ - one or more non-digits
    • (?:\d+|$) - one or more digits or end of string.
  • ) - end of the group.

See the regex demo.

user_38329700

In your case the split point can be a space, preceded with a digit.

To match the preceding digit use positive lookbehind:

result = full_name.str.split(r'(?<=\d) ', expand=True)

Then you can set names to columns, e.g.:

result.columns = ['Res', 'Comp']

The result is:

             Res              Comp
0    Reservoir 1     Compartment 1
1    Reservoir 1      Common Inlet
2    Reservoir 2     Compartment 1
3  Vyrnwy Line 2  Balancing Tank 1
4    Reservoir 1              None
user_73884770
Related