如何将特定格式字符串转换为Python DataFrame？

问题描述 #

给定如下格式的字符串：

string_1 = '{!Cat1!:{!A!:!xd!# !B!:!yd!# !C!:!zd!# !D!:[!nd!#!nd2!#!nd3!#!nd4!]# !E!:!hd!}#!Cat2!:{!A1!:!xd1!# !B1!:!yd1!# !C1!:!zd1!# !D1!:[!nd1!#!nd21!#!nd31!#!nd41!]# !E1!:!hd1!}}'

需要将其转换为如下结构的DataFrame：

Col1 Col2 Col3
Cat1  A    xd
Cat1  B    yd
Cat1  C    zd
Cat1  D    nd
Cat1  D    nd2
Cat1  D    nd3
Cat1  D    nd4
Cat1  E    hd
Cat2  A1   xd1
Cat2  B1   yd1
Cat2  C1   zd1
Cat2  D1   nd1
Cat2  D1   nd21
Cat2  D1   nd31
Cat2  D1   nd41
Cat2  E1   hd1

用户尝试了以下代码，但未得到预期结果：

new_String = string_1 .replace('!', '"').replace('#', ',')
new_String = json.loads(new_String )
new_String_df = pd.DataFrame.from_dict(new_String)

问题分析 #

直接将转换后的JSON用from_dict生成DataFrame，得到的是宽表结构（每个大类作为一列），且数组会被保留为单元格内的列表，不会自动展开为多行，因此不符合需求。需要先将嵌套的JSON数据展开为长表格式，同时处理数组元素的拆分。

正确实现步骤 #

1. 将自定义格式字符串转换为合法JSON #

先把原字符串的自定义分隔符替换为JSON格式符号，同时修正JSON语法错误（比如末尾多余的逗号）：

import json
import pandas as pd
import re

string_1 = '{!Cat1!:{!A!:!xd!# !B!:!yd!# !C!:!zd!# !D!:[!nd!#!nd2!#!nd3!#!nd4!]# !E!:!hd!}#!Cat2!:{!A1!:!xd1!# !B1!:!yd1!# !C1!:!zd1!# !D1!:[!nd1!#!nd21!#!nd31!#!nd41!]# !E1!:!hd1!}}'

# 替换自定义分隔符为JSON格式符号
json_str = string_1.replace('!', '"').replace('#', ',')
# 修正JSON语法错误：移除数组和字典末尾的多余逗号
json_str = re.sub(r',\s*]', ']', json_str)
json_str = re.sub(r',\s*}', '}', json_str)

# 加载JSON数据
data = json.loads(json_str)

2. 展开嵌套数据为目标结构 #

遍历JSON中的每个大类和键值对，将数组元素拆分为多行，最终收集所有行数据并转换为DataFrame：

# 初始化列表存储每行数据
rows = []

# 遍历每个大类（Col1）
for col1, items in data.items():
    # 遍历大类下的每个键值对（Col2和对应值）
    for col2, value in items.items():
        if isinstance(value, list):
            # 若值为数组，每个元素单独生成一行
            for val in value:
                rows.append({"Col1": col1, "Col2": col2, "Col3": val})
        else:
            # 普通值直接生成一行
            rows.append({"Col1": col1, "Col2": col2, "Col3": value})

# 转换为目标DataFrame
df = pd.DataFrame(rows)
print(df)

执行上述代码后，即可得到符合要求的长表结构DataFrame。

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