Remove/ filter rows in JSON based on condition with python-CodePudding

I have a table in a JSON whereby I need an entire row to be deleted/ filtered based on the condition if "Disposition (Non Open Market)" in "transactionType" then delete/ filter entry in all columns. Below is what my JSON file looks like:

{
    "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"
    }
}

My current code is the attempt to delete/ filter out an entry if the value is "Disposition (Non Open Market)":

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))

The preferred output JSON (showing the entry being deleted on all 3 columns):

{
    "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"
    }
}

CodePudding user response：

I was able to remove according to value by appending the keys that have the string value to a list and then simply removing it

import json

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

delete_keys = []

for value in data['transactionType']:
    if data['transactionType'][value] == 'Disposition (Non Open Market)':
        delete_keys.append(value)

print(delete_keys)

for key in delete_keys:
    del data['transactionType'][key]
    del data['lastDate'][key]
    del data['sharesTraded'][key]

print(data)

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

CodePudding user response：

data = {
    "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",
    },
    "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",
    },
    "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",
    }
}

for k,v in data["transactionType"].copy().items():
    if v == "Disposition (Non Open Market)":
        for key in data: # Remove the key from all other nested dictionaries 
            del data[key][k]

print(data)