-3

I need help to parse a text file to csv. My text file looks like this :

12: IBD08; ANALYSIS AND CHARACTERISATION OF THE FAECAL MICROBIAL DEGRADOME IN INFLAMMATORY BOWEL DISEASE
Identifiers: BioSample: SAMEA3914946; SRA: ERS1102080
Organism: Homo sapiens
Attributes:
    /sample name="ERS1102080"
    /collection date="2011"
    /environment biome="Intestine"
    /environment feature="Colon"
    /environment material="Faecal"
    /geographic location (country and/or sea)="United Kingdom"
    /host body product="Faeces"
    /host disease status="Healthy"
    /human gut environmental package="human-gut"
    /investigation type="metagenome"
    /latitude (raw)="51??31'03.3"
    /longitude (raw)="0??10'25.2"
    /project name="IBD gut"
    /sequencing method="Illumina Miseq"
Description:
Multi 'omic analysis of the gut microbiome in IBD
Accession: SAMEA3914946 ID: 5788180
2: qiita_sid_833:833.Sweden.IBD.102A; 833.Sweden.IBD.102A
Identifiers: BioSample: SAMEA3924619; SRA: ERS1111753
Organism: gut metagenome
Attributes:
    /sample name="ERS1111753"
    /sex="male"
    /age="3.9"
    /age group="2.0"
    /age unit="years"
    /altitude="0"
    /anonymized name="Sweden.IBD.102A"
    /antibiotics="definite_no"
    /assigned from geo="False"
    /barcodesequence="CTGCTATTCCTC"
    /body habitat="UBERON:feces"
    /body product="UBERON:feces"
    /tissue="UBERON:feces"
    /breed="Great_Dane"
    /breed grouping="Working"
    /collection date="1/30/12"
    /collection timestamp="1/30/12"
    /common name="gut metagenome"
    /geographic location="Sweden: GAZ"
    /depth="0"
    /disease="IBD"
    /dna extracted="True"
    /elevation="13.02"
    /emp status="NOT_EMP"
    /environment biome="ENVO:urban biome"
    /environment feature="ENVO:animal-associated habitat"
    /env matter="ENVO:feces"
    /experiment center="Texas A&M"
    /experiment design description="Fecal samples from dogs of various breeds, places of origin, and severity of bowel disorder were sequencing to obtain a dog gut metagenome."
    /experiment title="suchodolski_dog_ibd"
    /gender specific="M"
    /has extracted data="True"
    /has physical specimen="True"
    /histo="both"
    /host="domestic dog"
    /host="Canis lupus familiaris"
    /host subject id="Sweden.IBD.102A"
    /host taxonomy ID="9615"
    /illumina technology="HiSeq"
    /latitude="60.13"
    /library construction protocol="This analysis was done as in Caporaso et al 2011 Genome research. The PCR primers F515 and R806 were developed against the V4 region of the 16S rRNA, both bacteria and archaea, which we determined would yield optimal community clustering with reads of this length The reverse PCR primer is barcoded with a 12-base error-correcting Golay code to facilitate multiplexing of up to 1,500 samples per lane, and both PCR primers contain sequencer adapter regions."
    /linker="GT"
    /linkerprimersequence="GTGCCAGCMGCCGCGGTAA"
    /longitude="18.64"
    /pcr primers="FWD:GTGCCAGCMGCCGCGGTAA; REV:GGACTACHVGGGTWTCTAAT"
    /physical location="CCME"
    /physical specimen location="Texas A&M"
    /physical specimen remaining="False"
    /platform="Illumina"
    /platformchemistry="HiSeq_V4"
    /pool name="R.K.1.20.12"
    /primer plate="1"
    /public="False"
    /required sample info status="completed"
    /run center="CCME"
    /run date="1/30/12"
    /run prefix="Suchodolski_dog_ibd"
    /sample size="0.1, gram"
    /sample center="Texas A&M"
    /sample plate="IBD1"
    /sequencing meth="sequencing by synthesis"
    /size grouping="large"
    /study center="Texas A&M"
    /target gene="16S rRNA"
    /target subfragment="V4"
    /title="Suchodolski_dog_ibd"
    /total mass="54.0"
    /weight group="5.0"
    /weight kg="54.0"
    /well id="H6"
Description:
IBD1_Sweden_IBD_102A_H6_R.K.1.20.12
Accession: SAMEA3924619 ID: 5507372

