title: Python Pandas: Fill value from one dataframe with the value from a second dataframe based on condition
body: <p>I have a dataframe with a list of records that have a report date associated with them.  I need to associate these records with a month based on an in-house calendar.  This in-house calendar is held in a different dataframe.  The in-house calendar is not based on standard months.</p>
  <p>My goal is to check the dates of the report in my dataframe of record and see if they fall within the calendar range in df_cal.  If they do, I would like to return the in-house month from df_cal and attach it to the appropriate record in df1.</p>
  <p>I've tried just about everything that I can think of including attempting to nest an apply method within another apply method but couldn't figure out how to get it to work.  The latest route I tried was pairing apply with where.  I know this method won't work either but I've stripped it down and included it to show the logic needed to transfer the month.  I can't seem to figure this one out.</p>
  <p>About the real data:
  In reality, df1 is thousands of rows long and the calendar dataframe is 48 rows (4 years) for the time being; however, the dataframes below represent the major points of friction I am encountering with this problem.</p>
  <p>Any help would be much appreciated.</p>
  <p><strong>Calendar Dataframe:</strong></p>
  <pre><code>d_c = {&quot;Month&quot;: [&quot;May&quot;,&quot;Jun&quot;,&quot;Jul&quot;,&quot;Aug&quot;,&quot;Sep&quot;],
         &quot;FY&quot;: [2020, 2021, 2021, 2021, 2021],
         &quot;Start Date&quot;: [&quot;4/27/2020&quot;, &quot;5/25/2020&quot;, &quot;6/22/2020&quot;, &quot;7/27/2020&quot;, &quot;8/24/2020&quot;],
         &quot;End Date&quot;: [&quot;5/24/2020&quot;, &quot;6/21/2020&quot;,&quot;7/26/2020&quot;, &quot;8/23/2020&quot;, &quot;9/20/2020&quot;]}
  
  dfc = pd.DataFrame(data=d)
  dfc[&quot;Start Date&quot;] = pd.to_datetime(dfc[&quot;Start Date&quot;], format=&quot;%m/%d/%Y&quot;)
  dfc[&quot;End Date&quot;] = pd.to_datetime(dfc[&quot;End Date&quot;], format=&quot;%m/%d/%Y&quot;)
  </code></pre>
  <p><strong>Report Dataframe:</strong></p>
  <pre><code>d_1 = {&quot;Report Date&quot;:[&quot;5/5/2020&quot;,&quot;5/26/2020&quot;,&quot;6/9/2020&quot;,
                        &quot;6/30/2020&quot;,&quot;7/14/2020&quot;,&quot;8/11/2020&quot;,
                        &quot;8/25/2020&quot;,&quot;9/8/2020&quot;,&quot;9/15/2020&quot;],
         &quot;FY&quot;: [2020, 2021, 2021,
                2021, 2021, 2021,
                2021, 2021, 2021],
         &quot;Name&quot;: [&quot;Jamaal&quot;,&quot;Tamba&quot;,&quot;Patrick&quot;,
                  &quot;Tony&quot;,&quot;Derrick&quot;,&quot;Trent&quot;,
                  &quot;Clyde&quot;,&quot;Chris&quot;,&quot;Priest&quot;
                 ]}
  
  df1 = pd.DataFrame(data=d_1)
  df1[&quot;Report Date&quot;] = pd.to_datetime(df1[&quot;Report Date&quot;], format=&quot;%m/%d/%Y&quot;)
  </code></pre>
  <p><strong>Attempted Solution:</strong></p>
  <pre><code>def conditions(df_row):
      '''Passed to apply() to add correct months
      to the report.  This function specifies the conditions under which the
      month will be returned.'''
      
      report_fy = df_row[&quot;FY&quot;]
      report_date = df_row[&quot;Report Date&quot;]
      calendar_fy = dfc[&quot;FY&quot;]
      month_start = dfc[&quot;Start Date&quot;]
      month_end = dfc[&quot;End Date&quot;]
      month = dfc[&quot;Month&quot;]
      
      # This doesn't work, but it does show the logic that I need.
      return np.where((report_fy == calendar_fy)
                      &amp; (report_date &gt;= month_start)
                      &amp; (report_date &lt;= month_end))
  
  df_out = df1.apply(conditions, axis=1)
  </code></pre>
  <p><strong>Desired Output:</strong></p>
  <pre><code>    Report Date  FY     Name    Month
  0   2020-05-05   2020   Jamaal  May
  1   2020-05-26   2021   Tamba   Jun
  2   2020-06-09   2021   Patrick Jun
  3   2020-06-30   2021   Tony    Jul
  4   2020-07-14   2021   Derrick Jul
  5   2020-08-11   2021   Trent   Aug
  6   2020-08-25   2021   Clyde   Sep
  7   2020-09-08   2021   Chris   Sep
  8   2020-09-15   2021   Priest  Sep
  </code></pre>