Sponsored Content
Top Forums Shell Programming and Scripting Matching multiple fields from two files and then some? Post 302657585 by mbp on Monday 18th of June 2012 03:49:25 AM
Old 06-18-2012
Matching multiple fields from two files and then some?

Hi,
I am working with two tab-delimited files with multiple columns, formatted as follows:

File 1:

Code:
  >chrom 1       100     A          G          20       …(10 columns)
  >chrom 1       104     G          C          18       …(10 columns)
  >chrom 2       28       T          C          44       …(10 columns)
  etc.

File 2:

Code:
  >chrom 1       200     269     333     396     …(variable, odd number of columns)
  >chrom 2       15       114     207     273     400     496     …(variable, odd number of columns)
  etc.


I am trying to determine a way (in Unix/Linux) if I can do the following:

1) print all lines from file 1 where:
a) the entries in column 1 match for both file 1 and file 2 AND
b) the number in column 2 of file 1 is within 1000 of any of the numbers (i.e. column 2 onwards) in the matching line in file 2 from part ‘a’.

2) print all lines from file 1 where:
a) same as ‘a’ above AND
b) the number in column 2 of file 1 is equal to or between the numbers in columns 2 and 3, 4 and 5, 6 and 7, etc. of file 2, for as many pairs of numerical columns that there are for that particular line of file 2.

I have a feeling I might be in over my head here, but any help would certainly be appreciated. Is it possible that this can be done with awk? Thanks!


ps.
the files are both currently sorted on the first column

Last edited by Scrutinizer; 06-18-2012 at 05:04 AM.. Reason: code tags
 

10 More Discussions You Might Find Interesting

1. 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

2. Shell Programming and Scripting

comparing two files for matching fields

I am newbie to unix and would please like some help to solve the task below I have two files, file_a.text and file_b.text that I want to evaluate. file_a.text 1698.74 1711.88 6576.25 899.41 3205.63 4187.98 697.35 1551.83 ... (3 Replies)
Discussion started by: gameli
3 Replies

3. Shell Programming and Scripting

How to merge two or more fields from two different files where there is non matching column?

Hi, Please excuse for often requesting queries and making R&D, I am trying to work out a possibility where i have two files field separated by pipe and another file containing only one field where there is no matching columns, Could you please advise how to merge two files. $more... (3 Replies)
Discussion started by: karthikram
3 Replies

4. Shell Programming and Scripting

Print matching fields (if they exist) from two text files

Hi everyone, Given two files (test1 and test2) with the following contents: test1: 80263760,I71 80267369,M44 80274628,L77 80276793,I32 80277390,K05 80277391,I06 80279206,I43 80279859,K37 80279866,K35 80279867,J16 80280346,I14and test2: 80263760,PT18 80279867,PT01I need to do some... (3 Replies)
Discussion started by: gacanepa
3 Replies

5. 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

6. 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

7. 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

8. Shell Programming and Scripting

Matching two fields in two csv files, create new file and append match

I am trying to parse two csv files and make a match in one column then print the entire file to a new file and append an additional column that gives description from the match to the new file. If a match is not made, I would like to add "NA" to the end of the file Command that Ive been using... (6 Replies)
Discussion started by: dis0wned
6 Replies

9. Shell Programming and Scripting

Comparing two files by two matching fields

Long time listener first time poster. Hope someone can advise. I have two files, 1000+ lines in each, two fields in each file. After performing a sort, what is the best way to find exact matches where field $1 and $2 in file1 are also present in file2 on the same line, then output only those... (6 Replies)
Discussion started by: bstaff
6 Replies

10. UNIX for Beginners Questions & Answers

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... (2 Replies)
Discussion started by: jvoot
2 Replies
fspec(4)							   File Formats 							  fspec(4)

NAME
fspec - format specification in text files DESCRIPTION
It is sometimes convenient to maintain text files on the system with non-standard tabs, (tabs that are not set at every eighth column). Such files must generally be converted to a standard format, frequently by replacing all tabs with the appropriate number of spaces, before they can be processed by system commands. A format specification occurring in the first line of a text file specifies how tabs are to be expanded in the remainder of the file. A format specification consists of a sequence of parameters separated by blanks and surrounded by the brackets <: and :>. Each parameter consists of a keyletter, possibly followed immediately by a value. The following parameters are recognized: ttabs The t parameter specifies the tab settings for the file. The value of tabs must be one of the following: o A list of column numbers separated by commas, indicating tabs set at the specified columns. o A '-' followed immediately by an integer n, indicating tabs at intervals of n columns. o A '-' followed by the name of a ``canned'' tab specification. Standard tabs are specified by t-8, or equivalently, t1,9,17,25, etc. The canned tabs that are recognized are defined by the tabs(1) command. ssize The s parameter specifies a maximum line size. The value of size must be an integer. Size checking is performed after tabs have been expanded, but before the margin is prepended. mmargin The m parameter specifies a number of spaces to be prepended to each line. The value of margin must be an integer. d The d parameter takes no value. Its presence indicates that the line containing the format specification is to be deleted from the converted file. e The e parameter takes no value. Its presence indicates that the current format is to prevail only until another format specification is encountered in the file. Default values, which are assumed for parameters not supplied, are t-8 and m0. If the s parameter is not specified, no size checking is performed. If the first line of a file does not contain a format specification, the above defaults are assumed for the entire file. The following is an example of a line containing a format specification: * <:t5,10,15 s72:> * If a format specification can be disguised as a comment, it is not necessary to code the d parameter. SEE ALSO
ed(1), newform(1), tabs(1) SunOS 5.10 3 Jul 1990 fspec(4)
All times are GMT -4. The time now is 10:47 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy