用腐蚀ID提取生物计数？

Question

我有一个有很多列的文件，如：

ID1 XP_026389348.1_stearoyl-[acyl-carrier-protein]_9-desaturase,_chloroplastic_[Papaver_somniferum]
ID2 XP_026389348.1_stearoyl-[acyl-carrier-protein]_9-desaturase,_chloroplastic_[Papaver_somniferum]
ID3 XP_026389348.1_stearoyl-[acyl-carrier-protein]_9-desaturase,_chloroplastic_[Papaver_somniferum]
ID4 XP_026389348.1_stearoyl-[acyl-carrier-protein]_9-desaturase,_chloroplastic_[Papaver_somniferum]
ID5 XP_026389348.1_stearoyl-[acyl-carrier-protein]_9-desaturase,_chloroplastic_[Papaver_somniferum]
ID6 XP_022013305.1_60S_ribosomal_protein_L36-2-like_[Helianthus_annuus]
ID7 XP_022033863.1_60S_ribosomal_protein_L36-2-like_[Helianthus_annuus]
ID8 XP_022033864.1_60S_ribosomal_protein_L36-2-like_[Helianthus_annuus]
ID9 XP_022033865.1_60S_ribosomal_protein_L36-2-like_[Helianthus_annuus]
ID10    NP_850400.1_Plant_stearoyl-acyl-carrier-protein_desaturase_family_protein_[Arabidopsis_thaliana]
ID11    XP_015383392.1_60S_ribosomal_protein_L36-3-like_[Citrus_sinensis]
ID12    XP_015383392.1_60S_ribosomal_protein_L36-3-like_[Citrus_sinensis]
ID13    XP_019051818.1_PREDICTED:_stearoyl-[acyl-carrier-protein]_9-desaturase,_chloroplastic_isoform_X2_[Nelumbo_nucifera]
ID14    XP_019051818.1_PREDICTED:_stearoyl-[acyl-carrier-protein]_9-desaturase,_chloroplastic_isoform_X2_[Nelumbo_nucifera]
ID15    XP_019051818.1_PREDICTED:_stearoyl-[acyl-carrier-protein]_9-desaturase,_chloroplastic_isoform_X2_[Nelumbo_nucifera]
ID16    XP_021982111.1_stearoyl-[acyl-carrier-protein]_9-desaturase,_chloroplastic_[Helianthus_annuus]
ID17    NP_001150213.1_uncharacterized_protein_LOC100283843_[Zea_mays]
ID18    XP_027164486.1_stearoyl-[acyl-carrier-protein]_9-desaturase,_chloroplastic_[Coffea_eugenioides]
ID19    XP_009419937.1_PREDICTED:_60S_ribosomal_protein_L36-3-like_[Musa_acuminata]
ID20    XP_020267482.1_60S_ribosomal_protein_L36-2-like_[Asparagus_officinalis]

我想从第二列中提取有机体的名称，并用它们各自的ID进行计数，例如：

5   Papaver somniferum       ID1
                             ID2
                             ID3
                             ID4
                             ID5
4   Helianthus annuus        ID6
                             ID7
                             ID8
                             ID9
1   Arabidopsis thaliana     ID10
2   Citrus sinensis          ID11
                             ID12
3   Nelumbo nucifera         ID13
                             ID14
                             ID15
1   Helianthus annuus        ID16
1   Zea mays                 ID17
1   Coffea eugenioides       ID18
1   Musa acuminata           ID19
1   Asparagus officinalis    ID20

我试过一些东西：

cat file | cut -f2 | rev |awk -F "[" '{gsub("]", "");print $1 | "rev"}' | sed '/#/d' | sort |uniq -c| sort -nr

它提供了输出：

5   Papaver somniferum
4   Helianthus annuus
1   Arabidopsis thaliana
2   Citrus sinensis
3   Nelumbo nucifera
1   Helianthus annuus
1   Zea mays
1   Coffea eugenioides
1   Musa acuminata
1   Asparagus officinalis

谢谢你。

Answer 1

$ datamash -t' ' -g2 count 1 collapse 1 < <(sed 's/^\(ID[0-9]*\).*\[\([^]]*\)\]$/\1 \2/' file) \
   | awk '{ print $2,$1,$3 }' \
   | sed 's/,\(ID[^,]*\)/\n# # \1/g' \
   | column -t \
   | tr '#' ' '
5  Papaver_somniferum     ID1
                          ID2
                          ID3
                          ID4
                          ID5
4  Helianthus_annuus      ID6
                          ID7
                          ID8
                          ID9
1  Arabidopsis_thaliana   ID10
2  Citrus_sinensis        ID11
                          ID12
3  Nelumbo_nucifera       ID13
                          ID14
                          ID15
1  Helianthus_annuus      ID16
1  Zea_mays               ID17
1  Coffea_eugenioides     ID18
1  Musa_acuminata         ID19
1  Asparagus_officinalis  ID20

Step 1：使用sed提取ID和生物名称：

$ sed 's/^\(ID[0-9]*\).*\[\([^]]*\)\]$/\1 \2/' file
ID1 Papaver_somniferum
ID2 Papaver_somniferum
ID3 Papaver_somniferum
ID4 Papaver_somniferum
ID5 Papaver_somniferum
ID6 Helianthus_annuus
ID7 Helianthus_annuus
...

Step 2：将sed的S输出输入到GNU datamash并在第二个字段上分组，在第一个字段上计数和折叠：

$ datamash -t' ' -g2 count 1 collapse 1 < <(sed 's/^\(ID[0-9]*\).*\[\([^]]*\)\]$/\1 \2/' file)
Papaver_somniferum 5 ID1,ID2,ID3,ID4,ID5
Helianthus_annuus 4 ID6,ID7,ID8,ID9
Arabidopsis_thaliana 1 ID10
Citrus_sinensis 2 ID11,ID12
Nelumbo_nucifera 3 ID13,ID14,ID15
Helianthus_annuus 1 ID16
Zea_mays 1 ID17
Coffea_eugenioides 1 ID18
Musa_acuminata 1 ID19
Asparagus_officinalis 1 ID20

Step 3：添加一些格式以将输出带到表表单中：

• awk '{ print $2,$1,$3 }'交换列2(计数)和1(有机体名称)
• sed 's/,\(ID[^,]*\)/\n# # \1/g'将每个逗号和ID替换为换行符，两个空格分隔的虚拟字符#和ID (使用sed)
• column -t格式表
• tr '#' ' '用空格替换虚拟字符#

Answer 2

使用GNU awk和GNU column：

awk -F'[][ ]*' '{print ($(NF-1)==n?OFS:$(NF-1)),$1; n=$(NF-1)}' OFS=, file \
 | awk -F, -v OFS=, 'NF==3{line=line RS $0;i++} NF==2{if(line)print i,line; line=$0; i=1}' \
 | column -t -n -s,

第一个awk命令提取并以该格式显示想要的字段：

Papaver_somniferum,ID1
,,ID2
,,ID3
...

第二个awk命令构建一个由所有同名字段组成的新行，并将其计数添加到前面。

最后，列命令在-n选项的帮助下将结果显示在3列中：

5  Papaver_somniferum    ID1
                         ID2
                         ID3
                         ID4
                         ID5
4  Helianthus_annuus     ID6
                         ID7
...