Python Combining Names With Missing Values

January 29, 2024 Post a Comment

I have a pandas dataframe of names as shown. data = {'firstname': {0: 'Sassa', 1: 'Jennifer', 2: 'Jennifer', 3: 'Jennifer', 4: 'Vanessa', 5: 'Alexander', 6: 'Alexander', 7: 'Alexan

Solution 1:

Do you know about Python's string functions? you can combine these with pandas methods.

lets use stack along the index to remove any NaN values.

we can use groupby and ' '.join to create your full name column

df['PersonName' ] = df.stack().groupby(level=0).agg(' '.join)

print(df)

   firstname othername     surname              PersonName
0      Sassa      NaN  Radomirovic       Sassa Radomirovic
1   Jennifer      NaN        Catto          Jennifer Catto
2   Jennifer      NaN        Catto          Jennifer Catto
3   Jennifer      NaN        Catto          Jennifer Catto
4    Vanessa      NaN        Pinho           Vanessa Pinho
5  Alexander   Stuart        Clark  Alexander Stuart Clark
6  Alexander   Stuart        Clark  Alexander Stuart Clark
7  Alexander   Stuart        Clark  Alexander Stuart Clark

another, more verbose method would be to fill your columns and then replace all white space with a single space.

names = (
    df["firstname"] + " " + df["othername"].fillna("") + " " + df["surname"]
).replace("\s+", " ", regex=True)

print(names)

0         Sassa Radomirovic
1            Jennifer Catto
2            Jennifer Catto
3            Jennifer Catto
4             Vanessa Pinho
5    Alexander Stuart Clark
6    Alexander Stuart Clark
7    Alexander Stuart Clark

Solution 2:

I needed same thing at work and had used str.cat() method as shown below. @Datanovice were slightly slower

 (df.firstname.str.cat(df.othername.fillna(''), sep=' ')).str.cat(df.surname, sep=' ').str.replace('  ',' ')



firstname othername      surname              personname
0      Sassa       NaN  Radomirovic      Sassa  Radomirovic
1   Jennifer       NaN        Catto         Jennifer  Catto
2   Jennifer       NaN        Catto         Jennifer  Catto
3   Jennifer       NaN        Catto         Jennifer  Catto
4    Vanessa       NaN        Pinho          Vanessa  Pinho
5  Alexander    Stuart        Clark  Alexander Stuart Clark
6  Alexander    Stuart        Clark  Alexander Stuart Clark
7  Alexander    Stuart        Clark  Alexander Stuart Clark

Timing

@wwnde

 %timeit (df.firstname.str.cat(df.othername.fillna(''), sep=' ')).str.cat(df.surname, sep=' ').str.replace('  ',' ')
    997 µs ± 14.3 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

@Datanovice 1

%timeit df['PersonName' ] = df.stack().groupby(level=0).agg(' '.join)
3.5 ms ± 76.3 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

@Datanovice 1

%timeit df['PersonName' ] = df.stack().groupby(level=0).agg(' '.join)

1.34 ms ± 178 µs per loop (mean ± std. dev. of7 runs, 1000 loops each)

@Scott Boston

Baca Juga

%timeit (df['firstname'].str.cat(df[['othername', 'surname']], sep=' ', na_rep='').replace('\s\s', ' ', regex=True))
1.54 ms ± 133 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

Solution 3:

TRY: (Not a good solution though)

df['name'] =(df['firstname'].fillna('') + ' ' + df['othername'].fillna('')+' ' +df['surname'].fillna('')).str.strip(' ')

df:

    firstname   othername   surname     name
0   Sassa       NaN         Radomirovic Sassa Radomirovic
1   Jennifer    NaN         Catto       Jennifer Catto
2   Jennifer    NaN         Catto       Jennifer Catto
3   Jennifer    NaN         Catto       Jennifer Catto
4   Vanessa     NaN         Pinho       Vanessa Pinho
5   Alexander   Stuart      Clark       Alexander Stuart Clark
6   Alexander   Stuart      Clark       Alexander Stuart Clark
7   Alexander   Stuart      Clark       Alexander Stuart Clark

Solution 4:

I think you can also use, .str.cat like this:

(df['firstname'].str.cat(df[['othername', 'surname']], sep=' ', na_rep='')
                .replace('\s\s', ' ', regex=True))

Output:

0         Sassa Radomirovic
1            Jennifer Catto
2            Jennifer Catto
3            Jennifer Catto
4             Vanessa Pinho
5    Alexander Stuart Clark
6    Alexander Stuart Clark
7    Alexander Stuart Clark
Name: firstname, dtype: object

Adding Timings:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from timeit import timeit

data = {'firstname': {0: 'Sassa', 1: 'Jennifer', 2: 'Jennifer', 3: 'Jennifer', 
                      4: 'Vanessa', 5: 'Alexander', 6: 'Alexander', 7: 'Alexander'}, 
        'othername': {0: np.nan, 1: np.nan, 2: np.nan, 3: np.nan, 4: np.nan, 5: 'Stuart', 6: 'Stuart', 7: 'Stuart'}, 
        'surname': {0: 'Radomirovic', 1: 'Catto', 2: 'Catto', 3: 'Catto', 4: 'Pinho', 5: 'Clark', 6: 'Clark', 7: 'Clark'}}

df = pd.DataFrame(data)

defdn_1(d):
    return d.stack().groupby(level=0).agg(' '.join)
    
defdn_2(d):
    return (d["firstname"] + " " + d["othername"].fillna("") + " " + d["surname"]).replace("\s+", " ", regex=True)

defsb(d):
    return (df['firstname'].str.cat(df[['othername', 'surname']], sep=' ', na_rep='')
                .replace('\s\s', ' ', regex=True))

defww(d):
    return  (df.firstname.str.cat(df.othername.fillna(''), sep=' ')).str.cat(df.surname, sep=' ').str.replace('  ',' ')

#def pg(d):#    return (df['firstname'].fillna('') + ' ' + df['othername'].fillna('')+' ' +df['surname'].fillna('')).str.strip(' ')# Not equivalent result 

res = pd.DataFrame(
    index=[10, 30, 100, 300, 1000, 3000, 10000, 30000],
    columns='dn_1 dn_2 sb ww'.split(),
    dtype=float
)

for i in res.index:
    d = pd.concat([df]*i)
    for j in res.columns:
        stmt = '{}(d)'.format(j)
        setp = 'from __main__ import d, {}'.format(j)
        print(stmt, d.shape)
        res.at[i, j] = timeit(stmt, setp, number=100)

# res.groupby(res.columns.str[4:-1], axis=1).plot(loglog=True);
res.plot(loglog=True);

Chart:

Solution 5:

Try:

df['personname'] = str()
for i, row in df.iterrows():
    row.fillna('na', inplace=True)
    df['personname'][i] = f"{' '.join([name for name in row if name != 'na'])}".strip()