如何用Pandas DataFrame解析JSON日志并提取所有条目到指定列？

问题背景 #

指定目录下存有多个JSON日志文件，编写Python脚本解析所有文件，将JSON中指定键值映射到自定义列。目前字段匹配正常，但脚本仅能保留每个文件中的最后一条日志（表现为每个文件对应DataFrame一行），无法解析文件内全部日志条目。

原代码 #

import os, json
import pandas as pd

#Read files from Log directory
path_to_json = r'C:\TestLogfiles'
json_files = [pos_json for pos_json in os.listdir(path_to_json) if pos_json.endswith('.json')]
print(json_files)  

# Define Columns within Log files
jsons_data = pd.DataFrame(columns=['category', 'description', 'created_date', 'host', 'machine', 'user_id', 'error_code', 'process', 'thread'])

# we need both the json and an index number so use enumerate()
for index, js in enumerate(json_files):
    with open(os.path.join(path_to_json, js)) as json_files:
        json_text = json.load(json_files)
        for logMessages in json_text['logMessages']:

            #Nav thru the log entry to return a parsed list of these items
            category = logMessages['type']
            description = logMessages['message']
            created_date = logMessages['time']
            host = logMessages['source']
            machine = logMessages['machine']
            user_id = logMessages['user']
            error_code = logMessages['code']
            process = logMessages['process']
            thread = logMessages['thread']

            #push list of data into pandas DFrame at a row given by 'index'

            jsons_data.loc[index] = [category, description, created_date, host, machine, user_id, error_code, process, thread]
            
print(jsons_data)

JSON文件示例 #

{
    "hasMore": true,
    "startTime": 1663612608354,
    "endTime": 1662134983365,
    "logMessages": [
        {
            "type": "DEBUG",
            "message": "Health check took 50 ms.",
            "time": 1663612608354,
            "source": "Portal Admin",
            "machine": "machineName.domain.com",
            "user": "",
            "code": 9999,
            "elapsed": "",
            "process": "6966",
            "thread": "1",
            "methodName": "",
            "requestID": ""
        },
        {
            "type": "DEBUG",
            "message": "Checking the Sharing API took 12 ms.",
            "time": 1663612608354,
            "source": "Portal Admin",
            "machine": "machineName.domain.com",
            "user": "",
            "code": 9999,
            "elapsed": "",
            "process": "6966",
            "thread": "1",
            "methodName": "",
            "requestID": ""
        }
    ]
}

原运行结果 #

['PortalLog.json', 'Testlog.json']
  category                                        description   created_date                              host             machine      user_id error_code process thread
0  WARNING   Failed to delete service with service URL 'ht...  1662134983365                           Sharing  MachineName.domain.com  portaladmin     200007   11623      1
1   SEVERE  Error executing tool. Export Web Map Task : Fa...  1657133904189  Utilities/PrintingTools.GPServer  MachineName.domain.com  portaladmin      20010   25561    161

问题原因 #

核心问题是使用jsons_data.loc[index]赋值时，index是文件的枚举索引（每个文件对应唯一index值）。遍历单个文件内的多条日志时，每次循环都会覆盖同一个index对应的行，最终每个文件仅保留最后一条日志数据。

修复方案 #

采用先收集所有日志数据到列表，最后一次性生成DataFrame的方式，既避免索引覆盖问题，又提升运行效率（pandas循环append性能较差，列表收集更高效）。

修复后的代码 #

import os
import json
import pandas as pd

# 日志目录路径
path_to_json = r'C:\TestLogfiles'
# 获取目录下所有JSON文件
json_files = [f for f in os.listdir(path_to_json) if f.endswith('.json')]
print(json_files)

# 用于存储所有日志数据的列表
log_data = []

# 遍历每个JSON文件
for js_file in json_files:
    file_path = os.path.join(path_to_json, js_file)
    with open(file_path, 'r', encoding='utf-8') as f:
        json_content = json.load(f)
        # 遍历当前文件内的所有日志条目
        for log_entry in json_content['logMessages']:
            # 提取需要的字段并构造字典
            entry_dict = {
                'category': log_entry['type'],
                'description': log_entry['message'],
                'created_date': log_entry['time'],
                'host': log_entry['source'],
                'machine': log_entry['machine'],
                'user_id': log_entry['user'],
                'error_code': log_entry['code'],
                'process': log_entry['process'],
                'thread': log_entry['thread']
            }
            # 将字典添加到数据列表
            log_data.append(entry_dict)

# 从列表生成DataFrame
jsons_data = pd.DataFrame(log_data)
print(jsons_data)

修复后效果 #

运行脚本后，每个文件内的所有日志条目都会被提取并添加到DataFrame中，不会出现覆盖或遗漏。例如示例JSON文件中的两条DEBUG日志都会被正常解析，最终DataFrame会包含对应行数的记录。

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