compare two json files and match entries with the same value

ditrauth 0
Viewed 28

I need compare two json files on keys: a,b,c,d,e

If all keys is match for entry between json file 1 and json file 2 so i should find delta betwen platform_time for this entry. And then delete this entries from json file 1 and json file 2. (both json files have 10000000000 entries ):

So here we should match :

1) one[0] and [two][1] 
2) one[1] and [two][]

Data json one and json two: first file -

"one": [
    {
        "a" : "2022-09-12 00:00:00.000",
        "b" : "apple",
        "c" : "1",
        "d" : "2022-09-11 23:59:59.997",
        "e" : 88
    },
    {
        "a" : "2022-09-12 00:00:00.000",
        "b" : "orange",
        "c" : "2",
        "d" : "2022-09-11 23:59:59.997",
        "e" : 87
    },      
    {
        "a" : "2022-09-12 00:00:10.001",
        "b" : "apple",
        "c" : "6",
        "d" : "2022-09-11 23:59:59.997",
        "e" : 88
    },...]

second file -

"two": [
    {
        "a" : "2022-09-12 00:00:30.000",
        "b" : "orange",
        "c" : "2",
        "d" : "2022-09-11 23:59:59.997",
        "e" : 87
    },
    {
        "a" : "2022-09-12 00:00:10.001",
        "b" : "apple",
        "c" : "1",
        "d" : "2022-09-11 23:59:59.997",
        "e" : 88
    },
    {
        "a" : "2022-09-12 00:00:30.000",
        "b" : "orange",
        "c" : "200",
        "d" : "2021-09-11 23:59:59.997",
        "e" : 81
    },...
    ]

I start doing something like this, but itteration for all elements takes too much time. Could you please help me optimize my code ?

    import datetime
    import json
    import numpy as np
    import random``
    lst_in_seconds = []
    
    f = open('one_all.json')
    one = json.load(f)
    f.close()
    
    f1 = open('two_all.json')
    two = json.load(f1)
    f1.close()
    
    counter_one_better = 0
    counter_two_better = 0
    counter_the_same = 0

for k in range(10000000000):
   for i in range(10000000000):
    if one['one'][k]['b'] == two['two'][i]['b'] and one['one'][k]['e'] == two['two'][i]['e'] 
    and one['one'][k]['amount'] == two['two'][i]['amount'] 
    and one['one'][k]['d'] == two['two'][i]['d'] 
    and one['one'][k]['c'] == two['two'][i]['c']:
            if (one['one'][k]['a']) < (two['two'][i]['a']):
                # one better than two
                delt_one = datetime.datetime.strptime((one['one'][k]['a']), '%Y-%m-%d %H:%M:%S.%f')
                delt_two = datetime.datetime.strptime((two['two'][i]['a']), '%Y-%m-%d %H:%M:%S.%f')
                delta = delt_two - delt_one
                diff_in_seconds = delta.total_seconds()
                lst_in_seconds.append(diff_in_seconds)
                counter_one_better += 1
                two['two'][i]['b'] = random.randint(0,100000)
                break

                elif (one['one'][k]['a']) == (two['two'][i]['a']):
                    # same
                    delt_one = datetime.datetime.strptime((one['one'][k]['a']), '%Y-%m-%d %H:%M:%S.%f')
                    delt_two = datetime.datetime.strptime((two['two'][i]['a']), '%Y-%m-%d %H:%M:%S.%f')
                    delta = delt_two - delt_one
                    diff_in_seconds = delta.total_seconds()
                    lst_in_seconds.append(diff_in_seconds)
                    counter_the_same += 1
                    two['two'][i]['b'] = random.randint(0,100000)
                    break

                elif (one['one'][k]['a']) > (two['two'][i]['a']):
                    delt_one = datetime.datetime.strptime((one['one'][k]['a']), '%Y-%m-%d %H:%M:%S.%f')
                    delt_two = datetime.datetime.strptime((two['two'][i]['a']), '%Y-%m-%d %H:%M:%S.%f')
                    delta = delt_one - delt_two
                    diff_in_seconds = delta.total_seconds()
                    diff_in_seconds_to_str = float(('-' + str(diff_in_seconds)))
                    lst_in_seconds.append(diff_in_seconds_to_str)
                    counter_two_better += 1
                    two['two'][i]['b'] = random.randint(0,100000)
                    break

    #print('counter_the_same',counter_the_same,'count')
    #print('counter_one_better',counter_one_better,'count')
    #print('counter_two_better',counter_two_better,'count','\n')
    print('one better than two in ', round((counter_one_better / (counter_two_better+counter_one_better+counter_the_same))*100,4),'% case')
    print('the same ', round((counter_the_same / (counter_two_better+counter_one_better+counter_the_same))*100,4),'% case')
    print('two better than one in ', round((counter_two_better / (counter_two_better+counter_one_better+counter_the_same))*100,4),'% case','\n')
