%-------------Developer's Manual (so someone can pick up where you left off, easily)
\chapter{Developer's Manual}

The aim of this section is to frame it in a manner such that it may be used as a manual to redevelop the project. We also provide links to external references and points of contact.

We began by reading a tutorial drawn up by Saurabh Chakrabarty \cite{dataset_tutorial}, who had undertaken parts of this project earlier. The instructions therein provided us with an initial idea of what could be expected to be achieved over the course of this project.

The UCSF library website hosting 14 million tobacco documents was the data source. The site contained a list with definitions for the various topics and keywords one can use to navigate through these documents. The definitions proved to be very helpful in understanding the data structure.

\section{Metadata}
\label{section:meta}

The IDL dataset contains 76,014 types of documents.
Table ~\ref{table:frequent_document_types} lists the top five document types with the highest number of documents.

\begin{table}[H]
    \centering
    \caption{Document types: Frequent}
    \label{table:frequent_document_types}
    \begin{tabular}{| c | c | c |}
        \hline
        Document Type & Number of Documents\\
        \hline
        Letter & 1954308 \\
        \hline
        Report & 934164 \\
        \hline
        Memo & 795282 \\
        \hline
        Note  & 660035  \\
        \hline
        Email & 587367  \\
        \hline
    \end{tabular}
\end{table}

Table ~\ref{table:infrequent_document_types} lists some document types with a moderate number of documents.
These are the types of documents that occupy a chunk of the tobacco dataset but are individually lesser in number as compared to the types of documents in Table \ref{table:frequent_document_types}.

\begin{table}[H]
    \centering
    \caption{Document types: Moderate}
    \label{table:infrequent_document_types}
    \begin{tabular}{| c | c | c |}
        \hline
        Document Type & Number of Documents\\
        \hline
        proposal & 25203  \\
        \hline
       personnel information & 12449 \\
        \hline
        collage & 7768\\
        \hline
        patent application & 5988\\
        \hline
    \end{tabular}
\end{table}

Table ~\ref{table:sparse_document_types} lists some document types with  a smaller number of documents.
These are the types of documents that appear infrequently in the tobacco dataset, so the total number of their instances is much less when compared with the types of documents in Table \ref{table:frequent_document_types}.

\begin{table}[H]
    \centering
    \caption{Document types: Sparse}
    \label{table:sparse_document_types}
    \begin{tabular}{| c | c | c |}
        \hline
        Document Type & Number of Documents\\
        \hline
        microfilm & 1146 \\
        \hline
        application & 774 \\
        \hline
        flow chart & 542 \\
        \hline
        survey & 310\\
        \hline
        legislation & 207\\
        \hline
        telex message & 17 \\
        \hline
    \end{tabular}
\end{table}

Table ~\ref{table:combined_document_types} lists some document types that are actually combinations of discrete document types.
By inspecting some of these documents, it was found that these generally contain text that can be categorized as belonging to two or more document types.
For example, a single PDF of 2 pages where the first page contains a letter and the second page contains an article, can be found to be stored under the document type `letter; article.'
Also, these files are mostly longer, when compared to other tobacco documents which are classified as of a certain type.
The number of documents belonging to a certain combination of discrete file types can vary between moderate to very low.

\begin{table}[H]
    \centering
    \caption{Document types: Combined}
    \label{table:combined_document_types}
    \begin{tabular}{| c | c | c |}
        \hline
        Document Type & Number of Documents\\
        \hline
        bibliography; chart; graph; map; report, scientific & 3766 \\
        \hline
        consumer response; letter; letter, consumer & 2545 \\
        \hline
        budget;  budget review;  chart;  graph;  map;  memo;  table & 365 \\
        \hline
        brand plan; chart; graph; map; memo; promotional material; table & 323  \\
        \hline
        handwritten; report; report, market research & 80\\
        \hline
        email; revision; speech & 77\\
        \hline
        advertisement; agenda; brand review; presentation; &  \\
        promotional material; speech &  2\\
        \hline
    \end{tabular}
\end{table}

Table ~\ref{table:rare_document_types} lists examples of a few discrete document types which are extremely rare in the tobacco dataset, with occurrence counts like 1 or 2.

\begin{table}[H]
    \centering
    \caption{Document types: Rare}
    \label{table:rare_document_types}
    \begin{tabular}{| c | c | c |}
        \hline
        Document Type & Number of Documents\\
        \hline
        study proposal & 1 \\
        \hline
        submission, industry commission inquiry & 1 \\
        \hline
       response statement & 1 \\
        \hline
        disclosure of invention & 1  \\
        \hline
        19641014 & 1  \\
        \hline
    \end{tabular}
\end{table}

Table ~\ref{table:metadata_field_desc} lists the metadata fields along with their descriptions.

\begin{longtable}[c]{| c | c | p{6cm} |}
    \caption{Metadata field descriptions\label{long}}\\
    \hline
    \multicolumn{3}{| c |}{Begin of Table}\\
    \hline
    ID & Code & Description\\
    \hline
    \endfirsthead
    
    \hline
    \multicolumn{3}{|c|}{Continuation of Table \ref{long}}\\
    \hline
    ID & Code & Description\\
    \hline
    \endhead
    \hline
    \endfoot
    \hline
    \multicolumn{3}{| c |}{End of Table}\\
    \hline\hline
    \endlastfoot
0  &    IGNORE            &            IGNORE                                 \\\hline
1  &    tid               &            Legacy (LTDL2) Tobacco Id              \\\hline
2  &    cn                &           Collection                              \\\hline
3  &    ti                &             Title                                 \\\hline
4  &    dd                &           Document Date                           \\\hline
5  &    au                &           Author                                  \\\hline
6  &    recommend         &     Recommend                                     \\\hline
7  &    auo               &         Organization Author                       \\\hline
8  &    aup               &         Person Author                             \\\hline
10 &  refdoc              &     Referenced Document                           \\\hline
11 &  at                  &          Attending                                \\\hline
12 &  ato                 &        Organization Attending                     \\\hline
13 &  atp                 &        Person Attending                           \\\hline
14 &  brd                 &        Brands                                     \\\hline
15 &  bnalias             &     Alternate Bates Range                         \\\hline
16 &  mbn                 &      Bates Mater                                  \\\hline
17 &  othernum            &       Other Bates Range                           \\\hline
18 &  bn                  &         Bates Number                              \\\hline
19 &  cc                  &          Copied                                   \\\hline
20 &  cco                 &        Organization Copied                        \\\hline
21 &  ddi                 &         Date Added Industry Site                  \\\hline
24 &  case                &       Case                                        \\\hline
25 &  desc                &       Description                                 \\\hline
27 &  ddu                 &       Date Added UCSF                             \\\hline
28 &  ddprod              &           Date Produced                           \\\hline
29 &  dt                  &          Document Type                            \\\hline
32 &  availability        &             availablility                         \\\hline
33 &  availabilitystatus  &             availablilitystatus                   \\\hline
35 &  fn                  &          File Number                              \\\hline
36 &  grantnum            &         Grant Number                              \\\hline
38 &  men                 &      Mentioned                                    \\\hline
39 &  meno                &           Organization Mentioned                  \\\hline
40 &  menp                &           Person Mentioned                        \\\hline
45 &  pgdisp              &            Page Count Display                     \\\hline
46 &  attach              &            Attached Artifacts                     \\\hline
48 &  box                 &              Box Number                           \\\hline
49 &  rc                  &                 Recipient                         \\\hline
51 &  rco                 &                Organization Recipient             \\\hline
52 &  rcp                 &                Person Recipient                   \\\hline
53 &  redact              &            Redacted                               \\\hline
54 &  rnm                 &              Minnesota Request Number             \\\hline
55 &  rno                 &               Other Request Number                \\\hline
56 &  reqno               &            Primary Request Number                 \\\hline
57 &  area                &              Area                                 \\\hline
58 &  speccoll            &            Special Collection\\\hline
59 &  ddship              &           Ship Date                               \\\hline
60 &  mm                  &             Multimedia                            \\\hline
61 &  st                  &                 Status                            \\\hline
63 &  topic               &              Topic                                \\\hline
64 &  food                &             Food                                  \\\hline
65 &  pgmap               &                       Page Map                    \\\hline
66 &  ccp                 &               Person Copied                       \\\hline
67 &  kw                  &               Keyword
\\\hline
68 &  access              &            Availability
\\\hline
69 &  rt                  &                  Run Time                         \\\hline
70 &  genre               &            Genre                                  \\\hline
76 &  per                 &               comp:persons                        \\\hline
78 &  org                 &               comp:org                            \\\hline
83 &  mbn{\_}begin          &                      Master Bates begin           \\\hline
85 &  mbn{\_}end           &                  Master Bates End                 \\\hline
87 &  w                   &                Witness Name                       \\\hline
89 &  journal             &           Journal Citation                        \\\hline
90 &  folder              &                 Folder                            \\\hline
91 &  series              &                 Series                            \\\hline
92 &  rights              &                  Rights                           \\\hline
93 &  alias{\_}begin         &           Alias Begin                             \\\hline
94 &  alias{\_}end           &             Alias End                             \\\hline
95 &  bns                 &                   Numeric start Bates             \\\hline
96 &  bne                 &                  Numeric end Bates                \\\hline
97 &  bnp                 &                  Bates Number prefix              \\\hline
100& pg                   &                   Page Count                      \\\hline
101& privilegecode        &                   Privilege Code
\\\hline
103& ct                   &                    Country                        \\\hline
104& lg                   &                     Language                      \\\hline
105& dupes                &              Duplicates                           \\\hline
106& chemical             &            chemical                               \\\hline
107& ddm                  &                Date Modified                      \\\hline
108& exw                  &                Express Waiver                     \\\hline
109& adr                  &                  Adverse Ruling                   \\\hline
110& dpl                  &                   Privilege Log Date              \\\hline
111& source               &              Source                               \\\hline
112& crt                  &                    Court                          \\\hline
113& df                   &                    Document Format                \\\hline
114& ddmu                 &              Date Modified UCSF                   \\\hline
115& dpdt                 &                Deposition Date                    \\\hline
116& co                   &                   Company                         \\\hline
117& dg                   &                   Drug                            \\\hline
118& en                   &        Exhibit Number                             \\\hline
119& id                   &         IDDL ID      \\\hline
121& ot                   &         ocr text                                  \\\hline
122& md                   &      metadata                                     \\\hline
    \label{table:metadata_field_desc}
\end{longtable}

\section{Remote Machine}

For development purposes, we have used a Virtual Machine (tobacco.cs.vt.edu) hosted by the Department of Computer Science at Virginia Tech, with storage capacity of 2000 GB.
It came from others, pre-installed with MySQL and Python libraries.
To connect to the VM, ssh into tobacco.cs.vt.edu as user1.

Run the following command in a terminal:

\textbf{ssh -l user1 tobacco.cs.vt.edu}

When prompted for a password, type that, and press enter to login.
You may contact Saurabh Chakrabarty or one of the authors of this report for the password.

We created a directory `fall2019' to store this group’s files.

\section{IDL Dataset}
UCSF hosts another site which stores all the metadata and OCR-ed versions of the different files related to the tobacco settlement cases. 
We downloaded the metadata and created the database ‘data’ in our VM.


Next, we created a directory named ‘data’.
We downloaded all the above mentioned OCR-ed files under this directory.
We created the script ‘download.sh’ to download the zip files to the VM (./data/rawdata) directly from the UCSF site using the wget command.

These files were then unzipped at ./fall2019/data using the script saved as ‘untar.sh’.

Code used:
\textbf{tar -xzvf f-j.tar.gz}

These files are stored in a tree like structure with 16 base directories, with 4 levels under each. At each level, there are 16 more directories which are subdivided into 16 further directories. The process is repeated until the last level is reached, where the final folders containing the .ocr files are stored.
When we want to access an OCR-ed file with record key ffvv0000, we can find it listed as ffvv0000.ocr under the file path /fall2019/data/f/f/v/v/ffvv0000/

As an example, to view ffff0228.ocr, run the following command in terminal:\\
    \textbf{vim ./fall2019/data/f/f/f/f/ffff0228/ffff0228.ocr}

Figure ~\ref{fig:ffff0228_ocr} shows the output file.
%\newpage
\begin{figure}[H]
    \centering
    \includegraphics[width=\textwidth]{figures/dev1.png}
    \caption{Depicts the text editable version of a tobacco file saved as ffff0228.ocr}
    \label{fig:ffff0228_ocr}
\end{figure}

\section{MySQL Database}

The UCSF website also contains an instruction manual and README.txt, which helped us recreate the database on our Virtual Machine. 
We used the database server MariaDB, which is an open source fork of MySQL, for data manipulation.

First of all, a new database was created under the user `root’ and name `data’.

Login as root user and no password by running the following command:

\textbf{mysql -u root -p  }

Create the database ‘data’:

\textbf{[mysql] CREATE DATABASE data CHARACTER SET utf8mb4 COLLATE utf8mb4\_unicode\_ci;}

Also, notice that we have set the character set to utf8mb4 while creating the database.
This is because the default utf8 encoding allows a maximum of 3 bytes per characters which may not support some special characters or alphabets which would require 4 bytes.
Using utf8mb4, which allows up to 4 bytes per character, would work in case there were any special characters in the metadata.
Again, collation is a set of rules for comparing and sorting data in a character set.
To maintain uniformity, we set the collation to utf8mb4\_unicode\_ci (`ci’ designates case insensitive comparisons).


Next, we populated new\_data with the metadata from the database dump file.
The command used to extract the database dump file is:

\textbf{tar xzf idl-database-dump.tar.gz }

The command used to populate the metadata tables is:

\textbf{mysql -u root -p data < idl-database-dump.sql}

Figure ~\ref{fig:logging_into_mariadb} shows how one can log into MariaDB as user `root' and view the databases under it.
\begin{figure}[H]
    \centering
    \includegraphics[width=\textwidth]{figures/dev2.png}
    \caption{Logging into MariaDB and viewing data and other databases under it.}
    \label{fig:logging_into_mariadb}
\end{figure}


\begin{figure}[H]
\centering
 \includegraphics[width=\textwidth]{figures/dev3.png}
 \caption{Viewing the tables idl\_doc, idl\_doc\_field, and idl\_field present in the database `data' and the corresponding table specifications.}
 \label{fig:DBtables1}
\end{figure}

\begin{figure}[H]
\centering
 \includegraphics[width=\textwidth]{figures/dev4.png}
 \caption{Viewing the tables idl\_data\_tobacco, idl\_collection, and idl\_industry with their descriptions under data}
\label{fig:DBtables2}
\end{figure}

Figures \ref{fig:DBtables1} and \ref{fig:DBtables2} show how one can view the various tables in the database `data' and view the corresponding table descriptions.

To join idl\_doc and idl\_doc\_field, use:

\textbf{SELECT * FROM idl\_doc id, idl\_doc\_field idf WHERE id.id=idf.id;}

To retrieve record keys, use:

\textbf{SELECT record\_key FROM idl\_doc WHERE id=`id\_value’;}

To view descriptions of different numerical codes under the column idl\_doc\_field.itag, use:

\textbf{Select * from idl\_field;}
The database has metadata for documents pertaining to four industry types referenced by distinct ID, as shown in Table \ref{table:industry}.
To view the different types of industries whose files are in the database data, use:

\textbf{Select * from idl\_industry;}

Refer to Table \ref{table:industry} to view the output of the above query.
\begin{table}[h!]
\centering
\begin{tabularx}{0.8\textwidth} { 
  | >{\raggedright\arraybackslash}X 
  | >{\centering\arraybackslash}X 
  | >{\raggedleft\arraybackslash}X | }
 \hline
  ID & Name \\
 \hline
 1  & Drug  \\
 \hline
 2 & Tobacco \\
 \hline
 3 & Food \\
 \hline
 4 & Chemical \\
 \hline
\end{tabularx}
\caption{{Values and meanings of the industry IDs (idl\_doc.industry\_id)}}
\label{table:industry}
\end{table}

To view records of files belonging to the tobacco industry only, use:

\textbf{Select * from idl\_doc where industry\_id=2;}

OR,

\textbf{Select * from idl\_doc\_tobacco;}

Table idl\_doc\_tobacco has been created by extracting data from idl\_doc where the value of industry\_id is `2', i.e., fetching rows from the table pertaining to the tobacco industry. This was done to facilitate faster traversal of the rows related to tobacco case files while executing SQL queries.

To view record\_keys of document type deposition, use:

\textbf{SELECT distinct record\_key FROM idl\_doc\_tobacco id, idl\_doc\_field idf WHERE id.id=idf.id AND idf.itag=`29' AND idf.value=`deposition’;}

\subsection{Use of the Tables idl\_industry and idl\_field}


In case we want to know which kind of industry a document with idl\_doc.record\_key=`ffvv0000' and idl\_doc.industry\_id=`2' belongs to,  we would be able to query the table idl\_industry with the industry\_id which would return us a row with the meaning of ‘2.’
See Table~\ref{table:industry} for an example.

The table idl\_field contains the significance of all the `itag' attributes attached to any document we encounter in the table idl\_doc\_field.
For example: A document has itag attribute = `29’. We may query the table idl\_field with idl\_field.id=idl\_doc\_field.itag to view its meaning.
Refer to Table \ref{table:itag29} to view the output of the above query.

\begin{table}[h!]
\centering

\begin{tabularx}{1.0\textwidth} { 
  | >{\raggedright\arraybackslash}X 
  | >{\centering\arraybackslash}X 
  | >{\raggedleft\arraybackslash}X 
  | >{\centering\arraybackslash}X | }
 \hline
  ID & Code & Short\_Description \\
 \hline
 29  & dt & Document Type  \\
\hline
\end{tabularx}

\caption{{Description of the itag value 29 fetched by querying the table idl\_field}}
\label{table:itag29}
\end{table}


Here `ID'= value of itag and `Short\_Description' is the meaning of the itag.


\section{Scripts}

\subsection{Test Runs}
\label{Section:TestRun}

Having completed the setup, our next job was to start identifying the scope of our work and create short term goals.
We decided upon creating a smaller prototype with only the metadata of documents of the type `deposition’ (\textbf{select distinct record\_key from idl\_doc\_tobacco id, idl\_doc\_field idf where id.id=idf.id and idf.itag=29 and idf.value=`deposition’;}).
Typically, we would test out our code on a couple of instances before running it on the full set of deposition related data.

We ended up creating Python scripts with functionalities as follows:
\begin{enumerate}
\item \textbf{utils.py}: This script stores values of several constants with multiple instances of use throughout our scripts.
It stores the constants username, password, hostname, and database name required to connect to the database `new\_data’.
Additionally, the function connect\_db() defined here can be called from other programs to connect to `new\_data’ readily without going through the trouble of writing the code and setting up a connection every time.

\item \textbf{determine\_depositions.py}: This script was initially used to fetch the record ID (idl\_doc\_tobacco.id) of all the tobacco files which are of the type `deposition’. The output was stored in the text file deposition\_ids\_full.txt. However, in the next iteration, we upgraded the code to create script (3).

\item \textbf{determine\_ids\_by\_type.py}: This script is an upgraded version of script (2) and can be used to fetch the record ID (idl\_doc\_tobacco.id) of all the tobacco files by the document type.
Currently, we ran this to fetch the IDs of the document types `deposition’ and `article' and their corresponding outputs are stored in the text files deposition\_ids\_full.txt and article\_ids\_full, respectively.
A subset of the deposition IDs are stored in another text file, deposition\_ids\_100.txt, which was initially used as an input in script (4).
After successful execution with the small subset, we moved on to process all the depositions by using deposition\_ids\_full.txt as input in script (4).
The same process was repeated for the articles.

\item \textbf{metadata\_to\_json.py}: The next part was to convert the metadata of the files that we will be using into JSON format. Since we were just dealing with depositions initially, we converted the metadata for only that document type.
In the next iteration we also included metadata for document type `article'.
This script helped in achieving that, by using ID (idl\_doc\_tobacco.ID) of deposition documents and articles as input.
We had used the static file deposition\_ids\_full.txt as the input for depositions, and article\_ids\_full.txt for articles.
One should note that, based on the requirements from the ELS Team, we have included the URL of the source of each file as part of the JSON file containing the metadata.
Also, we have altered the date format to match mm/dd/yyyy based on their requirements, in the current code.

Figure~\ref{fig:metadata_to_json} shows a code snippet used to read the ID from \texttt{deposition\_ids\_100.txt} and fetch \texttt{idl\_doc\_tobacco.id} from the database.

\begin{figure}[H]
    \centering
    \includegraphics[width=\textwidth]{figures/dev5.png}
    \caption{Section of code from metadata\_to\_json.py}
    \label{fig:metadata_to_json}
\end{figure}

The output of this script is the generation of the file deposition\_metadata.json and article\_metadata.json which are stored in Ceph with file path: /mnt/ceph/shared/tobacco/metadata

\item \textbf{file2json.py}: This script was initially generated to convert the text content of the OCR-ed tobacco documents into JSON. 
We were able to find 8136 deposition documents, out of which four thousand were successfully parsed initially. The OCR-ed documents which were generated based on scanned documents contained many anomalous characters. These were due to the presence of handwritten texts, company logos, signatures and other less machine recognizable fonts in these scanned documents. The OCR techniques would fail to recognize these as valid English characters and pass garbage characters like `*~~'.
We found that the presence of these anomalous characters in the OCR-ed documents were causing the misses.
We were able to work around the problem by encoding each word in the text and leaving out the ones that could not be encoded from the file to overcome this impediment.
Later we were able to parse all of those 8136 `deposition' documents.
These JSON files can be found under the folder /fall2019/deposition\_json.
The record\_key of the files serve as their name under this directory.

\item \textbf{Preprocess\_sample.py}:
This script performs the following text processing tasks on the tobacco file texts: tokenization, lemmatization, and removal of unnecessary white spaces, numbers, and garbage characters. To view the processed version of the file fghb0000.ocr, run the following command: \textbf{less cleaned\_file.txt}. Figure~\ref{fig:cleaned_file_after_preprocessing} shows an example of a processed file.
However, in the final run, we ended up not requiring to implement lemmatization and tokenization on our documents; these tasks were designated to be taken care of by the TML team.

\begin{figure}[H]
    \centering
    \includegraphics[width=\textwidth]{figures/dev6.png}
    \caption{Cleaned file after application of text processing methods like lemmatization and tokenization.}
    \label{fig:cleaned_file_after_preprocessing}
\end{figure}
\end{enumerate}

\subsection{Final Run}

\begin{enumerate}
    \item \textbf{metadata\_to\_json\_fast.py:} This script did two tasks for us in the final run of the project:
    \begin{enumerate}
        \item Generate the metadata only: The metadata of all the tobacco documents were generated ordered by the range of `ID' values fed as user input. Typically, we generated the metadata for 1 million documents at a time.
        Code used to generate metadata for the first million of these documents is:
        \textbf{python metadata\_to\_json\_fast.py 0 1000000}
        
        \item In the next step, we modified this script to generate the metadata along with an additional field `text' that contained text or data within the tobacco documents.
        The same method as above could be followed to run this script.
        Also, based upon the ELS team's requirements that were due to constraints with regards to memory allocation, we generated metadata with data for 100k documents and stored them at Ceph. The location in the ceph directory is:
        {\textbf{cd /mnt/ceph/shared/tobacco/}}
    \end{enumerate}
    
    \item\textbf{file2json.py:} Unlike in the test runs, we were no longer required to send the JSON files containing the data within the tobacco documents separately to the ELS team. Instead the file generated using the second version of the script metadata\_to\_json\_fast.py would suffice. However, the script file2json.py is imported by metadata\_to\_json\_fast.py, so that it can use the functions to access the OCR-ed files and extract their cleaned versions as value of the metadata key field `text'.
    
    \item\textbf{file2json2.py:} This script was written to access the OCR-ed files and clean their contents. Then, it would generate files containing cleaned versions of data within those documents and save them by their record key.
    
    \item\textbf{utils.py:} This remains unchanged and has the same usage as in Test Run defined in Section \ref{Section:TestRun}.
\end{enumerate}



\section{Alternative OCR Techniques}

The presence of garbage characters and the poor quality of some OCR-ed files have lead us to look for alternative approaches to implement OCR on the tobacco documents.
This was mainly to improve the quality of text files which were extracted from scanned images of newspaper articles, blurry texts, and handwritten texts.
The methods implemented gave us results with a varying degree of success except for recognizing handwritten texts.

The Python scripts and output text files are saved in the folder ocr at the filepath: /fall2019/ocr/

We can do a comparative study based on the file jydp0228. The URL of the original PDF is \url{https://www.industrydocuments.ucsf.edu/tobacco/docs/#id=jydp0228}.

\begin{enumerate}
\item \textbf{PyPDF2}: This approach failed to identify paragraphs and columns within the text.
The output had a single paragraph with all of the columns merged together.
Figure 2.5 shows the screenshot of the text file extracted by this process.
The Python script used can be found with the name test2.py.

\item \textbf{PDFMiner}: This package gave us much better results with respect to approach (1).
It could identify paragraphs and columns.
This would be a fairly efficient approach when we are dealing with PDF files with column texts as it can append the text from the second column on a page below the text from the first column on that page.
However, it failed to identify the tabular structure of data in our test file.
The columns were simply appended one after the other which resulted in the loss of information to be retrieved from the rows of the table.
Figure 2.6 shows a screenshot of the text file retrieved by this process.
The Python script used can be found with the name test2.py.

\item \textbf{Abbyy Cloud OCR}: This tool was used on the UiPath platform to test the file.
We used a trial version which developers can sign up for from Abbyy's official website.
This method could extract text with relatively good accuracy.
In addition to identifying the columns, it could also recognize the tabular structure of the text.
Figure 2.7 shows a screenshot of the text file extracted by this process.
We will implement this method by using a Python script next.

\end{enumerate}

We also tested these methods on the scanned version of an old newspaper article belonging to the tobacco dataset, whose OCR-ed file is saved as ffbb0020.ocr in the database.
The PDF of the scanned copy can be found at the UCSF website.
Figure \ref{fig:ffbb0020pdf.JPG} shows a snapshot of the raw document from September 14, 1975.

\begin{figure}[H]
\centering
 \includegraphics[width=\textwidth]{figures/ffbb0020pdf.JPG}
 \caption{Original PDF file saved in UCSF website}
 \label{fig:ffbb0020pdf.JPG}
\end{figure}

It can be seen that the quality of scanned text cannot be regarded too highly in terms of readability; it is unlikely that the font would be easily recognized by machines.
The quality of the print in the document is not consistent either.
Some of the alphabetics are darker than the others throughout the article, which is probably due to the kind of ink used for printing in those days. 
In the current OCR-ed version, the body of the document is composed of words which are not necessarily part of the English dictionary and are generated based on the wrong interpretations of words present on the scanned document.
Applying method (1) with PyPDF2 yielded no result as the code failed to parse the text.
Method (2) using PDFMiner successfully extracted a text editable file, but the content does not make much sense; it is similar to what is the case with the current version.
Figure \ref{fig:PDFMiner2.PNG} shows the text file obtained.

\begin{figure}[H]
\centering
 \includegraphics[width=\textwidth]{figures/PDFMiner2.png}
 \caption{Text file extracted using method (2)}
 \label{fig:PDFMiner2.PNG}
\end{figure}

The process failed to recognize the English words and distinguish between sentences in a paragraph.
The accuracy of identifying meaningful text was below average.
For example, the first sentence in the article which says \textbf{"The cigarette pushers are now into what the advertising industry calls "positioning". which means that a product tries to stake out an area for itself tn the smoker’s mind."} has been interpreted as \textbf{"TAe elgarelle pusAers ar paw Intu wbal Pe advtttising mdnary eall[ "po :uimmiL" wMeh means Ual a proN[t tries lo staYe eut an ara Ior IuelnnNeamnlerlminJ ."}
As is evident, the resulting text does not pass for a meaningful sentence. 

Finally, the method (3) using Abbyy Cloud OCR did manage to extract the text with much better accuracy in this test case.
Even though we cannot vouch for the degree of success this method achieves with every other newspaper article, it is not a bleak situation that we look forward to.
The English words were mostly well recognized as were the columns in the report.
Having said that, due to limited spacing between the second and third column in the scanned document, the spacing between the last two columns is not very wide in the generated text document either.
If one refers to Figure \ref{fig:Abbyy2.PNG}, one may notice that the two columns are pretty close to each other.

\begin{figure}[H]
\centering
 \includegraphics[width=\textwidth]{figures/Abbyy2.PNG}
 \caption{Text file extracted using ABbyy Cloud OCR, method (3), with scaling}
 \label{fig:Abbyy2.PNG}
\end{figure}
This may require the reader of the text file to be slightly more attentive to be able to separate them.
This problem could, however, be overcome to some extent by increasing the scaling factor -- a feature of Abbyy that magnifies the incoming PDF document by a multiple of the scaling factor -- and reading the PDF. 

Figure \ref{fig:compare_crop.JPG} show how each OCR technique performs on a sentence extracted from the newspaper article.

\begin{figure}[H]
\centering
 \includegraphics[width=\textwidth]{figures/compare_crop.JPG}
 \caption{Comparison between different OCR techniques on a sentence of the news article shown}
 \label{fig:compare_crop.JPG}
\end{figure}



Table \ref{tab:ocr_method} illustrates a comparative study among all the above discussed OCR techniques over a varied set of types of documents.

\begin{table}[h]
    \centering
    \caption{Time and cost involved in  implementing the different OCR techniques}
    \label{table:OCR_Time_Cost}
    \begin{tabular}{|c|c|c|c|c|}
        \hline
         &\textbf{UCSF} & \textbf{PyPDF2} & \textbf{PDFMiner}&\textbf{Abbyy Cloud OCR}\\
        \hline
        \textbf{Time} & Unknown & Good & Good & Average (depending upon server speed)\\
        \hline
        \textbf{Cost} & Unknown & None & None & Paid \\
        \hline
    \end{tabular}
\end{table}

Table \ref{table:OCR_Time_Cost} shows how time and cost may impact our choice of tool.

Hence, we may conclude that Abbyy Cloud OCR is an ideal choice if we have the resources.
Otherwise, PDFMiner may be the optimal choice, given that it outperforms the UCSF's proprietary tool in scenarios involving two column PDFs.
However, it should be noted, there are no other significant differences between the merits of those two methods.


\section{File Storage System}
We are using Ceph to store our JSON files, which is an open-source storage platform based on a single distributed computer cluster.
All the recent data derived during the project are uploaded and stored there.

To access Ceph, ssh into tobacco.cs.vt.edu as user1 and run the following command in the terminal:

\textbf{cd /mnt/ceph}

You will find several folders under this directory.
We, the CMT team, have used the folder `shared’ to store our JSON files and tobacco documents, under which there is a `tobacco' folder.
To view, use:

\textbf{cd /mnt/ceph/shared/tobacco}

Generally, to access a JSON or text file in the storage, make sure that an appropriate folder is opened. After that, run one of the following commands in order to open the file.
\begin{itemize}
    \item \textbf{vim filename} (for OCR files without a file extension)
    \item \textbf{vim filename.json} (for JSON files)
\end{itemize}

\noindent
To obtain a list of the current path's objects, run \textbf{dir}.
The storage is organized in the following hierarchy. There are 3 main directories:
\begin{enumerate}
    \item \textbf{data}
    \item \textbf{metadata\textunderscore final}
    \item \textbf{metadata\textunderscore with\textunderscore data\textunderscore final}
\end{enumerate}

\noindent
Details are as follows.
\begin{enumerate}

\item \textbf{data:} This folder includes groups of OCR files used and/or produced during the project.
\begin{itemize}
    \item \textit{1million\textunderscore raw:} First 1 million OCR files obtained via sorting the whole set of documents by their IDs in database. Used for intermediate data preparation and testing of some of the algorithms.
    \item \textit{article\textunderscore data:} All the OCR files of 'article' type. Served as one of subsets of documents for cleaning
    \item \textit{article\textunderscore data\textunderscore content:} Output of the cleaning algorithm derived after using a set of 'article' documents as an input
    \item \textit{deposition\textunderscore data:} An unprocessed set of all 'deposition' type documents. Used for intermediate file processing and examining their contents.
\end{itemize}

\item \textbf{metadata\textunderscore final:} This directory comprises metadata JSON files for 10M documents of the tobacco dataset. There are 31 files overall; each file stores metadata for 500K documents sorted by corresponding document IDs in the database.
See Section~\ref{section:procmeta} for details.

\item \textbf{metadata\textunderscore with\textunderscore data\textunderscore final:} Here, 3 JSON files embracing both metadata and text content of 100K documents are stored, each for 33K files (see Section~\ref{section:procmeta} for details)

\end{enumerate}


\section{Code Repository}

The final code used for generating the metadata and data has been put in a GitLab repository \cite{GitLab_setUp}, under the project name `CMT' \cite{CMT_gitlab}.
The code is arranged in folders and should be easily navigable.
Figure~\ref{fig:gitLab.PNG} shows a screenshot of the repository.


\begin{figure}[H]
\centering
 \includegraphics[width=\textwidth]{gitLab.PNG}
 \caption{Screenshot of the CMT team's GitLab repository}
 \label{fig:gitLab.PNG}
\end{figure}


\section{Ingestion of New Documents}
In addition to the current dataset, it is anticipated that we may have to deal with a small number of files being added from time to time.
It would have been fairly easy to write a script that would extract the new data from the database and parse it into JSON format.
Finally, it could append the newly generated JSON file to our older version present in Ceph, which could in turn be accessed by the other teams on the project.
However, the INT team would not have access to our Virtual Machine (tobacco.cs.vt.edu) and the database `data'.
The INT team also tried to set up Kafka, but could not do so within the available time.
After weighing alternate options, it was proposed that, as a proof of concept, we create a solution to show that it is possible to create the JSON files even without a database.
\begin{enumerate}
    \item Creation of a demo folder outside the VM containing instances of  metadata files, PDFs, and text editable content of tobacco documents.
    \item Write a Python script to pick metadata and data from these files and parse them into JSON format matching the one for tobacco files currently present on the VM.
\end{enumerate}

\noindent
We employed the following steps to achieve a solution.
\begin{enumerate}
    \item To handle incoming PDFs of tobacco documents, we created a script that can download the PDF directly from the UCSF website and implement the OCR technique to generate a text editable file.
    We chose PDFMiner for this operation since it is free.
    
    \item To generate the JSON of the metadata, we prepared a modified version of our Python scripts to: 
        \begin{enumerate}
            \item Merge the CSV files of the tables idl\_doc\_tobacco and idl\_doc\_field based on common ID  values; and export results into new CSV file ‘output.csv’. Figure \ref{fig:csvtojson.PNG} shows a high level view.
            \item Store itag descriptions in an array ‘itag\_array’
            \item Pick data from the columns of  ‘output.csv’ that contain the Itag and Value associated with a document ID; retrieve corresponding  itag description based on itag\_array.
            \item Parse the data into desired JSON format and store into a new file ‘output.json’. 
        \end{enumerate}
\end{enumerate}

\begin{figure}{}
    \centering
    \includegraphics[width=\textwidth]{figures/csvtojson.PNG}
    \caption{CSV files used to generate metadata.}
    \label{fig:csvtojson.PNG}
\end{figure}

The above solution has its own set of limitations viz. scalability issues, not implemented end-to-end since the Kafka integration by the INT team is incomplete and cannot be termed practical.

\section{Using MySQL Workbench}

\textbf{Note:} One may also use an interface to manipulate the MySQL database.
Section 7.6 provides the instructions to connect to the MySQL Workbench.

The following instructions provide step-by-step guidance on how to connect the MySQL Workbench to the database containing all of the metadata stored at tobacco.cs.vt.edu.
We suggest using the MySQL Workbench as one of the most convenient up-to-date MySQL database management tools.
\begin{enumerate}
    \item Download MySQL Workbench installer by downloading the installation package from the following website.
https://dev.mysql.com/downloads/workbench/ (make sure to choose an appropriate OS version)

    \item Install MySQL Workbench and run the program.

    \item Click on the “+” button right next to the "MySQL Connections".

    \item You will see a new window with the "Setup New Connection" header.
    Input the following parameters.
\begin{itemize}
\item{Connection name: whatever name you want for the connection (e.g., tobacco).}

\item{Connection method: choose the "Standard TCP/IP over SSH" from the drop down menu.}

\item{ Make sure that the "Parameters" tab is chosen.}

\item{SSH Hostname: tobacco.cs.vt.edu.}

\item{SSH Username: user1}

\item{SSH Password: click on "Store inVault", then input the password in an open window (contact Dr. Fox/Saurabh Chakrabarty for the password).}

\item{SSH Key File: leave blank}

\item{MySQL Hostname: leave the default value (127.0.0.1)}

\item{MySQL Server Port: leave the default value (3306)}

\item{Username: root}
\item{Password: leave unchanged}
\item{Default Schema: leave blank}
\end{itemize}
After all the parameters are set up, click on "Test connection".
You will see a connection warning -- just ignore it and click "OK".
If the connection is set successfully, click "OK" in the previous window.
After this step, the connection should be established.
\end{enumerate}


