Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Compare columns of two files and retrieve data

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Compare columns of two files and retrieve data
# 1  
Old 07-04-2013
Compare columns of two files and retrieve data
Hi guys, I need your help.
I have two files:
file1
Code:
1
3
5

file2
Code:
1,XX
2,AA
3,BB
4,CC
5,DD

I would like to compare the first column and where they are equal to write that output in a new file:
Code:
1,XX
3,BB
5,DD

It is supposed that all numbers from file1 should be presented in file2.
And is it possible, just in case if the number is not available in file2, to write only the number from file1 and leave second column empty or fill it with N/A
E.g.
Code:
00,
or
00,N/A

Thanks in advance!
# 2  
Old 07-04-2013
Code:
awk -F, '
        NR == FNR {
                A[$1]
                next
        }
        $1 in A {
                B[$1]
                print
        }
        END {
                for ( k in A )
                {
                        if ( !( k in B ) )
                                print k, "N/A"
                }
        }
' OFS=, file1 file2

This User Gave Thanks to Yoda For This Post:
apenkov
# 3  
Old 07-04-2013
Code:
join -t, file1 file2 
1,XX
3,BB
5,DD

# 4  
Old 07-04-2013
The join command can be slightly modified to meet OP's requirement:
Code:
join -t, -a1 -1 1 -2 1 -e "N/A" -o 1.1 2.2 file1 file2

This User Gave Thanks to Yoda For This Post:
RavinderSingh13
# 5  
Old 07-05-2013
Hi,
I tried with awk, but I am getting syntax error.
This with join looks nice, but It doesn't work, just doing nothing.
My OS is:
Code:
 Solaris 10 10/08 s10s_u6wos_07b SPARC

# 6  
Old 07-05-2013
Try nawk or /usr/xpg4/bin/awk, instead of awk.
This User Gave Thanks to radoulov For This Post:
apenkov
# 7  
Old 07-05-2013
awk 'NR==FNR{A[$9];next}!($9 in A)' test.txt test2.txt --> change column name

above one will compare 9th column of two files and display the difference
This User Gave Thanks to NarayanaPrakash For This Post:
apenkov
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

How to compare two columns in two files?

Hi All, I have a.dat file with content 1,338,30253395122015103,2015103,UB0085000,STMT151117055527002,,, 1,338,30253395122015103,2015103,UB0085000,STMT151117055527001,,, and b.dat having content 1,STMT151117055527001,a1.txt,b1.txt,c1.txt 1,STMT151117055527002,a2.txt,b2.txt,c2.txt ... (13 Replies)
Discussion started by: PRAMOD 96
13 Replies

2. UNIX for Dummies Questions & Answers

Help need to compare columns in files

Hi, Below is my requirement file1 id|cnt 1|1 2|2 3|3 file2 id_1|cnt_1 1|1 2|1 3|1 I want to compare cnt and cnt_1 columns, if they are differ then give the details Am using below awk command, but the output is not as expected. (2 Replies)
Discussion started by: grandhirahuletl
2 Replies

3. Shell Programming and Scripting

Compare 2 csv files by columns, then extract certain columns of matcing rows

Hi all, I'm pretty much a newbie to UNIX. I would appreciate any help with UNIX coding on comparing two large csv files (greater than 10 GB in size), and output a file with matching columns. I want to compare file1 and file2 by 'id' and 'chain' columns, then extract exact matching rows'... (5 Replies)
Discussion started by: bkane3
5 Replies

4. Shell Programming and Scripting

Using shell script to compare files and retrieve connections

Hello, I want to use shell script to generate network files (I tried with python but its taking too long). I have a list of nodes: node.txt LOC_Os11g37970 LOC_Os01g07760 LOC_Os03g19480 LOC_Os11g45740 LOC_Os06g08290 LOC_Os07g02800 I have an edge-list as well: edge.txt Source_node ... (2 Replies)
Discussion started by: Sanchari
2 Replies

5. Shell Programming and Scripting

Help to retrieve data from two files matching a string

Hello Experts, I have come back to this forum after a while now, since require a better way to get my result.. My query is as below.. I have 3 files -- 1 Input file, 2 Data files .. Based on the input file, data has to be retreived matching from two files which has one common key.. For EX:... (4 Replies)
Discussion started by: shaliniyadav
4 Replies

6. UNIX for Dummies Questions & Answers

How to compare two columns and retrieve data

I am a newbie to Unix and slowly learning it. I have a large data set with 8 different columns. I want to compare two columns and retrieve data if the two columns have similar number. I have attached the example. There are two columns (S-Contig and N-Contig). I want to retrieve the data from... (7 Replies)
Discussion started by: bjorngill
7 Replies

7. Ubuntu

How to compare two columns and fetch the common data with additional column

Dear All, I am new to this forum and please ignore my little knowledge :p I have two types of data (a subset is given below) data version 1: 439798 2 1 451209 1 2 508696 2 1 555760 2 1 582757 1 2 582889 1 2 691827... (2 Replies)
Discussion started by: evoll
2 Replies

8. UNIX for Dummies Questions & Answers

Compare Columns in two files

Hi all, I would like to compare a column in one file to a column in another file and when there is a match it prints the first column and the corresponding second column. Example File1 ABA ABC ABE ABF File 2 ABA 123 ABB 124 ABD 125 ABC 126 So what I would like printed to a file... (0 Replies)
Discussion started by: pcg
0 Replies

9. Shell Programming and Scripting

Compare data and columns script

Hello all, I have below SQLs to compare data between 2 identical tables.in my database. Can any body help me to convert this into a script db2 "select *from TAB1 where PK IN (select PK from TAB2) order by BEDG_NR". Note: These SQL should run for any given 2 tables as input TAB1... (4 Replies)
Discussion started by: kanakaraju
4 Replies

10. Shell Programming and Scripting

How to compare 2 files & get only few columns based on a condition related to both files?

Hiiiii friends I have 2 files which contains huge data & few lines of it are as shown below File1: b.dat(which has 21 columns) SSR 1976 8 12 13 10 44.00 39.0700 70.7800 7.0 0 0.00 0 2.78 0.00 0.00 0 0.00 2.78 0 NULL ISC 1976 8 12 22 32 37.39 36.2942 70.7338... (6 Replies)
Discussion started by: reva
6 Replies
Login or Register to Ask a Question