parse and clean log files from AWS S3

parses3logs.py

import csv
import os
import re
import dateutil
import pandas as pd
from urlparse import urlparse

log_path = ''
# parsing code: http://ferrouswheel.me/2010/01/python_tparse-fields-in-s3-logs/
log_entries = []
for log in os.listdir(log_path):
    r = csv.reader(open(log_path + log), delimiter=' ', quotechar='"')
    for i in r:
        i[2] = i[2] + ' ' + i[3]  # repair date field
        del i[3]
        log_entries.append(i)
# format: http://docs.aws.amazon.com/AmazonS3/latest/dev/LogFormat.html
columns = ['Bucket_Owner', 'Bucket', 'Time', 'Remote_IP', 'Requester',
           'Request_ID', 'Operation', 'Key', 'Request_URI', 'HTTP_status',
           'Error_Code', 'Bytes_Sent', 'Object_Size', 'Total_Time',
           'Turn_Around_Time', 'Referrer', 'User_Agent', 'Version_Id']
df = pd.DataFrame(log_entries, columns=columns)
df = df.mask(df == '-')
df.Time = df.Time.map(lambda x: x[x.find('[') + 1:x.find(' ')])
df.Time = df.Time.map(lambda x: re.sub(':', ' ', x, 1))
df.Time = df.Time.apply(dateutil.parser.parse)
df['Date'] = df.Time.apply(lambda x: x.strftime('%m-%d-%Y'))
df.Key = df.Key.apply(lambda x: re.sub('index\.html', '', x) if x == x else None)
df.Referrer = df.Referrer.apply(lambda x: urlparse(x).hostname if x == x else None)
df.to_csv('log.csv', index=False)

I updated this for python3. note that the log format has expanded, so this structures the output correctly for old and new log files.

#!/usr/bin/python

## From https://gist.github.com/zmjones/8862947

import csv
import os
import re
import dateutil
import pandas as pd
#from urlparse import urlparse # python2
from urllib.parse import urlparse # python3
from pathlib import Path

log_path = 'logs/'
# parsing code: http://ferrouswheel.me/2010/01/python_tparse-fields-in-s3-logs/
columns = ['Bucket_Owner', 'Bucket', 'Time', 'Remote_IP', 'Requester',
           'Request_ID', 'Operation', 'Key', 'Request_URI', 'HTTP_status',
           'Error_Code', 'Bytes_Sent', 'Object_Size', 'Total_Time',
           'Turn_Around_Time', 'Referrer', 'User_Agent', 'Version_Id']

columns24 = columns + ['Host_Id', 'Signature_Version', 'Cipher_Suite', 'Authentication_Type', 'Host_Header', 'TLS_Version']

log_entries = pd.DataFrame(columns=columns24)

for log_file in Path(log_path).rglob('*'):
    if log_file.suffix != '':
        continue
    #with open(log_path + log, encoding="utf8") as csvfile: -- this was buggy compared to pandas. I  get utf8 errors with this
    try:
        line = pd.read_csv(log_file, delimiter=' ', quotechar='"', header=0, names=columns24)
    except:
        line = pd.read_csv(log_file, delimiter=' ', quotechar='"', header=0, names=columns)
    log_entries = log_entries.append(line)

# format: http://docs.aws.amazon.com/AmazonS3/latest/dev/LogFormat.html
df = pd.DataFrame(log_entries, columns=columns) # doesn't transform anything, just copies and renames.
df = df.mask(df == '-')
df.Time = df.Time.map(lambda x: x[x.find('[') + 1:x.find(' ')])
df.Time = df.Time.map(lambda x: re.sub(':', ' ', x, 1))
# these next lines didn't work for me. accepted malformed dates for now
#df.Time = df.Time.apply(dateutil.parser.parse)
#df['Date'] = df.Time.apply(lambda x: x.strftime('%m-%d-%Y'))
#df.Key = df.Key.apply(lambda x: re.sub('index\.html', '', x) if x == x else None)
df.Referrer = df.Referrer.apply(lambda x: urlparse(x).hostname if x == x else None)
df.to_csv('log.csv', index=False)