Replace one column with another except another is NaN in Pandas

user_8410477 0
Viewed 250

Assuming the test dataset as follows:

   id   name1  name2
0   1   james  James
1   2    Bond    NaN
2   3  Steven    NaN
3   4     Tom  Kevin
4   5    Alba    Tim

How could I replace name1's values with name2, except name2 are NaN?

The expected result:

   id   name1  name2
0   1   James  James
1   2    Bond    NaN
2   3  Steven    NaN
3   4   Kevin  Kevin
4   5     Tim    Tim

Thanks.

pandas dataframe python-3.x
2 Answers
df.name1 = np.where(df.name2.isna(), df.name1, df.name2)

Here the complete code:

import pandas as pd
import numpy as np

df = pd.DataFrame(
    data = [
        ['James',  'James'],
        ['Bond', np.nan],
        ['Steven', np.nan],
        ['Tom', 'Kevin'],
        ['Alba', 'Tim',],
    ], columns =['name1', 'name2']
)

df.name1 = np.where(df.name2.isna(), df.name1, df.name2)

The result is:

    name1  name2
0   James  James
1    Bond    NaN
2  Steven    NaN
3   Kevin  Kevin
4     Tim    Tim
user_53597970

Here is possible replace name2 misisng values by name1 and assign to name1:

df['name1'] = df['name2'].fillna(df['name1'])
print (df)
   id   name1  name2
0   1   James  James
1   2    Bond    NaN
2   3  Steven    NaN
3   4   Kevin  Kevin
4   5     Tim    Tim

Or you can use some solution with test missing/ non missing values, e.g. here Series.mask:

df['name1'] = df['name1'].mask(df['name2'].notna(), df['name2'])
print (df)
   id   name1  name2
0   1   James  James
1   2    Bond    NaN
2   3  Steven    NaN
3   4   Kevin  Kevin
4   5     Tim    Tim

Sample:

temp="""   id   name1  name2
0   1   james  James
1   2    Bond    NaN
2   3  Steven    NaN
3   4     Tom  Kevin
4   5    Alba    Tim"""
#after testing replace 'StringIO(temp)' to 'filename.csv'
df = pd.read_csv(StringIO(temp), sep="\s+")
    
print (df)
   id   name1  name2
0   1   james  James
1   2    Bond    NaN
2   3  Steven    NaN
3   4     Tom  Kevin
4   5    Alba    Tim
user_29010020
Related