Keeping record of file 2 based on a reference file 1 in awk


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Keeping record of file 2 based on a reference file 1 in awk
# 1  
Old 12-17-2015
Keeping record of file 2 based on a reference file 1 in awk

I have 2 input files (tab separated):
file1:
Code:
make_A   1990   foo   bar
make_B   2010   this   that
make_C   2004   these   those

file2:
Code:
make_X   1970   1995   ref_1:43   ref_2:65
make_A   1970   1995   ref_1:4   ref_2:21   ref_3:18
make_A   1980   2002   ref_1:7   ref_2:7   ref_3:0   ref_4:9
make_B   2007   2009   ref_1:98
make_C   2000   2004   ref_1:34   ref_2:4   ref_3:0

I am trying to append records of file 2 to file 1 if:
1) $1 of file 1 and $1 of file 2 are the same
AND
2) $2 of file 2 ≤ $2 of file 1 ≤ $3 of file 2
AND
3) file 2 contains the value '0' for ref_3 (i.e. 'ref_3:0')

then to count the number of records in file 2 that matched these criteria.

in order to get:
Code:
make_A   1990   foo   bar   make_A   1980   2002   ref_1:7   ref_2:7   ref_3:0   ref_4:9
make_C   2004   these   those   make_C   2000   2004   ref_1:34   ref_2:4   ref_3:0

Count
make_X   0
make_A   1
make_B   0
make_C   1

I tried the following, but it returns a blank output and I don't really understand why:
Code:
gawk '
BEGIN{FS=OFS="\t"}
NR==FNR{
    brand[$1]=$2;
    line[$1]=$0;
    next
    }
    {
    match($0, /ref_3\:[0-9]+/)
    ref_n=split((substr($0,RSTART,RLENGTH)),b,":")
    if($1 in brand){
        if($2<=brand[$1] && brand[$1]<=$3 && b[ref_n]==0){
            ref++
            print line[$1] FS $0
            }
        }
    }
END{print "\nCount"; for(i in ref){print ref[i]}}' file1.txt file2.txt

# 2  
Old 12-17-2015
You aren't far off. Using your indentation style and making a few minor changes:
Code:
gawk '
BEGIN{FS=OFS="   "}
NR==FNR{
    brand[$1]=$2;
    line[$1]=$0;
    next
    }
    {
    match($0, /ref_3\:[0-9]+/)
    ref_n=split((substr($0,RSTART,RLENGTH)),b,":")
    ref[$1]
    if($1 in brand){
        if($2<=brand[$1] && brand[$1]<=$3 && b[ref_n]==0){
            ref[$1]++
            print line[$1] FS $0
            }
        }
    }
END{print "\nCount"; for(i in ref){print i,ref[i]+0}}' file1.txt file2.txt

seems to do what you want. The problems in your code were:
  1. The biggest problem is that (even though you said your input files had tab separated fields), there are no tabs in either of your input files. The fields in your input files and in the output you said you wanted are separated by three space characters,
  2. brands that did not have any matched lines were not added to the ref[] array,
  3. the reference counts array (ref[]) was treated as a scalar when you incremented its value, and
  4. when you printed the counts, you only printed the count, not the array index and the count.
Changes to fix those minor issues are marked in red in the code above.

Note that your specification wasn't clear as to whether there should only be one output line for each brand if there are multiple input lines meeting your constraints or one output line for each input line meeting your constraints. The code above produces one output line for each input line in file2.txt that meets the constraints.

Note also that the order of the counts at the end of the output is in random order. Additional changes would be required if you need to have the output order of those line match the order in which each brand was first found in file2.txt (as it was in your sample output specification).

You might also want to compare the above with the following:
Code:
gawk '
BEGIN {	FS = OFS = "   "
}
NR == FNR {
	brand[$1] = $2
	line[$1] = $0
	next
}
{	ref[$1]
	if($1 in brand && $2 <= brand[$1] && brand[$1] <= $3 &&
	    $0 ~ / ref_3:0( |$)/) {
		ref[$1]++
		print line[$1] FS $0
	}
}
END {	print "\nCount"
	for(i in ref)
		print i, ref[i]+0
}' file1.txt file2.txt

