I'm looking for some advice on a data wrangling problem I'm trying to solve. I've spent a week solid taking different approaches and nothing seems to be quite perfect. Just FYI, this is my first big (for me anyway) data science project, so I'm really in need of some wisdom on the best way to approach it.

Essentially I have a set (200+) of docx files that are semi-structured. By semi-structured I mean the information I want is organized into tables (it's a form, with different tables which contain different info to fill out), but unfortunately these tables are not consistently formatted. Sometimes after people enter data into them they accidentally hit backspace to stick the tables together. Or sometimes they accidentally break the tables apart, for example.

My first attempt used python-docx to extract the data using document.tables[0] etc. I could then pull this into a big python dictionary for each document. It was quite neat but hit a snag - the table formatting problem above.

I then used python-docx again and tried to use the headings of each table as a marker (picked them out using regex) for when a sub data set should begin or end, and iterated across all text in the document. This sort of works, and is more flexible, but picks up a lot of text from outside of the table which makes it difficult to manage and clean.

Anyway, I'm interested in how an experienced data scientist would approach the problem.

The end goal is to extract the data from one of these documents into an SQL database.

If you're interested in the problem, let me know and I can send you the template documents I'm working with and some samples. If it's helpful, I can also post the code I've written so far (haven't done so because it's long).

  • $\begingroup$ For what is's worth, I would do the same thing as you: extract the relevant information based on format makers. It's important to avoid corrupted data in the final dataset so it's worth spending time getting it right... But the ideal way is to avoid MS Word documents for collecting data in the first place ;) $\endgroup$ – Erwan Jun 29 '19 at 14:59
  • $\begingroup$ That's good to know. Believe me I'd much rather not! Trying to find a work around by converting them to pdfs and then using tabula-py, or an equivalent... but I can't get pandas to bl**dy install! $\endgroup$ – mess1n Jul 2 '19 at 15:12
  • $\begingroup$ I have had to extract complex information from different document types a lot before. So much so I even have a company that uses AI to automate this process. My advice would be to convert the word document to either PDFs or Images. Once converted you can use well-documented solutions such as OCR or PDF libraries. In any case you will likely always rely on some form of pattern matching and that is never 100% reliable. Post a question on pandas installation and notify me. I am happy to help with that as well. All the best with your problem $\endgroup$ – IsakBosman Jan 15 '20 at 21:31

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)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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