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
102
mean in20161203001500,102
? KasiyaA, can you please add more clarity to this question ?$(date ...)
&$(date -d "-15 minutes ago" ...)
and pass it to anawk
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