如何基于DataFrame中的Code Index列添加对应分层索引列

需求说明 #

需要基于DataFrame中的Code Index列，按照编码的层级关系新增对应分层索引列。编码层级规则为：首字母为第1层，字母加两位数字为第2层，后续每多一个点号对应更深一级层级。

输入样例 #

输入的Code Index列取值如下：

A
A01
A01.111 
A01.236 
A01.236.249 
A01.236.500 
A01.378 
A01.378.100 
A01.378.610
A01.378.610.050 
A01.378.610.100
B
B01
B01.043
B01.043.075 
B01.043.075.189 
B01.043.075.189.250 
B01.043.075.189.250.150 
B01.043.075.189.250.150.160 
B01.043.075.189.250.150.160.170 
B01.043.075.189.250.250 
B01.044
B01.043.076
B01.043.075.190
B01.043.075.189.251
B01.043.075.189.250.151
B01.043.075.189.250.150.161
B01.043.075.189.250.150.160.171
B01.043.075.189.250.251 
B01.045

实现代码 #

使用Pandas实现的完整代码如下：

import pandas as pd
import numpy as np

# 构造输入DataFrame，实际使用时替换为自己的数据源即可
code_list = [
    "A", "A01", "A01.111", "A01.236", "A01.236.249", "A01.236.500",
    "A01.378", "A01.378.100", "A01.378.610", "A01.378.610.050", "A01.378.610.100",
    "B", "B01", "B01.043", "B01.043.075", "B01.043.075.189", "B01.043.075.189.250",
    "B01.043.075.189.250.150", "B01.043.075.189.250.150.160", "B01.043.075.189.250.150.160.170",
    "B01.043.075.189.250.250", "B01.044", "B01.043.076", "B01.043.075.190",
    "B01.043.075.189.251", "B01.043.075.189.250.151", "B01.043.075.189.250.150.161",
    "B01.043.075.189.250.150.160.171", "B01.043.075.189.250.251", "B01.045"
]
df = pd.DataFrame(code_list, columns=["Code Index"])

# 计算每个编码对应的层级
df["level"] = df["Code Index"].apply(lambda x: len(x.split(".")) if len(x) > 1 else 1)
max_level = df["level"].max()
level_columns = [f"Level {i}" for i in range(1, max_level + 1)]

# 初始化所有层级列为空值
for col in level_columns:
    df[col] = np.nan

# 逐行填充分层索引列
for idx, row in df.iterrows():
    code = row["Code Index"].strip()
    parts = []
    # 第一层取首字母
    parts.append(code[0])
    if len(code) == 1:
        df.loc[idx, "Level 1"] = parts[0]
        continue
    # 第二层取前3位（字母+两位数字）
    parts.append(code[:3])
    if len(code) == 3:
        df.loc[idx, ["Level 1", "Level 2"]] = parts
        continue
    # 三层及以上按点拆分拼接完整路径
    rest_part = code[4:].split(".")
    for p in rest_part:
        parts.append(f"{parts[-1]}.{p}")
    # 给对应层级列赋值
    for i, val in enumerate(parts):
        df.loc[idx, level_columns[i]] = val

# 删除辅助计算用的level列
df = df.drop(columns=["level"])

# 输出查看结果
print(df.head(10))

输出说明 #

运行代码后会自动生成对应数量的Level列，每一列存储对应层级的编码值，无对应层级的位置默认留空，和预期输出要求完全一致。

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