使用df1中的值从df2中检索值，其中df2列和索引包含一系列值 [英] Using values from df1 to retrieve values from df2 where df2 columns and index contain a range of values

问题描述

我有一个数据框，其中包含员工信息，例如姓名，Performance_factor_1和Performance_factor_2.

I have a dataframe that contains employee information such as Name, performance_factor_1 and performance_factor_2 .

我还有另一个数据框，可以根据Performance_factor_1和Performance_actor_2来获得报酬.

I have another dataframe where I get pay based on performance_factor_1 and performance_actor_2.

df1

Name                  pf1       pf2     pf3
Adam                  14.6      8.9     59 
Bob                   13.2      9       75
Charlie               11.1      9.1     89
Dylan                 14.6      9       97
Eric                  11.1      8.8     105
Fedderick             12.5      9.2     69

df2 数据框2的行为performance_factor_1，列为performance_factor_2.

df2 The rows of dataframe 2 are performance_factor_1 and columns are performance_factor_2.

pf1       8.8-8.9  9.0-9.2 9.3-9.4  9.5-9.6  9.7-10
11.1 to 14  100      200    300       400     500
8.1 to 11   200      300    400       500     600
6.1 to 8    300      400    500       600     700
below 6     400      500    600       700     800     

我想做的是，如果p3大于70，则向df1添加第三列工资，如下所示: df1

What I want to do is add a third column pay to df1 such as below if p3 is above 70: df1

Name                  pf1       pf2      pay
Adam                  14.6      8.9      200
Bob                   13.2      9        400
Charlie               11.1      9.1      700
Dylan                 14.6      9        300
Eric                  11.1      8.8      400
Fedderick             12.5      9.2      700

我在较早的帖子中尝试过的操作

What I have tried in my earlier post How to create Column C on DF1 using values from DF2 using Column A and B is to physically list out 14,13.9 --- 0.1,0 for the pf1 index and list out 8.8, 8.9 -- 10 and then using lookup to match the exact pf1 and pf2 values. However, that is not fit for the long term in case df2 changes in which there will be a ton of manual work related to changing most values of df2.

这是我尝试在精确值匹配查找方法中使用的代码:

This is the code I tried to use for in the exact value match lookup method:

df_outer.reset_index(inplace=True)

df3 = indiv.rename(index= lambda x: int(x * 10),
                 columns= lambda x: int(float(x) * 10))
out= []
for row, col in zip(df_outer['TTR'].mul(10).astype(int), df_outer['CSAT (NSE)'].mul(10).astype(int)):
    try:
        out.append(df3.at[row, col] )
    except KeyError:
        out.append(np.nan)

df_outer['Pay'] = out

df_outer.loc[df_outer['# of Closed SRs']>=70, 'Pay_new'] = df_outer['Pay']
print (df_outer)

所以最后，我得到了以下输出.但是它使用的是df2(旧)，我想使用df2(新)获取我的输出

So in the end, I have the below output. But it was using df2(old) and I want to use df2(new) to get my output

       Name   pf1  pf2  pf3  Pay  
0       Adam  14.6  8.9   59  NaN    
1        Bob  13.2  9.0   75  400    
2    Charlie  11.1  9.1   89  700    
3      Dylan  14.6  9.0   97  300    
4       Eric  11.1  8.8  105  400    
5  Fedderick  12.5  9.2   69  NaN    

以前，我的df2(旧)是这样

Previousy,my df2(old) was like this

pf1     8.8 8.9 9   9.1 9.2
14.6    100 200 300 400 500
13.2    200 300 400 500 600
12.5    300 400 500 600 700
11.1    400 500 600 700 800

现在我希望我的df2(新)像这样

Now I want my df2(new) to be like this

pf1       8.8-8.9  9.0-9.2 9.3-9.4  9.5-9.6  9.7-10
11.1 to 14  100      200    300       400     500
8.1 to 11   200      300    400       500     600
6.1 to 8    300      400    500       600     700
below 6     400      500    600       700     800   

我的df 2在csv中看起来像这样:

Edit 2: My df 2 looks like in this in the csv:

推荐答案

在这里可以通过

Here is possible create IntervalIndex by IntervalIndex.from_tuples in columns and index in df2 DataFrame and then change lookup with IntervalIndex.get_loc:

第一次测试:

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

这篇关于使用df1中的值从df2中检索值，其中df2列和索引包含一系列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！