Output format : Project Name BioSample SRA Organism sample name Etc... IBD08 SAMEA3914946 ERS1102080 Homo sapiens ERS1102080

Every project are having different fields. How to make a column of every field present in all the projects. Thanks in advance

2
  • 2
    What output format are you trying to achieve? Please edit the question to include this. Commented Sep 27, 2017 at 11:21
  • Possible duplicate of Python Parse CSV Correctly Commented Sep 27, 2017 at 11:45

1 Answer 1

1

Your two examples have very different fields, but you could still create a CSV containing all fields you need as follows:

from itertools import groupby, takewhile, ifilter
import re
import csv

heading = None
sub_headings = ['Identifiers', 'Organism']
attribute_fields = []

# First scan to determine list of all used attribute_fields
with open('projects.txt') as f_projects:
    re_attributes = re.compile(r'    \/(.*?)=".*"')

    for line in f_projects:
        # '    /sample size="0.1, gram"'
        re_attribute = re_attributes.match(line)

        if re_attribute:
            attribute_fields.append(re_attribute.group(1))

# Remove duplicate attributes, sort and prefix the top fields
attribute_fields = ['Description', 'id', 'Accession', 'AccessionID'] + sorted(set(attribute_fields))    

with open('projects.txt') as f_projects, open('output.csv', 'wb') as f_output:
    csv_output = csv.DictWriter(f_output, fieldnames=sub_headings + attribute_fields)
    csv_output.writeheader()

    skip_empty_lines = ifilter(lambda x: len(x.strip()), f_projects)

    for k, v in groupby(skip_empty_lines, lambda x: re.match('\d+: ', x)):
        if k:
            heading = next(v).strip()
        elif heading:
            row = {'id' : heading}
            lines = list(v)

            for line_number, line in enumerate(lines):
                for sub_heading in sub_headings:
                    if line.startswith(sub_heading):
                        row[sub_heading] = line.split(':', 1)[1].strip()

                if line.startswith('Attributes:'):
                    for attribute in takewhile(lambda x: x.startswith('    /'), iter(lines[line_number+1:])):
                        k, v = re.findall(r'/(.*?)="(.*?)"', attribute)[0]
                        row[k] = v

                if line.startswith('Description:'): 
                    row['Description'] = lines[line_number+2].strip() # use next line only

                # Accession: SAMN00030407\tID: 30407
                if line.startswith('Accession:'):
                    accession, accession_id = re.match('Accession: (.*?)\tID: (.*?)$', line).groups()
                    row.update({'Accession':accession, 'AccessionID':accession_id})

            csv_output.writerow(row)

This would generate a rather sparse output CSV as follows:

