Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

comparing the values of repeated keys in multiple columns

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting comparing the values of repeated keys in multiple columns
# 1  
Old 08-25-2009
comparing the values of repeated keys in multiple columns
Hi Guyz
The 1st column of the input file has repeated keys like x,y and z.
The ist task is if the 1st column has unique key (say x) and then need to consider 4th column, if it is + symbol then subtract 2nd column value with 3rd column value (we will get 2(10-8)) or if it is - symbol subtract 3rd column value with 2nd(we ll get 4 (14-10)) after that output the line with low value(2) i.e. x 10 8 + and the same follows to y and z.....etc.

Thnx
input
Code:
x   10   8   +      
x   10   14  -     
y   10   8   +     
y   10   11  -   
z   10   8   +

output
Code:
x   10   8   +  
y   10   11  - 
z   10   8   +

# 2  
Old 08-25-2009
This shell script does what you want, but there is probably a better way to solve your problem.
Code:
#!/bin/sh

while read line; do
	c1=$(echo "$line" | awk '{print $1}')
	c2=$(echo "$line" | awk '{print $2}')
	c3=$(echo "$line" | awk '{print $3}')
	c4=$(echo "$line" | awk '{print $4}')
	if [ "$c4" = '+' ]; then j=$(($c2-$c3)); else j=$(($c3-$c2)); fi
	[ ! "$p" ] && p="$c1"
	if [ "$p" != "$c1" ]; then echo "$this"; i=""; p=""; fi
	[ -z "$i" ] && i="$j"
	if [ "$j" -le "$i" ]; then i="$j"; this="$line"; fi
done < file
echo "$this"

exit 0

The datafile is called file in this script
From c1... to c4..., we register each field from the datafile file in a variable.
We then check if c4 is a + or - and act accordingly.
p is a variable to remember the last c1, on the first pass, it does not exist, so p = c1
if p != c1 (if we change from x to y or z), we display the line this that we set further in the script and we reset i (used later) and p.
if i does not exist (which is the case on the first pass), we set it to j, calculated above
if j is less than or equal to i, then we set j to i, and we keep the corresponding line from the datafile in the variable this
We go back to the top and check for the next line in the file.
Last edited by tukuyomi; 08-25-2009 at 02:34 PM..
# 3  
Old 08-25-2009
Code:
nawk '
  {diff=($NF=="+")?$2-$3:$3-$2; a[$1]=($1 in a)?((diff<=a[$1])?diff:a[$1]):diff;b[$1]=(diff==a[$1])?$0:b[$1]}
  END {
    for( i in b)
      print b[i]
}' myFile

# 4  
Old 08-25-2009
Thanx alot for broad explanation @tukuyomi . Really appreciate your effort.
But Vgersh script is quiet fast though.Thank you both guys
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Do replace operation and awk to sum multiple columns if another column has duplicate values

Hi Experts, Please bear with me, i need help I am learning AWk and stuck up in one issue. First point : I want to sum up column value for column 7, 9, 11,13 and column15 if rows in column 5 are duplicates.No action to be taken for rows where value in column 5 is unique. Second point : For... (12 Replies)
Discussion started by: as7951
12 Replies

2. Shell Programming and Scripting

Adding the values of repeated ids

File1 consist two columns, first some weired ids and second the numbers t|v203.1@t|k88711.1 0.1 t|v190.1@t|k90369.1 0.01 t|v203.1@t|k88711.1 0.5 t|v322.1@t|k88711.1 0.2 t|v207.1@t|k90369.1 0.11 t|v326.1@t|k85939.1 0.5 t|v207.1@t|k90369.1 0.7 t|v207.1@t|k90369.1 0.3 t|v326.1@t|k89421.1 0.33... (3 Replies)
Discussion started by: ashmit99
3 Replies

3. Shell Programming and Scripting

Help - manipulate data by columns and repeated

Hello good afternoon to everyone. I'm new to the forum and would like to request your help in handling data. I hope my English is clear. I have a file (Dato01.txt) to contine the following structure. # Col1 - Col2 - Col3 - Col4 Patricia started Jun 22 05:22:58 Carolina started Jun... (5 Replies)
Discussion started by: kelevra
5 Replies

4. Shell Programming and Scripting

Comparing multiple columns using awk

Hello All; I have two files with below conditions: 1. Entries in file A is missing in file B (primary is field 1) 2. Entries in file B is missing in file A (primary is field 1) 3. Field 1 is present in both files but Field 2 is different. Example Content: File A ... (4 Replies)
Discussion started by: mystition
4 Replies

5. Shell Programming and Scripting

Multiple columns replace with null values.

I am trying to replace the partcular columns(Col3,col5,col20,col44,col55,co56,col59,col60,col61,col62,col74,col75,col88,col90,col91,col93,col94,col95) with empty Input file Col1,col2,col3,col4,col5------,col100 1,2,3,4,5,---------,100 3,4,5,6,7,---------,300 Output : ... (3 Replies)
Discussion started by: onesuri
3 Replies

6. UNIX for Advanced & Expert Users

Need help in comparing multiple columns from two files.

Hi all, I have two files as below. I need to compare field 2 of file 1 against field 1 of file 2 and field 5 of file 1 against filed 2 of file 2. If both matches , then create a result file 1 with first file data and if not matches , then create file with first fie data. Please help me in... (12 Replies)
Discussion started by: sivarajb
12 Replies

7. Shell Programming and Scripting

Reading multiple values from multiple lines and columns and setting them to unique variables.

Hello, I would like to ask for help with csh script. An example of an input in .txt file is below, the number of lines varies from file to file and I have 2 or 3 columns with values. I would like to read all the values (probably one by one) and set them to independent unique variables that... (7 Replies)
Discussion started by: FMMOLA
7 Replies

8. Shell Programming and Scripting

awk arrays comparing multiple columns across two files.

Hi, I'm trying to use awk arrays to compare values across two files based on multiple columns. I've attempted to load file 2 into an array and compare with values in file 1, but success has been absent. If anyone has any suggestions (and I'm not even sure if my script so far is on the right lines)... (4 Replies)
Discussion started by: hubleo
4 Replies

9. Shell Programming and Scripting

Put repeated values into a table

Hi all, I have blocks of records like the following, each block ends in = in a new line, I want tabularize the entire output. The pattern is the same in every block although not all types are there in every block. For example gine3 is absent in the second block but present first and third. ... (7 Replies)
Discussion started by: ritakadm
7 Replies

10. Shell Programming and Scripting

Transposing Repeated Rows to Columns.

I have 1000s of these rows that I would like to transpose to columns. However I would like the transpose every 3 consecutive rows to columns like below, sorted by column 3 and provide a total for each occurrences. Finally I would like a grand total of column 3. 21|FE|41|0B 50\65\78 15... (2 Replies)
Discussion started by: ravzter
2 Replies
Login or Register to Ask a Question