Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Range Comparison Of Column Value in File1 with Other File

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Range Comparison Of Column Value in File1 with Other File
# 1  
Old 12-09-2014
Range Comparison Of Column Value in File1 with Other File
Hi,
I have a file1 whose 17th column needs to be checked if it exists in between the values of column 2 & column 3 as mentioned in another file2. Output of the matched value to be put in separate file 3 & 4.

File1:
Code:
 65535|1621|112614|20141209|9474756212|41247872144519|9474756212|32|0|0|0|0|15291|00000|1|807885C9|405891369683208|postpaid|null|null|15291|15291|OUT|11:26:46|321|321|144|null|405899153999999|405899153999999|55943562
65535|1076|112636|20141209|8647975195|25429434331441|8647975195|43|0|0|0|0|17090|00000|1|800FA10D|405891373812982|postpaid|null|null|17090|17090|OUT|11:27:19|337|337|144|null|405899153999999|405899153999999|63883612

File2:
Code:
 ROWB|1507000000|1507999999
ROWB|1360000000|1360999999
ROWB|1369670200|1369999999
ROWB|1369660200|1369670199
ROWB|1369000000|1369660199
ROWB|1961917716|1961999999
ROWB|1961000000|1961912715
ROWB|1743000000|1743999999

Output File 3
Code:
 65535|1621|112614|20141209|9474756212|41247872144519|9474756212|32|0|0|0|0|15291|00000|1|807885C9|405891369683208|postpaid|null|null|15291|15291|OUT|11:26:46|321|321|144|null|405899153999999|405899153999999|55943562

Output File 4
Code:
 65535|1076|112636|20141209|8647975195|25429434331441|8647975195|43|0|0|0|0|17090|00000|1|800FA10D|405891373812982|postpaid|null|null|17090|17090|OUT|11:27:19|337|337|144|null|405899153999999|405899153999999|63883612

Not getting any idea as to how to do it using awk. Please help
# 2  
Old 12-09-2014
None of your input lines will match any of the ranges in file2. Try nevertheless
Code:
awk     'NR==FNR        {MIN[NR]=$2
                         MAX[NR]=$3
                         next}
                        {for (i in MIN)
                                 if ($17 >= MIN[i] && $17 <= MAX[i])
                                        print >"outfile"FCNT++}
        ' FS="|" FCNT=3 file2 file1

With your sample data, no output files will be created.
# 3  
Old 12-09-2014
Hi Rudi,
I have modified the code as below, but still cannot see proper output in both files(ROWB & KOL). Can you please look into this and suggest

Code:
 awk     'NR==FNR        {MIN[NR]=$2;MAX[NR]=$3;next}
{
        for (i in MIN)
               if (substr($17,6,length($17)-5) >= MIN[i] && substr($17,6,length($17)-5)<= MAX[i])
                          print > "ROWB"
                 else
                           print >"KOL"
 }
 ' FS="|"  file2 file1

# 4  
Old 12-09-2014
What proper output don't you see? And what do you expect?
# 5  
Old 12-09-2014
In the present scenario for two records in the file1, we are getting 16 lines as total output. Which is incorrect
# 6  
Old 12-09-2014
Which is what you print. You should have one single line in ROWB, and the residual, non-matching ones in KOL. That's because only the first line in file1 fits into any of the ranges in file2.
Correct the ranges, and look into my proposal on how to print to different files per input line.
# 7  
Old 12-10-2014
Hi Rudi,
How do I put the residual non matching ones in KOL?

Since I have more than 100 files for doing this operation so please suggest if this way will be efficient or not?

---------- Post updated at 03:01 PM ---------- Previous update was at 10:37 AM ----------

Hi,
I have tried added the range for handling KOL output using it range mentioned in file3 & hence changing the code as below. Please help

File3:
Code:
 KOL|1350000000|1350851179
KOL|1350851180|1350911179
KOL|1350911180|1350999999
KOL|1365000000|1365765346
KOL|1365765347|1365765746
KOL|1365765747|1365999999
KOL|1373000000|1373999999
KOL|1491000000|1491999999
KOL|1959000000|1959918057
KOL|1959923058|1959999999

Code:
 awk 'FNR==1 {++filecounter}
 filecounter==1 {KOLMIN[NR]=$2;KOLMAX[NR]=$3;next}
