Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk to search for specific line and replace nth column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk to search for specific line and replace nth column
# 1  
Old 12-06-2013
awk to search for specific line and replace nth column
I need to be able to search for a string in the first column and if that string exists than replace the nth column with "-9.99".

Code:
AW12000012012   2.38   1.51   3.01   1.66   0.90   0.91   1.22   0.82   0.57   1.67   2.31   3.63   0.00
AW12000012013   1.52   0.90   1.20   1.34   1.21   0.67   1.18   1.22   2.75   0.84   1.52   0.27  -9.99
Q120100010001   6.35   1.70   2.59   1.88   3.08   0.70   0.71   0.31   2.27   0.19   2.42   6.05   0.00

In the case above I would need to search for "2013" and when it is present, replace the 13th column in that line only with "-9.99".

Thanks!
# 2  
Old 12-06-2013
Try
Code:
awk '$1 ~ /2013$/ {$13 = -9.99}1' file

I guess you want to keep the format? Try
Code:
 awk '$1 ~ /2013$/ {$13 = -9.99; for (i=2; i<=NF; i++) $i=$i+0}1' CONVFMT="%6.2f" file
AW12000012012   2.38   1.51   3.01   1.66   0.90   0.91   1.22   0.82   0.57   1.67   2.31   3.63   0.00
AW12000012013   1.52   0.90   1.20   1.34   1.21   0.67   1.18   1.22   2.75   0.84   1.52  -9.99  -9.99
Q120100010001   6.35   1.70   2.59   1.88   3.08   0.70   0.71   0.31   2.27   0.19   2.42   6.05   0.00

# 3  
Old 12-06-2013
Thanks RudiC

"2013" may exist in various places in column 1. So I need the script to only look for it in the last 4 fields. I recently got a tip on searching for that string -

Code:
awk 'substr($1,length($1)-3) <= 2013'

Would something like that work in this case?

---------- Post updated at 09:02 AM ---------- Previous update was at 08:43 AM ----------

I was able to get this command to work for the most part

Code:
awk 'substr($1,length($1)-3) == 2013 {$13 = -9.99; for (i=2; i<=NF; i++) $i=$i+0}1' CONVFMT="%6.2f" file

But some of the formatting is not correct.

For instance -

Code:
AA02700012013   1.98   3.09   2.29   2.44   5.48   7.04   5.90   3.34   5.07   1.55   3.64  -9.99  41.87
BB02800012013 3   2.89   2.89   2.60   3.82   9.49   5.27   4.66   2.13   1.99   2.79  -9.99  41.53
CC02900012013   0.50   0.39   0.21   0.18   0.22   0.65   3.50   2.16   4.16   0.43   0.88  -9.99  13.28
DD03000012013   2.12   2.38   1.80   3.07   4.31   7.45   4.62   3.84   3.46   3.18   3.78  -9.99  40.03
EE03100012013   4.69   4.09   2.67   4.68   3.96 9   8.41   4.77   2.80   2.20   3.46  -9.99  50.74

How do I correct so that the whole digits have 2 decimal precision?
# 4  
Old 12-06-2013
/2013$/ will match 2013 at field end only.
Looks like integers are not formatted with CONVFMT. Try
Code:
awk '$1 ~ /2013$/ {$13 = -9.99; for (i=2; i<=NF; i++) $i=sprintf("%6.2f", $i)}1' file

This User Gave Thanks to RudiC For This Post:
ncwxpanther
# 5  
Old 12-06-2013
Looks like duplicate post

https://www.unix.com/shell-programming-and-scripting/241714-awk-search-pattern-remove-line.html
# 6  
Old 12-09-2013
Quote:
Originally Posted by RudiC
/2013$/ will match 2013 at field end only.
Looks like integers are not formatted with CONVFMT. Try
Code:
awk '$1 ~ /2013$/ {$13 = -9.99; for (i=2; i<=NF; i++) $i=sprintf("%6.2f", $i)}1' file

To be more efficient what is the best way to also remove lines that contain integers greater than 2013.

I was using this command separately, but would rather combine it with the above code.

Code:
awk 'substr($1,length($1)-3) <= 2013 file

# 7  
Old 12-09-2013
combine like this

Code:
$ awk 'substr($1,length($1)-3) == 2013{$13 = -9.99; for (i=2; i<=NF; i++) $i=sprintf("%6.2f", $i)}1'  file
AW12000012012   2.38   1.51   3.01   1.66   0.90   0.91   1.22   0.82   0.57   1.67   2.31   3.63   0.00
AW12000012013   1.52   0.90   1.20   1.34   1.21   0.67   1.18   1.22   2.75   0.84   1.52  -9.99  -9.99
Q120100010001   6.35   1.70   2.59   1.88   3.08   0.70   0.71   0.31   2.27   0.19   2.42   6.05   0.00

