In Python, rearrange order of words in a dataframe when condition is met?

Question

I have this dataframe df:

        Addressee          Title   FirstName   LastName  SpTitle  SpFirstName  SpLastName
0  John Doe and Jane Doe    Mr.      John        Doe       Mrs.     Jane         Doe
1  Jane Doe and John Doe    Mrs.     Jane        Doe       Mr.      John         Doe

My data is over 50k rows, with unknown names. Addressee has different variations to consider.

     Addressee        Title   FirstName   LastName  SpTitle  SpFirstName  SpLastName
0  John and Jane       Mr.      John        Doe       Mrs.     Jane         Doe
1  Jane and John       Mrs.     Jane        Doe       Mr.      John         Doe
2  Mrs. Jane E. Doe    Mrs.     Jane        Doe       NaN       NaN         NaN
3  Jane Doe            Mrs.     Jane        Doe       NaN       NaN         NaN
4  John Michael Doe    Mr.      John        Doe       NaN       NaN         NaN

When Addressee has Mrs. first in Addressee, I want to swap John and Jane around, then mark the row as changed by adding a 1 at the end of the row.

        Addressee          Title   FirstName   LastName  SpTitle  SpFirstName  SpLastName Rearrange
0  John Doe and Jane Doe    Mr.      John        Doe       Mrs.     Jane         Doe
1  John Doe and Jane Doe    Mrs.     Jane        Doe       Mr.      John         Doe           1
2  Mrs. Jane E. Doe         Mrs.     Jane        Doe       NaN       NaN         NaN
3  Jane Doe                 Mrs.     Jane        Doe       NaN       NaN         NaN
4  John Michael Doe         Mr.      John        Doe       NaN       NaN         NaN

Here is what I have so far:

import pandas as pd 
import numpy as np

# Read csv
df= pd.read_csv(csv_file, encoding='latin-1')

# Add column to keep track of what was changed. 
df['Rearrange']= ''
df= df.replace(np.nan,'')
df.insert(0, 'ID', range(0, len(df)))

# Code I cannot figure out
####################### Pseudo code ##########################
#
# data_1 = If title == [(Mrs.|Ms.|Miss) & (FirstName comes first in Addressee)]:
#            df['Addressee'] == SpFirstName + SpLastName + 'and' + FirstName + LastName
#
####################### Pseudo code ##########################

# If the row is rearranged, keep track and add it to the list. 
    ids = df.index.tolist()
    for i in ids:
      df.at[i,'Rearrange']+=', 1'

    # Total rows changed
    changed = df[(df['Rearrange'] != '')]
    unchanged = df[(df['Rearrange'] == '')]
    changed['Rearrange'] = changed['Rearrange'].str[1:]
    changed = changed[(changed['Rearrange'] != '')]

    # Clean up
    del changed["ID"]
    del unchanged["ID"]

    # Print results 
    changed['Rearrange'].value_counts()
    print("There was a total of",data.shape[0], "rows.", "There were" ,data.shape[0] - changed.shape[0], " rows unchanged" ,changed.shape[0], "rows rearranged")

I have attempted to add something like the following, but it's not working quite right, and I cannot figure out how to do this, only if a condition is met:

df['Rearrange '] = [' '.join(s.split()[::-1]) for s in df['Addressee']]

How can I rearrange words to a specific order when a condition is met?

user_10035985 · Answer

Try:

mask = df["Title"].eq("Mrs.")

df.loc[mask, "Addressee"] = df[mask].apply(
    lambda x: f"{x['SpFirstName']} {x['SpLastName']} and {x['FirstName']} {x['LastName']}",
    axis=1,
)
df.loc[mask, "Rearrange"] = 1

print(df)

Prints:

               Addressee Title FirstName LastName SpTitle SpFirstName SpLastName  Rearrange
0  John Doe and Jane Doe   Mr.      John      Doe    Mrs.        Jane        Doe        NaN
1  John Doe and Jane Doe  Mrs.      Jane      Doe     Mr.        John        Doe        1.0

EDIT: Updated answer with new input:

mask = df["Title"].eq("Mrs.") & df.notna().all(axis=1)

df.loc[mask, "Addressee"] = df[mask].apply(
    lambda x: f"{x['SpFirstName']} {x['SpLastName']} and {x['FirstName']} {x['LastName']}",
    axis=1,
)
df.loc[mask, "Rearrange"] = 1

print(df)

Prints:

               Addressee Title FirstName LastName SpTitle SpFirstName SpLastName  Rearrange
0          John and Jane   Mr.      John      Doe    Mrs.        Jane        Doe        NaN
1  John Doe and Jane Doe  Mrs.      Jane      Doe     Mr.        John        Doe        1.0
2       Mrs. Jane E. Doe  Mrs.      Jane      Doe     NaN         NaN        NaN        NaN
3               Jane Doe  Mrs.      Jane      Doe     NaN         NaN        NaN        NaN
4       John Michael Doe   Mr.      John      Doe     NaN         NaN        NaN        NaN

In Python, rearrange order of words in a dataframe when condition is met?

1 Answers