Home > Enterprise >  Recode value in the column in unix with awk or sed
Recode value in the column in unix with awk or sed

Time:11-04

In the following file, The values of 6th column for the rows who have values other than 1 or 2 in sixth column should be replaced with -9. How can I do it?
old.fam

18_0033 26210 0 0 1 1
18_0036 24595 0 0 1 2
18_0040 25563 0 0 1 
18_0041 35990 0 0 0 -8
18_0042 39398 0 0 0 -8
18_0045 21586 0 0 1 1
18_0050 22211 0 0 1 2

new.fam should be

18_0033 26210 0 0 1 1
18_0036 24595 0 0 1 2
18_0040 25563 0 0 1 -9
18_0041 35990 0 0 0 -9
18_0042 39398 0 0 0 -9
18_0045 21586 0 0 1 1
18_0050 22211 0 0 1 2

Edit: I used cat old.fam | awk '{ if ($6==1 || $6==2) {print $1 " " $2 " " $3 " " $4 " " $5 " " $6 ;} else {print $1 " " $2 " " $3 " " $4 " " $5 " " -9;}}'> new.fam

Now the problem is the rows with replaced 6th column value (-9), does not have space separated FS between 5th and 6th column.

18_0033 26210 0 0 1 1
18_0036 24595 0 0 1 2
18_0040 25563 0 0 1-9
18_0041 35990 0 0 0-9
18_0042 39398 0 0 0-9
18_0045 21586 0 0 1 1
18_0050 22211 0 0 1 2

CodePudding user response:

Here you have something you can start working on:

cat test.txt | awk '{if ($6==1||$6==2) {print $1 " " $6;} else {print $1 " -9";}}'

The awk script does the following:

  • check the value of the sixth column
  • between both checks, there's the awk || logical OR operator

The rest of the script is obvious.

Edit
Apparently awk can't handle spaces, followed by numbers, so you might use this awk script:

awk '{ if ( $6==1|| $6==2) {print $1 " " $2 " " $3 " " $4 " " $5 " " $6 ;} else 
                           {print $1 " " $2 " " $3 " " $4 " " $5 " -9";}}'

(Mind the $5 " -9" at the end)

  • Related