Python - Parse text file with no delimiter and dynamic width values

Question

My goal is to parse a text file in Python that has no headers therefore no columns names and no delimiters. A sample of the original file looks as follows:

Apr 14, 2021 12:40:00 AM CEST INFO   [purge.PurgeManager run] PURGE: Atom purge all data beginning (1 threads)
Apr 14, 2021 1:40:00 AM CEST INFO    [purge.PurgeManager run] PURGE: Atom purge all data beginning (1 threads)
Apr 14, 2021 2:40:00 AM CEST INFO    [purge.PurgeManager run] PURGE: Atom purge all data beginning (1 threads)

I tried to import the file into an Excel file but since it has no delimiter nor fixed-width, each value of the row is wrapped within one cell (cell A).

Now, since the file is not fixed width or delimited, how can I extract the date from within each row (which is dynamic in width as you can see - row 1 length Apr 14, 2021 12:40:00 AM CEST INFO > row 2 length Apr 14, 2021 1:40:00 AM CEST INFO)? I have no interest in manipulating other columns' values, except for this.

I've tried using panda library with both read_csv() and read_fwf() and:

using read_csv() it returns a data frame with two columns: column[0] - the index and column[1] - the value (date and the rest) wrapped into one column cell;
using read_fwf(): cannot quite use it since the width of the date is dynamic.

Is there any way of achieving this using Python? Thanks

user_10035985 · Answer

You can read the file line by line and use str.split() to parse it:

import dateutil
import pandas as pd

data = []
with open("your_file.txt", "r") as f_in:
    for line in map(str.strip, f_in):
        if not line:
            continue
        line = line.split(maxsplit=6)
        date = " ".join(line[:6])
        status = line[-1].split(maxsplit=1)[0]
        rest = line[-1].split(maxsplit=1)[-1]
        data.append({"date": date, "status": status, "rest": rest})


tzmapping = {
    "CET": dateutil.tz.gettz("Europe/Berlin"),
    "CEST": dateutil.tz.gettz("Europe/Berlin"),
}

df = pd.DataFrame(data)
df["date"] = df["date"].apply(dateutil.parser.parse, tzinfos=tzmapping)
print(df)

Prints:

                       date status                                               rest
0 2021-04-14 00:40:00+02:00   INFO  [purge.PurgeManager run] PURGE: Atom purge all...
1 2021-04-14 01:40:00+02:00   INFO  [purge.PurgeManager run] PURGE: Atom purge all...
2 2021-04-14 02:40:00+02:00   INFO  [purge.PurgeManager run] PURGE: Atom purge all...

user_2497722 · Answer

from datetime import datetime
date = []
with open("file.txt") as f:
    lines = f.readlines()
    for line in lines:
        date.append(datetime.strptime(line[:24].strip(), '%b %d, %Y %H:%M:%S %p'))
df = pd.DataFrame({"date":date})
df

Python - Parse text file with no delimiter and dynamic width values

2 Answers