dataframe manipulation python based on conditons

user_9031176 0
Viewed 34
input_df1:  ID       MSG         
            id-1    'msg1'                     
            id-2    'msg2'                     
            id-3    'msg3'


ref_df2:     ID       MSG
             id-1     'msg1'
             id-2     'xyzz'
             id-4     'msg4'

I am trying to generate an output dataframe based on the following conditions:

  1. If both 'id' & 'msg' values in input_df match the values in ref_df = matched

  2. If 'id' value in input_df doesn't exists in ref_df = notfound

  3. If only 'id' value in input_df matches with 'id' value in ref_df = not_matched

sample output:  ID       MSG    flag
                id-1    'msg1'  matched 
                id-2    'msg2'  not_matched
                id-3    'msg3'  notfound

I can do it using lists but considering the fact that I deal with huge amounts of data, performance is important, hence I am looking for a much faster solution. Any little help will be highly appreciated '''

pandas data-manipulation dataframe python-3.x list
3 Answers

Let's use map to map the ids to the reference messages and use np.select:

ref_msg = df1['ID'].map(df2.set_index('ID')['MSG'])

df1['flag'] = np.select((ref_msg.isna(), ref_msg==df1['MSG']),
                        ('not found', 'matched'), 'not_matched')

Output (df1):

     ID     MSG         flag
0  id-1  'msg1'      matched
1  id-2  'msg2'  not_matched
2  id-3  'msg3'    not found
user_42384080

You can also use indicator=True parameter of df.merge:

In [3867]: x = df1.merge(df2, how='outer', indicator=True).groupby('ID', as_index=False).last()

In [3864]: d = {'both':'matched', 'right_only':'not_matched', 'left_only':'notfound'}

In [3869]: x._merge = x._merge.map(d)

In [3871]: x
Out[3871]: 
     ID     MSG       _merge
0  id-1  'msg1'      matched
1  id-2  'xyzz'  not_matched
2  id-3  'msg3'     notfound
user_58208140

The fastest and the most Pythonic way of doing what you want to do is to use dictionaries, as shown below:

list_ID_in = ['id-1', 'id-2', 'id-3']
list_msg_in = ['msg1', 'msg2', 'msg3']

list_ID_ref = ['id-1', 'id-2', 'id-4']
list_msg_ref = ['msg1', 'xyzz', 'msg4']

dict_in = {k:v for (k, v) in zip(list_ID_in, list_msg_in)}
dict_ref = {k:v for (k, v) in zip(list_ID_ref, list_msg_ref)}

list_out = [None] * len(dict_in)
for idx, key in enumerate(dict_in.keys()):
    try:
        ref_value = dict_ref[key]
        if ref_value == dict_in[key]:
            list_out[idx] = 'matched'
        else:
            list_out[idx] = 'not_matched'
    except KeyError:
        list_out[idx] = 'not_found'
user_18709690
Related