which produces the same output using a single if statement instead of a call to match(), a call to substr(), a call to split() and two if statements.
This User Gave Thanks to Don Cragun For This Post:
# 3  
Old 12-18-2015
Thanks for your clear explanation Don Cragun ! I understand now.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

awk to replace values in one file using a second reference file

Hi, I'd be grateful for your help with the following: I have a file with a single column (file1). Let's say the values are: a b c 5 d I have a second, reference file (ref_file), which is colon-delimited, and is effectively a key. Let's say the values in it are: a:1 b:2 c:3 d:4... (4 Replies)
Discussion started by: aberg
4 Replies

2. Shell Programming and Scripting

EBCDIC File Split Based On Record Key

I was wondering if anyone could explain to me how to split a variable length EBCDIC file into seperate files based on the record key. I have the COBOL layout, and so I need to split the file into 13 different EBCDIC files so that I can run each one through a C++ converter I have, and get the... (11 Replies)
Discussion started by: hanshot1stx
11 Replies

3. Shell Programming and Scripting

Replace from reference file awk

Basically want to replace any field in input file from the refernce file ... for example. clar_2400:3113 in input file will be replaced by clar_2400:3113 Input file field seperator is "," Field which is not found in reference will stay as it is ... Input File ... (3 Replies)
Discussion started by: greycells
3 Replies

4. Shell Programming and Scripting

Extract record from file based on section.

input file output file (1 Reply)
Discussion started by: lathigara
1 Replies

5. UNIX for Dummies Questions & Answers

keeping last record among group of records with common fields (awk)

input: ref.1;rack.1;1 #group1 ref.1;rack.1;2 #group1 ref.1;rack.2;1 #group2 ref.2;rack.3;1 #group3 ref.2;rack.3;2 #group3 ref.2;rack.3;3 #group3 Among records from same group (i.e. with same 1st and 2nd field - separated by ";"), I would need to keep the last record... (5 Replies)
Discussion started by: beca123456
5 Replies

6. Shell Programming and Scripting

Help with replace column one content based on reference file

Input file 1 testing 10 20 1 A testing 20 40 1 3 testing 23 232 2 1 testing 10 243 2 . . Reference file 1 final 3 used . . Output file (1 Reply)
Discussion started by: perl_beginner
1 Replies

7. Shell Programming and Scripting

Help with replace column one content based on reference file

Input file 1 testing 10 20 1 A testing 20 40 1 3 testing 23 232 2 1 testing 10 243 2 . . Reference file 1 final 3 used . . Output file (2 Replies)
Discussion started by: perl_beginner
2 Replies

8. Shell Programming and Scripting

Help with rename header content based on reference file problem

I got long list of reference file >data_tmp_number_22 >data_tmp_number_12 >data_tmp_number_20 . . Input file: >sample_data_1 Math, 5, USA, tmp SDFEWRWERWERWRWER FSFDSFSDFSDGSDGSD >sample_data_2 Math, 15, UK, tmp FDSFSDFF >sample_data_3 Math, 50, USA, tmp ARQERREQR . . Desired... (7 Replies)
Discussion started by: perl_beginner
7 Replies

9. Shell Programming and Scripting

Replace character based on reference file problem asking

I got two files right now, input file (target file), reference file 1 (query file) reference file 1 (long list of data) KOLOPWMOPOPO ADASDASD ADSASDASDAD . . target file (one long liner content) ADASDASDTYUKOKOLOPWMOPOPOOPLUAADSASDASDADPOPOUYADADASDASD desired output file content ... (1 Reply)
Discussion started by: patrick87
1 Replies

10. UNIX for Dummies Questions & Answers

Splitting a file based on record sin another file

All, We receive a file with a large no of records (records can vary) and we have to split it into two files based on another file. e.g. File1: UHDR 2008112 "25187","00000022","00",21-APR-1991,"" ,"D",-000000519,+0000000000,"C", ,+000000000,+000000000,000000000,"2","" ,21-APR-1991... (7 Replies)
Discussion started by: er_ashu
7 Replies
Login or Register to Ask a Question