如何高效构建SWRS-SWTS映射的Pandas DataFrame

问题说明 #

• 构建目标：生成SWRS（软件需求）-*SWTS（软件测试用例）*追踪矩阵，行索引为项目全量SWRS名称，列索引为项目全量SWTS名称；若某条SWRS被对应SWTS覆盖，在交叉单元格填入标记，后续需支持填入通过、失败、未执行等不同测试状态值
• 现有方案缺陷：先创建空Pandas DataFrame再双层遍历mapping映射字典逐单元格填充值的方式运行效率低；尝试直接传入mapping字典构造DataFrame时，仅能得到全为NaN的空表，无法正确生成映射关系

原有实现代码 #

import pandas as pd
struct = {
            "swrslist":["swrs1","swrs2","swrs3","swrs4"],
            "swtslist":["swts1","swts2","swts3","swts4","swts5","swts6"],
            "mapping":
                {
                    "swrs1": ["swts1", "swts3", "swts4"],
                    "swrs2": ["swts2", "swts3", "swts5"],
                    "swrs4": ["swts1", "swts3", "swts5"]
                }
           }

if __name__ == "__main__":
    df = pd.DataFrame( index = pd.Index(pd.Series(struct["swrslist"])),
                      columns = pd.Index(struct["swtslist"]))
    print(df)
    for key in struct["mapping"].keys():
        for elem in struct["mapping"][key]:
            print(key, elem)
            df.at[key,elem] = "x"
    print(df)
    df.to_excel("mapping.xlsx")

预期正确输出 #

swts1 swts2 swts3 swts4 swts5 swts6
swrs1     x   NaN     x     x   NaN   NaN
swrs2   NaN     x     x   NaN     x   NaN
swrs3   NaN   NaN   NaN   NaN   NaN   NaN
swrs4     x   NaN     x   NaN     x   NaN

错误构造方式：直接执行df = pd.DataFrame(struct["mapping"], index = pd.Index(pd.Series(struct["swrslist"])),columns = pd.Index(struct["swtslist"]))会生成全为NaN的空DataFrame，无法得到正确结果。

高效实现方案 #

核心逻辑为将映射关系转换为长表结构后，通过Pandas内置的透视、重索引方法自动生成矩阵，全程无逐单元格双层遍历，使用向量化运算保证性能，同时天然支持多状态值填充。

实现代码 #

import pandas as pd

struct = {
            "swrslist":["swrs1","swrs2","swrs3","swrs4"],
            "swtslist":["swts1","swts2","swts3","swts4","swts5","swts6"],
            "mapping":
                {
                    "swrs1": ["swts1", "swts3", "swts4"],
                    "swrs2": ["swts2", "swts3", "swts5"],
                    "swrs4": ["swts1", "swts3", "swts5"]
                }
           }

if __name__ == "__main__":
    # 展开映射关系为(需求ID, 用例ID, 标记值)三元组，后续可直接替换标记值为pass/fail/not_run等状态
    map_tuples = []
    for swrs_id, covered_swts in struct["mapping"].items():
        map_tuples.extend([(swrs_id, swts_id, "x") for swts_id in covered_swts])
    
    # 长表转宽表自动生成映射矩阵
    long_df = pd.DataFrame(map_tuples, columns=["swrs", "swts", "mark"])
    df = long_df.pivot(index="swrs", columns="swts", values="mark")
    
    # 补全所有缺失的行、列，保证与全量需求、全量用例列表完全对齐
    df = df.reindex(index=struct["swrslist"], columns=struct["swtslist"])
    
    print(df)
    df.to_excel("rtm_matrix.xlsx")

方案优势 #

• 无逐单元格循环操作，基于Pandas内置向量化逻辑实现，数据规模越大性能优势越明显
• 支持多状态标记：生成三元组时可直接给每个需求-用例对传入对应的测试执行结果值，无需修改后续矩阵生成逻辑
• 自动对齐全量索引：未关联任何用例的需求、未关联任何需求的用例都会自动补全到矩阵中，不会出现行列缺失

内容的提问来源于stack exchange，提问作者Catosh