这里可以创建IntervalIndex
by IntervalIndex.from_tuples http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.IntervalIndex.from_tuples.html在列和索引中df2
DataFrame,然后更改查找IntervalIndex.get_loc http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.IntervalIndex.get_loc.html:
第一次测试:
print (df2.columns)
Index(['8.8-8.9', '9.0-9.2', '9.3-9.4', '9.5-9.6', '9.7-10'], dtype='object')
print (df2.index)
Index(['11.1 to 14', '8.1 to 11', '6.1 to 8', 'below 6'], dtype='object', name='pf1')
c = [(float(x[0]), float(x[1])) for x in df2.columns.str.split('-')]
i = [(0, float(x[0].split()[1])) if 'below' in x[0] else (float(x[0]), float(x[1]))
for x in df2.index.str.split(' to ')]
print (i)
[(11.1, 14.0), (8.1, 11.0), (6.1, 8.0), (0, 6.0)]
print (c)
[(8.8, 8.9), (9.0, 9.2), (9.3, 9.4), (9.5, 9.6), (9.7, 10.0)]
df2.columns = pd.IntervalIndex.from_tuples(c, closed='both')
df2.index = pd.IntervalIndex.from_tuples(i, closed='both')
print (df2)
[8.8, 8.9] [9.0, 9.2] [9.3, 9.4] [9.5, 9.6] [9.7, 10.0]
[11.1, 14.0] 100 200 300 400 500
[8.1, 11.0] 200 300 400 500 600
[6.1, 8.0] 300 400 500 600 700
[0.0, 6.0] 400 500 600 700 800
out= []
for row, col in zip(df1['pf1'], df1['pf2']):
try:
out.append(df2.iat[df2.index.get_loc(row), df2.columns.get_loc(col)])
except KeyError:
out.append(np.nan)
df1['Pay'] = out
print (df1)
Name pf1 pf2 pf3 Pay
0 Adam 14.6 8.9 59 NaN
1 Bob 13.2 9.0 75 200.0
2 Charlie 11.1 9.1 89 200.0
3 Dylan 14.6 9.0 97 NaN
4 Eric 11.1 8.8 105 100.0
5 Fedderick 12.5 9.2 69 200.0