1. PDF Analyst
Why convert PDF to CSV or Excel?
Before diving into this task, let’s talk about why we’d even bother with such conversions. PDFs are widely used for sharing information because they’re static and easy to print.
However, when it comes to data analysis, PDFs are not exactly an analyst’s best friend. This is where CSV and Excel files step in. These formats are great for analysis, easy to open in Excel, or import into any data analysis tool. Reading, filtering, sorting, and visualizing data in these formats are a piece of cake. And who wouldn’t want that?
Tools and Libraries
To get this job done, we’ll use a few Python libraries that will help us break the "PDF curse" and transform the document into an awesome CSV (or Excel, if you prefer). These include PyPDF2, PDFPlumber, and pandas. PyPDF2 helps us extract text from PDFs, PDFPlumber takes it to a pro-level, and pandas is for working with the data in a tabular format.
If you haven’t installed these libraries yet, why not do it right now? Enter this command in your terminal:
pip install PyPDF2 PDFPlumber pandas
Done? Let’s get started!
2. Extracting Text from a PDF Document
Extracting Text Using PyPDF2
The first task is to extract the precious text from the PDF. For this, we’ll use the PyPDF2 library. Let’s write a small script that opens a PDF file and extracts text from each page of the document.
import PyPDF2
# Open the file
with open('sample.pdf', 'rb') as file:
reader = PyPDF2.PdfReader(file)
text = ""
# Loop through all pages and extract text
for page in reader.pages:
text += page.extract_text()
print(text) # Print the extracted text
That’s it! We read the file and extracted its text. But not all text is created equal; sometimes, you need to clean up extra characters or split it into lines.
Extracting Text Using PDFPlumber
PDFPlumber does a better job when it comes to PDFs with tables and complex structures. It lets you extract text and work with tables as well.
import pdfplumber
# Open the PDF file
with pdfplumber.open("sample_with_table.pdf") as pdf:
text = ""
for page in pdf.pages:
text += page.extract_text() + "\n"
print(text)
This code similarly extracts text from all pages, but compared to PyPDF2, PDFPlumber handles layout and tables much better.
Extracting Tables from PDF Using PDFPlumber
If the PDF contains tables, PDFPlumber lets you extract them as lists, which makes it easier to convert them into CSV or Excel.
import pdfplumber
import pandas as pd
# Open the PDF and extract tables
with pdfplumber.open("sample_with_table.pdf") as pdf:
all_tables = []
for page in pdf.pages:
table = page.extract_table()
if table:
all_tables.extend(table)
# Convert data to DataFrame
df = pd.DataFrame(all_tables[1:], columns=all_tables[0]) # First row as headers
print(df)
This code creates a table from the PDF data and saves it into a DataFrame, making it easy to save the table as a CSV or Excel file.
3. Saving Data
Converting Text into DataFrame
Now that we have the text, let’s pretend it’s packed into lines, each of which will be a row in our future table. Our task is to convert it into a DataFrame using pandas and then save it as a CSV file.
import pandas as pd
# Let’s say each line of text corresponds to a row of data
data = text.split('\n')
data = [row.split(',') for row in data if row.strip() != ''] # Split rows by commas
# Create a DataFrame
df = pd.DataFrame(data[1:], columns=data[0]) # Use the first row as headers
# Save DataFrame as CSV
df.to_csv('output.csv', index=False)
Here we simply split all text into rows, then further into individual elements, and created a DataFrame, setting the first row as column headers. After that, we saved it as a CSV file. Woohoo! We just did something that used to require hours with a pen and calculator.
Converting Data to CSV
After extracting text or tables from a PDF, you can save the data as a CSV using the pandas library.
Saving Data to CSV
# Save data to CSV
df.to_csv("output.csv", index=False)
print("Data successfully saved to output.csv")
This code saves the DataFrame data extracted from the PDF to a file named output.csv, which can be opened in any spreadsheet editor or uploaded to an analytics platform.
Converting to Excel
What if you want nothing less than Excel? No problem! Pandas has you covered. Just replace the last line in the previous example with:
df.to_excel('output.xlsx', index=False)
This will save your DataFrame as an Excel file, and you can scroll through it, apply filters, and use Excel pivot tables — all the things that make you the Analyst of the Year at the company party.
Challenges and Gotchas
As always, there are challenges along the way. Extracting text from PDFs can sometimes feel like trying to explain the cloud to your grandma. Some PDFs may have complex structures like tables, charts, and images that are not easy to convert into text, especially structured text. In such cases, you may need extra text processing, regex, or even specialized OCR libraries to extract data from images embedded in the PDF.
Additionally, not all PDFs are automation-friendly. Some of them are encrypted or password-protected. PyPDF2 allows you to handle passwords, but encryption can be trickier.
GO TO FULL VERSION