Pandas: Merge DataFrame and insert specific locations while preserving order of initial

Viewed 90

If I have an input data frame df:

   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

and a properties data frame props:

   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

A bit about the data, you can think of df as a cross-section where the midpoint of the cross-section is found at the minimum elevation. props is a table of properties about the cross-section that are specified by elevations.

Now, I need to perform 2 operations:

  1. merge the data from props into df where I have a matching Elev
  2. Insert rows from props in the sequential order of df where props.Elev falls between df.Elev. This seems like a one-to-many type insert where you need to insert on both sides of the minimum elevation.

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:

  1. Divide df into left and right using df.Elev.idxmin()
  2. Iterate through props to find a matching elevation or insert place using searchsorted for both left and right
  3. Reindex left and right using the correct order found in step 2 (i.e. _left and _right)
  4. Merge the data to get the result.
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')

My desired result res is:

    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
0 Answers
Related