how can I replace NaN value with data from another dataframe in python?

Question

from io import StringIO
import pandas as pd

x1 = """No.,col1,col2,col3,A
123,2,5,2,NaN
453,4,3,1,3
146,7,9,4,2
175,2,4,3,NaN
643,0,0,0,2
"""
x2 = """No.,col1,col2,col3,A
123,24,57,22,1
453,41,39,15,2
175,21,43,37,3
"""
df1 = pd.read_csv(StringIO(x1), sep=",")
df2 = pd.read_csv(StringIO(x2), sep=",")

how can I fill the NaN value in df1 with the corresponding No. column in df2, to have

No. col1 col2 col3 A
123 2 5 2 1
453 4 3 1 3
146 7 9 4 2
175 2 4 3 3
643 0 0 0 2

I tried the following line but nothing changed

df1['A'].fillna(df2['A'])

mozway · Answer 1 · 2021-09-09T13:55:17.797

3

Use combine_first that is explicitly designed for this purpose:

(df1.set_index('No.')
    .combine_first(df2.set_index('No.'))
    .reset_index()
)

output:

   No.  col1  col2  col3    A
0  123   2.0   5.0   2.0  1.0
1  146   7.0   9.0   4.0  2.0
2  175   2.0   4.0   3.0  3.0
3  453   4.0   3.0   1.0  3.0
4  643   0.0   0.0   0.0  2.0

or fillna after setting 'No.' as index:

(df1.set_index('No.')
    .fillna(df2.set_index('No.'))
    .reset_index()
)

output:

   No.  col1  col2  col3    A
0  123     2     5     2  1.0
1  453     4     3     1  3.0
2  146     7     9     4  2.0
3  175     2     4     3  3.0
4  643     0     0     0  2.0
)

edited Sep 09 '21 at 13:55

answered Sep 09 '21 at 13:39

mozway

194,879
13
39
75

Doesn't this mess up the order of the rows? – not_speshal Sep 09 '21 at 13:46
It reorders during the `reset_index`, if the index is important you can reset it as column first – mozway Sep 09 '21 at 13:49
`df1.combine_first(df1[['No.']].merge(df2, on='No.', how='left'))` – not_speshal Sep 09 '21 at 13:51
1

this doesn't reorder with `fillna`, so the reordering actually happens with `combine_first` – mozway Sep 09 '21 at 13:56

score 1 · Answer 2 · answered Sep 09 '21 at 13:34

1

Try this:

df1['A'] = df1['A'].fillna(df2.set_index('No.').reindex(df1['No.'])['A'].reset_index(drop=True))

answered Sep 09 '21 at 13:34

U13-Forward

69,221
14
89
114

score 1 · Answer 3 · answered Sep 09 '21 at 13:45

Another way with fillna and map:

df1["A"] = df1["A"].fillna(df1["No."].map(df2.set_index("No.")["A"]))

>>> df1
   No.  col1  col2  col3    A
0  123     2     5     2  1.0
1  453     4     3     1  3.0
2  146     7     9     4  2.0
3  175     2     4     3  3.0
4  643     0     0     0  2.0

how can I replace NaN value with data from another dataframe in python?

3 Answers3

Linked

Related