How can i fill in missing csv file value base on reference csv file

Question

I have a reference file like this

Id, Value1, Value2
a, a1, a2
b, b1, b2
c, c1, c2
d, d1, d2
...
n, n1, n2 

and the missing file

Id, Value1, Value2
d,    , d2
g,    , g2
a, a1 , 
c, c1 , 
...
n,    , n2 

how can i write the code to fill missing values based on reference file 'Id'

Answer 1

you can do it using fillna(), but first set your joining column as index in both DF's:

In [71]: df = df.set_index('Id').fillna(ref.set_index('Id')).reset_index()

In [72]: df
Out[72]:
  Id Value1 Value2
0  d     d1     d2
1  g    NaN     g2
2  a    a1      a2
3  c    c1      c2

Data:

In [69]: ref
Out[69]:
  Id Value1 Value2
0  a     a1     a2
1  b     b1     b2
2  c     c1     c2
3  d     d1     d2

In [70]: df
Out[70]:
  Id Value1 Value2
0  d    NaN     d2
1  g    NaN     g2
2  a    a1     NaN
3  c    c1     NaN