{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MHC_V20 Data Cleaning and Wrangling\n",
    "## -- as a part of the OSCR proejct\n",
    "Leo (Lizhou) Fan\n",
    "Acknowledgements: Dr. Ashley Sanders Garcia & Dr. Miles Chen"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import packags and data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "f = open('MHC-v20.txt', 'r')\n",
    "mhc20 = f.readlines()\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['\\ufeffMichigan historical collections I Michigan Historical Commission. ',\n",
       " 'Lansing : The Commission, 1915-1929. ',\n",
       " 'http://hdl.handle.net/2027/ucl.a0002813681 ',\n",
       " 'HathiTrust ',\n",
       " 'www.hathitrust.org ',\n",
       " 'Public Domain, Google-digitized ',\n",
       " 'http://www.hathitrust.0rg/access_use#pd-g00gle ',\n",
       " 'We have determined this work to be in the public domain, meaning that it is not subject to copyright. Users are free to copy, use, and redistribute the work in part or in whole. It is possible that current copyright holders, heirs or the estate of the authors of individual portions of the work, such as illustrations or photographs, assert copyrights over these portions. Depending on the nature of subsequent use that is made, additional rights may need to be obtained independently of anything we can address. The digital images and OCR of this work were produced by Google, Inc. (indicated by a watermark on each page in the PageTurner). Google requests that the images and OCR not be re-hosted, redistributed or used commercially. The images are provided for educational, scholarly, non-commercial purposes. ',\n",
       " 'Digitized by ',\n",
       " 'Google ',\n",
       " 'Original from ',\n",
       " 'UNIVERSITY OF CALIFORNIA ',\n",
       " 'LU ',\n",
       " '* ns ANCi- ',\n",
       " 'HISTORICAL COLLECTIONS ',\n",
       " 'COLLECTIONS AND RESEARCHES ',\n",
       " 'Man by the ',\n",
       " 'Michigan Pioneer and Historical Society ',\n",
       " 'REPRINT 1912 ',\n",
       " 'Vol. ',\n",
       " 'M. AGNES BURTON ',\n",
       " 'DETROIT, MICH. ',\n",
       " \"LANSING. MiCIll'L'-N WYNKOOP HALLENItECK CltAWl-'Oltl* . STATE PKINTEIIS 1012 \",\n",
       " 'Entered according to Act of Congress, in the year 1892, by the MICHIGAN PIONEER AND HISTORICAL SOCIETY, In the office of the Librarian of Congress, at Washington, D. C. ',\n",
       " 'PREFACE ',\n",
       " 'This, the twentieth volume of Pioneer and Historical Collections, as well as volume 19, is entirely devoted to copies of original documents from the Canadian Archives at Ottawa, relating to Michigan and its environs. ',\n",
       " 'The “Haldimand Papers’’ are a continuation of the same from volume 19. beginning with the year 1782, and carries them to a finish in 1789, covering the period of the closing of the Revolutionary war, and the establishing of peace between Great Britain and the United States, and the evacuation of some of the Upper Military Posts, and comprises nearly half of the volume. ',\n",
       " 'The papers devoted to “Indian Affairs” cover a period from 1761 to 1800, and contain a mass of correspondence relative to the Indian Depart- , ment and the Indian war with the United States. ',\n",
       " 'We trust its readers will find it fully as interesting as any which have preceded it. ',\n",
       " 'MICHAEL SHOEMAKER, Chairman, Jackson JOHN H. FORSTER, Williamston HENRY H. HOLT, Muskegon FREDERICK CARLISLE, Detroit ',\n",
       " 'Committee of Historians ',\n",
       " 'Lansing, Mich., Dec. 15. 1892 ',\n",
       " 'Digitized by ',\n",
       " 'Google ',\n",
       " '____Original from ',\n",
       " 'UNIVERSITY OF CALIFORNIA ',\n",
       " 'PREFACE TO REPRINT ',\n",
       " 'In reprinting this volume an effort has been made to correct such mistakes in names and dates as were evidently made by the copyist and explanatory’ notes have been added as in Vol. XIX, this series. ',\n",
       " \"The first half of the volume is a continuation of Vol. XIX, which concludes the Haldimand Papers. Pages 300-673 are a collection of papers on Indian Affairs; selected from the military correspondence in the Canadian Archives at Ottawa, a senes marked “C'? and containing 1,062 volumes of manuscripts. These are continued in Vol. XXIII this series. Conspicuous among the names in these papers is Joseph Chew, an old friend of Sir William Johnson, an ardent Loyalist, who with his son, held the office of Secretary of the Indian Department under different members of the Johnson family from 1774-1806. A brief sketch of his interesting life is given in the appendix under p. 313. \",\n",
       " 'M. AGNES BURTON Burton Library. Detroit. Mich., February. 1912 ',\n",
       " 'Digitized by ',\n",
       " 'Google ',\n",
       " 'Original frcm ',\n",
       " 'UNIVERSITY OF CALIFORNIA ',\n",
       " 'CONTENTS ',\n",
       " 'Page. ',\n",
       " 'Preface................................................................................... ',\n",
       " 'Preface to reprint............................................................................ v ',\n",
       " 'Contents.................................................................................... vii ',\n",
       " 'Illustrations: ']"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mhc20[0:50]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Original PDF refer to\n",
    "- Our Goal/Ideology of Cleaning: \n",
    "    - information retain?\n",
    "    - focus?\n",
    "    - depend on manually cleaning as the last step?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "join lines -> pattern to detect document -> metadata extract [table of content, beginning of each document] -> footnotes get rid of them"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Projects and their Questions:\n",
    "- Research question: role of emotion and sentiment play in decition making in American middle west 1800\n",
    "    - role of individual actors\n",
    "- \"Big volumes\" split them into document"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "References:\n",
    "- https://programminghistorian.org/en/lessons/cleaning-ocrd-text-with-regular-expressions\n",
    "- https://programminghistorian.org/en/lessons/generating-an-ordered-data-set-from-an-OCR-text-file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract Metadata and Basic Cleaning\n",
    "Regarding metadata, two things stands out:\n",
    "- all capital letter\n",
    "- Place + Time Combo\n",
    "\n",
    "We can use these traits as the clue for both extracting metadata and seperate documents."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "13352"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(mhc20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# initiate\n",
    "Text = mhc20\n",
    "\n",
    "# clean all the \\n and \\t to \" \"\n",
    "for line in Text:\n",
    "    line = re.sub(r'\\n|\\t', ' ', line)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create an empty list to fill with lines of corrected metadata\n",
    "CleanTitle = []\n",
    "CleanTitleNo = [] # line number of the title extracted\n",
    "CleanTimePlace = []\n",
    "CleanTimePlaceNo = [] # line number of the time and place extracted\n",
    "\n",
    "# checks each line in the imported text file for all the following patterns\n",
    "for i in range(len(Text)):\n",
    "    \n",
    "    ### title with people involved\n",
    "    # all capitals with the word \" TO \" could be a title\n",
    "    # as the extracts are all letter from one person to some other(s)\n",
    "    line11 = re.search(r' TO ', Text[i])\n",
    "    if line11:\n",
    "        CleanTitle.append(Text[i])\n",
    "        CleanTitleNo.append(i)\n",
    "    \n",
    "    ### Time and Place as a whole\n",
    "    # Time is ideally of the format Month Day Year or Day Month Year\n",
    "    # the four digits could be the signal\n",
    "    # The word(s) before time in the same line is the place\n",
    "    line21 = re.search(r' \\d{4}', Text[i])\n",
    "    if line21 and len(Text[i])<50:\n",
    "        CleanTimePlace.append(Text[i])\n",
    "        CleanTimePlaceNo.append(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['PREFACE TO REPRINT ',\n",
       " 'COPIES OF PAPERS ON FILE IN THE DOMINION ARCHIVES AT OTTAWA, CANADA, PERTAINING TO MICHIGAN, AS FOUND IN THE HALDIMAND AND OTHER OFFICIAL PAPERS ',\n",
       " 'CAPT. ALEXANDER GRANT TO BRIG. GEN. II. WATSON POWELL1 ',\n",
       " 'SIR HENRY CLINTON TO GEN. FREDERICK HALDIMAND ',\n",
       " 'MAJOR ARENT S. DE PEYSTER TO BRIG. GEN. H. WATSON POWELL ',\n",
       " 'FRANCIS BROWN TO CAPT. GRANT ',\n",
       " 'FRANCIS BROWN1 TO CAPT. ALEXANDER GRANT ',\n",
       " 'BRIG. GEN. H. WATSON POWELL TO GEN. FREDERICK HALDIMAND ',\n",
       " 'MAJOR ARENT S. DE PEYSTER TO BRIG. GEN. H. WATSON POWELL ',\n",
       " 'CAPT. ALEXANDER GRANT TO BRIG. GEN. H. WATSON POWELL ',\n",
       " 'GEN. FREDERICK HALDIMAND TO CHARLES BEMBRIDGE. WARRANT TO CAPT. LA MOTTE FOR PAYMENT TO DETROIT VOLUNTEERS ',\n",
       " 'COMMISSION TO JEHU HAY ',\n",
       " 'MAJOR GENERAL R1EDESEL* 2 TO GEN. FREDERICK HALDIMAND ',\n",
       " 'GEN. FREDERICK HALDIMAND TO SIR HENRY CLINTON ',\n",
       " 'GEN. FREDERICK HALDIMAND TO BRIG. GEN. H. WATSON POWELL ',\n",
       " 'GEN. FREDERICK HALDIMAND TO BRIG. GEN. II. WATSON POWELL ',\n",
       " 'BRIG. GEN. II. WATSON POWELL TO GEN. FREDERICK HALDIMAND ',\n",
       " 'GEN. FREDERICK HALDIMAND TO MAJOR GEN. DE RIEDESEL ',\n",
       " 'GEN. FREDERICK HALDIMAND TO MAJOR ROSS ',\n",
       " 'BRIG. GEN. II. WATSON POWIELL TO GEN. FREDERICK HALDIMAND ',\n",
       " 'LT. GOV. PATRICK SINCLAIR TO BRIG. GEN. H. WATSON POWELL ',\n",
       " 'CAPT. ROBERT MATHEWS TO JAMES S. GODDARD ',\n",
       " 'MAJOR ARENT S. DE PEYSTER TO BRIG. GEN. H. WATSON POWELL ',\n",
       " 'CAPT. ROBERT MATHEWS TO WILLIAM BARR ',\n",
       " 'CAPT. R. MATHEWS TO BRIG. GEN. H. WATSON POWELL ',\n",
       " 'MAJOR ARENT S. DE PEYSTER TO BRIG. GEN. II. WATSON POWELL ',\n",
       " 'WILLIAM BARR1 TO CAPT. ROBERT MATHEWS ',\n",
       " 'CAPT. ROBERT MATHEWS TO NATHANIEL DAY1 ',\n",
       " 'GEN. FREDERICK HALDIMAND TO BRIG. GEN. H. WATSON POWELL ',\n",
       " 'J. MAURER TO CAPT. ROBERT MATHEWS ',\n",
       " 'CAPT. ALEXANDER GRANT TO BRIG. GEN. H. WATSON POWELL ',\n",
       " 'MAJOR ARENT S. DE PEYSTER TO BRIG. GEN. H. WATSON POW’ELL ',\n",
       " 'CARE WILLIAM CALDWELL TO MAJOR ARENT 8. DE PEYSTER ',\n",
       " 'MAJOR ARENT S. DE PEYSTER TO BRIG. GEN. H. WATSON POWELL ',\n",
       " 'LT. JOHN TURNEY’ TO MAJOR ARENT S. DE PEYSTER ',\n",
       " 'MAJOR ARENT S. DE PEYSTER TO BRIG. GEN. H. WATSON POWELL ',\n",
       " 'MAJOR ARENT S. DE PEYSTER TO BRIG. GEN. II. WATSON POWELL ',\n",
       " 'MAJOR ARENT 8. DE PEYSTER TO BRIG. GEN. H. WATSON POWELL ',\n",
       " 'BRIG. GEN. H. WATSON POWELL TO GEN. FREDERICK HALDIMAND ',\n",
       " 'MEMORIAL OF JOHN DAILY TO GEN. FREDERICK HALDIMAND ',\n",
       " 'BRIG. GEN. II. WATSON POWELL TO GEN. FREDERICK HALDIMAND ',\n",
       " 'GEN. FREDERICK HALDIMAND TO BRIG. GEN. H. WATSON POWELL ',\n",
       " 'LT. GOV. PATRICK SINCLAIR TO LT. COL. JOHN CAMPBELL ',\n",
       " 'CAPT. ROBERT MATHEWS TO LIEUT. COL. JOHN CAMPBELL ',\n",
       " 'GEN. FREDERICK HALDIMAND TO BRIG. GEN. SPETH ',\n",
       " 'CAPT. ALEXANDER MC KEE TO MAJOR ARENT S. DE PEYSTER ',\n",
       " 'GEN. FREDERICK HALDIMAND TO SIR GUY CARLETON ',\n",
       " 'LT. COL. JOHN CAMPBELL TO CAPT. ROBERT MATHEWS ',\n",
       " 'CAPT. ROBERT MATHEWS TO LT. COL. JOHN CAMPBELL ',\n",
       " 'GEN. FREDERICK HALDIMAND TO BRIG. GEN. H. WATSON POWELL ']"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CleanTitle[0:50]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Lansing : The Commission, 1915-1929. ',\n",
       " 'REPRINT 1912 ',\n",
       " 'Lansing, Mich., Dec. 15. 1892 ',\n",
       " '“ 1773   324 ',\n",
       " '“ 1794   325 ',\n",
       " 'Sir 1782 ',\n",
       " 'Detroit 20h March 1782. ',\n",
       " 'Detroit 12 April 1782 Francis Brow n ',\n",
       " 'Niagara April 14h 1782. Sir ',\n",
       " 'Endorsed From A 1782 ',\n",
       " 'Detroit April 22nd 1782. Sir ',\n",
       " 'Sorel April 26h 1782 Sir ',\n",
       " 'Sir, 28h April 1782. ',\n",
       " 'To B. Genl. Powell 1782. 28h April ',\n",
       " 'Niagara Apl 30h 1782 ',\n",
       " 'Sir, Montreal 7th May 1782 ',\n",
       " '(Copy) 1782 ',\n",
       " 'Niagara May 7h 1782. ',\n",
       " 'Detroit May 15th 1782. Sir ',\n",
       " 'Montreal 15th May 1782 ',\n",
       " 'Sir 16 May 1782 ',\n",
       " 'Three Rivers 17h May Sir 1782. ',\n",
       " 'Sir Montreal 27h May 1782. ',\n",
       " 'Endorsed To 1782 ',\n",
       " 'Sir, 31st May 1782. ',\n",
       " '-This occurred Dec., 1782. ',\n",
       " 'Chumbly 1 June 1782. 503.50 98     3995     ',\n",
       " 'Oswego 26 Mar 1782 30.  2000         ',\n",
       " 'Niagara 30 Apl 1782. 133.30  12322         ',\n",
       " 'Montreal, 3d June 1782. ',\n",
       " 'Detroit June 6h 1782. ',\n",
       " 'Detroit June the 7h 1782 Sir ',\n",
       " ' Montreal, June 9h 1782. ',\n",
       " 'Sandusky June llh 1782. ',\n",
       " 'Detroit 12th June 1782. ',\n",
       " 'Sir June 17h 1782 ',\n",
       " 'Endorsed 3 1782 ',\n",
       " 'Sir 1782 ',\n",
       " 'No 9 Detroit June 23rd 1782. ',\n",
       " 'Detroit 29h of June 1782 ',\n",
       " 'Quebec 1st July 1782 ',\n",
       " 'Mlehc 30 Sep 1782 .86   7418        499 780 ',\n",
       " 'Niagara July 1st 1782. Sir ',\n",
       " 'From A 1782 ',\n",
       " 'Montreal 4h July 1782 ',\n",
       " 'Niagara July lOh 1782. Sir, ',\n",
       " 'Quebec llh July 1782. ',\n",
       " 'Copy 1782 ',\n",
       " 'Dated 16h July 1782. ',\n",
       " 'Montreal the 18h July 1782. ']"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CleanTimePlace[0:50]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "211"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(CleanTimePlace)-len(CleanTitle) # there are more possible time and place than titles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "580"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(CleanTitle) # about 580 documents are there, haven't checked"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "### save metadata into csv files\n",
    "Title = pd.DataFrame()\n",
    "Title[\"title\"] = CleanTitle\n",
    "Title[\"line\"] = CleanTitleNo\n",
    "TimePlace = pd.DataFrame()\n",
    "TimePlace[\"time_place\"] = CleanTimePlace\n",
    "TimePlace[\"line\"] = CleanTimePlaceNo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>line</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PREFACE TO REPRINT</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>COPIES OF PAPERS ON FILE IN THE DOMINION ARCHI...</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CAPT. ALEXANDER GRANT TO BRIG. GEN. II. WATSON...</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SIR HENRY CLINTON TO GEN. FREDERICK HALDIMAND</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MAJOR ARENT S. DE PEYSTER TO BRIG. GEN. H. WAT...</td>\n",
       "      <td>154</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               title  line\n",
       "0                                PREFACE TO REPRINT     36\n",
       "1  COPIES OF PAPERS ON FILE IN THE DOMINION ARCHI...    90\n",
       "2  CAPT. ALEXANDER GRANT TO BRIG. GEN. II. WATSON...    95\n",
       "3     SIR HENRY CLINTON TO GEN. FREDERICK HALDIMAND    108\n",
       "4  MAJOR ARENT S. DE PEYSTER TO BRIG. GEN. H. WAT...   154"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Title.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>time_place</th>\n",
       "      <th>line</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Lansing : The Commission, 1915-1929.</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>REPRINT 1912</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lansing, Mich., Dec. 15. 1892</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>“ 1773   324</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>“ 1794   325</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              time_place  line\n",
       "0  Lansing : The Commission, 1915-1929.      1\n",
       "1                          REPRINT 1912     18\n",
       "2         Lansing, Mich., Dec. 15. 1892     31\n",
       "3                          “ 1773   324     67\n",
       "4                          “ 1794   325     75"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "TimePlace.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Seperate Documents\n",
    "using title as signals, comebine the lines after it and save it into the same file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "# combine the lines into the same line after a title\n",
    "CobText = []\n",
    "cobtext = ''\n",
    "start = 37\n",
    "for i in range(start,len(Text)):\n",
    "    if i in CleanTitleNo or i==(len(Text)-1):\n",
    "        CobText.append(cobtext)\n",
    "        cobtext = ''\n",
    "    else:\n",
    "        cobtext += Text[i]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "580"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(CobText)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "580"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(CleanTitleNo)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save the title, start of line, and then combines text into a dataframe\n",
    "CleanText = pd.DataFrame()\n",
    "CleanText[\"start_line\"]= CleanTitleNo\n",
    "CleanText[\"title\"]= CleanTitle\n",
    "CleanText[\"text\"]= CobText"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>start_line</th>\n",
       "      <th>title</th>\n",
       "      <th>text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>36</td>\n",
       "      <td>PREFACE TO REPRINT</td>\n",
       "      <td>In reprinting this volume an effort has been m...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>90</td>\n",
       "      <td>COPIES OF PAPERS ON FILE IN THE DOMINION ARCHI...</td>\n",
       "      <td>Note.—Care has been taken in publishing the fo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>95</td>\n",
       "      <td>CAPT. ALEXANDER GRANT TO BRIG. GEN. II. WATSON...</td>\n",
       "      <td>Extracts of a Letter from Capt. Grant2 to Brig...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>108</td>\n",
       "      <td>SIR HENRY CLINTON TO GEN. FREDERICK HALDIMAND</td>\n",
       "      <td>Quadruplicate } of Letter sent in &gt; Cypher ove...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>154</td>\n",
       "      <td>MAJOR ARENT S. DE PEYSTER TO BRIG. GEN. H. WAT...</td>\n",
       "      <td>Extracts from Major De Peyster’s letter dated ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   start_line                                              title  \\\n",
       "0          36                                PREFACE TO REPRINT    \n",
       "1          90  COPIES OF PAPERS ON FILE IN THE DOMINION ARCHI...   \n",
       "2          95  CAPT. ALEXANDER GRANT TO BRIG. GEN. II. WATSON...   \n",
       "3         108     SIR HENRY CLINTON TO GEN. FREDERICK HALDIMAND    \n",
       "4         154  MAJOR ARENT S. DE PEYSTER TO BRIG. GEN. H. WAT...   \n",
       "\n",
       "                                                text  \n",
       "0  In reprinting this volume an effort has been m...  \n",
       "1  Note.—Care has been taken in publishing the fo...  \n",
       "2  Extracts of a Letter from Capt. Grant2 to Brig...  \n",
       "3  Quadruplicate } of Letter sent in > Cypher ove...  \n",
       "4  Extracts from Major De Peyster’s letter dated ...  "
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CleanText.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "CleanText.to_csv(\"MHC_V20_Cleaned.csv\",index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### More TO DO\n",
    "- Solve minor problems\n",
    "    - not a title\n",
    "    - extract time and place regarding comparative location to title\n",
    "- Remove footnotes\n",
    "- More cleaning of irregular/wrong chracters\n",
    "- Extract more metadata from the preface and content lists\n",
    "- Analysis work..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Leo (Lizhou) Fan, Jan 20th, 2020. All rights reserved."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}