How to replace a number to another number in a specific column using awk

Question

This is probably basic but I am completely new to command-line and using awk. I have a file like this:

1 RQ22067-0 -9
2   RQ34365-4   1
3   RQ34616-4   1
4   RQ34720-1   0
5   RQ14799-8   0
6   RQ14754-1   0
7   RQ22101-7   0
8   RQ22073-1   0
9   RQ30201-1   0

I want the 0s to change to 1 in column3. And any occurence of 1 and 2 to change to 2 in column3. So essentially only changing numbers in column 3. But I am not changing the -9.

1 RQ22067-0 -9
2   RQ34365-4   2
3   RQ34616-4   2
4   RQ34720-1   1
5   RQ14799-8   1
6   RQ14754-1   1
7   RQ22101-7   1
8   RQ22073-1   1
9   RQ30201-1   1

I have tried using (see below) but it has not worked

>> awk '{gsub("0","1",$3)}1' PRS_with_minus9.pheno.txt > PRS_with_minus9_modified.pheno
>> awk '{gsub("1","2",$3)}1' PRS_with_minus9.pheno.txt > PRS_with_minus9_modified.pheno

Thank you.

Answer 1

With this code in your question:

awk '{gsub("0","1",$3)}1' PRS_with_minus9.pheno.txt > PRS_with_minus9_modified.pheno
awk '{gsub("1","2",$3)}1' PRS_with_minus9.pheno.txt > PRS_with_minus9_modified.pheno

1. you're running both commands on the same input file and writing their output to the same output file so only the output of the 2nd script will be present in the output, and

2. you're trying to change 0 to 1 first and THEN change 1 to 2 so the $3s that start out as 0 would end up as 2, you need to change the order of the operations.

This is what you should be doing, using your existing code:

awk '{gsub("1","2",$3); gsub("0","1",$3)}1' PRS_with_minus9.pheno.txt > PRS_with_minus9_modified.pheno

For example:

$ awk '{gsub("1","2",$3); gsub("0","1",$3)}1' file
1 RQ22067-0 -9
2 RQ34365-4 2
3 RQ34616-4 2
4 RQ34720-1 1
5 RQ14799-8 1
6 RQ14754-1 1
7 RQ22101-7 1
8 RQ22073-1 1
9 RQ30201-1 1

The gsub() should also just be sub()s as you only want to perform each substitution once, and you don't need to enclose the numbers in quotes so you could just do:

awk '{sub(1,2,$3); sub(0,1,$3)}1' file