RETRIEVE GDELT DATA.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Download\n",
    "\n",
    "Berikut adalah contoh script untuk mengunduh data GDELT dengan country code ID. Selanjutnya dari csv bisa dipindah ke mysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import lxml.html as lh\n",
    "\n",
    "gdelt_base_url = 'http://data.gdeltproject.org/events/'\n",
    "\n",
    "# get the list of all the links on the gdelt file page\n",
    "page = requests.get(gdelt_base_url+'index.html')\n",
    "doc = lh.fromstring(page.content)\n",
    "link_list = doc.xpath(\"//*/ul/li/a/@href\")\n",
    "\n",
    "# separate out those links that begin with four digits\n",
    "file_list = [x for x in link_list if str.isdigit(x[0:4])]\n",
    "print(file_list)\n",
    "\n",
    "infilecounter = 0\n",
    "outfilecounter = 0\n",
    "\n",
    "import os.path\n",
    "import urllib\n",
    "import zipfile\n",
    "import glob\n",
    "import operator\n",
    "\n",
    "local_path = 'E:/GDELT/'\n",
    "\n",
    "fips_country_code = 'ID'\n",
    "\n",
    "for compressed_file in file_list[infilecounter:]:\n",
    "    print(compressed_file)\n",
    "\n",
    "    # if we dont have the compressed file stored locally, go get it. Keep trying if necessary.\n",
    "    while not os.path.isfile(local_path + compressed_file):\n",
    "        print('downloading, '),\n",
    "        urllib.urlretrieve(url=gdelt_base_url + compressed_file,\n",
    "                           filename=local_path + compressed_file)\n",
    "\n",
    "    # extract the contents of the compressed file to a temporary directory\n",
    "    print('extracting,'),\n",
    "    z = zipfile.ZipFile(file=local_path + compressed_file, mode='r')\n",
    "    z.extractall(path=local_path + 'tmp/')\n",
    "\n",
    "    # parse each of the csv files in the working directory,\n",
    "    print('parsing,'),\n",
    "    for infile_name in glob.glob(local_path + 'tmp/*'):\n",
    "        outfile_name = local_path + 'country/' + fips_country_code + '%04i.tsv' % outfilecounter\n",
    "\n",
    "        # open the infile and outfile\n",
    "        with open(infile_name, mode='r') as infile, open(outfile_name, mode='w') as outfile:\n",
    "            for line in infile:\n",
    "                # extract lines with our interest country code\n",
    "                if fips_country_code in operator.itemgetter(51, 37, 44)(line.split('\\t')):\n",
    "                    outfile.write(line)\n",
    "            outfilecounter += 1\n",
    "\n",
    "        # delete the temporary file\n",
    "        os.remove(infile_name)\n",
    "    infilecounter += 1\n",
    "    print('done')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Scraping GDELT\n",
    "Berikut adalah kode contoh untuk melakukan scraping dengan library newspaper. Skrip ini akan berjalan jika data dari GDELT telah tersimpan ke MySQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from newspaper import Article\n",
    "import pymysql.cursors\n",
    "import sys\n",
    "\n",
    "offset = sys.argv[1]\n",
    "limit = sys.argv[2]\n",
    "# year = sys.argv[3]\n",
    "\n",
    "# Connect to the database\n",
    "connection = pymysql.connect(host='localhost',\n",
    "                             user='root',\n",
    "                             password='',\n",
    "                             db='news',\n",
    "                             charset='utf8mb4',\n",
    "                             cursorclass=pymysql.cursors.DictCursor)\n",
    "\n",
    "hdr = {'User-Agent': 'Mozilla/5.0'}\n",
    "\n",
    "with connection.cursor() as cursor:\n",
    "    sql = \"SELECT * FROM `gdelt_content` WHERE (TITLE IS NULL OR TRIM(TITLE) = '') LIMIT \"+str(offset)+\",\"+str(limit)\n",
    "    cursor.execute(sql)\n",
    "    print(sql)\n",
    "    result = cursor.fetchall()\n",
    "    for r in result:\n",
    "        try:\n",
    "            r[\"URL\"] = r[\"URL\"].split('<UDIV>')\n",
    "            print(r[\"URL\"][0])\n",
    "            article = Article(r[\"URL\"][0])\n",
    "            article.download()\n",
    "            article.parse()\n",
    "            with connection.cursor() as cursor:\n",
    "                sql = \"UPDATE `gdelt_content` SET title=%s,content=%s WHERE GLOBALEVENTID=%s\"\n",
    "                cursor.execute(sql, (article.title.encode('utf-8'), article.text.encode('utf-8'),r[\"GLOBALEVENTID\"]))\n",
    "                connection.commit()\n",
    "        except:\n",
    "            pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Berikut adalah contoh untuk melakukan pickling data GDELT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import glob\n",
    "import pandas as pd\n",
    "\n",
    "local_path = 'E:/GDELT/'\n",
    "fips_country_code = 'ID'\n",
    "# Get the GDELT field names from a helper file\n",
    "colnames = pd.read_excel('CSV.header.fieldids.xlsx', sheetname='Sheet1',\n",
    "                         index_col='Column ID', parse_cols=1)['Field Name']\n",
    "\n",
    "# Build DataFrames from each of the intermediary files\n",
    "files = glob.glob(local_path + 'country/' + fips_country_code + '*')\n",
    "DFlist = []\n",
    "for active_file in files:\n",
    "    print active_file\n",
    "    DFlist.append(pd.read_csv(active_file, sep='\\t', header=None, dtype=str,\n",
    "                              names=colnames, index_col=['GLOBALEVENTID']))\n",
    "    break\n",
    "\n",
    "# Merge the file-based dataframes and save a pickle\n",
    "DF = pd.concat(DFlist)\n",
    "DF.to_pickle(local_path + 'backupsmall' + fips_country_code + '.pickle')\n",
    "\n",
    "# once everythin is safely stored away, remove the temporary files\n",
    "# for active_file in files:\n",
    "#     os.remove(active_file)"
   ]
  }
 ],
 "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.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}