Python remove middle initial from then end of a name string

Question

I am trying to remove the middle initial at the end of a name string. An example of how the data looks:

df = pd.DataFrame({'Name': ['Smith, Jake K',
                            'Howard, Rob',
                            'Smith-Howard, Emily R',
                            'McDonald, Jim T',
                            'McCormick, Erica']})

I am currently using the following code, which works for all names except for McCormick, Erica. I first use regex to identify all capital letters. Then any rows with 3 or more capital letters, I remove [:-1] from the string (in an attempt to remove the middle initial and extra space).

df['Cap_Letters'] = df['Name'].str.findall(r'[A-Z]')
df.loc[df['Cap_Letters'].str.len() >= 3, 'Name'] = df['Name'].str[:-1]

This outputs the following:

As you can see, this properly removes the middle initial for all names except for McCormick, Erica. Reason being she has 3 capital letters but no middle initial, which incorrectly removes the 'a' in Erica.

Answer 1

You can use Series.str.replace directly:

df['Name'] = df['Name'].str.replace(r'\s+[A-Z]$', '', regex=True)

Output:

0            Smith, Jake
1            Howard, Rob
2    Smith-Howard, Emily
3          McDonald, Jim
4       McCormick, Erica
Name: Name, dtype: object

See the regex demo. Regex details:

• \s+ - one or more whitespaces
• [A-Z] - an uppercase letter
• $ - end of string.