Update DataFrame based on matching rows in another DataFrame-CodePudding

Say there is a group of people who can choose an English and / or a Spanish word. Let's say they chose like this:

>>> pandas.DataFrame(dict(person=['mary','james','patricia','robert','jennifer','michael'],english=['water',None,'hello','thanks',None,'green'],spanish=[None,'agua',None,None,'bienvenido','verde']))
     person english     spanish
0      mary   water        None
1     james    None        agua
2  patricia   hello        None
3    robert  thanks        None
4  jennifer    None  bienvenido
5   michael   green       verde

Say I also have an English-Spanish dictionary (assume no duplicates, i.e. one-to-one relationship):

>>> pandas.DataFrame(dict(english=['hello','bad','green','thanks','welcome','water'],spanish=['hola','malo','verde','gracias','bienvenido','agua']))
   english     spanish
0    hello        hola
1      bad        malo
2    green       verde
3   thanks     gracias
4  welcome  bienvenido
5    water        agua

How can I fill in any missing words, i.e. update the first DataFrame using the second DataFrame where either english or spanish is None, to arrive at this:

>>> pandas.DataFrame(dict(person=['mary','james','patricia','robert','jennifer','michael'],english=['water','water','hello','thanks','welcome','green'],spanish=['agua','agua','hola','gracias','bienvenido','verde']))
     person  english     spanish
0      mary    water        agua
1     james    water        agua
2  patricia    hello        hola
3    robert   thanks     gracias
4  jennifer  welcome  bienvenido
5   michael    green       verde

CodePudding user response：

You may check the map with fillna

df['english'] = df['english'].fillna(df['spanish'].map(df2.set_index('spanish')['english']))
df['spanish'] = df['spanish'].fillna(df['english'].map(df2.set_index('english')['spanish']))
df
Out[200]: 
     person  english     spanish
0      mary    water        agua
1     james    water        agua
2  patricia    hello        hola
3    robert   thanks     gracias
4  jennifer  welcome  bienvenido
5   michael    green       verde