Replacing values in a column if they equal a certain value


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Replacing values in a column if they equal a certain value
# 1  
Old 07-18-2013
Replacing values in a column if they equal a certain value

Hi,

I have a tab delimited text file where some lines have the string "NA" in the second column. For these lines, I want to replace NA with the value in the first column, the symbol underscore followed by the value in the fourth column. How do I go about doing that? Thanks!

Input:

Code:
1       rs1933024       0       244859  0       2
1       NA      0       608390  0       2
1       rs12565286      0       761153  0       2
1       NA      0       763682  0       2


Output
Code:
1       rs1933024       0       244859  0       2
1       1:608930      0       608390  0       2
1       rs12565286      0       761153  0       2
1       1:763682      0       763682  0       2

Thanks!
# 2  
Old 07-18-2013
What have you tried so far?
# 3  
Old 07-18-2013
I tried grepping the lines containing NAs and replacing them with the new values using:

Code:
grep "NA" input |  awk '{print $1,$1,$4,$3,$4,$5,$6;} | sed 's/ /:/2' > output

Then I would grep the lines not containing NAs and use cat the concatenate the two.

However the problem with this approach is that the line order changes and I would like to keep that intact if possible.

Last edited by evelibertine; 07-18-2013 at 05:32 PM.. Reason: formatting code
# 4  
Old 07-18-2013
try:
Code:
awk '$2 == "NA" {$2=$1":"$4} 1' OFS="\t" infile

This User Gave Thanks to rdrtx1 For This Post:
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Delete row if both percentage values are equal to zero

Hello, I have compiled a script but I have stucked at one point. Each line contains two pcs of % value and what I want to do is to delete any line if both % values are zero. data: expected output: ow3 should be deleted as both percentage value in related line are equal to zero. ... (2 Replies)
Discussion started by: baris35
2 Replies

2. UNIX for Dummies Questions & Answers

Help in replacing column values

Hello All, I am having the file as below .I need to replace column 9-12 with some other values. In the below file I need to replace 1509 to 1508 and 1508 to 1507 .Can you please help me in how to do that Thanks, Arun ... (10 Replies)
Discussion started by: arunkumar_mca
10 Replies

3. Shell Programming and Scripting

Deleting consecutive equal values in a file

Hello everyone, I have a requirement as shown below. I need to delete consecutive same values from the source file and give it as output file. Source: a,b,c,d,e,e,f,g Target: a,b,c,d,f,g The repeating value "e" should be deleted from the file completely. How can I achieve this... (14 Replies)
Discussion started by: vamsikrishna928
14 Replies

4. Shell Programming and Scripting

Replacing values into a single column. sed/PERL

Hello everyone, i need to replace in the second column of my csv file, points by nothing and dash by comma like this: Input: 1 2 1;12.111.312-2;1.2;2;1-3 2 1 1;11.212.331-1;3.3;1;2-2 Output: 1 2 1;12111312;2;1.2;2;1-3 2 1 1;11212331;1;3.3;1;2-2 SED or PERL commands preferably. ... (7 Replies)
Discussion started by: satir
7 Replies

5. Shell Programming and Scripting

awk file to read values from Db2 table replacing hard coded values

Hi, I want to replace a chain of if-else statement in an old AWK file with values from Db2 table or CSV file. The part of code is below... if (start_new_rec=="true"){ exclude_user="false"; user=toupper($6); match(user, "XXXXX."); if (RSTART ==2 ) { ... (9 Replies)
Discussion started by: asandy1234
9 Replies

6. Shell Programming and Scripting

Replacing column values

hi all , ( perl) i am trying to replace jst one column in a file for eg month dayofweek dealar car-name jan thurs mercedes c300 feb wed lexus is300 all this data is in a master file and i want to replace jan with 1 feb... (5 Replies)
Discussion started by: technoman
5 Replies

7. Shell Programming and Scripting

replacing negative values in a column with zero

Hi, i need help on replacing negative values in a column with 0. any quick fix on this? thanks much. for instance, input: 1 2.3 -0.4 -25 12 13 45 -12 desired output 1 2.3 0 0 12 13 45 (4 Replies)
Discussion started by: ida1215
4 Replies

8. Shell Programming and Scripting

Replacing column 1 in one file with values in other file

Please help me with an shell / awk script to achieve following; File-1: ABCDW01 12322 23322 BDADW01 22232 24453 EDFAW00 32232 23422 and so on, notice that the first coloumn is a code and the another file contains the real value of each entry in the first colum above but not in a... (4 Replies)
Discussion started by: digipak
4 Replies

9. Shell Programming and Scripting

compare columns for equal values and output a summary

Hi all I am trying to scan a file that has 3 columns: red blue 123351 red blue 848655 red blue 126354 red blue 023158 black white 654896 red blue 650884 I want an output that sums the rows that have matching columns 1 and 2 :wall: red blue has 5 entries black white has 1 entry ... (4 Replies)
Discussion started by: reno
4 Replies

10. Shell Programming and Scripting

My Values are Equal but They are Not

Does anybody understand why this is not being interpreted as true. Script: #!/bin/bash errored=`grep "errored" new_update_scripts.txt` echo $errored = "errored" if ; then echo true else echo false fi Output: $ UpdateScripts errored = errored false (7 Replies)
Discussion started by: scottwmackey
7 Replies
Login or Register to Ask a Question