☰

如何将列中的字段分隔开（放入其自己的命名列中），然后从每个条目中删除重复的单词

tag-icon • tag-icon text-processing awk sed bioinformatics
如何将列中的字段分隔开（放入其自己的命名列中），然后从每个条目中删除重复的单词

我有一个包含 9 个变量的 tsv 文件，如下所示：

> seqnames  start   endwidth    strand  metadata    X.10logMacsq    annotation  distanceToTSS

元数据列包含我想要进行一些分析的信息，但我首先需要拆分条目并将它们放入自己的列中（带有标题）。元数据如下所示（第一行）：

ID=SRX067411;Name=H3K27ac%20(@%20HMEC);Title=GSM733660:%20Bernstein%20HMEC%20H3K27ac;Cell%20group=Breast;<br>source_name=HMEC;biomaterial_provider=Lonza;lab=Broad;lab%20description=Bernstein%20-%20Broad%20Institute;datatype=ChipSeq;datatype%20description=Chromatin%20IP%20Sequencing;cell%20organism=human;cell%20description=mammary%20epithelial%20cells;cell%20karyotype=normal;cell%20lineage=ectoderm;cell%20sex=U;antibody%20antibodydescription=rabbit%20polyclonal.%20Antibody%20Target:%20H3K27ac;

此列（每行）总共有 27 个条目（此处未全部显示），但我想我应该先将它们全部写入自己的列中，然后删除不需要的条目。一旦他们有了描述性的列标题，那么我也可以删除他们的名字（例如：ID=SRX 只是 SRX 等等）

示例文件输入（第一行）

seqnames    start   end width   strand  metadata    X.10logMacsq    annotation  geneChr geneStart   geneEnd geneLength  geneStrand  geneId  distanceToTSS
chr2    1711333 1711568 236 *   ID=SRX067411;Name=H3K27ac%20(@%20HMEC);Title=GSM733660:%20Bernstein%20HMEC%20H3K27ac;Cell%20group=Breast;<br>source_name=HMEC;biomaterial_provider=Lonza;lab=Broad;lab%20description=Bernstein%20-%20Broad%20Institute;datatype=ChipSeq;datatype%20description=Chromatin%20IP%20Sequencing; 447 Intron (uc002qxa.3/7837, intron 1 of 22)    1   1635659 1748291 112633  2   7837    36723

谁能帮我解决这个问题或给我一些建议吗？我对 Bash 还很陌生，对这些命令还不太熟悉。

到目前为止，我刚刚设法清理了一些文件：

cut --complement -f 9-14 hisHMECanno.tsv | sed 's/%20/ /g' > hisHMECannoFilt.tsv

（原始文件有一些不必要的列，我刚刚删除了）

然后我一直尝试使用 awk 将条目分隔成制表符分隔的列，但无济于事。

答案1

以下 perl 脚本使用文本::CSV模块读取 TSV 文件并输出格式正确的 TSV 数据。

如果需要，它会自动引用字段，并使用 的Text::CSV设置undef_str将未定义的元数据字段输出为带引号的空字符串""（带有如何将其打印为N/A或 的注释示例--）。

最多只应取消注释其中 3 行之一，其他应删除或注释掉。如果您只想将这些字段留空，请删除/注释掉所有这三行。

我建议在这些未定义的字段中添加一些内容，因为这样可以更轻松地使用其他工具对该脚本的输出进行后处理，这些工具可能将两个或多个选项卡（即空字段）视为与单个选项卡相同（例如，两者awk和perl默认情况下会这样做，除非您通过明确地将字段分隔符设置为单个选项卡来告诉他们不要这样做，而不是默认的“任意数量的空白”）。

Text::CSV为 debian 和相关发行版打包为libtext-csv-perl（纯 perl 版本）和libtext-csv-xs-perl（更快编译的 C 模块）。使用apt install libtext-csv-perl.其他发行版可能也打包了它。否则，请使用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;
};

将其另存为，例如process-tsv.pl，使其可执行，chmod +x process-tsv.pl并在运行时为其提供文件名参数。例如

$ ./process-tsv.pl filename.tsv

它将向 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

当然，您可以将输出重定向到 shell 中的文件：

./process-tsv.pl input.tsv > output.tsv

答案2

在每个 Unix 机器上的任何 shell 中使用任何 awk，这可能是您想要做的，但没有示例输入/输出，我们可以用它来测试猜测：

$ cat tst.awk
BEGIN { FS=OFS="\t" }
{
    gsub(/%20/," ")
    gsub(/<br>/,"")
}
NR==1 {
    hdr = $0
    next
}
NR==2 {
    orig = $0
    gsub(/=[^=;]+;/,OFS,$6)
    sub(OFS"$","",$6)
    tags = $6
    $0 = hdr
    $6 = tags
    print
    $0 = orig
}
{
    gsub(/[^=;]+=/,OFS,$6)
    sub("^"OFS,"",$6)
    gsub(/;/,"",$6)
    print
}


$ awk -f tst.awk file
>       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
>       foo     bar     etc     anon    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     end     more    stuff

以上是使用此输入文件运行的：

