{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip install influxdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd, numpy as np\n",
    "from influxdb import DataFrameClient\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "user = 'admin'\n",
    "password = open('auth/influxa.txt','r').read()\n",
    "host='influxdb'\n",
    "port=8086\n",
    "dbname='extra'\n",
    "protocol = 'line' #'json'\n",
    "client = DataFrameClient(host, port, user, password, dbname)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timedelta('3188 days 16:49:03.943357')"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_csv('data/all_stocks_5yr.csv')\n",
    "df['date']=df['date'].astype(np.datetime64)\n",
    "pd.to_datetime('now')-df['date'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "purge=True\n",
    "write_out=True\n",
    "full_overwrite=False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "if purge:\n",
    "    client.drop_database(dbname)\n",
    "    client.drop_retention_policy(dbname)\n",
    "    client.create_database(dbname)\n",
    "    client.create_retention_policy(dbname, '3300d', 1, default=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ResultSet({'('databases', None)': [{'name': '_internal'}, {'name': 'base'}, {'name': 'long'}]})"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "client.query('show databases')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "import datetime\n",
    "import pytz\n",
    "utc=pytz.UTC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def push2influx(df,measurement,field_columns,tag_columns,shift=False,dbclient=client,wo=write_out,fo=full_overwrite,daily=True):\n",
    "    if wo:\n",
    "        df=df.sort_index()\n",
    "        df.index=df.index.tz_localize('GMT')\n",
    "        if shift:\n",
    "            df.index+=pd.to_timedelta('12h')\n",
    "        if fo: \n",
    "            print('Purging',measurement,'...')\n",
    "            dbclient.query('DROP MEASUREMENT '+measurement)\n",
    "        else:\n",
    "            latest=dbclient.query('SELECT * FROM '+measurement+' GROUP BY \"1d\" ORDER BY DESC LIMIT 1')\n",
    "            if latest:\n",
    "                lat=latest[list(latest.keys())[0]].index[0]\n",
    "                if daily: lat+=pd.to_timedelta('1d')\n",
    "                df=df[lat:]\n",
    "                print('Slicing',measurement,'from',lat,'...')\n",
    "            else:\n",
    "                print('No data in db for',measurement,'...')\n",
    "        time.sleep(3)\n",
    "        print('Writing to',measurement,'...')\n",
    "        bsize=5000\n",
    "        bwait=2\n",
    "        print(len(df),'data points will be written in',len(df)/bsize,'batches.')\n",
    "        print('Expected query running time is:',int((len(df)/bsize)*bwait*1.1)+3,'seconds.')\n",
    "        for i in range(int(len(df)/bsize)+1):\n",
    "            r=range(i*bsize,min(len(df),(i+1)*bsize))\n",
    "            dc=df.iloc[r]\n",
    "            print('Writing batch',i+1,'...')\n",
    "            dbclient.write_points(dc, measurement, protocol=protocol,\n",
    "                                field_columns=field_columns,\n",
    "                                tag_columns=[])\n",
    "            time.sleep(bwait)\n",
    "        time.sleep(3)\n",
    "        print('Done!')\n",
    "    else:\n",
    "        print('Write-out not enabled. Skipping...')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-02-08</th>\n",
       "      <td>15.07</td>\n",
       "      <td>15.12</td>\n",
       "      <td>14.63</td>\n",
       "      <td>14.75</td>\n",
       "      <td>8407500</td>\n",
       "      <td>AAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-02-11</th>\n",
       "      <td>14.89</td>\n",
       "      <td>15.01</td>\n",
       "      <td>14.26</td>\n",
       "      <td>14.46</td>\n",
       "      <td>8882000</td>\n",
       "      <td>AAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-02-12</th>\n",
       "      <td>14.45</td>\n",
       "      <td>14.51</td>\n",
       "      <td>14.10</td>\n",
       "      <td>14.27</td>\n",
       "      <td>8126000</td>\n",
       "      <td>AAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-02-13</th>\n",
       "      <td>14.30</td>\n",
       "      <td>14.94</td>\n",
       "      <td>14.25</td>\n",
       "      <td>14.66</td>\n",
       "      <td>10259500</td>\n",
       "      <td>AAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-02-14</th>\n",
       "      <td>14.94</td>\n",
       "      <td>14.96</td>\n",
       "      <td>13.16</td>\n",
       "      <td>13.99</td>\n",
       "      <td>31879900</td>\n",
       "      <td>AAL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             open   high    low  close    volume Name\n",
       "date                                                 \n",
       "2013-02-08  15.07  15.12  14.63  14.75   8407500  AAL\n",
       "2013-02-11  14.89  15.01  14.26  14.46   8882000  AAL\n",
       "2013-02-12  14.45  14.51  14.10  14.27   8126000  AAL\n",
       "2013-02-13  14.30  14.94  14.25  14.66  10259500  AAL\n",
       "2013-02-14  14.94  14.96  13.16  13.99  31879900  AAL"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=df.set_index('date')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['close0']=df['close']-df['open']\n",
    "c1=[]\n",
    "c2=[]\n",
    "for i in df['close0'].values:\n",
    "    if i<0:\n",
    "        c1.append(-i)\n",
    "        c2.append(np.nan)\n",
    "    else:\n",
    "        c2.append(i)\n",
    "        c1.append(np.nan)\n",
    "df['close0plus']=c2\n",
    "df['close0minus']=c1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1=df[df['Name'].isin(['AAPL','AAL'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>Name</th>\n",
       "      <th>close0</th>\n",
       "      <th>close0plus</th>\n",
       "      <th>close0minus</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-02-08</th>\n",
       "      <td>15.070</td>\n",
       "      <td>15.12</td>\n",
       "      <td>14.6300</td>\n",
       "      <td>14.75</td>\n",
       "      <td>8407500</td>\n",
       "      <td>AAL</td>\n",
       "      <td>-0.320</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-02-11</th>\n",
       "      <td>14.890</td>\n",
       "      <td>15.01</td>\n",
       "      <td>14.2600</td>\n",
       "      <td>14.46</td>\n",
       "      <td>8882000</td>\n",
       "      <td>AAL</td>\n",
       "      <td>-0.430</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-02-12</th>\n",
       "      <td>14.450</td>\n",
       "      <td>14.51</td>\n",
       "      <td>14.1000</td>\n",
       "      <td>14.27</td>\n",
       "      <td>8126000</td>\n",
       "      <td>AAL</td>\n",
       "      <td>-0.180</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-02-13</th>\n",
       "      <td>14.300</td>\n",
       "      <td>14.94</td>\n",
       "      <td>14.2500</td>\n",
       "      <td>14.66</td>\n",
       "      <td>10259500</td>\n",
       "      <td>AAL</td>\n",
       "      <td>0.360</td>\n",
       "      <td>0.360</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-02-14</th>\n",
       "      <td>14.940</td>\n",
       "      <td>14.96</td>\n",
       "      <td>13.1600</td>\n",
       "      <td>13.99</td>\n",
       "      <td>31879900</td>\n",
       "      <td>AAL</td>\n",
       "      <td>-0.950</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-01</th>\n",
       "      <td>167.165</td>\n",
       "      <td>168.62</td>\n",
       "      <td>166.7600</td>\n",
       "      <td>167.78</td>\n",
       "      <td>47230787</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>0.615</td>\n",
       "      <td>0.615</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-02</th>\n",
       "      <td>166.000</td>\n",
       "      <td>166.80</td>\n",
       "      <td>160.1000</td>\n",
       "      <td>160.50</td>\n",
       "      <td>86593825</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>-5.500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-05</th>\n",
       "      <td>159.100</td>\n",
       "      <td>163.88</td>\n",
       "      <td>156.0000</td>\n",
       "      <td>156.49</td>\n",
       "      <td>72738522</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>-2.610</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-06</th>\n",
       "      <td>154.830</td>\n",
       "      <td>163.72</td>\n",
       "      <td>154.0000</td>\n",
       "      <td>163.03</td>\n",
       "      <td>68243838</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>8.200</td>\n",
       "      <td>8.200</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-07</th>\n",
       "      <td>163.085</td>\n",
       "      <td>163.40</td>\n",
       "      <td>159.0685</td>\n",
       "      <td>159.54</td>\n",
       "      <td>51608580</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>-3.545</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.545</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2518 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               open    high       low   close    volume  Name  close0  \\\n",
       "date                                                                    \n",
       "2013-02-08   15.070   15.12   14.6300   14.75   8407500   AAL  -0.320   \n",
       "2013-02-11   14.890   15.01   14.2600   14.46   8882000   AAL  -0.430   \n",
       "2013-02-12   14.450   14.51   14.1000   14.27   8126000   AAL  -0.180   \n",
       "2013-02-13   14.300   14.94   14.2500   14.66  10259500   AAL   0.360   \n",
       "2013-02-14   14.940   14.96   13.1600   13.99  31879900   AAL  -0.950   \n",
       "...             ...     ...       ...     ...       ...   ...     ...   \n",
       "2018-02-01  167.165  168.62  166.7600  167.78  47230787  AAPL   0.615   \n",
       "2018-02-02  166.000  166.80  160.1000  160.50  86593825  AAPL  -5.500   \n",
       "2018-02-05  159.100  163.88  156.0000  156.49  72738522  AAPL  -2.610   \n",
       "2018-02-06  154.830  163.72  154.0000  163.03  68243838  AAPL   8.200   \n",
       "2018-02-07  163.085  163.40  159.0685  159.54  51608580  AAPL  -3.545   \n",
       "\n",
       "            close0plus  close0minus  \n",
       "date                                 \n",
       "2013-02-08         NaN        0.320  \n",
       "2013-02-11         NaN        0.430  \n",
       "2013-02-12         NaN        0.180  \n",
       "2013-02-13       0.360          NaN  \n",
       "2013-02-14         NaN        0.950  \n",
       "...                ...          ...  \n",
       "2018-02-01       0.615          NaN  \n",
       "2018-02-02         NaN        5.500  \n",
       "2018-02-05         NaN        2.610  \n",
       "2018-02-06       8.200          NaN  \n",
       "2018-02-07         NaN        3.545  \n",
       "\n",
       "[2518 rows x 9 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Purging stocks ...\n",
      "Writing to stocks ...\n",
      "2518 data points will be written in 0.5036 batches.\n",
      "Expected query running time is: 4 seconds.\n",
      "Writing batch 1 ...\n",
      "Done!\n"
     ]
    }
   ],
   "source": [
    "tag_columns=['Name']\n",
    "field_columns=[i for i in df1.columns if i not in tag_columns]\n",
    "measurement='stocks'\n",
    "push2influx(df1,measurement,field_columns,tag_columns,fo=True)"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}