if you don't care about spacing then this also will work
Code:
$ awk 'substr($1,length($1)-3) == 2013{$13 = -9.99}{$1=$1}1'  file
AW12000012012 2.38 1.51 3.01 1.66 0.90 0.91 1.22 0.82 0.57 1.67 2.31 3.63 0.00
AW12000012013 1.52 0.90 1.20 1.34 1.21 0.67 1.18 1.22 2.75 0.84 1.52 -9.99 -9.99
Q120100010001 6.35 1.70 2.59 1.88 3.08 0.70 0.71 0.31 2.27 0.19 2.42 6.05 0.00
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Replace Value of nth Column of Each Line Using Array

Hello All, I am writing a shell script with following requirement: 1. I have one input file as below CHE01,A,MSC,INO CHE02,B,NST,INC CHE03,C,STM,INP 2. In shell script I have predefined array as below: Array1={A, B, C} Array2= {U09, C04, A054} (6 Replies)
Discussion started by: angshuman
6 Replies

2. Shell Programming and Scripting

Search term in nth field and replace kth column

Hi, I have a text file which looks like this a.txt A,12,Apple,Red B,33,Banana,Yellow C,66,Sky,Blue I need to search for a particular field(s) in particular column(s) and for that matching line need to replace the nth column. Sample scenario 1: Search for 66 in second field and Sky in... (5 Replies)
Discussion started by: wahi80
5 Replies

3. Shell Programming and Scripting

awk search and replace nth column by using a variable.

I am passing a variable and replace nth value with the variable. I tried using many options in awk command but unable to ignore the special characters in the output and also unable to pass the actual value. Input : "1","2","3" Output : "1","1000","3" TempVal=`echo 1000` Cat... (2 Replies)
Discussion started by: onesuri
2 Replies

4. Shell Programming and Scripting

How to search and replace string from nth column from a file?

I wanted to search for a string and replace it with other string from nth column of a file which is comma seperated which I am able to do with below # For Comma seperated file without quotes awk 'BEGIN{OFS=FS=","}$"'"$ColumnNo"'"=="'"$PPK"'"{$"'"$ColumnNo"'"="'"$NPK"'"}{print}' ${FileName} ... (5 Replies)
Discussion started by: Amit Joshi
5 Replies

5. Shell Programming and Scripting

Search Replace Specific Column using RegEx

Have Pipe Delimited File: > BRYAN BAKER|4/4/2015|518 VIRGINIA AVE|TEST > JOE BAXTER|3/30/2015|2233 MockingBird RD|ROW2On 3rd column where the address is located, I want to add a space after every numeric value - basically doing a "s//&\ / ": > BRYAN BAKER|4/4/2015|5 1 8 VIRGINIA AVE|TEST > JOE... (5 Replies)
Discussion started by: svn
5 Replies

6. Shell Programming and Scripting

awk : search last index in specific column

I am trying to search a given text in a file and find its last occurrence index. The task is to append the searched index in the same file but in a separate column. I am able to accomplish the task partially and looking for a solution. Following is the detailed description: names_file.txt ... (17 Replies)
Discussion started by: tarun.trehan
17 Replies

7. Shell Programming and Scripting

Calculating average for every Nth line in the Nth column

Is there an awk script that can easily perform the following operation? I have a data file that is in the format of 1944-12,5.6 1945-01,9.8 1945-02,6.7 1945-03,9.3 1945-04,5.9 1945-05,0.7 1945-06,0.0 1945-07,0.0 1945-08,0.0 1945-09,0.0 1945-10,0.2 1945-11,10.5 1945-12,22.3... (3 Replies)
Discussion started by: ncwxpanther
3 Replies

8. Shell Programming and Scripting

Using AWK to find top Nth values in Nth column

I have an awk script to find the maximum value of the 2nd column of a 2 column datafile, but I need to find the top 5 maximum values of the 2nd column. Here is the script that works for the maximum value. awk 'BEGIN { subjectmax=$1 ; max=0} $2 >= max {subjectmax=$1 ; max=$2} END {print... (3 Replies)
Discussion started by: ncwxpanther
3 Replies

9. Shell Programming and Scripting

Replace specific field on specific line sed or awk

I'm trying to update a text file via sed/awk, after a lot of searching I still can't find a code snippet that I can get to work. Brief overview: I have user input a line to a variable, I then find a specific value in this line 10th field in this case. After asking for new input and doing some... (14 Replies)
Discussion started by: crownedzero
14 Replies

10. Shell Programming and Scripting

search pattern and replace x-y characters in nth line after every match

Hi, I am looking for any script which can do the following. have to read a pattern from fileA and copy it to fileB. fileA: ... ... Header ... ... ..p1 ... ... fileB: .... .... Header (3 Replies)
Discussion started by: anilvk
3 Replies
Login or Register to Ask a Question