Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

replace but skip data between certain commas

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting replace but skip data between certain commas
# 1  
Old 09-07-2011
replace but skip data between certain commas
OK, I am one needy dude. 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.


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.
# 2  
Old 09-07-2011
Quote:
Originally Posted by herot
...
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.
Code:
$
$ cat qin.csv
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 != 2 && $i > bound) $i = bound
      print
  }' qin.csv
300,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
300,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
300,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
300,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
300,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
300,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
300,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
300,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
300,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
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
$
$

tyler_durden
# 3  
Old 09-07-2011
Great!
could you explain/discuss
Code:
($i > bound)

VERSUS
Code:
(i != 2 && $i > bound)

!= Not equal to?
&& Refer to the value before i

?
Last edited by herot; 09-07-2011 at 05:14 PM..
# 4  
Old 09-07-2011
Quote:
Originally Posted by herot
...
Code:
(i != 2 && $i > bound)

!= Not equal to?
&& Refer to the value before i

?
The condition is self-explanatory.
Check any one of the hundreds of awk manuals on the Internet for the meaning of those operators.

tyler_durden
# 5  
Old 09-07-2011
Thanks
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Inverted commas replace

Hi, My input file is like this chr1 + "NM_1234" chr1 - "NM_1234" If I want my third column to contain both the first and second column values, how do I do it? I know how to do it by including the column numbers, but I want the values before the inverted commas. So, my output would... (5 Replies)
Discussion started by: jacobs.smith
5 Replies

2. Shell Programming and Scripting

sed command to skip the first line during find and replace operation

Hi Gurus, I did an exhaustive search for finding the script using "sed" to exclude the first line of file during find and replace. The first line in my file is the header names. Thanks for your help.. (4 Replies)
Discussion started by: ks_reddy
4 Replies

3. Shell Programming and Scripting

HELP with AWK or SED. Need to replace the commas between double quotes in CSV file

Hello experts, I need to validate a csv file which contains data like this: Sample.csv "ABCD","I",23,0,9,,"23/12/2012","OK","Street,State, 91135",0 "ABCD","I",23,0,9,,"23/12/2012","OK","Street,State, 91135",0 I just need to check if all the records contain exactly the number of... (5 Replies)
Discussion started by: shell_boy23
5 Replies

4. Shell Programming and Scripting

Replace commas with newlines

Good afternoon, I am trying to read user input. Here is what I have so far: echo "Type the Container ID for every container that you want subnets exported" echo "for (with comma between each one, for example... 1,45,98)" echo -n "if you want every one listed, then just type ALL in caps... (2 Replies)
Discussion started by: brianjb
2 Replies

5. Shell Programming and Scripting

Replace field with commas with field without commas

Hey guys, I have the following text: 1,2,3,4,5,6,'NULL','when',NULL,1,2,0,'NULL' 1,2,3,4,5,6,'NULL','what','NULL',1,2,0,1 I need the same text with the word NULL without commas u know something like this: 1,2,3,4,5,6,NULL,'when',NULL,1,2,0,NULL 1,2,3,4,5,6,NULL,'what','NULL',1,2,0,1 ... (1 Reply)
Discussion started by: lmyk72
1 Replies

6. Shell Programming and Scripting

Replace all but skip first instance in a line

I have a record like the one given below. 010000306551~IN ~N~ |WINDWARD PK|Alpharetta| If ~ is present more than instance in a line,then I need to delete those instances. Any ideas? I am working in Solaris (7 Replies)
Discussion started by: prasperl
7 Replies

7. Web Development

Data type to use for prices with commas

Hi everybody, I`m very new with PHP and Databases and I having the follow issue with prices data.. The original information is in CSV files. The prices have formatted with commas and dots as follow: 12,300.99 -->(thousands separated by commas) 3,500.25 -->(thousands separated... (10 Replies)
Discussion started by: cgkmal
10 Replies

8. Shell Programming and Scripting

how to handle , in data where separator also commas in awk script

TEST_HEME,"SubNetwork=ONRM_RootMoR,SubNetwork=ARNC1",CELL when I split by FS="," then $0=TEST_HEME $1="SubNetwork=ONRM_RootMoR $2=SubNetwork=ARNC1" but I need this will be single value "SubNetwork=ONRM_RootMoR,SubNetwork=ARNC1" (4 Replies)
Discussion started by: Hemendra
4 Replies

9. Shell Programming and Scripting

parsing log files, removing spaces and replace with commas

Hello all i am working on a database to import log files from my systems, but i cannot seem to find the answer. I searched here for a good bit and couldnt peice together what i was looking for. I know you could do this with awk, i just dont know how. Any help would be greatly appreciated.... (6 Replies)
Discussion started by: caddyjoe77
6 Replies

10. Shell Programming and Scripting

replace one or more tabs with commas

Hi, Can any one tell me how to replace one or more tabs from start of the line and in between the words with commas in the file using unix commands? My actual data in the text file is as below with spaces.The spaces are not being shown in the post..please see them while replying to the post.... (1 Reply)
Discussion started by: tucs_123
1 Replies
Login or Register to Ask a Question