Python combining names with missing values

Question

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: 'Alexander'}, 'othername': {0: nan, 1: nan, 2: nan, 3: nan, 4: 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)

print(df)

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

I want to convert these columns to consist of a single-column text name for each person. i.e.

Sasa Radomirovic
Jennifer Catto
Vanessa Pinho
Alexander Stuart Clark

I tried using

personname = df['firstname']+str(' ')+df['othernames'].fillna('')+dfLinks2['surname']
df['personname'] = personname

Problem is, if the person has a middle-name (not NA), this gives no space between middle-name and surname, e.g. Alexander StuartClark. Whereas if I add another str(' ') then those with NA in middle-names end up with two spaces e.g. Jennifer Catto which I don't want.

I also get a SettingWithCopyWarning on the second step.

How should I do this?

@komatiraju032 I said Whereas if I add another str(' ') then those with NA in middle-names end up with two spaces e.g. Jennifer Catto which I don't want. — Mobeus Zoom
– Mobeus Zoom, Commented Jul 1, 2020 at 16:40
You can also work around it by df['PersonName'] = (df["first"]+" "+df["other"].fillna("@")+" "+df["surname"]).str.replace("@ ",""). — Henry Yik
– Henry Yik, Commented Jul 1, 2020 at 16:45
@Mobeus Zoom I provided a much faster method. Just in case you have chunks of data — wwnde
– wwnde, Commented Jul 2, 2020 at 1:16

Umar.H · Accepted Answer · 2020-07-01 16:57:45Z

4

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

edited Jul 1, 2020 at 16:57

answered Jul 1, 2020 at 16:41

Umar.H

23.1k7 gold badges50 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wwnde Over a year ago

@Datanovice good stuff. Found it slightly slower. Did some timing in my answer

wwnde · Accepted Answer · 2020-07-02 13:08:33Z

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. of 7 runs, 1000 loops each)

@Scott Boston

%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)

Pygirl · Accepted Answer · 2020-07-01 16:45:41Z

1

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

answered Jul 1, 2020 at 16:45

Pygirl

13.4k6 gold badges36 silver badges48 bronze badges

1 Comment

wwnde Over a year ago

@ Pygirl I needed same thing at work and had used str.cat() method as shown below. Datanovice methods were slightly slower. See timing below. Apologies for late post

Scott Boston · Accepted Answer · 2020-07-02 17:08:02Z

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)

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

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

def ww(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:

Christian Decker · Accepted Answer · 2020-07-01 17:22:34Z

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()

Output:

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

Collectives™ on Stack Overflow

Python combining names with missing values

5 Answers 5

1 Comment

Comments

1 Comment

Adding Timings:

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

1 Comment

Adding Timings:

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related