Replacing values in a column if they equal a certain value | Unix Linux Forums | UNIX for Dummies Questions & Answers

  Go Back    


UNIX for Dummies Questions & Answers If you're not sure where to post a UNIX or Linux question, post it here. All UNIX and Linux newbies welcome !!

Replacing values in a column if they equal a certain value

UNIX for Dummies Questions & Answers


Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 07-18-2013
evelibertine evelibertine is offline
Registered User
 
Join Date: May 2011
Last Activity: 27 December 2013, 6:16 PM EST
Posts: 193
Thanks: 94
Thanked 0 Times in 0 Posts
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!
Sponsored Links
    #2  
Old 07-18-2013
Scrutinizer's Avatar
Scrutinizer Scrutinizer is online now Forum Staff  
Moderator
 
Join Date: Nov 2008
Last Activity: 22 July 2014, 11:27 AM EDT
Location: Amsterdam
Posts: 9,278
Thanks: 260
Thanked 2,302 Times in 2,065 Posts
What have you tried so far?
Sponsored Links
    #3  
Old 07-18-2013
evelibertine evelibertine is offline
Registered User
 
Join Date: May 2011
Last Activity: 27 December 2013, 6:16 PM EST
Posts: 193
Thanks: 94
Thanked 0 Times in 0 Posts
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 04:32 PM.. Reason: formatting code
    #4  
Old 07-18-2013
rdrtx1 rdrtx1 is offline
Registered User
 
Join Date: Sep 2012
Last Activity: 15 July 2014, 11:14 AM EDT
Location: Houston, Texas, USA
Posts: 675
Thanks: 0
Thanked 203 Times in 195 Posts
try:

Code:
awk '$2 == "NA" {$2=$1":"$4} 1' OFS="\t" infile

The Following User Says Thank You to rdrtx1 For This Useful Post:
evelibertine (07-19-2013)
Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Replacing column values technoman Shell Programming and Scripting 5 07-12-2012 09:01 PM
replacing negative values in a column with zero ida1215 Shell Programming and Scripting 4 07-09-2012 02:06 AM
Replacing column 1 in one file with values in other file digipak Shell Programming and Scripting 4 06-12-2011 01:14 AM
compare columns for equal values and output a summary reno Shell Programming and Scripting 4 02-20-2011 07:22 PM
My Values are Equal but They are Not scottwmackey Shell Programming and Scripting 7 03-02-2010 11:37 PM



All times are GMT -4. The time now is 11:28 AM.