{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3d4fab9e",
   "metadata": {},
   "source": [
    "# Notebook for updating dateset timestamps\n",
    "Amazon Fraud Detector only retain 18 months of data for ingested events. This notebook provide functions to shift dateset timestamps to most recent months. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e3f51928",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-07T15:05:13.794536Z",
     "start_time": "2023-03-07T15:05:13.422274Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime, timezone, timedelta\n",
    "import glob\n",
    "import zipfile\n",
    "import os\n",
    "import glob\n",
    "\n",
    "def update_timestamp(file):\n",
    "    # Input: \n",
    "    #     file: file_path to csv \n",
    "\n",
    "    df = pd.read_csv(file,\n",
    "                dtype='object',\n",
    "                keep_default_na=False,\n",
    "                na_values='')\n",
    "\n",
    "    df['EVENT_TIMESTAMP'] = pd.to_datetime(df['EVENT_TIMESTAMP'])\n",
    "    min_dt = min(df['EVENT_TIMESTAMP']).replace(tzinfo=None)\n",
    "    max_dt = max(df['EVENT_TIMESTAMP']).replace(tzinfo=None)\n",
    "    \n",
    "    if 'LABEL_TIMESTAMP' in df.columns:\n",
    "        df['LABEL_TIMESTAMP'] = pd.to_datetime(df['LABEL_TIMESTAMP'])\n",
    "        min_dt = min(min_dt, df['LABEL_TIMESTAMP'].min().replace(tzinfo=None))\n",
    "        max_dt = max(max_dt, df['LABEL_TIMESTAMP'].max().replace(tzinfo=None))\n",
    "        \n",
    "    print('Orignal dates')\n",
    "    print(min_dt, max_dt)\n",
    "    \n",
    "    tz_info = max_dt.tzinfo\n",
    "\n",
    "    assert max_dt-min_dt<timedelta(days=547)\n",
    "\n",
    "    time_diff = datetime.now(tz_info)-max_dt-timedelta(days=1)\n",
    "\n",
    "    df['EVENT_TIMESTAMP'] = df['EVENT_TIMESTAMP'] + time_diff\n",
    "    print('Updated dates')\n",
    "    print(df['EVENT_TIMESTAMP'].min(), df['EVENT_TIMESTAMP'].max())\n",
    "    if 'LABEL_TIMESTAMP' in df.columns:\n",
    "        df['LABEL_TIMESTAMP'] = df['LABEL_TIMESTAMP'] + time_diff\n",
    "        print(df['LABEL_TIMESTAMP'].min(), df['LABEL_TIMESTAMP'].max())\n",
    "        \n",
    "    df['EVENT_TIMESTAMP'] = df['EVENT_TIMESTAMP'].dt.strftime(\"%Y-%m-%dT%H:%M:%SZ\")\n",
    "    if 'LABEL_TIMESTAMP' in df.columns:\n",
    "        df['LABEL_TIMESTAMP'] = df['LABEL_TIMESTAMP'].dt.strftime(\"%Y-%m-%dT%H:%M:%SZ\")\n",
    "        \n",
    "    \n",
    "    df.to_csv(file,index=False)\n",
    "    return 'SUCCESS'\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80a730b6",
   "metadata": {},
   "source": [
    "### Under data folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "838a4397",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-07T15:05:44.069838Z",
     "start_time": "2023-03-07T15:05:17.698660Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Orignal dates\n",
      "2021-09-01 13:05:39 2022-09-01 17:51:02\n",
      "Updated dates\n",
      "2022-03-06 10:19:54.806105+00:00 2023-03-06 15:05:17.806105+00:00\n",
      "Orignal dates\n",
      "2021-09-01 12:38:19 2022-09-01 17:51:19\n",
      "Updated dates\n",
      "2022-03-06 09:52:18.135153+00:00 2023-03-06 15:05:18.135153+00:00\n",
      "Orignal dates\n",
      "2022-05-05 21:51:22 2022-09-01 21:51:22\n",
      "Updated dates\n",
      "2022-11-07 15:05:19.543791+00:00 2023-03-06 15:05:19.543791+00:00\n",
      "2022-11-07 15:05:19.543791+00:00 2023-03-06 15:05:19.543791+00:00\n",
      "Orignal dates\n",
      "2022-03-04 07:30:48 2022-09-01 21:51:47\n",
      "Updated dates\n",
      "2022-09-06 00:44:29.041042+00:00 2023-03-06 00:43:47.041042+00:00\n",
      "2022-09-09 10:00:42.041042+00:00 2023-03-06 15:05:28.041042+00:00\n"
     ]
    }
   ],
   "source": [
    "update_timestamp('registration_data_20K_full.csv')\n",
    "\n",
    "update_timestamp('registration_data_20K_minimum.csv')\n",
    "\n",
    "update_timestamp('transaction_data_100K_full.csv')\n",
    "\n",
    "with zipfile.ZipFile(\"ato_data_800K_full.csv.zip\",\"r\") as zip_ref:\n",
    "    zip_ref.extractall(\".\")\n",
    "update_timestamp('ato_data_800K_full.csv')\n",
    "zipfile.ZipFile('ato_data_800K_full.csv.zip', mode='w').write(\"ato_data_800K_full.csv\", compress_type=zipfile.ZIP_DEFLATED)\n",
    "os.remove('ato_data_800K_full.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d20c172",
   "metadata": {},
   "source": [
    "### Under demo_scripts/data folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "76628e2a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-07T15:05:44.075669Z",
     "start_time": "2023-03-07T15:05:44.072539Z"
    }
   },
   "outputs": [],
   "source": [
    "files = glob.glob('../demo_scripts/data/*')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "abaceeaa",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-07T15:06:04.500222Z",
     "start_time": "2023-03-07T15:05:44.077285Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "../demo_scripts/data/Abuse_FreeTrialReferralAbuse_100k.csv\n",
      "Orignal dates\n",
      "2021-10-05 11:42:36 2022-10-05 17:19:26\n",
      "Updated dates\n",
      "2022-03-06 09:28:54.710904+00:00 2023-03-06 15:02:05.710904+00:00\n",
      "2023-03-06 15:05:44.710904+00:00 2023-03-06 15:05:44.710904+00:00\n",
      "====\n",
      "../demo_scripts/data/Advertisement_AdClickFraud_20k.csv\n",
      "Orignal dates\n",
      "2021-10-05 12:16:42 2022-10-05 17:19:29\n",
      "Updated dates\n",
      "2022-03-06 10:02:59.665429+00:00 2023-03-06 14:25:51.665429+00:00\n",
      "2023-03-06 15:05:46.665429+00:00 2023-03-06 15:05:46.665429+00:00\n",
      "====\n",
      "../demo_scripts/data/ContentModeration_FakeReviews_100k.csv\n",
      "Orignal dates\n",
      "2021-10-05 11:33:49 2022-10-05 17:19:29\n",
      "Updated dates\n",
      "2022-03-06 09:20:07.627030+00:00 2023-03-06 15:00:48.627030+00:00\n",
      "2023-03-06 15:05:47.627030+00:00 2023-03-06 15:05:47.627030+00:00\n",
      "====\n",
      "../demo_scripts/data/Insurance_FraudulentAutoInsuranceClaims_100k.csv\n",
      "Orignal dates\n",
      "2021-10-05 11:43:38 2022-10-05 17:19:31\n",
      "Updated dates\n",
      "2022-03-06 09:29:57.185178+00:00 2023-03-06 15:03:23.185178+00:00\n",
      "2023-03-06 15:05:50.185178+00:00 2023-03-06 15:05:50.185178+00:00\n",
      "====\n",
      "../demo_scripts/data/Registration_FakeAccountCreationByBots_100k.csv\n",
      "Orignal dates\n",
      "2021-10-05 11:34:22 2022-10-05 17:19:28\n",
      "Updated dates\n",
      "2022-03-06 09:20:46.911684+00:00 2023-03-06 15:04:02.911684+00:00\n",
      "2023-03-06 15:05:52.911684+00:00 2023-03-06 15:05:52.911684+00:00\n",
      "====\n",
      "../demo_scripts/data/Registration_FakeAccountCreationByHumans_100k.csv\n",
      "Orignal dates\n",
      "2021-10-05 11:42:29 2022-10-05 17:19:25\n",
      "Updated dates\n",
      "2022-03-06 09:28:59.536716+00:00 2023-03-06 15:05:03.536716+00:00\n",
      "2023-03-06 15:05:55.536716+00:00 2023-03-06 15:05:55.536716+00:00\n",
      "====\n",
      "../demo_scripts/data/Transactions_CardNotPresentOnlineTransactions_100k.csv\n",
      "Orignal dates\n",
      "2021-10-05 11:37:30 2022-10-05 17:19:35\n",
      "Updated dates\n",
      "2022-03-06 09:23:53.612257+00:00 2023-03-06 15:04:29.612257+00:00\n",
      "2023-03-06 15:05:58.612257+00:00 2023-03-06 15:05:58.612257+00:00\n",
      "====\n",
      "../demo_scripts/data/Transactions_LoyaltyPayments_100k.csv\n",
      "Orignal dates\n",
      "2021-10-05 11:37:28 2022-10-05 17:19:33\n",
      "Updated dates\n",
      "2022-03-06 09:23:57.204329+00:00 2023-03-06 15:02:34.204329+00:00\n",
      "2023-03-06 15:06:02.204329+00:00 2023-03-06 15:06:02.204329+00:00\n",
      "====\n"
     ]
    }
   ],
   "source": [
    "for f in files:\n",
    "    if f.endswith('.csv'):\n",
    "        print(f)\n",
    "        update_timestamp(f)\n",
    "        print('====')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8efd4cfe",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "920feedb",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}