How to update values of a column according to the values of a column of the other datafreme with pan-CodePudding

I have one dataframe:

import pandas as pd 
df1 = pd.DataFrame([['Tom', 'good', 3],
                       ['Jack', 'bad', 6],
                       ['Tom', 'average', 9],
                       ['Jerry', 'good', 89],
                       ['Lucy', 'average', 11]
                       ],
                      columns=['name', 'text', 'day'])

and the other dataframe:

df2 = pd.DataFrame([['Tom', 'bad', 55],
                   ['Jack', 'good', 64],
                   ['Mary', 'bad', 92],
                   ['Lucy', 'average', 109]
                   ],
                  columns=['name', 'text', 'day'])

if df2['name'] is in df1['name'], then the value of 'day' of df1 should be replaced by that of df2, which means, I have the following result:

result = pd.DataFrame([['Tom', 'good', 55],
                   ['Jack', 'bad', 64],
                   ['Tom', 'average', 55],
                   ['Jerry', 'good', 89],
                   ['Lucy', 'average', 109]
                   ],
                  columns=['name', 'text', 'day'])

I know update can do that, but I want a conditional replacement method.

CodePudding user response：

Just do np.where

df1['day'] = np.where(df1['name'].isin(df2['name']), df1['name'].map(df2.set_index('name')['day']),df1['day'])
df1
Out[263]: 
    name     text    day
0    Tom     good   55.0
1   Jack      bad   64.0
2    Tom  average   55.0
3  Jerry     good   89.0
4   Lucy  average  109.0

CodePudding user response：

Try this:

import numpy as np
df1 = df1.merge(right=df2[['name', 'day']], on='name', how='left')
df1['day_x'] = np.where(df1['day_y'].isnull(), df1['day_x'], df1['day_y'])
df1.drop(columns=['day_y'], inplace=True)
df1.rename(columns={'day_x': 'day'}, inplace=True)
print(df1)

    name     text    day
0    Tom     good   55.0
1   Jack      bad   64.0
2    Tom  average   55.0
3  Jerry     good   89.0
4   Lucy  average  109.0

CodePudding user response：

just a slight variation of this solution:

df1['day'] =  df1['name'].map(df2.set_index('name')['day']).combine_first(df1['day'])

>>> df1
'''
    name     text    day
0    Tom     good   55.0
1   Jack      bad   64.0
2    Tom  average   55.0
3  Jerry     good   89.0
4   Lucy  average  109.0