What is the fastest way to filter over 2.5gb Json file?

user_11651988 0
Viewed 698

I have 2.5 GB of JSON file with 25 columns and about 4 million rows. I try to filter the JSON with the following script it takes at least 10 minutes.

import json

product_list = ['Horse','Rabit','Cow']
year_list = ['2008','2009','2010']
country_list = ['USA','GERMANY','ITALY']

with open('./products/animal_production.json', 'r', encoding='utf8') as r:
     result = r.read()
result = json.loads(result)

for item in result[:]:
    if (not str(item["Year"]) in year_list) or (not item["Name"] in product_list) or (not item["Country"] in country_list):
        result.remove(item)
print(result)

I need to prepare the result in a max of 1 minute so what is your suggestion or the fastest way to filter JSON?

python json
3 Answers

Removing from a list in a loop is slower, each remove is O(n) and that is done n times so O(n^2), appending to a new list is O(1) and doing this n times is O(n) in a loop. So you can try this

[item for item in result if str(item["Year"] in year_list) or (item["Name"] in product_list) or (item["Country"] in country_list)]

Filter based on the condition you need and add only those that match.

user_125029590

You need to read the json file using Pandas dataframes, and then filter on the required columns.

Why ? because Pandas is column-based and therefore it's super fast for working with columns, it's built upon Series which is a one-dimensional labeled array (basically the column).

So you need something like that: (Assuming column names in json file are consistent)

import pandas as pd

product_list = ['Horse','Rabit','Cow']
year_list = ['2008','2009','2010']
country_list = ['USA','GERMANY','ITALY']

df = pd.read_json('./products/animal_production.json')

# Change the condition if it's not the desired one
condition = (df["Year"].isin(year_list) | (df["Name"].isin(product_list) | (df["Country"].isin(country_list)

df = df[condition]

I can't reproduce it to estimate the time needed but I am sure it would be hundreds or even thousands of times faster!

user_155921620

I do not know if it will be much faster, but you might json.load rather than read-ing then json.loadsing i.e. rather than

with open('./products/animal_production.json', 'r', encoding='utf8') as r:
     result = r.read()
result = json.loads(result)

you might do

with open('./products/animal_production.json', 'r', encoding='utf8') as r:
     result = json.load(r)
user_107859750
Related