{"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":"# An attempt to understand missing values\n\nPeople keep on asking what to do with missing values. It depends on the situation, but I am certain 9/10 times you do not want to do anything and you would actually treat `NA` as a number. Well this itself is a hard problem - where do I put `NA` in a number space? There are many things to do, but what really matters is to understand how `NA` are generated in the first place. Having that knowledge you can find unexpected solutions!\n\nLet's dive in into AMEX competition dataset. We are going to use the curated [dataset](https://www.kaggle.com/datasets/raddar/amex-data-integer-dtypes-parquet-format) which contains discovered integer column types.","metadata":{}},{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\npd.set_option('display.max_rows', 200)\n\ntrain = pd.read_parquet('../input/amex-data-integer-dtypes-parquet-format/train.parquet')\ntrain[train==-1] = np.nan # revert -1 to na encoding as in the original dataset","metadata":{"execution":{"iopub.status.busy":"2022-06-18T14:24:04.843593Z","iopub.execute_input":"2022-06-18T14:24:04.844154Z","iopub.status.idle":"2022-06-18T14:24:41.613618Z","shell.execute_reply.started":"2022-06-18T14:24:04.844054Z","shell.execute_reply":"2022-06-18T14:24:41.612492Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's calculate `NA` counts by feature. The idea is to identify feature clusters having the same `NA` values for each row - that is what I will be focusing in this notebook onwards. Let's explore clusters having more than 10 features.","metadata":{}},{"cell_type":"code","source":"cols = sorted(train.columns[2:].tolist())\nnas = train[cols].isna().sum(axis=0).reset_index(name='NA_count')\nnas['group_count'] = nas.loc[nas.NA_count>0].groupby('NA_count').transform('count')\nclusters = nas.loc[nas.group_count>10].sort_values(['NA_count','index']).groupby('NA_count')['index'].apply(list).values\nclusters","metadata":{"execution":{"iopub.status.busy":"2022-06-18T14:24:41.615936Z","iopub.execute_input":"2022-06-18T14:24:41.616474Z","iopub.status.idle":"2022-06-18T14:24:47.590206Z","shell.execute_reply.started":"2022-06-18T14:24:41.616424Z","shell.execute_reply":"2022-06-18T14:24:47.589189Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There are 3 large clusters having many overlapping `NA` rows! Let's analyse each cluster!","metadata":{}},{"cell_type":"code","source":"cluster0_customers = set(train.loc[pd.isnull(train[clusters[0][0]]),'customer_ID'])\ncluster1_customers = set(train.loc[pd.isnull(train[clusters[1][0]]),'customer_ID'])\ncluster2_customers = set(train.loc[pd.isnull(train[clusters[2][0]]),'customer_ID'])","metadata":{"execution":{"iopub.status.busy":"2022-06-18T14:24:47.591739Z","iopub.execute_input":"2022-06-18T14:24:47.592104Z","iopub.status.idle":"2022-06-18T14:24:47.740313Z","shell.execute_reply.started":"2022-06-18T14:24:47.592071Z","shell.execute_reply":"2022-06-18T14:24:47.739328Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## cluster ZERO\n\nLet's just simply look at how the customer profile looks for this feature cluster:","metadata":{}},{"cell_type":"code","source":"train.loc[train.customer_ID.isin(cluster0_customers),['customer_ID','S_2']+clusters[0]].head(100)","metadata":{"execution":{"iopub.status.busy":"2022-06-18T14:24:47.742387Z","iopub.execute_input":"2022-06-18T14:24:47.742749Z","iopub.status.idle":"2022-06-18T14:24:48.280994Z","shell.execute_reply.started":"2022-06-18T14:24:47.742716Z","shell.execute_reply":"2022-06-18T14:24:48.279886Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Were you able to notice the pattern? What if I told you that `NA` appears only on the first observation for each customer! So for this feature cluster `NA` represents fresh credit card accounts with probably zero balance!\n\nLet's double check that `NA` only appears on the first row:","metadata":{}},{"cell_type":"code","source":"# first row\nprint('first row of the customer')\nprint(train.loc[train.customer_ID.isin(cluster0_customers)].groupby('customer_ID')[clusters[0]].head(1).isna().sum(axis=0))\n\n# any row\nprint('any row of the customer')\nprint(train[clusters[0]].isna().sum(axis=0))","metadata":{"execution":{"iopub.status.busy":"2022-06-18T14:24:48.282345Z","iopub.execute_input":"2022-06-18T14:24:48.282701Z","iopub.status.idle":"2022-06-18T14:24:48.994527Z","shell.execute_reply.started":"2022-06-18T14:24:48.282668Z","shell.execute_reply":"2022-06-18T14:24:48.993660Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Assumption correct ^","metadata":{}},{"cell_type":"markdown","source":"### How could I use this information?\n\nWe know that the dataset has varying number of available observations for each customer (N=1,...,13). It is so far obvious that `last` type features are the strongest ones to build a model.\n\nFor N=1 it gets a bit interesting, because this `NA` propagates to `last` type features. This may cause headache for non-GBDT models. \n\nLet me show you what I mean - the distribution of `B_33_last` as an example:","metadata":{}},{"cell_type":"code","source":"train.groupby('customer_ID').tail(1).groupby('B_33', dropna=False)['customer_ID'].count()","metadata":{"execution":{"iopub.status.busy":"2022-06-18T14:24:48.996245Z","iopub.execute_input":"2022-06-18T14:24:48.996975Z","iopub.status.idle":"2022-06-18T14:24:51.337992Z","shell.execute_reply.started":"2022-06-18T14:24:48.996906Z","shell.execute_reply":"2022-06-18T14:24:51.337013Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There are 31 `NA` values, each coming from customers having a single entry in the data. So if you had the feature like `number_of_observations`, you could add dummy value for the counter meaning `1 observation and B_X is NA` - I will encode this as 0.5 in the example below:","metadata":{}},{"cell_type":"code","source":"train['number_of_observations'] = train.groupby('customer_ID')['customer_ID'].transform('count')\nprint(train.groupby('customer_ID').tail(1).groupby('number_of_observations')['customer_ID'].count().reset_index())\ntrain.loc[pd.isnull(train.B_33) & (train.number_of_observations==1),'number_of_observations'] = 0.5\nprint(train.groupby('customer_ID').tail(1).groupby('number_of_observations')['customer_ID'].count().reset_index())","metadata":{"execution":{"iopub.status.busy":"2022-06-18T14:24:51.339558Z","iopub.execute_input":"2022-06-18T14:24:51.339916Z","iopub.status.idle":"2022-06-18T14:24:59.922591Z","shell.execute_reply.started":"2022-06-18T14:24:51.339884Z","shell.execute_reply":"2022-06-18T14:24:59.921370Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"And now you are safe to set all `last` type features' `NA` to zero without losing any information. This means less categories for tree methods, and less hassle for building your neural networks.","metadata":{}},{"cell_type":"code","source":"train.loc[train.number_of_observations==0.5,['customer_ID','S_2','number_of_observations']+clusters[0]].fillna(0)","metadata":{"execution":{"iopub.status.busy":"2022-06-18T14:24:59.924167Z","iopub.execute_input":"2022-06-18T14:24:59.924479Z","iopub.status.idle":"2022-06-18T14:25:00.000931Z","shell.execute_reply.started":"2022-06-18T14:24:59.924449Z","shell.execute_reply":"2022-06-18T14:25:00.000042Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Another observation could be made that for aggregate type features (min/max/mean,...), it is safe to not include `NA` in any way (the information is already stored in `number_of_observations=0.5`). So using functions like `np.nanmin`, `np.nanmax`, etc. are prefered","metadata":{}},{"cell_type":"markdown","source":"## cluster ONE\n\nAgain, simply look at what the data has to show:","metadata":{}},{"cell_type":"code","source":"train.loc[train.customer_ID.isin(cluster1_customers),['customer_ID','S_2']+clusters[1]].head(100)","metadata":{"execution":{"iopub.status.busy":"2022-06-18T14:25:00.002687Z","iopub.execute_input":"2022-06-18T14:25:00.003216Z","iopub.status.idle":"2022-06-18T14:25:01.354316Z","shell.execute_reply.started":"2022-06-18T14:25:00.003166Z","shell.execute_reply":"2022-06-18T14:25:01.353156Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"This appears similar to cluster ZERO. However, not all `NA` are in the first row!","metadata":{}},{"cell_type":"code","source":"# first row\nprint('first row of the customer')\nprint(train.loc[train.customer_ID.isin(cluster1_customers)].groupby('customer_ID')[clusters[1]].head(1).isna().sum(axis=0))\n\n# any row\nprint('any row of the customer')\nprint(train[clusters[1]].isna().sum(axis=0))","metadata":{"execution":{"iopub.status.busy":"2022-06-18T14:25:01.357158Z","iopub.execute_input":"2022-06-18T14:25:01.357506Z","iopub.status.idle":"2022-06-18T14:25:03.129045Z","shell.execute_reply.started":"2022-06-18T14:25:01.357472Z","shell.execute_reply":"2022-06-18T14:25:03.127722Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"In fact, half of the `NA` are in first row and other are in random positions in the timeline. I have not yet found any pattern for the latter.\n\nSo, what can be done!?\n\nFor first row `NA` we can apply the same logic as in cluster ZERO - will leave this as an exercise for you :)\n\nAs for randomly appearing `NA` I think it is worth doing some kind of imputation. Can't stress this enough, but THE MODEL MUST VERIFY THAT IMPUTATION IS A VALID APPROACH! It may be obvious for you that imputation is a legitimate strategy, but the model you will build has to confirm it, meaning that the model with imputed values should perform no worse than with originally missing values. This is how you test the hypothesis if the `NA` is missing at random or not.\n\n\nWhat would I do here? I would probably take average values of `t-1` and `t+1` values for `NA` at timestep `t`. It is pretty straightforward to do so I won't cover this in this notebook. Let's better finish up with the last cluster!","metadata":{}},{"cell_type":"markdown","source":"## cluster TWO\n\nOnce again, simply look at what the data has to show:","metadata":{}},{"cell_type":"code","source":"train.loc[train.customer_ID.isin(cluster2_customers),['customer_ID','S_2']+clusters[2]].head(100)","metadata":{"execution":{"iopub.status.busy":"2022-06-18T14:25:03.131050Z","iopub.execute_input":"2022-06-18T14:25:03.131570Z","iopub.status.idle":"2022-06-18T14:25:04.372562Z","shell.execute_reply.started":"2022-06-18T14:25:03.131519Z","shell.execute_reply":"2022-06-18T14:25:04.371171Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"If you looked closely enough you would identify that we have rolling `NA` values and the rolling stops when the first numeric information appears, and `NA` no longer reappears. Let's confirm this with a code:","metadata":{}},{"cell_type":"code","source":"train['cluster2_NA'] = 0\ntrain.loc[pd.isnull(train[clusters[2][0]]),'cluster2_NA'] = 1\ntrain['rank'] = train.groupby('customer_ID')['S_2'].rank()\n\nnot_na = train.loc[train.cluster2_NA==0].groupby('customer_ID')['rank'].agg(('min','max'))\nyes_na = train.loc[train.cluster2_NA==1].groupby('customer_ID')['rank'].agg(('min','max'))\nc2 = pd.merge(yes_na,not_na,on='customer_ID')","metadata":{"execution":{"iopub.status.busy":"2022-06-18T14:25:49.537874Z","iopub.execute_input":"2022-06-18T14:25:49.538411Z","iopub.status.idle":"2022-06-18T14:26:10.663629Z","shell.execute_reply.started":"2022-06-18T14:25:49.538375Z","shell.execute_reply":"2022-06-18T14:26:10.662852Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The idea is that max rank of `NA` should be lower than min rank of non-`NA`. Let's see if it holds:","metadata":{}},{"cell_type":"code","source":"c2.loc[c2.max_x>c2.min_y].shape[0], c2.loc[c2.max_x<c2.min_y].shape[0]","metadata":{"execution":{"iopub.status.busy":"2022-06-18T14:28:45.972285Z","iopub.execute_input":"2022-06-18T14:28:45.972731Z","iopub.status.idle":"2022-06-18T14:28:45.992546Z","shell.execute_reply.started":"2022-06-18T14:28:45.972697Z","shell.execute_reply":"2022-06-18T14:28:45.991012Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#example customer timeframe showing that `NA` can start from the first observation\ntrain.loc[train.customer_ID == 'fffe2bc02423407e33a607660caeed076d713d8a5ad32321530e92704835da88',['customer_ID','S_2']+clusters[2]]","metadata":{"execution":{"iopub.status.busy":"2022-06-18T14:39:38.768799Z","iopub.execute_input":"2022-06-18T14:39:38.769275Z","iopub.status.idle":"2022-06-18T14:39:39.529864Z","shell.execute_reply.started":"2022-06-18T14:39:38.769241Z","shell.execute_reply":"2022-06-18T14:39:39.529109Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#example customer timeframe showing that `NA` does not always start from the first observation\ntrain.loc[train.customer_ID == '002259d195fcb87b15b98503ac5d2c4d29bc8383053ca3ad8951c598fbb6812a',['customer_ID','S_2']+clusters[2]]","metadata":{"execution":{"iopub.status.busy":"2022-06-18T14:39:11.950844Z","iopub.execute_input":"2022-06-18T14:39:11.951301Z","iopub.status.idle":"2022-06-18T14:39:12.703460Z","shell.execute_reply.started":"2022-06-18T14:39:11.951267Z","shell.execute_reply":"2022-06-18T14:39:12.702324Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"So my initial hypothesis was incorrect! Although 60922 customers do have rolling `NA` from first observation, however there are 6879 `NA` in the mix which may or may not be missing at random. So as in cluster ONE, we have two types of `NA` here! And things gets really complicated from here. One thing is obvious - `NA` count features for rolling first `NA` could be very useful for the models. However it is not clear if other `NA` should be included in those calculations or not! I am not convinced that they should. But the models could help you answer that!","metadata":{"execution":{"iopub.status.busy":"2022-06-18T14:28:36.216077Z","iopub.execute_input":"2022-06-18T14:28:36.216873Z","iopub.status.idle":"2022-06-18T14:28:36.225492Z","shell.execute_reply.started":"2022-06-18T14:28:36.216834Z","shell.execute_reply":"2022-06-18T14:28:36.223742Z"}}},{"cell_type":"markdown","source":"# Summary\n\nThis dataset is rich of `NA` information. We have both systemic `NA` and very likely missing at random `NA`. This means feature-wise `NA` should not be treated equally when doing any kind of feature engineering.\n\nOn top of that, I have showed you that it is possible to identify many interesting `NA` properties just by looking at 100 rows in the dataset.\n\nHope you enjoyed this one. I sure did while I was making this!","metadata":{}}]}