2b_parse_csv.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Parse Useful Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/home/rag/env/exa-data-1/exa-data/data/flattened_csvs\r\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "Parses CSV files\n",
    "Inserts values into into PostgreSQL : Docker\n",
    "'''\n",
    "\n",
    "import glob\n",
    "import os\n",
    "import db_connect\n",
    "import psycopg2\n",
    "import pandas as pd\n",
    "\n",
    "# # Set file path for CSVs\n",
    "# from set_constants import set_paths\n",
    "!pwd\n",
    "csv_directory = '/home/rag/env/exa-data-1/exa-data/data/flattened_csvs'\n",
    "os.chdir(csv_directory)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_csv(csv_file):\n",
    "\n",
    "    df = pd.read_csv(csv_file)\n",
    "    num_rows = len(df)\n",
    "\n",
    "    print(f\"this file has {num_rows} rows\")\n",
    "\n",
    "    #print(type(csv_file))\n",
    "\n",
    "    '''parse the csv file as a nested dictionary and extract the values into variables'''\n",
    "    dict_from_csv = pd.read_csv(csv_file, header=None, index_col=0).squeeze(\"columns\").to_dict()\n",
    "\n",
    "    response = dict_from_csv\n",
    "    entry = response[3]\n",
    "\n",
    "    for i in range(0,num_rows):\n",
    "\n",
    "        current_row = entry[i]\n",
    "        # convert string to dict\n",
    "        current_row = current_row.strip('\\\"')\n",
    "        d = eval(current_row)\n",
    "\n",
    "        fullUrl = d['fullUrl'] # no nested keys\n",
    "        resource = d['resource'] # has nested keys\n",
    "        request = d['request'] # no nested keys\n",
    "\n",
    "       # shorten the query length\n",
    "        dq = d.get('resource')\n",
    "        \n",
    "        resource_type = (dq.get('resourceType'))\n",
    "        resource_id = (dq.get('id'))\n",
    "        \n",
    "        try:\n",
    "            meta = (dq.get('meta').get('profile')[0])\n",
    "        except:\n",
    "            meta=\"na\"\n",
    "        \n",
    "        try:\n",
    "            identifier_system=(dq.get('identifier'))[0].get('system')\n",
    "        except:\n",
    "            identifier_system = \"na\"\n",
    "  \n",
    "\n",
    "        conn = psycopg2.connect(\n",
    "           database=\"postgres\",\n",
    "            user='postgres',\n",
    "            password='postgres',\n",
    "            host='localhost',\n",
    "            port= '5432'\n",
    "        )\n",
    "\n",
    "        conn.autocommit = True\n",
    "        cursor = conn.cursor()\n",
    "\n",
    "        #cursor.execute(sql)\n",
    "        \n",
    "        dictionary ={ 'info-1' : (fullUrl, resource_type,\n",
    "                               'moo_paul@gmail.com', resource_id,\n",
    "                                meta, 'identifier_use', identifier_system )\n",
    "        }\n",
    "\n",
    "        for i in dictionary.values():\n",
    "            sql2='''insert into patient_info(full_url ,\n",
    "                  p_resource , request, resource_id,meta, \n",
    "                  identifier_use,identifier_system) VALUES{};'''.format(i)\n",
    "\n",
    "            cursor.execute(sql2)\n",
    "\n",
    "        conn.commit()\n",
    "        conn.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "processing file Gus973_Windler79_09e292d4-f186-331c-ed95-c503acabc54e.csv\n",
      "\n",
      "===== New file ========\n",
      "\n",
      "this file has 330 rows\n",
      "processing file Della552_Ratke343_b0f40536-9dc8-2ea0-0bbf-467a69f5e3ad.csv\n",
      "\n",
      "===== New file ========\n",
      "\n",
      "this file has 556 rows\n",
      "processing file Lesli455_Champlin946_1d2875d4-f60d-df84-d3e1-12dbafa29bd4.csv\n",
      "\n",
      "===== New file ========\n",
      "\n",
      "this file has 1500 rows\n"
     ]
    },
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-25-1ff0ac45b480>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      9\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0m__name__\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m'__main__'\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     10\u001b[0m     \u001b[0;34m'''main driver'''\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 11\u001b[0;31m     \u001b[0mmain\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     12\u001b[0m     \u001b[0;31m#process_csv('Gus973_Windler79_09e292d4-f186-331c-ed95-c503acabc54e.csv')\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m<ipython-input-25-1ff0ac45b480>\u001b[0m in \u001b[0;36mmain\u001b[0;34m()\u001b[0m\n\u001b[1;32m      4\u001b[0m         \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34mf\"processing file {csv_file}\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m         \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"\\n===== New file ========\\n\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 6\u001b[0;31m         \u001b[0mprocess_csv\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcsv_file\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      7\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      8\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m<ipython-input-24-8e341a40e815>\u001b[0m in \u001b[0;36mprocess_csv\u001b[0;34m(csv_file)\u001b[0m\n\u001b[1;32m     42\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     43\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 44\u001b[0;31m         conn = psycopg2.connect(\n\u001b[0m\u001b[1;32m     45\u001b[0m            \u001b[0mdatabase\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"postgres\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     46\u001b[0m             \u001b[0muser\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'postgres'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.local/lib/python3.8/site-packages/psycopg2/__init__.py\u001b[0m in \u001b[0;36mconnect\u001b[0;34m(dsn, connection_factory, cursor_factory, **kwargs)\u001b[0m\n\u001b[1;32m    120\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    121\u001b[0m     \u001b[0mdsn\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_ext\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmake_dsn\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdsn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 122\u001b[0;31m     \u001b[0mconn\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_connect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdsn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconnection_factory\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mconnection_factory\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwasync\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    123\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0mcursor_factory\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    124\u001b[0m         \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcursor_factory\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcursor_factory\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "def main():\n",
    "    '''Import and parse all CSVs'''\n",
    "    for csv_file in glob.iglob(\"*.csv\"):\n",
    "        print(f\"processing file {csv_file}\")\n",
    "        print(\"\\n===== New file ========\\n\")\n",
    "        process_csv(csv_file)\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    '''main driver'''\n",
    "    main()\n",
    "    #process_csv('Gus973_Windler79_09e292d4-f186-331c-ed95-c503acabc54e.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}