Script to turn a bunch of ping logs into a CSV.

make_ping_csv.py

#!/usr/bin/env python

import os, sys, glob, re, time, datetime, pytz

rtt_regex = re.compile("rtt min/avg/max/mdev = ([0-9.]+)/([0-9.]+)/([0-9.]+)/([0-9.]+) ms")
loss_regex = re.compile("[0-9]+ packets transmitted, [0-9]+ received, ([0-9.]+)% packet loss, time [0-9]+ms")

ping_line_regex = re.compile(
    "([0-9]+) bytes from .*? \(([0-9.]+)\): " +
    "icmp_req=[0-9]+ ttl=[0-9]+ time=([0-9.]+) ms")

LOSS_KEY = 'l'
RTT_KEY = 'r'
MIN = 'm'
MAX = 'M'
AVG = 'a'
STD_DEV = 'd'

PACIFIC_TIME = pytz.timezone('America/Los_Angeles')

def localize_time(date_string, fmt):
    return PACIFIC_TIME.localize(
        datetime.datetime(*(time.strptime(date_string, fmt)[0:6])),
        is_dst=True)

START_TIME = localize_time(
    "2013-04-17-00:00:00-PDT", "%Y-%m-%d-%H:%M:%S-%Z")
END_TIME = localize_time(
    "2013-08-17-23:59:59-PDT", "%Y-%m-%d-%H:%M:%S-%Z")

def parse_file(filename):
    datum = []

    measurement_time = localize_time(filename, '%Y-%m-%d-%H:%M:%S-%Z.txt')

    if measurement_time < START_TIME or measurement_time > END_TIME:
        return None

    # Record measurement in a compressed format that will make it faster to
    # parse
    datum.append(measurement_time.strftime('%m%d%H%M'))

    with open(filename, 'r') as fp:
        lines = map(lambda x: x.strip(), fp.readlines())

    packet_loss_percent = None

    for line in lines:
        rtt_match = rtt_regex.match(line)

        if rtt_match is not None:
            # RTT measurements
            datum.extend([float(rtt_match.group(x)) for x in xrange(1,5)])

        loss_match = loss_regex.match(line)

        # packet loss
        if loss_match is not None:
            packet_loss_percent = float(loss_match.group(1))

    if packet_loss_percent is None:
        return None
    else:
        if packet_loss_percent == 100:
            assert len(datum) == 1
            datum.extend([0,0,0,0])

        datum.append(packet_loss_percent)

    assert len(datum) == 6
    return datum

data = []

ping_logs = sorted(glob.glob('*.txt'))

with open('ping_logs.csv', 'w') as fp:
    print >>fp, 'date,min_rtt,avg_rtt,max_rtt,mdev_rtt,packet_loss_percent'
    for filename in ping_logs:
        parsed_datum = parse_file(filename)

        if parsed_datum is not None:
            print >>fp, ','.join(map(str, parsed_datum))