0
\$\begingroup\$

i am looking for an efficient way to read and append texts of .txt files to a dataframe. I currently have 10 folders with 100k documents each. What i specifically need to do is:

  • getting the names of the files inside one folder (filenames contain important information like a unique ID for each company that issue the document and the date when the document was published, like this: "CIK_000000_DATE_14_12_2022.txt")
  • extract from the name of the files the unique ID (CIK) and the date
  • take the text related to the file

append this 3 pieces of informations in a dataset so that the dataset appear like:

CIK date text serial
000000 11/12/2012 some text here... 1
000001 14/11/2019 some other text here... 2

the folders are individually made like this:

TXT_01
|_ CIK__000000__DATE__14-12-2012__serial__0.txt
|_ CIK__000001__DATE__12-11-2019__serial__1.txt
|_ CIK__000001__DATE__11-12-2014__serial__2.txt
|_ CIK__000175__DATE__11-04-2011__serial__3.txt
|_ ...
TXT_02
|_ CIK__000135__DATE__11-04-2001__serial__100.txt
|_ CIK__000115__DATE__11-04-2001__serial__101.txt
|_ CIK__000145__DATE__11-04-2001__serial__103.txt
|_ CIK__000155__DATE__11-04-2001__serial__104.txt
|_ ...
...

They get the job done, but the time they take to finish a test folder with 4000 documents is way too much considering that i need to do this for 100k files folders.

i can provide more informations if needed. I'm open to any advice, even learning faster programming languages in order to get this done.

thank you all

here the code i'm currently executing (consider that it comes from a collab notebook)

folders = []
names = os.listdir()
for i in names:
    if i.startswith('TXT'):
        folders.append(i)

def get_file_names(folder):
  return os.listdir(folder)

def get_cik_date(file):
  cik = re.findall(r"CIK__([0-9]*)", file)[0].zfill(10)
  date = re.findall(r"date__(([0-9]*)-([0-9]*)-([0-9]*))", file)[0][0]
  serial = re.findall(r"serial__([0-9]*)", file)[0]
  return (cik, date, serial)

def get_text(file):
  get_text.counter +=1
  print(get_text.counter)
  with open(file) as f:
    lines = f.readlines()
  return lines
get_text.counter = 0

file_names = list(map(get_file_names,folders))
folder_names = list(zip(folders,file_names))
cik_date_folder = [[get_cik_date(i) for i in j] for k,j in folder_names]
texts = [[get_text(f"{k}/{i}") for i in j] for k,j in folder_names]
test = [dict(zip(cik_date_folder[i], texts[i])) for i,n in enumerate(folders)]
df = pd.DataFrame.from_dict(test)
df = df.ffill().bfill().head(1).T
df_reset = df.reset_index()
df_reset = df_reset.rename({"index": "cik_date", 0:"text"}, axis = 1)
df_reset[['cik','dates', 'serial']] = pd.DataFrame(df_reset['cik_date'].tolist(),index=df_reset.index)
df_complete = df_reset.drop("cik_date", axis = 1)
def concat_list(text_as_list):
    return " ".join(text_as_list)
df_complete["text"] = df_complete["text"].apply(concat_list)

```
\$\endgroup\$
1
  • \$\begingroup\$ i fixed the data in the table, hope it is now comprehensible. I'm sorry, i'm a bit nervous, it is for my master thesis \$\endgroup\$ Commented Dec 14, 2022 at 16:45

1 Answer 1

1
\$\begingroup\$

I created a few functions for quickly setting up a "test environment" of N folders each with M files:

import random
import re
import os
from pathlib import Path
from string import ascii_letters
import itertools
from time import time

import pandas as pd

BASE = "C:\\some\\path\\"
DATE_POOL = pd.date_range(start="01-01-2000", end="01-01-2022")
FILE_LEN = 64

def create_filename(serial: int):
    identifier = str(random.randint(0, 999999)).zfill(6)
    stamp = random.choice(DATE_POOL).strftime("%m_%d_%Y")
    return f"CIK__{identifier}__DATE__{stamp}__serial__{serial}.txt" 

def create_file_setup(num_folders: int, num_files: int):
    serial = 0
    for i in range(num_folders):
        subpath = os.path.join(BASE, f"TXT_{i}")
        os.mkdir(subpath)
        
        for j in range(num_files):
            txt_file = os.path.join(subpath, create_filename(serial))
            serial += 1
            content = ''.join(random.choices(ascii_letters + ' ', k=FILE_LEN))
            Path(txt_file).write_text(content)

So by running create_file_setup(10, 100_000) I create 10 folders each with 100k files, each following the naming scheme you described (or, at least, I deduced as well as I could). I don't know how large your files are, but I entered 64 characters into each. Maybe this can vary too, but you don't specify.

Now, just cleaning up your code slightly gives me:

def get_cik_date(filename: str):
    _, identifier, _, date, _, serial = filename.split("__")
    serial, _ = serial.split('.')
    
    return identifier, date, serial


def build_dataframe():
    folders = [os.path.join(BASE, p) for p in os.listdir(BASE)]
    
    file_names = list(map(os.listdir, folders))
    folder_names = list(zip(folders, file_names))
    cik_date_folder = [[get_cik_date(i) for i in j] for _, j in folder_names]
    texts = [[Path(f"{k}/{i}").read_text() for i in j] for k, j in folder_names]

    df = pd.DataFrame(itertools.chain(*cik_date_folder), columns=["id", "date", "serial"])
    df["text"] = list(itertools.chain(*texts))
    
    return df

If I now run df = build_dataframe() on my machine, the wall clock time is around 40 minutes. If you just need to do this processing once, this is probably much faster than spending time on learning to program in another language or spending time on optimizing your code. Just run it, do something else in the meantime, and come back to your freshly processed data.

\$\endgroup\$
1
  • \$\begingroup\$ thank you very much, the files have a dimension that ranges from few kiloBytes (0.5 kiloBytes in a sample) to 50.000 kiloBytes. The average dimension is 300 kB each text file. \$\endgroup\$ Commented Dec 15, 2022 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.