How to fill NAN Value base on another dataframe in Python

Question

How can I merge these two dataframes as my desired result?

df1 = pd.DataFrame({'Animals': ['Dog','Cat','Monkey','Snake','bear','dear'],
                    'Price': ['100','NAN','100','70','NAN','100']})

df2 = pd.DataFrame({'Animals': ['Cat','bear'], 'Price': ['50','200']})

result = pd.merge(df1, df2, how='left', on=['Animals'])

Desired result:

Animals     Price
Dog         100
Cat         50
Monkey      100
Snake       70
bear        200
dear        100

Does this answer your question? [How to merge two dataframe in pandas to replace nan](https://stackoverflow.com/questions/25095971/how-to-merge-two-dataframe-in-pandas-to-replace-nan) — ddejohn, Aug 27 '21 at 23:11
This may also help https://stackoverflow.com/questions/57702145/merge-dataframes-of-different-sizes-and-simultaneously-overwrite-nan-values — ddejohn, Aug 27 '21 at 23:19

score 0 · Answer 1 · answered Aug 27 '21 at 23:24

0

You can use combine_first if NAN will be pd.NA or np.nan

import pandas as pd

df1 = pd.DataFrame(data={'Animals': ['Dog', 'Cat', 'Monkey', 'Snake', 'bear', 'dear'], 'Price': ['100', pd.NA, '100', '70', pd.NA, '100']})
df2 = pd.DataFrame(data={'Animals': ['Cat', 'bear'], 'Price': ['50', '200']})

df1 = df1.set_index(keys='Animals')
df2 = df2.set_index(keys='Animals')
result = df2.combine_first(other=df1)
result = result.reset_index()
print(result)

answered Aug 27 '21 at 23:24

Idan Hazan

116
9

Thanks for ur answer, seen its work. but has one sorting issue. How can i combine without sorting? – YouKnowWho Aug 27 '21 at 23:34

MDR · Accepted Answer · 2021-08-28T00:55:03.213

Try:

df1 = pd.DataFrame(data={'Animals': ['Dog', 'Cat', 'Monkey', 'Snake', 'bear', 'dear'], 'Price': ['100', pd.NA, '100', '70', pd.NA, '100']})
df2 = pd.DataFrame(data={'Animals': ['Cat', 'bear'], 'Price': ['50', '200']})

df1 = df1.set_index('Animals')
df2 = df2.set_index('Animals')

df1['Price'].update(df2['Price'])
df1 = df1.reset_index()
df2 = df2.reset_index()

print(df1)

Outputs:

  Animals Price
0     Dog   100
1     Cat    50
2  Monkey   100
3   Snake    70
4    bear   200
5    dear   100

score -1 · Answer 3 · answered Aug 28 '21 at 16:34

Here is the dummy data:

df1 = pd.DataFrame({'Animals': ['Dog','Cat','Monkey','Snake','bear','dear'],
                    'Price': ['100','NAN','100','70','NAN','100']})

df2 = pd.DataFrame({'Animals': ['Cat','bear'], 'Price': ['50','200']})

First you need to make 'NAN' an actual "not a number" value (np.nan)

df1['Price'] = pd.to_numeric(df1['Price'], errors='coerce')

Then you can make the df2 to a dictionary to replace the values:

convert_dict = df2.set_index("Animals").to_dict()['Price']

and then, replace the nans with the values in the dictionary by replacing the animals:

df1.loc[df1['Price'].isna(),'Price'] = \
    df1.loc[df1['Price'].isna(),'Animals'].replace(convert_dict)

result :

|    | Animals   |   Price |
|---:|:----------|--------:|
|  0 | Dog       |     100 |
|  1 | Cat       |      50 |
|  2 | Monkey    |     100 |
|  3 | Snake     |      70 |
|  4 | bear      |     200 |
|  5 | dear      |     100 |

score -2 · Answer 4 · answered Aug 27 '21 at 23:08

-2

You could try replacing the NaNs with zeros:

df1 = df1.fillna(0)

answered Aug 27 '21 at 23:08

Jan Rostenkowski

315
2
11

i don't want to fill NA with 0 . Just want to fill with DF2 values. Thanks for your answer – YouKnowWho Aug 27 '21 at 23:18
Please provide additional details in your answer. As it's currently written, it's hard to understand your solution. – Community Aug 28 '21 at 03:25

How to fill NAN Value base on another dataframe in Python

4 Answers4