Automating Valuation System Mapping Tests with Python and Pandas

Background : In a C2B2C business model, the valuation system provides price estimates for recycling orders, and its stability and accuracy are critical. The system relies on many mapping relationships (SKU attributes, inspection items, valuation levels, etc.) that were previously tested manually, leading to high effort and low repeatability.

Goal : Simplify repetitive tasks and handle complex ones carefully by automating the manual Excel‑based mapping verification, enabling new QA members to quickly test valuation cases and improve overall team efficiency.

Research : Two common Excel filtering approaches were evaluated – native Excel functions (convenient but not reusable) and Python pandas (powerful, shareable, but requires scripting). Pandas was chosen for its extensibility and ease of integration.

Implementation :

1. Idea : Replace manual Excel filtering with a Python script that reads, filters, and writes back the required data.

2. Process :

(1) Import required libraries:

import pandas as pd
import numpy as np

(2) Read the SKU‑attribute mapping template:

spu_sku_template_excel = pd.read_excel('C:/Users/Administrator/Downloads/spu_sku映射.xlsx',
    sheet_name="SKU属性映射模板",
    dtype={'外部参数值Id（若有多个请使用+分隔）': np.str_})

(3) Filter by equality:

level_price_item = level_price_template.loc[level_price_template['型号'] == model_name]

(4) Filter by inclusion:

foreign_template_item = foreign_template_excel.loc[foreign_template_excel['外部检测值id'].isin(foreign_id)]

(5) Write results back to Excel:

valuation_item_result_writer = pd.ExcelWriter('C:/Users/Administrator/Downloads/估价项列表导出筛选结果_数码.xlsx')
valuation_item_result_df.to_excel(valuation_item_result_writer,
    sheet_name='筛选之后的', index=False)
valuation_item_result_writer.close()

(6) Use pandas data structures for further processing, e.g., extracting unique memory capacities:

# 运存容量 list
sku_capacity_list = sku_template_result['运存容量']
sku_capacity_series = pd.Series(sku_capacity_list.values).drop_duplicates()

(7) Filter valuation items by third‑level ID and rebuild DataFrame:

valuation_item_result_df = valuation_item_excel.loc[valuation_item_excel['3级类id'].isin(foreign_template_item_result_arr)]
valuation_item_result_df = pd.DataFrame(valuation_item_result_df.values)
valuation_item_result_df.columns = valuation_item_excel.columns

3. Result : The automated script reduces the manual Excel filtering time for a valuation case from about 10 minutes to less than 1 minute, dramatically cutting effort while increasing coverage and accuracy.

4. Future Work : Provide a front‑end UI to display comparison results, enable one‑click Excel download, and further integrate the tool into the valuation pipeline.

Conclusion : Repeating the same business tests manually is inefficient; automating these steps with Python and pandas exemplifies the principle “make complex tasks simple, handle tedious tasks with care,” and is an essential skill for QA professionals seeking higher efficiency.