Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Table look up using awk or any other means

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Table look up using awk or any other means
# 1  
Old 01-19-2013
Table look up using awk or any other means
Hi all,

I wanted to do a lookup table method using awk or any other commands. I would really appreciate any help on this. my sample input and desired outputs are given below. file1.txt is my lookuptable file, file2.txt, is the main file.


file1.txt
Code:
NaN   NaN
33.5    0.00000000000000
33.51   0.041351
33.52   0.082702
33.53   0.124053
33.54   0.165404
33.55   0.206755
33.56   0.248106
33.57   0.289457
33.58   0.330808
33.59   0.372159
33.6    0.41351002500000

file2.txt
Code:
33.00 
33.56
33.57
NaN
NaN
33.50
33.60
33.54
NaN
33.71
33.90

desired output
Code:
33.00  0.00000000000000 ; 33 < 33.5
33.56  0.041351
33.57  0.289457
NaN  NaN
NaN  NaN
33.50  0.00000000000000
33.60  0.41351002500000
33.54  0.165404
NaN  NaN
33.71  0.41351002500000 
33.90  0.41351002500000 ; 33.9 > 33.6

If the value in file2.txt is lower than that of the minimum in file1.txt, then it will use the corresponding value of the minimum in file1.txt and will use the maximum value in file2.txt, if it is otherwise.
Last edited by Scott; 01-19-2013 at 02:46 AM.. Reason: Fixed code tags
# 2  
Old 01-19-2013
Try:

Code:
awk '
  NR==FNR{
    A[$1]=$2
    if( $1!~/[[:alpha:]]/ ) {
      if( min=="") min=max=$1
      if( $1<min ) min=$1
      if( $1>max ) max=$1
    }
    next
  }
  {
    v=$1
    if( $1!~/[[:alpha:]]/ ) {
      if( $1<min ) v=min
      if( $1>max ) v=max
    }
    print $1, A[v]
  }
' file1 file2

This User Gave Thanks to Scrutinizer For This Post:
ida1215
# 3  
Old 01-19-2013
Hi scrutinizer,

Thank you very much for the code, it worked smoothly,Smilie

Quick question: what does this condition means?

Code:
 if( $1!~/[[:alpha:]]/ )

Thanks much again
Last edited by Scrutinizer; 01-19-2013 at 06:01 AM.. Reason: code tags
# 4  
Old 01-19-2013
Hi, it means "if the first field does not contain a letter" . [:alpha:] is a character class.
This User Gave Thanks to Scrutinizer For This Post:
ida1215
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Awk- Pivot Table Averages

Hi everyone, Has anyone figured out yet how to do pivot table averages using AWK. I didn't see anything with regards to doing averages. For example, suppose you have the following table with various individuals and their scores in round1 and round2: SAMPLE SCORE1 SCORE2 British ... (6 Replies)
Discussion started by: Geneanalyst
6 Replies

2. Shell Programming and Scripting

Transpose table with awk

I am trying to format the table below to the output input: cand week sub1 sub2 sub3 sub4 joe 1 94.19 70.99 43.93 60.14 joe 2 94.07 51.02 41.07 38.92 joe 3 26.24 30.95 44.56 67.67 joe 4 72.36 60.92 40.78 83.25 joe 5 51 70.01 44.66 82.22... (7 Replies)
Discussion started by: aydj
7 Replies

3. Shell Programming and Scripting

awk or any other means to find IP (File1 / MAC (File2)) entries and putting them on File3

Hi everyone, I would like to complete the following could you please find some time and help me to achieve below: File 1 has a list of IP address (more than 1k) File1:1.1.1.1 2.2.2.2 1.1.1.2 3.3.3.3 2.3.3.2File 2 has content like this:Internet 11.165.91.244 0 Incomplete ... (4 Replies)
Discussion started by: redred
4 Replies

4. Shell Programming and Scripting

Searching value in table through awk

I need to create one script in which I want to search in txt file , this txt file is having 10 columns , I want to check 4th column value if "BOY" & 10th column value =>500 it will print 4th column row value ,1st column row value & 10th column row value & store the same value in one file as... (2 Replies)
Discussion started by: dravi_laxmi
2 Replies

5. Shell Programming and Scripting

Create table within awk-if statement

Hi I am trying to create a table within an awk if statement awk -F, '{ if ($8 ~ /Match/) BEGIN{print "<table>"} {print "<tr>";for(i=1;i<=NF;i++)print "<td>" $i"</td>";print "</tr>"} END{print "</table>"}' SN1.csv | mailx -s "Your details" abc@123.com But this doesnt work.. Please suggest (8 Replies)
Discussion started by: sidnow
8 Replies

6. Shell Programming and Scripting

awk to convert table-by-row to matrix table

Hello, I need some help to reformat this table-by-row to matrix? infile: site1 A:o,p,q,r,s,t site1 C:y,u site1 T:v,w site1 -:x,z site2 A:p,r,t,v,w,z site2 C:u,y site2 G:q,s site2 -:o,x site3 A:o,q,s,t,u,z site3 C:y site3 T:v,w,x site3 -:p,routfile: SITE o p q r s t v u w x y... (7 Replies)
Discussion started by: yifangt
7 Replies

7. Shell Programming and Scripting

extracting table with awk

Hi, I am new to awk and want to create a script that finds a string of text then prints the following table to a seperate file. I thought of using the / / identifier, but how do I retrieve the next 15 lines? gavin (6 Replies)
Discussion started by: gav2251
6 Replies

8. Shell Programming and Scripting

How to transpose a table of data using awk

Hi. I have this data below:- v1 28 14 1.72414 1.72414 1.72414 1.72414 1.72414 v2 77 7 7.47126 6.89655 6.89655 6.89655 6.89655 v3 156 3 21.2644 21.2644 20.6897 21.2644 20.6897 v4 39 3 1.72414 1.72414 1.72414 1.72414 1.72414 v5 155 1 21.2644 23.5632 24.1379 23.5632 24.1379 v6 62 2 2.87356... (2 Replies)
Discussion started by: ahjiefreak
2 Replies
Login or Register to Ask a Question