pandas - Fill empty cells of dataframe with values from other dataframe

Question

I would like to fill empty cells of a dataframe. The cells should be filled with values or strings to be found in a other dataframe where the "Y" is the same. I thought about using a dicitonary, but cannt get it to work. The desires ouput would be to fill the emtpy cell in df1 (row 1) with the value "7". The sorting and the length of the dataframes will not be the same. df1 will also contain duplicates.

df1 = pd.DataFrame({'Z': ['a', 'b', 'c', 'a', 'a'],
                    'Y': [6,'',8, 6, 6]
                   })
df2 = pd.DataFrame({'Z': ['a', 'b', 'c', 'd', 'e'],
                   'Y': [6, 7, 8, 9, 1],
                   })
df1
Z  Y
0  a  6
1  b   
2  c  8
3  a  6
4  a  6
df2
Z  Y
0  a  6
1  b  7
2  c  8
3  d  9
4  e  1

what I tried:

dic = df2.set_index('Z').to_dict()['Y']
df1.replace({'Y': {'' :dic}})

I get the following error: "TypeError: unhashable type: 'numpy.ndarray' "
Thanks for any help.

question from:https://stackoverflow.com/questions/66062262/fill-empty-cells-of-dataframe-with-values-from-other-dataframe

Answer 1

You can replace only matched rows by mask for test empty rows by Series.map, if no match get missing values:

dic = df2.set_index('Z')['Y'].to_dict()

m = df1['Y'].eq('')
#if need test missing values
#m = df1['Y'].isna()
df1.loc[m, 'Y'] = df1['Z'].map(dic)
print (df1)
   Z  Y
0  a  6
1  b  7
2  c  8
3  a  6
4  a  6