python - Pandas 数据框列值与列表中的元素明智数字比较

Question

我有 3 个 pandas 多索引列数据框

数据框 1(最小值):

  |  A    |   B   |  C   |
  |  Min  |  Min  |  Min |
  |-------|-------|------|
0 | 26.47 | 17.31 | 1.26 |
1 | 27.23 | 14.38 | 1.36 |
2 | 27.23 | 18.88 | 1.28 |

dataframe 2(用于比较的值)

第 0 行、第 1 行和第 2 行相同，我将数据帧扩展为三行，以便与最小和最大数据帧进行比较。每个数据框单元格中的值是 ndarray

  |          A          |           B           |          C         |
  |         Val         |          Val          |         Val        |
  |---------------------|-----------------------|--------------------|
0 | [27.58,28.37,28.73] | [17.31, 18.42, 18.72] | [1.36, 1.28, 1.27] |
1 | [27.58,28.37,28.73] | [17.31, 18.42, 18.72] | [1.36, 1.28, 1.27] |
2 | [27.58,28.37,28.73] | [17.31, 18.42, 18.72] | [1.36, 1.28, 1.27] |

数据框 3(最大值):

  |  A    |   B   |  C   |
  |  Max  |  Max  |  Max |
  |-------|-------|------|
0 | 28.68 | 18.42 | 1.37 |
1 | 29.50 | 17.31 | 1.47 |
2 | 29.87 | 20.45 | 1.39 |

预期结果:

  |          A          |           B           |          C           |
  |        Result       |          Result       |         Result       |
  |---------------------|-----------------------|----------------------|
0 | [True, True, False] |  [True, True, False]  | [True, True, True]   |
1 | [True, True, True]  | [True, False, False]  | [True, False, False] |
2 | [True, True, True]  | [False, False, False] | [True, True, False]  |

我想以这种方式执行元素明智的比较:

min <= each element in ndarray <= max

即

for row 0:

26.47 <= [27.58,28.37,28.73] <= 28.68

17.31 <= [17.31, 18.42, 18.72] <= 18.42

1.26 <= [1.36, 1.28, 1.27] <= 1.37

等等

我试过了 ( datafram2 >= dataframe3 ) & ( datafram2 <= datafram3 )但不起作用。

计算结果的最简单和最快的方法是什么？

示例数据框代码:

min_columns = pd.MultiIndex.from_product( [ [ 'A', 'B', 'C' ], [ 'Min' ] ] )
val_columns = pd.MultiIndex.from_product( [ [ 'A', 'B', 'C' ], [ 'Val' ] ] )
max_columns = pd.MultiIndex.from_product( [ [ 'A', 'B', 'C' ], [ 'Max' ] ] )

min_df = pd.DataFrame( [ [ 26.47, 17.31, 1.26 ], [ 27.23, 14.38, 1.36 ], [ 27.23, 18.88, 1.28 ] ], columns=min_columns )
val_df = pd.DataFrame( [ [ [ 27.58, 28.37, 28.73 ], [ 17.31, 18.42, 18.72], [1.36, 1.28, 1.27 ] ] ] , columns=val_columns )
max_df = pd.DataFrame( [ [ 28.68, 18.42, 1.37 ], [ 29.50, 17.31, 1.47 ], [ 29.87, 20.45, 1.39 ] ] , columns=max_columns )

Answer 1

只需将列值转换为 NumPy 数组。并将其简单地视为数组比较问题(按行)。

你可以使用apply:

def bool_check(row):
    col = row.name[0]
    min_val = df1[pd.IndexSlice[col]].to_numpy()
    max_val = df3[pd.IndexSlice[col]].to_numpy()
    x = np.array(row.tolist())
    return list((x >= min_val) & (x <= max_val))

---

res = df2.apply(bool_check,axis=0).rename(columns={'Val':'Result'})

---

资源:

<表类="s-表"><头>ABC<正文>结果结果结果0[真，真，假][真，真，假][真，真，真]1[真，真，真][真、假、假][真、假、假]2[真，真，真][假，假，假][真，真，假]

更新

(基于您提供的数据的完整解决方案):

def bool_check(row):
    col = row.name[0]
    min_val = min_df[pd.IndexSlice[col]].to_numpy()
    max_val = max_df[pd.IndexSlice[col]].to_numpy()
    x = np.array(row.tolist())
    return list((x >= min_val) & (x <= max_val))

res = val_df.apply(bool_check,axis=0).rename(columns={'Val':'Result'})

---

时间比较:

方法一(Nk03的method1):

CPU times: user 19.5 ms, sys: 0 ns, total: 19.5 ms Wall time: 18.9 ms

方法二(Nk03的method2):

CPU times: user 23 ms, sys: 102 µs, total: 23.1 ms Wall time: 21.9 ms

方法 3(使用基于 numpy 的比较):

CPU times: user 8.76 ms, sys: 26 µs, total: 8.79 ms Wall time: 8.91 ms

Nk03更新优化方案:

CPU times: user 16 ms, sys: 0 ns, total: 16 ms Wall time: 15.5 ms