String split with exceptions

user_8694768 0
Viewed 601

I am splitting strings to rows by using comma as a delimiter.

for col in [col for col in df.loc[:,df.columns.str.contains(">")]]: #only on colnames containing ">"
    df[col] = df[col].str.split(", ")
    df = df.explode(col).reset_index(drop=True)

However, there are a three substrings where commas are appearing "naturally" and should not cause a split:

  1. Data related to sexual preferences, sex life, and/or sexual orientation
  2. Contract, salary and benefits
  3. Procurement, subcontracting and vendor management

I was thinking since there only are these three instances if there is a way make some exception using something like this: "preferences,", "sex life,", "Contract," and "Procurement,". Or some more elegant way of solving it?

Here is a example df:

df = pd.DataFrame({"col > 1": ["Personals, Financials, Data related to sexual preferences, sex life, and/or sexual orientation", "Personals, Financials", "Vendors, Procurement, subcontracting and vendor management"]})

And here is what it should output:

+-------------------------------------------------------------------------+
|                                 col > 1                                 |
+-------------------------------------------------------------------------+
| Personals                                                               |
| Financials                                                              |
| Data related to sexual preferences, sex life, and/or sexual orientation |
| Personals                                                               |
| Financials                                                              |
| Vendors                                                                 |
| Procurement, subcontracting and vendor management                       |
+-------------------------------------------------------------------------+
pandas python split
2 Answers
  1. You can temporarily replace the commas for those exceptions with something else (let's ay a ;.
  2. Create a list splitting by comma
  3. Explode the dataframe
  4. Replace the semi-colon's with commas
df = pd.DataFrame({"col > 1": ["Personals, Financials, Data related to sexual preferences, sex life, and/or sexual orientation", "Personals, Financials", "Vendors, Procurement, subcontracting and vendor management"]})
r1 = ['Data related to sexual preferences, sex life, and/or sexual orientation',
      'Contract, salary and benefits',
      'Procurement, subcontracting and vendor management']
r2 = ['Data related to sexual preferences; sex life; and/or sexual orientation',
      'Contract; salary and benefits',
      'Procurement; subcontracting and vendor management']
df = df.replace(r1,r2, regex=True)
df['col > 1'] = df['col > 1'].str.split(',')
df = df.explode('col > 1').replace(r2,r1,regex=True)
df
Out[1]: 
                                             col > 1
0                                          Personals
0                                         Financials
0   Data related to sexual preferences, sex life,...
1                                          Personals
1                                         Financials
2                                            Vendors
2   Procurement, subcontracting and vendor manage...
user_63667700

You could use a regex pattern with multiple negative lookbehind statements inside df.str.split() to essentially say "Split rows on , except in cases in which , is preceded by...".

To achieve this in Python it would probably be best to use multiple negative lookbehind assertions - Python regex enforces fixed width lookarounds so it's not quite as simple as a single negative lookbehind with clauses separated by |.

Using the phrases from your example to split on , unless preceded by any of the listed phrases you could use:

r"(?<!preferences)(?<!sex life)(?<!Contract)(?<!Procurement),"

Full code example:

import pandas as pd

df = pd.DataFrame({"col > 1": ["Personals, Financials, Data related to sexual preferences, sex life, and/or sexual orientation", "Personals, Financials", "Vendors, Procurement, subcontracting and vendor management"]})

df["col > 1"] = df["col > 1"].str.split(r"(?<!preferences)(?<!sex life)(?<!Contract)(?<!Procurement),")

df = df.explode("col > 1").reset_index(drop=True)

This will give you df with the desired ["col > 1"] values as outlined in your question a fresh index 0...n.

i.e

                                             col > 1
0                                          Personals
1                                         Financials
2   Data related to sexual preferences, sex life,...
3                                          Personals
4                                         Financials
5                                            Vendors
6   Procurement, subcontracting and vendor manage...
user_120459070
Related