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: 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 07-18-2013
Scrutinizer's Avatar
Scrutinizer Scrutinizer is offline Forum Staff  
Moderator
 
Join Date: Nov 2008
Last Activity: 19 December 2014, 1:14 AM EST
Location: Amsterdam
Posts: 9,690
Thanks: 300
Thanked 2,491 Times in 2,230 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: 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 05:32 PM.. Reason: formatting code
    #4  
Old 07-18-2013
rdrtx1 rdrtx1 is offline
Registered User
 
Join Date: Sep 2012
Last Activity: 16 December 2014, 11:19 AM EST
Location: Houston, Texas, USA
Posts: 676
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 10:01 PM
replacing negative values in a column with zero ida1215 Shell Programming and Scripting 4 07-09-2012 03:06 AM
Replacing column 1 in one file with values in other file digipak Shell Programming and Scripting 4 06-12-2011 02:14 AM
compare columns for equal values and output a summary reno Shell Programming and Scripting 4 02-20-2011 08:22 PM
My Values are Equal but They are Not scottwmackey Shell Programming and Scripting 7 03-03-2010 12:37 AM



All times are GMT -4. The time now is 04:58 PM.