如何用Python按条件过滤JSON行：移除指定transactionType条目

问题需求 #

过滤JSON格式的表格数据，当transactionType字段的值为"Disposition (Non Open Market)"时，删除该条目对应的所有列数据。

原始JSON数据 #

{"lastDate":{"0":"11\\/22\\/2022","1":"10\\/28\\/2022","2":"10\\/17\\/2022","3":"10\\/15\\/2022","4":"10\\/15\\/2022","5":"10\\/15\\/2022","6":"10\\/15\\/2022","7":"10\\/03\\/2022","8":"10\\/03\\/2022","9":"10\\/03\\/2022","10":"10\\/01\\/2022","11":"10\\/01\\/2022","12":"10\\/01\\/2022","13":"10\\/01\\/2022","14":"10\\/01\\/2022","15":"10\\/01\\/2022","16":"10\\/01\\/2022","17":"10\\/01\\/2022","18":"08\\/17\\/2022","19":"08\\/08\\/2022","20":"08\\/05\\/2022","21":"08\\/05\\/2022","22":"08\\/03\\/2022","23":"05\\/06\\/2022","24":"05\\/04\\/2022"},"transactionType":{"0":"Sell","1":"Automatic Sell","2":"Automatic Sell","3":"Disposition (Non Open Market)","4":"Option Execute","5":"Disposition (Non Open Market)","6":"Option Execute","7":"Automatic Sell","8":"Sell","9":"Automatic Sell","10":"Disposition (Non Open Market)","11":"Option Execute","12":"Disposition (Non Open Market)","13":"Option Execute","14":"Disposition (Non Open Market)","15":"Option Execute","16":"Disposition (Non Open Market)","17":"Option Execute","18":"Automatic Sell","19":"Automatic Sell","20":"Disposition (Non Open Market)","21":"Option Execute","22":"Automatic Sell","23":"Disposition (Non Open Market)","24":"Automatic Sell"},"sharesTraded":{"0":"20,200","1":"176,299","2":"8,053","3":"6,399","4":"13,136","5":"8,559","6":"16,612","7":"167,889","8":"13,250","9":"176,299","10":"177,870","11":"365,600","12":"189,301","13":"365,600","14":"184,461","15":"365,600","16":"189,301","17":"365,600","18":"96,735","19":"15,366","20":"16,530","21":"31,896","22":"25,000","23":"1,276","24":"25,000"}}

尝试的Python代码（存在逻辑错误） #

import json

data = json.load(open("AAPL22_institutional_table_MRKTVAL.json"))

modified = lambda feature: 'Disposition (Non Open Market)' not in feature['transactionType']
data2 = filter(modified, data)

open("AAPL22_institutional_table_MRKTVAL.json", "w").write(
    json.dumps(data2, indent=4))

期望输出JSON #

{"lastDate":{"0":"11\\/22\\/2022","1":"10\\/28\\/2022","2":"10\\/17\\/2022","4":"10\\/15\\/2022","6":"10\\/15\\/2022","7":"10\\/03\\/2022","8":"10\\/03\\/2022","9":"10\\/03\\/2022","11":"10\\/01\\/2022","13":"10\\/01\\/2022","15":"10\\/01\\/2022","17":"10\\/01\\/2022","18":"08\\/17\\/2022","19":"08\\/08\\/2022","21":"08\\/05\\/2022","22":"08\\/03\\/2022","24":"05\\/04\\/2022"},"transactionType":{"0":"Sell","1":"Automatic Sell","2":"Automatic Sell","4":"Option Execute","6":"Option Execute","7":"Automatic Sell","8":"Sell","9":"Automatic Sell","11":"Option Execute","13":"Option Execute","15":"Option Execute","17":"Option Execute","18":"Automatic Sell","19":"Automatic Sell","21":"Option Execute","22":"Automatic Sell","24":"Automatic Sell"},"sharesTraded":{"0":"20,200","1":"176,299","2":"8,053","4":"13,136","6":"16,612","7":"167,889","8":"13,250","9":"176,299","11":"365,600","13":"365,600","15":"365,600","17":"365,600","18":"96,735","19":"15,366","21":"31,896","22":"25,000","24":"25,000"}}

代码错误分析 #

原代码的核心问题是对JSON结构理解有误：

• 加载后的data是字典，键为列名（lastDate、transactionType等），值是索引到具体值的子字典，而非每行数据组成的列表。
• 使用filter(modified, data)时，遍历的是字典的键（列名），此时feature是字符串（如"lastDate"），调用feature['transactionType']会直接抛出KeyError。

正确的Python代码 #

import json

# 读取原始数据
with open("AAPL22_institutional_table_MRKTVAL.json", "r") as f:
    data = json.load(f)

# 筛选需要保留的索引
keep_indices = [idx for idx, trans_type in data["transactionType"].items() 
                if trans_type != "Disposition (Non Open Market)"]

# 对每个列过滤出保留的索引对应的数据
filtered_data = {}
for col_name, col_data in data.items():
    filtered_data[col_name] = {idx: val for idx, val in col_data.items() if idx in keep_indices}

# 写入过滤后的结果
with open("AAPL22_institutional_table_MRKTVAL.json", "w") as f:
    json.dump(filtered_data, f, indent=4)

代码说明 #

1. 先遍历transactionType列的索引和值，筛选出需要保留的索引（交易类型不是目标值的条目）。
2. 对每个列数据，仅保留筛选出的索引对应的键值对，生成新的列数据。
3. 使用with语句处理文件，确保文件正确关闭，避免资源泄漏。

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