Python - Extract and reformat field names from rows of data
Question: I have a flat text file (infile) that I would like to restructure. It has a
few tab-delimited columns and looks something like this:

    
    
     Person1    HEIGHT=60;WEIGHT=100;AGE=22
     Person2    HEIGHT=62;WEIGHT=101;AGE=25
     Person3    HEIGHT=64;WEIGHT=110;AGE=29
    

and I want it to look like this:

    
    
     PERSON    HEIGHT    WEIGHT    AGE
     1         60        100       22
     2         62        101       25
     3         64        110       29
    

You can see that the second column actually contains several semicolon-
delimited header/value fields, and I want to restructure them into typical
column header rows.

Right now I have:

    
    
    for line in infile:
            line = line.split("\t")
            line_meta = line[1].split(";")
            print line_meta
    

I am thinking the best solution will to now loop over the line_meta variable,
use use regular expressions to detect header names (detect strings which start
with multiple capital letters and ends with "="_), add each header to a
dictionary as a key, and then store the rest of the string as the value. Then,
for the next row, if the same header is detected just append to the existing
dictionary.

Can anyone help with this code or provice feedback about how to proceed?

Thank you

**EDIT:** Thank you for your responses. I simplified my data for this example,
but here is what one of the actual meta columns looks like (still ; delimited,
but values types are mixed):

    
    
           P=0.9626;IPU=.$.+1T.+1T.+;IRF=ncRNA;IUC=UTR3;IGN=NCRNA00115;IGI=NCRNA00115,RP11-206L10.16-001;IET=0;IEO=0;IEN=.;IHT=0;IHVC=0;IHD=.;IHI=.;IHN=.;IDI=.;IDN=.;ITMAF=.;ITAMR=.;ITASN=.;ITAFR=.;ITEUR=.;ITNRB=+A;ISF=.;ISD=.;ISM=.;ISX=.;
    


Answer: You could just use one regular expression to split out the key=value pairs:

    
    
    import re
    
    key_value = re.compile('(?P<key>[A-Z]+)=(?P<value>\[^\s=;]+)(?:(?=;)|$)')
    

This expression uses named groups, but you could do without those if you find
it easier to read:

    
    
    key_value = re.compile('([A-Z]+)=([^\s=;])(?:(?=;)|$)')
    

The `(?:..)` group is a non-capturing group; it is only used here to demark
what the `|` or symbol applies to. The pattern matches uppercase characters
before the `=` symbol, and anything that is _not_ whitespace, a `=` or `;`
character, provided that there is a `;` _or_ the end of the string right after
the value.

This splits out keys and values for each line:

    
    
    >>> key_value = re.compile('(?P<key>[A-Z]+)=(?P<value>[^\s=;]+)(?:(?=;)|$)')
    >>> key_value.findall('Person1\tHEIGHT=60;WEIGHT=100;AGE=22')
    [('HEIGHT', '60'), ('WEIGHT', '100'), ('AGE', '22')]
    

This can easily then be turned into a dictionary:

    
    
    >>> dict(key_value.findall('Person1\tHEIGHT=60;WEIGHT=100;AGE=22'))
    {'AGE': '22', 'WEIGHT': '100', 'HEIGHT': '60'}
    

You can then write these with, for example, using
[`csv.DictWriter()`](http://docs.python.org/2/library/csv.html):

    
    
    import csv
    import re
    
    key_value = re.compile('(?P<key>[A-Z]+)=(?P<value>[^\s=;]+)(?:(?=;)|$)')
    
    with open(inputfilename) as infile, open(outputfilename, 'wb') as outfile:
        writer = csv.DictWriter(outfile, ('PERSON', 'HEIGHT', 'WEIGHT', 'AGE'), delimiter='\t')
        writer.writeheader()
    
        for line in infile:
            person = line.split('\t', 1)[0]
            row = dict(key_value.findall(line))
            row['PERSON'] = person
            writer.writerow(row)
    

Demo based on your real data sample:

    
    
    >>> dict(key_value.findall('       P=0.9626;IPU=.$.+1T.+1T.+;IRF=ncRNA;IUC=UTR3;IGN=NCRNA00115;IGI=NCRNA00115,RP11-206L10.16-001;IET=0;IEO=0;IEN=.;IHT=0;IHVC=0;IHD=.;IHI=.;IHN=.;IDI=.;IDN=.;ITMAF=.;ITAMR=.;ITASN=.;ITAFR=.;ITEUR=.;ITNRB=+A;ISF=.;ISD=.;ISM=.;ISX=.;\n'))
    {'ISX': '.', 'ITAMR': '.', 'IDN': '.', 'ISM': '.', 'IDI': '.', 'ISF': '.', 'ISD': '.', 'ITMAF': '.', 'IUC': 'UTR3', 'IGI': 'NCRNA00115,RP11-206L10.16-001', 'ITNRB': '+A', 'IHVC': '0', 'IET': '0', 'ITASN': '.', 'ITEUR': '.', 'ITAFR': '.', 'IEO': '0', 'IEN': '.', 'IGN': 'NCRNA00115', 'IRF': 'ncRNA', 'P': '0.9626', 'IHT': '0', 'IHI': '.', 'IHN': '.', 'IPU': '.$.+1T.+1T.+', 'IHD': '.'}
    

