Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

find all numbers > x and replace with y within a file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting find all numbers > x and replace with y within a file
# 8  
Old 09-07-2011
Honestly, I can not explain it better than many existing awk tutorials. See awk.info Learn
===
You have a different FS (field separator), so just change it:
Code:
awk -F', ' -v bound=300 'BEGIN { OFS = "," } 
{
  for(i=1; i<=NF; i++)
    if ($i > bound) $i = bound
  print
}' INPUTFILE

# 9  
Old 09-07-2011
*UPDATE*
OK a change to what i need to alter:

Code:
4728.983333,AT1G54215,45.76,0,0,11.83,0,0,45.76,0,30.21,0,0,0,11.83,0,0,45.76,0,30.21,0,0,0
4289.164948,AT1G55670,66.73,0,24.76,0,0,0,66.73,0,11.83,0,8.75,24.76,0,0,66.73,0,11.83,0,8.75,0,0
4259.141414,AT2G32690,19.93,0,0,0,0,0,19.93,0,0,0,0,0,0,0,0,19.93,0,0,0,0,0
4199.354839,AT1G75550,54.09,54.09,0,11.83,0,0,31.62,0,0,0,0,0,11.83,0,0,31.62,0,0,0,0,0
4066.010753,AT2G34430,10.77,0,10.77,0,0,0,3.13,0,0,0,0,0,0,0,3.13,0,0,0,0,0,0

need to be converted to:

Code:
300,AT1G54215,45.76,0,0,11.83,0,0,45.76,0,30.21,0,0,0,11.83,0,0,45.76,0,30.21,0,0,0
300,AT1G55670,66.73,0,24.76,0,0,0,66.73,0,11.83,0,8.75,24.76,0,0,66.73,0,11.83,0,8.75,0,0
300,AT2G32690,19.93,0,0,0,0,0,19.93,0,0,0,0,0,0,0,0,19.93,0,0,0,0,0
300,AT1G75550,54.09,54.09,0,11.83,0,0,31.62,0,0,0,0,0,11.83,0,0,31.62,0,0,0,0,0
300,AT2G34430,10.77,0,10.77,0,0,0,3.13,0,0,0,0,0,0,0,3.13,0,0,0,0,0,0

so basically each value before ",AT" needs to be checked for > 300 and if so, then change to 300


Moderator's Comments:
Mod Comment Please use code tags!
Last edited by radoulov; 09-07-2011 at 12:58 PM..
# 10  
Old 09-07-2011
awk (assuming constant input file format: AT ... is always at the same position $2):

Code:
awk -F, '$1 = $1 > l ? l : $1' OFS=, l=300 infile

The following won't work if:

- the limit is 0 (l=0)
- variable FS is used

The first problem could be worked around with:

Code:
awk -F, '{
  $1 > l && $1 = l
  } 1' OFS=, l=300 infile

or:

Code:
awk -F, '($1 > l && $1 = l) || 1' OFS=, l=300 infile

The second requires a different approach.
Last edited by radoulov; 09-07-2011 at 03:17 PM..
# 11  
Old 09-07-2011
Quote:
Originally Posted by herot
*UPDATE*
OK a change to what i need to alter:

Code:
4728.983333,AT1G54215,45.76,0,0,11.83,0,0,45.76,0,30.21,0,0,0,11.83,0,0,45.76,0,30.21,0,0,0
4289.164948,AT1G55670,66.73,0,24.76,0,0,0,66.73,0,11.83,0,8.75,24.76,0,0,66.73,0,11.83,0,8.75,0,0
4259.141414,AT2G32690,19.93,0,0,0,0,0,19.93,0,0,0,0,0,0,0,0,19.93,0,0,0,0,0
4199.354839,AT1G75550,54.09,54.09,0,11.83,0,0,31.62,0,0,0,0,0,11.83,0,0,31.62,0,0,0,0,0
4066.010753,AT2G34430,10.77,0,10.77,0,0,0,3.13,0,0,0,0,0,0,0,3.13,0,0,0,0,0,0

need to be converted to:

Code:
300,AT1G54215,45.76,0,0,11.83,0,0,45.76,0,30.21,0,0,0,11.83,0,0,45.76,0,30.21,0,0,0
300,AT1G55670,66.73,0,24.76,0,0,0,66.73,0,11.83,0,8.75,24.76,0,0,66.73,0,11.83,0,8.75,0,0
300,AT2G32690,19.93,0,0,0,0,0,19.93,0,0,0,0,0,0,0,0,19.93,0,0,0,0,0
300,AT1G75550,54.09,54.09,0,11.83,0,0,31.62,0,0,0,0,0,11.83,0,0,31.62,0,0,0,0,0
300,AT2G34430,10.77,0,10.77,0,0,0,3.13,0,0,0,0,0,0,0,3.13,0,0,0,0,0,0

