/
sql_filter.sh
executable file
·69 lines (54 loc) · 3.02 KB
/
sql_filter.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
#!/bin/bash
acc=
db_name=
file=Trinotate_annotation_report
sed 's/|/:/g' $file | sed 's/\t/|/g' | sed 's/ //g' > scratch
sqlite3 $db_name "CREATE TABLE report(gene_id, transcript_id, sprot_Top_BLASTX_hit, TrEMBL_Top_BLASTX_hit, RNAMMER, prot_id, prot_coords, sprot_Top_BLASTP_hit, TrEMBL_Top_BLASTP_hit, Pfam, SignalP, TmHMM, eggnog, gene_ontology_blast, gene_ontology_pfam, transcript, peptide);"
sqlite3 $db_name ".import scratch report"
sqlite3 $db_name "SELECT * from report
WHERE sprot_Top_BLASTX_hit like '%Viridiplantae%'
OR TrEMBL_Top_BLASTX_hit like '%Viridiplantae%'
OR sprot_Top_BLASTP_hit like '%Viridiplantae%'
OR TrEMBL_Top_BLASTP_hit like '%Viridiplantae%';" > $acc.annotated_genes
sqlite3 $db_name "SELECT transcript_id from report
WHERE sprot_Top_BLASTX_hit like '%Viridiplantae%'
OR TrEMBL_Top_BLASTX_hit like '%Viridiplantae%'
OR sprot_Top_BLASTP_hit like '%Viridiplantae%'
OR TrEMBL_Top_BLASTP_hit like '%Viridiplantae%';" > $acc.annotated_genes.headers
sqlite3 $db_name "SELECT * from report
WHERE sprot_Top_BLASTX_hit not like '%Viridiplantae%'
AND TrEMBL_Top_BLASTX_hit not like '%Viridiplantae%'
AND sprot_Top_BLASTP_hit not like '%Viridiplantae%'
AND TrEMBL_Top_BLASTP_hit not like '%Viridiplantae%';" > $acc.not_viridiplantae_report
cp $acc.not_viridiplantae_report not_viridiplantae.scratch
sqlite3 $db_name "CREATE TABLE not_viridiplantae(gene_id, transcript_id, sprot_Top_BLASTX_hit, TrEMBL_Top_BLASTX_hit, RNAMMER, prot_id, prot_coords, sprot_Top_BLASTP_hit, TrEMBL_Top_BLASTP_hit, Pfam, SignalP, TmHMM, eggnog, gene_ontology_blast, gene_ontology_pfam, transcript, peptide);"
sqlite3 $db_name ".import not_viridiplantae.scratch not_viridiplantae"
sqlite3 $db_name "SELECT * from not_viridiplantae
WHERE sprot_Top_BLASTX_hit not like '.'
OR TrEMBL_Top_BLASTX_hit not like '.'
OR sprot_Top_BLASTP_hit not like '.'
OR TrEMBL_Top_BLASTP_hit not like '.';" > $acc.contaminant_genes
sqlite3 $db_name "SELECT transcript_id from not_viridiplantae
WHERE sprot_Top_BLASTX_hit not like '.'
OR TrEMBL_Top_BLASTX_hit not like '.'
OR sprot_Top_BLASTP_hit not like '.'
OR TrEMBL_Top_BLASTP_hit not like '.';" > $acc.contaminant_genes.headers
sqlite3 $db_name "SELECT * from not_viridiplantae
WHERE sprot_Top_BLASTX_hit like '.'
AND TrEMBL_Top_BLASTX_hit like '.'
AND sprot_Top_BLASTP_hit like '.'
AND TrEMBL_Top_BLASTP_hit like '.';" > $acc.no_annotation
sqlite3 $db_name "SELECT transcript_id from not_viridiplantae
WHERE sprot_Top_BLASTX_hit like '.'
AND TrEMBL_Top_BLASTX_hit like '.'
AND sprot_Top_BLASTP_hit like '.'
AND TrEMBL_Top_BLASTP_hit like '.';" > $acc.no_annotation.headers
rm scratch
rm not_viridiplantae.scratch
for i in annotated_genes contaminant_genes no_annotation; do
sed -i 's/|/\t/g' $acc.$i
sed -i 's/:/|/g' $acc.$i
done
for i in annotated_genes.headers contaminant_genes.headers no_annotation.headers; do
sed -i 's/:/|/g' $acc.$i
done