Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

awk replace cells with NaN/delete if condition

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers awk replace cells with NaN/delete if condition
# 1  
Old 06-22-2018
Question awk replace cells with NaN/delete if condition
Hello, I will like to delete/replace $3 with NaN. condition $3>-2000 (file1.dat) to produce out.dat. I want to retain the structure of the table. I use this code, this output only $3. Any idea on how to modify this code. Thank you.

Code:
awk -v OFS='' '{for(i=1; i<=NF; i++) if ($i > -2000 || $i ==" > -2000 ")$i = NaN }1' file1.xyz > out.xyz

Code:
file1.dat
2181.0640997 2898.54726885 -4682.91259766
2182.06412909 2898.54726885 -2683.671875
2183.06415848 2898.54726885 -684.24609375
2184.06418787 2898.54726885 -1684.00146484
2185.06421726 2898.54726885 -2682.8671875
2186.06424665 2898.54726885 -4080.03369141
2187.06427604 2898.54726885 -1675.8359375
2188.06430543 2898.54726885 -3671.14892578
2189.06433482 2898.54726885 -6666.21142578
2190.06436421 2898.54726885 -1661.75146484
2191.0643936 2898.54726885 -4657.61230469
2192.06442299 2898.54726885 -1653.79052734
2193.06445238 2898.54726885 -5650.45507813
2194.06448177 2898.54726885 -4647.76513672
2195.06451116 2898.54726885 -1645.11816406
2196.06454055 2898.54726885 -6642.38427734

out1.dat
2181.0640997 2898.54726885 -4682.91259766
2182.06412909 2898.54726885 -2683.671875
2183.06415848 2898.54726885 -684.24609375
2184.06418787 2898.54726885 NaN
2185.06421726 2898.54726885 -2682.8671875
2186.06424665 2898.54726885 -4080.03369141
2187.06427604 2898.54726885 NaN
2188.06430543 2898.54726885 -3671.14892578
2189.06433482 2898.54726885 -6666.21142578
2190.06436421 2898.54726885 NaN
2191.0643936 2898.54726885 -4657.61230469
2192.06442299 2898.54726885 NaN
2193.06445238 2898.54726885 -5650.45507813
2194.06448177 2898.54726885 -4647.76513672
2195.06451116 2898.54726885 NaN
2196.06454055 2898.54726885 -6642.38427734

# 2  
Old 06-22-2018
What do you mean by "I want to retain the structure of the table"?
Why do you run the for loop across all fields if you want to operate on $3 only?
What are the two ored branches in the if construct for?
Why is line 3 not NaNned?
This User Gave Thanks to RudiC For This Post:
geomarine
# 3  
Old 06-22-2018
Question
Thanks RudiC, I could just remove the fields with values above -2000, but I want to remain the structure such that I have a 4x4 matrix. then perform calculations only on the remaining values.
I made several attempts to operate only on $3, but couldn't so I decided to try another way, since $1 and $2 can't be affected by the condition (>-2000).

---------- Post updated at 03:35 PM ---------- Previous update was at 03:32 PM ----------

I also considered print $3, so I can apply the condition to it separately, but the whole fields are deleted and could not fit the 4x4 structure I intend to retain.
# 4  
Old 06-22-2018
Code:
awk '$3 >= -2000 {$3="NaN"}1' myFile

This User Gave Thanks to vgersh99 For This Post:
geomarine
# 5  
Old 06-23-2018
Quote:
Originally Posted by geomarine
Thanks RudiC, I could just remove the fields with values above -2000, but I want to remain the structure such that I have a 4x4 matrix. then perform calculations only on the remaining values.
I made several attempts to operate only on $3, but couldn't so I decided to try another way, since $1 and $2 can't be affected by the condition (>-2000).

---------- Post updated at 03:35 PM ---------- Previous update was at 03:32 PM ----------

I also considered print $3, so I can apply the condition to it separately, but the whole fields are deleted and could not fit the 4x4 structure I intend to retain.

