Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to compare 2 files & get only few columns based on a condition related to both files?
# 1  
Old 12-09-2009
Question 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)
HTML Code:
   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 225.0   7 0.00   0 0.00 0.00  0.00   0  4.20  4.20   0   NULL
   ISC 1976  8 12 23 26 47.09  26.6967  97.0421  31.0 326 6.20  79 0.00 5.90  6.10   0  0.00  6.20   0      7.99e+2
   SSR 1976  8 13  7 20 10.00  37.7300  69.3700   2.0   0 0.00   0 2.78 0.00  0.00   0  0.00  2.78   0   NULL
    ISC 1976 11 27 15 12 11.05  36.3106  71.3868 112.0  35 4.70   3 0.00 0.00  0.00   0  0.00  4.70   0   NULL
   PDE 1976 11 27 21 42 12.13  36.5171  71.0456 190.0 397 6.10  88 0.00 5.90  0.00   0  0.00  6.10   0     8.59e+24
    ISC 1977  1  1 21 39 43.99  38.1922  90.9651  43.0 304 5.80  73 0.00 6.00  6.30   0  0.00  6.30   0     1.43e+25
   SSR 1977  1  1 22 11 16.00  39.4000  73.0000   0.0   0 0.00   0 3.33 0.00  0.00   0  0.00  3.33   0   NULL
    ISC 1977  1 12 23 35 20.47   1.5668  99.8199 191.0 355 5.60  56 0.00 5.90  0.00   0  0.00  5.90   0     8.73e+24
   SSR 1977  1 18 20 16 33.00  36.5000  71.0000 160.0   0 0.00   0 3.33 0.00  0.00   0  0.00  3.33   0   NULL
   ISC 1977  1 19  0 46 15.76  37.0168  95.7286  15.0 377 5.80  89 0.00 5.80  5.80   0  0.00  5.80   0     7.25e+24
   SSR 1977  1 19  2 35 45.00  36.3000  70.3000 200.0   0 0.00   0 3.33 0.00  0.00   0  0.00  3.33   0   NULL
File2: a.dat (which has 11 columns)
HTML Code:
1976  8 12 23 26 46.20  97.12  26.55  148  45    93
1976 11 27 21 42 12.20  70.64  36.33  294  41   139
1977  1  1 21 39 41.30  90.67  38.22  322  37   127
1977  1 12 23 35 19.10  99.50   1.08   92  46   132
1977  1 19  0 46 18.30  94.81  37.08  289  35    68
The output file say c.dat should contain few columns of a.dat & few columns of b.dat.
i.e. 9th, 10th & 11th columns from a.dat & 8th,9th & 19th column from b.dat based on a condition tat few colmns of a.dat must be equal to few columns of b.dat & only that rows which satisfies the condition these colmns should be selected
for example: Output file of a.dat & b.dat is as shown below
c.dat:SmilieSmilie
HTML Code:
26.6967  97.0421  2.78  148  45    93
36.5171  71.0456  6.10  294  41   139
38.1922  90.9651  6.30  322  37   127
 1.5668  99.8199  5.90   92  46   132
37.0168  95.7286  5.80  289  35    68
Now Tell me how to compare these 2 files such that
1)the 1st, 2nd, 3rd & 4th column of a .dat must be equal to 2nd 3rd 4th & 5th column of b.dat . AND then
2)From these rows from both files where the condition satisfies. The 8th, 9th & 19th column must be selected from b.dat & 9th,10th & 11th column must be selected from a .dat & these data should be stored in any other file say c.dat..
How is it. The example data for such condition is as shown above
Please help me out...SmilieSmilie
# 2  
Old 12-09-2009
Code:
 awk 'NR==FNR {a[$1$2$3$4]=$9" "$10" "$11} NR>FNR&&a[b=$2$3$4$5] {print $8,$9,$19,a[b]}' a.dat b.dat

# 3  
Old 12-09-2009
Bug
Thanks a lot...Its working...
But if my data of b.dat is as follows
HTML Code:
   PDE 2009  6  6  9  6 21.30  37.6300  21.0100  15.0   0 3.80   0 0.00 0.00  0.00   0  0.00  3.80   0   NULL
   PDE 2009  6  6  9 44 41.05  30.1900  86.2700  48.0   0 4.00   0 0.00 0.00  0.00   0  0.00  4.00   0   NULL
&
a.dat is as follows
HTML Code:
2009  6  6  9 44 37.00  86.43  30.99  218  76    -9
Then how to get out put as
HTML Code:
30.1900  86.2700  4.00  218  76    -9

I mean to say It must just compare the next field of a.dat only in such lines & get the result.Smilie
Last edited by reva; 12-09-2009 at 07:13 AM..
# 4  
Old 12-09-2009
so how do you get:

36.1900 70.2700
# 5  
Old 12-09-2009
Sorry Tying mistak..I have corrected it now check it out
# 6  
Old 12-09-2009
Code:
 awk 'NR==FNR {a[$1$2$3$4$5]=$9" "$10" "$11} NR>FNR&&a[b=$2$3$4$5$6] {print $8,$9,$19,a[b]}' a.dat b.dat

