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:
- merge the data from
propsintodfwhere I have a matchingElev - Insert rows from
propsin the sequential order ofdfwhereprops.Elevfalls betweendf.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:
- Divide
dfintoleftandrightusingdf.Elev.idxmin() - Iterate through
propsto find a matching elevation or insert place usingsearchsortedfor bothleftandright - Reindex
leftandrightusing the correct order found in step 2 (i.e._leftand_right) - 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