Data extraction for specific string

Question

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.

Double request with bioinformatics.stackexchange.com/questions/19978/… — Arnaud Valmary
– Arnaud Valmary, Commented Nov 9, 2022 at 10:01

Reino · Accepted Answer · 2022-11-09 18:24:18Z

1

Maybe you'll find the JSON-parser xidel useful. It can open urls and can manipulate strings any way you want:

$ xidel -s "https://regulomedb.org/regulome-search/?regions=chr16:28539847-28539848&genome=GRCh37&format=json" \
  -e '"eQTLs&#9;"||join($json("@graph")()[method="eQTLs"]/value,", ")'
eQTLs   EIF3S8, EIF3CL

Or with the XPath/XQuery 3.1 syntax:

-e '"eQTLs&#9;"||join($json?"@graph"?*[method="eQTLs"]?value,", ")'

answered Nov 9, 2022 at 18:24

Reino

3,4901 gold badge17 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SaSkY · Accepted Answer · 2022-11-09 11:58:33Z

0

Try this:

cat file.json | grep -iE '"method":\s*"eQTLs"[^}]*' -o | cut -d ',' -f 1,5 | sed -r 's/"|:|method|value//gi' | sed 's/\s*eqtls,\s*//gi' | tr '\n' ',' | sed 's/,$/\n/g' | sed 's/,/, /g' | xargs echo -e 'eQTLs\x09'

edited Nov 9, 2022 at 11:58

answered Nov 9, 2022 at 10:42

SaSkY

1,0881 gold badge8 silver badges17 bronze badges

6 Comments

austin7923 Over a year ago

Hi @SaSkY, thank you for trying. However, I am getting errors as followings. grep: invalid option -- P usage: grep [-abcdDEFGHhIiJLlMmnOopqRSsUVvwXxZz] [-A num] [-B num] [-C[num]] [-e pattern] [-f file] [--binary-files=value] [--color=when] [--context[=num]] [--directories=action] [--label] [--line-buffered] [--null] [pattern] [file ...]

SaSkY Over a year ago

@austin7923 I updated the answer please try the command again

austin7923 Over a year ago

Thanks! It works! But it would be good if the final output could be glued together with a "comma", exactly like the one shown in the post.

SaSkY Over a year ago

@austin7923 I updated the answer again, can you try it and tell me if it works as you expected ?

austin7923 Over a year ago

Hi @SaSkY, Some edits need to be done on your command. It works flawlessly now. Thank you! cat file.json | grep -iE '"method":\s*"eQTLs"[^}]*' -o | cut -d ',' -f 1,5 | sed -r 's/"|:|method|value//gi' | sed 's/\s*eqtls,\s*//gi' | tr '\n' ',' | sed 's/,$/\n/g' | sed 's/,/, /g' | xargs echo 'eQTLs

|

Collectives™ on Stack Overflow

Data extraction for specific string

2 Answers 2

Comments

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Related