Convert_policy_data_to_csv.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "connection successful!!!\n"
     ]
    }
   ],
   "source": [
    "import psycopg2\n",
    "from dateutil import parser\n",
    "\n",
    "posts_counts={}\n",
    "columns=['altmetric_id','DOI','ISSN','mendeley','citeulike','connotea','twitter','reddit','facebook','googleplus','blogs','news','video','wikipedia','q&a',\n",
    "         'pinterest','weibo','peer_reviews','policy']\n",
    "\n",
    "with open(\"D:\\\\NIU\\\\Data Mining 2\\\\Project\\\\Connection_Details.txt\",'r') as con_file:\n",
    "    conDetails=con_file.readlines()\n",
    "    hostname=conDetails[0].strip('\\n')\n",
    "    username=conDetails[1].strip('\\n')\n",
    "    password=conDetails[2].strip('\\n')\n",
    "    database=conDetails[3].strip('\\n')\n",
    "\n",
    "myConnection = psycopg2.connect( host=hostname, user=username, password=password, dbname=database )\n",
    "\n",
    "if myConnection:\n",
    "    print(\"connection successful!!!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def doQuery( conn ) :\n",
    "    cur = conn.cursor()\n",
    "    cur.execute(\"SELECT publication -> 'altmetric_id', publication -> 'citation' ->> 'doi', publication -> 'citation' -> 'issns', publication -> 'counts' -> 'readers', \"\n",
    "                \"publication -> 'posts' FROM policy\")\n",
    "\n",
    "    with open(\"D:\\\\NIU\\\\Data Mining 2\\\\Project\\\\PolicyData.csv\", 'w+', encoding='utf-8') as newf:\n",
    "        header = ''\n",
    "        for col in columns:\n",
    "            header += col + ','\n",
    "        newf.write(header[:-1] + '\\n')\n",
    "\n",
    "        for alt_id,doi,issns,readers,posts in cur.fetchall():\n",
    "            new_record=''\n",
    "            policy_count=0\n",
    "\n",
    "            policy_appeared_dates=[]\n",
    "            for p in posts['policy']:\n",
    "                policy_count += 1\n",
    "                policy_appeared_dates.append(parser.parse(p['posted_on']))\n",
    "            first_mentioned_on_policy=min(policy_appeared_dates)\n",
    "\n",
    "            for post in posts:\n",
    "                if post!='policy':\n",
    "                    posts_counts[post]=0\n",
    "                    for p in posts[post]:\n",
    "                        posted_date=parser.parse(p['posted_on'])\n",
    "                        if posted_date<first_mentioned_on_policy:\n",
    "                            posts_counts[post]+=1\n",
    "            new_record+=str(alt_id)+','+str(doi)+','\n",
    "\n",
    "            for issn in issns:\n",
    "                if(len(issn)>=8):\n",
    "                    new_record+=str(issn)+';'\n",
    "            new_record = new_record[:-1]+ ',' + str(readers['mendeley']) + ',' + str(readers['citeulike']) + ',' + str(readers['connotea']) + ','\n",
    "\n",
    "            for i in range(6,len(columns)-1):\n",
    "                if columns[i] in posts_counts:\n",
    "                    new_record += str(posts_counts[columns[i]])+','\n",
    "                else:\n",
    "                    new_record += ','\n",
    "            new_record += str(policy_count)+'\\n'\n",
    "\n",
    "            newf.write(new_record)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "doQuery( myConnection )\n",
    "myConnection.close()\n",
    "print(\"data creation completed!\")"
   ]
  }
 ],
 "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
}