Sponsored Content
Top Forums UNIX for Beginners Questions & Answers awk for matching fields between files with repeated records Post 303041181 by jvoot on Sunday 17th of November 2019 12:48:25 AM
Old 11-17-2019
awk for matching fields between files with repeated records

Hello all, I am having trouble with what should be an easy task, but seem to be missing something fundamental. I have two files, with File 1 consisting of a single field of many thousands of records. I also have File 2 with two fields and many thousands of records.

My goal is that when $1 of File 1 matches $1 of File 2, then print $1 and $2 of File 2, or alternatively, print $1 from File 1 with $2 of File 2 when $1 and $2 match between the files. The problem is that File 1 has repeated records in it. Thus when I apply awk 'FNR==NR{a[$1]; next} $1 in a' File 1 File 2 I can get all matches where $1 in File 1 matches $1 in File 2 and then output $1 && $2 in File 2, but without the repeated records. However, I need the order of the records in File 1 to be retained as well as all of the repeated records.

File 1
Code:
ABC
DEF
XYZ
ABC
DEF
ABC
XYZ

File 2
Code:
ABC 123
DEF 345
XYZ 678

Desired Output:
Code:
ABC 123
DEF 345
XYZ 678
ABC 123
DEF 345
ABC 123
XYZ 678

NB: The records are much more varied and repeats much further spread out in the actual file than the simplified examples here.

I had a somewhat similar, albeit more involved, issue in the past that RudiC helped me with (see here), but I am having trouble applying his code to this simpler example.

I got it close with this:
Code:
awk 'NR==FNR {q=$1; $1=""; T[q "," ++C[q]] = $0; next} {q=$1; X=q "," ++D[q]; printf "%s\t",  $0; if(X in T); print T[X]}' File 2 File 1

While this attempt printed all of the repeated records of File 1, it only supplied $2 from File 2 along with $1 of File 1 on the first time it appears, but not every time, such as the following:

Code:
ABC 123
DEF 345
XYZ 678
ABC
DEF
ABC
XYZ

Thanks so much in advance.

Thanks so much.

Last edited by vbe; 11-17-2019 at 10:46 AM..
 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

AWK ??-print for fields within records in a file

Hello all, Would appreciate if someone can help me out on the following requirement. INPUT FILE: -------------------------- TPS REPORT abc def ghi jkl mon pqr stu vrs lll END OF TPS REPORT TPS REPORT field1 field2 field3 field4 field5 field6 (8 Replies)
Discussion started by: hyennah
8 Replies

2. Shell Programming and Scripting

AWK Matching Fields and Combining Files

Hello! I am writing a program to run through two large lists of data (~300,000 rows), find where rows in one file match another, and combine them based on matching fields. Due to the large file sizes, I'm guessing AWK will be the most efficient way to do this. Overall, the input and output I'm... (5 Replies)
Discussion started by: Michelangelo
5 Replies

3. Shell Programming and Scripting

Averaging all fields while counting repeated records

Hi every one; I have a 31500-line text file upon which two following tasks are to be performed: 1: Rearranging the file 2: Taking the average of each column (considering number of zeros) and output the result into a new file This is the code I've come up with: awk '(NR%3150<3150)... (0 Replies)
Discussion started by: nxp
0 Replies

4. Shell Programming and Scripting

awk pattern matching name in records

