<jupyter_start><jupyter_text>Camelot```Using camelot cli to process pdf files to extract table content to individual csvs and combine to a single csvSee blog post https://adamsramblings.xyzDecember 2020, <NAME>/--/csvs         --/pdfplumber_csvs -- holds csvs that are generated during processing  --/camelot_csvs   -- holds csvs that are generated during processing--/notebooks  -- holds notebooks--/outputs      --/csv      -- holds output csvs  --/sqldb    -- holds output sql dbs--/scripts    -- holds scripts--/srchtml    -- holds source html--/srcpdfs    -- holds source pdfs--/urls       -- holds urls```<jupyter_code># loop each pdf in srcpdfs path to process using camelot
# -p is the pages and here I'm specifying start from page 1 and go to the end
# -o is the output file name. Actually the first part of the file name generated. 
#     there's one per page named something like namefromoption-page-number-table-number.csv 
#     e.g. ufo_report_2009-page-1-table-1.csv)
#     "${filename##*/}" is used to pull just the name of the file, not the full path
# -f is the output format and here I'm specifying csv
# "lattice" is the command mode that is being used to identify the table and cell boundaries. Lattice uses the lines between text 
# $filename is the full path to the file to process
!for filename in ../srcpdfs/*.pdf; do echo $filename; camelot -p 1-end -o ../csvs/camelot_csvs/"${filename##*/}".csv -f csv lattice $filename ; done
import pandas as pd
import numpy as np
import os
import re
from datetime import datetime
startTime = datetime.now()

# These are the headers that will be set as the first row of the combined csv file
headers = ['Date','Time','Town / Village','Area','Occupation (Where Relevant)','Description']

# These files contain repeated table headers on every page
drop = ["ufo_report_2008.pdf","UFOReport2000.pdf","UFOReport1999.pdf","UFOReport1998.pdf"]

# This is the path to where the output csv files are stored and the combined csv file will be created
base_path = '../'

# This is the name of the output csv file
full_file = os.path.join(base_path,"outputs","csv","ufo_actvity_data_camelot.csv")

files = os.listdir(os.path.join(base_path,"csvs","camelot_csvs"))
data = []
for file in files:
    # split the file up in to parts [pdffile,page,page_number,table,1.csv]
    file_parts = file.split('-') 
    # get the year from the filename
    year = re.findall('\d+',file)[0]
    skip_rows = 0
    # if the page is the first page or the file contains repeated headers then we want to drop the headers
    if "page-1-" in file or file_parts[0] in drop: 
        skip_rows = 1
    # read csv file
    df_tmp = pd.read_csv(os.path.join(base_path,"csvs","camelot_csvs",file), skiprows=skip_rows, header=None, names=headers)
    # replace tab, new line
    df_tmp.replace(to_replace=[r"\\t|\\n|\\r", "\t|\n|\r"], value=["",""], regex=True, inplace=True)
    # convert empty strings to NaN for removal of full rows
    df_tmp.replace("", np.nan, inplace=True)
    # remove empty rows
    df_tmp.dropna(axis = 0, how = 'all', inplace = True)
    # set the page number
    df_tmp['Page'] = file_parts[2]
    # set the file name
    df_tmp['File'] = file_parts[0]
    # set the report year
    df_tmp['ReportYear'] = year
    data.append(df_tmp)

df = pd.concat(data, ignore_index=True) # join the data to a single data frame
# find instances of null area and repated lastword in Town / Village e.g. South East LondonLondon
# replace the Area value with the last word from Town / Village
df.loc[(df['Area'].isnull() ) & (df['Town / Village'].str.count(r'([A-Z][a-z]+)([A-Z][a-z]+)+')>0),'Area'] = df['Town / Village'].str.split().str[-1]
# strip the repeated word from Area
df.loc[( (df['Area'].str.count(r'([A-Z][a-z]+)([A-Z][a-z]+)+$')>0) & (df['Town / Village'].str.count(r'([A-Z][a-z]+)([A-Z][a-z]+)+$')>0) ),'Area'] = df['Area'].str.replace(r"(\w[a-z]+)","",1)
# strip the repeated word from Town / Village
df.loc[(df['Town / Village'].str.count(r'([A-Z][a-z]+)([A-Z][a-z]+)+$')>0),'Town / Village'] = df['Town / Village'].str.replace(r"\w[a-z]+$","",1)
# generate output csv file
df.to_csv(full_file, index=False,header=True)
print("Created file ",full_file)
print("File has",len(df)," rows")
# get row counts
df.groupby('File')['File'].count()
print("Time to complete",datetime.now() - startTime)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15252686942602334, 'fraction_numerical': 0.010061742510862108, 'mean_word_length': 3.887150837988827, 'pattern_counts': {'":': 0, '<': 6, '<?xml version=': 0, '>': 10, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '321839', 'license': 'MIT', 'n_tokens_mistral': 1504, 'n_tokens_neox': 1440, 'n_words': 472, 'path': 'notebooks/Using Camelot to process PDFs.ipynb', 'repo_name': 'adamsquire/ufo_files', 'size': '4378'}