Manipulating very large text file and clustering analysis

Question

I'm trying to work with a (very) large 45gb .txt file that cannot be opened using normal text editors.

Data within each row is separated by a spacing, although there are also spaces within each parameter. For example, 1 row looks somewhat like this:

University of Cambridge CB2 1TQ 0001234567 2011-01-25 12345 11.12345 12.12345 13.12345 14.1234 16.2716)

What I would want to is essentially to clean it up, filter some data, sum up some cells/parameters, append some additional data to each row, and perform clustering analysis (probably with python or C# since I'm starting to pick it up).

If you use the `vim` editor, it can open very large files easily. — Kingsley, Nov 07 '18 at 05:10
This data can be parsed easily - so as long as you start from the right. Once 10 fields have been found, the rest is the title. — Kingsley, Nov 07 '18 at 05:28

score 0 · Accepted Answer · answered Nov 07 '18 at 05:18

Well having it in a database sounds like a good start, rather than trying to do all your data munging from a text file. If you know you can filter the data right now then that might make life easier too.

with open("file.txt", "r") as file:
    for line in file:
        # Filter
        # If filter passes, add to batch
        # At a batch size n, insert to db and restart batch

Now your data is in a database and you can execute arbitrary SQL queries against it in a more performant way, get aggregate stats like sums, and append values. You can extract samples from it for your ML ops or use fit generator methods that do queries. I've used the clustering implementations in Python scikit-learn and I enjoyed working with them, but I don't recall there being any out-of-memory implementations. (I can't speak to c# libraries since I haven't used them.)

Python has DB APIs in stdlib like https://docs.python.org/3/library/sqlite3.html

Python has ORM with https://www.sqlalchemy.org/

Python also has public cloud database SDKs like

https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/rds.html

and

https://cloud.google.com/python/getting-started/using-cloud-sql

Manipulating very large text file and clustering analysis

1 Answers1