1
$\begingroup$

I have a long list of JSON data, with repeats of contents similar to followings.

Due to the original JSON file is too long, I will just shared the hyperlinks here. This is a result generated from a database called RegulomeDB.

Direct link to the JSON file

I would like to extract specific data (eQTLs) from "method": "eQTLs" and "value": "xxxx", and put them into 2 columns (tab delimited) exactly like below. Note: "value":"xxxx" is extracted right after "method": "eQTLs"is detected.

eQTLs   firstResult, secondResult, thirdResult, ...

In this example, the desired output is:

eQTLs   EIF3S8, EIF3CL

I've tried using a python script but was unsuccessful.

import json
with open('file.json') as f:
    f_json = json.load(f)
    print 'f_json[0]['"method": "eQTLs"'] + "\t" + f_json[0]["value"]

Thank you for your kind help.

$\endgroup$
2
  • $\begingroup$ Could you clarify what output format you expect please? You say "tab delimited" but show comma delimited data. $\endgroup$
    – terdon
    Nov 9, 2022 at 9:27
  • $\begingroup$ HI @terdon, edited my post. Thank you. $\endgroup$
    – austin7923
    Nov 9, 2022 at 9:50

3 Answers 3

3
$\begingroup$

Here a jq solution if you can use it:

jq -r --arg f "eQTLs" '$f + "\t" + ([."@graph"[] | select(.method == $f) | .value] | join(", "))' file.json

Explanations:

  • -r: Raw string result (so result string will be without between ")
  • --arg name value: Your filter on eQTLs method value
  • Search in @graph array elements with []
  • Select only array element with method attribute equals to eQTLs (in $f argument)
  • The result, 2 in this case, are put in a array [...] and join with string ,
  • $f string, a tabulation "\t" and the join result are concatenated to one string with + operators

Result:

eQTLs   EIF3S8, EIF3CL
$\endgroup$
0
0
$\begingroup$

It's a little hard to know what your goal is here based on the sample code, but I do find that your JSON snippet does not match any spec. I was able to edit it to add brackets to make the entries elements of a list:

[
  {
  "biosample_ontology": {
    "@id": "https://www.encodeproject.org/biosample-types/primary_cell_CL_0000576/",
    "@type": [
      "BiosampleType",
      "Item"
    ],
    "aliases": [],
    "cell_slims": [
      "hematopoietic cell",
      "monocyte",
      "myeloid cell",
      "leukocyte"
    ],
    "classification": "primary cell",
    "dbxrefs": [],
    "developmental_slims": [
      "mesoderm"
    ],
    "name": "primary_cell_CL_0000576",
    "organ_slims": [
      "blood",
      "bodily fluid"
    ],
    "references": [],
    "schema_version": "1",
    "status": "released",
    "synonyms": [],
    "system_slims": [
      "immune system"
    ],
    "term_id": "CL:0000576",
    "term_name": "monocyte",
    "uuid": "20685264-1187-4c5c-b4ce-dc85ea53fc11"
  },
  "chrom": "chr11",
  "dataset": "https://www.encodeproject.org/annotations/ENCSR464XOM/",
  "dataset_rel": "/annotations/ENCSR464XOM/",
  "documents": [],
  "end": 59936979,
  "file": "ENCFF814NRI",
  "method": "eQTLs",
  "start": 59936978,
  "strand": null,
  "targets": [],
  "value": "MS4A4A"
},

    {
  "biosample_ontology": {
    "@id": "https://www.encodeproject.org/biosample-types/primary_cell_CL_0000576/",
    "@type": [
      "BiosampleType",
      "Item"
    ],
    "aliases": [],
    "cell_slims": [
      "hematopoietic cell",
      "monocyte",
      "myeloid cell",
      "leukocyte"
    ],
    "classification": "primary cell",
    "dbxrefs": [],
    "developmental_slims": [
      "mesoderm"
    ],
    "name": "primary_cell_CL_0000576",
    "organ_slims": [
      "blood",
      "bodily fluid"
    ],
    "references": [],
    "schema_version": "1",
    "status": "released",
    "synonyms": [],
    "system_slims": [
      "immune system"
    ],
    "term_id": "CL:0000576",
    "term_name": "monocyte",
    "uuid": "20685264-1187-4c5c-b4ce-dc85ea53fc11"
  },
  "chrom": "chr11",
  "dataset": "https://www.encodeproject.org/annotations/ENCSR464XOM/",
  "dataset_rel": "/annotations/ENCSR464XOM/",
  "documents": [],
  "end": 59936979,
  "file": "ENCFF814NRI",
  "method": "eQTLs",
  "start": 59936979,
  "strand": null,
  "targets": [],
  "value": "MS4A4B"
}
]

When I did this, I was able to slightly modify your code to print out each element's method key, value pair:

import json
with open('/Users/max.press/Downloads/file.json') as f:
    f_json = json.load(f)
    for i in range(len(f_json)):
        print(f_json[i]["method"] + "\t" + f_json[i]["value"])
% python ~/Downloads/readfile.py
# prints this:
eQTLs   MS4A4A
eQTLs   MS4A4B
$\endgroup$
3
  • $\begingroup$ Hi Maximilian Press, I am sorry the code doesn't work, with error code as followings. print(f_json[i]["method"] + "\t" + f_json[i]["value"]) KeyError: 0 $\endgroup$
    – austin7923
    Nov 9, 2022 at 9:08
  • $\begingroup$ I am sorry about the JSON snippet format. Let me reupload a complete one to the original question. Could you please try again? Thank you. $\endgroup$
    – austin7923
    Nov 9, 2022 at 9:09
  • $\begingroup$ @austin7923 it looks like there was additional formatting in your json outside of the snippet that you first posted, which makes the outer layer into a dict within which the list is nested. Thus the keyerror. regardless it looks like the jq solution works for you. $\endgroup$ Nov 9, 2022 at 17:54
0
$\begingroup$

A (very) simple Python solution:

The script file readfile.py:

#! /usr/bin/python3

import sys
import json

filename=sys.argv[1]
filter=sys.argv[2]

results=[]
with open(filename) as json_file:
    json_content = json.load(json_file)
    for json_graph_element in json_content["@graph"]:
        if json_graph_element["method"] == filter:
            results.append(json_graph_element["value"])
print("%s\t%s" % (filter, ", ".join(results)))

Make the script executable:

chmod +x readfile.py

Use the script:

./readfile.py /path/to/the/file.json  eQTLs

Result:

eQTLs   EIF3S8, EIF3CL
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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