0

How to replace the NaN values with the values from the first df:

    country   sex    year      cancer
0   Albania  female  2000       32
1   Albania  male    2000       58
2   Antigua  female  2000       2
3   Antigua  male    2000       5
4   Argen    female  2000       591
5   Argen    male    2000       2061

in the second df:

    country year    sex    cancer
0   Albania 1985    female  NaN
1   Albania 1985    male    NaN
2   Albania 1986    female  NaN
3   Albania 1986    male    NaN
4   Albania 1987    female  25.0
5   Antigua 1992    male    NaN
6   Antigua 1985    female  NaN

the final should look like:

country year    sex     cancer 
    0   Albania 1985    female  32
    1   Albania 1985    male    58
    2   Albania 1986    female  32
    3   Albania 1986    male    58
    4   Albania 1987    female  25
    5   Antigua 1992    male    5
    6   Antigua 1985    female  2

Important are 2 conditions Country and Sex

Sinchetru
  • 539
  • 1
  • 3
  • 13
  • 1
    You can try [merging](https://stackoverflow.com/questions/53645882/pandas-merging-101) – Sotos Mar 19 '19 at 14:34
  • Possible duplicate of [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) – Terry Mar 19 '19 at 14:39
  • I guess the difficulty here is to only replace `NaN` values. – IanS Mar 19 '19 at 14:40
  • Have you tried [`combine_first`](https://pandas.pydata.org/pandas-docs/version/0.22/generated/pandas.DataFrame.combine_first.html)? Not sure how well it would work in your case... – IanS Mar 19 '19 at 14:41
  • Just do `merge` with `np.where` – BENY Mar 19 '19 at 14:42
  • Steps 1. Check the value 2. if nan > check country and sex 3. get the value for country and sex from the other df and fill the NaN value. 4. if not nan> go further – Sinchetru Mar 19 '19 at 14:51

1 Answers1

2

I am end up using fillna 

df2.set_index(['country','sex'],inplace=True)
df2['cancer']=df2['cancer'].fillna(df1.set_index(['country','sex']).cancer)
df2.reset_index(inplace=True)
df2
Out[745]: 
   country     sex  year  cancer
0  Albania  female  1985    32.0
1  Albania    male  1985    58.0
2  Albania  female  1986    32.0
3  Albania    male  1986    58.0
4  Albania  female  1987    25.0
5  Antigua    male  1992     5.0
6  Antigua  female  1985     2.0
BENY
  • 317,841
  • 20
  • 164
  • 234