Help me out - what 4x4- matrix are you talking of? I see a 3 fields file n lines long. If you need to convert that to another matrix, post the rules / logics to be applied and people in here might be able to help.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to replace a specific field in certain condition

Hi, I have a file like below PRUM,67016800 ,CC ,C1,67016800 , ,Y,Y,2 ,CK,BX,FOX ,00000001,EA,00000001,20141120 00:00:00, ,N,Y,Y,CK ABCDEF... (7 Replies)
Discussion started by: mady135
7 Replies

2. Shell Programming and Scripting

Delete complete row according to condition

Gents, Please can you help me. In the range 4-24 column the values are duplicate some times and I will like to delete the fist occurrence and keep the last only. The file is not sorted and I can sorted because from column 75 to the end the file is increase by time.. I have a file like this... (10 Replies)
Discussion started by: jiam912
10 Replies

3. Shell Programming and Scripting

Delete File in a Directory Using a Condition

Hello, I have a directory with many files whose creation time is distributed all over the day. I need ANY 20 files per hour. So, I need 20 files for hour 00 20 files for hour 01 ... 20 files for hour 23 What I have done so far is not great. Here is the code: # get the Month a=`echo... (8 Replies)
Discussion started by: shekhar2010us
8 Replies

4. UNIX for Dummies Questions & Answers

Replace with condition

I have file input a,b,b,b,ee,b3012dasda,53012,203,1,END,START b,c,b,b,ee,sdasdad,57483,203,,END,START a,b,b,b,ee,b3012dasda,53012,203,,END,START b,c,b,b,ee,sdasdad,57483,203,1,END,START I want every ",," in $9 replace with ,0, the output should be ... (3 Replies)
Discussion started by: radius
3 Replies

5. Shell Programming and Scripting

Delete if condition met in a column

i have a table like this: id, senderNumber, blacklist ----------------------------- 1 0835636326 Y 2 0373562343 Y 3 0273646833 Y and I want to delete automatically if a new inserted row on another table consist anything on senderNumber column above using a BASH Script I... (9 Replies)
Discussion started by: jazzyzha
9 Replies

6. Shell Programming and Scripting

Delete records within a file upon a condition

Hi Friends, I have the following file, cat input chr1 1000 2000 chr1 600 699 chr1 701 1000 chr1 600 1710 chr2 900 1800 Now, I would like to see the difference of Record1.Col2 - Record2.Col2 Record1.Col2 - Record2.Col3 Record1.Col3 - Record2.Col2 Record1.Col3 - Record2.Col3 ... (1 Reply)
Discussion started by: jacobs.smith
1 Replies

7. UNIX for Dummies Questions & Answers

[Solved] Delete files with condition

I have a directory containing thousands of items “video files”, these items were generated by an application, which generates two items with the same name but with different extensions “.avi and .mp4”. There was a tool in my application to remove one item @ a time. Later I found this tool removes... (5 Replies)
Discussion started by: awadeid
5 Replies

8. UNIX for Dummies Questions & Answers

How do you delete cells from a space delimited text file given row and column number?

How do you delete cells from a space delimited text file given row and column number? Letś say the row number is r and the column number is c. Thanks! (5 Replies)
Discussion started by: evelibertine
5 Replies

9. Shell Programming and Scripting

Delete line with sed or awk (with specified condition)

Hello. I'm trying to delete the lines of a file does not contain the letter "T " (for example) at position 26. So far, I could only print the result: awk '{if (substr ($ 1,1,26)! ~ / T /) print}' file.txt How I can do to eliminate the lines that meet this condition? Help please. ... (4 Replies)
Discussion started by: </kida>
4 Replies

10. Shell Programming and Scripting

Comparison of Cells in EXCEL using awk

Hi I have 2 csv files which looks like the following and i have to compare the 2 CSVs and create a third file such that if the value of the 1st cell in A.CSV and the value of the first cell in the B.CSV are same, it should print "SAME" in the third file or else print NOT SAME. Likewise i need... (19 Replies)
Discussion started by: meva
19 Replies
Login or Register to Ask a Question