How to add new column based on values of existing column in .txt file unix

Question

I have a .txt file like this containing six columns. I want to add a seventh column V8 into the file based on the values of column V7_Phenoin a way that Yes should be coded as 2, No as 1 and missing values as -9

V1       V2     V3      V4      V6     V7_Pheno
2253792 20482   NA      DNA     1       Yes
2253802 20491   NA      DNA     4       Yes
2253816 20503   NA      DNA     0       No
2253820 20508   NA      DNA     4       Yes
2253821 20509   NA      DNA     0       No
2253824 20511   NA      DNA     0       No
2253826 20513   NA      DNA     3       Yes
2253829 20516   NA      DNA     6       Yes

The outcome .txt file i am looking for should be like this

V1       V2     V3      V4      V6     V7_Pheno   V8
    2253792 20482   NA      DNA     1       Yes    2
    2253802 20491   NA      DNA     4       Yes    2
    2253816 20503   NA      DNA     0       No     1
    2253820 20508   NA      DNA     4       Yes    2
    2253821 20509   NA      DNA     0       No     1
    2253824 20511   NA      DNA     0       No     1
    2253826 20513   NA      DNA     3       Yes    2
    2253829 20516   NA      DNA     6       Yes    2

Sounds like a job for "awk". Take a look at this tutorial (or another - you'll find many!), and see if you agree: grymoire.com/Unix/Awk.html — paulsm4
– paulsm4, Commented Jun 15, 2021 at 0:48

ashish_k · Accepted Answer · 2021-06-15 05:24:00Z

3

Using awk:

awk 'NR==1{$7="V8";print;next}\
 $6 == "Yes" {$7="2"};\
 $6 == "No" {$7="1"}1' a.txt |column -t

The first line print the V8 in the header
If the value of 6th col: V7_Pheno is Yes, then $7 = "2"
If the value of 6th col: V7_Pheno is No, then $7 = "1"
column -t : for better formatting

edited Jun 15, 2021 at 5:24

answered Jun 15, 2021 at 5:18

ashish_k

1,5912 gold badges14 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aditya · Accepted Answer · 2021-06-16 23:14:28Z

1

With Perl:

perl -lane 'print join "\t", @F, $.==1 ? "V8" : $F[5]=~/No/ ? 1 : $F[5]=~/Yes/ ? 2 : -9' file.txt | column -t

print join "\t", @F print the fields of every line, using tab as the field separator.

$.==1 ? "V8" if this is the 1st line, print an additional field V8.

$F[5]=~/No/ ? 1 else, if the 6th field is No, print an additional field 1.

$F[5]=~/Yes/ ? 2 else, if the 6th field is Yes, print an additional field 2.

-9 else, print -9.

edited Jun 16, 2021 at 23:14

answered Jun 16, 2021 at 22:14

Aditya

3742 gold badges6 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to add new column based on values of existing column in .txt file unix

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related