0

Apologies this is my first experience with JSON files.

I have complex nested JSON file which should corelated to excel file which have various branches. I am required to verify the data of JSON is same as in the excel. However, as mentioned before the JSON file is nested and it proving its challenge.

So my question is, is there a way to change complex nested Json into panda data frame and comparing with excel ?

bmaster69
  • 121
  • 9
  • 1
    you need to post data example and desired output. Additionally post code witch you have worked to solve this problem. – Zaraki Kenpachi Sep 25 '20 at 08:58
  • @BisalDhakai--if the nesting is not too complex you can use [json_normalize](https://stackoverflow.com/questions/46091362/how-to-normalize-json-correctly-by-python-pandas) – DarrylG Sep 25 '20 at 09:30
  • @DarrylG, i tried normalizing but it ended up making a mess. i guess i have to rule that out . – bmaster69 Sep 25 '20 at 10:25
  • @BisalDhakal--can you share an example of the JSON? I have a normalizing function I developed I could try with your example. – DarrylG Sep 25 '20 at 10:29
  • hi @DarrylG how can i share my JSON, apologies this is my first post – bmaster69 Sep 25 '20 at 10:38
  • @BisalDhakal--how large is it (i.e. 10's, 100's of lines or even larger)? – DarrylG Sep 25 '20 at 10:43
  • @ 1331 to be exact – bmaster69 Sep 25 '20 at 11:24
  • @BisalDhakal--can you provide an online link to the file (i.e. Dropbox, Google Drive, OneDrive, etc.)? – DarrylG Sep 25 '20 at 11:29
  • https://1drv.ms/u/s!AjSImbqMWAumm25CdLzOp88AzSQc?e=dv1kDk @DarrylG – bmaster69 Sep 25 '20 at 11:48
  • @BisalDhakal--the links seems to for a png image of your IDE. Can you share the data? – DarrylG Sep 25 '20 at 11:50
  • okay, the term i am looking for is flattening nested arrays in JSON file which then cane be compared to the excel data frame. – bmaster69 Sep 25 '20 at 11:55
  • @BisalDhakal--never mind I found your data in the link. I'll take a look. – DarrylG Sep 25 '20 at 12:01
  • @DarrylG, png file is the excel output in python which can easily be read with all the columns and headers. however, json file when imported in the python comes as nested thus, making it impossible to compare the data frame. I wish to linearize the json data, making it flattened which then i can compare with excel data frame. However, my skills are limited with python and i am not sure what i am doing. I am aware that i will need recursion loop to bring all the dictionaries forward as list but not sure going about it. – bmaster69 Sep 25 '20 at 12:48
  • @BisalDhakal--does the Excel show the data is only 247 rows x 11 columns? If so, why is the son so complex. I'm able to unnest the JSON to various depths, but the number of rows and columns grows to a lot larger than this therefore my question. – DarrylG Sep 25 '20 at 15:28
  • @DarrylG, hi again mate.those are the discrepancies i want to find. is that a possibility with python ? – bmaster69 Sep 27 '20 at 19:31
  • so, excel comes up as 247 x 11 dataframe, whereas json comes up as 2x 1 due to its being nested. is there any possibility if i can convert json into data frame and check them. and once the data frame matches then i can go further to check each cells. – bmaster69 Sep 27 '20 at 20:17
  • @BisalDhakal--is there a description of how you would relate a value in the 247x11 dataframe to a value in the json? – DarrylG Sep 27 '20 at 20:29
  • @DarrylG, sorry i was expecting if the json is exact copy of excel then all the values should match up. apologies, i am new to this so i have very limited knowledge. – bmaster69 Sep 27 '20 at 20:45
  • @BisalDhakal--what I'm asking is do you visually see a path through the json (i.e. by traversing through its dictionaries & lists) to get a corresponding value based upon a row/column entry in the data frame? – DarrylG Sep 27 '20 at 21:07
  • @DarrylG, hi mate and yes. I have uploaded the visual path of the json in the shared folder. – bmaster69 Sep 28 '20 at 08:11
  • @BisalDhakal--when you say the Excel is 247x11 do you mean there is only 11 different types of measurements (i.e. columns) and you have 247 values for each measurement? Or is it 247 different types of values and there is 11 measurement of each value? Are you referring to [this](https://onedrive.live.com/?authkey=%21AEJ0vM6nzwDNJBw&cid=A60B588CBA998834&id=A60B588CBA998834%213568&parId=A60B588CBA998834%213566&o=OneUp) by visualization? – DarrylG Sep 28 '20 at 08:30
  • @DarrylG, its all good. nvm and thanks for all the effort you put in, i really appreciate your help. – bmaster69 Sep 29 '20 at 09:07
  • @BisalDhakal--too bad I couldn't help more. It's just I'm a bit confused about why the Excel is so simple considering the complexity of the JSON. – DarrylG Sep 29 '20 at 09:10
  • @DarrylG, yes that was the problem and i was trying to de-nest the json to see if i can bring up the list which then can the transferred to panda dataframe. and i could check 2 data frame, one from excel and one from json and then run boolen parameter to check weather the data is TRUE or FALSE. well atleast i learned how much nightmare it is, if the json is too much nested. Thanks again – bmaster69 Sep 29 '20 at 09:16

0 Answers0