How can I replace strings in a given column of a table using awk and regex?

Question

I am studying bioinformatics, but I don't have long experience with awk and now I'm stuck.

I have a table with 13 columns.

In column 9 I have a lot of variations of strings like ELL1-XXXXXXXXX (e.g. ELL1-II_EC_cell1) or CDK8-XXXXXX (e.g. CDK8-213_mCdk8_ChIPseq_Tnaive_stim_CDK8-214_mCdk8_ChIPseq_Tnaive_stim_AS).

I have >200 variations of the ELL1-XXXXX string, which I would like to change to ELL1 or CDK8, and I would like to change the other strings to simpler ones, as well.

I tried

awk -F '\t' '{gsub("CDK8-213_mCdk8_ChIPseq_Tnaive_stim_CDK8-214_mCdk8_ChIPseq_Tnaive_stim_AS","CDK8",$9); print}' input.lst > output.lst && mv output.lst input.lst

but this way I have to search for the strings to be replaced one by one. I've read numerous forum threads, but couldn't find the command that I could use for my file.

Here are 4 sample rows as input:

DRX154054   ILLUMINA    SINGLE  ChIP-seq    mm_embryonicstemcell_embryonicstemcell  Mus_musculus    None    No  ELL1-II_EC_cell121  NA  NA  NA  ftp://ftp-trace.ncbi.nlm.nih.gov/sra/sra-instant/reads/ByExp/sra/DRX/DRX154/DRX154054/  
DRX154053   ILLUMINA    SINGLE  ChIP-seq    mm_embryonicstemcell_embryonicstemcell  Mus_musculus    None    No  ELL2-II_EC_cell210  NA  NA  NA  ftp://ftp-trace.ncbi.nlm.nih.gov/sra/sra-instant/reads/ByExp/sra/DRX/DRX154/DRX154053/  
ERX3608304  ILLUMINA    SINGLE  ChIP-Seq    mm_Unknown_Unknown  Mus_musculus    None    No  EP1-BCL6-Fast-C57-Rep1-ChIP-seq NA  NA  NA  ftp://ftp-trace.ncbi.nlm.nih.gov/sra/sra-instant/reads/ByExp/sra/ERX/ERX360/ERX3608304/ 
DRX154052   ILLUMINA    SINGLE  ChIP-seq    mm_embryonicstemcell_embryonicstemcell  Mus_musculus    None    No  DNMT3A-Dnmt3a1_BioChIPSeq_r1    NA  NA  NA  ftp://ftp-trace.ncbi.nlm.nih.gov/sra/sra-instant/reads/ByExp/sra/DRX/DRX154/DRX154052/

The expected output:

DRX154054   ILLUMINA    SINGLE  ChIP-seq    mm_embryonicstemcell_embryonicstemcell  Mus_musculus    None    No  ELL1    NA  NA  NA  ftp://ftp-trace.ncbi.nlm.nih.gov/sra/sra-instant/reads/ByExp/sra/DRX/DRX154/DRX154054/  
DRX154053   ILLUMINA    SINGLE  ChIP-seq    mm_embryonicstemcell_embryonicstemcell  Mus_musculus    None    No  ELL2    NA  NA  NA  ftp://ftp-trace.ncbi.nlm.nih.gov/sra/sra-instant/reads/ByExp/sra/DRX/DRX154/DRX154053/
ERX3608304  ILLUMINA    SINGLE  ChIP-Seq    mm_Unknown_Unknown  Mus_musculus    None    No  EP1 NA  NA  NA  ftp://ftp-trace.ncbi.nlm.nih.gov/sra/sra-instant/reads/ByExp/sra/ERX/ERX360/ERX3608304/ 
DRX154052   ILLUMINA    SINGLE  ChIP-seq    mm_embryonicstemcell_embryonicstemcell  Mus_musculus    None    No  DNMT3A  NA  NA  NA  ftp://ftp-trace.ncbi.nlm.nih.gov/sra/sra-instant/reads/ByExp/sra/DRX/DRX154/DRX154052/

As you can see, the following strings were replaced:

ELL1-II_EC_cell121 -> ELL1

ELL2-II_EC_cell210 -> ELL2

EP1-BCL6-Fast-C57-Rep1-ChIP-seq -> EP1

DNMT3A-Dnmt3a1_BioChIPSeq_r1 -> DNMT3A

Perhaps a simple substring replacement (like $9 = substr($9,1,4)) would be more appropriate than a regular expression replacement — steeldriver
– steeldriver, Commented Dec 14, 2019 at 14:14
... or, if you cant rely on a fixed prefix length, just replace everything from the first dash onward sub(/-.*/,"",$9) — steeldriver
– steeldriver, Commented Dec 14, 2019 at 14:46
Danger (1). You need to set up BEGIN { OFS = "\t" } too, or all your TAB separators will become single spaces. DANGER (2) You MUST not write back over your original files until you have fully tested all this. Send outputs to a subdirectory and diff them before taking the risk. Or, back them all up in tar so you can restore them for each test. — Paul_Pedant
– Paul_Pedant, Commented Dec 15, 2019 at 0:17
edit your question to contain concise, testable sample input and expected output so we can try to help you. — Ed Morton
– Ed Morton, Commented Dec 15, 2019 at 7:44
I have added 4 sample rows, the expected output and the changes to be made. — Orsi
– Orsi, Commented Dec 17, 2019 at 18:27

Kusalananda · Accepted Answer · 2019-12-26 14:59:03Z

Assuming tab-delimited data:

