Update pandas DataFrame based on a different DataFrame

Question

I have two pandas DataFrames:

df1

key    id    count
100    9821   7
200    9813   10

df2

nodekey    nodeid    
100        9821   
200        9813

If the nodekey+nodeid in df2 match key+id in df1, count in df1 has to be set to 0. So, the result of the example above should be;

key    id    count
100    9821   0
200    9813   0

I tried the following (matching on key and nodekey only, as a test) but receive an error:

df1['count']=np.where((df1.key == df2.nodekey),0)

ValueError: either both or neither of x and y should be given

Suggestions?

https://stackoverflow.com/questions/33266344/np-where-not-working-in-my-pandas — xyzjayne, Jul 09 '18 at 21:37

rafaelc · Accepted Answer · 2018-07-09T21:41:41.653

2

This should work

df1.loc[df1[['key', 'id']].transform(tuple,1).isin(df2[['nodekey', 'nodeid']].transform(tuple,1)), "count"] = 0

which is basically using

df.loc[mask, 'count']=0

where mask is True for rows where tuple ('key', 'id') matches any tuple ('nodekey', 'nodeid')

edited Jul 09 '18 at 21:41

answered Jul 09 '18 at 21:32

rafaelc

1

Works like a charm! Thank you so much for the help. – FunnyChef Jul 10 '18 at 01:43

score 0 · Answer 2 · answered Jul 09 '18 at 21:42

Merge the dataframes using the left merge (the rows that are present in df1 but not in df2 will be filled with nans):

combined = df1.merge(df2, left_on=['key', 'id'],
                         right_on=['nodekey', 'nodeid'], how='left')

Update the counts for the rows that are non-nan:

combined.loc[combined.nodekey.notnull(), 'count'] = 0

Cleanup the unwanted columns:

combined.drop(['nodekey', 'nodeid'], axis=1, inplace=True)
#   key    id  count
#0  100  9821      0
#1  200  9813      0
#2  300  9855      7

2 Answers2