Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Two columns-Common records - 20 files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Two columns-Common records - 20 files
# 1  
Old 02-27-2013
Two columns-Common records - 20 files
Hi Friends,

I have an input file like this

Code:
cat input1
x 1
y 2
z 3
a 2
b 4
c 6
d 9

Code:
cat input2
x 7
h 8
k 9
l  5
m 9
d 12
c 7

Similarly I have 20 files, but now I want to match on column1, and print the corresponding values as 20 different columns

Code:
cat output
x 1 7 ..................................value_in_file20
d 9 12.................................value_in_file20
c 6 7...................................value_in_file20

# 2  
Old 02-27-2013
with 245 posts (most of which were awk-related)... what have you tried so far?
# 3  
Old 02-27-2013
Quote:
Originally Posted by vgersh99
with 245 posts (most of which were awk-related)... what have you tried so far?
Code:
awk 'NR==FNR{a[$1]=$0;next}$1 in a{print a[$1],$0}' *.txt

And instead of seeing 21 columns, I see only 3.

Thanks for testing my awk skillset. I like this way. Smilie
# 4  
Old 02-27-2013
awk -f jac.awk file1 file2 ... fileN
jac.awk:
Code:
FNR==1 {argc=(argc)?argc+1:2}
{v[$1];a[argc,$1]=$2}
END {
  for( i in v) {
    printf i
    for(j=2;j<=argc;j++) {
      idx=j SUBSEP i
      printf("%c%s",OFS,(idx in a)?a[idx]:OFS)
    }
    printf ORS
  }
}

Last edited by vgersh99; 02-27-2013 at 04:29 PM.. Reason: empty cell if a value is not present in a file
# 5  
Old 02-27-2013
Quote:
Originally Posted by vgersh99
awk -f jac.awk file1 file2 ... fileN
jac.awk:
Code:
FNR==1 {argc=(argc)?argc+1:2}
{v[$1];a[argc,$1]=$2}
END {
  for( i in v) {
    printf i
    for(j=2;j<=argc;j++) {
      idx=j SUBSEP i
      printf("%c%s",OFS,(idx in a)?a[idx]:0)
    }
    printf ORS
  }
}

Thanks a ton.

If I understood it right, it prints all common records if col1 is present in two or more files. And if there is no value even if col1 is present in only two files, it would print 0 instead.

Did I get it right?
# 6  
Old 02-27-2013
that's correct - I just changed the code print ''empty string' if a particular value is not present in col1 of a particular file. Not sure how you wanted it: 0 or 'empty field.
This User Gave Thanks to vgersh99 For This Post:
jacobs.smith
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Paste columns based on common column: multiple files

Hi all, I've multiple files. In this case 5. Space separated columns. Each file has 12 columns. Each file has 300-400K lines. I want to get the output such that if a value in column 2 is present in all the files then get all the columns of that value and print it side by side. Desired output... (15 Replies)
Discussion started by: genome
15 Replies

2. UNIX for Beginners Questions & Answers

Comparing fastq files and outputting common records

I have two files: File_1: @M04961:22:000000000-B5VGJ:1:1101:9280:7106 1:N:0:86 GGCATGAAAACATACAAACCGTCTTTCCAGAAATTGTTCCAAGTATCGGCAACAGCTTTATCAATACCATGAAAAATATCAACCACACCAGAAGCAGCAT + GGGGGGGGGGGGGGGGGCCGGGGGF,EDFFGEDFG,@DGGCGGEGGG7DCGGGF68CGFFFGGGG@CGDGFFDFEFEFF:30CGAFFDFEFF8CAF;;8F ... (3 Replies)
Discussion started by: Xterra
3 Replies

3. UNIX for Dummies Questions & Answers

Merge selective columns from files based on common key

Hi, I am trying to selectively merge two files based on keys reported in the 1st column. File1: #file1-header1 file1-header2 111 qwe rtz uio 198 asd fgh jkl 165 yxc 789 poi uzt rew 89 lkj File2: #file2-header2 file2-header2 165 ghz nko2 ... (2 Replies)
Discussion started by: dovah
2 Replies

4. Shell Programming and Scripting

Common values in 2 columns in 2 files

Hello, Suppose I have these 2 tab delimited files, where the second column in first file contains matching values from first column of the second file, I would like to get an output like this: File A 1 A 2 B 3 C File B A Apple C Cinnabon B Banana I would like... (1 Reply)
Discussion started by: Mohamed EL Hadi
1 Replies

5. Shell Programming and Scripting

Compare multiple files, identify common records and combine unique values into one file

Good morning all, I have a problem that is one step beyond a standard awk compare. I would like to compare three files which have several thousand records against a fourth file. All of them have a value in each row that is identical, and one value in each of those rows which may be duplicated... (1 Reply)
Discussion started by: nashton
1 Replies

6. Shell Programming and Scripting

Compare 2 files having different number of columns and records

Hi , My requirement is to Compare 2 files having different number of columns and records and get the ouptut containing all the non-matching records from File A(with all column values ) .Example data below : File A contains following : Aishvarya |1234... (4 Replies)
Discussion started by: aishvarya.singh
4 Replies

7. Shell Programming and Scripting

Common records

Hi, I have the following files, A M 2 3 B E 4 5 C I 5 6 D O 4 5 A M 3 4 B E 5 2 F U 7 9 J K 2 3 OUTPUT A M 2 3 3 4 B E 4 5 5 2 thanks in advance, (7 Replies)
Discussion started by: jacobs.smith
7 Replies

8. Shell Programming and Scripting

Common records after matching on different columns

Hi, I have the following files. cat 1.txt cat 2.txt output.txt The logic is as follows.... (10 Replies)
Discussion started by: jacobs.smith
10 Replies

9. Shell Programming and Scripting

Common records using AWK

Hi, To be honest, I am really impressed and amazed at the pace I find solutions for un-solved coding mysteries in this forum. I have a file like this input1.txt x y z 1 2 3 a b c 4 -3 7 k l m n 0 p 1 2 a b c 4 input2 x y z 9 0 -1 a b c 0 6 9 k l m 8 o p 1 2 a f x 9 Output... (9 Replies)
Discussion started by: jacobs.smith
9 Replies

10. Solaris

Comparing the common columns of a table in two files

Hi, I have two text files.The first and the 2nd file have data in the same format For e.g. The first file has BOOKS COUNT: 40 BOOKS AUTHOR1 SUM:1018 MAX:47 MIN:1 AVG:25.45 BOOKS AUTHOR3 SUM:181 MAX:48 MIN:3 AVG:18.1 Note:Read it as Table columnname sum(column) max(column) min(column)... (1 Reply)
Discussion started by: ragavhere
1 Replies
Login or Register to Ask a Question