bigdata dictionary for-loop python-3.x json
1 Answers

You can reduce the time taken to compare the files by pre-processing the two files to dictionaries with keys of the values to be compared. Then for each entry in one, you can look up the entries in two which have the same b,c,d, and e values and compare the times. Note that you can make your code a lot more "DRY" by noting that the only difference in the three branches of the if is which counter is updated.

from collections import defaultdict
import datetime
import random

one = { "one": [
    {
        "a" : "2022-09-12 00:00:00.000",
        "b" : "apple",
        "c" : "1",
        "d" : "2022-09-11 23:59:59.997",
        "e" : 88
    },
    {
        "a" : "2022-09-12 00:00:00.000",
        "b" : "orange",
        "c" : "2",
        "d" : "2022-09-11 23:59:59.997",
        "e" : 87
    },      
    {
        "a" : "2022-09-12 00:00:10.001",
        "b" : "apple",
        "c" : "6",
        "d" : "2022-09-11 23:59:59.997",
        "e" : 88
    }
] }

two = { "two": [
    {
        "a" : "2022-09-12 00:00:30.000",
        "b" : "orange",
        "c" : "2",
        "d" : "2022-09-11 23:59:59.997",
        "e" : 87
    },
    {
        "a" : "2022-09-12 00:00:10.001",
        "b" : "apple",
        "c" : "1",
        "d" : "2022-09-11 23:59:59.997",
        "e" : 88
    },
    {
        "a" : "2022-09-12 00:00:30.000",
        "b" : "orange",
        "c" : "200",
        "d" : "2021-09-11 23:59:59.997",
        "e" : 81
    }
] }

compare_keys = ['b', 'c', 'd', 'e']
value_key = 'a'
set_key = 'b'

ones = defaultdict(list)
for o in one['one']:
    ones[tuple(o[k] for k in compare_keys)].append(o[value_key])

twos = defaultdict(list)
for t in two['two']:
    twos[tuple(t[k] for k in compare_keys)].append({ k : t[k] for k in [value_key, set_key] })

counter_one_better = 0
counter_two_better = 0
counter_the_same = 0
lst_in_seconds = []

for k, o in ones.items():
    t = twos.get(k)
    if t is None:
        continue
    for o1 in o:
        for i, t2 in enumerate(t):
            delt_one = datetime.datetime.strptime(o1, '%Y-%m-%d %H:%M:%S.%f')
            delt_two = datetime.datetime.strptime(t2[value_key], '%Y-%m-%d %H:%M:%S.%f')
            delta = delt_two - delt_one
            diff_in_seconds = delta.total_seconds()
            lst_in_seconds.append(diff_in_seconds)
            counter_one_better += diff_in_seconds < 0
            counter_the_same += diff_in_seconds == 0
            counter_two_better += diff_in_seconds > 0
            t[i][set_key] = random.randint(0,100000)

print(counter_one_better, counter_the_same, counter_two_better)
print(lst_in_seconds)

# reconstruct the two dict
new_two = { 'two' : [ dict([*zip(compare_keys, k), *v.items()]) for k in twos for v in twos[k] ] }

Output (for your sample data):

# counter_one_better, counter_the_same, counter_two_better
0 0 2
# lst_in_seconds
[10.001, 30.0]
# new_two
{
    "two": [
        {
            "b": 4459,
            "c": "2",
            "d": "2022-09-11 23:59:59.997",
            "e": 87,
            "a": "2022-09-12 00:00:30.000"
        },
        {
            "b": 93855,
            "c": "1",
            "d": "2022-09-11 23:59:59.997",
            "e": 88,
            "a": "2022-09-12 00:00:10.001"
        },
        {
            "b": "orange",
            "c": "200",
            "d": "2021-09-11 23:59:59.997",
            "e": 81,
            "a": "2022-09-12 00:00:30.000"
        }
    ]
}
usergAKD0
Related