Sql Generator.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>textID</th>\n",
       "      <th># words</th>\n",
       "      <th>genre</th>\n",
       "      <th>year</th>\n",
       "      <th>title</th>\n",
       "      <th>author</th>\n",
       "      <th>Publication information</th>\n",
       "      <th>Library of Congress classification (NF)</th>\n",
       "      <th>FIXED</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2697</th>\n",
       "      <td>773626</td>\n",
       "      <td>22,744</td>\n",
       "      <td>NF</td>\n",
       "      <td>1949</td>\n",
       "      <td>Handicrafts of New England</td>\n",
       "      <td>Allen H. Eaton</td>\n",
       "      <td>Harper and Brothers Publishers, New York</td>\n",
       "      <td>N</td>\n",
       "      <td>n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2698</th>\n",
       "      <td>774245</td>\n",
       "      <td>40,990</td>\n",
       "      <td>NF</td>\n",
       "      <td>1949</td>\n",
       "      <td>A Short History of Science and Scientific Thought</td>\n",
       "      <td>F. Sherwood Taylor</td>\n",
       "      <td>New York: W. W. Norton &amp; Company</td>\n",
       "      <td>Q</td>\n",
       "      <td>n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2699</th>\n",
       "      <td>774247</td>\n",
       "      <td>42,072</td>\n",
       "      <td>NF</td>\n",
       "      <td>1949</td>\n",
       "      <td>The Origins of Modern Science</td>\n",
       "      <td>H. Butterfield</td>\n",
       "      <td>New York: The Macmillan Company</td>\n",
       "      <td>Q</td>\n",
       "      <td>n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2700</th>\n",
       "      <td>774311</td>\n",
       "      <td>39,501</td>\n",
       "      <td>NF</td>\n",
       "      <td>1949</td>\n",
       "      <td>Clothing for Moderns</td>\n",
       "      <td>Mabel D. Erwin</td>\n",
       "      <td>New York: The MacMillan Company</td>\n",
       "      <td>T</td>\n",
       "      <td>n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2701</th>\n",
       "      <td>774345</td>\n",
       "      <td>43,624</td>\n",
       "      <td>NF</td>\n",
       "      <td>1949</td>\n",
       "      <td>Wells Fargo: Advancing the American Frontier</td>\n",
       "      <td>Edward Hungerford</td>\n",
       "      <td>Random House, New York</td>\n",
       "      <td>H</td>\n",
       "      <td>n</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      textID   # words   genre  year  \\\n",
       "2697  773626      22,744    NF  1949   \n",
       "2698  774245      40,990    NF  1949   \n",
       "2699  774247      42,072    NF  1949   \n",
       "2700  774311      39,501    NF  1949   \n",
       "2701  774345      43,624    NF  1949   \n",
       "\n",
       "                                                  title               author  \\\n",
       "2697                         Handicrafts of New England       Allen H. Eaton   \n",
       "2698  A Short History of Science and Scientific Thought  F. Sherwood Taylor    \n",
       "2699                      The Origins of Modern Science      H. Butterfield    \n",
       "2700                               Clothing for Moderns       Mabel D. Erwin   \n",
       "2701       Wells Fargo: Advancing the American Frontier    Edward Hungerford   \n",
       "\n",
       "                       Publication information  \\\n",
       "2697  Harper and Brothers Publishers, New York   \n",
       "2698          New York: W. W. Norton & Company   \n",
       "2699           New York: The Macmillan Company   \n",
       "2700           New York: The MacMillan Company   \n",
       "2701                    Random House, New York   \n",
       "\n",
       "     Library of Congress classification (NF) FIXED  \n",
       "2697                                       N     n  \n",
       "2698                                       Q     n  \n",
       "2699                                       Q     n  \n",
       "2700                                       T     n  \n",
       "2701                                       H     n  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = pandas.read_csv(\"6M Partition/1940.csv\")\n",
    "a.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "genre = [\"FIC\", \"NF\", \"MAG\"]\n",
    "years = [1820, 1830, 1840, 1850, 1860, 1870, 1880, 1890, 1900, 1910, 1920, 1930, 1940, 1950, 1960, 1970, 1980, 1990, 2000]\n",
    "verb = ['demand', 'maintain']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "FIC\n",
      "NF\n",
      "MAG\n"
     ]
    }
   ],
   "source": [
    "for i in genre:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "f=open(\"HandpickedSelectionByGenreLast2.sql\", \"a+\")\n",
    "for v in verb:\n",
    "    for i in years:\n",
    "        a = pandas.read_csv(\"6M Partition/\" + str(i) + \".csv\")\n",
    "        for j in genre:\n",
    "            sqlQuery = \"\"\n",
    "            sqlSource = \"\"\n",
    "            sqlQuery = \"\\n\" + \"--\" + str(i) + j + v + \"\\n\"\n",
    "            b = a.loc[a['genre'] == j]\n",
    "            for row in b.iterrows():\n",
    "                sqlSource = sqlSource + \"s.textid = \" + str(row[1][0]) + \" or \"\n",
    "            sqlSource = sqlSource[:-3]\n",
    "            sqlQuery = sqlQuery + \"\"\"\n",
    "COPY (\n",
    "SELECT count(*), lemma from (\n",
    "SELECT c.w2 FROM wordlines as c, lexicon as l , sources as s\n",
    "where l.lemma like \\'\"\"\" + v + \"\\'\" + \"\"\"\n",
    "and c.w1 = l.wid \n",
    "and c.source = s.textid\n",
    "and s.year BETWEEN \"\"\" + str(i) + \" and \" + str(i + 9) + \"\"\"\n",
    "and (\n",
    "\"\"\" + sqlSource + \")\" + \"\"\"\n",
    "UNION ALL \"\"\" + \"\"\"\n",
    "SELECT c.w3 FROM wordlines as c, lexicon as l , sources as s\n",
    "where l.lemma like \\'\"\"\" + v + \"\\'\" + \"\"\"\n",
    "and c.w1 = l.wid \n",
    "and c.source = s.textid\n",
    "and s.year BETWEEN \"\"\" + str(i) + \" and \" + str(i + 9) + \"\"\"\n",
    "and ( \"\"\" + sqlSource + \")\" + \"\"\"\n",
    "UNION ALL \"\"\" + \"\"\"\n",
    "SELECT c.w4 FROM wordlines as c, lexicon as l , sources as s\n",
    "where l.lemma like \\'\"\"\" + v + \"\\'\" + \"\"\"\n",
    "and c.w1 = l.wid \n",
    "and c.source = s.textid\n",
    "and s.year BETWEEN \"\"\" + str(i) + \" and \" + str(i + 9) + \"\"\"\n",
    "and ( \"\"\" + sqlSource + \")\" + \"\"\"\n",
    "UNION ALL \"\"\" + \"\"\"\n",
    "SELECT c.w5 FROM wordlines as c, lexicon as l , sources as s\n",
    "where l.lemma like \\'\"\"\" + v + \"\\'\" + \"\"\"\n",
    "and c.w1 = l.wid \n",
    "and c.source = s.textid\n",
    "and s.year BETWEEN \"\"\" + str(i) + \" and \" + str(i + 9) + \"\"\"\n",
    "and ( \"\"\" + sqlSource + \")\" + \"\"\"\n",
    "UNION ALL \"\"\" + \"\"\"\n",
    "SELECT c.w6 FROM wordlines as c, lexicon as l , sources as s\n",
    "where l.lemma like \\'\"\"\" + v + \"\\'\" + \"\"\"\n",
    "and c.w1 = l.wid \n",
    "and c.source = s.textid\n",
    "and s.year BETWEEN \"\"\" + str(i) + \" and \" + str(i + 9) + \"\"\"\n",
    "and ( \"\"\" + sqlSource + \")\" + \"\"\" ) a, lexicon b where \n",
    "(\n",
    "b.pos like '%nn@%' or b.pos like '%nn\\%%' or b.pos like 'nn'\n",
    "or b.pos like '%nn1%' or b.pos like '%nn2%'\n",
    ") \n",
    "and a.w2 = b.wid group by b.lemma order by count(*) desc \n",
    ") TO \"\"\" + \"\\'/var/lib/postgresql/HandpickedFullSelectByGenre/\" + v + str(i) + j + \".csv\\' CSV HEADER;\"\n",
    "            \n",
    "            f.write(sqlQuery)\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}