Visit Our UNIX and Linux User Community

AWK - Comparing/Matching/Counting with 2 files

Thread Tools Search this Thread
Top Forums Shell Programming and Scripting AWK - Comparing/Matching/Counting with 2 files
# 1  
Old 03-30-2011
AWK - Comparing/Matching/Counting with 2 files

I have 2 files that I want to do some comparing on.

First, I want to find the unique list of devices in file1 and then put them to a new file, file2. I was able to do this without any problem with the following statement:
cat file1 | awk '{print $2}' | awk '!x[$0]++' > file2

Here is what I can't figure out how to do...I want to count every time each device listed in file2 (1st field) matches with a device in file1 (2nd field) AND the 5th field is a 0. Once I have this count which corresponds to each unique device, I want to put them into a new column in file2.

<timestamp> <device> <LBA> <size> <0 or 1>
302.984379 0 18289744 16 0
302.984515 1 33077888 16 0
302.992183 1 30383184 16 0
302.984379 0 18289744 16 0
302.984515 1 33077888 16 1
302.992183 1 30383184 16 0
302.999034 3 30920224 16 0
303.013796 2 21785824 16 0
303.017008 2 21996176 16 1
303.017494 5 20484048 16 0
303.018940 4 20599728 64 1
303.048797 2 10935056 64 0

<unique devices from file1>

<unique devices from file1> <# of times the device had a 0 in 5th col of file1>
0 2
1 3
2 2
3 1
4 0
5 1

Thanks in advance for your help!
# 2  
Old 03-30-2011
awk '{a[$2]}$NF=="0"{++b[$2]}END{for(i in a) print i,b[i]?b[i]:"0"|"sort -n"}' file

This User Gave Thanks to yinyuemi For This Post:
# 3  
Old 03-30-2011
That worked perfectly! Thank you so much!

Previous Thread | Next Thread
Test Your Knowledge in Computers #838
Difficulty: Medium
Both SMTP and HTTP are valid application layer protocols used as transport for SOAP.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Comparing two files by two matching fields

Long time listener first time poster. Hope someone can advise. I have two files, 1000+ lines in each, two fields in each file. After performing a sort, what is the best way to find exact matches where field $1 and $2 in file1 are also present in file2 on the same line, then output only those... (6 Replies)
Discussion started by: bstaff
6 Replies

2. Shell Programming and Scripting

Comparing same column from two files, printing whole row with matching values

First I'd like to apologize if I opened a thread which is already open somewhere. I did a bit of searching but could quite find what I was looking for, so I will try to explaing what I need. I'm writing a script on our server, got to a point where I have two files with results. Example: File1... (6 Replies)
Discussion started by: mitabrev83
6 Replies

3. UNIX for Dummies Questions & Answers

Awk: Counting occurrences between two files

Hi, I have two text files (1.txt and 2.txt). 2.txt contains two columns which are extracted from 1.txt using a simple if(condition) print. I want to: - count how many times the values contained in 2.txt appear in 1.txt -if they appear just one time, I have to delete the entire row in... (5 Replies)
Discussion started by: Pintug
5 Replies

4. Shell Programming and Scripting

Awk:Comparing and matching two file

Dear All, I have 2 files as follows file1.txt 261187210101|r 261187210101|r 261187220101|y 261187220102|y 261187220103|y 261187231011|b 261187231011|b 261187241012|g 261187241012|g file2.txt 1187220 1187241 output: file1 |file2 |match (3 Replies)
Discussion started by: sayandri
3 Replies

5. Shell Programming and Scripting

comparing two files for matching fields

I am newbie to unix and would please like some help to solve the task below I have two files, file_a.text and file_b.text that I want to evaluate. file_a.text 1698.74 1711.88 6576.25 899.41 3205.63 4187.98 697.35 1551.83 ... (3 Replies)
Discussion started by: gameli
3 Replies

6. Shell Programming and Scripting

Comparing the matches in two files using awk when both files have their own field separators

I've two files with data like below: file1.txt: AAA,Apples,123 BBB,Bananas,124 CCC,Carrot,125 file2.txt: Store1|AAA|123|11 Store2|BBB|124|23 Store3|CCC|125|57 Store4|DDD|126|38 So,the field separator in file1.txt is a comma and in file2.txt,it is | Now,the output should be... (2 Replies)
Discussion started by: asyed
2 Replies

7. Shell Programming and Scripting

comparing 2 files with awk

Hi, I'm a new user in awk and i'm trying to compare two files to create a third one if some values match in both files. The first file has this content: s 45.960746365 _21_ AGT 2490 [21:0 22:0 s 45.980418496 _21_ AGT 2491 [21:0 22:0 s 46.000090627 _21_ AGT 2492 [21:0 22:0 s 47.906552206... (2 Replies)
Discussion started by: carlosoria
2 Replies

8. Shell Programming and Scripting

comparing two files using awk

hit brick wall while trying to knock up a script that will take values from the "lookup" file and look it up in the "target" file and return values that dont appear in "target" but do in "lookup". just knocked up something using bits from previous threads but theres gotta be something wrong... (13 Replies)
Discussion started by: jack.bauer
13 Replies

9. Shell Programming and Scripting

Comparing 2 csv files and matching content

Hello, I have the following problem: There are two csv files csv-file #1: aaa1, aaa2, ... aaan aaa1, bbb2, ... bbbn aaa1, ccc2, ... cccn bbb1, bbb2, ... bbbn ... zzz1, zzz2, ... zzzn csv-file #2: aaa1, matchvalue1 ccc1, matchvalue2 (7 Replies)
Discussion started by: ghl10000
7 Replies

10. Shell Programming and Scripting

Merging files with AWK filtering and counting lines

Hi there, I have a couple of files I need to merge. I can do a simple merge by concatenating them into one larger file. But then I need to filter the file to get a desired result. The output looks like this: TRNH 0000000010941 ORDH OADR OADR ORDL ENDT 1116399 000000003... (2 Replies)
Discussion started by: Meert
2 Replies

Featured Tech Videos