Member Avatar for fo2sh

Hello everybody,

I have the following records in a file.

15:00:00.870;334
15:00:00.872;1314
15:00:00.889;334
15:00:00.890;334
15:00:00.891;334
15:00:00.892;334
15:00:00.909;212
15:00:00.910;334
15:00:00.929;334
15:00:00.948;334
15:00:00.950;334
15:00:00.969;334
15:00:00.970;334
15:00:00.990;334
15:00:01.010;295
15:00:01.011;334
15:00:01.012;334
15:00:01.029;334
15:00:01.030;334
15:00:01.048;334
15:00:01.049;334
15:00:01.051;1314

I want to count second column occurrence per second, so I would be able to get something like the below:

1314 occurred at 15:00:01 1 time
334 occurred at 15:00:01 6 time
295 occurred at 15:00:01 1 time
334 occurred at 15:00:00 12 time
212 occurred at 15:00:00 1 time
1314 occurred at 15:00:00 1 time

Would someone help me with this please, thanks in advance.

Edited by fo2sh
  • 3 Contributors
  • 2 Replies
  • 621 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by dcblack56

Dani AI

Generated

Brief recap and options: ’s file has timestamps with fractional seconds and a semicolon-separated value. suggested using Python and showed a Perl one-liner. If the goal is to stay in a bash-friendly toolset, awk or a small sort+uniq pipeline is the simplest, portable solution that mirrors what those answers do: extract the seconds-truncated time and the second column, then count pairs.

A compact awk solution (works in a plain shell):

awk -F'[.;]' '
  { k = $1 SUBSEP $3; c[k]++ }
  END {
    for (k in c) {
      split(k, a, SUBSEP)
      printf "%s occurred at %s %d time(s)\n", a[2], a[1], c[k]
    }
  }
' file

Notes: the field separator splits on the dot and the semicolon so $1 is HH:MM:SS and $3 is the second column. SUBSEP builds a unique composite key. The output order is arbitrary (awk hashes keys).

A streaming alternative that produces sorted output and uses very little memory:

awk -F'[.;]' '{print $1, $3}' file | sort | uniq -c | awk '{print $3 " occurred at " $2 " " $1 " time(s)"}'

Explanation: first awk prints time value, sort groups identical pairs, uniq -c counts them, final awk formats the sentence. This is preferable for very large files.

Troubleshooting and caveats:

  • If the file has CRLF endings, run tr -d '\r' or dos2unix first.
  • If timestamps or separators vary, adapt the -F or use a regex in awk.
  • For huge numbers of distinct pairs the in-memory awk counter may grow; prefer the sort|uniq pipeline in that case.
  • If a different output ordering is required (by time, then by count), pipe the results to sort with appropriate keys.

Either approach gives the same counts as the Perl/Python options but stays friendly to a bash-centric workflow.

Recommended Answers

All 2 Replies

Member Avatar for rproffitt

My advice is to expand the possible solutions to python or another scripting language. Just bash? Too much of a straight jacket approach.

Member Avatar for dcblack56

Perhaps unreadable, but quick/easy/works -- you just have to master Perl5

perl -ne '$R{$1,$2}++ if m/^(\d+:\d+:\d+)[.]\d+;(\d+)$/; END{ for $tv (keys %R) { ($t,$v)=split($;,$tv); printf "%s occured at %s %d times\n",$v,$t,$R{$tv};}}' FILENAME

Edited by Reverend Jim because: Removed the SHOUTING
commented: "Now they have two problems." +15
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.