$ awk -F '\t' -v OFS='\t' '{ sub("-.*", "", $9); print }' file
DRX154054       ILLUMINA        SINGLE  ChIP-seq        mm_embryonicstemcell_embryonicstemcell  Mus_musculusNone     No      ELL1    NA      NA      NA      ftp://ftp-trace.ncbi.nlm.nih.gov/sra/sra-instant/reads/ByExp/sra/DRX/DRX154/DRX154054/
DRX154053       ILLUMINA        SINGLE  ChIP-seq        mm_embryonicstemcell_embryonicstemcell  Mus_musculusNone     No      ELL2    NA      NA      NA      ftp://ftp-trace.ncbi.nlm.nih.gov/sra/sra-instant/reads/ByExp/sra/DRX/DRX154/DRX154053/
ERX3608304      ILLUMINA        SINGLE  ChIP-Seq        mm_Unknown_Unknown      Mus_musculus    None    No  EP1      NA      NA      NA      ftp://ftp-trace.ncbi.nlm.nih.gov/sra/sra-instant/reads/ByExp/sra/ERX/ERX360/ERX3608304/
DRX154052       ILLUMINA        SINGLE  ChIP-seq        mm_embryonicstemcell_embryonicstemcell  Mus_musculusNone     No      DNMT3A  NA      NA      NA      ftp://ftp-trace.ncbi.nlm.nih.gov/sra/sra-instant/reads/ByExp/sra/DRX/DRX154/DRX154052/

This simply applies a substitution to the 9th field that removes everything from the first dash character onwards in that field. The modified data is then printed.

The regular expression -.* will match from the first - onwards (literally "match a - followed by zero or more of any character"), and using this with sub() and an empty replacement string on the 9th field removes all text that matches from that field. Note that we don't need to use gsub() here as we will only ever have to do the replacement once per input line.

Since we modify one of the fields, we also have to make sure that the output field separator (OFS) is set to a tab character, otherwise we'll get space-separated fields in the output. You could obviously have done that with a BEGIN block as well:

awk 'BEGIN { OFS=FS="\t" } { sub("-.*", "", $9); print }' file

This version of command worked, as well, thank you for your effort! — Orsi
– Orsi, Commented Dec 31, 2019 at 15:15

aborruso · Accepted Answer · 2019-12-26 09:06:55Z

Using Miller (https://github.com/johnkerl/miller) and running

mlr --tsv --implicit-csv-header --headerless-csv-output put '$9=gsub($9,"-.+$","")' input >output

you will have

+------------+----------+--------+----------+----------------------------------------+--------------+------+----+--------+----+----+----+-----------------------------------------------------------------------------------------+
| 1          | 2        | 3      | 4        | 5                                      | 6            | 7    | 8  | 9      | 10 | 11 | 12 | 13                                                                                      |
+------------+----------+--------+----------+----------------------------------------+--------------+------+----+--------+----+----+----+-----------------------------------------------------------------------------------------+
| DRX154054  | ILLUMINA | SINGLE | ChIP-seq | mm_embryonicstemcell_embryonicstemcell | Mus_musculus | None | No | ELL1   | NA | NA | NA | ftp://ftp-trace.ncbi.nlm.nih.gov/sra/sra-instant/reads/ByExp/sra/DRX/DRX154/DRX154054/  |
| DRX154053  | ILLUMINA | SINGLE | ChIP-seq | mm_embryonicstemcell_embryonicstemcell | Mus_musculus | None | No | ELL2   | NA | NA | NA | ftp://ftp-trace.ncbi.nlm.nih.gov/sra/sra-instant/reads/ByExp/sra/DRX/DRX154/DRX154053/  |
| ERX3608304 | ILLUMINA | SINGLE | ChIP-Seq | mm_Unknown_Unknown                     | Mus_musculus | None | No | EP1    | NA | NA | NA | ftp://ftp-trace.ncbi.nlm.nih.gov/sra/sra-instant/reads/ByExp/sra/ERX/ERX360/ERX3608304/ |
| DRX154052  | ILLUMINA | SINGLE | ChIP-seq | mm_embryonicstemcell_embryonicstemcell | Mus_musculus | None | No | DNMT3A | NA | NA | NA | ftp://ftp-trace.ncbi.nlm.nih.gov/sra/sra-instant/reads/ByExp/sra/DRX/DRX154/DRX154052/  |
+------------+----------+--------+----------+----------------------------------------+--------------+------+----+--------+----+----+----+-----------------------------------------------------------------------------------------+

For some reason, the above Miller command didn't work for me and I don't know what was wrong, I couldn't figure it out. Nevertheless, thank you for the suggestion to get familiar with Miller, it may help a lot in the future! — Orsi
– Orsi, Commented Dec 25, 2019 at 22:18
@Orsi there was an error of mine. Your input file is a TSV: I have edited my reply, please try again using the new syntax — aborruso
– aborruso, Commented Dec 26, 2019 at 9:08

jesse_b · Accepted Answer · 2019-12-26 14:27:47Z

1

Using substr and index

awk -F '\t' '{$9=substr($9, 0, index($9, "-")-1); print}' input.lst > output.lst && mv output.lst input.lst

edited Dec 26, 2019 at 14:27

jesse_b

41.6k14 gold badges108 silver badges162 bronze badges

answered Dec 26, 2019 at 14:09

Maxime Levesque

4114 silver badges5 bronze badges

Add a comment |

Stack Exchange Network

How can I replace strings in a given column of a table using awk and regex?

3 Answers 3

You must log in to answer this question.

Hot Network Questions

How can I replace strings in a given column of a table using awk and regex?

3 Answers 3

You must log in to answer this question.

Related

Hot Network Questions