# Parsing the json file from gene ontology result into dataframe

How do I parse the json file from ontology output. My file json file

jdata <- read_json("analysis.json", simplifyVector = TRUE)


I want to convert it into a data.frame format. I can able to take the GO term and respective p value etc. But I would like to know the genes that are getting part of the enriched list which is under the upload_1 for example under DNA Replication there are 33 terms but 8 are from my input. So my objective is to get the 8 gene so on and so forth.

So I would like to see a data.frame where I want to have GO term in the first column gene mapped into the term would be second i.e upload_[#] third one fold enrichment and last one p value.

This is how i would like to see this one is the text output in the column upload_1 (450) instead of that number i would like to see then gene names which are present in the json file.

Some progress here is my code

l <- fromJSON("analysis.json")

m <- lapply(
l[[1]]$$result, function(x) c(x$$input_list$$mapped_id_list['mapped_id'], x$$term['id'],x$$input_list['pValue'],x$$term['label'])
)
m <- do.call(rbind, m)


output:

mapped_id     id           pValue       label
[1,] Character,8   "GO:0006270" 0.007540801  "DNA replication initiation"
[2,] Character,388 "GO:0009987" 3.232843e-10 "cellular process"


Right now I can parse out first two row.

Things I would like to append to the code is

• I would like to do for all the terms
• The first column where i am trying to get all the genes now it is coming as character while number suggests gene present. That I wont need probably since i need only gene names which is being shown as character now.
dput(head(b))
structure(list(GO biological process complete = c("DNA replication initiation (GO:0006270)",
"regulation of blood coagulation (GO:0030193)", "regulation of hemostasis (GO:1900046)",
"regulation of coagulation (GO:0050818)", "platelet degranulation (GO:0002576)",
"cell cycle G1/S phase transition (GO:0044843)"), upload_1 (450) = c(8,
13, 13, 13, 16, 15), upload_1 (fold Enrichment) = c(11.23,
7.09, 7, 6.62, 5.75, 5.35), upload_1 (P-value) = c(0.00754,
0.000627, 0.000716, 0.00135, 0.000347, 0.00228)), row.names = c(NA,
-6L), class = c("tbl_df", "tbl", "data.frame"))


Any suggestion or help would be really appreciated.

• I guess you can import your json into excel, modify it, and then save it as a csv or excel file. Then easily import the csv or excel file into R using tidyverse tools. – Reza Rezaei Aug 19 '20 at 5:56
• json into excel? i tired but mess up all the formatting – krushnach Chandra Aug 25 '20 at 6:30
• I'm not an expert in using it but I used excel to parse a json file one time and it worked for me! – Reza Rezaei Aug 25 '20 at 11:17

I don't know how to do that in R but I think this is what you want:

I used http://json2table.com for view the data structure of the JSON file, then I used json_normalize to normalize the JSON data into a flat table using the key [result] to a list of DataFrames for each index in the JSON file. Then, I concatenated the list of DataFrames in a single one with df.concat()

import json
import pandas as pd

with open('analysis.json') as f: