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.

#file1
<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

#file2
<unique devices from file1>

0
1
2
3
4
5

#file3
<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!
Jonathan

try:

awk '{a[$2]}$NF=="0"{++b[$2]}END{for(i in a) print i,b?b:"0"|"sort -n"}' file
1 Like

That worked perfectly! Thank you so much!