ssi_parse_pdfs.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3f8b50c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pdfplumber\n",
    "import pandas as pd\n",
    "import datetime\n",
    "import os\n",
    "import re\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5dc6d92d",
   "metadata": {},
   "outputs": [],
   "source": [
    "path = 'data/raw/'\n",
    "filenames = os.listdir(path)\n",
    "filenames = sorted(filenames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1ef1f401",
   "metadata": {},
   "outputs": [],
   "source": [
    "unt_pages = [*range(129,136), *range(140,146), *range(152,156), *range(215,285), 288, 289, 291, 292, 293, *range(294,333), *range(334, 899), *range(923,947), *range(948,1207), *range(1220,1618), *range(1619,1651), *range(1653,1872), *range(1873,2121), *range(2124,2132), *range(2138, 2575), *range(2577, 2593), *range(2596, 2613), *range(2618, 2695), *range(2701, 2724), *range(2730, 2764), *range(2775,2783), 2788, 2792, 2793, 2808, 2809, 2812, 2814, 2823, 2824, 2825, 2847, 2848, 2850, 2851, 2856, 2858, 2862, 2866, 2867, 2868, 2869, 2870, 2871, 2873, 2874, 2875, 2876, 2877, 2878, 2879, 2880, 2881, 2882, 2883, 2885, 2886, 2887, 2888, 2889, 2890, 2891, 2893, 2894, 2895, 2896, 2897, 2898, 2899, 2900, 2902, 2904, *range(2906,2930), *range(2932, 2943), *range(2944,2954), *range(2959,2965), *range(2967,2991)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9b315863",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'.DS_Store'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filenames.pop(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c778c865",
   "metadata": {},
   "outputs": [],
   "source": [
    "counter = 0\n",
    "ds = []\n",
    "pass_nums = []\n",
    "fail_nums = []\n",
    "#loop through each file\n",
    "for filename in filenames:\n",
    "\n",
    "    #add the file name to the path\n",
    "    filepath = path + filename\n",
    "\n",
    "    #open the file with pdfplumber\n",
    "    with pdfplumber.open(filepath) as pdf:\n",
    "\n",
    "            #create empty string to store pdf text\n",
    "            pdf_text = ''\n",
    "\n",
    "            #loop through each page in the pdf and add the text to the pdf_text string\n",
    "            if 'unt' not in filename:\n",
    "                for i in range(0, len(pdf.pages)):\n",
    "                    page = pdf.pages[i]\n",
    "                    text = page.extract_text()\n",
    "                    pdf_text = pdf_text + '\\n' + text\n",
    "                \n",
    "            else: #dont want to loop through all 4k plus pages of UNT doc=\n",
    "                for i in unt_pages: \n",
    "                    page = pdf.pages[i-1] #i-1 because page indicies start at 0 and I wrote down actual page numbers ie. [0] is page 1\n",
    "                    text = page.extract_text()\n",
    "                    pdf_text = pdf_text + '\\n' + text\n",
    "                unt_text = pdf_text #save the text for later\n",
    "            \n",
    "            if re.findall('Alert\\s{1,2}#(.*)\\s{1,2}(was|matched)', pdf_text) != []: #grab all the alert numbers for alert notifications and topic rollups\n",
    "                    ids = re.findall('Alert\\s{1,2}#(.*)\\s{1,2}(was|matched)', pdf_text)\n",
    "                    \n",
    "                    #get only the alert number not anything else, including whitespaces\n",
    "                    for i,j in enumerate(ids):\n",
    "                        ids[i] = j[0][:9].strip() \n",
    "\n",
    "            elif re.findall('Alert ID: (.*) \\n', pdf_text) != []: #grab all the alert numbers\n",
    "                ids = re.findall('Alert ID: (.*) \\n', pdf_text)\n",
    "\n",
    "            else: #set ids to empty list if no alert numbers are found\n",
    "                ids = []\n",
    "\n",
    "            #split the text into a list of lines called y\n",
    "            y = pdf_text.split('\\n')\n",
    "                \n",
    "            #loop through each id it finds\n",
    "            for i in range(0, len(ids)):\n",
    "                d = {}\n",
    "                   \n",
    "                d['alert_id'] = ids[i]\n",
    "                d['filename'] = filename\n",
    "\n",
    "                #if it is not the last id in the list, grab the part of y from that id to the line that has the next id in it\n",
    "                id1 = ids[i]\n",
    "                idx1 = [idx for idx, s in enumerate(y) if id1 in s][0]\n",
    "                \n",
    "                try:\n",
    "                    id2 = ids[i+1]\n",
    "                    idx2 = [idx for idx, s in enumerate(y) if id2 in s][0]\n",
    "                    z = y[idx1:idx2]\n",
    "                \n",
    "                except: #should be the case for the last id in the list\n",
    "                    z = y[idx1:]\n",
    "\n",
    "                chunk = ''\n",
    "                    \n",
    "                #loop through the subsection of y and add the text to the chunk string\n",
    "                for line in z:\n",
    "                    chunk += line + '\\n'\n",
    "                \n",
    "                #time posted\n",
    "                if re.findall('Time\\s{1,2}posted:(.*)', chunk) != []: #topic rollup and alert notification\n",
    "                    d['time_posted'] = re.findall('Time\\s{1,2}posted:(.*)', chunk)[0]\n",
    "                \n",
    "                elif re.findall('Published:(.*)', chunk) != []: #alert summary\n",
    "                    d['time_posted'] = re.findall('Published:(.*)', chunk)[0]\n",
    "                \n",
    "                else: #failure case\n",
    "                    d['time_posted'] = ''\n",
    "                \n",
    "                #locations\n",
    "                if re.findall('Locations: (.*)', chunk) != []: #alert notification and topic rollup\n",
    "                    d['locations'] = re.findall('Locations: (.*)', chunk)[0]\n",
    "                \n",
    "                elif re.findall('Location\\(s\\):(.*)', chunk) != []: #alert summary\n",
    "                    d['locations'] = re.findall('Location\\(s\\):(.*)', chunk)[0]\n",
    "                \n",
    "                else: #failure case\n",
    "                    d['locations'] = ''\n",
    "                \n",
    "                #link\n",
    "                if re.findall('Details:(.*)', chunk) != []: #alert summary\n",
    "                    d['link'] = re.findall('Details:(.*)', chunk)[0]\n",
    "                \n",
    "                elif re.findall('Link:(.*)', chunk) != []: #topic rollup\n",
    "                    d['link'] = re.findall('Link:(.*)', chunk)[0]\n",
    "                \n",
    "                else: #failure case\n",
    "                    d['link'] = ''\n",
    "\n",
    "                #source\n",
    "                if re.findall('Source: (.*)', chunk) != []: \n",
    "                    d['source'] = re.findall('Source: (.*)', chunk)[0]\n",
    "                \n",
    "                else:\n",
    "                    d['source'] = ''\n",
    "\n",
    "                #author\n",
    "                if re.findall('Author: (.*)', chunk) != []: #alert summary and topic rollup\n",
    "                    d['author'] = re.findall('Author: (.*)', chunk)[0]\n",
    "                \n",
    "                \n",
    "                if re.findall('Post\\s{1,2}from(.*)', chunk) != []: #alert notification\n",
    "                    \n",
    "                    pf = re.findall('Post\\s{1,2}from(.*)', chunk)[0]\n",
    "                    pf = pf.strip()\n",
    "                    \n",
    "                   #if we don't already have an author, set the author to the post from value\n",
    "                    try: \n",
    "                        d['author']\n",
    "                    except KeyError:\n",
    "                        d['author'] = pf\n",
    "\n",
    "                elif re.findall('(.*) posted\\s{1,2}an\\s{1,2}image', chunk) != []: #UNT alert notification for Instagram\n",
    "                    pf = re.findall('(.*) posted\\s{1,2}an\\s{1,2}image', chunk)[0]\n",
    "                    \n",
    "                    #if we dont already have a source set the source to what it says in the post from line\n",
    "                    try:\n",
    "                        d['source']\n",
    "                    except KeyError:\n",
    "                        d['source'] = 'Instagram'\n",
    "                    \n",
    "                    #if we don't already have an author, set the author to the post from value\n",
    "                    try:\n",
    "                        d['author']\n",
    "                    except KeyError:\n",
    "                        d['author'] = pf\n",
    "                \n",
    "                elif re.findall('(.*)posted\\s{1,2}a\\s{1,2}video', chunk) != []: #UNT alert notification\n",
    "                    pf = re.findall('(.*)posted\\s{1,2}a\\s{1,2}video', chunk)[0]\n",
    "                    \n",
    "                    try:\n",
    "                        d['author']\n",
    "                    except KeyError:\n",
    "                        d['author'] = pf\n",
    "                \n",
    "                elif re.findall('(.*)posted\\s{1,2}a\\s{1,2}note', chunk) != []: #UNT alert notification for Google+\n",
    "                    pf = re.findall('(.*)posted\\s{1,2}a\\s{1,2}note', chunk)[0]\n",
    "                    d['source'] = 'Google+'\n",
    "                    \n",
    "                    try:\n",
    "                        d['author']\n",
    "                    except KeyError:\n",
    "                        d['author'] = pf\n",
    "            \n",
    "                elif re.findall('(.*)posted\\s{1,2}a\\s{1,2}bookmark', chunk) != []: #UNT alert notification for Google+\n",
    "                    pf = re.findall('(.*)posted\\s{1,2}a\\s{1,2}bookmark', chunk)[0]\n",
    "                    d['source'] = 'Google+'\n",
    "                    \n",
    "                    try:\n",
    "                        d['author']\n",
    "                    except KeyError:\n",
    "                        d['author'] = pf\n",
    "                \n",
    "                elif re.findall('(.*)shared\\s{1,2}an\\s{1,2}activity', chunk) != []: #UNT alert notification for Google+\n",
    "                    pf = re.findall('(.*)shared\\s{1,2}an\\s{1,2}activity', chunk)[0]\n",
    "                    d['source'] = 'Google+'\n",
    "                    \n",
    "                    try:\n",
    "                        d['author']\n",
    "                    \n",
    "                    except KeyError:\n",
    "                        d['author'] = pf\n",
    "\n",
    "                elif re.findall('(.*)shared\\s{1,2}a\\s{1,2}photo', chunk) != []: #UNT alert notification for Google+\n",
    "                    pf = re.findall('(.*)shared\\s{1,2}a\\s{1,2}photo', chunk)[0]\n",
    "                    d['source'] = 'Google+'\n",
    "                    \n",
    "                    try:\n",
    "                        d['author']\n",
    "                    except KeyError:\n",
    "                        d['author'] = pf\n",
    "                \n",
    "                elif re.findall('(.*)shared\\s{1,2}a\\s{1,2}video', chunk) != []: #UNT alert notification for Google+\n",
    "                    pf = re.findall('(.*)shared\\s{1,2}a\\s{1,2}video', chunk)[0]\n",
    "                    d['source'] = 'Google+'\n",
    "                    \n",
    "                    try:\n",
    "                        d['author']\n",
    "                    except KeyError:\n",
    "                        d['author'] = pf \n",
    "                \n",
    "                else:\n",
    "                    pf = ''\n",
    "\n",
    "                #delivered to\n",
    "                if re.findall('delivered to (.*)', chunk) != []:\n",
    "                    d['delivered_to'] = re.findall('delivered to (.*)', chunk)[0]\n",
    "                else:\n",
    "                    d['delivered_to'] = ''\n",
    "                \n",
    "                #geodata\n",
    "                if re.findall('Geodata: (.*)', chunk) != []: #topic rollup\n",
    "                    d['geodata'] = re.findall('Geodata: (.*)', chunk)[0]\n",
    "                else: #failure case\n",
    "                    d['geodata'] = ''\n",
    "                \n",
    "                #rmap match\n",
    "                if re.findall('Match: (.*)', chunk) != []: #topic rollup and alert notification\n",
    "                    d['rmap_match'] = re.findall('Match: (.*)', chunk)[0]\n",
    "                else: #failure case\n",
    "                    d['rmap_match'] = ''\n",
    "            \n",
    "                #client id and type\n",
    "                if 'alert_summary' in filename: #alert summary\n",
    "                    d['client_id'] = filename[14:18]\n",
    "                    d['type'] = 'alert summary'\n",
    "                \n",
    "                elif 'topic_rollup' in filename: #topic rollup\n",
    "                    d['client_id'] = filename[13:17]\n",
    "                    d['type'] = 'topic rollup'\n",
    "                \n",
    "                elif 'palm_beach' in filename: #alert notification (palm beach)\n",
    "                    d['client_id'] = '3408'\n",
    "                    d['type'] = 'alert notification'\n",
    "                \n",
    "                elif 'unt' in filename: #alert notification (unt)\n",
    "                    d['client_id'] = '1042'\n",
    "                    d['type'] = 'alert notification'\n",
    "                \n",
    "                else: #failure case\n",
    "                    d['client_id'] = ''\n",
    "                    d['type'] = ''\n",
    "\n",
    "                #content\n",
    "                if 'topic_rollup' in filename: #topic rollup\n",
    "                    #grab lines from the line with 'Content:' in it to the line with 'Link:' in it then combine them \n",
    "                    if [idx for idx, s in enumerate(z) if 'Content:' in s] != []:\n",
    "                        \n",
    "                        if [idx for idx, s in enumerate(z) if 'Link' in s]  != []:\n",
    "                            \n",
    "                            c_idx = [idx for idx, s in enumerate(z) if 'Content:' in s][0]\n",
    "                            l_idx = [idx for idx, s in enumerate(z) if 'Link' in s][0]\n",
    "                            lines = z[c_idx:l_idx]\n",
    "                            d['content'] = '\\n'.join(lines)\n",
    "                            \n",
    "                            #clean up the content\n",
    "                            d['content'] = d['content'].replace('Content: ', '')\n",
    "                            # d['content'] = d['content'].replace('\\n', ' ')\n",
    "                        \n",
    "                        else:\n",
    "                            \n",
    "                            c_idx = [idx for idx, s in enumerate(z) if 'Content:' in s][0]\n",
    "                            lines = z[c_idx:]\n",
    "                            d['content'] = '\\n'.join(lines)\n",
    "                            \n",
    "                            #clean up the content\n",
    "                            d['content'] = d['content'].replace('Content: ', '')\n",
    "                            # d['content'] = d['content'].replace('\\n', ' ')\n",
    "                    \n",
    "                    else:\n",
    "                        d['content'] = chunk\n",
    "                \n",
    "                elif 'palm_beach' in filename:\n",
    "                    \n",
    "                    if [idx for idx, s in enumerate(z) if 'Post from' in s] != []:\n",
    "                        \n",
    "                        if [idx for idx, s in enumerate(z) if 'View Alert' in s] != []:\n",
    "                            \n",
    "                            #grab lines from the line after 'Post from' in it to the line with 'View Alert' in it then combine them\n",
    "                            pf_idx = [idx for idx, s in enumerate(z) if 'Post from' in s][0]\n",
    "                            va_idx = [idx for idx, s in enumerate(z) if 'View Alert' in s][0]\n",
    "                            lines = z[pf_idx+1:va_idx]\n",
    "                            \n",
    "                            #clean up the content\n",
    "                            d['content'] = '\\n'.join(lines)\n",
    "                            # d['content'] = d['content'].replace('\\n', ' ')\n",
    "                        \n",
    "                        else:\n",
    "                            pf_idx = [idx for idx, s in enumerate(z) if 'Post from' in s][0]\n",
    "                            lines = z[pf_idx+1:]\n",
    "                            d['content'] = '\\n'.join(lines)\n",
    "                            # d['content'] = d['content'].replace('\\n', ' ')\n",
    "                    \n",
    "                    else:\n",
    "                        d['content'] = chunk\n",
    "                \n",
    "                elif 'unt' in filename:\n",
    "                    \n",
    "                    if pf != '':\n",
    "                        \n",
    "                        #grab lines from the line after 'Post from' in it to the line with 'View Alert' in it then combine them\n",
    "                        pf_idx = [idx for idx, s in enumerate(z) if pf in s][0]\n",
    "                        \n",
    "                        if re.findall('Social\\s{1,2}Sentinel\\s{1,2}Alert\\s{1,2}Notification', chunk) != []:\n",
    "                            va = re.findall('Social\\s{1,2}Sentinel\\s{1,2}Alert\\s{1,2}Notification', chunk)[0]\n",
    "                            va_idx = [idx for idx, s in enumerate(z) if va in s][0]\n",
    "                            lines = z[pf_idx+1:va_idx]\n",
    "                            #clean up the content\n",
    "                            d['content'] = '\\n'.join(lines)\n",
    "                            # d['content'] = d['content'].replace('\\n', ' ')\n",
    "                            if ids[i] not in pass_nums:\n",
    "                                pass_nums.append(ids[i])\n",
    "                        else:\n",
    "                            lines = z[pf_idx+1:]   \n",
    "                            d['content'] = '\\n'.join(lines)\n",
    "                            if ids[i] not in pass_nums:\n",
    "                                pass_nums.append(ids[i])\n",
    "                            # d['content'] = d['content'].replace('\\n', ' ')\n",
    "                            \n",
    "                    else: #pf == ''\n",
    "                        #failure case: write the whole chunk as content\n",
    "                        d['content'] = chunk\n",
    "                        fail_nums.append(ids[i])\n",
    "                        \n",
    "                elif 'alert_summary' in filename:\n",
    "                    #grab lines from the line after 'Author:' in it to the end of the chunk then combine them\n",
    "                    a_idx = [idx for idx, s in enumerate(z) if 'Author:' in s][0]\n",
    "                    if 'All Rights Reserved.' in chunk:\n",
    "                        ar_idx = [idx for idx, s in enumerate(z) if 'All Rights Reserved.' in s][0]\n",
    "                        lines = z[a_idx+1:ar_idx]\n",
    "                    else:\n",
    "                        lines = z[a_idx+1:]\n",
    "                    #clean up the content\n",
    "                    d['content'] = '\\n'.join(lines)\n",
    "                    d['content'] = d['content'].replace('\\n', ' ')\n",
    "                else:\n",
    "                    #failure case: write the whole chunk as content\n",
    "                    d['content'] = chunk\n",
    "\n",
    "        \n",
    "                ds.append(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1c3510ce",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "47\n"
     ]
    }
   ],
   "source": [
    "#check how many alert just failed completely\n",
    "fails = list(set([i for i in fail_nums if i not in pass_nums]))\n",
    "print(len(fails))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "69507e69",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(ds)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "3432944e",
   "metadata": {},
   "outputs": [],
   "source": [
    "map_dict = {'2193': 'Palm Beach State College', \n",
    " '3408':'Palm Beach State College',\n",
    " '3696':'Gulf Coast State College',\n",
    " '3790':'Gulf Coast State College',\n",
    " '4896':'Indian River State College',\n",
    " '3792':'Gulf Coast State College',\n",
    " '1042':'University of North Texas'}\n",
    "\n",
    "schools = []\n",
    "for i in list(df['client_id']):\n",
    "    try:\n",
    "        schools.append(map_dict[i])\n",
    "    except:\n",
    "        schools.append('')\n",
    "df['school'] = schools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "a5c26b17",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['datetime'] = pd.to_datetime(df['time_posted'], errors='coerce')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "9b53c1f7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6979\n",
      "5921\n",
      "4261\n",
      "4260\n"
     ]
    }
   ],
   "source": [
    "print(len(df))\n",
    "df= df.loc[df['content'].str.strip() != ''].reset_index(drop=True)\n",
    "print(len(df))\n",
    "df = df.groupby('alert_id').first().reset_index() #dedupe with alert_id\n",
    "print(len(df))\n",
    "df = df.loc[df['content'].str.len() < 25000].reset_index() #get rid of one outlier screed\n",
    "print(len(df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "ae64f3ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('data/created/flagged_tweets.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.13 ('base')",
   "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.9.13"
  },
  "vscode": {
   "interpreter": {
    "hash": "d5c0b1bc2e732ecc44534eaffed66287fe33215677cef0fa29158217bd223645"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}