0

I have a file with ~13K lines, containing two columns. I want to extract the values in 'phNAME': 'value', but each line could have more than one phNAME.

In case there is more than one phNAME on a single line, I want to write each phNAME value to a separate line (see input and output samples).

Here is a sample of the input file:

ENSG00000268674 "[{'rgID': 'AMBN10042640', 'probeID': '246945', 'phenodata': [{'phcluster': '7', 'howgood': '1', 'bestgenes': '539', 'ScrID': 'M1_SyM_1', 'phenotypes': [{'ScrID': 'M1_SyM_1', 'phNAME': 'Dynamic changes', 'phID': 7, 'phWEIGHT': 1}]}], 'goodmatch': 0}, {'probeID': '249958', 'rgID': 'AMBN10047320', 'goodmatch': 0, 'phenodata': [{'phenotypes': [{'ScrID': 'E1_SyM_1', 'phWEIGHT': 1, 'phID': 2, 'phNAME': 'Strong inhibition of secretion'}], 'ScrID': 'E1_SyM_1', 'phcluster': '2', 'howgood': '1', 'bestgenes': '1378'}]}]"
ENSG00000215268 "[{'probeID': '105114', 'rgID': 'AMBN10004073', 'goodmatch': 0, 'phenodata': [{'howgood': '0.5', 'phcluster': '7', 'bestgenes': '539', 'phenotypes': [{'ScrID': 'M1_SyM_1', 'phNAME': 'Dynamic changes', 'phID': 7, 'phWEIGHT': 0.5}], 'ScrID': 'M1_SyM_1'}]}]"
ENSG00000130538 "[{'rgID': 'AMBN10042690', 'probeID': '247755', 'phenodata': [{'phenotypes': [{'phNAME': 'Intracellular retention of SH4(YES)-mCherry', 'phID': 2, 'phWEIGHT': 0.5, 'ScrID': 'B1_SyM_1'}], 'ScrID': 'B1_SyM_1', 'bestgenes': '984', 'howgood': '0.5', 'phcluster': '2'}], 'goodmatch': 0}]"

My desired output looks like this:

ENSG00000268674 Dynamic changes
ENSG00000268674 Strong inhibition of secretion
ENSG00000215268 Dynamic changes
ENSG00000130538 Intracellular retention of SH4(YES)-mCherry
  • 3
    Sigh... What writes this output? It's partly JSON, but the JSON part does not contain the Ensembl gene ID, and has single quotes instead of double quotes, which makes parsing it properly as JSON a mess. One could possibly try to incorporate the Ensembl gene ID into the JSON data and then use jq to extract the information... – Kusalananda Jul 23 '18 at 14:26
  • It was a son file, but I convert it to that because I'm thinking that's would be more easier to me, but NOT and also I don't know how to use jq ! – Sara Wasl Jul 23 '18 at 14:30
  • I don't think you need to convert json, you just make it more complicated for yourself. You can use python and get it as json without conversion and use it as dictionary. Other suggestion use bash script some find functions. – alex_mike Jul 23 '18 at 14:44
  • 3
    If it was a JSON file, why not start with parsing that rather than the munged not-quite-JSON you've presented here? – DopeGhoti Jul 23 '18 at 16:53
1

Solution:

perl -lane 'print "$F[0] $_" for /'\''phNAME'\'': '\''([^'\'']+)/g' input.txt

Output: (based on your posted input file)

ENSG00000268674 Dynamic changes
ENSG00000268674 Strong inhibition of secretion
ENSG00000215268 Dynamic changes
ENSG00000130538 Intracellular retention of SH4(YES)-mCherry

Explanation:

A detailed explanation will be provided upon request.
In my experience, perl is often the most suitable tool for this kind of tasks.
The code looks quite messy because of '\'', which is simply an escaped '.

Recommendation:

If you can, avoid messing with json data and parse it with jq, as already sensibly suggested.

  • 1
    +1 works well for me. – pLumo Jul 24 '18 at 9:13
0

This seems to do the trick:

$ sed -E 's/^([A-Z0-9]*) .*(phNAME)'"'"': '"'"'([^'"'"']*).*/\1 \3/' input
ENSG00000268674 Strong inhibition of secretion
ENSG00000215268 Dynamic changes
ENSG00000130538 Intracellular retention of SH4(YES)-mCherry
  • It's clearly early in the day. For some reason I was not successful in escaping an embedded ' in a strong-quoted string; hence the '"'"' monstrosities. – DopeGhoti Jul 23 '18 at 15:09
  • 1
    Thank you, but still not return all the phNAME, for example ENSG00000268674 have another phNAME :( @DopeGhoti – Sara Wasl Jul 23 '18 at 16:26
0

The other solutions are fine if you always have the same format and want to extract only this single value.

If you need more flexibility to grab other data too, you should make use of the json format of the data.


Python solution:

import csv, demjson
with open('test') as csvfile:
    reader = csv.reader(csvfile, delimiter=' ', quotechar='"')
    for row in reader:
        data = demjson.decode(row[1])
        for d in data:
            for pd in d['phenodata']:
                for pt in pd['phenotypes']:
                    phname = pt['phNAME']
                    print('%s %s' % (row[0],phname))

Output:

ENSG00000268674 Dynamic changes
ENSG00000268674 Strong inhibition of secretion
ENSG00000215268 Dynamic changes
ENSG00000130538 Intracellular retention of SH4(YES)-mCherry

As your json string is not valid, we cannot use the json module.
You need to have demjson module installed:

# python3:
pip3 install demjson
# python2:
pip install demjson
  • 1) if you always have the same format input file format is part of the question and is expected to stay the same within reasonable boundaries. 2) want to extract only this single value the desired field name could just as well be passed as an argument. 3) you should make use of the json format of the data this has been suggested over and over again. – simlev Jul 24 '18 at 9:07
  • @simlev: What is your problem? 1) it may still help others. I like more portable solutions. 2) dito, and then I also disagree, almost everytime, things change. 3.) yes it was suggested in the comments, but no one posted an answer. – pLumo Jul 24 '18 at 9:08
  • I thought I'd point out parts of your reasoning I don't agree with. Notwithstanding, I find this to be a perfectly valid answer and didn't downvote it. – simlev Jul 24 '18 at 9:13

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.