# 7  
Old 12-10-2009
Bug
I tried to do like this already..It deletes my other data..
for example
a.dat:
HTML Code:
2009 10 29 17 44 31.00  70.66  36.40  286  24    76
2009 11  7 20  9  4.00  86.22  29.29  172  43  -105
2009 11 10  2 48 46.00  91.86   8.08  123  78   169
b.dat:
HTML Code:
  PDE-Q 2009 10 29 17  0 38.84  27.2600  91.3800  26.0   0 5.20   0 0.00 0.00  0.00   0  0.00  5.20   0   NULL
 PDE-Q 2009 10 29 17 44 31.75  36.4300  70.7300 205.0   0 6.20   0 0.00 0.00  0.00   0  0.00  6.20   0   NULL
 PDE-Q 2009 11  7 20  8 46.72  29.5200  86.0600   7.0   0 5.60   0 0.00 0.00  0.00   0  0.00  5.60   0   NULL
 PDE-Q 2009 11 10  2 48 46.87   8.0800  91.8900  23.0   0 6.00   0 0.00 0.00  0.00   0  0.00  6.00   0   NULL
The correct output i should get is
c.dat:
HTML Code:
36.4300  70.7300   6.20 286 24 76
29.5200  86.0600  5.60 172  43  -105
8.0800 91.8900 6.00 123 78 169
but if i use your code i get output for such lines as
HTML Code:
36.4300 70.7300 6.20 286 24 76
8.0800 91.8900 6.00 123 78 169
&
if i use your first code which is thier i get output as
HTML Code:
27.2600 91.3800 5.20 286 24 76
36.4300 70.7300 6.20 286 24 76
29.5200 86.0600 5.60 172 43 -105
8.0800 91.8900 6.00 123 78 169
Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Compare two files with awk and condition

I am preparing a script to check the configuration of the db2 against the standard configuration. I am fetching the output in file A and want to compare it with the standard output written in file B. File A Diagnostic error capture level (DIAGLEVEL) = 3 Audit buffer size (4KB) (AUDIT_BUF_SZ)... (2 Replies)
Discussion started by: bashb
2 Replies

2. UNIX for Beginners Questions & Answers

Compare between two files with condition

Hello there. I am trying to compare two files. File1 Austria Mobile 1 United Kingdom Mobile 1 ... File2 Austria Mobile Vien 2 Austria Mobile Ostr 0 United Kingdom Mobile Dev 0.7 United Kingdom Mobile OST 1.5 What i want to do is to compare both files and... (12 Replies)
Discussion started by: dragonfly85
12 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

compare 2 files and return unique lines in each file (based on condition)

hi my problem is little complicated one. i have 2 files which appear like this file 1 abbsss:aa:22:34:as akl abc 1234 mkilll:as:ss:23:qs asc abc 0987 mlopii:cd:wq:24:as asd abc 7866 file2 lkoaa:as:24:32:sa alk abc 3245 lkmo:as:34:43:qs qsa abc 0987 kloia:ds:45:56:sa acq abc 7805 i... (5 Replies)
Discussion started by: anurupa777
5 Replies

5. UNIX for Dummies Questions & Answers

moving files based on condition

hi i have to move files and send an email and attached the bad files to inform the developer about that. #!/bin/ksh BASE_DIR=/data/SrcFiles cd $BASE_DIR ## finding the files from work directory which are changed in 1 day find -type f -name "*.csv" –ctime 0 > /home/mydir/flist.txt ##... (14 Replies)
Discussion started by: awais290
14 Replies

6. Shell Programming and Scripting

compare 2 files and extract the data which is not present in other file with condition

I have 2 files whose data's are as follows : fileA 00 lieferungen 00 attractiop 01 done 02 forness 03 rasp 04 alwaysisng 04 funny 05 done1 fileB alwayssng dkhf fdgdfg dfgdg sdjkgkdfjg funny rasp (7 Replies)
Discussion started by: rajniman
7 Replies

7. Shell Programming and Scripting

compare 2 files based on columns

Hi Experts, Is there a way to compare 2 files by columns and print matching cases. I have 2 files as below, I want cases where col1 and col2 in f1 matches col1 and col2 in f2 to be printed as output. The separator is space. I want the output to have col1 col2 col 3 from both files printed... (7 Replies)
Discussion started by: novice_man
7 Replies

8. Shell Programming and Scripting

Compare columns of 2 files based on condition defined in a different file

I have a control file which tells me which are the fields in the files I need to compare and based on the values I need to print the exact value if key =Y and output is Y , or if output is Y/N then I need to print only Y if it matches or N if it does not match and if output =N , then skip the feild... (7 Replies)
Discussion started by: newtoawk
7 Replies

9. UNIX for Dummies Questions & Answers

How to compare 2 files & get specific value & replace it in other file.

Hiiii Friends I have 2 files with huge data. I want to compare this 2 files & if they hav same set of vales in specific rows & columns i need to get that value from one file & replace it in other. For example: I have few set data of both files here: a.dat: PDE-W 2009 12 16 5 29 11.11 ... (10 Replies)
Discussion started by: reva
10 Replies

10. Shell Programming and Scripting

Merging of all files based on a condition

Hi Friends, I am new to UNIX. I need to merge all the files(to FINAL.txt) in single directory based one condition. Out of all the files one of file will have specific value like :GF01: at any where in the file. so the file which is having :GF01: should be appended at the last. EX:... (5 Replies)
Discussion started by: arund_01
5 Replies
Login or Register to Ask a Question