How to get difference of the same column between two files when other column matches?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to get difference of the same column between two files when other column matches?
# 1  
Old 04-18-2013
How to get difference of the same column between two files when other column matches?

File 1:
Code:
20130416,235800,10.78.25.104,BR2-loc,60.0,1624,50.0,0,50.0,0
20130416,235800,10.78.25.104,BR1-LOC,70.0,10,50.0,0,70.0,0
20130416,235800,10.78.25.104,Hub_None,60.0,15,60.0,0,50.0,0

File 2:
Code:
20130417,000200,10.78.25.104,BR2-loc,60.0,1626,50.0,0,50.0,0
20130417,000200,10.78.25.104,BR1-LOC,70.0,12,50.0,0,70.0,0
20130417,000200,10.78.25.104,Hub_None,60.0,17,60.0,0,50.0,0

I have to compare these two files, match the third and fouth column and if they matches, should do the diff of fourth column. How can I do it ?

i.e i should get output like
Code:
20130417,000200,10.78.25.104,BR2-loc,60.0,2,50.0,0,50.0,0
20130417,000200,10.78.25.104,BR1-LOC,70.0,2,50.0,0,70.0,0
20130417,000200,10.78.25.104,Hub_None,60.0,2,60.0,0,50.0,0

Moderator's Comments:
Mod Comment Use code tags, see PM.
# 2  
Old 04-18-2013
You mean the difference of the sixth column, don't you?
# 3  
Old 04-18-2013
yes. If the third and fourth column matches need to get the difference of 6th column
# 4  
Old 04-18-2013
Code:
$ cat temp.sh
paste -d "," file1 file2 > pasted.txt
awk 'BEGIN { FS = OFS = "," } \
  $3 == $13 && $4 == $14 { \
    print $11, $12, $13, $14, $15, $16 - $6, $17, $18, $19, $20 \
    }' pasted.txt

Code:
$ ./temp.sh
20130417,000200,10.78.25.104,BR2-loc,60.0,2,50.0,0,50.0,0
20130417,000200,10.78.25.104,BR1-LOC,70.0,2,50.0,0,70.0,0
20130417,000200,10.78.25.104,Hub_None,60.0,2,60.0,0,50.0,0

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

If pattern in column 3 matches pattern in column 2 (any row), print value in column 1

Hi all, I have searched and searched, but I have not found a solution that quite fits what I am trying to do. I have a long list of data in three columns. Below is a sample: 1,10,8 2,12,10 3,13,12 4,14,14 5,15,16 6,16,18 Please use code tags What I need to do is as follows: If a... (4 Replies)
Discussion started by: bleedingturnip
4 Replies

2. Shell Programming and Scripting

I want to find the difference between two files, only for the header (column names)

Hi All, I want to find the difference between two files, by checking only the headers (column names) and report if any new column is added in the latest file. For Ex: If the file "declartion.txt has these columns url;image;id;showcase_id;showcase_name and the actual file "feed.txt" has... (34 Replies)
Discussion started by: Praveen Pandit
34 Replies

3. Shell Programming and Scripting

Difference between 2 files, one with 1 column and 2nd file with multiple columns

Hi, I need to find the difference between 2 files in unix and write the result in the new file File1: A B File2: X 123 hajkd Y 345 adjfka A 123 djafjhd B 678 dsndjks Output file: X 123 hajkd Y 345 adjfka Thanks. (6 Replies)
Discussion started by: nani1984
6 Replies

4. Shell Programming and Scripting

Difference of the same column when two other column matches and one column differs less than 1 hour

This is my input file : # cat list 20130430121600, cucm, location,76,2 20130430121600,cucm1,location1,76,4 20130430122000,cucm,location,80,8 20130430122000,cucm1,location1,90,8 20130430140000,cucm1,location1,87,11 20130430140000, cucm,location,67,9 This is the required output ... (1 Reply)
Discussion started by: Lakshmikumari
1 Replies

5. Shell Programming and Scripting

awk - how to get difference of the same column when other column matches

I have a file like this : # cat list cucm, location,76,2 cucm1,location1,76,4 cucm,location,80,8 cucm1,location1,90,8 cucm1,location1,87,11 cucm,location,67,9 and I want output like this : cucm,location,76,2 cucm1,location1,76,4 cucm,location,80, 6 ===> (8-2 =6) cucm1,location1,90,4... (5 Replies)
Discussion started by: Lakshmikumari
5 Replies

6. Homework & Coursework Questions

Script to find difference between 2 files by column

Hi , i am newbie to shell scripting and am trying to do the below job, A shell script to be run with a command like sh Compare.ksh file1.txt file2.txt 1 2 > file3.txt 1 2-are the key columns Consider the delimiter would be Tab or comma File 1: SK TEST NAME MATHS PHYSICS 21 1 AAA... (1 Reply)
Discussion started by: shakthi666
1 Replies

7. Shell Programming and Scripting

Script to find difference between 2 files by column

Hi , i am newbie to shell scripting and am trying to do the below job, A shell script to be run with a command like sh Compare.ksh file1.txt file2.txt 1 2 > file3.txt 1 2-are the key columns Consider the delimiter would be Tab or comma File 1: SK TEST NAME MATHS PHYSICS 21 1... (1 Reply)
Discussion started by: shakthi666
1 Replies

8. Shell Programming and Scripting

Merge two columns from two files into one if another column matches

I have two text files that look something like this: A:B:C 123 D:E:F 234 G:H:I 345 J:K:L 123 M:N:O 456 P:Q:R 567 A:B:C 456 D:E:F 567 G:H:I 678 J:K:L 456 M:N:O 789 P:Q:R 890 I want to find the line where the first column matches and then combine the second columns into a single... (8 Replies)
Discussion started by: pbluescript
8 Replies

9. Shell Programming and Scripting

Replace column that matches specific pattern, with column data from another file

Can anyone please help with this? I have 2 files as given below. If 2nd column of file1 has pattern foo1@a, find the matching 1st column in file2 & replace 2nd column of file1 with file2's value. file1 abc_1 foo1@a .... abc_1 soo2@a ... def_2 soo2@a .... def_2 foo1@a ........ (7 Replies)
Discussion started by: prashali
7 Replies

10. Shell Programming and Scripting

script to compare first column of two files and find difference

Hi, I want to write a script which will compare the 1st column of both the files and will give the difference. e.g:- my 1st file contains: 89 /usr 52 /usr/local 36 /tmp 92 /opt 96 /home 27 /etc/opt/EMCom 1 ... (3 Replies)
Discussion started by: adityam
3 Replies
Login or Register to Ask a Question