如何根据另一个DataFrame中的条件创建一个新列？

Question

df1:

Variables     left      right
0  AUM           -0.001    28.20
1  AUM           28.20     40.28
2  AUM           40.28     58.27
3  AUM           58.27     80.72
4  AUM           80.72     100.00
0  ABS           -88.01    200.72
1  ABS           200.72    480.72
2  ABS           480.72    800.20
0  LS            10000     200000
1  LS            200000    400000

df2:
   
    Pan_no     ABS      AUM     LS      
0   AAA        28        30    10001      
2   CCC        500       98    390000     
1   BBB        250       50    150000     
3   DDD        100       60    380000     
4   EEE         88       10    378347   
  

条件：

根据df1中的左值和右值，在df2中创建一个新列，并将该列中的值设置为该特定变量的df1索引。

例如：如果在df2中，AUM值落在此范围内（-0.001 - 28.20），则新列将具有df1的索引值作为新值。即0。

同样地，

如果在df2中ABS值在此范围内（200.72 - 480.72），则新的ABS_BIN列将具有df1的索引值作为新值。即1。

我尝试过的方法是：

binning_vars = ['ABS','AUM','LS']
def f(row):
  for i in binning_vars:
      for j in df1[df1['Variable'] == i].index:
            if df1[i] >= df1['left'] & df1[i] >= df1['right']:
                value = j
            else:
                pass
            return value
df2[i,'_bin'] = df1.apply(f, axis=1)

但是它会抛出一个错误，TypeError: 无法对 'float' 和 'float' 类型执行 & 操作。非常感谢您的任何帮助。

Expected Output:
with new columns in df2:
    
    Pan_no     ABS      AUM     LS      ABS_BIN    AUM_BIN     LS_BIN
0   AAA        28        30    10001      0          1           0
1   BBB        250       50    150000     1          2           0
2   CCC        500       98    390000     2          4           1
3   DDD        100       60    380000     0          3           1
4   EEE         88       10    378347     0          0           1

- tamil selvi

---

良好的文档化问题和代码。+1 - Corralien

在下面的讨论中，你的数据集有多大？有多少行，有多少个变量？ - Corralien

5万行，10个变量 - tamil selvi

你能提供实际数据集吗？还是你的数据是保密的？ - Corralien

it's confidential :) - tamil selvi

那么我们将无法评估最佳解决方案 :) 祝你有美好的一天。 - Corralien

Answer 1

你可以使用pd.cut，并避免在 binning 函数内使用循环：

def binning(sr):
    df = df1.loc[df1['Variables'] == sr.name, ['left', 'right']]
    bins = sorted(set(df.to_numpy().ravel()))
    return pd.cut(sr, bins=bins, labels=df.index)

out = df2[binning_vars].apply(binning).add_suffix('_BIN')
df2 = pd.concat([df2, out], axis=1)

输出：

>>> df2
  Pan_no  ABS  AUM      LS ABS_BIN AUM_BIN LS_BIN
0    AAA   28   30   10001       0       1      0
2    CCC  500   98  390000       2       4      1
1    BBB  250   50  150000       1       2      0
3    DDD  100   60  380000       0       3      1
4    EEE   88   10  378347       0       0      1

- Corralien

---

@sammywemmy。谢谢。现在我已经在函数内指定了。相对于行的循环，对几列的循环没有影响。 - Corralien

Answer 2

你可以使用 merge_asof 来避免使用 apply：

out = df2.merge(
 pd.merge_asof((df2.melt(id_vars='Pan_no')
                   .astype({'value': float})
                   .sort_values(by='value')
                ),
               df1.reset_index().sort_values(by='left'),
               left_by='variable', right_by='Variables',
               left_on='value', right_on='left', direction='backward')
  .pivot(index='Pan_no', columns='variable', values='index')
  .add_suffix('_BIN'),
    left_on='Pan_no', right_index=True
)

输出：

  Pan_no  ABS  AUM      LS  ABS_BIN  AUM_BIN  LS_BIN
0    AAA   28   30   10001        0        1       0
2    CCC  500   98  390000        2        4       1
1    BBB  250   50  150000        1        2       0
3    DDD  100   60  380000        0        3       1
4    EEE   88   10  378347        0        0       1

- mozway

---

@sammywemmy 我正要测试它，我也不指望有很大的差异，因为这需要融合/旋转/合并。我主要是为了好玩而已 :p - mozway

@sammy，速度差不多，apply稍微快一点（3.9 vs 4.1 毫秒），但由于重复项，我不确定如何在更大的数据集上进行测试。 - mozway

@sammywemmy 是的，但缺点是如果有重复/重叠会导致大小增加。 - mozway

@Corralien 不要生气，我不是在挑选答案，我已经点赞了你的回答 :p - mozway

@Corralien 不用担心，我知道的（这就是为什么有 :p） - mozway