title: Extract and match items dealing with multiple data-frames using Python
body: <p>I have two dataframes which can be created using the code shown below</p>
  
  <pre><code>df1 = pd.DataFrame({'home':[1,np.nan,2,np.nan,3,4],
   'PERSONAL INFORMATION':['Study Number', 'Study ID','Age when interview 
   done', 'Derived using date of birth','Gender','ethnicity],
     'VARIABLE': 
  ['studyid','dummy','age_interview','dummy','gender','Chinese'],
      'Remarks':[2000000001,20005000001,4265453,0,4135376,2345678]})
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/JjZSO.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JjZSO.png" alt="enter image description here"></a></p>
  
  <pre><code> df2 = df2 = pd.DataFrame({'level_0': ['studyid','age_interview','gender','dobyear','ethderived','smoke','alcohol'],
   '0':['tmp001', 56,'Female',1950,'Chinese','No', 'Yes']})
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/nrxMw.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nrxMw.png" alt="enter image description here"></a></p>
  
  <p><strong>Aim</strong></p>
  
  <p>1) My objective is to take the values from <strong>'level_0'</strong> column of df2 and look for them in <strong>'VARIABLE'</strong> column of df1 to fetch their <strong>'Remarks'</strong> column value provided it satisfies the the below condition</p>
  
  <pre><code>  a) 'Home' column of df1 should contain digits as part of their value( Ex: 1,2,3,4,B1.5,C1.9, D1.2 etc are all valid values for 'Home' column) 
  </code></pre>
  
  <p>2) My objective is same as above, but here I would like to take the values from <strong>'0'</strong> column of df2 and look for them in <strong>'PERSONAL INFORMATION'</strong> column of df1 to fetch their <strong>'Remarks'</strong> value provided it satisfies the below condition</p>
  
  <pre><code>  a) 'VARIABLE' column of df1 should contain 'dummy' as a value
  </code></pre>
  
  <p>For the above two scenarios, I have written the below code but for some reason I feel that it is quite lengthy/inefficient. There should be some easy way to do this.</p>
  
  <p><strong>Scenario - 1</strong></p>
  
  <pre><code>qconc_id = []
  missed_items=[]
  col_list=[]
  for i in df7.index:
     ques = df7['level_0'][i]
     col_list.append(ques)
     try:
        qindex = int(df[df['VARIABLE']==ques].index[0]), 
                      df.columns.get_loc('VARIABLE')
      pos_qindex = qindex[0]
      ques_value = df['home '][pos_qindex]
      result = re.match(r"[A-Z]?[\d]?[\.]?[\d]+", ques_value)
      while result is None:
          pos_qindex = pos_qindex-1
          ques_value = df['home '][pos_qindex]
          result = re.match(r"[A-Z]?[\d]?[\.]?[\d]+", ques_value)
      qconc_id.append(df['Remarks'][pos_qindex])
      except:
          missed_items.append(ans)
  </code></pre>
  
  <p><strong>Scenario - 2</strong></p>
  
  <pre><code>aconc_id = []
  missed_items=[]
  ans_list=[]
  for i in df7.index:
      ans = df7[0][i]
      print("ans is ",ans)
      ans_list.append(ans)
      idx=0
      try:
          aindex = df[df['PERSONAL 
           INFORMATION'].str.contains(ans,case=False,regex=False)].index
           print(aindex)
           pos_aindex = aindex[idx]
           while (df['VARIABLE'][pos_aindex] !='dummy') and 
          (df['PERSONAL INFORMATION'].str.contains('Yes|No',regex=True) 
          [pos_aindex])==False):
               pos_aindex = aindex[idx+1]
           print("The value is ",df['Remarks'][pos_aindex])
           aconc_id.append(df['Remarks'][pos_aindex])
      except:
            print("Goes to Exception")
            aconc_id.append('0')
            missed_items.append(ans)
  </code></pre>
  
  <p>Please note these two things</p>
  
  <p>a) I have used while loop because the values might be repeating. For example, we might have a matching value as 'No' but the df1['VARIABLE'] may not be dummy. So I increase the id values in both scenarios to find whether the next occurrence of 'No' has 'Dummy' value for VARIABLE column. The same applies for scenario 1 as well</p>
  
  <p>b) How can I handle scenarios like "No" when finds match in "Notes", "Nocase". As you can see from my code that I am using regex but it still am encountering error here. </p>
  
  <p>As you can see, I am making some modifications to the code and writing it twice. How can I make it elegant and efficient? I am sure there must be very easy and simple way to do this. </p>
  
  <p>Any suggestions/ideas on alternative approach w.r.t to changing the data format of source data or using merge/join approach is also welcome. </p>
  
  <p>I expect the output, the 'Remarks' value to be stored in the list. Please find the screenshot of what I have done</p>
  
  <p><a href="https://i.stack.imgur.com/MTeUY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/MTeUY.png" alt="enter image description here"></a></p>