Python 在 Pandas 中的列之间搜索范围内的值(不是日期列和没有 sql)

发布于05月14日

提前谢谢你的帮助.我有两个数据帧

size=pd.DataFrame({"Min Size":[30,41,40],
                   "Max Size":[40, 60, 50],
                   "Category":['small', 'big', "medium"],
                   "Product":['Apple', 'Apple', "Peach"]})
sold=pd.DataFrame({"Purchase_date":["20/01/2020", "18/02/2020", "01/06/2020"],
                          "Size":[35, 45, 42],
                          "Category":["small","big","medium"],
                          "Product":['Apple', 'Peach', "Apple"]})

推荐答案

Pandas 的加入条件必须完全匹配.它没有SQL中的BETWEEN ... AND ...子句.

可以使用numpy广播将sold中的每一行与size中的每一行进行比较，并筛选匹配项:

# Converting everything to numpy for comparison
sold_product = sold["Product"].to_numpy()[:, None]
sold_size = sold["Size"].to_numpy()[:, None]

product, min_size, max_size = size[["Product", "Min Size", "Max Size"]].T.to_numpy()

# Compare every row in `sold` to every row in `size`.
# `mask` is a len(sold) * len(size) matrix whose value
# indicate if row i in `sold` matches row j in `size`
mask = (sold_product == product) & (min_size <= sold_size) & (sold_size <= max_size)

# For each row in `sold`, find the first row in `size` that
# is True / non-zero
idx, join_key = mask.nonzero()
sold.loc[idx, "join_key"] = join_key

# Result
sold.merge(
    size[["Category"]],
    how="left",
    left_on="join_key",
    right_index=True,
    suffixes=("_Expected", "_Actual"),
)