3

I have a file with part of that as sample like below, which contains a timestamp field:

20161203001211,00
20161203001200,00
20161203001500,102
20161203003224,00
20161203001500,00
20161203004211,00
20161203005659,102
20161203000143,103
20161202001643,100
....

I would like to process this file based on the timestamp to count the occurrences within 15 minute intervals. I know how to do it ever minute also I did within 10 minutes intervals using awk script, but have no idea how I can I make it possible to get the below output in 15 minute intervals:

startTime-endTime             total SUCCESS FAILED    
20161203000000-20161203001500 5     3       2
20161203001500-20161203003000 2     1       1
20161203003000-20161203004500 2     2       0
20161203004500-20161203010000 1     0       1
20161202000000-20161202001500 0     0       0
20161202001500-20161202003000 1     0       1
....

00 indicates as success, any other case indicates failure record.

and yes, it's 24 hours, so for each hour in a day there should be 4 records of intervals print.

8
  • Can be done, but I have little time today, and probably tomorrow. Are you in a hurry with it? Anyway, if no one posted yet I will at latest on Monday. One question: I assume the example input does not correspond with the (example) output? Dec 3, 2016 at 10:50
  • What does 102 mean in 20161203001500,102 ? KasiyaA, can you please add more clarity to this question ? Dec 7, 2016 at 1:31
  • @Serg it shows just a result, I assume as failure record, all except ones with 00 Dec 7, 2016 at 6:01
  • OK. And in your report file, there is start time and end time. How do you get that ? Is input file sorted ? Dec 7, 2016 at 7:07
  • @Serg Actually at first all my input records are in 24 hours format, so for each hour I have relevant record, and I have a script runs every 15 minutes and these ranges (in sample output) I'm getting from $(date ...) & $(date -d "-15 minutes ago" ...) and pass it to an awk command to counts number of success and failure within these ranges and print it line by line, for first script run time it will report "20161203000000-20161203001500 5 3 2", for next 15 minutes it will report this "20161203001500-20161203003000 2 1 1" and so on Dec 7, 2016 at 16:59

1 Answer 1

7
+800

Writing reports on time stamped datafiles; complex requirements


While the original question was a bit complicated, the context of the question made it a quite difficult one. Additional circumstances were (as discussed in chat):

  • The script needed to combine multiple, time stamped files into one report, possibly spread over multiple day-stamped folders (depending on the set time-range).
  • The script needed to be able to select a time range, not only from the time stamps on the file names, but also a sub-range read from the files' lines.
  • Time sections (quarters) with no data should report "zeroed" output
  • The time format in the ouput, both in the report name and the reported lines (per 15 minutes) was needed in a different from the input format.
  • Lines to be processed needed to meet a condition, which the script had to check
  • Relevant data could possibly be on different positions in the line
  • The script needed to be python2
  • The script had to consider a (variable) difference between local time and UTC.
  • Extra options added: option to export to basic csv, optional columns on/off
  • Last but not least: the amounts of data, to be processed were more then huge; thousands of files, hundreds of thousands of lines per file, many GB's, millions of lines. In other words: procedures had to be smart and efficient to be able to process the data within a reasonable amount of time.

Explanation

The end result is too comprehensive to explain in detail, but, for those who are interested, the headlines:

  • All time calculations were done (no wonder) in epoch time
  • Reading the files' lines, the very first was to check the condition per line, to decrease the number of lines to be processed immediately
  • Frome the lines, the time stamp, after converting to epoch, was divided by 900 (seconds, 15 minutes), rounded down (taking int(n)), and subsequently multiplied by 900 again to calculate the 15 minute- section they belonged to
  • Lines were subsequently sorted and grouped by itertools' groupby and results per group were generated with the help of ifilter (python2)
  • Reports were subsequently first created per file, since reports were per 15 minutes. The reported output per file could not be more then tens of lines. No possible issue to temporarily store into memory.
  • Once all relevant files and lines were processed this way, all reports were finally combined into one final report

The script turns out to do the job very well, despite the amount of data. While processing, the processor shows about 70% occupation on my 10+ years old system, running stable. The computer is still very well usable for other tasks.

The script

#!/usr/bin/env python2
import time
import datetime
from itertools import groupby, ifilter
from operator import itemgetter
import sys
import os
import math