so basically each value before ",AT" needs to be checked for > 300 and if so, then change to 300


Moderator's Comments:
Mod Comment Please use code tags!
I changed your sample data file a bit so that the test isn't skewed:

Code:
$
$ cat f17
4728.983333,AT1G54215,45.76,0,0,11.83,0,0,45.76,0,30.21,0,0,0,11.83,0,0,45.76,0,30.21,0,0,0
4289.164948,AT1G55670,66.73,0,24.76,0,0,0,66.73,0,11.83,0,8.75,24.76,0,0,66.73,0,11.83,0,8.75,0,0
259.141414,AT2G32690,19.93,0,0,0,0,0,19.93,0,0,0,0,0,0,0,0,19.93,0,0,0,0,0
4199.354839,AT1G75550,54.09,54.09,0,11.83,0,0,31.62,0,0,0,0,0,11.83,0,0,31.62,0,0,0,0,0
66.010753,AT2G34430,10.77,0,10.77,0,0,0,3.13,0,0,0,0,0,0,0,3.13,0,0,0,0,0,0
$
$
$ perl -lne '@x = split /,/;
             for ($i=0; $i<$#x; $i++) {$x[$i-1]=300 if $x[$i] =~ /^AT/ and $x[$i-1] > 300};
             print join ",",@x
            ' f17
300,AT1G54215,45.76,0,0,11.83,0,0,45.76,0,30.21,0,0,0,11.83,0,0,45.76,0,30.21,0,0,0
300,AT1G55670,66.73,0,24.76,0,0,0,66.73,0,11.83,0,8.75,24.76,0,0,66.73,0,11.83,0,8.75,0,0
259.141414,AT2G32690,19.93,0,0,0,0,0,19.93,0,0,0,0,0,0,0,0,19.93,0,0,0,0,0
300,AT1G75550,54.09,54.09,0,11.83,0,0,31.62,0,0,0,0,0,11.83,0,0,31.62,0,0,0,0,0
66.010753,AT2G34430,10.77,0,10.77,0,0,0,3.13,0,0,0,0,0,0,0,3.13,0,0,0,0,0,0
$
$

Quote:
Originally Posted by herot
How would I do this? How could i use <> symbols for numbers in the find/replace code below?


Code:
perl -pi -e 's/test/tst/'
OR is there a better way?

100 5000 2 432 4 2 33 4 5 6 65 300 301

needs to be:

100 300 2 300 4 2 33 4 5 6 65 300 300

also it might not always need spaces... i need multiple types of delimiters please.
Code:
$
$
$ echo "100 5000 2 432 4 2 33 4 5 6 65 300 301" | perl -ne 'while (/(\d+)/g) {print $1 > 300 ? 300 : $1," "} END{print "\n"}'
100 300 2 300 4 2 33 4 5 6 65 300 300
$
$

tyler_durden
# 12  
Old 09-07-2011
Quote:
Originally Posted by durden_tyler
I changed your sample data file a bit so that the test isn't skewed:

Code:
$
$ cat f17
4728.983333,AT1G54215,45.76,0,0,11.83,0,0,45.76,0,30.21,0,0,0,11.83,0,0,45.76,0,30.21,0,0,0
4289.164948,AT1G55670,66.73,0,24.76,0,0,0,66.73,0,11.83,0,8.75,24.76,0,0,66.73,0,11.83,0,8.75,0,0
259.141414,AT2G32690,19.93,0,0,0,0,0,19.93,0,0,0,0,0,0,0,0,19.93,0,0,0,0,0
4199.354839,AT1G75550,54.09,54.09,0,11.83,0,0,31.62,0,0,0,0,0,11.83,0,0,31.62,0,0,0,0,0
66.010753,AT2G34430,10.77,0,10.77,0,0,0,3.13,0,0,0,0,0,0,0,3.13,0,0,0,0,0,0
$
$
$ perl -lne '@x = split /,/;
             for ($i=0; $i<$#x; $i++) {$x[$i-1]=300 if $x[$i] =~ /^AT/ and $x[$i-1] > 300};
             print join ",",@x
            ' f17
300,AT1G54215,45.76,0,0,11.83,0,0,45.76,0,30.21,0,0,0,11.83,0,0,45.76,0,30.21,0,0,0
300,AT1G55670,66.73,0,24.76,0,0,0,66.73,0,11.83,0,8.75,24.76,0,0,66.73,0,11.83,0,8.75,0,0
259.141414,AT2G32690,19.93,0,0,0,0,0,19.93,0,0,0,0,0,0,0,0,19.93,0,0,0,0,0
300,AT1G75550,54.09,54.09,0,11.83,0,0,31.62,0,0,0,0,0,11.83,0,0,31.62,0,0,0,0,0
66.010753,AT2G34430,10.77,0,10.77,0,0,0,3.13,0,0,0,0,0,0,0,3.13,0,0,0,0,0,0
$
$



Code:
$
$
$ echo "100 5000 2 432 4 2 33 4 5 6 65 300 301" | perl -ne 'while (/(\d+)/g) {print $1 > 300 ? 300 : $1," "} END{print "\n"}'
100 300 2 300 4 2 33 4 5 6 65 300 300
$
$

tyler_durden
OK, I am one needy bastard. However, how can I make the program NOT change any of the values BETWEEN the first and second "," ?

I dont want any of the numbers changed that are preceded by "AT".
I want ALL other numeric values > 300 changed to 300. Not just in front of the 1rst column anymore.

Code:
cat qin.csv |head
17497.5,AT2G07765,20.31,17.27,5.46,10.02,5.7,19.33,11.18,0,0,3.13,0,4.09,5.46,10.02,5.7,19.33,11.18,0,0,3.13,0
9095.008264,AT1G29910,82.14,38.18,0,0,0,0,40.4,0,0,40.4,15.49,0,0,0,40.4,0,0,40.4,15.49,21.25,0
8093.269841,AT1G67090,82.14,0,11.83,0,0,0,26.35,51.84,0,0,0,0,0,26.35,0,51.84,0,0,0,0,0
7464.533333,AT1G29930,82.14,38.18,0,0,0,0,82.14,0,11.83,0,0,0,0,0,82.14,0,11.83,0,0,0,30.21
5402.863636,AT4G31280,67.65,0,0,0,0,9.66,0,0,9.66,0,0,0,0,0,0,9.66,0,9.66,9.66,0,0
5225.831933,AT1G29920,82.14,82.14,0,0,0,0,38.18,0,0,0,0,0,0,0,38.18,0,0,0,0,40.4,0
5110.942857,AT2G37830,16.66,0,0,0,0,0,3.13,0,0,7.11,0,0,0,0,0,3.13,0,0,7.11,0,0
5077.444444,AT4G22020,48.55,0,0,0,0,0,0,0,9.08,0,0,0,0,0,0,9.08,0,0,0,0,31.23
5070.434343,AT1G31580,81.62,44.88,0,4.2,0,0,0,0,0,0,0,4.2,0,0,0,0,0,0,0,0,0
4728.983333,AT1G54215,45.76,0,0,11.83,0,0,45.76,0,30.21,0,0,0,11.83,0,0,45.76,0,30.21,0,0,0


awk -F',' -v bound=300 'BEGIN { OFS = "," } 
{
  for(i=1; i<=NF; i++)
    if ($i > bound) $i = bound
  print
}' qin.csv |head
300,300,20.31,17.27,5.46,10.02,5.7,19.33,11.18,0,0,3.13,0,4.09,5.46,10.02,5.7,19.33,11.18,0,0,3.13,0
300,300,82.14,38.18,0,0,0,0,40.4,0,0,40.4,15.49,0,0,0,40.4,0,0,40.4,15.49,21.25,0
300,300,82.14,0,11.83,0,0,0,26.35,51.84,0,0,0,0,0,26.35,0,51.84,0,0,0,0,0
300,300,82.14,38.18,0,0,0,0,82.14,0,11.83,0,0,0,0,0,82.14,0,11.83,0,0,0,30.21
300,300,67.65,0,0,0,0,9.66,0,0,9.66,0,0,0,0,0,0,9.66,0,9.66,9.66,0,0
300,300,82.14,82.14,0,0,0,0,38.18,0,0,0,0,0,0,0,38.18,0,0,0,0,40.4,0
300,300,16.66,0,0,0,0,0,3.13,0,0,7.11,0,0,0,0,0,3.13,0,0,7.11,0,0
300,300,48.55,0,0,0,0,0,0,0,9.08,0,0,0,0,0,0,9.08,0,0,0,0,31.23
300,300,81.62,44.88,0,4.2,0,0,0,0,0,0,0,4.2,0,0,0,0,0,0,0,0,0
300,300,45.76,0,0,11.83,0,0,45.76,0,30.21,0,0,0,11.83,0,0,45.76,0,30.21,0,0,0

The above code turns all my "AT" column values into 300. I need to prevent this. It does everything else right though.

Thanks very much for everyone's help.
Last edited by herot; 09-07-2011 at 04:25 PM..
# 13  
Old 09-07-2011
This seems like trolling! I'm closing the thread.
The OP should open a new one once he/she decides/realizes
what he/she really needs.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Find duplicates in file with line numbers

Hello All, This is a noob question. I tried searching for the answer but the answer found did not help me . I have a file that can have duplicates. 100 200 300 400 100 150 the number 100 is duplicated twice. I want to find the duplicate along with the line number. expected... (4 Replies)
Discussion started by: vatigers
4 Replies

2. Shell Programming and Scripting

Use awk to replace numbers in a file with a column from another file

Hello, I am trying to make a awk code that will take 2 files, a txt file like this : 1 1 88 c(1:38, 42, 102) 2 2 128 c(39:41, 43:101, 103:105, 153, 155:189, 292, 344:369) 3 3 84 c(190:249, 603, 606:607, 609:629) 4 4 12 ... (8 Replies)
Discussion started by: nastaziales
8 Replies

3. UNIX for Dummies Questions & Answers

Sed/awk to find negative numbers and replace with 1?

Greetings. I have a three column file, and there are some numbers in the second column that are <1. However I need all numbers to be positive, thus need to replace all those numbers with just one. I feel like there must be a simple way to use awk to find these numbers and sed to replace but can't... (5 Replies)
Discussion started by: Twinklefingers
5 Replies

4. Shell Programming and Scripting

How to find numbers in text file?

Hi I have a text file with rows like this: 7 Herman ASI-40 Jungle (L) Blueprint (L) Weapon Herman ASI-40 Jungle (L) 215.00 57 65.21 114.41 and 9 Herman CAP-505 (L) Blueprint (L) Weapon Herman CAP-505 (L) 220.00 46.84 49.1 104.82 and 2 ClericDagger 1C blueprint Melee - Shortblade... (2 Replies)
Discussion started by: pesa
2 Replies

5. Shell Programming and Scripting

using sed to find and replace multiple numbers

I have looked around and there are several examples of how to use sed, but I don't think any of them help me very much with what I am trying to do. I have a text file like this.... 1! SRCNAM = 00001 ! 1! X = 50.0000, 0.0000,... (10 Replies)
Discussion started by: mercury.int
10 Replies

6. Shell Programming and Scripting

Replace 2nd column of CSV file with numbers on line

I have a csv file with occasional multiple entries in the second column. 111111,104,07-24-2011,3.15,N, 222222,020 140,07-24-2011,10.00,N,I want the result 111111,104,07-24-2011,3.15,N, 222222,020,07-24-2011,10.00,N, 222222,140,07-24-2011,10.00,N, I know I can get the output of the second... (5 Replies)
Discussion started by: ffdstanley
5 Replies

7. Shell Programming and Scripting

Replace several numbers with respective tag and make a single file

Dear All, I have a final output files as 736645|0| 13879|1| 495563|10| 127933|14| 4975|16| 49038|6| 53560|7| 135115|8| 178857|9| Now I want to replace second column with respective tag as per the value (4 Replies)
Discussion started by: jojo123
4 Replies

8. Shell Programming and Scripting

count numbers of matching rows and replace its value in another file

Hello all, can you help me in this problem, assume We have two txt file (file_1 and file_3) one is file_1 contains the data: a 0 b 1 c 3 a 7 b 4 c 5 b 8 d 6 . . . . and I need to count the lines with the matching data (a,b,..) and print in new file called file_2 such as the... (4 Replies)
Discussion started by: GoldenFalcon10
4 Replies

9. AIX

How to replace many numbers with one number in a file

How to replace many numbers with one number in a file. Many numbers like 444565,454678,443298,etc. i want to replace these with one number (300).Please halp me out. (2 Replies)
Discussion started by: vpandey
2 Replies

10. Shell Programming and Scripting

to replace one character by numbers in a file

suppose u have a file aas P-H 123 gdg O-U 223 hdy I-Y 12 fgd K-O 333 ssa L-P 32 output shud be like that aas P123H gdg O223U hdy I12Y fgd K333O ssa L32P thanks (7 Replies)
Discussion started by: cdfd123
7 Replies
Login or Register to Ask a Question