Pandas filling blanks

November 23, 2021

Python 3.9 and Pandas 1.3.4

So here’s the df:

1   First name  Last Name
2   Freddie     Mercury
3   John        Lennon
4   David       Bowie
5   
6   Joseph
7               Jovi

I’m trying to fill the blank line (5) with "John Doe" when I concat First name and Last name but I do not want to put a "John Doe" in line 6 or 7 as it has a partial name.

So this is my current code:

import pandas as pd

df = pd.read_csv('file.csv', dtype=str, header=0)
df['First name'] = df['First name'].str.replace(' ', 'John Doe', regex=True)
df['Last name'] = df['Last name'].str.replace(' ', 'John Doe', regex=True)
df['fullname'] = df['First name'].fillna(" ") + " " + df["Last name"].fillna(" ")


df.to_csv('file.csv', index=False)

This currently produces a fullname column which looks like:

fullname
Freddie Mercury
John Lennon
David Bowie

Joseph
Jovi

This is what I want:

Freddie Mercury
John Lennon
David Bowie
John Doe
Joseph
Jovi

>Solution :

Try:

df['fullname'] = (df[['First name', 'Last Name']]
   .fillna('').agg(' '.join, axis=1)        # replace nan with '' and concatenate
   .str.strip()                             # remove leading/trailing spaces
   .replace('', 'John Doe')                 # replace empty name with default
)

Output:

  First name Last Name         fullname
0    Freddie   Mercury  Freddie Mercury
1       John    Lennon      John Lennon
2      David     Bowie      David Bowie
3       None      None         John Doe
4     Joseph      None           Joseph
5        NaN      Jovi             Jovi