Fill empty cells of dataframe with values from other dataframe

user_15151075 0
Viewed 347

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.

pandas fill dataframe dictionary
1 Answers

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
user_29010020
Related