{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.14","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":84493,"databundleVersionId":9871156,"sourceType":"competition"}],"dockerImageVersionId":30804,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"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","trusted":true,"execution":{"iopub.status.busy":"2024-12-10T01:22:52.231824Z","iopub.execute_input":"2024-12-10T01:22:52.232226Z","iopub.status.idle":"2024-12-10T01:22:52.727895Z","shell.execute_reply.started":"2024-12-10T01:22:52.232174Z","shell.execute_reply":"2024-12-10T01:22:52.726806Z"},"collapsed":true,"jupyter":{"outputs_hidden":true}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n#Step1 读取数据\n#并筛选出来包含空值的列\nimport  polars as pl\npath = r'/kaggle/input/jane-street-real-time-market-data-forecasting/train.parquet/partition_id=0/part-0.parquet'\ndf = pl.read_parquet(path)\nprint(df.shape)\n\n# 获取包含空值的列名，任何包含了空值的feature\ncols_with_nulls = [col for col in df.columns\n                   if df[col].is_null().any()]\nprint(cols_with_nulls)\n\n# 筛选出列 'feature_00' 中为空的行\ndf_null_in_a = df.filter(pl.col(\"feature_00\").is_null()).select(['date_id','time_id','symbol_id','feature_00'])\nprint(df_null_in_a)\n\nprint(df_null_in_a.shape)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-10T01:23:03.59974Z","iopub.execute_input":"2024-12-10T01:23:03.600263Z","iopub.status.idle":"2024-12-10T01:23:06.481952Z","shell.execute_reply.started":"2024-12-10T01:23:03.600222Z","shell.execute_reply":"2024-12-10T01:23:06.480588Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"'''\nStep2\n对每个column调用describe函数，用来展示为空值和非空值的数量\n并计算每个feature中空值在所有数值中的占比，并按照降序排序为1代表全部为空\n'''\n\npd.set_option('display.max_rows',None)\n#循环每个列名，然后做describe操作\ncount_list = []\nnull_count_list = []\nfor col in df.columns:\n    describe_df = df[col].describe()\n    count = describe_df.filter(pl.col('statistic')=='count').select('value').row(0)[0]\n    count_list.append(count)\n    null_count = describe_df.filter(pl.col('statistic')=='null_count').select('value').row(0)[0]\n    null_count_list.append(null_count)\n#创建一个字典包含各个列名，每个列名对应的空值和正常值的数量\ndata = {'col':df.columns,\n       'count':count_list,\n       'null_count':null_count_list}\n# 从字典创建dataframe\ncount_df = pd.DataFrame(data,\n                      index = range(len(df.columns)))\ncount_df['null_ratio'] = count_df['null_count']/(count_df['count']+count_df['null_count'])\ncount_df = count_df[count_df['null_count']>0]\ncount_df = count_df.sort_values('null_ratio',ascending=False)\ncount_df = count_df.reset_index()\nprint(count_df)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-09T10:06:16.962164Z","iopub.execute_input":"2024-12-09T10:06:16.9626Z","iopub.status.idle":"2024-12-09T10:06:21.067437Z","shell.execute_reply.started":"2024-12-09T10:06:16.962561Z","shell.execute_reply":"2024-12-09T10:06:21.066359Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"8 features have no value all null, can drop\nelse max null ratio is 0.167025,can fill by some method include bfill, ffill or mean fill or some other methods","metadata":{}},{"cell_type":"code","source":"'''\nStep3\n统计每个代码，每个feature上为空值的数值占比，并计算空值的比例，为1代表全部为空\n'''\n#check every code and every feature how many value is null\n\n#first cal the totla time series length of all time idx\ntime_df = df.select(['date_id','time_id'])\ntime_df = time_df.unique()\ntotal_time_length = len(time_df)\nprint(total_time_length)\n\n#筛选出所有的股票代码，并去重\nsymbol_list = list(set(df['symbol_id'].to_list()))\n\ndf_symbol_list = []\ndf_feature_list = []\ncount_list = []\nnull_count_list = []\n#循环每一个股票代码\nfor symbol in symbol_list:\n    #循环每一个col，除了['date_id','time_id','symbol_id']\n    for col in df.columns:\n        if col in ['date_id','time_id','symbol_id']:\n            continue\n        each_iter = df.filter(pl.col('symbol_id') == symbol)\n        df_symbol_list.append(symbol)\n        #截取每个symbol对应的col做describe函数分析\n        describe_df = each_iter[col].describe()\n        df_feature_list.append(col)\n        #统计其中不为空的数量\n        count = describe_df.filter(pl.col('statistic')=='count').select('value').row(0)[0]\n        count_list.append(count)\n        #统计其中为空的数量\n        null_count = describe_df.filter(pl.col('statistic')=='null_count').select('value').row(0)[0]\n        null_count_list.append(null_count)\neach_symbol_null_stat = pd.DataFrame(data={'symbol':df_symbol_list,\n                                          'feature':df_feature_list,\n                                          'count':count_list,\n                                          'null_count':null_count_list},\n                                    index=range(len(df_symbol_list))\n                                    )\neach_symbol_null_stat['null_ratio'] = each_symbol_null_stat['null_count']/(each_symbol_null_stat['count']+each_symbol_null_stat['null_count'])\nprint(each_symbol_null_stat.head(20))","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-09T10:25:03.528779Z","iopub.execute_input":"2024-12-09T10:25:03.529197Z","iopub.status.idle":"2024-12-09T10:25:56.484564Z","shell.execute_reply.started":"2024-12-09T10:25:03.529162Z","shell.execute_reply":"2024-12-09T10:25:56.483433Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"'''\n本来计划是做折线图展示每个代码每个symbol的数值情况，为空的部分做在图中作展示\n但是因为时间序列上长度太长，很多点合并到一起根本显示不出来\n'''\nimport matplotlib.pyplot as plt\n\n#plot line graph to show the missing value\n#each iteration one code and one col to loop\n#skip all null value, only show partial missing value\n\neach_symbol_null_stat = each_symbol_null_stat[each_symbol_null_stat['count']>0]\nfor index in each_symbol_null_stat.index:\n    symbol,feature,count,null_count = each_symbol_null_stat.loc[index]\n    if null_count == 0:\n        continue\n    print(symbol,feature,count,null_count)\n    each_iter_df = df.filter(pl.col('symbol_id')==symbol).select(['date_id','time_id','symbol_id',feature])\n    print(each_iter_df)\n    # 获取 DataFrame 的长度\n    length = len(each_iter_df)\n    \n    # 创建一个新的列，值为 range(length)\n    new_column = pl.Series(\"plot_index\", range(length))\n    \n    # 使用 with_column 方法增加新的一列\n    each_iter_df_with_plot_index = each_iter_df.with_columns(new_column)\n    \n    # 打印增加新列后的 DataFrame\n    print(each_iter_df_with_plot_index)\n\n    #用折线图展示在整个时序上的空值情况\n    # 提取列 \"A\" 和 \"B\"\n    selected_df = each_iter_df_with_plot_index.select([\"plot_index\", feature])\n    \n    # 提取列 \"A\" 和 \"B\" 的值\n    x = selected_df['plot_index'].to_list()\n    y = selected_df[feature].to_list()\n    \n    # 创建折线图\n    plt.plot(x, y)\n    \n    # 添加标题和标签\n    plt.title('%s %s value line plot'%(symbol, feature))\n    plt.xlabel('X axis')\n    plt.ylabel('Y axis')\n    \n    # 显示图形\n    plt.show()\n    \n    break","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-04T14:35:27.820414Z","iopub.execute_input":"2024-12-04T14:35:27.820778Z","iopub.status.idle":"2024-12-04T14:35:28.266249Z","shell.execute_reply.started":"2024-12-04T14:35:27.820748Z","shell.execute_reply":"2024-12-04T14:35:28.264911Z"},"jupyter":{"source_hidden":true,"outputs_hidden":true},"collapsed":true},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"单个symbol和单个feature上的数据量都比较大，有14万左右的数据，从折线图上比较难看出来null值情况，要么就是截取观察，要么就是同一的方式处理，ffill可能相对合理","metadata":{}},{"cell_type":"code","source":"'''\n本意是做部分的截取，但是这个还需要在完善一下，不过对于fill missing的意义也没有很大\n'''\n\n#展示整个symbol的数据，长度太长了，不适合在图形中显示，我只截取其中空值前后的数字做展示\nimport matplotlib.pyplot as plt\n\n#only filter null value and Select non-null value  前后五个不为空的值\n\neach_symbol_null_stat = each_symbol_null_stat[each_symbol_null_stat['count']>0]\nfor index in each_symbol_null_stat.index:\n    symbol,feature,count,null_count = each_symbol_null_stat.loc[index]\n    if null_count == 0:\n        continue\n    print(symbol,feature,count,null_count)\n    each_iter_df = df.filter(pl.col('symbol_id')==symbol).select(['date_id','time_id','symbol_id',feature])\n    # 获取 DataFrame 的长度\n    length = len(each_iter_df)\n    \n    # 创建一个新的列，值为 range(length)\n    new_column = pl.Series(\"plot_index\", range(length))\n\n    # 使用 with_column 方法增加新的一列\n    each_iter_df_with_plot_index = each_iter_df.with_columns(new_column)\n    print(each_iter_df_with_plot_index.head())\n    \n    # 过滤出列 feature 上所有值为空的行\n    null_rows = each_iter_df_with_plot_index.filter(pl.col(feature).is_null())\n\n    # 获取空值的索引\n    null_indices = null_rows.select('plot_index').to_series().to_list()\n\n    for idx in null_indices:\n        start_idx = max(0, idx - 5)\n        end_idx = min(len(each_iter_df), idx + 6)\n        sliced_df = each_iter_df[start_idx:end_idx]\n        print(sliced_df)\n        break\n    break","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-04T14:37:24.787671Z","iopub.execute_input":"2024-12-04T14:37:24.788255Z","iopub.status.idle":"2024-12-04T14:37:24.848184Z","shell.execute_reply.started":"2024-12-04T14:37:24.788198Z","shell.execute_reply":"2024-12-04T14:37:24.847131Z"},"collapsed":true,"jupyter":{"source_hidden":true,"outputs_hidden":true}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"one_code_df = df.filter(pl.col('symbol_id')==0)\none_code_one_date_df = one_code_df.filter(pl.col('date_id')==96)\none_code_one_date_df = one_code_one_date_df.select(['date_id','time_id','symbol_id',feature])\nprint(one_code_one_date_df)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-04T14:43:13.918725Z","iopub.execute_input":"2024-12-04T14:43:13.919136Z","iopub.status.idle":"2024-12-04T14:43:13.967436Z","shell.execute_reply.started":"2024-12-04T14:43:13.919098Z","shell.execute_reply":"2024-12-04T14:43:13.966355Z"},"jupyter":{"source_hidden":true,"outputs_hidden":true},"collapsed":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"'''\nStep4 将polars dataframe 转成pandas dataframe\n'''\n#将polars dataframe转成pandas datafr ame\npd_df = df.to_pandas()\nprint(pd_df.head())","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-10T01:23:22.037895Z","iopub.execute_input":"2024-12-10T01:23:22.038278Z","iopub.status.idle":"2024-12-10T01:23:23.130868Z","shell.execute_reply.started":"2024-12-10T01:23:22.038246Z","shell.execute_reply":"2024-12-10T01:23:23.129701Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"'''\n统计在当前数据集时间长度为多长\n'''\none_day_time_list = pd_df['time_id'].unique().tolist()\none_day_time = len(one_day_time_list)\nprint('there are %s time index in one day'%one_day_time)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-05T00:57:50.898391Z","iopub.execute_input":"2024-12-05T00:57:50.898958Z","iopub.status.idle":"2024-12-05T00:57:50.923332Z","shell.execute_reply.started":"2024-12-05T00:57:50.898904Z","shell.execute_reply":"2024-12-05T00:57:50.921937Z"},"jupyter":{"source_hidden":true}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"there are 849 time index in one day","metadata":{}},{"cell_type":"code","source":"#确认是否每天都是849个time_id\ntime_num_df = pd_df.groupby(['date_id','symbol_id'],as_index=False)['time_id'].count()\nprint(time_num_df)\ntime_num_df.describe()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-05T01:14:12.427948Z","iopub.execute_input":"2024-12-05T01:14:12.428497Z","iopub.status.idle":"2024-12-05T01:14:12.5716Z","shell.execute_reply.started":"2024-12-05T01:14:12.428445Z","shell.execute_reply":"2024-12-05T01:14:12.570314Z"},"collapsed":true,"jupyter":{"source_hidden":true,"outputs_hidden":true}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"every date_id include 849 time_id","metadata":{}},{"cell_type":"code","source":"'''\n统计当前数据集中有多少个日期\n'''\n#want to know how many date in data set\ndate_list = pd_df['date_id'].unique().tolist()\ndate_num = len(date_list)\nprint('there are %s days in data set'%(date_num))","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-05T01:46:09.891035Z","iopub.execute_input":"2024-12-05T01:46:09.891408Z","iopub.status.idle":"2024-12-05T01:46:09.908172Z","shell.execute_reply.started":"2024-12-05T01:46:09.891376Z","shell.execute_reply":"2024-12-05T01:46:09.906901Z"},"jupyter":{"source_hidden":true}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"'''\n统计当前数据集中有多少个symbol\n'''\n#want to know how many code in data set\nsymbol_list = pd_df['symbol_id'].unique().tolist()\nsymbol_num = len(symbol_list)\nprint('there are %s symbols in data set'%(symbol_num))","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-06T00:57:26.32151Z","iopub.execute_input":"2024-12-06T00:57:26.321956Z","iopub.status.idle":"2024-12-06T00:57:26.343711Z","shell.execute_reply.started":"2024-12-06T00:57:26.321921Z","shell.execute_reply":"2024-12-06T00:57:26.342443Z"},"jupyter":{"source_hidden":true}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"20 * 170 * 849 = 2886600 the data set include 1944210 rows","metadata":{}},{"cell_type":"code","source":"# 获取包含空值的列名，cols_with_nulls\n#逐个的检查是否null都存在于一天之中，如果一个code一天之内的数据全为空，可能代表这个标的今天停牌，需要用之前的数据进行填充 \n#遍历每一个为空的column 过滤掉那些全部为空的列\n#目前统计的是每个symbol 以及每个feature在每个date上为null值的time_id的数量，以此来检查如果某一天的数据为空时是全天为空还是部分时间为空\nsymbol_list = pd_df['symbol_id'].unique().tolist() #数据集中的所有标的代码\nfull_null = each_symbol_null_stat[each_symbol_null_stat['null_ratio']==1][['symbol','feature']] #筛选出对应feature全部为空的symbol\n#代表的是数据集中的symbol在这个feature上的值全为空，这种应该drop掉\nfull_null_dict = full_null.groupby('feature')['symbol'].apply(list).to_dict()\n#遍历所有为包含了null值的列\nnull_df_list = []\nfor null_col in cols_with_nulls: #任何包含了空值的feature\n    #获取feature对应的全部为null的symbol list，如果一个symbol在对应的feature下值全为空，那应该drop掉\n    if null_col in full_null_dict.keys():\n        full_null_symbol_list = full_null_dict[null_col]\n    else:\n        full_null_symbol_list = []\n    each_feature_df = pd_df[['date_id','time_id','symbol_id',null_col]]\n    for symbol in symbol_list:\n        #跳过那些symbol对应feature都为空的数据\n        if symbol in full_null_symbol_list:\n            continue\n        #print(symbol,null_col)\n        each_symbol_each_feature_df = each_feature_df[each_feature_df['symbol_id']==symbol]\n        # 一个symbol 一个feature 截取的内容中提取包含null值的行\n        each_symbol_each_feature_df = each_symbol_each_feature_df[each_symbol_each_feature_df[null_col].isnull()]\n        #计算这些为null的内容中，每个date_id下面有多少个time_id，即每天中为空的值的个数是多少，是全部为空还是部分为空\n        time_num_df = each_symbol_each_feature_df.groupby('date_id',as_index=False)['time_id'].count()\n        time_num_df = time_num_df.rename(columns={'time_id':'one_day_null_time_num'})\n        time_num_df['symbol_id'] = symbol\n        time_num_df['feature'] = null_col\n        null_df_list.append(time_num_df)\ntotal_null_df = pd.concat(null_df_list,axis=0)\nprint(total_null_df.head(20))\n    ","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-09T01:01:30.93744Z","iopub.execute_input":"2024-12-09T01:01:30.937867Z","iopub.status.idle":"2024-12-09T01:01:35.853088Z","shell.execute_reply.started":"2024-12-09T01:01:30.937824Z","shell.execute_reply":"2024-12-09T01:01:35.852049Z"},"collapsed":true,"jupyter":{"source_hidden":true,"outputs_hidden":true}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"total_null_df = total_null_df.sort_values(['symbol_id','feature','date_id'])\nprint(total_null_df.head(30))\nsave_path = r'/kaggle/working/missing_check.csv'\ntotal_null_df.to_csv(save_path,index=False)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-06T00:59:28.545456Z","iopub.execute_input":"2024-12-06T00:59:28.545858Z","iopub.status.idle":"2024-12-06T00:59:28.651756Z","shell.execute_reply.started":"2024-12-06T00:59:28.545822Z","shell.execute_reply":"2024-12-06T00:59:28.650411Z"},"collapsed":true,"jupyter":{"source_hidden":true,"outputs_hidden":true}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"'''\n经过之前的统计发现，空值在时间和symbol的分布上存在一些规律也有很大的随机性\n需要进行进一步的处理，具体的操作包括\n1.取出'symbol_id','feature','date_id'这三列，然后按照groupby(['symbol_id','feature'])['date_id'].count()\n对日期的数量进行求和，检查是否存在空值的天数为数据集整体的170天\n2.取出'symbol_id','feature','time_id'这三列，然后对数据进行drop_duplicates()检查是每天存在空值的time_id有多少，是否每天的数量都相等\n如果每天控制数量都相等，并且整体天数为170天，代表的是这个feature本身就有这种属性，在某些time_id上会为空，那我们应该用前值代替作为信号没有变化\n'''\ndate_count = total_null_df.groupby(['symbol_id','feature'],as_index=False)['date_id'].count()\nevery_day_null_time_count = total_null_df[['symbol_id','feature','one_day_null_time_num']].drop_duplicates()\ndate_count = pd.merge(date_count,every_day_null_time_count,on=['symbol_id','feature'],how='outer')\nprint(date_count.head(20))\nsave_path = r'/kaggle/working/missing_date_num_time_num.csv'\ndate_count.to_csv(save_path,index=False)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-06T01:25:18.918259Z","iopub.execute_input":"2024-12-06T01:25:18.918676Z","iopub.status.idle":"2024-12-06T01:25:18.967057Z","shell.execute_reply.started":"2024-12-06T01:25:18.918644Z","shell.execute_reply":"2024-12-06T01:25:18.965752Z"},"collapsed":true,"jupyter":{"source_hidden":true,"outputs_hidden":true}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"最终根据结果来看有些missing值的分布是有规律的，例如每天都固定缺少某些time_id上的值，还有很多feature上的missing值是数量不固定的，存在比较大的波动，但是综合来看的话因为对于投资上的预测根据历史经验来看需要沿用之前的信号值做填充，取均值或者其他方式是否合理，尤其是在使用未来数据上也做好检查和分析","metadata":{}},{"cell_type":"code","source":"'''\nStep5 提取所有的列名,并筛选区中包含feature和不含feature的列名\n'''\nimport re\n#要处理所有feature的missing值，所以先把feature的列名都提取出来\ncolumn_list = pd_df.columns.tolist()\nfeature_list = [col for col in column_list if 'feature' in col]\nprint(feature_list)\n\nnot_feature_list = [col for col in column_list if not 'feature' in col]\nprint(not_feature_list)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-09T10:26:09.75295Z","iopub.execute_input":"2024-12-09T10:26:09.753332Z","iopub.status.idle":"2024-12-09T10:26:09.760174Z","shell.execute_reply.started":"2024-12-09T10:26:09.753301Z","shell.execute_reply":"2024-12-09T10:26:09.758987Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"'''\nStep6 生成date_time_id将date和time进行组合，得到一个新的id标识，按照这个对每个symbol进行ffill填充\n'''\nffill_columns = ['date_id', 'time_id', 'symbol_id'] + feature_list\nmissing_feature_df = pd_df[ffill_columns]\nmissing_feature_df = missing_feature_df.astype({'date_id':int,\n                                              'time_id':int})\nmissing_feature_df  = missing_feature_df.sort_values(by=['symbol_id','date_id', 'time_id'])\nprint(missing_feature_df.head())","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-09T10:26:12.321921Z","iopub.execute_input":"2024-12-09T10:26:12.32289Z","iopub.status.idle":"2024-12-09T10:26:14.123243Z","shell.execute_reply.started":"2024-12-09T10:26:12.32284Z","shell.execute_reply":"2024-12-09T10:26:14.122127Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"In this dataset，symbol_id is Time Series Identifiers，there is no metadata or static features，others dataframe is Time-Varying Features","metadata":{}},{"cell_type":"code","source":"'''\nStep7\nffill missing method\n'''\n\ndef ffill_missing(missing_df):\n    # 确保数据按 'symbol', 'date_id', 'time_id' 排序\n    ffill_df = missing_df.sort_values(by=['symbol_id','date_id','time_id'])\n\n    # 获取所有 feature 列（假设 feature 列名以 'feature_' 开头）\n    feature_cols = [col for col in ffill_df.columns if col.startswith('feature_')]\n\n    # 对每个 symbol 分组，按 feature 列进行 ffill 填充\n    ffill_df[feature_cols] = ffill_df.groupby('symbol_id')[feature_cols].ffill()\n    \n    return ffill_df\n","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-10T02:22:55.18978Z","iopub.execute_input":"2024-12-10T02:22:55.190168Z","iopub.status.idle":"2024-12-10T02:22:55.196827Z","shell.execute_reply.started":"2024-12-10T02:22:55.190137Z","shell.execute_reply":"2024-12-10T02:22:55.195615Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"ffill_df = ffill_missing(missing_feature_df)","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"'''\nStep8\n用missingno 显示填充前后的空缺值情况\n'''\nimport missingno as msno\nimport matplotlib.pyplot as plt\n\n#每次显示5个有空值的列\niter_num = 50\nfreq = 9\n\n\n#missing_feature_df是从数据集中筛选了symbol_id,date_id,time_id再加上所有的feature字段组成了一个新的dataframe\n#missing_feature_df包含了所有的symbol和date还有time\ndf_len = len(missing_feature_df)\ndate_list = np.sort(missing_feature_df['date_id'].unique().tolist())\nytick_list = date_list[::freq]\n\n# ffill_df = missing_feature_df.set_index(['symbol_id','date_time_id']).groupby(level='symbol_id').ffill().reset_index(\n\nshow_num = 5\nshow_cur = 0\n\nfor feature in cols_with_nulls:\n    show_cols = ['date_time', 'symbol_id'] + [feature]\n    print('%s before ffill the missing value msno matrix is '%feature)\n    show_missing_df = missing_feature_df[show_cols]\n    show_missing_df = show_missing_df.pivot(index='date_time',columns='symbol_id',values=feature)\n    msno.matrix(show_missing_df)\n    \n    # #设置新的yticks\n    # plt.yticks(ytick_list)\n    plt.show()\n\n\n   \n    #print(ffill_df[show_cols].head())\n    print('%s after ffill the missing value msno matrix is '%feature)\n    show_ffill_df = ffill_df[show_cols]\n    show_ffill_df = show_ffill_df.pivot(index='date_time',columns='symbol_id',values=feature)\n    msno.matrix(show_ffill_df)\n    plt.show()\n\n    if show_cur == show_num:\n        break\n    else:\n        show_cur += 1","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-09T10:26:18.682495Z","iopub.execute_input":"2024-12-09T10:26:18.68303Z","iopub.status.idle":"2024-12-09T10:26:57.309299Z","shell.execute_reply.started":"2024-12-09T10:26:18.682992Z","shell.execute_reply":"2024-12-09T10:26:57.307651Z"},"collapsed":true,"jupyter":{"outputs_hidden":true}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"在missingno检查缺失值时，ffill填充之后还是存在一些空白，代表缺失数据，分析来看是每个symbol在时间维度上不只是存在缺失值还有某些date_id就不存在","metadata":{}},{"cell_type":"code","source":"#check each symbol data set length\nsymbol_szie = pd_df.groupby('symbol_id').size()\nprint(symbol_szie)\n\n#the result indicate the each symbol's time length is different\n#I want to generate a full time list as each symbol's time index\n#then fill na","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-10T01:23:33.92609Z","iopub.execute_input":"2024-12-10T01:23:33.926598Z","iopub.status.idle":"2024-12-10T01:23:33.988522Z","shell.execute_reply.started":"2024-12-10T01:23:33.92656Z","shell.execute_reply":"2024-12-10T01:23:33.985758Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"'''\n以feature_08和symbol_id 0 为例来做一个检查为什么，ffill之后还是有很多missing value没有填充\nmissing_check是从missing_feature_df中剔除出来了单个symbol和单个date，截取的新dataframe\nfilling_check 是从ffill_df中截取的一个新的dataframe\n唯一的区别就是ffill_df是missing_check经过了ffill的方式填充了缺失值\n'''\ncols = []\nmissing_check = missing_feature_df[['symbol_id','date_id','time_id','feature_08']]\nmissing_check = missing_check[missing_check['symbol_id']==0]\nis_nan = missing_check['feature_08'].isna()\ngaps = is_nan.ne(is_nan.shift()).cumsum()\n# 使用 groupby 找到每个空值段的起始和结束索引\nmissing_result = missing_check[list(is_nan)].groupby(gaps).agg(['first', 'last'])\n\nprint(missing_result.head())\n\nfilling_check = ffill_df[['symbol_id','date_id','time_id','feature_08']]\nfilling_check = filling_check[filling_check['symbol_id']==0]\nis_nan = filling_check['feature_08'].isna()\ngaps = is_nan.ne(is_nan.shift()).cumsum()\n# 使用 groupby 找到每个空值段的起始和结束索引\nfilling_result = filling_check[list(is_nan)].groupby(gaps).agg(['first', 'last'])\n\nprint(filling_result.head())","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-09T10:28:06.687754Z","iopub.execute_input":"2024-12-09T10:28:06.688194Z","iopub.status.idle":"2024-12-09T10:28:07.877773Z","shell.execute_reply.started":"2024-12-09T10:28:06.688158Z","shell.execute_reply":"2024-12-09T10:28:07.876732Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"msno.matrix(missing_check)\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-09T10:30:04.320632Z","iopub.execute_input":"2024-12-09T10:30:04.321098Z","iopub.status.idle":"2024-12-09T10:30:04.763897Z","shell.execute_reply.started":"2024-12-09T10:30:04.321064Z","shell.execute_reply":"2024-12-09T10:30:04.76286Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"msno.matrix(filling_check)\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-09T10:30:53.672439Z","iopub.execute_input":"2024-12-09T10:30:53.672932Z","iopub.status.idle":"2024-12-09T10:30:54.271178Z","shell.execute_reply.started":"2024-12-09T10:30:53.672895Z","shell.execute_reply":"2024-12-09T10:30:54.270142Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"import itertools\n#全部的symbol列表\nfull_symbol_list = pd_df['symbol_id'].unique().tolist()\n#全部的date列表\nfull_date_list = pd_df['date_id'].unique().tolist()\n#全部的time列表\nfull_time_list = pd_df['time_id'].unique().tolist()\n\n#生成所有的date time组合\ndate_time_combinations = list(itertools.product(full_symbol_list,full_date_list,full_time_list))\n\ndate_time_df = pd.DataFrame(date_time_combinations,columns=['symbol_id','date_id','time_id'])\n\nprint(date_time_df.shape)\n\nprint(date_time_df.head())","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-10T02:08:58.248247Z","iopub.execute_input":"2024-12-10T02:08:58.248788Z","iopub.status.idle":"2024-12-10T02:09:00.97295Z","shell.execute_reply.started":"2024-12-10T02:08:58.248735Z","shell.execute_reply":"2024-12-10T02:09:00.971703Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"#将所有symbol在时间上对齐，每个symbol在时间上的长度一致\nfull_time_df = pd_df.copy()\nprint('before emrge the full symbol,date,time combination,the dataframe shape is')\nprint(full_time_df.shape)\n\nfull_time_df = pd.merge(date_time_df,full_time_df,on=['symbol_id','date_id','time_id'],how='left')\nprint('after emrge the full symbol,date,time combination,the dataframe shape is')\nprint(full_time_df.shape)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-10T02:10:39.570752Z","iopub.execute_input":"2024-12-10T02:10:39.571109Z","iopub.status.idle":"2024-12-10T02:10:42.904817Z","shell.execute_reply.started":"2024-12-10T02:10:39.571077Z","shell.execute_reply":"2024-12-10T02:10:42.903721Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"import missingno as msno\nimport matplotlib.pyplot as plt\n\nfull_time_ffill_df = ffill_missing(full_time_df)\n\ncols_with_nulls = ['feature_08']\n\nshow_num = 5\nshow_cur = 0\n\nfor feature in cols_with_nulls:\n    show_cols = ['symbol_id','date_id','time_id'] + [feature]\n    print('%s before ffill the missing value msno matrix is '%feature)\n    show_missing_df = full_time_df[show_cols]\n    show_missing_df = show_missing_df.pivot(index=['date_id','time_id'],columns='symbol_id',values=feature)\n    msno.matrix(show_missing_df)\n    \n    # #设置新的yticks\n    # plt.yticks(ytick_list)\n    plt.show()\n\n\n   \n    #print(ffill_df[show_cols].head())\n    print('%s after ffill the missing value msno matrix is '%feature)\n    show_ffill_df = full_time_ffill_df[show_cols]\n    show_ffill_df = show_ffill_df.pivot(index=['date_id','time_id'],columns='symbol_id',values=feature)\n    msno.matrix(show_ffill_df)\n    plt.show()\n\n    if show_cur == show_num:\n        break\n    else:\n        show_cur += 1","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-10T02:23:52.018361Z","iopub.execute_input":"2024-12-10T02:23:52.018728Z","iopub.status.idle":"2024-12-10T02:24:00.497291Z","shell.execute_reply.started":"2024-12-10T02:23:52.018694Z","shell.execute_reply":"2024-12-10T02:24:00.495684Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"fill missing \nmethod2: impute missing with previous day, question is if time is missing in first day how to deal\nmethod3: time dimension averge profile, the same time_id and symbol_id average value to fill missing\n\nin my reading book and knowledge the is also consider weekday or seasonal,but the DGP is different,so I don't use fill missing by consider weekday or sensonal","metadata":{}}]}