CODEWITHSUNDEEP
bashshellawk
kbiles
kbiles

Reputation: 33

count the number of times a value in a field appears in another field

I have had some great help recently in counting the number of times a value is repeated in a field but my next step is to count how many times a value is repeated in a field ($1) based on the value in another field ($3) with the result at the end of the line such as the example below:

Input File
1,2,3
1,1,1
3,2,3
4,1,4
2,1,3
5,2,2
5,1,5
5,4,6

Output File 

1,2,3,1    
1,1,1,2    
3,2,3,1    
4,1,4,1    
2,1,3,1    
5,2,2,1    
5,1,5,3    
5,4,6,0

I am looking at doing this with awk if possible but happy any other suggestion.

Upvotes: 1

Views: 345

Answers (1)

Steve
Steve

Reputation: 54402

Here's one way using awk:

awk -F, 'FNR==NR { a[$1]++; next } { print $0, ($3 in a ? a[$3] : "0") }' OFS=, file file

Results:

1,2,3,1
1,1,1,2
3,2,3,1
4,1,4,1
2,1,3,1
5,2,2,1
5,1,5,3
5,4,6,0

Explanation:

FNR==NR { ... }   # for the first file in the arguments list

a[$1]++           # add column one to an array incrementing it's value.

next              # skip processing the rest of the code

{ ... }           # for every line in the second file in the arguments list

print $0          # print the line

($3 in a ? a[$3] : "0")    # ...followed by the value of the third field in the
                           # array if it is indeed in the array, else print "0".
                           # this is a ternary operator, much like an if/else 
                           # statement

Upvotes: 1

Related Questions