Data wrangling for a big set of docx files advice!

This question has been sitting here for quite a while but I will give answer as I faced a very similar problem some weeks ago.

I had multiple different files (.docx, .pdf) sitting in different folders. To make matters more complicated the .docx files were falling into three groups: SS.docx, ss.docx and plain .docx. My information was in the plain .docx in one table in each document.

I went the hard way and wrote a Python extraction algorithm which I will walk you through now:

import os
import pandas as pd
import docx
from docx.api import Document

#fixed variable
location = 'C:\\Data_analysis\\N_TRACKING'

I then added a function which iterates through the complete file tree starting from location and stored all the files names and their paths in the list targets_in_dir.

#the function 

def find_docx(location):
    all_files = []    # Will contain found files

    # Walk top-down through directory tree
    for (root, dirs, files) in os.walk(location, topdown=True):
   
        for f in files:
            f = f.lower()              # Make conditional case insensitive
            if f.endswith('.pdf'):
                continue               # Skipping pdf
            if f.endswith('ss.docx'):
                continue              # Skipping ss.docx or SS.docx
            if f.endswith('.docx'):
                # Desired docx (undesired docx has been filtered out by previous conditional)
                all_files.append(os.path.join(root, f))
                
    return all_files

the endswith function was very helpful here as it compares the end of string - here my list elements - with the string in the brackets. Essentially, I skip through everything which I do not want and only add what I want to all_files which is returned by the function. I apply the function to the location and get my results stored in targets_in_dir.

#main
targets_in_dir = find_docx(location) 

Then I feed it in the extraction loop

error_log =[]
empty =[]
processed_files =[]


final_df= pd.DataFrame(empty)
for idx, c in enumerate(targets_in_dir):
    try:
        document = Document(c)
        table = document.tables[6]
        processed_files.append(c)
    except: 
        error_log.append(c)
        
    data = []
                
    keys = None
    for i, row in enumerate(table.rows):
        text = (cell.text for cell in row.cells)

        if i == 0:
            keys = tuple(text)
            continue
        row_data = dict(zip(keys, text))
        data.append(row_data)
                 
    df = pd.DataFrame(data)
    df['report'] = str(c)
    final_df = final_df.append(df)

I use the python-docx library here which directly allows me to select table as an object. Strangely I had to pip install python-docx and was then able to import it only as docx Iterating through that many files often fails due to some kind of document error. That's what the try: except: loop is for

errors are stored in error_log. The files the algorithm went through in processed_files. final_df is an empty dataframe to which I will append my results.

Please note I specify the table I want (number 6) from all these documents in this line:

 table = document.tables[6]

the code then iterates through the rows of the table using the first row as keys for the dictionary and mapping the content on it. Then I add tracking information (always know where thy data comes from!) through the line

 df['report'] = str(c)

and dataframe the result and adding it to my empty df from the beginning. And then you can start cleaning the data as humans tend to be messy!

The big problem with docx files is that they have a ton of content related to formatting that most people find irreverent when scraping docx files. Hence, one approach is to convert the docx files to something more friendly like json that excludes all the irrelevant bits, and then use existing frameworks like JSON schema for pattern matching and jmespath for data extraction. There is a python package called simplify-docx for converting Docx files to JSON and a toy website where you can try it out. (Disclaimer: I'm the author of both)