how to add a dataframe with another dataframe and updated common values based on a column-CodePudding

my first data frame-

df1 = pd.DataFrame({'CONTRACT':['Tom', 'nick', 'krish', 'jack'],
        'Net_Qty':[20, 21, 19, 18]})

    CONTRACT    Net_Qty
0   Tom       20
1   nick      21
2   krish     19
3   jack      18

second data frame-

df2 = pd.DataFrame({'CONTRACT':['Tom', 'nick', 'amit', 'joy'],
        'Net_Qty':[30, 40, 45, 54]})
    CONTRACT    Net_Qty
0   Tom         30
1   nick        40
2   amit        45
3   joy         54

I want dataframe dataframe Like this (all values of df2 and uncommon values of df1)-

        CONTRACT    Net_Qty
    0   Tom         30
    1   nick        40
    2   krish       19
    4   jack        18
    2   amit        45
    3   joy         54

I tried like this-

cols = list(df1.columns)
            df1.loc[df1.CONTRACT.isin(
                df2.CONTRACT), cols] = df2[cols]
            print(df1)

but its not working fine.......

Can anyone please suggest a better way-

CodePudding user response：

Use pd.concat and drop_duplicates:

out = pd.concat([df2, df1]).drop_duplicates('CONTRACT', ignore_index=True)
print(out)

# Output
  CONTRACT  Net_Qty
0      Tom       30
1     nick       40
2     amit       45
3      joy       54
4    krish       19
5     jack       18

CodePudding user response：

I resolved it with this code

df1 = pd.DataFrame({'CONTRACT':['Tom', 'nick', 'krish', 'jack'],'Net_Qty':[20, 21, 19, 18]})
df2 = pd.DataFrame({'CONTRACT':['Tom', 'nick', 'amit', 'joy'],'Net_Qty':[30, 40, 45, 54]})
df_merge = pd.merge(df1, df2, on = 'CONTRACT', how = 'outer')
df_merge[['Net_Qty_x', 'Net_Qty_y']] = df_merge[['Net_Qty_x', 'Net_Qty_y']].replace({np.nan : None})
condition_list = [df_merge['Net_Qty_y'].values != None]
choice_list = [df_merge['Net_Qty_y']]
df_merge['Net_Qty'] = np.select(condition_list, choice_list, df_merge['Net_Qty_x'])
df_merge['Net_Qty'] = df_merge['Net_Qty'].astype(int)
df_merge = df_merge[['CONTRACT', 'Net_Qty']]
df_merge