Python Forum
How to compare two json and write to third json differences with pandas and numpy
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to compare two json and write to third json differences with pandas and numpy
#1
Hi

I am trying to compare two json and then write another json with columns names and with differences as yes or no. I am using pandas and numpy

Input files: And these input files are dynamic, for example this below example file has only two keys, where are ohter files i have may dynamic number of keys. so requirement is to loop all columns and then compare and write to json.

fut.json

[
    {
        "AlarmName": "test",
        "StateValue": "OK"
    }
]

Curr.json:

[
    {
        "AlarmName": "test",
        "StateValue": "OK"
    }
]
Below code I have tried:

    import pandas as pd
    import numpy as np

    with open(r"c:\csv\fut.json", 'r+') as f:
        data_b = json.load(f)
    with open(r"c:\csv\curr.json", 'r+') as f:
        data_a = json.load(f)
    df_a = pd.json_normalize(data_a)
    df_b = pd.json_normalize(data_b)
    
    _, df_a = df_b.align(df_a, fill_value=np.NaN)
    _, df_b = df_a.align(df_b, fill_value=np.NaN)
    
    with open(r"c:\csv\report.json", 'w') as _file:
        for col in df_a.columns:
            df_temp = pd.DataFrame()
            df_temp[col + '_curr'], df_temp[col + '_fut'], df_temp[col + '_diff'] = df_a[col], df_b[col], np.where((df_a[col] == df_b[col]), 'No', 'Yes')
            #[df_temp.rename(columns={c:'Missing'}, inplace=True) for c in df_temp.columns if df_temp[c].isnull().all()]
            df_temp.fillna('Missing', inplace=True)
            with pd.option_context('display.max_colwidth', -1):
                _file.write(df_temp.to_json(orient='records'))
 
Expected output:

[
    {
        "AlarmName_curr": "test",
        "AlarmName_fut": "test",
        "AlarmName_diff": "No"
    },
    {
        "StateValue_curr": "OK",
        "StateValue_fut": "OK",
        "StateValue_diff": "No"
    }
]
Coming output: Not able to parse it in json validator, below is the problem, those [] should be replaed by ',' to get right json dont know why its printing like that

[{"AlarmName_curr":"test","AlarmName_fut":"test","AlarmName_diff":"No"}][{"StateValue_curr":"OK","StateValue_fut":"OK","StateValue_diff":"No"}]
Tried below as well

_file.write(df_temp.to_json(orient='records',lines=True))
now i get json which is again not parsable, ',' is missing and unless i add , between two dic and [ ] at beginning and end manually , its not parsing..

[{"AlarmName_curr":"test","AlarmName_fut":"test","AlarmName_diff":"No"}{"StateValue_curr":"OK","StateValue_fut":"OK","StateValue_diff":"No"}]
any help will be highly appreciated.. thanks in advance
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  [Numpy] How to store different data type in one numpy array? water 7 284 Mar-26-2024, 02:18 PM
Last Post: snippsat
  How to customize VS Code setting.json? NewPi 1 905 Dec-11-2022, 08:03 PM
Last Post: jefsummers
Smile How to further boost the data read write speed using pandas tjk9501 1 1,227 Nov-14-2022, 01:46 PM
Last Post: jefsummers
  Pandas dataframes and numpy arrays bytecrunch 1 1,292 Oct-11-2022, 08:08 PM
Last Post: Larz60+
  Parse Nested JSON String in Python rwalde 4 2,858 Sep-08-2022, 10:32 AM
Last Post: rwalde
  Numpy returns "TypeError: unsupported operand type(s) for *: 'numpy.ufunc' and 'int'" kalle 2 2,527 Jul-19-2022, 06:31 AM
Last Post: paul18fr
Information Parssing Json.dump from PYTHON to PHP for output on browser jodqueshiva 1 2,372 Nov-01-2021, 02:34 PM
Last Post: snippsat
  Write a dictionary with arrays as values into JSON format paul18fr 3 5,505 Oct-20-2021, 10:38 AM
Last Post: buran
  [Pandas] Write data to Excel with dot decimals manonB 1 5,772 May-05-2021, 05:28 PM
Last Post: ibreeden
  HELP! Importing json file into csv into jupyter notebook vilsef 2 2,531 Jan-22-2021, 11:06 AM
Last Post: snippsat

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020