{"cells":[{"metadata":{"_uuid":"062d00a6ad7d024b3d91c8b94d44f94b1536db30"},"cell_type":"markdown","source":"### updates / notes\n* for purchase amount I have done an adjustment so that I am not dealing with negatives - note that I might change how I've done this later\n* ht = historical transactions; nt = new merchant transactions\n* setting up crosstabs at the moment - I'll add more analysis later - how much more depends on upvotes :)\n* I only do quick checks so please use results with caution"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true},"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 in \nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n# Input data files are available in the \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory\nimport os\n# Any results you write to the current directory are saved as output.\nfrom datetime import datetime\nfrom IPython.core.display import display, HTML\nimport math\nimport csv\nbln_create_df_all_csv_file = True\nbln_create_words_csv_file = False\nint_df_all_version = 6\nbln_ready_to_commit = True\nbln_create_estimate_files = False\nbln_upload_input_estimates = False\nbln_recode_variables = True\npd.set_option(\"display.max_rows\", 101)\npd.set_option(\"display.max_columns\", 25)\n\ndf_time_check = pd.DataFrame(columns=['Stage','Start','End', 'Seconds', 'Minutes'])\nint_time_check = 0\ndat_start = datetime.now()\ndat_program_start = dat_start\n\nif not bln_ready_to_commit:\n    int_read_csv_rows = 100000\nelse:\n    int_read_csv_rows= None\n    \n# generate crosstabs  {0 = nothing; 1 = screen}\nint_important_crosstab = 1\nint_past_crosstab = 0\nint_current_crosstab = 1\n\nprint('input:\\n', os.listdir(\"../input\"))\n","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"def get_translations_analysis_description(df_input, str_language, str_group, int_code):\n    # created by darryldias 25may2018\n    df_temp = df_input[(df_input['language']==str_language) & (df_input['group']==str_group) & (df_input['code']==int_code)] \\\n                    ['description']\n    return df_temp.iloc[0]\n\n#translations_analysis = pd.read_csv('../input/ulabox-translations-analysis/translations_analysis.csv')\nstrg_count_column = 'count'   #get_translations_analysis_description(translations_analysis, str_language, 'special', 2)\n\ndef start_time_check():\n    # created by darryldias 21may2018 - updated 8june2018\n    global dat_start \n    dat_start = datetime.now()\n    \ndef end_time_check(dat_start, str_stage):\n    # created by darryldias 21may2018 - updated 8june2018\n    global int_time_check\n    global df_time_check\n    int_time_check += 1\n    dat_end = datetime.now()\n    diff_seconds = (dat_end-dat_start).total_seconds()\n    diff_minutes = diff_seconds / 60.0\n    df_time_check.loc[int_time_check] = [str_stage, dat_start, dat_end, diff_seconds, diff_minutes]\n\ndef create_topline(df_input, str_item_column, str_count_column):\n    # created by darryldias 21may2018; updated by darryldias 29may2018\n    str_percent_column = 'percent'   #get_translations_analysis_description(translations_analysis, str_language, 'special', 3)\n    df_temp = df_input.groupby(str_item_column).size().reset_index(name=str_count_column)\n    df_output = pd.DataFrame(columns=[str_item_column, str_count_column, str_percent_column])\n    int_rows = df_temp.shape[0]\n    int_columns = df_temp.shape[1]\n    int_total = df_temp[str_count_column].sum()\n    flt_total = float(int_total)\n    for i in range(int_rows):\n        str_item = df_temp.iloc[i][0]\n        int_count = df_temp.iloc[i][1]\n        flt_percent = round(int_count / flt_total * 100, 1)\n        df_output.loc[i] = [str_item, int_count, flt_percent]\n    \n    df_output.loc[int_rows] = ['total', int_total, 100.0]\n    return df_output        \n\ndef get_dataframe_info(df_input, bln_output_csv, str_filename):\n    # created by darryldias 24may2018 - updated 7june2018\n    int_rows = df_input.shape[0]\n    int_cols = df_input.shape[1]\n    flt_rows = float(int_rows)\n    \n    df_output = pd.DataFrame(columns=[\"Column\", \"Type\", \"Not Null\", 'Null', '% Not Null', '% Null'])\n    df_output.loc[0] = ['Table Row Count', '', int_rows, '', '', '']\n    df_output.loc[1] = ['Table Column Count', '', int_cols, '', '', '']\n    int_table_row = 1\n    for i in range(int_cols):\n        str_column_name = df_input.columns.values[i]\n        str_column_type = df_input.dtypes.values[i]\n        int_not_null = df_input[str_column_name].count()\n        int_null = sum( pd.isnull(df_input[str_column_name]) )\n        flt_percent_not_null = round(int_not_null / flt_rows * 100, 1)\n        flt_percent_null = round(100 - flt_percent_not_null, 1)\n        int_table_row += 1\n        df_output.loc[int_table_row] = [str_column_name, str_column_type, int_not_null, int_null, flt_percent_not_null, flt_percent_null]\n\n    if bln_output_csv:\n        df_output.to_csv(str_filename)\n        print ('Dataframe information output created in file: ' + str_filename)\n        return None\n    return df_output\n\ndef check_numeric_var(str_question, int_groups):\n    # created by darryldias 3jul2018  \n    #print(df_output.iloc[3][2])\n    flt_min = application_all[str_question].min()\n    flt_max = application_all[str_question].max()\n    flt_range = flt_max - flt_min \n    flt_interval = flt_range / int_groups \n    df_output = pd.DataFrame(columns=['interval', 'value', 'count', 'percent', 'code1', 'code2'])\n\n    int_total = application_all[ (application_all[str_question] <= flt_max) ][str_question].count()\n    for i in range(0, int_groups + 1):\n        flt_curr_interval = i * flt_interval\n        flt_value = flt_min + flt_curr_interval\n        int_count = application_all[ (application_all[str_question] <= flt_value) ][str_question].count()\n        flt_percent = int_count /  int_total * 100.0\n        str_code_value = \"{0:.6f}\".format(flt_value)\n        str_code1 = \"if row['\" + str_question + \"'] <= \" + str_code_value + \":\"\n        str_code2 = \"return '(x to \" + str_code_value + \"]'\"\n        df_output.loc[i] = [flt_curr_interval, flt_value, int_count, flt_percent, str_code1, str_code2]\n\n    return df_output\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"50c8a81229c41ac30dca9f8343f5d6d0c16b2d14"},"cell_type":"code","source":"def get_column_analysis(int_analysis, int_code):\n    # created by darryldias 24jul2018 \n    if int_code == 1:\n        return ['overall', 'test', 'train', 'negative', 'zero', 'positive', '2A', '2B', '2C', '2D', '2E', '2F', '2G', '3A', '3B']\n    elif int_code == 2:\n        return ['overall', 'train_or_test', 'train_or_test', 'target_s1d', 'target_s1d', 'target_s1d', \\\n                'target_s2d', 'target_s2d', 'target_s2d', 'target_s2d', 'target_s2d', 'target_s2d', 'target_s2d', 'target_s3d', 'target_s3d']\n    elif int_code == 3:\n        return ['yes', 'test', 'train', 'negative', 'zero', 'positive', '2A', '2B', '2C', '2D', '2E', '2F', '2G', '3A', '3B']\n    else:\n        return None\n\ndef create_crosstab_type1(df_input, str_row_question, int_output_destination):\n    # created by darryldias 10jun2018 - updated 27sep2018 \n    # got some useful code from:\n    # https://chrisalbon.com/python/data_wrangling/pandas_missing_data/\n    # https://www.tutorialspoint.com/python/python_lists.htm\n    # https://stackoverflow.com/questions/455612/limiting-floats-to-two-decimal-points\n\n    if int_output_destination == 0:\n        return None\n    \n    str_count_desc = 'count'  #get_translations_analysis_description(translations_analysis, str_language, 'special', 3)\n    str_colpercent_desc = 'col percent'\n    \n    list_str_column_desc = get_column_analysis(1, 1)\n    list_str_column_question = get_column_analysis(1, 2)\n    list_str_column_category = get_column_analysis(1, 3)\n    int_columns = len(list_str_column_desc)\n    list_int_column_base = []\n    list_flt_column_base_percent = []\n    \n    df_group = df_input.groupby(str_row_question).size().reset_index(name='count')\n    int_rows = df_group.shape[0]\n\n    for j in range(int_columns):\n        int_count = df_input[ df_input[str_row_question].notnull() & (df_input[list_str_column_question[j]]==list_str_column_category[j]) ] \\\n                                [list_str_column_question[j]].count()\n        list_int_column_base.append(int_count)\n        if int_count == 0:\n            list_flt_column_base_percent.append('')\n        else:\n            list_flt_column_base_percent.append('100.0')\n        \n    list_output = []\n    list_output.append('row_question')\n    list_output.append('row_category')\n    list_output.append('statistic')\n    for k in range(1, int_columns+1):\n        str_temp = 'c' + str(k)\n        list_output.append(str_temp)\n    df_output = pd.DataFrame(columns=list_output)\n\n    int_row = 1\n    list_output = []\n    list_output.append(str_row_question)\n    list_output.append('')\n    list_output.append('')\n    for k in range(int_columns):\n        list_output.append(list_str_column_desc[k])\n    df_output.loc[int_row] = list_output\n    \n    int_row = 2\n    list_output = []\n    list_output.append(str_row_question)\n    list_output.append('total')\n    list_output.append(str_count_desc)\n    for k in range(int_columns):\n        list_output.append(list_int_column_base[k])\n    df_output.loc[int_row] = list_output\n    \n    int_row = 3\n    list_output = []\n    list_output.append(str_row_question)\n    list_output.append('total')\n    list_output.append(str_colpercent_desc)\n    for k in range(int_columns):\n        list_output.append(list_flt_column_base_percent[k])\n    df_output.loc[int_row] = list_output\n\n    for i in range(int_rows):\n        int_row += 1\n        int_count_row = int_row\n        int_row += 1\n        int_colpercent_row = int_row\n\n        str_row_category = df_group.iloc[i][0]\n\n        list_int_column_count = []\n        list_flt_column_percent = []\n        for j in range(int_columns):\n            int_count = df_input[ (df_input[str_row_question]==str_row_category) & \\\n                                  (df_input[list_str_column_question[j]]==list_str_column_category[j]) ] \\\n                                [list_str_column_question[j]].count()\n            list_int_column_count.append(int_count)\n            flt_base = float(list_int_column_base[j])\n            if flt_base > 0:\n                flt_percent = round(100 * int_count / flt_base,1)\n                str_percent = \"{0:.1f}\".format(flt_percent)\n            else:\n                str_percent = ''\n            list_flt_column_percent.append(str_percent)\n        \n        list_output = []\n        list_output.append(str_row_question)\n        list_output.append(str_row_category)\n        list_output.append(str_count_desc)\n        for k in range(int_columns):\n            list_output.append(list_int_column_count[k])\n        df_output.loc[int_count_row] = list_output\n        \n        list_output = []\n        list_output.append(str_row_question)\n        list_output.append(str_row_category)\n        list_output.append(str_colpercent_desc)\n        for k in range(int_columns):\n            list_output.append(list_flt_column_percent[k])\n        df_output.loc[int_colpercent_row] = list_output\n        \n    return df_output        \n\ndef get_ct_statistic2(df_input, str_row_question, str_col_question, str_col_category, str_statistic):\n    # created by darryldias 17jul2018\n    if str_statistic == 'total':\n        int_temp = df_input[ (df_input[str_col_question] == str_col_category) ][str_row_question].isnull().count() \n    elif str_statistic == 'notnull':\n        int_temp = df_input[ (df_input[str_col_question] == str_col_category) ][str_row_question].count() \n    elif str_statistic == 'null':\n        int_temp = df_input[ (df_input[str_col_question] == str_col_category) ][str_row_question].isnull().sum() \n    elif str_statistic == 'mean':\n        int_temp = df_input[ (df_input[str_col_question] == str_col_category) ][str_row_question].mean() \n    elif str_statistic == 'median':\n        int_temp = df_input[ (df_input[str_col_question] == str_col_category) ][str_row_question].median() \n    elif str_statistic == 'minimum':\n        int_temp = df_input[ (df_input[str_col_question] == str_col_category) ][str_row_question].min() \n    elif str_statistic == 'maximum':\n        int_temp = df_input[ (df_input[str_col_question] == str_col_category) ][str_row_question].max() \n    else:\n        int_temp = None\n    return int_temp\n \ndef create_crosstab_type2(df_input, str_row_question, int_output_destination):\n    # created by darryldias 24jul2018\n    if int_output_destination == 0:\n        return None\n\n    list_str_column_desc = get_column_analysis(1, 1)\n    list_str_column_question = get_column_analysis(1, 2)\n    list_str_column_category = get_column_analysis(1, 3)\n    int_analysis_columns = len(list_str_column_question)\n\n    list_str_statistics = ['total', 'notnull', 'null', 'mean', 'median', 'minimum', 'maximum']\n    list_str_counts = ['total', 'notnull', 'null']\n    int_statistics = len(list_str_statistics)\n\n    df_output = pd.DataFrame(columns=['row_question', 'row_category', 'statistic', 'c1', 'c2', 'c3', 'c4', 'c5'])\n    int_row = 1\n\n    list_values = []\n    list_values.append(str_row_question)\n    list_values.append('')\n    list_values.append('')\n    for j in range(int_analysis_columns):\n        list_values.append(list_str_column_desc[j])\n    df_output.loc[int_row] = list_values\n\n    for i in range(int_statistics):\n        str_statistic = list_str_statistics[i] \n        list_values = []\n        list_values.append(str_row_question)\n        if str_statistic in list_str_counts:\n            list_values.append(str_statistic)\n            list_values.append('count')\n        else:\n            list_values.append('numeric')\n            list_values.append(str_statistic)\n    \n        for j in range(int_analysis_columns):\n            str_col_question = list_str_column_question[j]\n            str_col_category = list_str_column_category[j]\n            num_statistic = get_ct_statistic2(df_input, str_row_question, str_col_question, str_col_category, str_statistic)\n            list_values.append(num_statistic)\n        int_row += 1\n        df_output.loc[int_row] = list_values\n    return df_output\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"af2ab20afd1405494ba91e35603ccd31986385c1"},"cell_type":"code","source":"def year_month_code (row, str_input_column):\n    value = (row[str_input_column].year) * 100 + row[str_input_column].month\n    return value\n\ndef category_s1d (row, str_input_column, str_input_category):\n    str_value = row[str_input_column] \n    if str_value == str_input_category :   \n        return 1\n    elif pd.isnull(str_value):\n        return None\n    else:\n        return 0\n\ndef category_s2d (row, str_input_column):\n    flt_value = row[str_input_column] \n    if flt_value <= 0.2 :   \n        return '{1} [0.0-0.2]'\n    elif flt_value <= 0.4 :   \n        return '{2} (0.2-0.4]'\n    elif flt_value <= 0.6 :   \n        return '{3} (0.4-0.6]'\n    elif flt_value <= 0.8 :   \n        return '{4} (0.6-0.8]'\n    elif flt_value <= 1.0 :   \n        return '{5} (0.8-1.0]'\n    else:\n        return '{6} none'\n\ndef target_s1d (row):\n    flt_target = row['target'] \n    if flt_target < 0 :   \n        return 'negative'\n    elif flt_target == 0 :   \n        return 'zero'\n    elif flt_target > 0 :   \n        return 'positive'\n    else:\n        return 'test'\n\ndef target_s2d (row):\n    flt_target = row['target'] \n    if flt_target < -1.35 :   \n        return '2A'\n    elif flt_target < -0.5 :   \n        return '2B'\n    elif flt_target < 0.0 :   \n        return '2C'\n    elif flt_target == 0.0 :   \n        return '2D'\n    elif flt_target <= 0.5 :   \n        return '2E'\n    elif flt_target <= 1.35 :   \n        return '2F'\n    elif flt_target > 1.35 :   \n        return '2G'\n    else:\n        return 'test'\n\ndef target_s3d (row):\n    flt_target = row['target'] \n    if flt_target < -30 :   \n        return '3A'\n    elif flt_target >= -30 :   \n        return '3B'\n    else:\n        return 'test'\n\ndef ht_count_s1d (row):\n    int_count = row['ht_count'] \n    if int_count <= 25 :   \n        return '001-025'\n    elif int_count <= 50 :   \n        return '026-050'\n    elif int_count <= 100 :   \n        return '051-100'\n    elif int_count >= 101 :   \n        return '101+'\n    else:\n        return 'unknown'\n\ndef nt_count_s1d (row):\n    int_count = row['nt_count'] \n    if int_count <= 2 :   \n        return '1-2'\n    elif int_count <= 4 :   \n        return '3-4'\n    elif int_count <= 8 :   \n        return '5-8'\n    elif int_count >= 9 :   \n        return '9+'\n    else:\n        return '0'\n\ndef ht_pur_amt_adj_sum_s1d (row):\n    flt_value = row['ht_pur_amt_adj_sum'] \n    if flt_value <= 3.0 :   \n        return '[00.0-03.0]'\n    elif flt_value <= 7.0 :   \n        return '(03.0-07.0]'\n    elif flt_value <= 15.0 :   \n        return '(07.0-15.0]'\n    elif flt_value > 15.0 :   \n        return '(15.0+'\n    else:\n        return 'unknown'\n\ndef nt_pur_amt_adj_sum_s1d (row):\n    flt_value = row['nt_pur_amt_adj_sum'] \n    if flt_value <= 0.2 :   \n        return '[0.0-0.2]'\n    elif flt_value <= 0.5 :   \n        return '(0.2-0.5]'\n    elif flt_value <= 1.0 :   \n        return '(0.5-1.0]'\n    elif flt_value <= 2.0 :   \n        return '(1.0-2.0]'\n    elif flt_value <= 4.0 :   \n        return '(2.0-4.0]'\n    elif flt_value > 4.0 :   \n        return '(4.0+'\n    else:\n        return 'none'\n\ndef first_active_month_s2d (row):\n    flt_value = row['first_active_month_s1d'] \n    if flt_value >= 201710 :   \n        return '201710+'\n    elif flt_value >= 201707 :   \n        return '201707-201709'\n    elif flt_value >= 201702 :   \n        return '201702-201706'\n    elif flt_value >= 201608 :   \n        return '201608-201701'\n    elif flt_value >= 201111 :   \n        return '201111-201607'\n    else:\n        return 'unknown'\n\ndef nt_pur_date_max_s2d (row):\n    flt_value = row['nt_pur_date_max_s1d'] \n    if flt_value >= 201804 :   \n        return '201804'\n    elif flt_value >= 201803 :   \n        return '201803'\n    elif flt_value >= 201802 :   \n        return '201802'\n    elif flt_value >= 201801 :   \n        return '201801'\n    elif flt_value >= 201711 :   \n        return '201711-201712'\n    elif flt_value >= 201708 :   \n        return '201708-201710'\n    elif flt_value >= 201703 :   \n        return '201703-201707'\n    else:\n        return 'none'\n\ndef ht_pur_date_max_s2d (row):\n    flt_value = row['ht_pur_date_max_s1d'] \n    if flt_value >= 201802 :   \n        return '201802'\n    elif flt_value >= 201801 :   \n        return '201801'\n    elif flt_value >= 201711 :   \n        return '201711-201712'\n    elif flt_value >= 201709 :   \n        return '201709-201710'\n    elif flt_value >= 201706 :   \n        return '201706-201708'\n    elif flt_value >= 201702 :   \n        return '201702-201705'\n    else:\n        return 'none'\n\ndef has_new_tran (row):\n    int_count = row['nt_count'] \n    if int_count >= 1 :   \n        return 'yes'\n    else:\n        return 'no'\n\ndef nt_category_2_mean_s1d (row):\n    flt_value = row['nt_category_2_mean'] \n    str_value = row['has_new_tran'] \n    if flt_value == 1.0 :   \n        return '{1} 1.0'\n    elif flt_value <= 2.0 :   \n        return '{2} (1.0-2.0]'\n    elif flt_value <= 3.0 :   \n        return '{3} (2.0-3.0]'\n    elif flt_value <= 4.0 :   \n        return '{4} (3.0-4.0]'\n    elif flt_value <= 5.0 :   \n        return '{5} (4.0-5.0]'\n    else:\n        if str_value == 'no':\n            return '{7} none'\n        else:\n            return '{6} unknown'\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"5535fc588f4a756641c3ae650de3930e6a9fda7c"},"cell_type":"code","source":"df_train = pd.read_csv('../input/train.csv', nrows=int_read_csv_rows, parse_dates=[\"first_active_month\"])\ndf_test = pd.read_csv('../input/test.csv', nrows=int_read_csv_rows, parse_dates=[\"first_active_month\"])\ndf_train['train_or_test'] = 'train'\ndf_test['train_or_test'] = 'test'\ndf_all = pd.concat([df_train, df_test], sort=False)\ndf_all['overall'] = 'yes'\ndf_all['target_s1d'] = df_all.apply(target_s1d, axis=1)\ndf_all['target_s2d'] = df_all.apply(target_s2d, axis=1)\ndf_all['target_s3d'] = df_all.apply(target_s3d, axis=1)\ndf_all['first_active_month_s1d'] = df_all.apply(year_month_code, axis=1, str_input_column='first_active_month')\ndf_all['first_active_month_s2d'] = df_all.apply(first_active_month_s2d, axis=1)\n\ndf_hist_trans = pd.read_csv('../input/historical_transactions.csv', nrows=int_read_csv_rows, parse_dates=[\"purchase_date\"])\nflt_min = df_hist_trans['purchase_amount'].min()\ndf_hist_trans['purchase_amount_adj'] = df_hist_trans['purchase_amount'] - flt_min\ngrouped = df_hist_trans.groupby('card_id')\ndf_grouped = grouped['card_id'].count().reset_index(name='ht_count')  \ndf_all = pd.merge(df_all, df_grouped, how='left', on=['card_id'])\ndf_grouped = grouped['purchase_amount_adj'].sum().reset_index(name='ht_pur_amt_adj_sum')  \ndf_all = pd.merge(df_all, df_grouped, how='left', on=['card_id'])\ndf_grouped = grouped['purchase_date'].max().reset_index(name='ht_pur_date_max')  \ndf_all = pd.merge(df_all, df_grouped, how='left', on=['card_id'])\n\n\ndf_new_trans = pd.read_csv('../input/new_merchant_transactions.csv', nrows=int_read_csv_rows, parse_dates=[\"purchase_date\"])\nflt_min = df_new_trans['purchase_amount'].min()\ndf_new_trans['purchase_amount_adj'] = df_new_trans['purchase_amount'] - flt_min\ndf_new_trans['nt_category_1_N'] = df_new_trans.apply(category_s1d, axis=1, str_input_column='category_1', str_input_category='N')\n#df_new_trans['nt_category_3_A'] = df_new_trans.apply(category_s1d, axis=1, str_input_column='category_3', str_input_category='A')\n#df_new_trans['nt_category_3_B'] = df_new_trans.apply(category_s1d, axis=1, str_input_column='category_3', str_input_category='B')\n#df_new_trans['nt_category_3_C'] = df_new_trans.apply(category_s1d, axis=1, str_input_column='category_3', str_input_category='C')\ngrouped = df_new_trans.groupby('card_id')\ndf_grouped = grouped['card_id'].count().reset_index(name='nt_count')  \ndf_all = pd.merge(df_all, df_grouped, how='left', on=['card_id'])\ndf_grouped = grouped['purchase_amount_adj'].sum().reset_index(name='nt_pur_amt_adj_sum')  \ndf_all = pd.merge(df_all, df_grouped, how='left', on=['card_id'])\ndf_grouped = grouped['purchase_date'].max().reset_index(name='nt_pur_date_max')  \ndf_all = pd.merge(df_all, df_grouped, how='left', on=['card_id'])\ndf_grouped = grouped['nt_category_1_N'].mean().reset_index(name='nt_category_1_N_mean')  \ndf_all = pd.merge(df_all, df_grouped, how='left', on=['card_id'])\ndf_grouped = grouped['category_2'].mean().reset_index(name='nt_category_2_mean')  \ndf_all = pd.merge(df_all, df_grouped, how='left', on=['card_id'])\n#df_grouped = grouped['nt_category_3_A'].mean().reset_index(name='nt_category_3_A_mean')  \n#df_all = pd.merge(df_all, df_grouped, how='left', on=['card_id'])\n#df_grouped = grouped['nt_category_3_B'].mean().reset_index(name='nt_category_3_B_mean')  \n#df_all = pd.merge(df_all, df_grouped, how='left', on=['card_id'])\n#df_grouped = grouped['nt_category_3_C'].mean().reset_index(name='nt_category_3_C_mean')  \n#df_all = pd.merge(df_all, df_grouped, how='left', on=['card_id'])\n\n\ndf_all['has_new_tran'] = df_all.apply(has_new_tran, axis=1)\ndf_all['ht_count_s1d'] = df_all.apply(ht_count_s1d, axis=1)\ndf_all['nt_count_s1d'] = df_all.apply(nt_count_s1d, axis=1)\ndf_all['ht_pur_amt_adj_sum_s1d'] = df_all.apply(ht_pur_amt_adj_sum_s1d, axis=1)\ndf_all['nt_pur_amt_adj_sum_s1d'] = df_all.apply(nt_pur_amt_adj_sum_s1d, axis=1)\ndf_all['ht_pur_date_max_s1d'] = df_all.apply(year_month_code, axis=1, str_input_column='ht_pur_date_max')\ndf_all['ht_pur_date_max_s2d'] = df_all.apply(ht_pur_date_max_s2d, axis=1)\ndf_all['nt_pur_date_max_s1d'] = df_all.apply(year_month_code, axis=1, str_input_column='nt_pur_date_max')\ndf_all['nt_pur_date_max_s2d'] = df_all.apply(nt_pur_date_max_s2d, axis=1)\ndf_all['nt_category_1_N_mean_s1d'] = df_all.apply(category_s2d, axis=1, str_input_column='nt_category_1_N_mean')\ndf_all['nt_category_2_mean_s1d'] = df_all.apply(nt_category_2_mean_s1d, axis=1)\n\n\ndf_all.info()\n#df_train['k_qid'] = df_train.index + 10000001\n#df_test['k_qid'] = df_test.index + 20000001","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"e23272c361d96e0fe3e4d4f1b3821ccb2fbfe555"},"cell_type":"code","source":"df_all.sample(10)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"5bfb01314797df900ecf22d7c9e7dd9ddb2dd03e"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'overall', int_important_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"27d88575b9ec48a04bb4020e3ef2c31ce7f651c5"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'train_or_test', int_important_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"7cc946d4cb24cd28f769e22a39ffa98a26cbc850"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'target_s1d', int_important_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"02f7898bcacb5dbd981cbbda3fa6225b246906dc"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'target_s2d', int_important_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"5d47a08fd681c21bcb7d576926e2ffe1f35b1013"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'target_s3d', int_important_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"79736913a678abb9fa19a8249c2a169f7a4b3021"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'feature_1', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"3afd73f39aeb3686b91a134b8eb4d6816f5cb35a"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'feature_2', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"ee68dd76fe6325bcc1e0d1c80322d948721e75bc"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'feature_3', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"886df565934904312f26ed20b6f1f5e2560786d5","_kg_hide-input":true},"cell_type":"code","source":"create_crosstab_type1(df_all, 'first_active_month_s2d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"1b0c32710bc2652ed867836888636416a3db44c6"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'has_new_tran', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"429b153e4c6946d3648122ccdf5e8724e9ceb4a2"},"cell_type":"markdown","source":"### historical transactions"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"24b2fc74ae1ea46977654e7e683230b51b72b4ca"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'ht_count_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"6b92f9e304fcd88672e943a6866939002da02067"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'ht_pur_amt_adj_sum_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"d8b8820dd25b9fd695b4720448e0d1421af38a93"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'ht_pur_date_max_s2d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1ce5405caa8c1a8f7500d894db273ae4e4c22206"},"cell_type":"markdown","source":"### new merchant transactions"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"f17605568839c5f9355ab4dc08354800f15706d5"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'nt_count_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"cd90b971703c29ddfe59c10f96d2b565c93b9506"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'nt_pur_amt_adj_sum_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"0bfa1d8bcbe594efaab2e15e61a3c01283c56f5e"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'nt_pur_date_max_s2d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a13550f65d00d39333499b2908927c3471b87414","_kg_hide-input":true},"cell_type":"code","source":"create_crosstab_type1(df_all, 'nt_category_1_N_mean_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"61426435560786ffe43e6724262f7df4f8465c88"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'nt_category_2_mean_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"4f0f84643129c3a668e2e19df5737c425c37f27b","_kg_hide-output":true},"cell_type":"code","source":"#df_new_trans.info()\n#df_temp = df_new_trans[ df_new_trans['card_id'] == 'C_ID_0c167d84b2' ]\n#df_temp[['card_id', 'merchant_id']].sample(5)\n#df_new_trans[['category_1', 'nt_category_1_N']].sample(50)\n#df_all['nt_category_2_mean'].describe()\n\n#df_new_trans['nt_category_3_C'].describe()\n#grouped = df_new_trans.groupby('card_id')\n#df_grouped = grouped['nt_category_1_N'].mean().reset_index(name='nt_category_1_N_mean')  \n#df_all = pd.merge(df_all, df_grouped, how='left', on=['card_id'])\n#df_all['nt_category_3_A_mean_s1d'] = df_all.apply(category_s2d, axis=1, str_input_column='nt_category_3_A_mean')\n#df_all['nt_category_3_B_mean_s1d'] = df_all.apply(category_s2d, axis=1, str_input_column='nt_category_3_B_mean')\n#create_crosstab_type1(df_all, 'nt_category_3_A_mean_s1d', int_current_crosstab)\n#create_crosstab_type1(df_all, 'nt_category_3_B_mean_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true,"_uuid":"08935950d8e2548601f015da9bb41f1a09adf48f"},"cell_type":"code","source":"#df_temp = df_all[ df_all['nt_category_2_mean_s1d'] == '{7} none' ]\n#df_temp[['nt_category_2_mean', 'nt_category_2_mean_s1d', 'has_new_tran']].sample(20)\n#df_temp.info()\n#df_temp.max()\n#df_all['target'].describe()\n#create_topline(df_new_trans, 'category_2', 'count')\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"eb536012a276b08a76bd08a79607939e6b5a8389"},"cell_type":"code","source":"end_time_check(dat_program_start, 'overall')\ndf_time_check","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}