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

  Unix/Linux 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 Linux or Unix Question    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 07-18-2013
evelibertine evelibertine is offline
Registered User
 
Join Date: May 2011
Last Activity: 22 August 2014, 5:12 AM EDT
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 Unix and Linux 07-18-2013
Scrutinizer's Unix or Linux Image
Scrutinizer Scrutinizer is offline Forum Staff  
Moderator
 
Join Date: Nov 2008
Last Activity: 29 March 2015, 2:35 PM EDT
Location: Amsterdam
Posts: 10,064
Thanks: 324
Thanked 2,631 Times in 2,352 Posts
What have you tried so far?
Sponsored Links
    #3  
Old Unix and Linux 07-18-2013
evelibertine evelibertine is offline
Registered User
 
Join Date: May 2011
Last Activity: 22 August 2014, 5:12 AM EDT
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 Unix and Linux 07-18-2013
rdrtx1 rdrtx1 is offline
Registered User
 
Join Date: Sep 2012
Last Activity: 13 January 2015, 10:34 AM EST
Location: Houston, Texas, USA
Posts: 681
Thanks: 0
Thanked 206 Times in 198 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 Linux or Unix Question

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Unix or Linux Image 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 12:26 AM.