filecounter==2 {ROWBMIN[NR]=$2;ROWBMAX[NR]=$3;next}
{
for (i in MIN)
{        if (substr($17,6,length($17)-5) >= KOLMIN[i] && substr($17,6,length($17)-5) <= KOLMAX[i])
              {  print $0"|"FILENAME   >>"koloutfile" }
else if (substr($17,6,length($17)-5) >= ROWBMIN[i] && substr($17,6,length($17)-5) <= ROWBMAX[i])
{print $0"|"FILENAME   >>"rowboutfile"}
 }
}
' FS="|" file3 file2 file1
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to sum value of a column by range defined in another file awk?

I have two files, file1.table is the count table, and the other is the range condition file2.range. file1.table chr start end count N1 0 48 1 N1 48 181 2 N1 181 193 0 N1 193 326 2 N1 326 457 0 N1 457 471 1 N1 471 590 2 N1 590 604 1 N1 604 752 1 N1 752 875 1 file2.range... (12 Replies)
Discussion started by: yifangt
12 Replies

2. Shell Programming and Scripting

awk to search field2 in file2 using range of fields file1 and using match to another field in file1

I am trying to use awk to find all the $2 values in file2 which is ~30MB and tab-delimited, that are between $2 and $3 in file1 which is ~2GB and tab-delimited. I have just found out that I need to use $1 and $2 and $3 from file1 and $1 and $2of file2 must match $1 of file1 and be in the range... (6 Replies)
Discussion started by: cmccabe
6 Replies

3. Shell Programming and Scripting

Replace specific column range in a non-delimited file with a string!

Hi All, I will need an help with respect to replacing a range of columns on a non-delimited file using a particular string pattern. Say file input is MYNUMBERD000000-BAN CHUE INSNTS ** N+ MYAREDSDD000000+BAN CHUE INSNTS ** N+ MYDERFFFSD00000-GIR PENT - ACH ** ... (5 Replies)
Discussion started by: navojit dutta
5 Replies

4. UNIX for Dummies Questions & Answers

Extracting rows from a text file if the value of a column falls between a certain range

Hi, I have a file that looks like the following: 10 100080417 rs7915867 ILMN_1343295 12 6243093 7747537 10 100190264 rs2296431 ILMN_1343295 12 6643093 6647537 10 100719451 SNP94374 ILMN_1343295 12 6688093 7599537 ... (1 Reply)
Discussion started by: evelibertine
1 Replies

5. UNIX for Dummies Questions & Answers

if matching strings in file1 and file2, add column from file1 to file2

I have very limited coding skills but I'm wondering if someone could help me with this. There are many threads about matching strings in two files, but I have no idea how to add a column from one file to another based on a matching string. I'm looking to match column1 in file1 to the number... (3 Replies)
Discussion started by: pathunkathunk
3 Replies

6. Shell Programming and Scripting

Match one column of file1 with that of file2

Hi, I have file1 like this aaa ggg ddd vvv eeeand file2 aaa 2 aaa 443 xxx 76 aaa 34 ggg 33 wee 99 ggg 33 ddd 1 ddd 10 ddd 98 sds 23 (4 Replies)
Discussion started by: polsum
4 Replies

7. Shell Programming and Scripting

awk column comparison big file

Hi all, I would like to compare a column in one file to a column in another file and when there is a match it prints the first column and the corresponding second column. Example File1 ABA ABC ABE ABF File 2 ABA 123 ABB 124 ABD 125 ABC 126 So what I would like printed to a... (6 Replies)
Discussion started by: pcg
6 Replies

8. Shell Programming and Scripting

column value comparison in a file

Hi, Can any one help with my below requirement. i need to compare each line by line and in each line i have to compare some columns values with previous line column values in perl script. Can any one help me........! its very urgent. Thanks (3 Replies)
Discussion started by: jam_prasanna
3 Replies

9. Shell Programming and Scripting

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2 file 1 sample SNDK 80004C101 AT XLNX 983919101 BB NETL 64118B100 BS AMD 007903107 CC KLAC 482480100 DC TER 880770102 KATS ATHR 04743P108 KATS... (7 Replies)
Discussion started by: rydz00
7 Replies

10. Shell Programming and Scripting

Looking for AWK Solution for column comparison in a single file

- I am looking for different kind of awk solution which I don't think is mentioned before in these forums. Number of rows in the file are fixed Their are two columns in file1.txt 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 10 10 I am looking for 3... (1 Reply)
Discussion started by: softwarekids23
1 Replies
Login or Register to Ask a Question