{"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":"# G-Research Crypto Forecasting Data Wrangling and Exploratory Data Analysis","metadata":{"execution":{"iopub.status.busy":"2022-01-10T09:53:24.929597Z","iopub.execute_input":"2022-01-10T09:53:24.930399Z","iopub.status.idle":"2022-01-10T09:53:24.934345Z","shell.execute_reply.started":"2022-01-10T09:53:24.930355Z","shell.execute_reply":"2022-01-10T09:53:24.933521Z"}}},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nfrom datetime import datetime\nimport time","metadata":{"execution":{"iopub.status.busy":"2022-01-11T15:59:12.332724Z","iopub.execute_input":"2022-01-11T15:59:12.333636Z","iopub.status.idle":"2022-01-11T15:59:12.360081Z","shell.execute_reply.started":"2022-01-11T15:59:12.33348Z","shell.execute_reply":"2022-01-11T15:59:12.359452Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Data Wrangling","metadata":{}},{"cell_type":"markdown","source":"### Get the Data\n\nAvailable train data inclues test data, this leading to astounding scores on the public leaderboard. In time series, such a phenomenon when info about the future is leaked to our analysis or training process is called a **lookahead**. A lookahead is a way, through data, to find out something about the future earlier thant you ought to know it. Information about what will happen in the future propagates back in time in our modelling and affects how our model behaves earlier in time (generally tends to faking model performance improvement). Here, data posterior to *2021-06-13* is leaky, i.e. it is data bout the future that should not be used for anything else than testing models or assumptions. The `read_csv_strict()` function designed by [dataista0](https://www.kaggle.com/julian3833) helps in avoiding the caveat of using future data for training models.","metadata":{}},{"cell_type":"code","source":"path = '../input/g-research-crypto-forecasting/'\n\ndef read_csv_strict(file_name='train.csv'): \n    df = pd.read_csv(path+file_name)\n    for asset in df['Asset_ID'].unique():\n        df.loc[df['Asset_ID']==asset, 'datetime'] = pd.to_datetime(df.loc[df['Asset_ID']==asset,'timestamp'], unit='s')\n    df = df[df['datetime'] < '2021-06-13 00:00:00']\n    return df","metadata":{"execution":{"iopub.status.busy":"2022-01-11T15:59:12.361848Z","iopub.execute_input":"2022-01-11T15:59:12.362334Z","iopub.status.idle":"2022-01-11T15:59:12.369497Z","shell.execute_reply.started":"2022-01-11T15:59:12.36229Z","shell.execute_reply":"2022-01-11T15:59:12.368745Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Load train data and asset details\ntrain_data = read_csv_strict()\nasset_details = pd.read_csv(path+'asset_details.csv')","metadata":{"execution":{"iopub.status.busy":"2022-01-11T15:59:12.370567Z","iopub.execute_input":"2022-01-11T15:59:12.37106Z","iopub.status.idle":"2022-01-11T16:00:17.074092Z","shell.execute_reply.started":"2022-01-11T15:59:12.37103Z","shell.execute_reply":"2022-01-11T16:00:17.073037Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_data.head()","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:00:17.076663Z","iopub.execute_input":"2022-01-11T16:00:17.077338Z","iopub.status.idle":"2022-01-11T16:00:17.102092Z","shell.execute_reply.started":"2022-01-11T16:00:17.077285Z","shell.execute_reply":"2022-01-11T16:00:17.101119Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"asset_details.head()","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:00:17.103606Z","iopub.execute_input":"2022-01-11T16:00:17.104079Z","iopub.status.idle":"2022-01-11T16:00:17.115308Z","shell.execute_reply.started":"2022-01-11T16:00:17.104033Z","shell.execute_reply":"2022-01-11T16:00:17.114419Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Join train and asset details to get asset names and weight alongside their time series\ntrain_data = pd.merge(train_data, asset_details, on='Asset_ID')\ntrain_data = train_data[[train_data.columns.tolist()[0], train_data.columns.tolist()[-1], train_data.columns.tolist()[-2]]+train_data.columns.tolist()[2:-2]]","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:00:17.116441Z","iopub.execute_input":"2022-01-11T16:00:17.116657Z","iopub.status.idle":"2022-01-11T16:00:23.677507Z","shell.execute_reply.started":"2022-01-11T16:00:17.116632Z","shell.execute_reply":"2022-01-11T16:00:23.676594Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Looking for missing data","metadata":{}},{"cell_type":"code","source":"# By asset, which range of data do we have in the dataset (in minutes)?\n# How many data points are recorded for each asset (1 data point record = 1 minute)?\ngrouped_stats = train_data.groupby('Asset_Name')['timestamp'].agg([('timestamp', lambda x: (np.min(pd.to_datetime(train_data['timestamp'], unit='s')))), ('timestamp', lambda x: (np.max(pd.to_datetime(train_data['timestamp'], unit='s')))),\n                                                                   ('timestamp', lambda x: int((np.max(pd.to_datetime(train_data['timestamp'], unit='s')) - np.min(pd.to_datetime(train_data['timestamp'], unit='s'))).total_seconds()/60)),\n                                                                   ('timestamp', lambda x: x.count())])\ngrouped_stats.columns = ['start_date', 'end_date', 'nb_minutes', 'nb_datapoints']\ngrouped_stats['missing_datapoints'] = grouped_stats['nb_minutes'] - grouped_stats['nb_datapoints'] + 1","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:00:23.678703Z","iopub.execute_input":"2022-01-11T16:00:23.678945Z","iopub.status.idle":"2022-01-11T16:00:51.614308Z","shell.execute_reply.started":"2022-01-11T16:00:23.678919Z","shell.execute_reply":"2022-01-11T16:00:51.613299Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"grouped_stats","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:00:51.615503Z","iopub.execute_input":"2022-01-11T16:00:51.615767Z","iopub.status.idle":"2022-01-11T16:00:51.628141Z","shell.execute_reply.started":"2022-01-11T16:00:51.615736Z","shell.execute_reply":"2022-01-11T16:00:51.627486Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Data missing for many assets, let's see if it's really true\nfor asset in train_data['Asset_Name'].unique():\n    df_asset = train_data[train_data['Asset_Name']==asset].set_index('timestamp')\n    print(asset, (df_asset.index[1:]-df_asset.index[:-1]).value_counts().head(2))\n    print()","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:00:51.629098Z","iopub.execute_input":"2022-01-11T16:00:51.629679Z","iopub.status.idle":"2022-01-11T16:01:39.208038Z","shell.execute_reply.started":"2022-01-11T16:00:51.629643Z","shell.execute_reply":"2022-01-11T16:01:39.207312Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Data Imputation","metadata":{}},{"cell_type":"code","source":"# Let's fill missing asset values using forward fill\n# value imputed is the last valid value, this prevents any lookahead (that would have been introduced by backward fill for instance)\ntrain = pd.DataFrame([])\nfor asset in train_data['Asset_Name'].unique():\n    df_asset = train_data[train_data['Asset_Name']==asset].set_index('timestamp')\n    df_asset = df_asset.reindex(range(df_asset.index[0], df_asset.index[-1]+60, 60), method='ffill')\n    print(asset, (df_asset.index[1:]-df_asset.index[:-1]).value_counts().head()) # Are gaps filled correctly?\n    train = pd.concat([train, df_asset])\ntrain.reset_index(level=0, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:01:39.21019Z","iopub.execute_input":"2022-01-11T16:01:39.210402Z","iopub.status.idle":"2022-01-11T16:02:40.216833Z","shell.execute_reply.started":"2022-01-11T16:01:39.210376Z","shell.execute_reply":"2022-01-11T16:02:40.215919Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Exploratory Data Analysis","metadata":{"execution":{"iopub.status.busy":"2022-01-10T15:36:07.896114Z","iopub.execute_input":"2022-01-10T15:36:07.897161Z","iopub.status.idle":"2022-01-10T15:36:07.926103Z","shell.execute_reply.started":"2022-01-10T15:36:07.897093Z","shell.execute_reply":"2022-01-10T15:36:07.925259Z"}}},{"cell_type":"markdown","source":"### Classical Methods\n\nFirst we'll approach this data set with classical, non time series-specific methods.","metadata":{}},{"cell_type":"code","source":"# Let's visualise some of the data at hand\n# Let's focus on 2021 Bitcoin and Monero\nbtc = train[train['Asset_Name']=='Bitcoin'].set_index('timestamp')\nmnr = train[train['Asset_Name']=='Monero'].set_index('timestamp')\neth = train[train['Asset_Name']=='Ethereum'].set_index('timestamp')","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:02:40.218178Z","iopub.execute_input":"2022-01-11T16:02:40.218424Z","iopub.status.idle":"2022-01-11T16:02:51.211498Z","shell.execute_reply.started":"2022-01-11T16:02:40.218391Z","shell.execute_reply":"2022-01-11T16:02:51.210334Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(f'Bitcoin NaN values: {btc.isna().sum()[btc.isna().sum()!=0]}')\nprint(f'Monero NaN values: {mnr.isna().sum()[mnr.isna().sum()!=0]}')\nprint(f'Ethereum NaN values: {eth.isna().sum()[eth.isna().sum()!=0]}')\n\n\nprint('\\nDropping NaN values...')\nfor df in [btc, mnr, eth]:\n    df.dropna(inplace=True)\n    df.reset_index(inplace=True)\n    \nprint('...done.')","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:02:51.212885Z","iopub.execute_input":"2022-01-11T16:02:51.213189Z","iopub.status.idle":"2022-01-11T16:02:53.682198Z","shell.execute_reply.started":"2022-01-11T16:02:51.21315Z","shell.execute_reply":"2022-01-11T16:02:53.681333Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# log of Close values is chosen because of the different price scales\nf = plt.figure(figsize=(10,7))\nplt.plot(np.log(btc['Close']), label='BTC');\nplt.plot(np.log(mnr['Close']), label='MNR');\nplt.plot(np.log(eth['Close']), label='ETH');\nplt.title('Closing asset value over time');\nplt.xlabel('Time');\nplt.ylabel('log(close)');\nplt.legend();","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:02:53.683577Z","iopub.execute_input":"2022-01-11T16:02:53.683896Z","iopub.status.idle":"2022-01-11T16:02:59.233322Z","shell.execute_reply.started":"2022-01-11T16:02:53.683856Z","shell.execute_reply":"2022-01-11T16:02:59.232426Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Are percentage changes in prices kind of correlated as would Close values be ?\nf = plt.figure(figsize=(10,7))\nplt.plot(btc['Close'].pct_change(), label='BTC');\nplt.plot(mnr['Close'].pct_change(), label='MNR');\nplt.plot(eth['Close'].pct_change(), label='ETH');\nplt.title('Percentage changes in asset closing values over time');\nplt.xlabel('Time');\nplt.ylabel('Close % change');\nplt.legend();","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:02:59.234409Z","iopub.execute_input":"2022-01-11T16:02:59.234627Z","iopub.status.idle":"2022-01-11T16:03:02.997284Z","shell.execute_reply.started":"2022-01-11T16:02:59.2346Z","shell.execute_reply":"2022-01-11T16:03:02.996649Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Histogram of Close values for each asset\nf = plt.figure(figsize=(10,7))\nnp.log(btc['Close']).hist(label='BTC');\nnp.log(mnr['Close']).hist(label='MNR');\nnp.log(eth['Close']).hist(label='ETH');\nplt.xlabel('log(close)');\nplt.ylabel('Count');\nplt.title('Distribution of asset closing values');\nplt.legend()\nplt.grid(False);","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:03:02.99829Z","iopub.execute_input":"2022-01-11T16:03:02.999092Z","iopub.status.idle":"2022-01-11T16:03:03.504032Z","shell.execute_reply.started":"2022-01-11T16:03:02.999053Z","shell.execute_reply":"2022-01-11T16:03:03.503233Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Histogram of Close value changes for each asset\nf = plt.figure(figsize=(10,7))\nbtc['Close'].pct_change().hist(alpha=0.5, label='BTC');\nmnr['Close'].pct_change().hist(alpha=0.3, label='MNR');\neth['Close'].pct_change().hist(alpha=0.3, label='ETH');\nplt.xlabel('Close % change');\nplt.ylabel('Count');\nplt.title('Distribution of percentage changes in closing asset values');\nplt.legend();\nplt.grid(False);","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:03:03.505143Z","iopub.execute_input":"2022-01-11T16:03:03.505375Z","iopub.status.idle":"2022-01-11T16:03:04.011953Z","shell.execute_reply.started":"2022-01-11T16:03:03.505349Z","shell.execute_reply":"2022-01-11T16:03:04.011292Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# So apparently % changes are very very centered around 0...\n# Maybe it's due to the recording resolution, and such changes would appear higher with weekly sampled data","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:03:04.012889Z","iopub.execute_input":"2022-01-11T16:03:04.013541Z","iopub.status.idle":"2022-01-11T16:03:04.016683Z","shell.execute_reply.started":"2022-01-11T16:03:04.013506Z","shell.execute_reply":"2022-01-11T16:03:04.015945Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Dataset for correlations between BTC and ETH\nbtc_eth = pd.merge(btc, eth, on='datetime', how='inner')\nbtc_eth.drop('timestamp_y', axis=1, inplace=True)\n\ncols = list()\nfor col in btc_eth.columns:\n    if col == 'timestamp_x':\n        col = col.replace('_x', '')\n    else:\n        col = col.replace('_x', '_BTC').replace('_y', '_ETH')\n    cols.append(col)\n    \nbtc_eth.columns = cols","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:03:04.017545Z","iopub.execute_input":"2022-01-11T16:03:04.018188Z","iopub.status.idle":"2022-01-11T16:03:05.501058Z","shell.execute_reply.started":"2022-01-11T16:03:04.018154Z","shell.execute_reply":"2022-01-11T16:03:05.500287Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Scatter plot of Close values between BTC and ETH\nf = plt.figure(figsize=(10,7))\nplt.scatter(x=btc_eth['Close_BTC'].pct_change(), y=btc_eth['Close_ETH'].pct_change());\nplt.xlabel('BTC close value % change');\nplt.ylabel('ETH close value % change');\nplt.title('ETH close value vs. BTC close value.');","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:03:05.502848Z","iopub.execute_input":"2022-01-11T16:03:05.503172Z","iopub.status.idle":"2022-01-11T16:03:11.943343Z","shell.execute_reply.started":"2022-01-11T16:03:05.50313Z","shell.execute_reply":"2022-01-11T16:03:11.942399Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# ETH and BTC seem to be very positively linearly correlated...\nbtc_eth[['Close_BTC', 'Close_ETH']].corr()","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:03:11.944697Z","iopub.execute_input":"2022-01-11T16:03:11.944952Z","iopub.status.idle":"2022-01-11T16:03:12.007668Z","shell.execute_reply.started":"2022-01-11T16:03:11.944921Z","shell.execute_reply":"2022-01-11T16:03:12.006784Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# ...but this actually doesn't teach us much.\n# Indeed, positive linear correlation depicts the fact that when BTC close value increases, so does ETH close value, and vice versa and when they decrease...\n# ...but once BTC has increased, ETH has jointly increased, there are no predictive information here","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:03:12.009062Z","iopub.execute_input":"2022-01-11T16:03:12.00928Z","iopub.status.idle":"2022-01-11T16:03:12.012613Z","shell.execute_reply.started":"2022-01-11T16:03:12.009254Z","shell.execute_reply":"2022-01-11T16:03:12.012033Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# What about the correlation between percentage change in ETH close value and percentage change of the preceding record in BTC close value?\nf = plt.figure(figsize=(10,7))\nplt.scatter(x=btc_eth['Close_BTC'].pct_change(), y=btc_eth['Close_BTC'].pct_change().shift(-1));\nplt.xlabel('Lagged BTC close value % change');\nplt.ylabel('ETH close value % change');\nplt.title('ETH close value vs. BTC close value.');","metadata":{"execution":{"iopub.status.busy":"2022-01-11T16:03:12.013575Z","iopub.execute_input":"2022-01-11T16:03:12.014194Z","iopub.status.idle":"2022-01-11T16:03:18.482864Z","shell.execute_reply.started":"2022-01-11T16:03:12.014161Z","shell.execute_reply":"2022-01-11T16:03:18.482015Z"},"trusted":true},"execution_count":null,"outputs":[]}]}