Group and sort based on count of same value of a column and sort another column within those groups

user_13591871 0
Viewed 160

Assume I got this logfile:

100.1 500
100.2 501
100.2 501
100.2 501
100.1 501
666.2 501
300.1 555
300.1 501
100.1 300
100.1 300
100.1 300
102.3 500
202.1 500
301.2 300

Any duplicate lines should be removed first. Then swap the columns. And then group lines based on first column value (501, 500, etc), sorting such groups based on the count of these values. Finally, sort the second column value of such groups in descending order.

This is how far I've gotten:

$ awk '{!seen[$2, $1]++}; END {for (i in seen) print i}' $filename |
       sort -k2 -n -r
555?300.1
501?666.2
501?300.1
501?100.2
501?100.1
500?202.1
500?102.3
500?100.1
300?301.2
300?100.1

This is the expected output:

501 666.2
501 300.1
501 100.2
501 100.1
500 202.1
500 102.3
500 100.1
300 301.2
300 100.1
555 300.1
awk sorting
2 Answers

With GNU awk could you please try following, though I still not sure if OP needs it exactly this one. Since OP confirmed in comments that this worked so adding this an answer and requested OP to change question accordingly too.

awk '
!c[$1,$2]++{
  a[$2]++
  b[$2]=(b[$2]?b[$2] ORS:"")$2 OFS $1
}
END{
  PROCINFO["sorted_in"] = "@val_num_desc"
  for(i in a){ print b[i] }
}
' Input_file

(Edit by Sundeep) I don't fully understand this myself, but this seems to work with GNU awk

$ awk 'BEGIN{PROCINFO["sorted_in"] = "@val_num_desc"};
       !seen[$0]++{a[$2]++; b[$2][$1]}
       END{for(i in a) for(j in b[i]) print i, j}' ip.txt
501 666.2
501 300.1
501 100.2
501 100.1
500 202.1
500 102.3
500 100.1
300 301.2
300 100.1
555 300.1
user_58665800

Another approach: this is a Schwartzian transform.

sort -u file \
| awk '
    {count[$2]++; a[NR] = $1; b[NR] = $2}
    END {for (i=1; i<=NR; i++) print count[b[i]], b[i], a[i]}
  ' \
| sort -nr -k 1,1 -k 3,3 \
| cut -d " " -f 2-

output

501 666.2
501 300.1
501 100.2
501 100.1
500 202.1
500 102.3
500 100.1
300 301.2
300 100.1
555 300.1
user_75520
Related