Parsing a large pseudo csv log file in Python

Question

I have several very large not quite csv log files.

Given the following conditions:

1. value fields have unescaped newlines and commas, almost anything can be in the value field including '='
2. each valid line has an unknown number of valid value fields
3. valid value looks like key=value such that a valid line looks like key1=value1, key2=value2, key3=value3 etc.
4. the start of each valid line should begin with eventId=<some number>,

What is the best way to read a file, split the file into correct lines and then parse each line into correct key value pairs?

I have tried

file_name = 'file.txt'
read_file = open(file_name, 'r').read().split(',\neventId')

This correctly parses the first entry but all other entries starts with =# instead of eventId=#. Is there a way to keep the deliminator and split on the valid newline?

Also, speed is very important.

Example Data:

eventId=123, key=value, key2=value2:
this, will, be, a problem,
maybe?=,
anotherkey=anothervalue,
eventId=1234, key1=value1, key2=value2, key3=value3,
eventId=12345, key1=
msg= {this is not a valid key value pair}, key=value, key21=value=,

Yes the file really is this messy (sometimes) each event here has 3 key value pairs although in reality there is an unknown number of key value pairs in each event.

Answer 1

If the start of each valid line should begin with eventId= is correct, you can groupby those lines and find valid pairs with a regex:

from itertools import groupby
import re
with open("test.txt") as f:
    r = re.compile("\w+=\w+")
    grps = groupby(f, key=lambda x: x.startswith("eventId="))
    d = dict(l.split("=")  for k, v in grps if k
             for l in r.findall(next(v))[1:])
    print(d)
    {'key3': 'value3', 'key2': 'value2', 'key1': 'value1', 'goodkey': 'goodvalue'}

If you want to keep the eventIds:

import re
with open("test.txt") as f:
    r = re.compile("\w+=\w+")
    grps = groupby(f, key=lambda x: x.startswith("eventId="))
    d = list(r.findall(next(v)) for k, v in grps if k)
    print(d)
[['eventId=123', 'goodkey=goodvalue', 'key2=somestuff'], ['eventId=1234', 'key1=value1', 'key2=value2', 'key3=value3']]

Not clear from your description exactly what the output should be, if you want all the valids key=value pairs and if the start of each valid line should begin with eventId= is not accurate:

from itertools import groupby,chain
import re
def parse(fle):
    with open(fle) as f:
        r = re.compile("\w+=\w+")
        grps = groupby(f, key=lambda x: x.startswith("eventId="))
        for k, v in grps:
            if k:
                sub = "".join((list(v)) + list(next(grps)[1]))
                yield from r.findall(sub)

print(list(parse("test.txt")))

Output:

['eventId=123', 'key=value', 'key2=value2', 'anotherkey=anothervalue',   
'eventId=1234', 'key1=value1', 'key2=value2', 'key3=value3', 
'eventId=12345', 'key=value', 'key21=value']

Answer 2

This problem is pretty insane, but here's a solution that seems to work. Always use an existing library to output formatted data, kids.

import re;

in_string = """eventId=123, goodkey=goodvalue, key2=somestuff:
this, will, be, a problem,
maybe?=,
anotherkey=anothervalue, gotit=see,
the problem===s,
eventId=1234, key1=value1, key2=value2, key3=value3,
eventId=12345, key1=
msg= {this is not a valid key value pair}, validkey=validvalue,"""

line_matches = list(re.finditer(r'(,\n)?eventId=\d', in_string))

lines = []
for i in range(len(line_matches)):
    match_start = line_matches[i].start()
    next_match_start = line_matches[i+1].start() if i < len(line_matches)-1 else len(in_string)-1
    line = in_string[match_start:next_match_start].lstrip(',\n')
    lines.append(line)

lineDicts = []
for line in lines:
    d = {}
    pad_line = ', '+line
    matches = list(re.finditer(r', [\w\d]+=', pad_line))
    for i in range(len(matches)):
        match = matches[i]
        key = match.group().lstrip(', ').rstrip('=')
        next_match_start = matches[i+1].start() if i < len(matches)-1 else len(pad_line)
        value = pad_line[match.end():next_match_start]
        d[key] = value
    lineDicts.append(d)

print lineDicts

Outputs [{'eventId': '123', 'key2': 'somestuff:\nthis, will, be, a problem,\nmaybe?=,\nanotherkey=anothervalue', 'goodkey': 'goodvalue', 'gotit': 'see,\nthe problem===s'}, {'eventId': '1234', 'key2': 'value2', 'key1': 'value1', 'key3': 'value3'}, {'eventId': '12345', 'key1': '\nmsg= {this is not a valid key value pair}', 'validkey': 'validvalue'}]

Answer 3

If your values are can really contain anything, there's no unambiguous way of parsing. Any key=value pair could be part of the preceding value. Even a eventID=# pair on a new line could be part of a value from the previous line.

Now, perhaps you can do a "good enough" parse on the data despite the ambiguity, if you assume that values will never contain valid looking key= substrings. If you know the possible keys (or at least, what constraints they have, like being alphanumeric), it will be a lot easier to guess at what is a new key and what is just part of the previous value.

Anyway, if we assume that all alphanumeric strings followed by equals signs are indeed keys, we can do a parse with regular expressions. Unfortunately, there's no easy way to do this line by line, nor is there a good way to capture all the key-value pairs in a single scan. However, it's not too hard to scan once to get the log lines (which may have embedded newlines) and then separately get the key=value, pairs for each one.

with open("my_log_file") as infile:
    text = infile.read()

line_pattern = r'(?S)eventId=\d+,.*?(?:$|(?=\neventId=\d+))'
kv_pattern = r'(?S)(\w+)=(.*?),\s*(?:$|(?=\w+=))'
results = [re.findall(kv_pattern, line) for line in re.findall(line_pattern, text)]

I'm assuming that the file is small enough to fit into memory as a string. It would be quite a bit more obnoxious to solve the problem if the file can't all be handled at once.

If we run this regex matching on your example text, we get:

[[('eventId', '123'), ('key', 'value'), ('key2', 'value2:\nthis, will, be, a problem,\nmaybe?='), ('anotherkey', 'anothervalue')],
 [('eventId', '1234'), ('key1', 'value1'), ('key2', 'value2'), ('key3', 'value3')],
 [('eventId', '12345'), ('key1', '\nmsg= {this is not a valid key value pair}'), ('key', 'value'), ('key21', 'value=')]]

maybe? is not considered a key because of the question mark. msg and the final value are not considered keys because there were no commas separating them from a previous value.

Answer 4

Oh! This is an interesting problem, you'll want to process each line and part of line separately without iterating though the file more than once.

data_dict = {}
file_lines = open('file.txt','r').readlines()
for line in file_lines:
    line_list = line.split(',')
    if len(line_list)>=1:
        if 'eventId' in line_list[0]:
            for item in line_list:
                pair = item.split('=')
                data_dict.update({pair[0]:pair[1]})

That should do it. Enjoy!

If there are spaces in the 'pseudo csv' please change the last line to:

data_dict.update({pair[0].split():pair[1].split()})

In order to remove spaces from the strings for your key and value.

p.s. If this answers your question, please click the check mark on the left to record this as an accepted answer. Thanks!

p.p.s. A set of lines from your actual data would be very helpful in writing something to avoid error cases.