"""
folders by day stamp: 20161211 (yyymmdd)
files by full readable (start) time 20161211093512 (yyyymmddhhmmss) + header / tail
records inside files by full start time 20161211093512 (yyyymmddhhmmss)
commands are in UTC, report name and time section inside files: + timeshift
"""

################## settings  ##################

# --- format settings (don't change) ---
readable = "%Y%m%d%H%M%S"
outputformat = "%d-%m-%Y %H:%M"
dateformat = "%Y%m%d"

#---------- time settings ----------
# interval (seconds)
interval = 900
# time shift UTC <> local (hrs)
timeshift = 3.5
# start from (minutes from now in the past)
backintime = 700

# ---- dynamically set values -------
# condition (string/position)
iftrue = ["mies", 2]
# relevant data (timestamp, result)
data = [0, 1]
# datafolder
datafolder = "/home/jacob/Bureaublad/KasIII"

# ----- output columns------
# 0 = timestamp, 1 = total, 2 = SUCCESS, 3 = FAILS
# don't change the order though, distances will mess up
items = [0, 1, 2, 3]
# include simple csv file
csv = True

###############################################

start = sys.argv[1]
end = sys.argv[2]
output_path = sys.argv[3]

timeshift = timeshift*3600

def extraday():
    """
    function to determine what folders possibly contain relevant files
    options: today or *also* yesterday
    """
    current_time = [
        getattr(datetime.datetime.now(), attr) \
        for attr in ['hour', 'minute']]
    minutes = (current_time[0]*60)+current_time[1]                   
    return backintime >= minutes

extraday()

def set_layout(line):
    # take care of a nice output format
    line = [str(s) for s in line]
    dist1 = (24-len(line[0]))*" "
    dist2 = (15-len(line[1]))*" "
    dist3 = (15-len(line[2]))*" "
    distances = [dist1, dist2, dist3, ""]
    displayed = "".join([line[i]+distances[i] for i in items])
    return displayed


    # return line[0]+dist1+line[1]+dist2+line[2]+dist3+line[3]

def convert_toepoch(pattern, stamp):
    """
    function to convert readable format (any) into epoch
    """
    return int(time.mktime(time.strptime(stamp, pattern)))

def convert_toreadable(pattern, stamp, shift=0):
    """
    function to convert epoch into readable (any)
    possibly with a time shift
    """
    return time.strftime(pattern, time.gmtime(stamp+shift))

def getrelevantfiles(backtime):
    """
    get relevant files from todays subfolder, from starttime in the past
    input format of backtime is minutes
    """
    allrelevant = []
    # current time, in epoch, to select files
    currt = int(time.time())
    dirs = [convert_toreadable(dateformat, currt)]
    # if backintime > today's "age", add yesterday
    if extraday():
        dirs.append(convert_toreadable(dateformat, currt-86400))
    print("Reading from: "+str(dirs))
    # get relevant files from folders
    for dr in dirs:
        try:
            relevant = [
                [f, convert_toepoch(readable, f[7:21])]
                for f in os.listdir(os.path.join(datafolder, dr))
                ]
            allrelevant = allrelevant + [
                os.path.join(datafolder, dr, f[0])\
                for f in relevant if f[1] >= currt-(backtime*60)
                ]
        except (IOError, OSError):
            print "Folder not found:", dr
    return allrelevant

def readfile(file):
    """
    create the line list to work with, meeting the iftrue conditions
    select the relevant lines from the file, meeting the iftrue condition
    """
    lines = []
    with open(file) as read:
        for l in read:
            l = l.split(",")
            if l[iftrue[1]].strip() == iftrue[0]:
                lines.append([l[data[0]], l[data[1]]])
    return lines

def timeselect(lines):
    """
    select lines from a list that meet the start/end time
    input is the filtered list of lines, by readfile()
    """
    return [l for l in lines if int(start) <= int(l[0]) < int(end)]

def convert_tosection(stamp):
    """
    convert the timestamp in a line to the section (start) it belongs to
    input = timestamp, output = epoch
    """
    rsection = int(convert_toepoch(readable, stamp)/interval)*interval
    return rsection

reportlist = []

foundfiles = getrelevantfiles(backintime)

