facebook-analysis-python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Thesis title:** Who’s afraid of the big bad terrorist?\n",
    "\n",
    "**Subtitle:** Threat perception from Islamist and right-wing terrorism and the consequences in the United Kingdom and Germany.\n",
    "\n",
    "**Submission Date:** April 2018"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preparation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### IMPORTS ###\n",
    "import urllib.request\n",
    "import urllib.error\n",
    "import json\n",
    "import datetime as dt\n",
    "import csv\n",
    "import time\n",
    "import re\n",
    "import pandas as pd\n",
    "import os\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "sns.set()\n",
    "import numpy as np\n",
    "import sklearn as skl\n",
    "import sklearn.linear_model as lm\n",
    "reg = lm.LinearRegression()\n",
    "from sklearn.linear_model import Lasso\n",
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "\n",
    "### SET FOLDERS FOR WORKING DIRECTORY ###\n",
    "folder_Webscraping      = 'replace with path'#### ANONYMIZED ####\n",
    "folder_scraped_statuses = 'replace with path'#### ANONYMIZED ####\n",
    "os.chdir(folder_Webscraping) # set wd to folder 'Webscraping'\n",
    "\n",
    "\n",
    "### FACEBOOK ACCESS ###\n",
    "app_id = \"replace with ID\" #### ANONYMIZED ####\n",
    "app_secret = \"replace with secret\" #### ANONYMIZED ####\n",
    "access_token = app_id + \"|\" + app_secret\n",
    "\n",
    "### HELPER FUNCTIONS ###\n",
    "def request_until_succeed(url):\n",
    "    \"\"\" helper function to catch HTTP error 500\"\"\"\n",
    "    req = urllib.request.Request(url)\n",
    "    success = False\n",
    "    \n",
    "    while success is False:\n",
    "        try: \n",
    "            response = urllib.request.urlopen(req)\n",
    "            if response.getcode() == 200:\n",
    "                success = True\n",
    "        except Exception as e:\n",
    "            print(e)\n",
    "            time.sleep(5)\n",
    "            print(\"Error for URL\")\n",
    "    return response.read()\n",
    "\n",
    "def testFacebookPageData(page_name, access_token=access_token):\n",
    "    \"\"\" get page's numeric information \"\"\"  \n",
    "    # construct the URL string\n",
    "    base = \"https://graph.facebook.com/v2.4\"\n",
    "    node = \"/\" + page_name\n",
    "    parameters = \"/?access_token=%s\" % access_token\n",
    "    url = base + node + parameters\n",
    "    # retrieve data    \n",
    "    with urllib.request.urlopen(url) as url:\n",
    "        data = json.loads(url.read().decode()) \n",
    "    #print(data)\n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "## [IDs OF] PAGES OF INTEREST ##\n",
    "pages = [ # UK newspapers (broadsheet)\n",
    "    'financialtimes', 'dailytelegraph', 'theguardian', \n",
    "    'TELEGRAPH.CO.UK', 'timesandsundaytimes',\n",
    "    'northwesteveningmail',\n",
    "    # UK newspapers (tabloids)\n",
    "    'dailymail', 'DailyExpress', 'SundayExpres', 'thesun',\n",
    "    'dailymirror', 'MirrorPolitics', 'thesundaypeople',\n",
    "    'thedailystar', 'morningstaronline',\n",
    "    'eveningstandard', 'MetroUK', 'cityam',\n",
    "    # UK TV broadcasting\n",
    "    'bbcnews', 'itv',\n",
    "    'itvnews', 'uktvnow', 'Channel4', \n",
    "    'Channel4News', 'skynews',\n",
    "    #25\n",
    "    # Germany Newspapers (broadsheet and tabloid)\n",
    "    'bild', 'faz', 'FrankfurterRundschau', \n",
    "    'handelsblatt',\n",
    "    'jungefreiheit',\n",
    "    'junge.welt', 'ihre.sz', 'spiegelonline', \n",
    "    'taz.kommune', 'welt',\n",
    "    #'zeitonline'\n",
    "    # Germany regional newspapers (> 200.000 circulation)\n",
    "    'abendblatt',  'AugsburgerAllgemeine', 'freiepresse',\n",
    "    'HNA', 'ksta.fb', 'NeueWestfaelische', 'rheinpfalz',\n",
    "    'rponline', 'szonline', 'suedwestpresse', 'waz',\n",
    "\n",
    "    # public broadcast\n",
    "    'ARD', 'ZDF', 'ZDFheute',  'radiobremen', 'WDR',\n",
    "    'monitor.wdr', 'hessischerrundfunk', 'SRonline.de',\n",
    "    'SWRAktuell', 'bayerischer.rundfunk', 'fernsehen.rbb',\n",
    "    'rbb24.de', 'NDR.de',\n",
    "    # private broadcast\n",
    "    'sat1tv', 'meinRTL', 'ProSieben', 'kabeleins'\n",
    "    # 39\n",
    "    ] #print(pages)\n",
    "\n",
    "### BUILD DICTS FOR NAME OF PAGE AND NUMERIC ID ###\n",
    "pages_ids_dict = {} # empty dictionary to store results\n",
    "for page in pages:\n",
    "    pages_ids_dict[testFacebookPageData(page, access_token)['name']] \n",
    "                    = testFacebookPageData(page, access_token)['id']\n",
    "\n",
    "pages_ids_dict_backup = pages_ids_dict\n",
    "#build inverse dictionary\n",
    "inv_page_ids_dict = {v: k for k, v in pages_ids_dict.items()} "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def UNIX_ts_from_iso_8601_with_tz_offset(iso_8601):\n",
    "    \"\"\" Convert ISO 8601 with a timezone offset to unix timestamp \"\"\"\n",
    "    # input format: ISO 8601 compliant, preferably 'YYYY-MM-DDTHH:MM:SS+XXXX'\n",
    "    utc_at_epoch = dt.datetime(1970, 1, 1)\n",
    "    if 'T' in iso_8601: iso_8601_dt = dt.datetime.strptime(iso_8601[:-5], '%Y-%m-%dT%H:%M:%S')\n",
    "    else: iso_8601_dt = dt.datetime.strptime(iso_8601, '%Y-%m-%d')\n",
    "    epoch_without_tz_offset = (iso_8601_dt - utc_at_epoch).total_seconds()\n",
    "    if '+' in iso_8601 or iso_8601[-5] == '-': tz_offset = 60 * (60 * int(iso_8601[-4:-2]) + int(iso_8601[-2:]))\n",
    "    else: tz_offset = 0\n",
    "    if iso_8601[-5] == '-': tz_offset = -tz_offset\n",
    "    return int(epoch_without_tz_offset - tz_offset)\n",
    "#---------------------------------------------------------------------------------#\n",
    "def queryFB_def_time (page_id, from_inp_perpcat_fatal_inj, duration_days=7, access_token=access_token, posts_lim=100, comm_lim=100, comments=False, get_data=True):\n",
    "    # former name: getFacebookPageFeedData\n",
    "    \"\"\"retrieves (IF SELECTED) the statuses, (COMMENTS IF SELECTED,) reaction counts and share counts of one page for a specified time range (OTHERWISE DISPLAYS URL TO DO RETRIEVE THIS DATA)\"\"\"\n",
    "    # don't use 'print(queryFB(...)')\n",
    "    # parameter explanation: page_id       = the page's numeric ID\n",
    "    #                        from_inp     = format \"[YYYY]-[MM]-[DD]\"T\"[HH]:[MM]:[SS]+[XXXX]\" time in UTC (XXXX = time zone difference to UTC)\n",
    "    #                        duration_days = format \"XXXX\" (duration of time window in days)\n",
    "    #                        access_token  = [app_id] + \"|\" + [app_secret]\n",
    "    #                        posts_lim     = limit for number of posts\n",
    "    #                        comm_lim      = limit for number of comments retrieved\n",
    "    #                        comments      = True - the comments are included in the query; no comments are retrieved\n",
    "    #                        get_data      = True - the query is executed; False - the URL is displayed as link\n",
    "       \n",
    "    # build URL\n",
    "    base = \"https://graph.facebook.com/v2.11\"\n",
    "    node = \"/\"\n",
    "    since = \"since=\" + str(UNIX_ts_from_iso_8601_with_tz_offset(from_inp_perpcat_fatal_inj[:10]))\n",
    "    plus = \"&\"\n",
    "    until = \"until=\" + str(UNIX_ts_from_iso_8601_with_tz_offset(from_inp_perpcat_fatal_inj[:10])+86400*duration_days)\n",
    "    fields = \"fields=id,created_time,type,link,name,message,likes.limit(0).summary(total_count),reactions.type(LOVE).summary(total_count).limit(0).as(love),reactions.type(WOW).summary(total_count).limit(0).as(wow),reactions.type(HAHA).summary(total_count).limit(0).as(haha),reactions.type(SAD).summary(total_count).limit(0).as(sad),reactions.type(ANGRY).summary(total_count).limit(0).as(angry),shares.limit(0).summary(True)\"\n",
    "    comm_number = \",comments.limit(0).summary(True)\"\n",
    "    commfield = \",comments.limit(%s).summary(True)\" % comm_lim\n",
    "    token = \"access_token=%s\" % access_token\n",
    "    url_p1 = base + node + page_id + node + \"posts?\" + since + plus + until + plus + fields\n",
    "    if comments == True: url = url_p1 + commfield + plus + token\n",
    "    else: url = url_p1 + comm_number + plus + token\n",
    "    \n",
    "    if get_data == False:    # not: return url\n",
    "        print(from_inp_perpcat[-13:], ':', url)       # sample URL = https://graph.facebook.com/v2.11/228735667216/posts?since=2017-05-22T00:00:00+0000&until=2017-05-26T00:00:00&fields=id,created_time,updated_time,message,likes.summary(True)&access_token=107542820013006|ee6JFonBTXB51TmAGmZxCI1o7A8\n",
    "                                        # THIS WORKS! The output - when entering that URL into the browser - is a JSON which is still a bit confusing but seems to work!\n",
    "    else: # retrieve data\n",
    "        data = json.loads(request_until_succeed(url))\n",
    "        #return json.dumps(data, indent=4, sort_keys=True)\n",
    "        return data\n",
    "#---------------------------------------------------------------------------------#\n",
    "def processFBStatus(status, from_inp_perpcat_fatal_inj, UTCplusX = 0): # status = JSON.data[X] // IB\n",
    "    # former name: processFacebookPageFeedStatus\n",
    "    ''' translates the JSON input from [queryFB] into a tuple with the values of interest '''\n",
    "    # The status is a Python dictionary (JSON), so for top-level items, we can call the key. As some items may not exist, we must check their existence first\n",
    "    \n",
    "    status_id = 'missing_value' if 'id' not in status.keys() else status['id']\n",
    "    status_message = 'missing_value' if 'message' not in status.keys() else status['message'].encode('utf-8')\n",
    "    link_name = 'missing_value' if 'name' not in status.keys() else status['name'].encode('utf-8')\n",
    "    status_type = 'missing_value' if 'type' not in status.keys() else status['type']\n",
    "    status_link = 'missing_value' if 'link' not in status.keys() else status['link']\n",
    "    perpcat = from_inp_perpcat_fatal_inj[:13]\n",
    "    fat = from_inp_perpcat_fatal_inj[14:18]\n",
    "    inj = from_inp_perpcat_fatal_inj[19:]\n",
    "    \n",
    "    # Time needs special care since a) it's in UTC and b) it's not easy to use in statistical programs.\n",
    "    status_published = '' if 'created_time' not in status.keys() else dt.datetime.strptime(status['created_time'],'%Y-%m-%dT%H:%M:%S+0000')\n",
    "    status_published = status_published + dt.timedelta(hours = + UTCplusX) # to local time from UTC\n",
    "    status_published = status_published.strftime('%Y-%m-%d %H:%M:%S') # best time format for spreadsheet programs\n",
    "    \n",
    "    # Nested items require chaining dictionary keys.\n",
    "    num_likes = \"-99\" if 'likes' not in status.keys() else status['likes']['summary']['total_count']\n",
    "    num_love = \"-99\" if 'love' not in status.keys() else status['love']['summary']['total_count']\n",
    "    num_wow = \"-99\" if 'wow' not in status.keys() else status['wow']['summary']['total_count']\n",
    "    num_haha = \"-99\" if 'haha' not in status.keys() else status['haha']['summary']['total_count']\n",
    "    num_sad = \"-99\" if 'sad' not in status.keys() else status['sad']['summary']['total_count']\n",
    "    num_angry = \"-99\" if 'angry' not in status.keys() else status['angry']['summary']['total_count']\n",
    "    num_comments = \"-99\" if 'comments' not in status.keys() else status['comments']['summary']['total_count']\n",
    "    num_shares = \"-99\" if 'shares' not in status.keys() else status['shares']['count']\n",
    "    \n",
    "    # return a tuple of all processed data\n",
    "    return (status_id, status_published, perpcat, status_message, link_name, status_type,\n",
    "            status_link, num_likes, num_love, num_wow, num_haha, num_sad, num_angry,\n",
    "            num_comments, num_shares, inj, fat)\n",
    "#---------------------------------------------------------------------------------#\n",
    "filenames_list = [] # this list is needed for the function as empty list under that name\n",
    "def scrapeFB (page_id, from_inp_perpcat_fatal_inj, duration_days=7, access_token=access_token, limit=100, posts_lim=100, comm_lim=100, comments=False, get_data=True, UTCplusX=0):\n",
    "    # former name: scrapeFacebookPageFeedStatus, based on: https://github.com/minimaxir/facebook-page-post-scraper/blob/master/examples/how_to_build_facebook_scraper.ipynb\n",
    "    ''' takes the posts of one page (page_id) and scrapes them. As of now (2018-01-05), time limitation from the queryFB function does not work yet\n",
    "        1. Query each page of Facebook Page Statuses (100 statuses per page) using getFacebookPageFeedData.\n",
    "        2. Process all statuses on that page using processFBStatus and writing the output to a CSV file.\n",
    "        3. Navigate to the next page, and repeat until no more statuses\n",
    "        This function implements both the writing to CSV and page navigation.'''\n",
    "    os.chdir(folder_scraped_statuses)\n",
    "    with open('%s_%s_facebook_statuses.csv' % (from_inp_perpcat_fatal_inj[:13], page_id), 'w') as file:\n",
    "        filenames_list.append('%s_%s_facebook_statuses.csv' % (from_inp_perpcat_fatal_inj[:13], page_id))\n",
    "    #with open('%s_facebook_statuses.csv' % page_id, 'w') as file: # backup\n",
    "        w = csv.writer(file)\n",
    "        w.writerow([\"status_id\", \"publication datetime\", \"perpetrator_categorized\", \"status_message\", \"link_name\", \"status_type\",\n",
    "            \"status_link\", \"num_likes\", \"num_love\", \"num_wow\", \"num_haha\", \"num_sad\", \"num_angry\",\n",
    "            \"num_comments\", \"num_shares\", \"fatalities\", \"injured\"])\n",
    "        \n",
    "        has_next_page = True\n",
    "        num_processed = 0   # keep a count on how many we've processed\n",
    "        scrape_starttime = dt.datetime.now()\n",
    "        \n",
    "        print(\"Scraping Facebook Page: %s for %s and the following week. Scraping starts at: %s\" % (inv_page_ids_dict[page_id], from_inp_perpcat_fatal_inj[:-13], scrape_starttime))\n",
    "        \n",
    "        statuses = queryFB_def_time(page_id, from_inp_perpcat_fatal_inj, duration_days, access_token, posts_lim, comm_lim, comments, get_data) # limit deleted ### queryFB_from_until would need to go here if used\n",
    "        while has_next_page == True:\n",
    "            for status in statuses['data']:\n",
    "                w.writerow(processFBStatus(status, from_inp_perpcat_fatal_inj, UTCplusX=0))\n",
    "                \n",
    "                # output progress occasionally to make sure code is not stalling\n",
    "                num_processed += 1\n",
    "                if num_processed % 100 == 0:\n",
    "                    print(\"%s Statuses Processed: %s\" % (num_processed, dt.datetime.now()))\n",
    "            # if there is no next page, we're done.\n",
    "            if 'paging' in statuses.keys() and 'next' in statuses['paging'].keys():\n",
    "                statuses = json.loads(request_until_succeed(statuses['paging']['next']))\n",
    "            else:\n",
    "                has_next_page = False\n",
    "                print(\"Scraping Facebook Page %s done! %s Statuses Processed in %s \\n\\n\" % (page_id, num_processed, dt.datetime.now() - scrape_starttime))\n",
    "                global total_number # total_number need to be defined globally to def available in all functions\n",
    "                total_number += num_processed\n",
    "                os.chdir(folder_Webscraping)\n",
    "#---------------------------------------------------------------------------------#\n",
    "def scrapingFB_nested (ids_list, ls_date_perpabbr_fat_inj, duration=7, comments=False):\n",
    "    ''' implements the scraping, nested with pages and time windows to scrape [duration in days]'''\n",
    "    global total_number\n",
    "    total_number = 0\n",
    "    # input is a (str) list of the pages' IDs that need to be scraped and the beginnings of the time windows from which onwards one week will be scraped per instant\n",
    "    starting_time_nested_scraping = dt.datetime.now()\n",
    "    idx = int()\n",
    "    for iterator in ls_date_perpabbr_fat_inj:   # execute the scraping for every time window in the list 'parseddates_ls'\n",
    "        for idx in ids_list:              # execute the scraping for every page in the list 'pages_ids_ls'\n",
    "            scrapeFB(idx, iterator, duration, access_token, 100, 100, comments) #, UTCplusX=0)\n",
    "    print('\\n\\n\\n________________________________________________LOOP DONE. SCRAPING COMPLETED!________________________________________________\\nScraped %s statuses in %s' % (total_number, (dt.datetime.now() - starting_time_nested_scraping)))\n",
    "#---------------------------------------------------------------------------------#"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data on attacks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the dates of the attacks from the GTD output\n",
    "# the data was retrieved from: http://www.start.umd.edu/gtd/search/ with the following parameters:\n",
    "            # WHEN                2001-09-12 to 2016-12-31 [the last possible date included in the data base]\n",
    "            #                         (the data is downloaded in two chunks, from 2001-09-12 to 2009-12-31 and 2010-01-01 to 2016-12-31)\n",
    "            # REGION              Western Europe\n",
    "            # COUNTRY             [All]\n",
    "            # PERPETRATOR GROUP, WEAPON TYPE, ATTACK TYPE, TARGET TYPE  [All]\n",
    "            # TERRORISM CRITERIA  Require Criteria I-III, exclude ambiguous cases, exclude unsuccessful attacks\n",
    "            # CASUALTIES          Casualty type: both injured and fatalities; Number of casualties: any number\n",
    "            # in addition, the the files can be found in the online appendix \n",
    "\n",
    "# load data\n",
    "csv_attacks = \"replace with path\\#MPhil_thesis_Appendix\\2001-09-12_2016-12-31_GTD-Export_Western_Europe.csv\" #### ANONYMIZED ####\n",
    "attacks_df = pd.read_csv(csv_attacks, delimiter=',', header=0)\n",
    "attacks_df.insert(0, 'DATE_STR', attacks_df.DATE)\n",
    "attacks_df.DATE = pd.to_datetime(attacks_df.DATE) # convert date to datetime\n",
    "attacks_df.set_index('DATE', inplace=True) # set datetime index 'DATE'\n",
    "del csv_attacks # clean up temporary data sets\n",
    "\n",
    "### CLEAN AND CATEGORIZE PERPETRATORS ###\n",
    "# define categorization\n",
    "dict_categorization = dict_no_hash = {\n",
    "    \"Protestant extremists\": \"SEPARATIST (IRE, LOY)\",\n",
    "    \"Basque Fatherland and Freedom (ETA)\": \"SEPARATIST (NON-UK)\",\n",
    "    \"Revolutionary Solidarity\": \"LEFT\",\n",
    "    \"Unknown\": \"UNKNOWN\",\n",
    "    \"Ulster Volunteer Force (UVF)\": \"SEPARATIST (IRE, LOY)\",\n",
    "    \"Irish National Liberation Army (INLA)\": \"SEPARATIST (IRE, REP)\",\n",
    "    \"First of October Antifascist Resistance Group (GRAPO)\": \"LEFT\",\n",
    "    \"Revolutionary People's Struggle (ELA)\": \"LEFT\",\n",
    "    \"Terra Lliure\": \"IRRELEVANT TO RQ\",\n",
    "    \"Ulster Freedom Fighters (UFF)\": \"SEPARATIST (IRE, LOY)\",\n",
    "    \"November 17 Revolutionary Organization (N17RO)\": \"LEFT\",\n",
    "    \"Corsican National Liberation Front (FLNC)\": \"SEPARATIST (NON-UK)\",\n",
    "    \"Iparretarrak (IK)\": \"IRRELEVANT TO RQ\",\n",
    "    \"Red Army Faction (RAF)\": \"LEFT\",\n",
    "    \"Armed Falange\": \"IRRELEVANT TO RQ\",\n",
    "    \"Serbian extremists\": \"IRRELEVANT TO RQ\",\n",
    "    \"People's Rebellion\": \"RIGHT\",\n",
    "    \"Serbian Nationalists\": \"IRRELEVANT TO RQ\",\n",
    "    \"Iranian extremists\": \"IRRELEVANT TO RQ\",\n",
    "    \"Serbian guerrillas\": \"IRRELEVANT TO RQ\",\n",
    "    \"Resistenza\": \"LEFT\",\n",
    "    \"Irish People's Liberation Organization (IPLO)\": \"SEPARATIST (IRE, REP)\",\n",
    "    \"Neo-Nazi extremists\": \"RIGHT\",\n",
    "    \"Action Group for the Destruction of the Police State\": \"LEFT\",\n",
    "    \"Iranian exiles\": \"UNKNOWN\",\n",
    "    \"Movement for the Protection of Jerusalem\": \"RIGHT\",\n",
    "    \"Kurdistan Workers' Party (PKK)\": \"SEPARATIST (NON-UK)\",\n",
    "    \"Mafia\": \"MAFIA\",\n",
    "    \"Anti-Iran Government Exiles\": \"UNKNOWN\",\n",
    "    \"Latvian Republic Volunteer Troops\": \"IRRELEVANT TO RQ\",\n",
    "    \"Albanian exiles\": \"IRRELEVANT TO RQ\",\n",
    "    \"French National\": \"IRRELEVANT TO RQ\",\n",
    "    \"White extremists\": \"RIGHT\",\n",
    "    \"Hungarian Skin Head Group\": \"RIGHT\",\n",
    "    \"Iranians\": \"IRRELEVANT TO RQ\",\n",
    "    \"Polish Skinheads\": \"RIGHT\",\n",
    "    \"Red Commandos\": \"SEPARATIST (IRE, LOY)\",\n",
    "    \"Jewish Extremists\": \"RELIGIOUS (JEWISH)\",\n",
    "    \"Greek Anarchists' Union\": \"LEFT\",\n",
    "    \"NaN\": \"UNCATEGORIZED\",\n",
    "    \"Left-Wing Militants\": \"LEFT\",\n",
    "    \"Youths\": \"UNKNOWN\",\n",
    "    \"Animal Rights extremists\": \"ENVIRONMENTALIST\",\n",
    "    \"Right-wing extremists\": \"RIGHT\",\n",
    "    \"Right-Wing Youths\": \"RIGHT\",\n",
    "    \"Bavarian Liberation Army\": \"IRRELEVANT TO RQ\",\n",
    "    \"Belarusian Liberation Army\": \"IRRELEVANT TO RQ\",\n",
    "    \"Israeli extremists\": \"IRRELEVANT TO RQ\",\n",
    "    \"Armed Islamic Group (GIA)\": \"ISLAMIST\",\n",
    "    \"Algerian Moslem Fundamentalists\": \"ISLAMIST\",\n",
    "    \"Al-Gama'at al-Islamiyya (IG)\": \"ISLAMIST\",\n",
    "    \"International Justice Group (Gama'a al-Adela al-Alamiya)\": \"ISLAMIST\",\n",
    "    \"Corsican Separatists\": \"SEPARATIST (NON-UK)\",\n",
    "    \"Turkish Revenge Brigade\": \"IRRELEVANT TO RQ\",\n",
    "    \"Fighting Guerrilla Formation\": \"IRRELEVANT TO RQ\",\n",
    "    \"Serbs\": \"SEPARATIST (NON-UK)\",\n",
    "    \"Loyalist Volunteer Forces (LVF)\": \"SEPARATIST (IRE, LOY)\",\n",
    "    \"Continuity Irish Republican Army (CIRA)\": \"SEPARATIST (IRE, REP)\",\n",
    "    \"Revolutionary Nuclei\": \"IRRELEVANT TO RQ\",\n",
    "    \"Orange Volunteers (OV)\": \"IRRELEVANT TO RQ\",\n",
    "    \"Red Hand Defenders (RHD)\": \"SEPARATIST (IRE, LOY)\",\n",
    "    \"Combat 18/ White Wolves (UK)\": \"RIGHT\",\n",
    "    \"Loyalists\": \"SEPARATIST (IRE, LOY)\",\n",
    "    \"Red Brigades Fighting Communist Party (BR-PCC)\": \"LEFT\",\n",
    "    \"Hells Angels/ Nationalsocialistisk Front (NSF)\": \"RIGHT\",\n",
    "    \"Breton Liberation Front (FLB)\": \"SEPARATIST (NON-UK)\",\n",
    "    \"Irish Republican Extremists\": \"SEPARATIST (IRE, REP)\",\n",
    "    \"Former Soldiers/Police\": \"IRRELEVANT TO RQ\",\n",
    "    \"National Socialist Underground\": \"RIGHT\",\n",
    "    \"Real Irish Republican Army (RIRA)\": \"SEPARATIST (IRE, REP)\",\n",
    "    \"Anti-Semitic extremists\": \"RIGHT\",\n",
    "    \"Revolutionary Perspective\": \"LEFT\",\n",
    "    \"Animal Liberation Front (ALF)\": \"ENVIRONMENTALIST\",\n",
    "    \"Armata Corsa\": \"IRRELEVANT TO RQ\",\n",
    "    \"Haika\": \"IRRELEVANT TO RQ\",\n",
    "    \"Revolutionary Proletarian Initiative Nuclei (NIPR)\": \"LEFT\",\n",
    "    \"Anti-Imperialist Territorial Nuclei (NTA)\": \"LEFT\",\n",
    "    \"Revolutionary Violence Units\": \"SEPARATIST (NON-UK)\",\n",
    "    \"Anarchist Liberation Brigade\": \"LEFT\",\n",
    "    \"Group of Carlo Giuliani\": \"IRRELEVANT TO RQ\",\n",
    "    \"Anarchist Squad\": \"LEFT\",\n",
    "    \"Neo-Fascists\": \"RIGHT\",\n",
    "    \"Catholic Reaction Force\": \"IRRELEVANT TO RQ\",\n",
    "    \"Association Totalement Anti-Guerre (ATAG)\": \"IRRELEVANT TO RQ\",\n",
    "    \"Red Hand Defenders (RHD)/ Ulster Freedom Fighters (UFF)\": \"IRRELEVANT TO RQ\",\n",
    "    \"Red Brigades Fighting Communist Union (BR-UCC)\": \"LEFT\",\n",
    "    \"Popular Resistance (Laiki Antistasi)\": \"RIGHT\",\n",
    "    \"Sardinian Autonomy Movement\": \"IRRELEVANT TO RQ\",\n",
    "    \"New Revolutionary Popular Struggle (NELA)\": \"LEFT\",\n",
    "    \"Rabid Brothers of Giuliani\": \"IRRELEVANT TO RQ\",\n",
    "    \"Democratic Iraqi Opposition of Germany\": \"IRRELEVANT TO RQ\",\n",
    "    \"CCCCC\": \"IRRELEVANT TO RQ\",\n",
    "    \"Resistenza Corsa\": \"IRRELEVANT TO RQ\",\n",
    "    \"Supporters of Johnny Adair\": \"IRRELEVANT TO RQ\",\n",
    "    \"Proletarian Nuclei for Communism\": \"LEFT\",\n",
    "    \"Anti IRQ War\": \"UNKNOWN\",\n",
    "    \"Revolutionary Struggle\": \"LEFT\",\n",
    "    \"Loyalist Action Force\": \"SEPARATIST (IRE, LOY)\",\n",
    "    \"Informal Anarchist Federation\": \"LEFT\",\n",
    "    \"Anarchists\": \"LEFT\",\n",
    "    \"Abu Hafs al-Masri Brigades\": \"ISLAMIST\",\n",
    "    \"Moroccan extremists\": \"IRRELEVANT TO RQ\",\n",
    "    \"Resistance Cell\": \"LEFT\",\n",
    "    \"Hofstad Network\": \"ISLAMIST\",\n",
    "    \"Global Intifada\": \"ISLAMIST\",\n",
    "    \"Secret Organization of al-Qaida in Europe\": \"ISLAMIST\",\n",
    "    \"Al-Qaida Organization for Jihad in Sweden\": \"ISLAMIST\",\n",
    "    \"Anti-State Justice\": \"LEFT\",\n",
    "    \"Revolutionary Action of Liberation\": \"LEFT\",\n",
    "    \"Solidarity with imprisoned members of Action Directe (AD)\": \"IRRELEVANT TO RQ\",\n",
    "    \"Athens and Thessaloniki Arsonist Nuclei\": \"IRRELEVANT TO RQ\",\n",
    "    \"Al-Qaida in Iraq\": \"ISLAMIST\",\n",
    "    \"Dissident Republicans\": \"SEPARATIST (IRE, REP)\",\n",
    "    \"Muslim extremists\": \"ISLAMIST\",\n",
    "    \"Real Irish Republican Army (RIRA)/ Irish National Liberation Army (INLA)\": \"SEPARATIST (IRE, REP)\",\n",
    "    \"Anti-Independence extremists\": \"IRRELEVANT TO RQ\",\n",
    "    \"Conspiracy of Cells of Fire\": \"IRRELEVANT TO RQ\",\n",
    "    \"Forbidden Blockade (Greece)\": \"UNKNOWN\",\n",
    "    \"Anti-Democratic Struggle\": \"UNKNOWN\",\n",
    "    \"LW\": \"UNKNOWN\",\n",
    "    \"Hutu extremists\": \"IRRELEVANT TO RQ\",\n",
    "    \"Oglaigh na hEireann\": \"IRRELEVANT TO RQ\",\n",
    "    \"Gangs of Conscience\": \"IRRELEVANT TO RQ\",\n",
    "    \"Popular Will (Greece)\": \"RIGHT\",\n",
    "    \"Incendiary Committee of Solidarity for Detainees\": \"UNKNOWN\",\n",
    "    \"Sect of Revolutionaries (Greece)\": \"LEFT\",\n",
    "    \"Revolutionary Liberation Action (Epanastatiki Apelevtherotiki Drasi) - Greece\": \"IRRELEVANT TO RQ\",\n",
    "    \"Nihilists Faction\": \"IRRELEVANT TO RQ\",\n",
    "    \"Basque Separatists\": \"SEPARATIST (NON-UK)\",\n",
    "    \"Alexandros Grigoropoulos Anarchist Attack Group\": \"LEFT\",\n",
    "    \"Armed Revolutionary Action (ENEDRA)\": \"IRRELEVANT TO RQ\",\n",
    "    \"Attack Teams for the Dissolution of the Nation (Greece)\": \"UNKNOWN\",\n",
    "    \"Deniers of Holidays\": \"IRRELEVANT TO RQ\",\n",
    "    \"Zero Tolerance\": \"IRRELEVANT TO RQ\",\n",
    "    \"Illuminating Paths of Solidarity\": \"IRRELEVANT TO RQ\",\n",
    "    \"Militant Forces Against Huntingdon\": \"IRRELEVANT TO RQ\",\n",
    "    \"Council for the Destruction of Order\": \"IRRELEVANT TO RQ\",\n",
    "    \"Hoodie Wearers\": \"IRRELEVANT TO RQ\",\n",
    "    \"Anarchist Action (CA / United States)\": \"LEFT\",\n",
    "    \"Black and Red Anarchist and Anti-Authoritarians Initiative (Greece)\": \"LEFT\",\n",
    "    \"Paramilitary members\": \"IRRELEVANT TO RQ\",\n",
    "    \"Revolutionary Continuity\": \"LEFT\",\n",
    "    \"Sisters in Arms\": \"IRRELEVANT TO RQ\",\n",
    "    \"Rebellious Group Lambros Foundas\": \"IRRELEVANT TO RQ\",\n",
    "    \"Groups for Dissemination of Revolutionary Theory and Action\": \"LEFT\",\n",
    "    \"Real Ulster Freedom Fighters (UFF) - Northern Ireland\": \"SEPARATIST (IRE, LOY)\",\n",
    "    \"Iraqi extremists\": \"IRRELEVANT TO RQ\",\n",
    "    \"Animal Rights Militia\": \"ENVIRONMENTALIST\",\n",
    "    \"Provisional RSPCA\": \"IRRELEVANT TO RQ\",\n",
    "    \"Hekla Reception Committee-Initiative for More Social Eruptions\": \"IRRELEVANT TO RQ\",\n",
    "    \"Sunni Muslim extremists\": \"ISLAMIST\",\n",
    "    \"Friends of Loukanikos\": \"LEFT\",\n",
    "    \"Republican Action Against Drugs (RAAD)\": \"IRRELEVANT TO RQ\",\n",
    "    \"Jihadi-inspired extremists\": \"ISLAMIST\",\n",
    "    \"International Revolutionary Front\": \"RIGHT\",\n",
    "    \"Hezbollah\": \"IRRELEVANT TO RQ\",\n",
    "    \"Irish Republican Army (IRA)\": \"SEPARATIST (IRE, REP)\",\n",
    "    \"The New Irish Republican Army\": \"SEPARATIST (IRE, REP)\",\n",
    "    \"Militant Minority (Greece)\": \"IRRELEVANT TO RQ\",\n",
    "    \"Militant Minority (Greece)/ Circle of Violators/Nucleus Lovers of Anomy\": \"IRRELEVANT TO RQ\",\n",
    "    \"People's Fighter Group (Band of Popular Fighters)\": \"IRRELEVANT TO RQ\",\n",
    "    \"Wild Freedom/ Instigators of Social Explosion\": \"IRRELEVANT TO RQ\",\n",
    "    \"Wild Freedom\": \"IRRELEVANT TO RQ\",\n",
    "    \"Angry Brigade\": \"IRRELEVANT TO RQ\",\n",
    "    \"Real Ulster Freedom Fighters (UFF) - Northern Ireland/ Loyalist Volunteer Forces (LVF)\": \"SEPARATIST (IRE, LOY)\",\n",
    "    \"Informal Anarchist Federation/ Int'l Revolutionary Front\": \"LEFT\",\n",
    "    \"Overall Deniers of Joining the Existing\": \"IRRELEVANT TO RQ\",\n",
    "    \"Epanastatiki Anatropi (Revolutionary Overthrow)\": \"IRRELEVANT TO RQ\",\n",
    "    \"Anti-Clerical Pro-Sex Toys Group\": \"IRRELEVANT TO RQ\",\n",
    "    \"Proletariat Self-defense Groups\": \"LEFT\",\n",
    "    \"English Defense League (EDL)\": \"RIGHT\",\n",
    "    \"Anti-Muslim extremists\": \"RIGHT\",\n",
    "    \"Borderless Solidarity Cell (BSC)\": \"LEFT\",\n",
    "    \"Detonators of Social Uprisings\": \"IRRELEVANT TO RQ\",\n",
    "    \"Comite d'Action Viticole\": \"IRRELEVANT TO RQ\",\n",
    "    \"Angry Foxes Cell/ All Coppers Are Bastards\": \"IRRELEVANT TO RQ\",\n",
    "    \"Powers of the Revolutionary Arc\": \"IRRELEVANT TO RQ\",\n",
    "    \"Mateo Morral Insurrectionist Commandos\": \"LEFT\",\n",
    "    \"Resistencia Galega\": \"IRRELEVANT TO RQ\",\n",
    "    \"Militant People's Revolutionary Forces\": \"LEFT\",\n",
    "    \"Corsican Nationalists\": \"IRRELEVANT TO RQ\",\n",
    "    \"Jewish Defense League (JDL)\": \"IRRELEVANT TO RQ\",\n",
    "    \"Group of Popular Fighters\": \"IRRELEVANT TO RQ\",\n",
    "    \"The Justice Department\": \"IRRELEVANT TO RQ\",\n",
    "    \"National Liberation Front of Provence (FLNP)\": \"IRRELEVANT TO RQ\",\n",
    "    \"Islamic State of Iraq and the Levant (ISIL)\": \"ISLAMIST\",\n",
    "    \"Organization for Revolutionary Self Defense\": \"LEFT\",\n",
    "    \"German Resistance Movement\": \"RIGHT\",\n",
    "    \"Random Anarchists\": \"LEFT\",\n",
    "    \"Left-wing extremists\": \"LEFT\",\n",
    "    \"Proletarian Solidarity\": \"LEFT\",\n",
    "    \"The Irish Volunteers\": \"SEPARATIST (IRE, REP)\",\n",
    "    \"Yazidi extremists\": \"IRRELEVANT TO RQ\",\n",
    "    \"Anarchist Anti-Capitalist Action Group\": \"LEFT\",\n",
    "    \"Proletarian Assault Group\": \"LEFT\",\n",
    "    \"Informal Anarchist Federation/ Earth Liberation Front\": \"LEFT\",\n",
    "    \"Free Network South (Freies Netz Sued)\": \"RIGHT\",\n",
    "    \"Al-Qaida in the Arabian Peninsula (AQAP)\": \"ISLAMIST\",\n",
    "    \"Anarchist Commando Nestor Makhno Group\": \"LEFT\",\n",
    "    \"Anti-Immigrant extremists\": \"RIGHT\",\n",
    "    \"Patriotic Europeans against the Islamization of the West (PEGIDA)\": \"RIGHT\",\n",
    "    \"Nihilistic Patrol and Neighborhood Arsonists\": \"LEFT\",\n",
    "    \"International Revolutionary Front/ Informal Anarchist Federation\": \"RIGHT\",\n",
    "    \"Earth Liberation Front (ELF)\": \"ENVIRONMENTALIST\",\n",
    "    \"Revolutionary Cells Network (SRN)\": \"IRRELEVANT TO RQ\",\n",
    "    \"The Third Way (Der III. Weg)\": \"RIGHT\",\n",
    "    \"Rubicon (Rouvikonas)\": \"IRRELEVANT TO RQ\",\n",
    "    \"Freital Group\": \"RIGHT\",\n",
    "    \"Informal Anarchist Federation/ International Revolutionary Front\": \"LEFT\",\n",
    "    \"Anarchist Cell Acca (C.A.A.)\": \"LEFT\",\n",
    "    \"Ramiro Ledesma Social Centre\": \"IRRELEVANT TO RQ\",\n",
    "    \"Irish National Liberation Army (INLA) / New IRA\": \"SEPARATIST (IRE, REP)\",\n",
    "    \"Les Casseurs\": \"IRRELEVANT TO RQ\",\n",
    "    \"Unrepentant Anarchists\": \"LEFT\",\n",
    "    \"Lone Wolves of Radical, Autonomous, Militant National Socialism\": \"RIGHT\",\n",
    "    \"Alde Hemendik Movement\": \"IRRELEVANT TO RQ\",\n",
    "    \"No Borders Group\": \"LEFT\",\n",
    "    \"Bahoz\": \"SEPARATIST (NON-UK)\",\n",
    "    \"Nordic Resistance Movement\": \"RIGHT\",\n",
    "    \"Angry Foxes Cell\": \"LEFT\"\n",
    "    }\n",
    "\n",
    "dict_abbreviation_cat = {\n",
    "    'RIGHT' : '_RI', 'LEFT': '_LE', 'SEPARATIST (NON-UK)': '_SE', \n",
    "    'SEPARATIST (IRE, REP)': '_SR', 'SEPARATIST (IRE, LOY)': '_SL',\n",
    "    'ISLAMIST': '_IS', 'UNKNOWN' : '_UN', 'ENVIRONMENTALIST': '_EN',\n",
    "    'IRRELEVANT TO RQ': '_IR'\n",
    "    }\n",
    "\n",
    "PERP = pd.Series(attacks_df['PERPETRATOR 1'])\n",
    "PERP_CATEGORIZED = PERP.map(dict_categorization)\n",
    "if 'PERPETRATOR CATEGORIZED' not in attacks_df.columns: attacks_df.insert(2, 'PERPETRATOR CATEGORIZED', PERP_CATEGORIZED)\n",
    "#attacks_df.head(5)\n",
    "\n",
    "CAT = pd.Series(attacks_df['PERPETRATOR CATEGORIZED'])\n",
    "CAT_2L = CAT.map(dict_abbreviation_cat)\n",
    "if 'ABBREVIATED CATEGORIZATION' not in attacks_df.columns: attacks_df.insert(0, 'ABBREVIATED CATEGORIZATION', CAT_2L)\n",
    "\n",
    "ls_categories = ['RIGHT', 'LEFT', 'SEPARATIST (NON-UK)', \n",
    "                 'SEPARATIST (IRE, REP)', 'SEPARATIST (IRE, LOY)',\n",
    "                 'ISLAMIST', 'UNKNOWN', 'ENVIRONMENTALIST',\n",
    "                 'IRRELEVANT TO RQ']\n",
    "del dict_abbreviation_cat\n",
    "\n",
    "### CLEAN DATAFRAME ###\n",
    "attacks_df = attacks_df[attacks_df['PERPETRATOR 1'] != 'Unknown'] # drop line if perpetrator is unknown\n",
    "attacks_df['STR_FATALITIES'] = attacks_df['FATALITIES'].copy() # create \"FATALITIES\" column as string\n",
    "attacks_df['STR_INJURED'] = attacks_df['INJURED'].copy() # create \"INJURED\" column as string\n",
    "attacks_df['FATALITIES'] = pd.to_numeric(attacks_df['FATALITIES'], errors='coerce')\n",
    "attacks_df['INJURED'] = pd.to_numeric(attacks_df['INJURED'], errors='coerce')\n",
    "# drop columns\n",
    "attacks_df.drop(attacks_df.columns[14:25], axis=1, inplace=True) #drop columns that are not of interest\n",
    "attacks_df.drop(attacks_df.columns[7:12], axis=1, inplace=True) #drop columns that are not of interest\n",
    "attacks_df.drop(attacks_df.columns[6], axis=1, inplace=True) #drop perpetrator 1, too\n",
    "#attacks_df.drop(attacks_df.columns[1], axis=1, inplace=True) #drop DATE_STR done later, after filtering\n",
    "attacks_df['FAT & INJ'] = attacks_df['FATALITIES'] + attacks_df['INJURED']\n",
    "# reformat strings\n",
    "attacks_df['STR_FATALITIES'] = attacks_df['STR_FATALITIES'].apply('{:0>4}'.format) # fill up with leadin zeros to 4 numbers\n",
    "attacks_df['STR_INJURED'] = attacks_df['STR_INJURED'].apply('{:0>4}'.format) # fill up with leadin zeros to 4 numbers\n",
    "\n",
    "\n",
    "### FILTERING THE DATA SET ###\n",
    "attacks_of_interest = attacks_df[           # only attacks of RIGHT/ LEFT/ ISLAMIST/ SEPPARATIST (UK) AND WITH AT LEAST ONE CASUALTY\n",
    "        (\n",
    "            (attacks_df['PERPETRATOR CATEGORIZED'] == 'blank') # placeholder: impossible condition for coding convenience\n",
    "            | (attacks_df['PERPETRATOR CATEGORIZED'] == 'RIGHT')\n",
    "            | (attacks_df['PERPETRATOR CATEGORIZED'] == 'LEFT')\n",
    "            | (attacks_df['PERPETRATOR CATEGORIZED'] == 'SEPARATIST (IRE, REP)')\n",
    "            | (attacks_df['PERPETRATOR CATEGORIZED'] == 'SEPARATIST (IRE, LOY)')\n",
    "            | (attacks_df['PERPETRATOR CATEGORIZED'] == 'ISLAMIST')\n",
    "            #| (attacks_df['PERPETRATOR CATEGORIZED'] == 'SEPARATIST (NON-UK)')\n",
    "            #| (attacks_df['PERPETRATOR CATEGORIZED'] == 'ENVIRONMENTALIST')\n",
    "            #| (attacks_df['PERPETRATOR CATEGORIZED'] == 'UNKNOWN') \n",
    "            #| (attacks_df['PERPETRATOR CATEGORIZED'] == 'IRRELEVANT TO RQ')\n",
    "        )\n",
    "        &\n",
    "        (\n",
    "            (attacks_df['FAT & INJ'] >= 100000) # placeholder: impossible condition for coding convenience\n",
    "            #| (attacks_df['FAT & INJ'] >= 2)\n",
    "            #| (attacks_df['INJURED'] >10)\n",
    "            | (attacks_df['FATALITIES'] > 0)\n",
    "            #& (attacks_df['FAT & INJ'] > 1)\n",
    "        )\n",
    "        ]\n",
    "del attacks_df, CAT, CAT_2L # clean up\n",
    "\n",
    "### GENERATE DATE AND ABBREVIATION FOR PERPETRATOR ###\n",
    "# EXTRACT DATES, ABBREVATED PERPETRATOR, FATALITIES, AND INJURED #\n",
    "attacks_of_interest.sort_index(inplace=True)\n",
    "dates_ls = attacks_of_interest['DATE_STR'].tolist()\n",
    "perpcat_abbr_list = attacks_of_interest['ABBREVIATED CATEGORIZATION']\n",
    "inj_ls = attacks_of_interest['STR_INJURED']\n",
    "fat_ls = attacks_of_interest['STR_FATALITIES']\n",
    "\n",
    "parseddates_ls = []\n",
    "for i in dates_ls: # parse dates\n",
    "    parseddate = dt.datetime.strftime(dt.datetime.strptime(i[0:10],'%d/%m/%Y'),'%Y-%m-%d')\n",
    "    parseddates_ls.append(str(parseddate) + i[10:])\n",
    "    del parseddate\n",
    "assert len(parseddates_ls) == len(perpcat_abbr_list) & len(perpcat_abbr_list) == len(inj_ls) & len(inj_ls) == len(fat_ls)\n",
    "date_perp_abbr_ls = [x+y for x,y in zip(parseddates_ls,perpcat_abbr_list)] # redundant; only as backup\n",
    "ls_date_perpabbr_fat_inj = [w + x + '_' + y + '_' + z for w,x,y,z in zip(parseddates_ls,perpcat_abbr_list, fat_ls, inj_ls)]\n",
    "assert len(date_perp_abbr_ls) == len(parseddates_ls) == len(perpcat_abbr_list) # redundant; only as backup\n",
    "assert len(ls_date_perpabbr_fat_inj) == len(parseddates_ls)\n",
    "\n",
    "print('There are ' + str(len(date_perp_abbr_ls)) + ' attacks of interest, for which the week following the attack will be scraped.') # print(parseddates_ls[0:])\n",
    "\n",
    "pages_ids_ls = []\n",
    "for k in pages_ids_dict:\n",
    "    list_item = pages_ids_dict[k]\n",
    "    pages_ids_ls.append(list_item)\n",
    "assert len(pages_ids_dict) == len(pages_ids_ls) # ensure the list contains as many items as the dictionary contains key/ value pairs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Scraping Facebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# analysis of the posts scrapted for the entire time period shows that the first posts on terrorist attacks were in 2010; therefore, the searched time frame is restriced to this time frame to save bandwidth\n",
    "\n",
    "### SCRAPE FB WITH THE PAGES AND DATES SPECIFIED ###\n",
    "scrapingFB_nested(pages_ids_ls, ls_date_perpabbr_fat_inj[28:], duration=7, comments=True) #temp_date_perp_abbr_ls) # this command executes the scraping and takes about 5min to execute"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Importing and cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### LOAD DATA INTO PANDAS ###\n",
    "posts = pd.DataFrame() # empty pandas data frame\n",
    "for filename in filenames_list_man: # imports the files one by one into one data frame\n",
    "    os.chdir(folder_scraped_statuses)\n",
    "    df = pd.read_csv(filename, delimiter=',', converters={'link_name': str, 'status_message': str}, na_values=['-99'],\n",
    "                     encoding = \"ISO-8859-1\") # other values (e.g. 'missing_value', as assigned) commented out to deal with missing values in another way\n",
    "    pageid = filename.split('_')[2] # take 'filename' from 'filename_list', take what's before the '_'\n",
    "    df.insert(0, 'page_id', str(pageid)) # append this as the new first column called \"page_id\" to the data frame\n",
    "    #print(df.head())#\n",
    "    posts = posts.append(df)\n",
    "    os.chdir(folder_Webscraping)\n",
    "    \n",
    "### CLEAN AND ORDER DATA ###\n",
    "print('The initial dimensions of the DataFrame are:    ' + str(posts.shape) + \".\") #The initial dimensions of the DataFrame are:    (5847, 16)\n",
    "posts['status_id'] = posts['status_id'].str.replace('\\d+_', '').to_frame() # delete any number of digits before the '_' in the column post['status_ids']\n",
    "posts['attack_date'], posts['attacker_categorized'] = posts['perpetrator_categorized'].str.split('_', 1).str # make attack date_perpetrator into two columns\n",
    "\n",
    "# include page name in addition to page ID to ensure better readability\n",
    "if 'page_name' in posts.columns: del posts['page_name'] # if code is run multiple times\n",
    "posts.insert(0, 'page_name', posts['page_id']) # append this as the new first column called \"page_id\"\n",
    "posts[\"page_name\"].replace(inv_page_ids_dict, inplace=True) # include name of page instead of numeric ID\n",
    "#print(posts.columns)\n",
    "posts_reordered = posts[['page_name', 'page_id', 'status_id', # reorder columns\n",
    "                         'attack_date', 'attacker_categorized', 'publication datetime',\n",
    "                         'status_type', 'status_message', 'link_name', 'status_link',\n",
    "                         'num_likes', 'num_love', 'num_wow', 'num_haha', 'num_sad', 'num_angry', \n",
    "                         'num_comments', 'num_shares', 'perpetrator_categorized', \n",
    "                         'fatalities', 'injured']]\n",
    "posts_reordered = posts_reordered.rename(columns={'publication datetime': 'publication_datetime_str'}) #rename column\n",
    "assert posts.shape == posts_reordered.shape\n",
    "del posts_reordered['perpetrator_categorized'], posts #clean up\n",
    "\n",
    "# DELETE DUPLICATES AND MISSING VALUES #\n",
    "posts_reordered.drop_duplicates(keep='first', inplace=True) #if only selecting on the status ID: 'status_id', keep='first', inplace=True) [not advisable since one post might belong to two instances]\n",
    "\n",
    "#del miss_values\n",
    "posts_reordered.dropna(thresh=14, inplace=True) # it seems i can only drop all... need to fix this    \n",
    "posts_reordered['status_message'].dropna(inplace=True) # inplace needs to be True if not assigning anew, otherwise needs False\n",
    "posts_reordered['link_name'].dropna(inplace=True) # inplace needs to be True if not assigning anew, otherwise needs False\n",
    "\n",
    "## INDEX ##\n",
    "dict_remap = {'RI': 'RIGHT', 'LE': 'LEFT', 'SL': 'SEP-LOY', 'SR': 'SEP-REP', 'IS': 'ISLAMIST'}\n",
    "posts_reordered['attacker_categorized'] = posts_reordered['attacker_categorized'].map(dict_remap)\n",
    "del dict_remap # clean up\n",
    "posts_reordered['attack_date'] = pd.to_datetime(posts_reordered['attack_date'], errors='ignore') # convert column to datetime\n",
    "posts_reordered['status_id'] = pd.to_numeric(posts_reordered['status_id'], errors='ignore') # convert column to numeric\n",
    "posts_reordered.sort_values(by=['attack_date', 'status_id'], ascending=True, inplace=True, na_position='first')\n",
    "\n",
    "posts_reordered.reset_index(inplace=True)\n",
    "if 'index' in posts_reordered.columns: del posts_reordered['index']\n",
    "if 'level_0' in posts_reordered.columns: del posts_reordered['level_0'] # this indexing was chosen to facilitate the string search below that runs into difficulties (lexsort) with multilevel indexes\n",
    "\n",
    "### INSPECTING AND CLEANING DATA ###\n",
    "# convert data types\n",
    "cols_to_convert = ['page_id', 'num_likes', 'num_love', 'num_wow', 'num_haha', \n",
    "                   'num_sad', 'num_angry', 'num_comments', 'num_shares', \"injured\", \"fatalities\"]\n",
    "posts_reordered[cols_to_convert] = posts_reordered[cols_to_convert].apply(pd.to_numeric, errors='ignore', axis=1) # convert to numeric\n",
    "posts_reordered['publication_datetime_dt'] = pd.to_datetime(posts_reordered['publication_datetime_str'], errors='ignore') # convert column to datetime\n",
    "del cols_to_convert # clean up\n",
    "\n",
    "print(\"After cleaning, the DataFrame's dimensions are: \" + str(posts_reordered.shape) + '. This should be [initial number of columns] + 3 (split of attack_date and attacker_categorized, added page_name and datetime_publication_dt)')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#define keywords for which to search the posts\n",
    "keywords = ['terror',\n",
    "            'attack', 'Angriff', 'Anschlag', 'Anschläge',\n",
    "            'shooting','Schießerei',\n",
    "            'knife','Messer',\n",
    "            'Islamist', 'jihad', 'Dschihad'\n",
    "            'Islam',\n",
    "            'Extremist'\n",
    "           ]\n",
    "\n",
    "negative_keywords = ['cyber', # topics\n",
    "                     'boko', # groups\n",
    "                     'Chad', 'Niger', 'Cameroon', 'Tunis', 'Iraq', 'Afghanistan',\n",
    "                     'Tschad', 'Kamerun', 'Tunesien', 'Irak',\n",
    "                     \"Weltkrieg\", \"World War\"\n",
    "                    ]\n",
    "posts_filtered = posts_reordered[((posts_reordered['status_message'].str.contains('|'.join(keywords), case=False))\n",
    "                                  | (posts_reordered['link_name'].str.contains('|'.join(keywords))))\n",
    "                               & (~posts_reordered['status_message'].str.contains('|'.join(negative_keywords), case=False))\n",
    "                               & (~posts_reordered['link_name'].str.contains('|'.join(negative_keywords), case=False))\n",
    "                                ]\n",
    "print(\"Dimensions of filtered posts: \" + str(posts_filtered.shape)) # (1316, 21)\n",
    "posts_filtered.reset_index(inplace=True) # reset index\n",
    "posts_filtered.fillna(0, inplace=True) # fill missing values with 0\n",
    "\n",
    "posts_filtered.sort_values(by=['attack_date', 'status_id'], ascending=True, inplace=True, na_position='first')\n",
    "posts_filtered.drop([0, 1, 2], inplace=True) # the first three posts were dated back by the site owners (published post-2010, dated to 2005)\n",
    "posts_filtered.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#insert new columns for dummies\n",
    "cols = [ # columns to create\n",
    "    \"islamist_attack\", \"left_attack\", \"right_attack\", \"sep_loy_attack\", \"sep_rep_attack\", \"separatist\", #attacks\n",
    "    \"photo\", \"video\", \"link\", \"type_ord\",#status types\n",
    "    \"YYYYMM\",\n",
    "    \"terror_mentioned\"]\n",
    "dict_perps = {'ISLAMIST': \"islamist_attack\", 'LEFT': \"left_attack\", 'RIGHT': \"right_attack\", 'SEP-LOY': \"sep_loy_attack\", 'SEP-REP': \"sep_rep_attack\"}\n",
    "dict_islamist = {\"JeSuisCharlie\": \"islamist_attack\", \"Hebdo\": \"islamist_attack\", \"ParisAttacks\": \"islamist_attack\",\n",
    "                 \"Anschlag in Paris\": \"islamist_attack\", \"Paris Attacks\": \"islamist_attack\", \"Je suis charlie\": \"islamist_attack\", \"Paris terror attack\": \"islamist_attack\", \"shootings in Paris\": \"islamist_attack\",\n",
    "                 \"attack in Paris\": \"islamist_attack\",  \"Paris beginnt der Schweigemarsch\": \"islamist_attack\", \"Anschlag von Paris\": \"islamist_attack\", \"attacks in Paris\": \"islamist_attack\",\n",
    "                 \n",
    "                 \"Islam\": \"islamist_attack\", \"jihad\": \"islamist_attack\"}\n",
    "dict_status = {\"photo\": \"photo\", \"link\": \"link\", \"video\": \"video\"}\n",
    "for col in cols:\n",
    "    if col in posts_filtered.columns: del posts_filtered[col]\n",
    "    posts_filtered[col] = pd.Series(0, index=posts_filtered.index)\n",
    "\n",
    "# assign attacker categories and status types dummies\n",
    "for k in dict_perps:\n",
    "    posts_filtered.loc[posts_filtered['attacker_categorized'] == k, dict_perps[k]] = 1\n",
    "\n",
    "for k in dict_islamist:\n",
    "    posts_filtered.loc[posts_filtered['status_message'].str.contains(k, case=False), 'islamist_attack'] = 1 # check that posts refering to charlie hebdo are labelled as \"islamist\"\n",
    "    \n",
    "for k in dict_status:\n",
    "    posts_filtered.loc[posts_filtered['status_type'] == k, dict_status[k]] =  1\n",
    "    \n",
    "posts_filtered.loc[posts_filtered['attacker_categorized'].str.contains('SEP', case=False), \"separatist\"] = 1\n",
    "\n",
    "dict_status_type = {\"link\": 0,  \"video\": 1, \"photo\": 2}\n",
    "for key in dict_status_type:\n",
    "    posts_filtered.loc[posts_filtered['status_type'] == key, 'type_ord'] = dict_status_type[key]\n",
    "\n",
    "# check that islamist attacks are labelled only as such\n",
    "ls_attack_dummies_without_islamist = ['sep_loy_attack', 'sep_rep_attack', \"right_attack\", \"left_attack\"]\n",
    "for i in ls_attack_dummies_without_islamist:\n",
    "    posts_filtered.loc[posts_filtered[\"islamist_attack\"]==1, i] = 0\n",
    "    \n",
    "posts_filtered[\"YYYYMM\"] = posts_filtered['publication_datetime_str'].str[0:4] + posts_filtered['publication_datetime_str'].str[5:7] # assign YYYYMM to column\n",
    "posts_filtered[\"YYYYMM\"] = pd.to_numeric(posts_filtered[\"YYYYMM\"])\n",
    "\n",
    "# assign \"terrorism mentioned\" dummy\n",
    "posts_filtered.loc[posts_filtered['status_message'].str.contains(\"terror\", case=False), \"terror_mentioned\"] = 1 \n",
    "\n",
    "# combine 'reactions'\n",
    "if 'reactions_comb' in posts_filtered.columns: del posts_filtered['reactions_comb'] #if code is run multiple times\n",
    "posts_filtered = posts_filtered.assign(reactions_comb = #create column with combined reactions\n",
    "                     pd.Series(posts_filtered['num_likes']\n",
    "                             + posts_filtered['num_love']\n",
    "                             + posts_filtered['num_wow']\n",
    "                             + posts_filtered['num_haha']\n",
    "                             + posts_filtered['num_sad']\n",
    "                             + posts_filtered['num_angry']\n",
    "                             + posts_filtered['num_comments'] # not sure about this\n",
    "                             + posts_filtered['num_shares'] # not sure about this\n",
    "                            ))\n",
    "# drop previously combined columns\n",
    "posts_filtered_dropped = posts_filtered.drop(\n",
    "    ['num_likes', 'num_love', 'num_wow', 'num_haha', 'num_sad', 'num_angry',\n",
    "     'num_comments', 'num_shares', \n",
    "     'page_id', 'status_id', 'attacker_categorized', 'status_type', 'status_message', 'link_name', 'status_link'], axis=1\n",
    "    )\n",
    "\n",
    "del cols, dict_perps, dict_status, ls_attack_dummies_without_islamist, dict_islamist, dict_status_type"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Transform and export data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "posts_only = posts_filtered.drop(['level_0', 'index', 'page_name', 'page_id', 'status_id', 'publication_datetime_str', \n",
    "                                  'status_type', 'status_message', 'link_name', 'status_link',\n",
    "                                  'num_likes', 'num_love', 'num_wow', 'num_haha', 'num_sad', 'num_angry',\n",
    "                                  'num_comments', 'num_shares', 'publication_datetime_dt', 'photo', 'video', 'link',\n",
    "                                  'reactions_comb'], axis=1)\n",
    "\n",
    "posts_only[\"att_date_perpcat\"] = posts_only[\"attack_date\"].map(str) + \"_\" + posts_only[\"attacker_categorized\"] # prepare column to of attack and perpetrator (in case multiple attacks by different groups happened on the same day)\n",
    "posts_only['att_date_perpcat'] = posts_only['att_date_perpcat'].str.replace(' 00:00:00', '') # remove \" 00:00:00\"\n",
    "posts_only.sort_values(by=['att_date_perpcat'], ascending=True, inplace=True, na_position='first') # sort\n",
    "\n",
    "### VERSION 1 ### #prepare df with meta data on posts -- version 1: don't change!\n",
    "I_post_count = posts_only.groupby('att_date_perpcat').first() # create result df and group by attack_date column; keep first occurence\n",
    "I_post_count['count'] = posts_only['att_date_perpcat'].value_counts() # count values of attack_date in the column counts\n",
    "I_post_count.reset_index(inplace=True) # reset index\n",
    "\n",
    "### VERSION 2 ### #prepare df with meta data on posts -- version 2: don't change!\n",
    "II_post_count = posts_only.groupby('att_date_perpcat').sum() # create result df and group by attack_date column; keep sum of occurences\n",
    "II_post_count['count'] = posts_only['att_date_perpcat'].value_counts() # count values of attack_date in the column counts\n",
    "II_post_count.reset_index(inplace=True) # reset index\n",
    "\n",
    "### Add 1 column (terror_mentioned) from V2 to replace that column in V1\n",
    "I_post_count['terror_mentioned'] = II_post_count['terror_mentioned']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export post analysis dataframe\n",
    "#posts_filtered.to_csv(\"posts_export_v1.csv\", na_rep=\"-99\")\n",
    "posts_filtered.to_csv(\"posts_export.csv\", na_rep=\"-99\")\n",
    "\n",
    "# Export post count dataframe\n",
    "#I_post_count.to_csv(\"post_count_export_v1.csv\", na_rep='-99')\n",
    "I_post_count.to_csv(\"post_count_export.csv\", na_rep='-99')\n",
    "#II_post_count.to_csv(\"post_count_export2.csv\", na_rep='-99')"
   ]
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}