Hi, I'm very new to these forums. I was wondering if someone could help an AWK beginner with a pattern matching an actor to his appearance in movies, which would be stored as records. Let's say we have a database of 4 movies (each movie a record with name, studio + year, and actor fields with... (2 Replies)
Discussion started by: Jill Ceke
2 Replies

5. UNIX for Dummies Questions & Answers

Make all records with the same number of fields (awk)

Hi, input: AA|BB|CC DD|EE FF what I am trying to get: AA|BB|CC DD|EE| FF|| I tried to create first an UDF for printing repeats, but I think I have an issue with my END section or my array: function repeat(str, n, rep, i) { for(i=1 ;i<n;i++) rep=rep str return rep } ... (6 Replies)
Discussion started by: beca123456
6 Replies

6. UNIX for Beginners Questions & Answers

Awk: matching multiple fields between 2 files

Hi, I have 2 tab-delimited input files as follows. file1.tab: green A apple red B apple file2.tab: apple - A;Z Objective: Return $1 of file1 if, . $1 of file2 matches $3 of file1 and, . any single element (separated by ";") in $3 of file2 is present in $2 of file1 In order to... (3 Replies)
Discussion started by: beca123456
3 Replies

7. Shell Programming and Scripting

awk to print fields that match using conditions and a default value for non-matching in two files

Trying to use awk to match the contents of each line in file1 with $5 in file2. Both files are tab-delimited and there may be a space or special character in the name being matched in file2, for example in file1 the name is BRCA1 but in file2 the name is BRCA 1 or in file1 name is BCR but in file2... (6 Replies)
Discussion started by: cmccabe
6 Replies

8. UNIX for Beginners Questions & Answers

Continued trouble matching fields in different files and selective field printing ([g]awk)

I apologize in advance, but I continue to have trouble searching for matches between two files and then printing portions of each to output in awk and would very much appreciate some help. I have data as follows: File1 PS012,002 PRQ 0 1 1 17 1 0 -1 3 2 1 2 -1 ... (7 Replies)
Discussion started by: jvoot
7 Replies

9. UNIX for Beginners Questions & Answers

Matching fields between two files, repeated records

In two previous posts (here) and (here), I received help from forum members comparing multiple fields across two files and selectively printing portions of each as output based upon would-be matches using awk. I had been fairly comfortable populating awk arrays with fields and using awk's special... (3 Replies)
Discussion started by: jvoot
3 Replies

10. UNIX for Beginners Questions & Answers

Awk: group multiple fields from different records

Hi, My input looks like that: A|123|qwer A|456|tyui A|456|wsxe B|789|dfgh Using awk, I am trying to get: A|123;456|qwer;tyui;wsxe B|789|dfgh For records with same $1, group all the $2 in a field (without replicates), and all the $3 in a field (without replicates). What I have tried:... (6 Replies)
Discussion started by: beca123456
6 Replies
Path::Class::File::Stat(3pm)				User Contributed Perl Documentation			      Path::Class::File::Stat(3pm)

NAME
Path::Class::File::Stat - cache and compare stat() calls on a Path::Class::File object SYNOPSIS
use Path::Class::File::Stat; my $file = Path::Class::File::Stat->new('path','to','file'); # $file has all the magic of Path::Class::File # sometime later if ($file->changed) { # do something provocative } DESCRIPTION
Path::Class::File::Stat is a simple extension of Path::Class::File. Path::Class::File::Stat is useful in long-running programs (as under mod_perl) where you might have a file handle opened and want to check if the underlying file has changed. METHODS
Path::Class::File::Stat extends Path::Class::File objects in the following ways. use_md5 Calling this method will attempt to load Digest::MD5 and use that instead of stat() for creating file signatures. This is similar to how File::Modified works. changed Returns the previously cached File::stat object if the file's device number and inode number have changed, or if the modification time or size has changed. Returns 0 (false) otherwise. While File::Modified uses a MD5 signature of the stat() of a file to determine if the file has changed, changed() uses a simpler (and probably more naive) algorithm. If you need a more sophisticated way of determining if a file has changed, use the restat() method and compare the cached File::stat object it returns with the current File::stat object. Example of your own changed() logic: my $oldstat = $file->restat; my $newstat = $file->stat; # compare $oldstat and $newstat any way you like Or just use File::Modified instead. restat Re-cache the File::stat object in the Path::Class::File::Stat object. Returns the previously cached File::stat object. The changed() method calls this method internally if changed() is going to return true. SEE ALSO
Path::Class, Path::Class::File, File::Signature, File::Modified AUTHOR
Peter Karman, <karman@cpan.org> COPYRIGHT AND LICENSE
Copyright (C) 2006 by Peter Karman This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself. perl v5.14.2 2012-01-28 Path::Class::File::Stat(3pm)
All times are GMT -4. The time now is 07:17 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy