title: Pandas: Merge DataFrame and insert specific locations while preserving order of initial
body: <p>If I have an input data frame <code>df</code>:</p>
  <pre><code>   PtNo  Elev data
  0     1   6.0    a
  1     2   4.4    b
  2     3   2.5    c
  3     4   5.1    d
  4     5   7.0    e
  </code></pre>
  <p>and a properties data frame <code>props</code>:</p>
  <pre><code>   Elev props
  0   2.5    ab
  1   2.8    ba
  2   3.3    cc
  3   4.0    dd
  4   4.4    ee
  5   5.1    hh
  6   6.0    nn
  7   6.2    pp
  8   7.0    ii
  </code></pre>
  <p>A bit about the data, you can think of <code>df</code> as a cross-section where the midpoint of the cross-section is found at the minimum elevation.  <code>props</code> is a table of properties about the cross-section that are specified by elevations.</p>
  <p>Now, I need to perform 2 operations:</p>
  <ol>
  <li>merge the data from <code>props</code> into <code>df</code> where I have a matching <code>Elev</code></li>
  <li>Insert rows from <code>props</code> in the sequential order of <code>df</code> where  <code>props.Elev</code> falls between <code>df.Elev</code>.  This seems like a one-to-many type insert where you need to insert on both sides of the minimum elevation.</li>
  </ol>
  <p>My approach to the problem feels hacky and I want to know if there is a more concise way to complete this insert/merge operation. Conceptually here is the workflow:</p>
  <ol>
  <li>Divide <code>df</code> into <code>left</code> and <code>right</code> using <code>df.Elev.idxmin()</code></li>
  <li>Iterate through <code>props</code> to find a matching elevation or insert place using <code>searchsorted</code> for both <code>left</code> and <code>right</code></li>
  <li>Reindex <code>left</code> and <code>right</code> using the correct order found in step 2 (i.e. <code>_left</code> and <code>_right</code>)</li>
  <li>Merge the data to get the result.</li>
  </ol>
  <pre><code>df = pd.DataFrame(data = {'PtNo': [1,2,3,4,5],
                            'Elev':[6,4.4,2.5,5.1,7],
                            'data': ['a','b','c','d','e']})
  
  
  props = pd.DataFrame(data = {'Elev':[2.5,2.8,3.3,4,4.4,5.1,6,6.2,7],
                               'props': ['ab','ba','cc','dd','ee','hh','nn', 'pp','ii']})
  
  left = df.loc[:df.Elev.idxmin(), :]
  left = left.sort_values('Elev')
  right = df.loc[df.Elev.idxmin():, :]
  
  _right = []
  _left = []
  for val in props.Elev:
      
      if val in left.Elev.values:
          _left.append(val)
      else:
          idx = left.Elev.searchsorted(val)
          _left.insert(idx,val)
      
  
      if val in right.Elev.values:
          _right.append(val)
      else:
          idx = right.Elev.searchsorted(val)
          _right.insert(idx,val)
          
  
  left =  left.set_index('Elev').reindex(_left).sort_index()
  left = left.loc[:left.PtNo.idxmin()]
  left = left[::-1]
  right =  right.set_index('Elev').reindex(_right).sort_index()
  
  res = pd.concat([left,right.iloc[1:]])
  res = res.merge(props, on=['Elev'], how='left')
  </code></pre>
  <p>My desired result <code>res</code> is:</p>
  <pre><code>    Elev  PtNo data props
  0    6.0   1.0    a    nn
  1    5.1   NaN  NaN    hh
  2    4.4   2.0    b    ee
  3    4.0   NaN  NaN    dd
  4    3.3   NaN  NaN    cc
  5    2.8   NaN  NaN    ba
  6    2.5   3.0    c    ab
  7    2.8   NaN  NaN    ba
  8    3.3   NaN  NaN    cc
  9    4.0   NaN  NaN    dd
  10   4.4   NaN  NaN    ee
  11   5.1   4.0    d    hh
  12   6.0   NaN  NaN    nn
  13   6.2   NaN  NaN    pp
  14   7.0   5.0    e    ii
  </code></pre>