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(
  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)


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.
    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.


  • $\begingroup$ 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. $\endgroup$ – Reza Rezaei Aug 19 '20 at 5:56
  • $\begingroup$ json into excel? i tired but mess up all the formatting $\endgroup$ – krushnach Chandra Aug 25 '20 at 6:30
  • $\begingroup$ I'm not an expert in using it but I used excel to parse a json file one time and it worked for me! $\endgroup$ – 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:
    d = json.load(f)

dfs = []
for x, y in enumerate(d):
    data = pd.json_normalize(d[x]['result'])

df = pd.concat(dfs, ignore_index=True)

df.to_csv('analysis.csv', index=False)
  • $\begingroup$ thanks for the answer will try to use it.. $\endgroup$ – krushnach Chandra Aug 19 '20 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.