2

I have df1 (key_col, cola, colb, colc) and df2(key_col, colz) I want to do the SQL equivalent of:

UPDATE df1
SET df1.colc = df2.colz
WHERE df1.key = df2.key

I've tried various incarnations of merge and join without success. Not looking for loops but rather using Pandas

df1: key_col  cola  colb  colc
     50       'foo' 'bar' 'foo'
     49       'foo' 'bar' 'bla'
     23       'bar' 'foo' 'bla'

df2: key_col  colz
     23      'something'
     50      'something else'

Note, the key col is not a pandas index rather a normal column

Resulting df1 desired:

df1: key_col  cola  colb  colc
     50   'foo' 'bar' 'something else'
     49   'foo' 'bar' 'bla'
     23   'bar' 'foo' 'something'

EDIT:

Have added row key_col = 49 to make clear that a straight copy over of colc c will not work as it will overwrite existing values.

RookieCookie
  • 155
  • 8

2 Answers2

2

Try this.

df1.set_index('Key',inplace=True)
df2.set_index('Key',inplace=True)
df2.rename(columns={'colz':'colc'}, inplace=True)
df1.update(df2)
df1.reset_index(inplace=True)
moys
  • 7,747
  • 2
  • 11
  • 42
1
df1 = pd.merge(df1, df2)
df1.drop('colc',axis=1,inplace=True)
df1.rename(columns={'colz':'colc'}, inplace=True)
moys
  • 7,747
  • 2
  • 11
  • 42
  • Thanks, but your solution would blanket overwrite existing df1's colc values, whereas I only an update to occur when the key's match. I've edited the original dfs to make this explicit. – RookieCookie Aug 19 '19 at 14:33