Split CSV file with timestamp along breaks for defined period

Question

I wonder if there's an easy way, maybe one liner, with unix cli tools to split a CSV file with ISO-8601 UTC timestamps in millisecond precision (+%FT%T.%3NZ, e. g. 2021-05-27T13:59:33.641Z) along a defined time offset / break / difference, like for example two hours.

As always there are certain different ways of having it and while for other users with similar questions, other options might also be relevant in a comprehensive answer, I ...

... use/have git 2.31.1's GNU Bash 4.4.23, GNU sed 4.8, GNU Awk 5.0.0 (and all the other tools it bundles), xsv 0.13.0 and jq 1.6 on Windows 7
... would rather use this in a script that in an interactive shell
... use a semicolon (;) as delimiter, no comma
... do not have my values quoted (e. g. in single (') or double quotes ("))
... do not have a header
... would already have the entire CSV in a variable and would also want to have the result in variables (an array?) in order to be able to further analyze them
My columns do not have a fixed lengths in reality and may contain spaces and hyphens besides alphanumerical characters
The timestamp is the fifth of eight columns in my real world data
The file can be assumed to be at most 250k lines and 20 MiB
While it would be preferable if the script/command took less than half a second on my i5-4300U, 5 to 10 seconds max would still not be a dealbreaker

Example

If I had 2 hours as offset to use for my split (and I did not mix anything up), this file:

abc;square;2021-05-27T14:15:39.315Z
def;circle;2021-05-27T14:17:03.416Z
ghi;triang;2021-05-27T14:45:13.520Z
abc;circle;2021-05-27T15:25:47.624Z
ghi;square;2021-05-27T17:59:33.641Z
def;triang;2021-05-27T18:15:33.315Z
abc;circle;2021-05-27T21:12:13.350Z
ghi;triang;2021-05-27T21:15:31.135Z

would get split to the following three parts

abc;square;2021-05-27T14:15:39.315Z
def;circle;2021-05-27T14:17:03.416Z
ghi;triang;2021-05-27T14:45:13.520Z
abc;circle;2021-05-27T15:25:47.624Z

ghi;square;2021-05-27T17:59:33.641Z
def;triang;2021-05-27T18:15:33.315Z

abc;circle;2021-05-27T21:12:13.350Z
ghi;triang;2021-05-27T21:15:31.135Z

disclaimer: I am no native speaker, so if rewording makes this question more comprehensible please go for it. The verbosity re. e.g. also specifying the options that do not apply to my use case (comma, quotes) or using both the word semicolon and the sign ; in this question's text is for SEO purposes

This might be a good starting point: unix.stackexchange.com/a/121607/473754 — Andy, May 27, 2021 at 13:05
where does the starting time for the 2 hour period come from? a command line argument? the first time seen in the file? — cas, May 27, 2021 at 13:55
With respect to would already have the entire CSV in a variable and would also want to have the result in variables (an array?) in order to be able to further analyze them - for efficiency, robustness, simplicity, brevity, clarity, portability, etc. having your entire CSV in a variable is a bad starting point, outputting the result into a shell array or variable is a bad ending point, and continuing to analyze the CSV in shell afterwards is a bad approach. A shell is an environment from which to create/destroy files and processes and sequence calls to tools such as awk to analyze text. — Ed Morton, May 27, 2021 at 18:52

glenn jackman · Accepted Answer · 2021-05-27 15:05:54Z

Given your sample CSV data in variable $csv:

gawk '
    function timestamp2epoch(ts,       m) {
        if(match(ts, /([0-9]{4})-([0-9]{2})-([0-9]{2})T([0-9]{2}):([0-9]{2}):([0-9]{2})\..*/, m)) 
            return mktime(m[1] " " m[2] " " m[3] " " m[4] " " m[5] " " m[6])
        else
            return -1
    }

    BEGIN {
        FS = ";"
        interval = 2 * 3600     # 2 hours
    }

    { t = timestamp2epoch($3) }
    t > start + interval { start = t; n++ }
    { batch[n] = batch[n] (batch[n] == "" ? "" : "/") $0 }

    END {
        PROCINFO["sorted_in"] = "@ind_num_asc"
        for (i in batch)
            print batch[i]
    }
' <<<"$csv"

outputs

abc;square;2021-05-27T14:15:39.315Z/def;circle;2021-05-27T14:17:03.416Z/ghi;triang;2021-05-27T14:45:13.520Z/abc;circle;2021-05-27T15:25:47.624Z
ghi;square;2021-05-27T17:59:33.641Z/def;triang;2021-05-27T18:15:33.315Z
abc;circle;2021-05-27T21:12:13.350Z/ghi;triang;2021-05-27T21:15:31.135Z

That can be read into a shell array like:

mapfile -t batches < <(gawk '...' <<<"$csv")
declare -p batches

declare -a batches=([0]="abc;square;2021-05-27T14:15:39.315Z/def;circle;2021-05-27T14:17:03.416Z/ghi;triang;2021-05-27T14:45:13.520Z/abc;circle;2021-05-27T15:25:47.624Z" [1]="ghi;square;2021-05-27T17:59:33.641Z/def;triang;2021-05-27T18:15:33.315Z" [2]="abc;circle;2021-05-27T21:12:13.350Z/ghi;triang;2021-05-27T21:15:31.135Z")

And then interate over them like:

for ((i = 0; i < "${#batches[@]}"; i++)); do
    IFS="/" read -ra records <<<"${batches[i]}"
    echo "batch $i"
    for record in "${records[@]}"; do echo "  $record"; done
    echo
done

batch 0
  abc;square;2021-05-27T14:15:39.315Z
  def;circle;2021-05-27T14:17:03.416Z
  ghi;triang;2021-05-27T14:45:13.520Z
  abc;circle;2021-05-27T15:25:47.624Z

batch 1
  ghi;square;2021-05-27T17:59:33.641Z
  def;triang;2021-05-27T18:15:33.315Z

batch 2
  abc;circle;2021-05-27T21:12:13.350Z
  ghi;triang;2021-05-27T21:15:31.135Z

Here, I'm using / to separate records to communicate the awk output back to shell: if that character appears in your data, you'll need to choose something different. — glenn jackman, May 27, 2021 at 15:06
The awk script would use your local TZ and so DST would play a factor whereas the user says their timestamps are in UTC. So you may want to add a TZ setting or set the "utc flag" which is the 3rd arg to mktime() - mktime(m[1] " " m[2] " " m[3] " " m[4] " " m[5] " " m[6], 1). Alternatively you could add a 0 as the last part of the time spec to turn off DST determination - mktime(m[1] " " m[2] " " m[3] " " m[4] " " m[5] " " m[6] " 0") but setting the UTC flag seems most appropriate since the time IS in UTC. — Ed Morton, May 27, 2021 at 18:06

cas · Accepted Answer · 2021-05-28 01:15:11Z

The following perl script will output the input file, adding a blank line every time it sees a line that isn't within 2 hours of the previous starting period - splitting the input into batches of a maximum 2 hour duration.

The starting period is set when reading the first line, and only updated when an extra blank line is printed - this is to ensure a new batch at least every 2 hours - otherwise your sample input would be split into only two batches (6 lines from 14:15 to 18:15, and 2 lines at 21:12 and 21:15), and an extra log entry at, say, 16:45 and another at, say, 20:00 would prevent any splitting of your sample input.

It gets the date & time from the third field of the input - note that perl arrays start from zero rather than one, so $F[2] is the third field of array @F.

#!/usr/bin/perl

use strict;
use Date::Parse;

my $start;

while(<>) {
  chomp;
  my $approx;
  my @F = split /;/;

  # approximate date/time to start of hour
  ($approx = $F[2]) =~ s/:\d\d:\d\d\.\d+Z$/:00:00/;

  my $now = str2time($approx);
  $start = $now if ($. == 1);

  if (($now - $start) > 7200) {
    $start = $now;
    print "\n";
  };
  print "$_\n";
}

Sample output:

$ ./split.pl input.csv 
abc;square;2021-05-27T14:15:39.315Z
def;circle;2021-05-27T14:17:03.416Z
ghi;triang;2021-05-27T14:45:13.520Z
abc;circle;2021-05-27T15:25:47.624Z

ghi;square;2021-05-27T17:59:33.641Z
def;triang;2021-05-27T18:15:33.315Z

abc;circle;2021-05-27T21:12:13.350Z
ghi;triang;2021-05-27T21:15:31.135Z

If you need the output in separate files, you could do something like this instead:

#!/usr/bin/perl

use strict;
use Date::Parse;

my $start;

# output-file counter
my $fc = 1;
my $outfile = "file.$fc.csv";

open (my $fh, ">", $outfile) || die "couldn't open $outfile for write: $!\n";

while(<>) {
  chomp;
  my $approx;
  my @F = split /;/;

  # approximate date/time to start of hour
  ($approx = $F[2]) =~ s/:\d\d:\d\d\.\d+Z$/:00:00/;

  my $now = str2time($approx);
  $start = $now if ($. == 1);

  if (($now - $start) > 7200) {
    $start = $now;
    close($fh);
    $fc++;
    $outfile = "file.$fc.csv";
    open ($fh, ">", $outfile) || die "couldn't open $outfile for write: $!\n";
  };
  print $fh "$_\n";
}

If you want either version of the script to be a bit more flexible with the time formats it can handle, use:

  ($approx = $F[2]) =~ s/:\d\d:\d\d(?:\.\d+)?Z?$/:00:00/;

This allows both the decimal fraction and the Z to be optional in the time string.

The core Time::Piece module can parse timestamps, and doesn't require installation of Date::Parse. — glenn jackman, May 27, 2021 at 15:08
@glennjackman I prefer Graham Barr's Data::Parse from his TimeDate collection because it can handle pretty much any time format I throw at it, without having to hard-code a specific time format. If I needed something different, i.e. with more precision in time calculations, I'd use Dave Rolsky's DateTime and related modules. — cas, May 27, 2021 at 15:30

Ed Morton · Accepted Answer · 2021-05-28 13:06:31Z

With GNU awk for gensub() and mktime():

$ cat tst.awk
BEGIN {
    FS = ";"
    maxSecs = 2 * 60 * 60
    prevTime = -(maxSecs + 1)
}
{
    split($3,dt,/[.]/)
    dateHMS   = gensub(/[-T:]/," ","g",dt[1])
    currSecs  = mktime(dateHMS,1) "." dt[2]
    secsDelta = currTime - prevTime
    prevTime  = currTime
}
secsDelta > maxSecs {
    close(out)
    out = "out" (++numOut)
}
{ print > out }

$ awk -f tst.awk file

$ head out?
==> out1 <==
abc;square;2021-05-27T14:15:39.315Z
def;circle;2021-05-27T14:17:03.416Z
ghi;triang;2021-05-27T14:45:13.520Z
abc;circle;2021-05-27T15:25:47.624Z

==> out2 <==
ghi;square;2021-05-27T17:59:33.641Z
def;triang;2021-05-27T18:15:33.315Z

==> out3 <==
abc;circle;2021-05-27T21:12:13.350Z
ghi;triang;2021-05-27T21:15:31.135Z

DanieleGrassini · Accepted Answer · 2021-05-28 13:33:52Z

0

If all the date in the file belongs to the same day:

#!/usr/bin/awk -f
BEGIN {
    FS=OFS=";"
    ho = 1
}

{
    # Split the last field in date and times
    split($NF, a, "T")

    # Get the hour from time
    h = a[2]
    sub(/:.*$/, "", h)
    
    if (lh == 0) lh = h+ho

    if (h > lh) {
        lh = h+ho
        print "\n"
    }
}1

You can edit the ho (hour offset) in the BEGIN block of the script to split in the csv for other hour offset.

#!/usr/bin/awk -f
BEGIN {
    FS=OFS=";"

    # Set here the hour offset
    hour_offset = 1

    # Get the hour values in seconds
    ho = 60 * 60 * hour_offset
}

{
    sub(/Z$/, "", $NF)

    # Call /bin/date and translate the 'visual date' to
    # epoch timestamp.
    cmd="/bin/date -d " $NF " +%s"
    epoch=((cmd | getline line) > 0 ? line : -1)
    close(cmd)

    if (epoch == -1) {
        print "Date throw an error at : " NR;
        exit 1; 
    }

    # If the lh (last hour) is not set, set it
    # to the current value for the epoch time plus 
    # the chosen offset
    if (!lh) lh = epoch + ho

    # if the current offset less the the old hour processed is
    # greater then the offset you choose: update the offset and 
    # print the separator
    if (epoch - lh > ho) {
        lh = epoch + ho
        print ""
    }
}1

edited May 28, 2021 at 13:33

answered May 27, 2021 at 13:59

DanieleGrassini

2,7996 silver badges18 bronze badges

1

Imagine one timestamp was 2021-05-27T23:59:59.135Z and the next was 2021-05-28T00:00:01.135Z (note the date change). Unless I'm misreading it your code assumes all timestamps are within the same day so when a day boundary is crossed the hour value of the 2nd timestamp being 23 hours less than the hour value of the first timestamp would lead to incorrect output.
– Ed Morton
May 27, 2021 at 18:35
1

@EdMorton you are right as ever. I have, maybe wrongly, assumed that all the timestamps in the file belongs to the same day. As soon as i can i will update my answer keeping in mind about you statements
– DanieleGrassini
May 27, 2021 at 19:47
2

1) It'll be extremely slow (literally orders of magnitude slower than calling gawk with mktime()) since it's spawning a subshell to call date once per line of input. 2) close("/bin/date") is closing the wrong thing, you need to close the whole command which is "/bin/date -d " $NF " +%s" - it's always best to use a variable to avoid that issue, cmd="..."; cmd | getline; close(cmd). 3) If that call to getline failed for some reason the rest of the code would just use the previous success value and youd never know, do epoch=( (cmd | getline line) > 0 ? line : -1 ) or similar instead
– Ed Morton
May 28, 2021 at 12:12
1

IMHO it'll be far too slow for the OPs needs since they mentioned performance requirements. I was actually considering adding a non-gawk answer that doesn't even use mktime() in case THAT was too slow but figured I'd see if the OP says it's too slow first plus you already have such an answer brewing.
– Ed Morton
May 28, 2021 at 12:30
1

Thanks again @EdMorton, edited.
– DanieleGrassini
May 28, 2021 at 13:34

| Show 4 more comments

Stack Exchange Network

Split CSV file with timestamp along breaks for defined period

Example

4 Answers 4

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged
awk
sed
csv
.

Linked

Hot Network Questions

Split CSV file with timestamp along breaks for defined period

Example

4 Answers 4

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged awksedcsv.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
awk
sed
csv
.