{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"### If someone wants to collaborate I can share the notebook\nThis notebook is born from the desire of undestanding how VWAP is implemented/calculated. I started a discussion with this topic which can be found in this [link](https://www.kaggle.com/c/g-research-crypto-forecasting/discussion/286491) \n","metadata":{}},{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2021-11-09T14:27:17.685438Z","iopub.execute_input":"2021-11-09T14:27:17.685854Z","iopub.status.idle":"2021-11-09T14:27:17.699587Z","shell.execute_reply.started":"2021-11-09T14:27:17.68579Z","shell.execute_reply":"2021-11-09T14:27:17.698711Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"As seen in the data tab of the competition (https://www.kaggle.com/c/g-research-crypto-forecasting/data) an explanation of each file and it's contents.\n### Files and features:\n* train.csv - The training set\n * timestamp - A timestamp for the minute covered by the row.\n * Asset_ID - An ID code for the cryptoasset.\n * Count - The number of trades that took place this minute.\n * Open - The USD price at the beginning of the minute.\n * High - The highest USD price during the minute.\n * Low - The lowest USD price during the minute.\n * Close - The USD price at the end of the minute.\n * Volume - The number of cryptoasset units traded during the minute.\n * VWAP - The volume weighted average price for the minute.\n * Target - 15 minute residualized returns. See the '[Prediction and Evaluation](https://www.kaggle.com/cstein06/tutorial-to-the-g-research-crypto-competition)' section of this notebook for details of how the target is calculated.\n\n\n* example_test.csv - An example of the data that will be delivered by the time series API. The data is just copied from train.csv.\n\n* example_sample_submission.csv - An example of the data that will be delivered by the time series API. The data is just copied from train.csv.\n\n* asset_details.csv - Provides the real name and of the cryptoasset for each Asset_ID and the weight each cryptoasset receives in the metric.\n\n* gresearch_crypto - An unoptimized version of the time series API files for offline work. You may need Python 3.7 and a Linux environment to run it without errors.\n\n* supplemental_train.csv - After the submission period is over this file's data will be replaced with cryptoasset prices from the submission period. The current copy, which is just filled approximately the right amount of data from train.csv is provided as a placeholder.","metadata":{}},{"cell_type":"code","source":"mainFolder = '/kaggle/input/g-research-crypto-forecasting/'","metadata":{"execution":{"iopub.status.busy":"2021-11-09T14:27:17.701213Z","iopub.execute_input":"2021-11-09T14:27:17.701448Z","iopub.status.idle":"2021-11-09T14:27:17.706162Z","shell.execute_reply.started":"2021-11-09T14:27:17.701419Z","shell.execute_reply":"2021-11-09T14:27:17.705405Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time \n# datatable installation with internet\n!pip install datatable==0.11.0 > /dev/null","metadata":{"execution":{"iopub.status.busy":"2021-11-09T14:27:17.707728Z","iopub.execute_input":"2021-11-09T14:27:17.708118Z","iopub.status.idle":"2021-11-09T14:27:26.374739Z","shell.execute_reply.started":"2021-11-09T14:27:17.708078Z","shell.execute_reply":"2021-11-09T14:27:26.373867Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import datatable as dt","metadata":{"execution":{"iopub.status.busy":"2021-11-09T14:27:26.376849Z","iopub.execute_input":"2021-11-09T14:27:26.377167Z","iopub.status.idle":"2021-11-09T14:27:26.382076Z","shell.execute_reply.started":"2021-11-09T14:27:26.377057Z","shell.execute_reply":"2021-11-09T14:27:26.381461Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"As suggested by [@julian3833](https://www.kaggle.com/julian3833) in this [notebook](https://www.kaggle.com/julian3833/s-proposal-for-a-meaningful-lb) I will leave the test data out of the training data (as he said Only keep data from before 2021-06-13 00:00:00.). ","metadata":{}},{"cell_type":"code","source":"# Optimize reading speed of csv with datatable library\ndef read_csv_strict(fullPath='/kaggle/input/g-research-crypto-forecasting/train.csv'):\n    csvDT = dt.fread(fullPath)\n    df = csvDT.to_pandas()\n    df['datetime'] = pd.to_datetime(df['timestamp'], unit='s')\n    df = df[df['datetime'] < '2021-06-13 00:00:00']\n    return df","metadata":{"execution":{"iopub.status.busy":"2021-11-09T14:27:26.383989Z","iopub.execute_input":"2021-11-09T14:27:26.384209Z","iopub.status.idle":"2021-11-09T14:27:26.396319Z","shell.execute_reply.started":"2021-11-09T14:27:26.384182Z","shell.execute_reply":"2021-11-09T14:27:26.395405Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\ndf = read_csv_strict()","metadata":{"execution":{"iopub.status.busy":"2021-11-09T14:27:26.398855Z","iopub.execute_input":"2021-11-09T14:27:26.399354Z","iopub.status.idle":"2021-11-09T14:27:35.64473Z","shell.execute_reply.started":"2021-11-09T14:27:26.399308Z","shell.execute_reply":"2021-11-09T14:27:35.64391Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.shape","metadata":{"execution":{"iopub.status.busy":"2021-11-09T14:27:35.646189Z","iopub.execute_input":"2021-11-09T14:27:35.646995Z","iopub.status.idle":"2021-11-09T14:27:35.653947Z","shell.execute_reply.started":"2021-11-09T14:27:35.646949Z","shell.execute_reply":"2021-11-09T14:27:35.65299Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.head(10)","metadata":{"execution":{"iopub.status.busy":"2021-11-09T14:27:35.655472Z","iopub.execute_input":"2021-11-09T14:27:35.655681Z","iopub.status.idle":"2021-11-09T14:27:35.682184Z","shell.execute_reply.started":"2021-11-09T14:27:35.655656Z","shell.execute_reply":"2021-11-09T14:27:35.681522Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Let's explore assets details","metadata":{}},{"cell_type":"code","source":"asset_details = pd.read_csv(mainFolder + 'asset_details.csv')\nasset_details.sort_values(by='Weight', ascending=False)","metadata":{"execution":{"iopub.status.busy":"2021-11-09T14:27:35.683461Z","iopub.execute_input":"2021-11-09T14:27:35.68377Z","iopub.status.idle":"2021-11-09T14:27:35.700509Z","shell.execute_reply.started":"2021-11-09T14:27:35.683738Z","shell.execute_reply":"2021-11-09T14:27:35.699742Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"bitcoinAsset = asset_details[asset_details['Asset_Name'] == 'Bitcoin']\nbitcoinWeight = bitcoinAsset.Weight\nbitcoinWeight","metadata":{"execution":{"iopub.status.busy":"2021-11-09T14:27:35.701791Z","iopub.execute_input":"2021-11-09T14:27:35.70204Z","iopub.status.idle":"2021-11-09T14:27:35.709626Z","shell.execute_reply.started":"2021-11-09T14:27:35.702012Z","shell.execute_reply":"2021-11-09T14:27:35.708846Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Get all bitcoin asset data\nbitcoin = df[df['Asset_ID'] == 1]","metadata":{"execution":{"iopub.status.busy":"2021-11-09T14:27:35.711055Z","iopub.execute_input":"2021-11-09T14:27:35.71131Z","iopub.status.idle":"2021-11-09T14:27:35.972037Z","shell.execute_reply.started":"2021-11-09T14:27:35.711281Z","shell.execute_reply":"2021-11-09T14:27:35.971219Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Checking there is only bitcoin asset\nbitcoin['Asset_ID'].nunique()","metadata":{"execution":{"iopub.status.busy":"2021-11-09T14:27:35.973385Z","iopub.execute_input":"2021-11-09T14:27:35.973592Z","iopub.status.idle":"2021-11-09T14:27:35.98877Z","shell.execute_reply.started":"2021-11-09T14:27:35.973568Z","shell.execute_reply":"2021-11-09T14:27:35.988177Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"bitcoin.head()","metadata":{"execution":{"iopub.status.busy":"2021-11-09T14:27:35.989902Z","iopub.execute_input":"2021-11-09T14:27:35.990417Z","iopub.status.idle":"2021-11-09T14:27:36.007616Z","shell.execute_reply.started":"2021-11-09T14:27:35.990383Z","shell.execute_reply":"2021-11-09T14:27:36.00677Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from sklearn.metrics import mean_absolute_error","metadata":{"execution":{"iopub.status.busy":"2021-11-09T14:42:43.56301Z","iopub.execute_input":"2021-11-09T14:42:43.563304Z","iopub.status.idle":"2021-11-09T14:42:43.567201Z","shell.execute_reply.started":"2021-11-09T14:42:43.56327Z","shell.execute_reply":"2021-11-09T14:42:43.566252Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"As [@abdelghanibelgaid](https://www.kaggle.com/abdelghanibelgaid) suggested I tried this function to recreate VWAP:","metadata":{}},{"cell_type":"code","source":"def VWAP(df, period=1):\n    df['VWAP_Pre'] = ((df['High']+df['Low']+ df['Close'])/3) * df['Volume']\n    df['VWAP_Source'] = pd.Series(df['VWAP_Pre'].rolling(period,min_periods=period).mean())\n    df['VolumeSMA'] = pd.Series(df['Volume'].rolling(period, min_periods=period).mean())\n    df['VWAP2'] =  df['VWAP_Source']/df['VolumeSMA']\n    return df['VWAP2']","metadata":{"execution":{"iopub.status.busy":"2021-11-09T14:42:42.696181Z","iopub.execute_input":"2021-11-09T14:42:42.696466Z","iopub.status.idle":"2021-11-09T14:42:42.703067Z","shell.execute_reply.started":"2021-11-09T14:42:42.696436Z","shell.execute_reply":"2021-11-09T14:42:42.702081Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"VWAP(bitcoin)","metadata":{"execution":{"iopub.status.busy":"2021-11-09T14:55:22.566555Z","iopub.execute_input":"2021-11-09T14:55:22.566865Z","iopub.status.idle":"2021-11-09T14:55:22.697246Z","shell.execute_reply.started":"2021-11-09T14:55:22.566812Z","shell.execute_reply":"2021-11-09T14:55:22.696523Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"bitcoin.head(10)","metadata":{"execution":{"iopub.status.busy":"2021-11-09T14:55:40.460285Z","iopub.execute_input":"2021-11-09T14:55:40.460581Z","iopub.status.idle":"2021-11-09T14:55:40.485977Z","shell.execute_reply.started":"2021-11-09T14:55:40.460547Z","shell.execute_reply":"2021-11-09T14:55:40.485115Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"mean_absolute_error(bitcoin['VWAP'], bitcoin['VWAP2'])","metadata":{"execution":{"iopub.status.busy":"2021-11-09T14:55:24.839374Z","iopub.execute_input":"2021-11-09T14:55:24.839636Z","iopub.status.idle":"2021-11-09T14:55:24.858294Z","shell.execute_reply.started":"2021-11-09T14:55:24.839607Z","shell.execute_reply":"2021-11-09T14:55:24.85722Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"But it was some error. ","metadata":{}},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}