Remove unwanted parts from strings in Dataframe-CodePudding

I am looking for an efficient way to remove unwanted parts from strings in a DataFrame column.

My dataframe:

     Passengers

1    Sally Muller, President, Mark Smith, Vicepresident, John Doe, Chief of Staff
2    Sally Muller, President, Mark Smith, Vicepresident
3    Sally Muller, President, Mark Smith, Vicepresident, John Doe, Chief of Staff
4    Mark Smith, Vicepresident, John Doe, Chief of Staff, Peter Parker, Special Effects
5    Sally Muller, President, John Doe, Chief of Staff, Peter Parker, Special Effects, Lydia Johnson, Vice Chief of Staff
...

desired form of df:

     Passengers

1    Sally Muller, Mark Smith, John Doe
2    Sally Muller, Mark Smith
3    Sally Muller, Mark Smith, John Doe
4    Mark Smith, John Doe, Peter Parker
5    Sally Muller, John Doe, Peter Parker, Lydia Johnson
...

Up to now I did it with endless handmade copy/paste regex list:

df = df.replace(r'President,','', regex=True) 
df = df.replace(r'Vicepresident,','', regex=True) 
df = df.replace(r'Chief of Staff,','', regex=True) 
df = df.replace(r'Special Effects,','', regex=True) 
df = df.replace(r'Vice Chief of Staff,','', regex=True) 
...

Is there a more comfortable way to do this?

Edit

More accurate example of original df:

     Passengers

1    Sally Muller, President, EL Mark Smith, John Doe, Chief of Staff,  Peter Gordon, Director of Central Command
2    Sally Muller, President, EL Mark Smith, Vicepresident
3    Sally Muller, President, EL Mark Smith, Vicepresident, John Doe, Chief of Staff, Peter Gordon, Dir CC 
4    Mark Smith, Vicepresident, John Doe, Chief of Staff, Peter Parker, Special Effects
5    President Sally Muller, John Doe Chief of Staff, Peter Parker, Special Effects, Lydia Johnson , Vice Chief of Staff
...

desired form of df:

     Passengers

1    Sally Muller, Mark Smith, John Doe, Peter Gordon
2    Sally Muller, Mark Smith
3    Sally Muller, Mark Smith, John Doe, Peter Gordon
4    Mark Smith, John Doe, Peter Parker
5    Sally Muller, John Doe, Peter Parker, Lydia Johnson
...

Up to now I did it with endless handmade copy/paste regex list:

df = df.replace(r'President','', regex=True) 
df = df.replace(r'Director of Central Command,','', regex=True) 
df = df.replace(r'Dir CC','', regex=True) 
df = df.replace(r'Vicepresident','', regex=True) 
df = df.replace(r'Chief of Staff','', regex=True) 
df = df.replace(r'Special Effects','', regex=True) 
df = df.replace(r'Vice Chief of Staff','', regex=True) 
...

messy output is like:

     Passengers

1    Sally Muller, , Mark Smith, John Doe, , Peter Gordon,
2    Sally Muller, Mark Smith,
3    Sally Muller, Mark Smith,, John Doe, Peter Gordon
4    Mark Smith, John Doe, Peter Parker
5    Sally Muller,, John Doe, Peter Parker , Lydia Johnson,
...

CodePudding user response：

If every passenger has their title, then you can use str.split explode, then select every second item starting from the first item, then groupby the index and join back:

out = df['Passengers'].str.split(',').explode()[::2].groupby(level=0).agg(', '.join)

or str.split explode and apply a lambda that does the selection join

out = df['Passengers'].str.split(',').apply(lambda x: ', '.join(x[::2]))

Output:

0                 Sally Muller,  Mark Smith,  John Doe
1                            Sally Muller,  Mark Smith
2                 Sally Muller,  Mark Smith,  John Doe
3                 Mark Smith,  John Doe,  Peter Parker
4    Sally Muller,  John Doe,  Peter Parker,  Lydia...

Edit:

If not everyone has a title, then you can create a set of titles and split and filter out the titles. If the order of the names don't matter in each row, then you can use set difference and cast each set to a list in a list comprehension:

titles = {'President', 'Vicepresident', 'Chief of Staff', 'Special Effects', 'Vice Chief of Staff'}

out = pd.Series([list(set(x.split(', ')) - titles) for x in df['Passengers']])

If order matters, then you can use a nested list comprehension:

out = pd.Series([[i for i in x.split(', ') if i not in titles] for x in df['Passengers']])

CodePudding user response：

This is one case where apply is actually faster that explode:

df2 = df['Passengers'].apply(lambda x: ', '.join(x.split(', ')[::2])) #.to_frame() # if dataframe needed

output:

                                          Passengers
0                 Sally Muller, Mark Smith, John Doe
1                           Sally Muller, Mark Smith
2                 Sally Muller, Mark Smith, John Doe
3                 Mark Smith, John Doe, Peter Parker
4  Sally Muller, John Doe, Peter Parker, Lydia Jo...

CodePudding user response：

We can create a full regex pattern match on every string you need to remove and replace.

This can handle situations were the passengers will not have a title.

df2 = df['Passengers'].str.replace("(President)|(Vicepresident)|(Chief of Staff)|(Special Effects)|(Vice Chief of Staff)", "",regex=True).replace("( ,)", "", regex=True).str.strip().str.rstrip(",")