Paul
Reputation: 321
Parsing data with more conditions in awk
I have big table of data (50 columns, >50 rows) and I need to parse specific columns first by & and then by : in column (3,4,5). Problem is, that I do not know, how many times is observed in my data value separated by & and :. And then in column (9,10) parse by ().
The reason for parsing is because I need to filtrate data by its numeric value.
Data are tab delimeter. If the value missing - there is a mark -.
Lets see the input tab:
Ref Alt GMAF EUR_MAF ExAC_MAF Consequence IMPACT BIOTYPE SIFT POLYPHEN Chr DP Freq Gen SYMBOL
AC A - - - TF_binding_site_variant MODIFIER - tolerated(0.51) benign(0.001) chrM 98 94.9 HOM -
C CTTTCTT G:0.0004&-:0.4535 - TTTCTT:0.496&TGTCTT:8.275e-06&TCTCTT:8.275e-06 inframe_insertion MODERATE protein_coding - - chr1 109 24.77 HET CDK11A
C CAT -:0.2622&A:0.2188 - - intron_variant MODIFIER protein_coding tolerated(1) benign(0.002) chr1 86 36.05 HET CDK11A
A AG -:0.4958 G:0.6889 - intron_variant MODIFIER protein_coding deleterious(0) probably_damaging(0.996) chr1 50 32 HET MMEL1
C CG -:0.0000 G:1 G:1.000 intron_variant MODIFIER protein_coding tolerated(1) benign(0) chr1 84 95.24 HOM PLEKHG5
CT C T:0.1619 -:0.7724 - intron_variant MODIFIER protein_coding tolerated(1) benign(0) chr1 55 58.18 HET TMEM51
CCTT C CTT:0.2766 -:0.8062 -:0.775 intron_variant MODIFIER protein_coding - - chr1 58 48.28 HET CLCNKB
C CTCCTG TCCTG:0.0976 TCCTG:0.0984 - intron_variant MODIFIER protein_coding - - chr1 58 50 HET CLCNKB
AG A -:0.1006 -:0.0984 A:1.602e-03&-:0.083 intron_variant MODIFIER protein_coding - - chr1 88 40.91 HET CLCNKB
and required output table:
Ref Alt GMAF GMAF GMAF GMAF EUR_MAF EUR_MAF ExAC_MAF ExAC_MAF ExAC_MAF ExAC_MAF ExAC_MAF ExAC_MAF Consequence IMPACT BIOTYPE SIFT SIFT POLYPHEN POLYPHEN Chr DP Freq Gen SYMBOL
AC A - - 0.4535 - - - - - - - TF_binding_site_variant MODIFIER - tolerated 0.51 benign 0.001 chrM 98 94.9 HOM -
C CTTTCTT G 0.0004 A 0.2188 - - TTTCTT 0.496 TGTCTT 8.28E-006 TCTCTT 8.28E-006 inframe_insertion MODERATE protein_coding - - chr1 109 24.77 HET CDK11A
C CAT - 0.2622 - - - - - - - - - - intron_variant MODIFIER protein_coding tolerated 1 benign 0.002 chr1 86 36.05 HET CDK11A
A AG - 0.4958 - - G 0.6889 - - - - - - intron_variant MODIFIER protein_coding deleterious 0 probably_damaging 0.996 chr1 50 32 HET MMEL1
C CG - 0 - - G 1 G 1 - - - - intron_variant MODIFIER protein_coding tolerated 1 benign 0 chr1 84 95.24 HOM PLEKHG5
CT C T 0.1619 - - - 0.7724 - - - - - intron_variant MODIFIER protein_coding tolerated 1 benign 0 chr1 55 58.18 HET TMEM51
CCTT C CTT 0.2766 - - - 0.8062 - 0.775 - - - - intron_variant MODIFIER protein_coding - - - chr1 58 48.28 HET CLCNKB
C CTCCTG TCCTG 0.0976 - - TCCTG 0.0984 - - - - - intron_variant MODIFIER protein_coding - - - chr1 58 50 HET CLCNKB
AG A - 0.1006 - - - 0.0984 A 1.60E-003 - 0.083 - - intron_variant MODIFIER protein_coding - - - chr1 88 40.91 HET CLCNKB
Solution is not required in awk / sed, but is is preferable. Thank you for any idea.
EDIT READABLE TABLE:
INPUT:
+------+---------+-------------------+--------------+------------------------------------------------+-------------------------+----------+----------------+-----------------+--------------------------+------+-----+-------+-----+---------+
| Ref | Alt | GMAF | EUR_MAF | ExAC_MAF | Consequence | IMPACT | BIOTYPE | SIFT | POLYPHEN | Chr | DP | Freq | Gen | SYMBOL |
+------+---------+-------------------+--------------+------------------------------------------------+-------------------------+----------+----------------+-----------------+--------------------------+------+-----+-------+-----+---------+
| AC | A | - | - | - | TF_binding_site_variant | MODIFIER | - | tolerated(0.51) | benign(0.001) | chrM | 98 | 94.9 | HOM | - |
+------+---------+-------------------+--------------+------------------------------------------------+-------------------------+----------+----------------+-----------------+--------------------------+------+-----+-------+-----+---------+
| C | CTTTCTT | G:0.0004&-:0.4535 | - | TTTCTT:0.496&TGTCTT:8.275e-06&TCTCTT:8.275e-06 | inframe_insertion | MODERATE | protein_coding | - | - | chr1 | 109 | 24.77 | HET | CDK11A |
+------+---------+-------------------+--------------+------------------------------------------------+-------------------------+----------+----------------+-----------------+--------------------------+------+-----+-------+-----+---------+
| C | CAT | -:0.2622&A:0.2188 | - | - | intron_variant | MODIFIER | protein_coding | tolerated(1) | benign(0.002) | chr1 | 86 | 36.05 | HET | CDK11A |
+------+---------+-------------------+--------------+------------------------------------------------+-------------------------+----------+----------------+-----------------+--------------------------+------+-----+-------+-----+---------+
| A | AG | -:0.4958 | G:0.6889 | - | intron_variant | MODIFIER | protein_coding | deleterious(0) | probably_damaging(0.996) | chr1 | 50 | 32 | HET | MMEL1 |
+------+---------+-------------------+--------------+------------------------------------------------+-------------------------+----------+----------------+-----------------+--------------------------+------+-----+-------+-----+---------+
| C | CG | -:0.0000 | G:1 | G:1.000 | intron_variant | MODIFIER | protein_coding | tolerated(1) | benign(0) | chr1 | 84 | 95.24 | HOM | PLEKHG5 |
+------+---------+-------------------+--------------+------------------------------------------------+-------------------------+----------+----------------+-----------------+--------------------------+------+-----+-------+-----+---------+
| CT | C | T:0.1619 | -:0.7724 | - | intron_variant | MODIFIER | protein_coding | tolerated(1) | benign(0) | chr1 | 55 | 58.18 | HET | TMEM51 |
+------+---------+-------------------+--------------+------------------------------------------------+-------------------------+----------+----------------+-----------------+--------------------------+------+-----+-------+-----+---------+
| CCTT | C | CTT:0.2766 | -:0.8062 | -:0.775 | intron_variant | MODIFIER | protein_coding | - | - | chr1 | 58 | 48.28 | HET | CLCNKB |
+------+---------+-------------------+--------------+------------------------------------------------+-------------------------+----------+----------------+-----------------+--------------------------+------+-----+-------+-----+---------+
| C | CTCCTG | TCCTG:0.0976 | TCCTG:0.0984 | - | intron_variant | MODIFIER | protein_coding | - | - | chr1 | 58 | 50 | HET | CLCNKB |
+------+---------+-------------------+--------------+------------------------------------------------+-------------------------+----------+----------------+-----------------+--------------------------+------+-----+-------+-----+---------+
| AG | A | -:0.1006 | -:0.0984 | A:1.602e-03&-:0.083 | intron_variant | MODIFIER | protein_coding | - | - | chr1 | 88 | 40.91 | HET | CLCNKB |
+------+---------+-------------------+--------------+------------------------------------------------+-------------------------+----------+----------------+-----------------+--------------------------+------+-----+-------+-----+---------+
OTPUT:
+------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+
| Ref | Alt | GMAF | GMAF | GMAF | GMAF | EUR_MAF | EUR_MAF | ExAC_MAF | ExAC_MAF | ExAC_MAF | ExAC_MAF | ExAC_MAF | ExAC_MAF | Consequence | IMPACT | BIOTYPE | SIFT | SIFT | POLYPHEN | POLYPHEN | Chr | DP | Freq | Gen | SYMBOL |
+------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+
| AC | A | - | | - | 0.4535 | - | - | - | - | - | - | - | | TF_binding_site_variant | MODIFIER | - | tolerated | 0.51 | benign | 0.001 | chrM | 98 | 94.9 | HOM | - |
+------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+
| C | CTTTCTT | G | 0.0004 | A | 0.2188 | - | - | TTTCTT | 0.496 | TGTCTT | 8.28E-006 | TCTCTT | 8.28E-006 | inframe_insertion | MODERATE | protein_coding | - | | - | | chr1 | 109 | 24.77 | HET | CDK11A |
+------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+
| C | CAT | - | 0.2622 | - | - | - | - | - | - | - | - | - | - | intron_variant | MODIFIER | protein_coding | tolerated | 1 | benign | 0.002 | chr1 | 86 | 36.05 | HET | CDK11A |
+------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+
| A | AG | - | 0.4958 | - | - | G | 0.6889 | - | - | - | - | - | - | intron_variant | MODIFIER | protein_coding | deleterious | 0 | probably_damaging | 0.996 | chr1 | 50 | 32 | HET | MMEL1 |
+------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+
| C | CG | - | 0 | - | - | G | 1 | G | 1 | - | - | - | - | intron_variant | MODIFIER | protein_coding | tolerated | 1 | benign | 0 | chr1 | 84 | 95.24 | HOM | PLEKHG5 |
+------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+
| CT | C | T | 0.1619 | - | - | - | 0.7724 | - | | - | - | - | - | intron_variant | MODIFIER | protein_coding | tolerated | 1 | benign | 0 | chr1 | 55 | 58.18 | HET | TMEM51 |
+------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+
| CCTT | C | CTT | 0.2766 | - | - | - | 0.8062 | - | 0.775 | - | - | - | - | intron_variant | MODIFIER | protein_coding | - | | - | - | chr1 | 58 | 48.28 | HET | CLCNKB |
+------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+
| C | CTCCTG | TCCTG | 0.0976 | - | - | TCCTG | 0.0984 | - | | - | - | - | - | intron_variant | MODIFIER | protein_coding | - | | - | - | chr1 | 58 | 50 | HET | CLCNKB |
+------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+
| AG | A | - | 0.1006 | - | - | - | 0.0984 | A | 1.60E-003 | - | 0.083 | - | - | intron_variant | MODIFIER | protein_coding | - | | - | - | chr1 | 88 | 40.91 | HET | CLCNKB |
+------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+
My try was to use something like this (according my previous question):
awk -F'[ \t:&]+' -v OFS='\t' '{$1=$1}1' file
but this does not parse document exact my criteria.
Upvotes: 0
Views: 71
Answers (1)
Jose Ricardo Bustos M.
Reputation: 8174
You can try with gawk
awk '
BEGIN{OFS="\t"; split("3 4 5 9 10",a)}
NR==FNR{
for(i in a){
n=split($a[i], v, /[:&(]/);
if(n>max[a[i]]) max[a[i]]=n
}
next
}
{
for(i in max){
s=""
if(FNR==1) for(j=1; j<=max[i]; ++j) s = s OFS $i
else{
gsub(")","",$i)
n=split($i, v, /[:&(]/)
for(j=1; j<=max[i]; ++j){
if(j<=n) s = s OFS v[j]
else s = s OFS "-"
}
}
$i = s
}
print
}' file{,}
you get,
Ref Alt GMAF GMAF GMAF GMAF EUR_MAF EUR_MAF ExAC_MAF ExAC_MAF ExAC_MAF ExAC_MAF ExAC_MAF ExAC_MAF Consequence IMPACT BIOTYPE SIFT SIFT POLYPHEN POLYPHEN Chr DP Freq Gen SYMBOL AC A - - - - - - - - - - - - TF_binding_site_variant MODIFIER - tolerated 0.51 benign 0.001 chrM 98 94.9 HOM - C CTTTCTT G 0.0004 - 0.4535 - - TTTCTT 0.496 TGTCTT 8.275e-06 TCTCTT 8.275e-06 inframe_insertion MODERATE protein_coding - - - - chr1 109 24.77 HET CDK11A C CAT - 0.2622 A 0.2188 - - - - - - - - intron_variant MODIFIER protein_coding tolerated 1 benign 0.002 chr1 86 36.05 HET CDK11A A AG - 0.4958 - - G 0.6889 - - - - - - intron_variant MODIFIER protein_coding deleterious 0 probably_damaging 0.996 chr1 50 32 HET MMEL1 C CG - 0.0000 - - G 1 G 1.000 - - - - intron_variant MODIFIER protein_coding tolerated 1 benign 0 chr1 84 95.24 HOM PLEKHG5 CT C T 0.1619 - - - 0.7724 - - - - - - intron_variant MODIFIER protein_coding tolerated 1 benign 0 chr1 55 58.18 HET TMEM51 CCTT C CTT 0.2766 - - - 0.8062 - 0.775 - - - - intron_variant MODIFIER protein_coding - - - - chr1 58 48.28 HET CLCNKB C CTCCTG TCCTG 0.0976 - - TCCTG 0.0984 - - - - - - intron_variant MODIFIER protein_coding - - - - chr1 58 50 HET CLCNKB AG A - 0.1006 - - - 0.0984 A 1.602e-03 - 0.083 - - intron_variant MODIFIER protein_coding - - - - chr1 88 40.91 HET CLCNKB
+------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+ | Ref | Alt | GMAF | GMAF | GMAF | GMAF | EUR_MAF | EUR_MAF | ExAC_MAF | ExAC_MAF | ExAC_MAF | ExAC_MAF | ExAC_MAF | ExAC_MAF | Consequence | IMPACT | BIOTYPE | SIFT | SIFT | POLYPHEN | POLYPHEN | Chr | DP | Freq | Gen | SYMBOL | +------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+ | AC | A | - | - | - | - | - | - | - | - | - | - | - | - | TF_binding_site_variant | MODIFIER | - | tolerated | 0.51 | benign | 0.001 | chrM | 98 | 94.9 | HOM | - | +------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+ | C | CTTTCTT | G | 0.0004 | - | 0.4535 | - | - | TTTCTT | 0.496 | TGTCTT | 8.275e-06 | TCTCTT | 8.275e-06 | inframe_insertion | MODERATE | protein_coding | - | - | - | - | chr1 | 109 | 24.77 | HET | CDK11A | +------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+ | C | CAT | - | 0.2622 | A | 0.2188 | - | - | - | - | - | - | - | - | intron_variant | MODIFIER | protein_coding | tolerated | 1 | benign | 0.002 | chr1 | 86 | 36.05 | HET | CDK11A | +------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+ | A | AG | - | 0.4958 | - | - | G | 0.6889 | - | - | - | - | - | - | intron_variant | MODIFIER | protein_coding | deleterious | 0 | probably_damaging | 0.996 | chr1 | 50 | 32 | HET | MMEL1 | +------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+ | C | CG | - | 0.0000 | - | - | G | 1 | G | 1.000 | - | - | - | - | intron_variant | MODIFIER | protein_coding | tolerated | 1 | benign | 0 | chr1 | 84 | 95.24 | HOM | PLEKHG5 | +------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+ | CT | C | T | 0.1619 | - | - | - | 0.7724 | - | - | - | - | - | - | intron_variant | MODIFIER | protein_coding | tolerated | 1 | benign | 0 | chr1 | 55 | 58.18 | HET | TMEM51 | +------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+ | CCTT | C | CTT | 0.2766 | - | - | - | 0.8062 | - | 0.775 | - | - | - | - | intron_variant | MODIFIER | protein_coding | - | - | - | - | chr1 | 58 | 48.28 | HET | CLCNKB | +------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+ | C | CTCCTG | TCCTG | 0.0976 | - | - | TCCTG | 0.0984 | - | - | - | - | - | - | intron_variant | MODIFIER | protein_coding | - | - | - | - | chr1 | 58 | 50 | HET | CLCNKB | +------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+ | AG | A | - | 0.1006 | - | - | - | 0.0984 | A | 1.602e-03 | - | 0.083 | - | - | intron_variant | MODIFIER | protein_coding | - | - | - | - | chr1 | 88 | 40.91 | HET | CLCNKB | +------+---------+-------+--------+------+--------+---------+---------+----------+-----------+----------+-----------+----------+-----------+-------------------------+----------+----------------+-------------+------+-------------------+----------+------+-----+-------+-----+---------+
Upvotes: 3
Related Questions
- Extracting specific data from text file - bash
- parse text with sed + awk + bash
- Parsing data using awk
- BASH. Parsing blocks of text with additional conditions
- awk to parse input with multiple conditions
- Parsing data with awk
- How to get specific data from block of data based on condition
- Need help parsing string in bash
- parsing multiple values from a file
- Parsing using awk or sed in Unix