title: Merging two dataframe using ID and asof
body: <p>I have two data frame that I wish to join together where the left dataframe have information index by (date, ID) and the right dataframe has information index by (period, ID), where period is year-month.</p>
  
  <p>I end up doing a group by ID for the left frame, iterating thru the groups, select the same group on the right frame, and then doing and asof operation for the index of the group from the left dataframe, like so:</p>
  
  <pre><code>def merge_func(base_df, si_df):  
      df_list = list()
      by_cusip = base_df.groupby('cusip8')
  
      for cusip, group in by_cusip:
          si_df_by_cusip = si_df[si_df.cusip==cusip]
          if len( si_df_by_cusip[ pd.notnull(si_df_by_cusip['sif'])]) &gt; 0:
              group['sif'] = si_df_by_cusip['sif'].asof(group.index)
          else:
              group['sif'] = np.nan
          if len( si_df_by_cusip[ pd.notnull(si_df_by_cusip['si_cover'])]) &gt; 0:
              group['sir'] = si_df_by_cusip['si_cover'].asof(group.index)
          else:
              group['sir'] = np.nan
          df_list.append(group)
      return pd.concat(df_list)
  </code></pre>
  
  <p>But this function is quite slow. Does anyone have a way to make this merge function faster and more efficient?</p>
  
  <p>You may find these links relevant to what I'm trying to accomplish: <a href="https://stackoverflow.com/questions/12322289/kdb-like-asof-join-for-timeseries-data-in-pandas/12336039#12336039">sample for doing asof-join</a> , <a href="https://stackoverflow.com/questions/14614512/merging-two-tables-with-millions-of-rows-in-python/14617925#14617925">merging tables with millions of rows</a></p>
  
  <p>Thank you in advance for your comments and help!</p>