Bottom Page

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
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  JSON file Loading issue punna111 4 2,784 Jun-29-2020, 08:07 AM
Last Post: buran
  Loading multiple JSON files to create a csv 0LI5A3A 0 103 Jun-28-2020, 10:35 PM
Last Post: 0LI5A3A
  Indirectlty convert string to float in JSON file WBPYTHON 6 406 May-06-2020, 12:09 PM
Last Post: WBPYTHON
  Read json array data by pandas vipinct 0 240 Apr-13-2020, 02:24 PM
Last Post: vipinct
  What is the mechanism of numpy function returning pandas object? Ibaraki 2 301 Apr-04-2020, 10:57 PM
Last Post: Ibaraki
  Help batch converting .json chosen file to MySQL BrandonKastning 2 334 Mar-14-2020, 09:19 PM
Last Post: BrandonKastning
  Transform Facebook Graph API insights JSON to pandas dataframe usman 0 390 Mar-03-2020, 05:14 AM
Last Post: usman
  JSON to Dataframe DrX 1 328 Feb-19-2020, 07:58 PM
Last Post: DrX
  Handling escape charters while converting data frame to JSON RahulShukla 0 328 Nov-11-2019, 11:22 AM
Last Post: RahulShukla
  How to resolve scipy differences? Oliver 2 832 Oct-08-2019, 08:40 PM
Last Post: Oliver

Forum Jump:


Users browsing this thread: 2 Guest(s)