files to one json file

Question

I'm a bit lost to create a json from few files. I have several files like named : CLT.txt, LYO.txt .... Inside of us I have date + value : dd/mm/yyyy hh:mm xxxxx

I add new line on each file every 5 minutes

examples : CLT.txt

01/01/2020 00:00 45
01/01/2020 00:05 457
01/01/2020 00:10 458
01/01/2020 00:15 402
01/01/2020 00:20 585
...
02/01/2020 00:00 57
02/01/2020 00:05 86
02/01/2020 00:10 45
02/01/2020 00:15 402
02/01/2020 00:20 104
...

LYO.txt

01/01/2020 00:00 70
01/01/2020 00:05 221
01/01/2020 00:10 315
01/01/2020 00:15 57
01/01/2020 00:20 420
...
02/01/2020 00:00 50
02/01/2020 00:05 92
02/01/2020 00:10 32
02/01/2020 00:15 125
02/01/2020 00:20 10
...

I have around 15 files like that.

So in the end I would like to get only one json file created each 5 minutes with this format :

{
  "CLT": {
    "01/01/2020": {
      "00:00": 45,
      "00:05": 457,
      "00:10": 458,
      "00:15": 402,
      "00:20": 585
...
    },
    "02/01/2020": {
      "00:00": 57,
      "00:05": 86,
      "00:10": 45,
      "00:15": 402,
      "00:20": 104
...
    }
  },
  "LYO": {
    "01/01/2020": {
      "00:00": 70,
      "00:05": 221,
      "00:10": 315,
      "00:15": 57,
      "00:20": 420
...
    },
    "02/01/2020": {
      "00:00": 50,
      "00:05": 92,
      "00:10": 32,
      "00:15": 125,
      "00:20": 10
...
    }
  }
}

if you have a simple idea, I'm interested. All processes are done on Ubuntu LTS 19 machine.

Thanks

Answer 1

Don't use "bash-scripting" for this. Pick a language that has native support for nested structures. Once that's available, it becomes easy to load each file into a dict that's shaped exactly like what you want, and then dump the whole thing at once as JSON.

For example:

#!/usr/bin/env python3
import glob
import json

data = {}
for txtfile in glob.glob("???.txt"):
    code = txtfile.split("/")[-1].split(".")[0]
    data[code] = {}
    with open(txtfile, "r") as fh:
        for line in fh:
            date, time, count = line.strip().split()
            data[code].setdefault(date, {})
            data[code][date][time] = int(count)

print(json.dumps(data))

Alternatively:

#!/usr/bin/env ruby
require 'json'

data = {}
Dir["???.txt"].each do |txtfile|
    code = File.basename(txtfile, ".txt")
    data[code] = {}
    File.open(txtfile, "r").each do |line|
        date, time, count = line.strip.split
        data[code][date] ||= {}
        data[code][date][time] = count.to_i
    end
end

puts JSON.generate(data)

Alternatively:

#!/usr/bin/env perl
use File::Basename;
use JSON;

my $data = {};
for my $txtfile (glob("???.txt")) {
    my ($code) = basename($txtfile, ".txt");
    if (open(my $fh, "<", $txtfile)) {
        while (my $line = <$fh>) {
            my ($date, $time, $count) = ($line =~ /^(\S+) (\S+) (\S+)/);
            $data->{$code}->{$date}->{$time} = int $count;
        }
        close($fh);
    } else {
        die "cannot open $txtfile: $!";
    }
}

print JSON->new->encode($data);

Answer 2

awk is a good tool for this purpose. I am not an expert of it myself, but here is what I came up with:

#!/bin/sh -e

while true; do
    echo "{" > new.json
    # put other file names here
    for tla in CLT LYO; do
        [ "$tla" = CLT ] || printf ",\n" >> new.json
        printf "\t\"%s\": {" "$tla" >> new.json
        awk '
BEGIN {
    date="other"
}
{
    curd=$1
    if(curd != date) {
        if(date != "other") {
            printf "\n\t\t},"
        }
        printf "\n\t\t\"%s\": {\n", curd
        date = curd
    } else {
        printf ",\n"
    }
    printf "\t\t\t\"%s\": \"%s\"", $2, $3
}
' "$tla.txt" >> new.json
        printf "\n\t\t}\n\t}" >> new.json
    done
    printf "\n}\n" >> new.json
    mv -f new.json output.json
    # repeat approximately every 5 minutes
    sleep 600
done

Of course, a loop with sleep 600 only makes sense if you call that script interactively. If not, just remove the outer loop and sleep and call the script from cron (if possible, this variant is strongly recommended). If you want, you can also remove the "redirction" part from the script and call the script redirecting all its output (it really depends on the context in which you are using this).