Login or Register to Ask a Question and Join Our Community


UNIX for Advanced & Expert Users

Match and print based on columns

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Advanced & Expert Users Match and print based on columns
# 1  
Old 08-09-2014
Match and print based on columns
HI,

I have 2 different questions in this thread.
Consider 2 files as input (input file have different line count )
File 1
Code:
1 1 625 56
1 12 657 34 
1 9 25 45 1
2 20 54 67
3 25 35 27
4 45 73 36
5 125 56 45

File2
Code:
1 1 878 76
1 9 83 67
2 20 73 78
4 47 22 17
3 25 67 99

1st Question : When column2, file1 = column2, file2 and column2, file1 !=column2,file2 anywhere in the file, the output should be
Code:
1 1 625 56 1 1 878 76
1 9 25 45 1 9 83 67
2 20 54 67 2 20 73 78
3 25 35 37 3 25 67 99
1 12 657 34 4 47 22 17
4 45 73 36 - - - - 
5 125 56 45 - - - -

so the 1st 4 lines are the lines where columns match and the last 3 lines are where columns don't match. (In general, 1st want all the matching columns to be printed followed by non matching ones).

2nd Question.The output should look like this when the columns 2 match and when they don't match from the input file1 and 2.
Code:
1 1 625 56 1 1 878 76
1 12 657 34 - - - -
1 9 25 41 1 9 83 67
2 20 54 67 2 20 73 78
3 25 35 27 3 25 67 99
4 45 73 36 - - - -
- - - - 4 27 22 17
5 125 56 45 - - - -

Thanks in advance!!!
Last edited by rossi; 08-09-2014 at 08:52 AM..
# 2  
Old 08-09-2014
Please don't use ICODE tags, use CODE tags instead.

How do you match records in the third last line, i.e. 1 12 657 34 4 47 22 17 Why should we select that record from file2?
# 3  
Old 08-09-2014
HI RudiC,

I have edit the tags.

The 3rd last line is printed like that because now there isn't anything matching in file 1 and file to so the unmatched things are being printed side by side.
(If there was something else left in file 2 (printing a hypothetical example) which wasn't matching then the 2nd last line would have been printed like
Code:
4 45 73 36 10 67 89 90

# 4  
Old 08-09-2014
For your second problem, how about this:
Code:
awk     'NR==FNR        {T[$2]=$0; next}
         $2 in T        {print $0, T[$2]; delete T[$2]; next}
                        {print $0, "- - - -"}
         END            {for (i in T) print "- - - -", T[i]}
        ' file2 file1
1 1 625 56 1 1 878 76
1 12 657 34 - - - -
1 9 25 45 1 1 9 83 67
2 20 54 67 2 20 73 78
3 25 35 27 3 25 67 99
4 45 73 36 - - - -
5 125 56 45 - - - -
- - - - 1 15 56 57
- - - - 4 47 22 17

You're missing the 1 15 56 57 line from file2 in your sample output. Or I'm missing your problem...
Last edited by RudiC; 08-09-2014 at 08:50 AM.. Reason: posted result twice.
This User Gave Thanks to RudiC For This Post:
rossi
# 5  
Old 08-09-2014
Sorry, I missed it so took it out from the thread now

---------- Post updated at 12:56 PM ---------- Previous update was at 11:53 AM ----------

Thanks RudiC,

The code for the second problem works perfectly.
I will give a try once again for the 1st problem.

Vishal
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Data match 2 files based on first 2 columns matching only and join if match

Hi, i have 2 files , the data i need to match is in masterfile and i need to pull out column 3 from master if column 1 and 2 match and output entire row to new file I have tried with join and awk and i keep getting blank outputs or same file is there an easier way than what i am... (4 Replies)
Discussion started by: axis88
4 Replies

2. Shell Programming and Scripting

Comparing two columns in two files and printing a third based on a match

Hello all, First post here. I did not notice a previous post to help me down the right path. I am looking to compare a column in a CSV file against another file (which is not a column match one for one) but more or less when a match is made, I would like to append a third column that contains a... (17 Replies)
Discussion started by: dis0wned
17 Replies

3. Shell Programming and Scripting

Need to print the next word from the same line based on grep string condtion match.

I need to fetch particular string from log file based on grep condition match. Actual requirement is need to print the next word from the same line based on grep string condtion match. File :Java.lanag.xyz......File copied completed : abc.txt Ouput :abc.txt I have used below... (5 Replies)
Discussion started by: siva83
5 Replies

4. Shell Programming and Scripting

Match columns and print specific field

Hello, I have data in following format. ... (6 Replies)
Discussion started by: Pushpraj
6 Replies

5. Shell Programming and Scripting

Print sequences from file2 based on match to, AND in same order as, file1

I have a list of IDs in file1 and a list of sequences in file2. I can print sequences from file2, but I'm asking for help in printing the sequences in the same order as the IDs appear in file1. file1: EN_comp12952_c0_seq3:367-1668 ES_comp17168_c1_seq6:1-864 EN_comp13395_c3_seq14:231-1088... (5 Replies)
Discussion started by: pathunkathunk
5 Replies

6. Shell Programming and Scripting

Match two columns from two files and print output

Hello, I have two files which are of the following format File 1 which has two columns Protein_ID Substitution NP_997239 T53R NP_060668 V267M NP_058515 P856A NP_001206 T55M NP_006601 D371Y ... (2 Replies)
Discussion started by: nans
2 Replies

7. Shell Programming and Scripting

Match files based on either of the two columns awk

Dear Shell experts, I have 2 files with structure: File 1: ID and count head test_GI_count1.txt 1000094 2 10039307 1 10039641 1 10047177 11 10047359 1 1008555 2 10120302 1 10120672 13 10121776 1 10121865 32 And 2nd file: head Protein_gi_GeneID_symbol.txt protein_gi GeneID... (11 Replies)
Discussion started by: smitra
11 Replies

8. Shell Programming and Scripting

match two key columns in two files and print output (awk)

I have two files... file1 and file2. Where columns 1 and 2 of file1 match columns 1 and 2 of file2 I want to create a new file that is all file1 + columns 3 and 4 of file2 :b: Many thanks if you know how to do this.... :b: file1 31-101 106 0 92 31-101 106 29 ... (2 Replies)
Discussion started by: pelhabuan
2 Replies

9. Shell Programming and Scripting

Match and print columns in second file

Hi All, I have to match each row in file 1 with 1st row in file 2 and print the corresponding column from file2. I am trying to use an awk script to do this. For example cat File1 X1 X3 X4 cat File2 ID X1 X2 X3 X4 A 1 6 2 1 B 2 7 3 3 C 3 8 4 1 D 4 9 1 1 (3 Replies)
Discussion started by: newpro
3 Replies

10. Shell Programming and Scripting

awk to print lines based on string match on another line and condition

Hi folks, I have a text file that I need to parse, and I cant figure it out. The source is a report breaking down softwares from various companies with some basic info about them (see source snippet below). Ultimately what I want is an excel sheet with only Adobe and Microsoft software name and... (5 Replies)
Discussion started by: rowie718
5 Replies
Login or Register to Ask a Question