if foundfiles:
    # the actual work, first reports per file, add them to reportlist
    for f in foundfiles:
        # create report per file
        # get lines that match condition, match the end/start
        lines = timeselect(readfile(f))
        # get the (time) relevant lines inside the file
        for item in lines:
            # convert stamp to section
            item[0] = convert_tosection(item[0])
        lines.sort(key=lambda x: x[0])
        for item, occurrence in groupby(lines, itemgetter(0)):
            occ = list(occurrence)
            total = len(occ)
            # ifilter is python2 specific (<> filterfalse in 3)
            success = len(list(ifilter(lambda x: x[1].strip() == "00", occ)))
            fails = total-success
            reportlist.append([item, total, success, fails])

    finalreport = []

    # then group the reports per file into one
    reportlist.sort(key=lambda x: x[0])
    for item, occurrence in groupby(reportlist, itemgetter(0)):
        occ = [it[1:] for it in list(occurrence)]
        output = [str(sum(i)) for i in zip(*occ)]
        output.insert(0, item)
        finalreport.append(output)

    # create timeframe to fill up emty sections
    framestart = int(convert_toepoch(readable, start)/interval)*interval
    frameend = int(math.ceil(convert_toepoch(readable, end)/interval))*interval
    timerange = list(range(framestart, frameend, interval))
    currlisted = [r[0] for r in finalreport]
    extra = [item for item in timerange if not item in currlisted]

    # add missing time sections
    for item in extra:
        finalreport.append([item, 0, 0, 0])
    finalreport.sort(key=lambda x: x[0])
    print(str(len(finalreport))+" timesections reported")

    # define output file
    fname1 = convert_toreadable(
        readable,
        convert_toepoch(readable, start),
        timeshift) 
    fname2 = convert_toreadable(
        readable,
        convert_toepoch(readable, end),
        timeshift)
    filename = "report_"+fname1+"_"+fname2
    outputfile = os.path.join(output_path, filename)
    # edit the time stamp into the desired output format, add time shift
    with open(outputfile, "wt") as report:
        report.write(set_layout(["starttime", "total", "SUCCESS", "FAILED"])+"\n")
        for item in finalreport:
            item[0] = convert_toreadable(outputformat, item[0], timeshift)
            report.write(set_layout(item)+"\n")
    if csv:
        with open(outputfile+".csv", "wt") as csv_file:
            csv_file.write(",".join(["starttime", "total", "SUCCESS", "FAILED"])+"\n")
            for item in finalreport:
                csv_file.write(",".join(item)+"\n")

else:
    print("no files to read")

A small sample of the output

starttime               total          SUCCESS        FAILED
12-12-2016 03:30        2029           682            1347
12-12-2016 03:45        2120           732            1388
12-12-2016 04:00        2082           745            1337
12-12-2016 04:15        2072           710            1362
12-12-2016 04:30        2004           700            1304
12-12-2016 04:45        2110           696            1414
12-12-2016 05:00        2148           706            1442
12-12-2016 05:15        2105           704            1401
12-12-2016 05:30        2040           620            1420
12-12-2016 05:45        2030           654            1376
12-12-2016 06:00        2067           692            1375
12-12-2016 06:15        2079           648            1431
12-12-2016 06:30        2030           706            1324
12-12-2016 06:45        2085           713            1372
12-12-2016 07:00        2064           726            1338
12-12-2016 07:15        2113           728            1385
4
  • @KasiyA OK, I will add it probably tonight or tomorrow morning. I had the feeling I still owed you though :) Dec 4, 2016 at 10:16
  • @KasiyA AHA that's a new challenge :). Getting more interesting by the minute :). Reading & processing 5000 files of 20 mb each is ~10GB of data. Reading in all files at once will not work for sure. The output file however will only have a limited number of lines, depending on the time span (how many appr?). What I suggest is to create files per (sub) report first, then write another script to merge (the data of) these files. That way we'd keep the chunks doable. I will do some tests, maybe having multiple jobs run at the same time will speed up the whole task. Dec 5, 2016 at 8:42
  • @KasiyA ^ please let me know the "usual" time span I should think of. Dec 5, 2016 at 8:43
  • Let us continue this discussion in chat. Dec 5, 2016 at 8:52

You must log in to answer this question.

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