{"cells":[{"metadata":{"_uuid":"3ad4ae8548e8c316ce7892c713d38830aa80ba61"},"cell_type":"markdown","source":"Objective\nThis is my first kernal and primary objective of this notebook is to understand the Elo data. Understand how independent variables are related with target avariable. If possible find out logical transformations required. "},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import os\nprint(os.listdir(\"../input\"))\n#printing datasets available in this competition","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1758ff57f050117f802c06aa8152830501958958"},"cell_type":"markdown","source":"Seven files are available. Lets understand those files. Competition provides following details\n\ntrain.csv - this is training dataset which we can use for building models \n\ntest.csv - this is test dataset which needs to scored for submission\n\nsample_submission.csv - a sample  file for submission  with required format.\n\nhistorical_transactions.csv - up to 3 months' worth of historical transactions for each card_id\n\nmerchants.csv - additional information about all merchants / merchant_ids in the dataset.\n\nnew_merchant_transactions.csv - two months' worth of data for each card_id containing ALL purchases that card_id made at merchant_ids that were not visited in the historical data."},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"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)\nimport os\nimport json\nfrom pandas.io.json import json_normalize\nimport matplotlib.pyplot as plt\nimport seaborn as sns\ncolor = sns.color_palette()\n\n%matplotlib inline\n\nfrom plotly import tools\nimport plotly.offline as py\npy.init_notebook_mode(connected=True)\nimport plotly.graph_objs as go\n\nfrom sklearn import model_selection, preprocessing, metrics\nimport lightgbm as lgb\n\npd.options.mode.chained_assignment = None\npd.options.display.max_columns = 999\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"efbf04046067e4ee306cb878b06a9a40ec4a7b3c"},"cell_type":"code","source":"#read train and test files into python dataframe\ntrain = pd.read_csv(\"../input/train.csv\")\ntest = pd.read_csv(\"../input/test.csv\")\nprint(\"Number of rows and columns in training file : \",train.shape)\nprint(\"Number of rows and columns in test file : \",test.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7e885d0d24617385b48c6cffe11b72662811d8ce"},"cell_type":"code","source":"#lets look at  first five rows of training \ntrain.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"135ef5ee7185db0d8c802b44dfc723acf6f2712d"},"cell_type":"code","source":"#also in test\ntest.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"99681ed2b4715550d2f82343e44c29d6864feb9c"},"cell_type":"markdown","source":"**Observation 1**   Interesting target variables has negative values as well. It will be good it see its distribution."},{"metadata":{"trusted":true,"_uuid":"996891f9c85ecc8ffbc0656c097e00ae9c714361"},"cell_type":"code","source":"# Number of each type of column\ntrain.dtypes.value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"052d706ba0ac2d4780b69f1d2da32b2f5d512d94"},"cell_type":"code","source":"test.dtypes.value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"36d1f4a8392a93ceaae407d3296e36ef7ba0cd50"},"cell_type":"code","source":"# Number of unique classes in each object column\ntrain.select_dtypes('int64').apply(pd.Series.nunique, axis = 0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6c0c45d347232ba1da90109bb15dafcfb77355cf"},"cell_type":"code","source":"# Number of unique classes in each object column\ntest.select_dtypes('int64').apply(pd.Series.nunique, axis = 0)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"374ca02efecfe0e373eb948c2b8d285c3b5026d6"},"cell_type":"markdown","source":"**Observation 2 **:   Train file was read in dataframe.  It shows that there are two objects and 3 integer variables. But unique values for those integer variables 5,3,2 respectively. These are feature variables and since this is encoded data. It could be categorical variables. \nWe should try understanding it better. But lets first see target variable.\n\nTraining and test have similar structure"},{"metadata":{"trusted":true,"_uuid":"8b10acea6af1f6d9f3aa6eefb98aae7b5ff9f700"},"cell_type":"code","source":"plt.figure(figsize=(12,8))\nsns.distplot(train[\"target\"].values, bins=60, kde=False, color=\"blue\")\nplt.title(\"Distribution of Loyalty score\")\nplt.xlabel('Loyalty score', fontsize=12)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"edb555895d3509af36419cff801f05700b96cd30"},"cell_type":"markdown","source":"**Observations 3:**\n    1. It is symmetric distribution.\n    2. Most values are very close to zero \n    3. There is outlier towards left and values less than -30 definately are outlier and we should be careful about this. I may treat these values , but lest count those cases"},{"metadata":{"trusted":true,"_uuid":"d16ce16846759ffc68fd70bfa4e619add752901b"},"cell_type":"code","source":"(train[\"target\"]<-30).sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"5ca0398c3f5e6851b81a24d038deccbc2cdc6b73"},"cell_type":"code","source":"train.groupby(\"feature_1\").card_id.count()\nfig, ax = plt.subplots(1, 3, figsize = (15, 5))\nsns.countplot(train.feature_1,ax=ax[0])\nsns.countplot(train.feature_2,ax=ax[1])\nsns.countplot(train.feature_3,ax=ax[2])\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4a39f52ee03e8721930f1fba6c90cc150340f69f"},"cell_type":"markdown","source":"**Observation 4**\nDistribution of values of Feature 2 and 3 has an order but feature 1 does not have an order.\nLets check the distribution of loyalty score by different values of these features at average level\n\n"},{"metadata":{"trusted":true,"_uuid":"45c8b046e3ead0df41347836714f5dc0564b23e1"},"cell_type":"code","source":"train.groupby(\"feature_1\").target.mean()\nsubsetDataFrame = train[train['target'] >-30]\n#subsetDataFrame.groupby(\"feature_1\").target.mean()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"59e3039b9dda7cf1b7afe3d0dcf8fe13938fca36","scrolled":false},"cell_type":"code","source":"fig, ax = plt.subplots(2,3, figsize = (15, 5))\nsns.catplot(x=\"feature_1\", y=\"target\", kind=\"bar\", data=train,ax=ax[0,0])\nsns.catplot(x=\"feature_2\", y=\"target\", kind=\"bar\", data=train,ax=ax[0,1])\nsns.catplot(x=\"feature_3\", y=\"target\", kind=\"bar\", data=train,ax=ax[0,2])\nsns.catplot(x=\"feature_1\", y=\"target\", kind=\"bar\", data=subsetDataFrame,ax=ax[1,0])\nsns.catplot(x=\"feature_2\", y=\"target\", kind=\"bar\", data=subsetDataFrame,ax=ax[1,1])\nsns.catplot(x=\"feature_3\", y=\"target\", kind=\"bar\", data=subsetDataFrame,ax=ax[1,2])\nplt.show();\n\n### Loyalty score =-30 has impact everywhere","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e158c530968c5f6392021318de91b8526b810fdb"},"cell_type":"markdown","source":"Extreme values are all over in the data. Lets see the distribution of loyalty score by feature values"},{"metadata":{"trusted":true,"_uuid":"91e60dc15747b53694fa253149c63a9c17853942"},"cell_type":"code","source":"\nfig, ax = plt.subplots(1, 3, figsize = (15, 5))\nsns.boxplot(x=\"feature_1\", y=\"target\",showfliers=False, data=train,ax=ax[0])\nsns.boxplot(x=\"feature_2\", y=\"target\",showfliers=False, data=train,ax=ax[1])\nsns.boxplot(x=\"feature_3\", y=\"target\",showfliers=False, data=train,ax=ax[2])\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7e1a131f2e510dc1a92d9f9f3b7a067235b0032e"},"cell_type":"markdown","source":"**Observation 5 **\nDistribution is similar across the feature types and values\n\nLets check the distribution of first active month.  There can be variation in the distribution of active month between test and traing dataset"},{"metadata":{"trusted":true,"_uuid":"3fb2cddb858a6709ef0a556a437ac91faf87f3b3","scrolled":false},"cell_type":"code","source":"train = pd.read_csv(\"../input/train.csv\", parse_dates=[\"first_active_month\"])\ntest = pd.read_csv(\"../input/test.csv\", parse_dates=[\"first_active_month\"])\n\nplt.figure(figsize=(20,6))\nsns.countplot(train.first_active_month,color='green')\nplt.xticks(rotation='vertical')\nplt.xlabel('First active month', fontsize=12)\nplt.show()\n\nplt.figure(figsize=(20,6))\nsns.countplot(test.first_active_month,color='green')\nplt.xticks(rotation='vertical')\nplt.xlabel('First active month', fontsize=12)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f0dfe1eec9881e36dc9968ef1ecea79721fb5c0e"},"cell_type":"markdown","source":"**Observation:**\n    Both training and Test have similar distribution\n    \n   \n    "},{"metadata":{"_uuid":"742e53ce654f2498fc08e7f84fe55679abf3a732"},"cell_type":"markdown","source":"**Historical Transactions:**\n Lets check what is there in historical transaction files"},{"metadata":{"trusted":true,"_uuid":"d377a939d8d36088bfd5ec9257f918af45fd91e3"},"cell_type":"code","source":"hist = pd.read_csv(\"../input/historical_transactions.csv\")\nhist.head()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cd176d5f086ba485f01dcb0ee36fd9cac0c3cb67"},"cell_type":"code","source":"gdf = hist.groupby(\"card_id\")\ngdf = gdf[\"purchase_amount\"].size().reset_index()\ngdf.columns = [\"card_id\", \"num_hist_transactions\"]\ntrain = pd.merge(train, gdf, on=\"card_id\", how=\"left\")\ntest = pd.merge(test, gdf, on=\"card_id\", how=\"left\")\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"85ce328a2f9266625cd2ee96cc0239a3c89b8022"},"cell_type":"code","source":"hist.select_dtypes('object').apply(pd.Series.nunique, axis = 0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2992ff37159919e0b1ca8226a818e3ec33ce717d"},"cell_type":"code","source":"#creating features using numeric variables in the history file by aggregating it at card_id level\ndef agg_numeric(df, parent_var, df_name):\n    \"\"\"\n    Groups and aggregates the numeric values in a child dataframe\n    by the parent variable.\n    \n    Parameters\n    --------\n        df (dataframe): \n            the child dataframe to calculate the statistics on\n        parent_var (string): \n            the parent variable used for grouping and aggregating\n        df_name (string): \n            the variable used to rename the columns\n        \n    Return\n    --------\n        agg (dataframe): \n            a dataframe with the statistics aggregated by the `parent_var` for \n            all numeric columns. Each observation of the parent variable will have \n            one row in the dataframe with the parent variable as the index. \n            The columns are also renamed using the `df_name`. Columns with all duplicate\n            values are removed. \n    \n    \"\"\"\n\t # Remove id variables other than grouping variable\n    for col in df:\n        if col != parent_var and 'card_id' in col:\n            df = df.drop(columns = col)\n            \n    # Only want the numeric variables\n    parent_ids = df[parent_var].copy()\n    numeric_df = df.select_dtypes('number').copy()\n    numeric_df[parent_var] = parent_ids\n\n    # Group by the specified variable and calculate the statistics\n    agg = numeric_df.groupby(parent_var).agg([ 'mean', 'max', 'min', 'sum'])\n\n    # Need to create new column names\n    columns = []\n\n    # Iterate through the variables names\n    for var in agg.columns.levels[0]:\n        if var != parent_var:\n            # Iterate through the stat names\n            for stat in agg.columns.levels[1]:\n                # Make a new column name for the variable and stat\n                columns.append('%s_%s_%s' % (df_name, var, stat))\n    \n    agg.columns = columns\n    \n    # Remove the columns with all redundant values\n    _, idx = np.unique(agg, axis = 1, return_index=True)\n    agg = agg.iloc[:, idx]\n    \n    return agg","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c26af19b5986938e0735df5eb8d7aed94d76eb5e"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"021a2407ca91a99212964e35e5cf8d52245851e5"},"cell_type":"code","source":"def agg_categorical(df, parent_var, df_name):\n    \"\"\"\n    Aggregates the categorical features in a child dataframe\n    for each observation of the parent variable.\n    \n    Parameters\n    --------\n    df : dataframe \n        The dataframe to calculate the value counts for.\n        \n    parent_var : string\n        The variable by which to group and aggregate the dataframe. For each unique\n        value of this variable, the final dataframe will have one row\n        \n    df_name : string\n        Variable added to the front of column names to keep track of columns\n\n    \n    Return\n    --------\n    categorical : dataframe\n        A dataframe with aggregated statistics for each observation of the parent_var\n        The columns are also renamed and columns with duplicate values are removed.\n        \n    \"\"\"\n    \n    # Select the categorical columns\n    categorical = pd.get_dummies(df.select_dtypes('category'))\n\n    # Make sure to put the identifying id on the column\n    categorical[parent_var] = df[parent_var]\n\n    # Groupby the group var and calculate the sum and mean\n    categorical = categorical.groupby(parent_var).agg(['sum', 'count', 'mean'])\n    \n    column_names = []\n    \n    # Iterate through the columns in level 0\n    for var in categorical.columns.levels[0]:\n        # Iterate through the stats in level 1\n        for stat in ['sum', 'count', 'mean']:\n            # Make a new column name\n            column_names.append('%s_%s_%s' % (df_name, var, stat))\n    \n    categorical.columns = column_names\n    \n    # Remove duplicate columns by values\n    _, idx = np.unique(categorical, axis = 1, return_index = True)\n    categorical = categorical.iloc[:, idx]\n    \n    return categorical","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5cc08645c417462165098a8d4512a12de15b24a8"},"cell_type":"code","source":"import sys\n\ndef return_size(df):\n    \"\"\"Return size of dataframe in gigabytes\"\"\"\n    return round(sys.getsizeof(df) / 1e9, 2)\n\ndef convert_types(df, print_info = False):\n    \n    original_memory = df.memory_usage().sum()\n    \n    # Iterate through each column\n    for c in df:\n        \n        # Convert ids and booleans to integers\n            \n        # Convert objects to category\n        if (df[c].dtype == 'object') and (df[c].nunique() < df.shape[0]):\n            df[c] = df[c].astype('category')\n        \n        # Booleans mapped to integers\n        elif list(df[c].unique()) == [1, 0]:\n            df[c] = df[c].astype(bool)\n        \n        # Float64 to float32\n        elif df[c].dtype == float:\n            df[c] = df[c].astype(np.float32)\n            \n        # Int64 to int32\n        elif df[c].dtype == int:\n            df[c] = df[c].astype(np.int32)\n        \n    new_memory = df.memory_usage().sum()\n    \n    if print_info:\n        print(f'Original Memory Usage: {round(original_memory / 1e9, 2)} gb.')\n        print(f'New Memory Usage: {round(new_memory / 1e9, 2)} gb.')\n        \n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"60f8abc921e28d4143967f847e79dc45c5ec8d0d"},"cell_type":"code","source":"train = convert_types(train, print_info=True)\ntest = convert_types(test, print_info=True)\nhist=convert_types(hist, print_info=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0293228f689b5d0a6253df8ba4ea9265c1ac7075"},"cell_type":"code","source":"# Calculate value counts for each categorical column\nhist_count = agg_categorical(hist[['authorized_flag','card_id','category_1','category_3']], 'card_id', 'hist')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0eeb2b2b3506fdaf256f3669c3c99f0f106784fd"},"cell_type":"code","source":"# Calculate aggregate statistics for each numeric column\nhist_agg = agg_numeric(hist[['card_id','installments','purchase_amount','month_lag']], 'card_id', 'hist')\nprint('Previous aggregation shape: ', hist_agg.shape)\nhist_agg.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"318ffa7452328a9c242d4a40b7b068ed7aea6578"},"cell_type":"code","source":"hist_agg=hist_agg.drop(['hist_month_lag_sum'],axis=1)\nhist_agg.head()\ntrain = pd.merge(train, hist_agg, on=\"card_id\", how=\"left\")\ntest = pd.merge(test, hist_agg, on=\"card_id\", how=\"left\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a7f47640905ab54b224c8a1f0b7e3293212ed9be"},"cell_type":"code","source":"# Memory management\nimport gc\ngc.enable()\ndel hist_agg, gdf\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"858269ea5e45cc489a09b46fa9c239346a4f51fc"},"cell_type":"code","source":"hist=hist[['authorized_flag','card_id','category_1','category_3']]\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"db191a2a98b48a8663e99a20b07e193b4ebd27d0"},"cell_type":"code","source":"hist_count.head()","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}