基于DataFrame的Number列数值整理字典列表并处理换行问题

给定如下数据集：

Name  Notes  Source            Number  Info
0    Bob    NaN     NaN              g:45   NaN
1  Billy    1.0    Home             B:+67   NaN
2  Billy    1.0    Work               B:3   NaN
3  Billy    NaN     NaN  hhtps://uishiufb   NaN
4  Billy    0.0  School             V9:67   NaN
5   Eric    0.0     NaN             R:+35   NaN
6   Eric    NaN    Home            f-g:35   NaN

需求说明：

• 基于Number列的纯数字对行分组，相同数字的行合并为一个字典（忽略数字前的标签，如B:+、V9这类前缀）
• 类似URL的特殊内容需单独生成字典
• 处理单元格中的换行符：输出到Excel时将\n替换为逗号
• 最终生成符合要求的字典列表

使用Python结合Pandas实现，代码逻辑清晰，直接处理数据并生成目标结果：

import pandas as pd
import re

# 构造数据集（实际场景可替换为pd.read_csv/pd.read_excel读取文件）
data = [
    ["Bob", pd.NA, pd.NA, "g:45", pd.NA],
    ["Billy", 1.0, "Home", "B:+67", pd.NA],
    ["Billy", 1.0, "Work", "B:3", pd.NA],
    ["Billy", pd.NA, pd.NA, "hhtps://uishiufb", pd.NA],
    ["Billy", 0.0, "School", "V9:67", pd.NA],
    ["Eric", 0.0, pd.NA, "R:+35", pd.NA],
    ["Eric", pd.NA, "Home", "f-g:35", pd.NA]
]
df = pd.DataFrame(data, columns=["Name", "Notes", "Source", "Number", "Info"])

# 提取Number中的纯数字（兼容带+的正数）
def extract_number(s):
    match = re.search(r'([+]?\d+)', str(s))
    return match.group(1) if match else None

# 判断是否为URL（兼容数据中的拼写错误）
def is_url(s):
    return str(s).startswith(('http://', 'https://', 'hhtps://'))

# 拆分URL行和普通行
url_rows = df[df['Number'].apply(is_url)]
normal_rows = df[~df['Number'].apply(is_url)].copy()

# 为普通行添加提取的数字列，用于分组
normal_rows['extracted_num'] = normal_rows['Number'].apply(extract_number)

# 分组合并：按Name和提取的数字聚合Source、Number列表
grouped = normal_rows.groupby(['Name', 'extracted_num']).agg({
    'Source': lambda x: [item for item in x if pd.notna(item)] or [pd.NA],
    'Number': list
}).reset_index()

# 生成普通行的字典
result_dicts = []
for _, row in grouped.iterrows():
    result_dicts.append({
        'Name': row['Name'],
        'Source': row['Source'],
        'Number': row['Number']
    })

# 处理URL行，每个URL单独生成字典
for _, row in url_rows.iterrows():
    result_dicts.append({
        'Name': row['Name'],
        'Source': [row['Source']] if pd.notna(row['Source']) else [pd.NA],
        'Number': [row['Number']]
    })

# 替换所有字符串中的换行符为逗号
def replace_newline(obj):
    if isinstance(obj, str):
        return obj.replace('\n', ',')
    elif isinstance(obj, list):
        return [replace_newline(item) for item in obj]
    return obj

processed_dicts = [replace_newline(d) for d in result_dicts]

# 按示例格式打印结果
for i, d in enumerate(processed_dicts, 1):
    # 统一缺失值显示为Nan
    formatted = {
        k: [str(v).replace('nan', 'Nan') if pd.isna(v) else v for v in val] 
        if isinstance(val, list) else val
        for k, val in d.items()
    }
    source_str = ", ".join(formatted['Source'])
    number_str = ", ".join(formatted['Number'])
    print(f"dict{i} = {{Name: {formatted['Name']}, Source:[{source_str}], Number: [{number_str}]}}")

# 输出到Excel（将列表转为字符串，方便Excel展示）
output_df = pd.DataFrame(processed_dicts)
output_df['Source'] = output_df['Source'].apply(lambda x: ", ".join(map(str, x)).replace('nan', 'Nan'))
output_df['Number'] = output_df['Number'].apply(lambda x: ", ".join(map(str, x)))
output_df.to_excel('result.xlsx', index=False)

执行代码后，会输出与需求匹配的字典列表：

dict1 = {Name: Billy, Source:[Home, School], Number: [B:+67, V9:67]}
dict2 = {Name: Billy, Source:[Work], Number: [B:3]}
dict3 = {Name: Bob, Source:[Nan], Number: [g:45]}
dict4 = {Name: Eric, Source:[Nan, Home], Number: [R:+35, f-g:35]}
dict5 = {Name: Billy, Source:[Nan], Number: [hhtps://uishiufb]}

同时会生成result.xlsx文件，其中所有换行符已替换为逗号。

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