The following perl script uses the Text::CSV module to read the TSV file and to output properly formatted TSV data.
It automatically quotes fields if required and uses Text::CSV's undef_str setting to output undefined metadata fields as a quoted empty string "" (with commented out examples of how to print them as N/A or -- instead).
At most, only one of those 3 lines should be uncommented, the others should be deleted or commented out. If you just want those fields to be empty, delete/comment out all three of those lines.
I recommend having something in those undefined fields because it will make it easier to post-process the output of this script with other tools that might treat two or more tabs (i.e. an empty field) the same as just a single tab (e.g. both awk and perl will do that by default, unless you tell them not to by explicitly setting the field separator to a single tab, rather than the default of "any amount of whitespace").
Text::CSV is packaged for debian and related distros as libtext-csv-perl (pure perl version) and libtext-csv-xs-perl (faster compiled C module). Install both with apt install libtext-csv-perl. Other distros probably have it packaged too. Otherwise, install it with cpan.
#!/usr/bin/perl
use strict;
use Text::CSV qw(csv);
my $csv=Text::CSV->new({sep_char => "\t", quote_space => 0});
# optional: define how to print undefined fields
#$csv->undef_str ('--');
#$csv->undef_str ('N/A');
$csv->undef_str ('""');
# get header line, split into an arrayref called $cols
my $cols = $csv->getline(*ARGV);
# get first data row, extract headers & data from metadata field
my $row = $csv->getline(*ARGV);
# The following line assumes that the metadata in the FIRST data row
# contains ALL of the metadata fields in the exact order you want them
# included in the output.
#
my $md_headers = extract_metadata_headers($$row[4]);
#
# If this is not the case, then delete the extract_metadata_headers
# subroutine and define the metadata fields manually with something
# like:
#
#my $md_headers = [
# 'ID', 'Name', 'Title', 'Cell group', 'source_name',
# 'biomaterial_provider', 'lab', 'lab description', 'datatype',
# 'datatype description', 'cell organism', 'cell description',
# 'cell karyotype', 'cell lineage', 'cell sex',
# 'antibody antibodydescription'
#];
# This defines both the extra metadata headers **and** the order
# that they will be included in each output row.
# extract the data from the metadata field
my $md_data = extract_metadata($$row[4]);
# replace the metadata header in $cols aref with the md headers
splice @$cols,4,1,@$md_headers;
# replace the metadata field in $row aref with the md fields
splice @$row,4,1,@$md_data;
# print the updated header line and the first row of data
$csv->say(*STDOUT,$cols);
$csv->say(*STDOUT,$row);
# main loop: extract and print the rest of the data
while (my $row = $csv->getline(*ARGV)) {
my $md_data = extract_metadata($$row[4]);
splice @$row,4,1,@$md_data;
$csv->say(*STDOUT,$row);
}
###
### subroutines
###
sub extract_metadata_headers {
my $md = clean_metadata(shift);
my @metadata = split /;/, $md;
my @headers=();
foreach (@metadata) {
next if m/^\s*$/; # skip empty metadata
my ($key,$val) = split /=/;
push @headers, $key;
};
return \@headers;
};
sub extract_metadata {
my $md = clean_metadata(shift);
my @metadata = split /;/, $md;
my %data=();
foreach (@metadata) {
next if m/^\s*$/; # skip empty metadata
my ($key,$val) = split /=/;
$data{$key} = $val;
};
return [@data{@$md_headers}];
};
sub clean_metadata {
my $md = shift;
$md =~ s/%(\d\d)/chr hex $1/eg; # decode %-encoded spaces etc.
$md =~ s/<[^>]*>//g; # remove HTML crap like <br>
return $md;
};
Save it as, e.g., process-tsv.pl, make it executable with chmod +x process-tsv.pl and give it a filename argument when you run it. e.g.
$ ./process-tsv.pl filename.tsv
It will produce output like this to stdout:
$ ./process-tsv.pl input.tsv
seqnames start endwidth strand ID Name Title Cell group source_name biomaterial_provider lab lab description datatype datatype description cell organism cell description cell karyotype cell lineage cell sex antibody antibodydescription X.10logMacsq annotation distanceToTSS
seq1 1 10 X SRX067411 H3K27ac (@ HMEC) GSM733660: Bernstein HMEC H3K27ac Breast HMEC Lonza Broad Bernstein - Broad Institute ChipSeq Chromatin IP Sequencing human mammary epithelial cells normal ectoderm U rabbit polyclonal. Antibody Target: H3K27ac x10 annot dist
seq2 2 20 Y SRX067411 H3K27ac (@ HMEC) GSM733660: Bernstein HMEC H3K27ac "" "" Lonza Broad Bernstein - Broad Institute ChipSeq Chromatin IP Sequencing human mammary epithelial cells normal ectoderm U "" Y10 annot2 dist2
You can, of course, redirect the output to a file in your shell:
./process-tsv.pl input.tsv > output.tsv
