3-pandas-are-cute.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install -r requirements.txt # Only run if haven't run before"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 1 - Import dependencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2 - Profit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_path = \"test-data/accidents_1.csv\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Load CSV into Data Frame*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(csv_path)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Load JSON into Data Frame*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "json_path = \"test-data/100000.json\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_json(json_path, orient='records')\n",
    "\n",
    "# Print the first few rows to inspect the data\n",
    "print(df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Data Frame to CSV... to SQL*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import yaml"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "keys_file_path = 'keys.yaml' # TODO: Make sure this is in your repository and get your own access/secret keys from MinIO!\n",
    "\n",
    "with open(keys_file_path, 'r') as file:\n",
    "    keys = yaml.safe_load(file)\n",
    "\n",
    "postgres_credentials = keys.get('postgres', {})\n",
    "postgres_endpoint = keys.get('services')['postgres_endpoint'] \n",
    "\n",
    "username = postgres_credentials.get('username')\n",
    "password = postgres_credentials.get('password')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbname = \"test\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_params = {\n",
    "    'dbname': dbname,\n",
    "    'user': username,\n",
    "    'password': password,\n",
    "    'host': postgres_endpoint,\n",
    "    'port': 5432 # NOTE: Postgres default port is 5432\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_name = \"test_example1\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a Pandas DataFrame (replace this with your actual DataFrame)\n",
    "data = {'column1': [1, 2, 3], 'column2': ['A', 'B', 'C']}\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# Create a SQLAlchemy engine to connect to the PostgreSQL database\n",
    "engine = create_engine(f'postgresql://{db_params[\"user\"]}:{db_params[\"password\"]}@{db_params[\"host\"]}:{db_params[\"port\"]}/{db_params[\"dbname\"]}')\n",
    "\n",
    "# Push the DataFrame to the PostgreSQL table\n",
    "df.to_sql(name=table_name, con=engine, if_exists='replace', index=False)\n",
    "\n",
    "# Replace 'replace' with 'append' if you want to append data to an existing table"
   ]
  }
 ],
 "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": 2
}