data_manipulation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "991b9bc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import pprint as pp\n",
    "import datetime\n",
    "import gzip\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5dcb9f9d",
   "metadata": {},
   "outputs": [],
   "source": [
    "exchange = \"binance\"\n",
    "data_folder_name = \"datasets_\" + exchange\n",
    "dir_name = os.path.join(os.getcwd(), data_folder_name)\n",
    "extension = \".gz\"\n",
    "regex_match_to_strip = \"\\.csv.gz$\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9501fa6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dict = {}\n",
    "for root, _, file_names in os.walk(dir_name):\n",
    "    for file_name in file_names:\n",
    "        if file_name.endswith(extension):\n",
    "            file_path = os.path.join(root, file_name)\n",
    "            data_name = re.sub(regex_match_to_strip, '', file_name)\n",
    "            print(file_path)\n",
    "            try:\n",
    "                data_dict[data_name] = pd.read_csv(file_path, compression='gzip')\n",
    "            except:\n",
    "                print(\"No data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bac3161a",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dict['2022-04-28_ETHBTC']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c180687",
   "metadata": {},
   "source": [
    "## Preliminary thoughts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d14e55cc",
   "metadata": {},
   "source": [
    "2 weeks data is 275 MB (in csv.gz), so 3 years would be 21.5GB (should be fine on server, right?)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "68ab37ef",
   "metadata": {},
   "source": [
    "Input variables: \n",
    "s = time increment (affects \"Price and Log Return Table\", and implicitly affects \"Realised Volatility Table\"), default=1min\n",
    "h = lookback time of Realised Volatility (affects \"Realised Volatility Table\"), options=[10min, 30min, 65min, 1day]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "168ca405",
   "metadata": {},
   "source": [
    "Output tables:\n",
    "folder structure: one folder for each date, nested: YYYY - MM - DD (three layers\n",
    "file name: XXXXXX (copy the ticker)\n",
    "\"Price and Log Return Table\", with columns: time (HH column, MM column), log return\n",
    "\"Realised Volatility Table\", with columns: time (HH column, MM column), realised volatility"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bce1d8e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "utc_offset = datetime.datetime.fromtimestamp(86400*180) - datetime.datetime.utcfromtimestamp(86400*180)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e89e8a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "utc_offset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5706ff35",
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "print(-time.timezone)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "595c2996",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(-time.altzone)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e0ba344",
   "metadata": {},
   "outputs": [],
   "source": [
    "datetime.datetime.utcfromtimestamp(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49493455",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "11a0e185",
   "metadata": {},
   "outputs": [],
   "source": [
    "x = 1651104001009000\n",
    "datetime.datetime.utcfromtimestamp(x/1000000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67ab7bcf",
   "metadata": {},
   "outputs": [],
   "source": [
    "x = 1651190394661000\n",
    "datetime.datetime.utcfromtimestamp(x/1000000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4e4a163",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b0312e90",
   "metadata": {},
   "outputs": [],
   "source": [
    "returns_folder_name = \"returns\"\n",
    "import pathlib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a7c2f11",
   "metadata": {},
   "outputs": [],
   "source": [
    "pathlib.Path(os.path.join(os.getcwd(), returns_folder_name)).mkdir(parents=True, exist_ok=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57bf9a1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_frame = data_dict['2022-04-28_ETHBTC']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b66d28b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_frame.iloc[140:180]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bca467a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_frame['minute'] = data_frame.timestamp.apply(lambda x: datetime.datetime.utcfromtimestamp(x/1000000).minute)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef0b23cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_frame['hour'] = data_frame.timestamp.apply(lambda x: datetime.datetime.utcfromtimestamp(x/1000000).hour)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "14ec37e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.Index(data_frame['local_timestamp']).is_monotonic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23e34720",
   "metadata": {},
   "outputs": [],
   "source": [
    "x = 1651104059645000\n",
    "datetime.datetime.utcfromtimestamp(x/1000000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f558ea78",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_frame = data_frame[['hour', 'minute', 'timestamp', 'side', 'price']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8482aa8a",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_frame.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55429576",
   "metadata": {},
   "outputs": [],
   "source": [
    "sell_data_frame = data_frame[data_frame.side == 'sell']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45b846bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "sell_maxes = sell_data_frame.groupby(['hour', 'minute', 'side']).timestamp.transform(max)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dfd946d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "sell_data_frame[sell_data_frame.timestamp == sell_maxes]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d4e919a",
   "metadata": {},
   "outputs": [],
   "source": [
    "buy_data_frame = data_frame[data_frame.side == 'buy']\n",
    "buy_mins = buy_data_frame.groupby(['hour', 'minute', 'side']).timestamp.transform(max)\n",
    "buy_data_frame[buy_data_frame.timestamp == buy_mins]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e8ec4326",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_frame.groupby(['hour', 'minute', 'side']).timestamp.transform(max)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "394d758b",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_frame = data_frame[data_frame.timestamp == data_frame.groupby(['hour', 'minute', 'side']).timestamp.transform(max)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d05b1e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_frame.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "829311ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "sell_data_frame = data_frame[data_frame.side == 'sell']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0cec295c",
   "metadata": {},
   "outputs": [],
   "source": [
    "sell_maxes = sell_data_frame.groupby(['hour', 'minute']).price.transform(max)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17007c7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "sell_data_frame = sell_data_frame[sell_data_frame.price == sell_maxes]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "064fa491",
   "metadata": {},
   "outputs": [],
   "source": [
    "sell_data_frame_unique = sell_data_frame.groupby(['hour', 'minute']).tail(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2bb9b015",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(sell_data_frame_unique)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bbdd2f71",
   "metadata": {},
   "outputs": [],
   "source": [
    "buy_data_frame = data_frame[data_frame.side == 'buy']\n",
    "buy_mins = buy_data_frame.groupby(['hour', 'minute']).price.transform(min)\n",
    "buy_data_frame = buy_data_frame[buy_data_frame.price == buy_mins]\n",
    "buy_data_frame_unique = buy_data_frame.groupby(['hour', 'minute']).tail(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d0a780f",
   "metadata": {},
   "outputs": [],
   "source": [
    "buy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a87b4ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_rows', 1440)\n",
    "buy_data_frame_unique.head(1440)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb19b333",
   "metadata": {},
   "outputs": [],
   "source": [
    "buy_data_frame_unique['raw_minute'] = buy_data_frame_unique['hour'] * 60 + buy_data_frame_unique['minute']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "388a2ced",
   "metadata": {},
   "outputs": [],
   "source": [
    "buy_data_frame_unique.set_index('raw_minute').reindex(range(0,1440), fill_value='')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "94dbd9cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(buy_data_frame_unique)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "697f1564",
   "metadata": {},
   "outputs": [],
   "source": [
    "buy_data_frame_unique.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ee254d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dict['2022-04-28_ETHBTC'][61096:61200]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d38189d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dict['2022-04-14_ETHBTC'][81:84]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b10cfcb4",
   "metadata": {},
   "outputs": [],
   "source": [
    "x = 1649894409423000\n",
    "datetime.datetime.fromtimestamp(x/1000000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3acd017d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "257c8b71",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_frame.iloc[0]['symbol'] == file_name_ticker"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a14a78d",
   "metadata": {},
   "outputs": [],
   "source": [
    "datetime.datetime(2022,4,28,24,0,0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34368fa9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3741b3df",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "289a5bd6",
   "metadata": {},
   "outputs": [],
   "source": [
    "datetime_object = datetime.datetime.fromtimestamp(x/1000000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "89247de7",
   "metadata": {},
   "outputs": [],
   "source": [
    "prog = re.compile(pattern)\n",
    "result = prog.match(string)\n",
    "# is equiv to \n",
    "result = re.match(pattern, string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b441aead",
   "metadata": {},
   "outputs": [],
   "source": [
    "target = '2022-04-28_ETHBTC'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "98807bd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "pattern = \"(.*?)_(.*)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b060711",
   "metadata": {},
   "outputs": [],
   "source": [
    "result = re.match(pattern, target)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4248d8fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_name_date = result.group(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ecc4c69f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7baf8daf",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_name_ticker = result.group(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "024a7333",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_name_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad5ff763",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_name_datetime_object = datetime.datetime.strptime(file_name_date, \"%Y-%m-%d\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df942bfa",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_name_datetime_object + datetime.timedelta(days=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30e173ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "first_time = data_frame.iloc[0]['timestamp']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ba081ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "first_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13da12ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_time = data_frame.iloc[-1]['timestamp']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb93efa7",
   "metadata": {},
   "outputs": [],
   "source": [
    "last_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d8f5108",
   "metadata": {},
   "outputs": [],
   "source": [
    "datetime.datetime.fromtimestamp(x/1000000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "af4beabd",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "488bcfdf",
   "metadata": {},
   "outputs": [],
   "source": [
    "def time_is_in_day(a, timestamp):\n",
    "    # checks if timestamp (in microseconds) is within 1 day in the future of a (a datetime object)\n",
    "    b = datetime.datetime.utcfromtimestamp(timestamp/1000000)\n",
    "    return a <= b < a + datetime.timedelta(days=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ec253c79",
   "metadata": {},
   "outputs": [],
   "source": [
    "time_is_in_day(file_name_datetime_object, first_time)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c61c78d",
   "metadata": {},
   "outputs": [],
   "source": [
    "time_is_in_day(file_name_datetime_object, last_time + 6 * 60 * 1000000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d9ea6864",
   "metadata": {},
   "outputs": [],
   "source": [
    "def calculate_returns_from_raw_data(data_frame): # do this directly in the reading loop (so no storing all data)\n",
    "    "
   ]
  }
 ],
 "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.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}