如何读取含JSON结构的文本文件并生成指定格式的DataFrame

你可以通过逐行解析文本、拆分用户字段、解析JSON地址段的方式生成目标DataFrame，以下是可直接运行的代码：

import pandas as pd
import json

def parse_custom_log(file_path):
    result_rows = []
    cur_user_info = None
    cur_json_lines = []
    
    with open(file_path, 'r', encoding='utf-8') as f:
        for line in f:
            stripped_line = line.strip()
            if not stripped_line:
                continue
            
            # 识别新记录的起始行
            if '| homeAddress =' in stripped_line:
                # 保存上一条完整记录
                if cur_user_info and cur_json_lines:
                    addr_dict = json.loads(''.join(cur_json_lines))
                    cur_user_info.update({
                        "city": addr_dict["city"],
                        "housenumber": addr_dict["houseNumber"],
                        "housenumber_addition": addr_dict["houseNumberAddition"],
                        "postalcode": addr_dict["postalCode"],
                        "street": addr_dict["street"],
                        "typename": addr_dict["__typename"]
                    })
                    result_rows.append(cur_user_info)
                
                # 拆分用户名字段
                user_part, addr_start = stripped_line.split('| homeAddress =', 1)
                un1, un2 = user_part.strip().split(':', 1)
                cur_user_info = {"username1": un1.strip(), "username2": un2.strip()}
                cur_json_lines = [addr_start.strip()]
            else:
                # 追加JSON内容行
                cur_json_lines.append(stripped_line)
        
        # 处理最后一条记录
        if cur_user_info and cur_json_lines:
            addr_dict = json.loads(''.join(cur_json_lines))
            cur_user_info.update({
                "city": addr_dict["city"],
                "housenumber": addr_dict["houseNumber"],
                "housenumber_addition": addr_dict["houseNumberAddition"],
                "postalcode": addr_dict["postalCode"],
                "street": addr_dict["street"],
                "typename": addr_dict["__typename"]
            })
            result_rows.append(cur_user_info)
    
    df = pd.DataFrame(result_rows)
    # 如需修正示例中的NEW YOK拼写错误，可取消注释下行
    # df['city'] = df['city'].replace('NEW YOK', 'NEW YORK')
    return df

# 调用示例，替换为你的实际文件路径
df = parse_custom_log("your_file.txt")
print(df)

输出的DataFrame完全匹配你要求的列名和格式，适配你提供的样本数据结构。

内容的提问来源于stack exchange，提问作者teller.py3