$ cat file
>       seqnames        start   endwidth        strand  metadata        X.10logMacsq    annotation      distanceToTSS
>       foo     bar     etc     anon    ID=SRX067411;Name=H3K27ac%20(@%20HMEC);Title=GSM733660:%20Bernstein%20HMEC%20H3K27ac;Cell%20group=Breast;<br>source_name=HMEC;biomaterial_provider=Lonza;lab=Broad;lab%20description=Bernstein%20-%20Broad%20Institute;datatype=ChipSeq;datatype%20description=Chromatin%20IP%20Sequencing;cell%20organism=human;cell%20description=mammary%20epithelial%20cells;cell%20karyotype=normal;cell%20lineage=ectoderm;cell%20sex=U;antibody%20antibodydescription=rabbit%20polyclonal.%20Antibody%20Target:%20H3K27ac;       end     more    stuff

其中所有空格都是制表符。

column您可以通过使用使其可视化为表格来查看值如何与标签（列标题字符串）对齐：

输入：

$ column -s$'\t' -t file
>  seqnames  start  endwidth  strand  metadata                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           X.10logMacsq  annotation  distanceToTSS
>  foo       bar    etc       anon    ID=SRX067411;Name=H3K27ac%20(@%20HMEC);Title=GSM733660:%20Bernstein%20HMEC%20H3K27ac;Cell%20group=Breast;<br>source_name=HMEC;biomaterial_provider=Lonza;lab=Broad;lab%20description=Bernstein%20-%20Broad%20Institute;datatype=ChipSeq;datatype%20description=Chromatin%20IP%20Sequencing;cell%20organism=human;cell%20description=mammary%20epithelial%20cells;cell%20karyotype=normal;cell%20lineage=ectoderm;cell%20sex=U;antibody%20antibodydescription=rabbit%20polyclonal.%20Antibody%20Target:%20H3K27ac;  end           more        stuff

输出：

$ awk -f tst.awk file | column -s$'\t' -t
>  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
>  foo       bar    etc       anon    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  end           more        stuff

答案3

使用磨坊主

$ mlr --tsv put -S '
    $metadata = gsub($metadata,"%20"," "); $metadata = gsub($metadata,"<br>|;$","")
  ' then put -S '
  $* = mapsum($*,splitkvx($metadata,"=",";"))
' then cut -x -f metadata HisHMECanno.tsv
seqnames        start   end     width   strand  X.10logMacsq    annotation      geneChr geneStart       geneEnd geneLength                                                 geneStrand       geneId  distanceToTSS   ID      Name    Title   Cell group      source_name     biomaterial_provider   lab                                                 lab description  datatype        datatype description
chr2    1711333 1711568 236     *       447     Intron (uc002qxa.3/7837, intron 1 of 22)        1       1635659 1748291                                                    112633   2       7837    36723   SRX067411       H3K27ac (@ HMEC)        GSM733660: Bernstein HMEC H3K27ac       Breast HMEC                                                Lonza    Broad   Bernstein - Broad Institute     ChipSeq Chromatin IP Sequencing

这两个put命令可以组合起来，但是我认为将它们分为“数据清理”步骤和“字段分割”步骤会更清楚。

将输出格式更改为 CSV 以使字段分割更清晰：

mlr --itsv --ocsv put -S '
  $metadata = gsub($metadata,"%20"," "); $metadata = gsub($metadata,"<br>|;$","")
' then put -S '
  $* = mapsum($*,splitkvx($metadata,"=",";"))
' then cut -x -f metadata HisHMECanno.tsv
seqnames,start,end,width,strand,X.10logMacsq,annotation,geneChr,geneStart,geneEnd,geneLength,geneStrand,geneId,distanceToTSS,ID,Name,Title,Cell group,source_name,biomaterial_provider,lab,lab description,datatype,datatype description
chr2,1711333,1711568,236,*,447,"Intron (uc002qxa.3/7837, intron 1 of 22)",1,1635659,1748291,112633,2,7837,36723,SRX067411,H3K27ac (@ HMEC),GSM733660: Bernstein HMEC H3K27ac,Breast,HMEC,Lonza,Broad,Bernstein - Broad Institute,ChipSeq,Chromatin IP Sequencing

答案4

这可以使用 Python 字典和列表数据结构，结合正则表达式和列表理解来完成。

python3 -c 'import sys, re
ifile = sys.argv[1]
fs,rs = ofs,ors = "\t","\n"
with open(ifile) as f:
  for nr,l in enumerate(f,1):
    F = l.rstrip(rs).split(fs)
    if nr == 1:
      H = F
      idx_md = F.index("metadata")
      continue
    md_hdrs = re.findall(r"[^=;]+(?==)",F[idx_md])
    md = dict(t.split("=") for t in re.sub(r";+$","",F[idx_md]).split(";"))
    if nr == 2:
      print(*H[:idx_md], *md_hdrs, *H[idx_md+1:], sep=ofs)
    print(*F[:idx_md], *[md.get(key,"") for key in md_hdrs], *F[idx_md+1:], sep=ofs)
' file

输出：

seqnames    start   end width   strand  ID  Name    Title   Cell%20group    <br>source_name biomaterial_provider    lab lab%20description   datatype    datatype%20description  X.10logMacsq    annotation  geneChr geneStart   geneEnd geneLength  geneStrand  geneId  distanceToTSS
chr2    1711333 1711568 236 *   SRX067411   H3K27ac%20(@%20HMEC)    GSM733660:%20Bernstein%20HMEC%20H3K27ac Breast  HMEC    Lonza   Broad   Bernstein%20-%20Broad%20Institute   ChipSeq Chromatin%20IP%20Sequencing 447 Intron (uc002qxa.3/7837, intron_1_of_22)    1   1635659 1748291 112633  2   7837    36723

相关内容