generate_synthetic_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e01f5160",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c17378d1",
   "metadata": {},
   "source": [
    "# **Generating Synthetic Data using CTGAN**\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dfe85c8e",
   "metadata": {},
   "source": [
    "https://github.com/sdv-dev/CTGAN"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9db933e0",
   "metadata": {},
   "source": [
    "----"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1fceb4e2",
   "metadata": {},
   "source": [
    "# **Showcases of synthetic data:**\n",
    "## 1. **Input**: original .csv file, **output**: synthetic .csv file\n",
    "## 2. **Input**: original database table, **output**: synthetic database table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97dfc13d",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "923f24c8",
   "metadata": {},
   "source": [
    "## **1. Input: original .csv file, output: synthetic .csv file**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "619a3611",
   "metadata": {},
   "source": [
    "## 1.1 Script"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca678572",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sdv.demo import load_tabular_demo\n",
    "from sdv.tabular import CTGAN\n",
    "import pandas as pd\n",
    "import ocifs\n",
    "import os\n",
    "from ocifs import OCIFileSystem\n",
    "\n",
    "def script_1(input_data):\n",
    "    \n",
    "    #get the primary key (like Employee ID or customer ID), the csv name, and determine number of new synthetic rows.\n",
    "    prim_key = data[\"prim_key\"]\n",
    "    csv_name = data[\"csv_name\"]\n",
    "    number_new_rows = data[\"number_new_rows\"]\n",
    "    \n",
    "    #get the csv file from a bucket\n",
    "    input_location = \"oci://West_BP@frqap2zhtzbe/synthetic_data/\"\n",
    "    input_csv = pd.read_csv(input_location + csv_name)\n",
    "    \n",
    "    #show snapshot of original data\n",
    "    print(\"--------------------------------\")\n",
    "    print(\"Snapshot of the original data is: \")\n",
    "    print(\"--------------------------------\")\n",
    "    print(input_csv.head(10).to_string())\n",
    "\n",
    "    #get max 200 rows to train on (for demo purposes)\n",
    "    data_short = input_csv.head(200)\n",
    "    \n",
    "    #load ctgan model\n",
    "    model = CTGAN(primary_key=prim_key)\n",
    "    \n",
    "    #fit model on short data\n",
    "    model.fit(data_short)\n",
    "    \n",
    "    #output file name\n",
    "    output_file_name = \"/home/datascience/synthetic_\" + csv_name\n",
    "    \n",
    "    #delete file if exists already\n",
    "    if os.path.exists(output_file_name):\n",
    "        os.remove(output_file_name)\n",
    "    else:\n",
    "        print(\"The file does not exist yet, but that's fine\")\n",
    "    \n",
    "    #create new synthetic rows and store as .csv file\n",
    "    new_data = model.sample(num_rows = number_new_rows, output_file_path = output_file_name) \n",
    "    \n",
    "    #copy new .csv file back to bucket\n",
    "    fs = OCIFileSystem()\n",
    "    fs.invalidate_cache()\n",
    "    \n",
    "    new_csv_local_path = os.path.join(\"/home/datascience/\", output_file_name)\n",
    "       \n",
    "    with open(new_csv_local_path, 'rb') as f:\n",
    "        with fs.open(input_location + os.path.basename(new_csv_local_path), 'wb') as file_out:\n",
    "            file_out.write(f.read())\n",
    "    \n",
    "    #show snapshot of synthetic data\n",
    "    print(\"--------------------------------\")\n",
    "    print(\"--------------------------------\")\n",
    "    print(\"--------------------------------\")\n",
    "    print(\"Snapshot of the synthetic data is: \")\n",
    "    print(\"--------------------------------\")\n",
    "    loc_synthetic_data = \"oci://West_BP@frqap2zhtzbe/synthetic_data/\" + \"synthetic_\" + csv_name\n",
    "    snapshot_synthetic = pd.read_csv(loc_synthetic_data)\n",
    "    print(snapshot_synthetic.head(10).to_string())\n",
    "    \n",
    "    print(\"--------------------------------\")\n",
    "    print(\"--------------------------------\")\n",
    "    return print(\"Synthetic data has been generated\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c76c628",
   "metadata": {},
   "source": [
    "## 1.2 Run script and see output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f70958d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# determine values to pass \n",
    "primary_key = \"PassengerId\"\n",
    "csv_name = \"titanic_original.csv\"\n",
    "number_new_rows = 200\n",
    "\n",
    "#call script_1\n",
    "data = {\"prim_key\":primary_key, \"csv_name\":csv_name, \"number_new_rows\":number_new_rows}\n",
    "script_1(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26c7c4ec",
   "metadata": {},
   "source": [
    "## **2. Input: original database table, output: synthetic database table**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a463c6b",
   "metadata": {},
   "source": [
    "## 2.1 Script"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4f57b81",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "password = \"xx\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56195f27",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sdv.demo import load_tabular_demo\n",
    "from sdv.tabular import CTGAN\n",
    "import pandas as pd\n",
    "import ocifs\n",
    "import os\n",
    "from ocifs import OCIFileSystem\n",
    "import ads\n",
    "    \n",
    "def script_2(input_data):\n",
    "\n",
    "    #get the primary key (like Employee ID or customer ID), the csv name, and determine number of new synthetic rows.\n",
    "    prim_key = data[\"prim_key\"]\n",
    "    table_name = data[\"table_name\"]\n",
    "    number_new_rows = data[\"number_new_rows\"]\n",
    "\n",
    "    #connect to the autonomous database\n",
    "    connection_parameters = {\n",
    "        \"user_name\": \"BOB\",\n",
    "        \"password\": password,\n",
    "        \"service_name\": \"adwwest_high\",\n",
    "        \"wallet_location\": \"/home/datascience/synthetic_data/wallet/Wallet_ADWWEST.zip\"}\n",
    "    \n",
    "    # Read and show titanic data from database\n",
    "    sql_statement = \"SELECT * FROM \" + table_name\n",
    "    df_original = pd.DataFrame.ads.read_sql(sql_statement, connection_parameters=connection_parameters)\n",
    "    \n",
    "    #show snapshot of original data\n",
    "    print(\"--------------------------------\")\n",
    "    print(\"--------------------------------\")\n",
    "    print(\"--------------------------------\")\n",
    "    print(\"Snapshot of the original data is: \")\n",
    "    print(\"--------------------------------\")\n",
    "    print(df_original.head(10).to_string())\n",
    "    \n",
    "    #get max 200 rows to train on (for demo purposes)\n",
    "    data_short = df_original.head(200)\n",
    "    \n",
    "    #load ctgan model\n",
    "    model = CTGAN(primary_key=prim_key)\n",
    "    \n",
    "    #fit model on short data\n",
    "    model.fit(data_short)\n",
    "    \n",
    "    #output file name\n",
    "    output_file_name = \"/home/datascience/synthetic_from_db_\" + table_name\n",
    "    \n",
    "    #delete file if exists already\n",
    "    if os.path.exists(output_file_name):\n",
    "        os.remove(output_file_name)\n",
    "    else:\n",
    "        print(\"The file does not exist yet, but that's fine\")\n",
    "    \n",
    "    #create new synthetic rows and store as .csv file\n",
    "    new_data = model.sample(num_rows = number_new_rows, output_file_path = output_file_name) \n",
    "    \n",
    "    #load new data as pd dataframe\n",
    "    new_csv_local_path = os.path.join(\"/home/datascience/\", output_file_name)\n",
    "    snapshot_synthetic_from_db = pd.read_csv(os.path.join(\"/home/datascience/\", output_file_name))\n",
    "    \n",
    "    #push the new synthetic data as new table in the database\n",
    "    snapshot_synthetic_from_db.ads.to_sql(\"TITANIC_SYNTHETIC\",connection_parameters=connection_parameters, if_exists=\"replace\")\n",
    "    \n",
    "    #show snapshot of synthetic data\n",
    "    print(\"--------------------------------\")\n",
    "    print(\"--------------------------------\")\n",
    "    print(\"--------------------------------\")\n",
    "    print(\"Snapshot of the synthetic data is: \")\n",
    "    print(\"--------------------------------\")   \n",
    "    \n",
    "    #query synthetic data from the newly created table. And show.\n",
    "    sql_statement_syn = \"SELECT * FROM TITANIC_SYNTHETIC\"\n",
    "    df_synthetic = pd.DataFrame.ads.read_sql(sql_statement_syn, connection_parameters=connection_parameters)\n",
    "    \n",
    "    print(df_synthetic.head(10).to_string())\n",
    "    \n",
    "    print(\"--------------------------------\")\n",
    "    print(\"--------------------------------\")\n",
    "    return print(\"Synthetic data has been generated and pushed to database\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2212e5b4",
   "metadata": {},
   "source": [
    "## 2.2 Run script and see output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88472f11",
   "metadata": {},
   "outputs": [],
   "source": [
    "# determine values to pass \n",
    "primary_key = \"PASSENGERID\"\n",
    "table_name = \"TITANIC_ORIGINAL\"\n",
    "number_new_rows = 200\n",
    "\n",
    "#call script_2\n",
    "data = {\"prim_key\":primary_key, \"table_name\":table_name, \"number_new_rows\":number_new_rows}\n",
    "script_2(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9afea26f",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:fdf_conda]",
   "language": "python",
   "name": "conda-env-fdf_conda-py"
  },
  "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.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}