Postgresql: update data from dataframe python in spesific column table

Question

i have table in my postgresql database with name "abc"

id | name | age | hobby | address | status |
1  | ana  | 32  | eat   | NULL    |  NULL  | 
2  | mela | 43  | play  | NULL    |  NULL  | 
3  | ani  | 50  | play  | NULL    |  NULL  | 
4  | jen  | 23  | read  | NULL    |  NULL  | 
5  | sop  | 14  | sleep | NULL    |  NULL  |
...etc

and i have dataframe in pandas with name "aadc"

id | address | status   |
1  | jambi   | single   |
2  | tgr     | married  |
3  | jakarta | divorced |
4  | jaksel  | single   |
5  | meruya  | divorced |
..etc

But I am actually expecting this output in database table "abc"

id | name | age | hobby | address | status   |
1  | ana  | 32  | eat   | jambi   | single   |
2  | mela | 43  | play  | tgr     | married  | 
3  | ani  | 50  | play  | jakarta | divorced |
4  | jen  | 23  | read  | jaksel  | single   |
5  | sop  | 14  | sleep | meruya  | divorced |
...etc

how to i update table "abc" in my postgre db from dataframe "aadc" ?

Answer 1

Remove null columns first and then merge with left join:

df = df1.drop(['address','status'], axis=1).merge(df2, on='id', how='left')

Or replace missing values by id in DataFrame.combine_first:

df = df1.set_index('id').combine_first(df2.set_index('id')).reset_index()
print (df)
   id  address  age  hobby  name    status
0   1    jambi   32    eat   ana    single
1   2      tgr   43   play  mela   married
2   3  jakarta   50   play   ani  divorced
3   4   jaksel   23   read   jen    single
4   5   meruya   14  sleep   sop  divorced