How to map values to new column with upper and lower bounds

Given a dataframe with two columns that describe a record type and the phase it is in, what is the most pythonic way of assigning unique values to a new column based on the phase and type? For example:

d = {'type': ['a','b','c','a','b','c','a','b','c',], 'phase': [-10,3,2,1,-7,-3,-1,-5,4]}
df_ = pd.DataFrame(data=d)
df_

Contrived example mappings with upper and lower bounds:

a_phase = {
('<', -9):0.001,
-9:0.010,
-8:0.022,
-7:0.026,
-6:0.092,
-5:0.091,
-4:0.082,
-3:0.121,
-2:0.060,
-1:0.105,
0:0.018,
1:0.092,
2:0.092,
3:0.092,
4:0.092,
('>',4):0.000,
}

b_phase = {
('<', -9):0.016,
-9:0.011,
-8:0.021,
-7:0.028,
-6:0.052,
-5:0.075,
-4:0.057,
-3:0.102,
-2:0.238,
-1:0.270,
0:0.034,
1:0.014,
2:0.061,
('>',2):0.000,
}

c_phase = {
('<', -9):0.016,
-9:0.016,
-8:0.011,
-7:0.010,
-6:0.038,
-5:0.015,
-4:0.099,
-3:0.117,
-2:0.216,
-1:0.213,
0:0.008,
1:0.008,
2:0.008,
('>',2):0.000,
}

I could just go about using a bunch of np.where clauses but that feels extremely inefficient so I wanted to reach out to the community and see if anyone has suggestions. I also realize I can't use logical operators > < as keys in my dictionary mappings, this is for representation purposes only.

The ideal output would look like:

One option could be to set up a nested dictionary and map the values per group, eventually using cut to bin the ranges. If you have many dictionaries that might not be very efficient.

Another option, if you change your dictionaries a bit would be to craft a DataFrame and use merge_asof:

Inf = float('inf')

a_phase = {
-Inf:0.001,
-9:0.010,
-8:0.022,
-7:0.026,
-6:0.092,
-5:0.091,
-4:0.082,
-3:0.121,
-2:0.060,
-1:0.105,
0:0.018,
1:0.092,
2:0.092,
3:0.092,
4:0.092,
4.1:0.000,
}
b_phase = {
-Inf:0.016,
-9:0.011,
-8:0.021,
-7:0.028,
-6:0.052,
-5:0.075,
-4:0.057,
-3:0.102,
-2:0.238,
-1:0.270,
0:0.034,
1:0.014,
2:0.061,
2.1:0.000,
}
c_phase = {
-Inf:0.016,
-9:0.016,
-8:0.011,
-7:0.010,
-6:0.038,
-5:0.015,
-4:0.099,
-3:0.117,
-2:0.216,
-1:0.213,
0:0.008,
1:0.008,
2:0.008,
2.1:0.000,
}

ref = (pd.DataFrame({'a': a_phase, 'b': b_phase, 'c': c_phase})
         .melt(var_name='type', value_name='weight', ignore_index=False)
         .rename_axis('phase').reset_index()
         .sort_values(by='phase')
         .dropna(subset='weight')
       )

out = (pd.merge_asof(df_.reset_index()
                        .astype({'phase': 'float64'})
                        .sort_values(by='phase'),
                     ref, by='type', on='phase')#, direction='forward')
         .set_index('index').reindex(df_.index)
       )

Output:

  type  phase  weight
0    a  -10.0   0.001
1    b    3.0   0.000
2    c    2.0   0.008
3    a    1.0   0.092
4    b   -7.0   0.028
5    c   -3.0   0.117
6    a   -1.0   0.105
7    b   -5.0   0.075
8    c    4.0   0.000

First modify dictioanries by tuples for less and more values:

a_phase = {
('<', -9):0.001,
-9:0.010,
-8:0.022,
-7:0.026,
-6:0.092,
-5:0.091,
-4:0.082,
-3:0.121,
-2:0.060,
-1:0.105,
0:0.018,
1:0.092,
2:0.092,
3:0.092,
4:0.092,
('>', 4):0.000,
}

b_phase = {
('<', -9):0.016,
-9:0.011,
-8:0.021,
-7:0.028,
-6:0.052,
-5:0.075,
-4:0.057,
-3:0.102,
-2:0.238,
-1:0.270,
0:0.034,
1:0.014,
2:0.061,
('>', 2):0.000,
}

c_phase = {
('<', -9):0.016,
-9:0.016,
-8:0.011,
-7:0.010,
-6:0.038,
-5:0.015,
-4:0.099,
-3:0.117,
-2:0.216,
-1:0.213,
0:0.008,
1:0.008,
2:0.008,
('>', 2):0.000,
}

Then create DataFrame by dictionaries:

d1 = {'a':a_phase,
      'b':b_phase,
      'c':c_phase}

df = pd.DataFrame(((k, *x) for k, v in d1.items() for x in v.items()),
                  columns=['type','phase','weight'])

And left join exact matched values, not matched less and greater ones:

df_ = df_.merge(df, how='left')

Processing only not matched values filled missing values, first exctract them:

mask = df_['weight'].isna()

df1 = (df[pd.to_numeric(df['phase'], errors='coerce').isna()]
            .assign(op=lambda x: x['phase'].str[0], phase=lambda x: x['phase'].str[1]))

print (df1)

   type  phase  weight op
0     a     -9   0.001  <
15    a      4   0.000  >
16    b     -9   0.016  <
29    b      2   0.000  >
30    c     -9   0.016  <
43    c      2   0.000  >

And then filter and merge by type for both, join togethter and assign to column weight filtered by mask:

s1 = (df_[mask].reset_index().merge(df1[df1['op'].eq('<')], how='left', on='type')
                .assign(weight = lambda x: x['weight_y'].where(x['phase_x'].lt(x['phase_y'])))
                .set_index('index')['weight'].dropna())

s2 = (df_[mask].reset_index().merge(df1[df1['op'].eq('>')], how='left', on='type')
                .assign(weight = lambda x: x['weight_y'].where(x['phase_x'].gt(x['phase_y'])))
                .set_index('index')['weight'].dropna())

df_.loc[mask, 'weight'] = pd.concat([s1, s2])
print (df_)
  type phase  weight
0    a   -10   0.001
1    b     3   0.000
2    c     2   0.008
3    a     1   0.092
4    b    -7   0.028
5    c    -3   0.117
6    a    -1   0.105
7    b    -5   0.075
8    c     4   0.000