Find row number in column where it matches any other value in column of other dataframe

Question

I have a code:

import pandas as pd
import numpy as np

arm_1_and_m1_df = pd.DataFrame({ 'record_id': [1, 4, 3, np.nan],
                   'two': [1, 2, np.nan , 4]
                 })

redcap_final_arm1_data = pd.DataFrame({ 'record_id': [1, 2, 3, 4, 5, 6, 7, 8, 9, np.nan],
                   'two': [1, 2, 3, 4, 5, 6, 7, 8, 9, np.nan]
                 })

ahk_ids_new=[]
for items in arm_1_and_m1_df['record_id'].iteritems():     # https://www.geeksforgeeks.org/python-pandas-series-iteritems/
    ahk_ids_new.append(np.where(redcap_final_arm1_data['record_id'] == items))    # https://stackoverflow.com/questions/48519062/rs-which-and-which-min-equivalent-in-python

After running code above and after ahk_ids_new the content of ahk_ids_new is:

[(array([], dtype=int64),),
 (array([], dtype=int64),),
 (array([], dtype=int64),),
 (array([], dtype=int64),)]

Values in redcap_final_arm1_data['record_id'] are unique.

Question: I want to get all row numbers (index) of redcap_final_arm1_data['record_id'] in ahk_ids_new where redcap_final_arm1_data['record_id'] has the same value as any values in arm_1_and_m1_df['record_id']. How to do that?

Expected output (content) of ahk_ids_new:

Out[57]: [0, 3, 2, 9]

If there is a better way to do what I need with data frames from my code please post your better variant instead of fixing my code.

Please post your expected output so it is easier for us to help you — Juan C, Mar 03 '20 at 17:25
@jfaccioni It should be `[0, 3, 2, 9]`. Sorry, I am coming from R where index starts from `1`. — vasili111, Mar 03 '20 at 17:35

score 3 · Accepted Answer · edited Apr 01 '20 at 14:55

3

Try isin and slicing on index

a_index = (redcap_final_arm1_data.index[redcap_final_arm1_data.record_id
                                           .isin(arm_1_and_m1_df.record_id)].tolist())

output:

Out[1355]: [0, 2, 3, 9]

edited Apr 01 '20 at 14:55

vasili111

6,032
10
50
80

answered Mar 03 '20 at 17:38

Andy L.

24,909
4
17
29

Find row number in column where it matches any other value in column of other dataframe

1 Answers1

Linked