Identifiers,Organism,Description,id,Accession,AccessionID,!16S_BarcodeSequence,"""PUBLIC""",16S_ForwardPrimer,16S_LinkerPrimerSequence,ArrayExpress-Species,ENA-CHECKLIST,ENA-FIRST-PUBLIC,ENA-LAST-UPDATE,HCA_MBT,HEIGHT,ITS2_BarcodeSequence,ITS2_LinkerPrimerSequence,PUBLIC,PlatformChemistry,Species,TOTAL_SCCA,WEIGHT,age,age at fmt,age group,age unit,age_unit,altitude,analyte type,anonymized name,anonymized_name,antibiotics,assigned from geo,assigned_from_geo,barcoded primer name,barcoded_primer_name,barcodesequence,bcs,bcs grouping,biomaterial provider,biospecimen repository,biospecimen repository sample id,body habitat,body mass index,body product,breed,breed grouping,calprotectin,cd behavior,cd location,cd resection,chemical administration,collection date,collection timestamp,common name,common_name,crude fiber 1000kcalg me group,cultivar,day since fmt,depth,description,detail,dewormed,diagnosis full,disease,disease control,dna extracted,donor group,donor kind,donor or patient,donor_recipient,ecotype,elevation,emp status,env matter,env_matter,environment biome,environment feature,environment material,environmental package,ethnicity,exp code,experiment center,experiment design description,experiment title,fecal date,fmt modality,g fat 1000kcal me group,g protein 1000kcal me group,gastrointestinal tract disorder,gender specific,geographic location,geographic location (country and/or sea),has extracted data,has physical specimen,health state,histo,histological type,hospitalized for fmt,host,host age,host body mass index,host body product,host disease,host disease status,host family relationship,host genotype,host sex,host subject id,host taxonomy ID,host tissue sampled,host-associated environmental package,human gut environmental package,ibd,ibd or not,ibd subtype,illumina technology,immune_state,immunocompromized,indiv g fat 1000kcal me group,indiv g protein 1000kcal me group,individual,indoor outdoor,inflammed,investigation type,isolate,isolation and growth condition,isolation source,lane,latitude,latitude (raw),latitude and longitude,library construction protocol,linker,linkerprimersequence,longitude,longitude (raw),marital status,mid,miscellaneous parameter,molecular data type,mouse_number,non barcoded linker,non barcoded primer,non barcoded primer name,non_barcoded_linker,non_barcoded_primer,non_barcoded_primer_name,number courses metronidazole,number fidaxo courses,number ivig,number prior episodes,number prior fmt,number recurrence after fmt,number std vanco courses,number vanco tapers,pathology,patient,patientnumber,pcr primers,pcr_primers,perc crude protein min group,percent crude fat min group,percent crude fiber max group,percent met cal carb group,percent met cal fat group,percent met cal protein group,perianal disease,perturbation,phenotype,physical location,physical specimen location,physical specimen remaining,platform,platformchemistry,pool name,postfmt cdi result,postfmt symptoms,prebotic source,primer plate,project name,protein source,public,race code,replicate,required sample info status,run center,run date,run prefix,sample center,sample collection device or method,sample id,sample name,sample no ngs nr,sample plate,sample size,sample storage temperature,sample type,sample_code,sample_id,sampling_time,secondary description,separate first,separate first and donor,seq_meth,sequencing meth,sequencing method,sex,size grouping,source material identifiers,state,state us,strain,study,study center,study design,study id,study name,subject,subject code,submitted sample id,submitted subject id,submitter handle,target gene,target subfragment,target_gene,target_subfragment,taxon id,taxon_id,terminal ileum,time_point,time_point_label,time_point_months,timepoint,tissue,title,total mass,travel history,treatment_parasite,uc extent,unknown,vanc plus rif chaser,visit_num,weight group,weight kg,well id,year diagnosed
BioSample: SAMEA3914960,Homo sapiens,Accession: SAMEA3914960    ID: 5788191,1: IBD22; ANALYSIS AND CHARACTERISATION OF THE FAECAL MICROBIAL DEGRADOME IN INFLAMMATORY BOWEL DISEASE,SAMEA3914960,5788191,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2011,,,,,,,,,,,,,,,,,,,,,,,,Intestine,Colon,Faecal,,,,,,,,,,,,,,United Kingdom,,,,,,,,,,Faeces,,Inflammatory Bowel Disease,,,,,,,,human-gut,,,,,,,,,,,,metagenome,,,,,,51??31'03.3,,,,,,0??10'25.2,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,IBD gut,,,,,,,,,,,,ERS1102094,,,,,,,,,,,,,,Illumina Miseq,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
BioSample: SAMEA3914951; SRA: ERS1102085,Homo sapiens,Accession: SAMEA3914951   ID: 5788190,2: IBD13; ANALYSIS AND CHARACTERISATION OF THE FAECAL MICROBIAL DEGRADOME IN INFLAMMATORY BOWEL DISEASE,SAMEA3914951,5788190,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2011,,,,,,,,,,,,,,,,,,,,,,,,Intestine,Colon,Faecal,,,,,,,,,,,,,,United Kingdom,,,,,,,,,,Faeces,,Inflammatory Bowel Disease,,,,,,,,human-gut,,,,,,,,,,,,metagenome,,,,,,51??31'03.3,,,,,,0??10'25.2,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,IBD gut,,,,,,,,,,,,ERS1102085,,,,,,,,,,,,,,Illumina Miseq,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Tested on Python 2.7.12

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

14 Comments

Thanks for help Martin Evans. I have quoted 2 examples from my text file, like this i have around 2000 projects. will this work for all of them ? If one project is not having those fields will it take value as NA or blank ?
Currently if a project contains an unseen field, the script will stop and tell you which field is missing. You can then simply copy this into headings_field. Alternatively you could tell it to ignore missing fields. Also, missing fields are currently left blank. If you want NA, pass restval='NA' as a paramter to DictWriter().
Thanks Martin Evans I will try the following code. :)
Could you give me a link to an actual copy of your input file? You could use a service like 0bin. I recommend you use the full link at the top of the page, the short link sometimes doesn't work.
Thanks. The file did actually contain lines with just a single space on which broke the analysis. It now skips those lines. I have also updated the list of possible headings. Feel free to change the order of these if needed.
|

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.