{"cells":[{"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.\n\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","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2ef83c4d895a12fbc6319cd3394774cb5288467f"},"cell_type":"markdown","source":"### updates / notes\n* created a words datafile - has been added to the dd9_files dataset. I needed to create more than one file because I encountered problems when trying to create a single file. Please be careful when using this dataset as it would be regarded as an external data source. \n* the all.csv file will be added to the dd9_files dataset (when I have time and remember to do it)\n* starting to create crosstabs \n* please note that I only do quick checks - use results with caution and if you spot anything odd let me know"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"f752dfb026ed4a192cff1c7c78d42c941741cbf8"},"cell_type":"code","source":"print('input:\\n', os.listdir(\"../input\"))\nprint('\\nembeddings:\\n', os.listdir(\"../input/embeddings\"))","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"3b07dae9e387bbcf50bbece1a21b1e00c6b6f0f3"},"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":"2c687ba171013c0dc1c32a03873ad317f28fe281"},"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', 'sincere', 'insincere']\n    elif int_code == 2:\n        return ['overall', 'train_or_test', 'train_or_test', 'target_s1d', 'target_s1d']\n    elif int_code == 3:\n        return ['yes', 'test', 'train', 'sincere', 'insincere']\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\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"6af16de2363aea1fd4545fb18355790fbc6071de"},"cell_type":"code","source":"def percent_summary_1 (row, str_input_column):\n    # created by darryldias 27may2018   \n    if row[str_input_column] == 0 :   \n        return 'no'\n    if row[str_input_column] > 0 :\n        return 'yes'\n    return 'Unknown'\n\ndef month_description (row, str_input_column):\n    # created by darryldias 1june2018   \n    if row[str_input_column] == 1 :   \n        return 'Jan'\n    if row[str_input_column] == 2 :   \n        return 'Feb'\n    if row[str_input_column] == 3 :   \n        return 'Mar'\n    if row[str_input_column] == 4 :   \n        return 'Apr'\n    if row[str_input_column] == 5 :   \n        return 'May'\n    if row[str_input_column] == 6 :   \n        return 'Jun'\n    if row[str_input_column] == 7 :   \n        return 'Jul'\n    if row[str_input_column] == 8 :   \n        return 'Aug'\n    if row[str_input_column] == 9 :   \n        return 'Sep'\n    if row[str_input_column] == 10 :   \n        return 'Oct'\n    if row[str_input_column] == 11 :   \n        return 'Nov'\n    if row[str_input_column] == 12 :   \n        return 'Dec'\n    return 'Unknown'\n\ndef year_month_code1 (row, str_input_column_year, str_input_column_month):\n    # created by darryldias 1june2018   \n    if row[str_input_column_month] <= 9 :   \n        return int(str(row[str_input_column_year]) + '0' + str(row[str_input_column_month]))\n    if row[str_input_column_month] <= 12 :   \n        return int(str(row[str_input_column_year]) + str(row[str_input_column_month]))\n    return 0\n\ndef year_month_code2 (row, str_input_column):\n    str_date = str(row[str_input_column])\n    return int(str_date[:6])\n\ndef year_month_code3 (row, str_input_column):\n    int_date = row[str_input_column]\n    if int_date > 0:\n        str_date = str(int_date)\n        return int(str_date[:6])\n    else:\n        return None\n    \ndef n_0_1_summary (row, str_input_column):\n    # created by darryldias 11jun2018   \n    if row[str_input_column] == 0 :   \n        return '0'\n    if row[str_input_column] == 1 :\n        return '1'\n    return 'Unknown'\n\ndef n_0_1_summary2 (row, str_input_column):\n    # created by darryldias 28jun2018   \n    if row[str_input_column] <= 0.1 :   \n        return '(0 to 0.1]'\n    if row[str_input_column] <= 0.2 :   \n        return '(0.1 to 0.2]'\n    if row[str_input_column] <= 0.3 :   \n        return '(0.2 to 0.3]'\n    if row[str_input_column] <= 0.4 :   \n        return '(0.3 to 0.4]'\n    if row[str_input_column] <= 0.5 :   \n        return '(0.4 to 0.5]'\n    if row[str_input_column] <= 0.6 :   \n        return '(0.5 to 0.6]'\n    if row[str_input_column] <= 0.7 :   \n        return '(0.6 to 0.7]'\n    if row[str_input_column] <= 0.8 :   \n        return '(0.7 to 0.8]'\n    if row[str_input_column] <= 0.9 :   \n        return '(0.8 to 0.9]'\n    if row[str_input_column] <= 1.0 :   \n        return '(0.9 to 1.0]'\n    return 'UNKNOWN'\n\ndef n_0_10_summary (row, str_input_column):\n    # created by darryldias 29jun2018   \n    for i in range(11):\n        if row[str_input_column] == i :   \n            return str(i)\n    return 'UNKNOWN'\n\ndef expm1_s1d (row):  \n    return math.expm1( row['abc'] )\n\ndef log1p_s1d (row):  \n    flt_revenue = row['transactionRevenue']\n    if np.isnan(flt_revenue):\n        flt_revenue = 0.0\n    return math.log1p( flt_revenue )\n\ndef rev_sum_div_s1d (row):  \n    str_train_or_test = row['train or test']\n    flt_rev = row['totals_transactionRevenue_sum_div']\n    if str_train_or_test == 'train':\n        if flt_rev > 0:\n            return 'rev'\n        else:\n            return 'no rev'\n    else:\n        return 'na test'\n\ndef rev_sum_div_s2d (row):  \n    str_train_or_test = row['train or test']\n    flt_rev = row['totals_transactionRevenue_sum_div']\n    if str_train_or_test == 'train':\n        if flt_rev > 0:\n            if flt_rev <= 25:\n                return '(000 - 025]'\n            elif flt_rev <= 50:\n                return '(025 - 050]'\n            elif flt_rev <= 100:\n                return '(050 - 100]'\n            else:\n                return '(100 +'\n        else:\n            return 'no rev'\n    else:\n        return 'na test'\n    \ndef sessions_s1d (row):\n    int_sessions = row['fullVisitorId_count'] \n    if int_sessions == 1 :   \n        return '1'\n    elif int_sessions == 2 :   \n        return '2'\n    elif int_sessions == 3 :   \n        return '3'\n    elif int_sessions == 4 :   \n        return '4'\n    else:\n        return '5 or more'\n\ndef date_diff_days_s1d (row):\n    int_days = row['date_diff_days'] \n    if int_days == 0 :   \n        return '00'\n    elif int_days >= 1 and int_days <= 10:   \n        return '01 - 10'\n    else:\n        return '11 or more'\n\ndef totals_hits_avg_s1d (row):\n    int_hits = row['totals_hits_avg'] \n    if int_hits <= 1 : # min is actually 1   \n        return '(00 - 01]'\n    elif int_hits <= 3:   \n        return '(01 - 03]'\n    elif int_hits <= 10:   \n        return '(03 - 10]'\n    else:\n        return '(10 +'\n\ndef totals_pageviews_avg_s1d (row):\n    int_pvs = row['totals_pageviews_avg'] \n    if int_pvs <= 1 : # min is actually 1   \n        return '(00 - 01]'\n    elif int_pvs <= 3:   \n        return '(01 - 03]'\n    elif int_pvs <= 10:   \n        return '(03 - 10]'\n    elif int_pvs > 10:   \n        return '(10 +'\n    else:\n        return 'unknown'\n\ndef rev_count_s1d (row):  \n    str_train_or_test = row['train or test']\n    flt_rev = row['revenue_sum_div']\n    flt_count = row['revenue_count']\n    if str_train_or_test == 'train':\n        if flt_rev > 0:\n            if flt_count == 1:\n                return '1'\n            else:\n                return '2+'\n        else:\n            return '0'\n    else:\n        return 'na test'\n\ndef date_min_s2d (row):\n    int_yyyymm = row['date_min_s1d'] \n    if int_yyyymm >= 201608 and int_yyyymm <= 201610:   \n        return '201608 - 201610'\n    elif int_yyyymm >= 201611 and int_yyyymm <= 201701:   \n        return '201611 - 201701'\n    elif int_yyyymm >= 201702 and int_yyyymm <= 201804:   \n        return '201702 - 201804'\n    else:\n        return 'other'\n\ndef sp1_s1d (row):\n    str_date_min_s2d = row['date_min_s2d'] \n    str_rev_sum_div_s1d = row['rev_sum_div_s1d'] \n    if str_date_min_s2d == '201608 - 201610': \n        if str_rev_sum_div_s1d == 'rev':\n            return 'sp1 rev'\n        else:\n            return 'sp1 no rev'\n    else:\n        return 'other/na'\n\ndef sp1_s2d (row):\n    str_sp1_s1d = row['sp1_s1d'] \n    int_yyyymm_rev_min = row['revenue_date_min_s1d']\n    if str_sp1_s1d == 'sp1 rev': \n        if int_yyyymm_rev_min >= 201702:\n            return 'rev min 201702 or later'\n        elif int_yyyymm_rev_min == 201701:\n            return 'rev min 201701'\n        elif int_yyyymm_rev_min == 201612:\n            return 'rev min 201612'\n        elif int_yyyymm_rev_min == 201611:\n            return 'rev min 201611'\n        elif int_yyyymm_rev_min == 201610:\n            return 'rev min 201610'\n        elif int_yyyymm_rev_min == 201609:\n            return 'rev min 201609'\n        elif int_yyyymm_rev_min == 201608:\n            return 'rev min 201608'\n        else:\n            return 'rev unknown'\n    elif str_sp1_s1d == 'sp1 no rev':\n        return 'sp1 no rev'\n    else:\n        return 'other/na'\n\n# new\ndef target_s1d (row):\n    int_target = row['target'] \n    if int_target == 0 :   \n        return 'sincere'\n    elif int_target == 1 :   \n        return 'insincere'\n    else:\n        return 'na test'\n\ndef q_length_s1d (row):\n    int = row['q_length'] \n    if int <= 50 : \n        return '001 to 050'\n    elif int <= 100:   \n        return '051 to 100'\n    elif int >= 101:   \n        return '101 or more'\n    else:\n        return 'unknown'\n\ndef word_count (row):\n    int = len( row['question_text'].split() )\n    return int\n\ndef word_count_s1d (row):\n    int = row['word_count']\n    if int <= 10:\n        return '01 to 10'\n    elif int <= 20:\n        return '11 to 20'\n    elif int >= 21:\n        return '21 or more'\n    else:\n        return 'unknown'\n\ndef qmark_count (row):\n    int = row['question_text'].count('?') \n    return int\n\ndef qmark_count_s1d (row):\n    int = row['qmark_count']\n    if int == 0:\n        return '0'\n    elif int == 1:\n        return '1'\n    elif int >= 2:\n        return '2+'\n    else:\n        return 'unknown'\n\ndef period_count (row):\n    int = row['question_text'].count('.') \n    return int\n\ndef period_count_s1d (row):\n    int = row['period_count']\n    if int == 0:\n        return '0'\n    elif int >= 1:\n        return '1+'\n    else:\n        return 'unknown'\n\ndef first_word (row):\n    words = row['question_text'].split()\n    str = words[0].lower()\n    return str\n\ndef first_word_s1d (row):\n    str_word = row['first_word']\n    if str_word in ['what', 'where', 'why', 'how', 'when', 'is', 'will', 'should', 'are', 'can', 'do', 'did', 'who', 'if', \\\n                    'would', 'which', 'does', 'has', 'was', 'could', 'have']:\n        return str_word\n    else:\n        return 'other'\n\ndef cap_word_count (row):\n    words = row['question_text'].split()\n    int_count = 0\n    for word in words:\n        char = word[0] \n        if char == char.upper() and char.isalpha():\n            int_count += 1\n    return int_count\n\ndef cap_word_count_s1d (row):\n    int = row['cap_word_count']\n    if int <= 3:\n        return str(int)\n    elif int >= 4:\n        return '4+'\n    else:\n        return 'unknown'\n\ndef contains_word (row, word):\n    # to do setup clean word function \n    words = row['question_text'].lower().split()\n    if word in words:\n        return 'yes'\n    else:\n        return 'no'\n\ndef create_contains_word_var(str_word):\n    temp_var = 'contains_word_' + str_word\n    df_all[temp_var] = df_all.apply(contains_word, axis=1, word=str_word)\n\ndef equals_count (row):\n    int = row['question_text'].count('=') \n    return int\n\ndef equals_count_s1d (row):\n    int = row['equals_count']\n    if int == 0:\n        return '0'\n    elif int >= 1:\n        return '1+'\n    else:\n        return 'unknown'\n\ndef dollar_count (row):\n    int = row['question_text'].count('$') \n    return int\n\ndef dollar_count_s1d (row):\n    int = row['dollar_count']\n    if int == 0:\n        return '0'\n    elif int >= 1:\n        return '1+'\n    else:\n        return 'unknown'\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"80fea195a8e6ebfa8b40f26604b786f7c8bfa65e"},"cell_type":"code","source":"df_train = pd.read_csv('../input/train.csv', nrows=int_read_csv_rows)\ndf_train['k_qid'] = df_train.index + 10000001\ndf_test = pd.read_csv('../input/test.csv', nrows=int_read_csv_rows)\ndf_test['k_qid'] = df_test.index + 20000001\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'\n\ndf_all['target_s1d'] = df_all.apply(target_s1d, axis=1)\ndf_all['q_length']  = df_all['question_text'].str.len()\ndf_all['q_length_s1d'] = df_all.apply(q_length_s1d, axis=1)\ndf_all['word_count'] = df_all.apply(word_count, axis=1)\ndf_all['word_count_s1d'] = df_all.apply(word_count_s1d, axis=1)\ndf_all['qmark_count'] = df_all.apply(qmark_count, axis=1)\ndf_all['qmark_count_s1d'] = df_all.apply(qmark_count_s1d, axis=1)\ndf_all['period_count'] = df_all.apply(period_count, axis=1)\ndf_all['period_count_s1d'] = df_all.apply(period_count_s1d, axis=1)\ndf_all['first_word'] = df_all.apply(first_word, axis=1)\ndf_all['first_word_s1d'] = df_all.apply(first_word_s1d, axis=1)\ndf_all['cap_word_count'] = df_all.apply(cap_word_count, axis=1)\ndf_all['cap_word_count_s1d'] = df_all.apply(cap_word_count_s1d, axis=1)\ndf_all['equals_count'] = df_all.apply(equals_count, axis=1)\ndf_all['equals_count_s1d'] = df_all.apply(equals_count_s1d, axis=1)\ndf_all['dollar_count'] = df_all.apply(dollar_count, axis=1)\ndf_all['dollar_count_s1d'] = df_all.apply(dollar_count_s1d, axis=1)\n\ndf_all['version'] = int_df_all_version\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"8337b5b974995add651c4d3bb54cbc1d9ee088ab"},"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":"e4178104660bac769ad49153b83f504c569df0e9"},"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":"5d5bc0211347db8bfbf816def705b19d71f785c9"},"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":"f61fa55c766c32ee41c31d9ec925703a04bdcdf3"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'q_length_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"e972c6303d7b7f8790cd4e9fee0703660392bb35"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'word_count_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"8ca6afba826a2dd52d9c499cfe1f2b5aa3e02e69"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'qmark_count_s1d', int_current_crosstab) ","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"063b898fc82dfc4f37395d0844bf6e495c96c0c2"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'period_count_s1d', int_current_crosstab) ","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"e7d4fec652b869aabc1248f8b3e38b90ebccd24c"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'first_word_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"16640f28e1f5557d41186bf5b3a015146099953f"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'cap_word_count_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"13e68b4fe22fa4eadc1dca9708dfde251bc85488"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'equals_count_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"bebd2b74567c1a6e6a9da28c2f04ba7a2c0edc5c"},"cell_type":"code","source":"create_crosstab_type1(df_all, 'dollar_count_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"c673a86fff96cacb021ce0f8008d797ae5ada6be"},"cell_type":"code","source":"df_all.sample(5)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"bb859fc948b7ca1999d1b6e60dd0b2b249508461"},"cell_type":"code","source":"if bln_create_df_all_csv_file:\n    print('questions_s1.csv file created (version ' + str(int_df_all_version) + ')')\n    df_all.to_csv('questions_s1.csv', index=False)\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"73cd84ff91817af5d2bfca885d2c31ab7b86227e"},"cell_type":"markdown","source":"### I'll be changing the following variable creation slightly in a future update"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"3f74e1cca89af500f6cd6ce8c46df47b4bf42c23"},"cell_type":"code","source":"create_contains_word_var('i')\ncreate_crosstab_type1(df_all, 'contains_word_i', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"5bb332ce502ba078191bca132391f4f30a5d6d1c"},"cell_type":"code","source":"create_contains_word_var('best')\ncreate_crosstab_type1(df_all, 'contains_word_best', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"cbc060b91aea6801cc15e8f5861a361d82db8eb6"},"cell_type":"code","source":"create_contains_word_var('men')\ncreate_crosstab_type1(df_all, 'contains_word_men', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"6fb9937275d4a97f02c3b37a2cc58f9abf198863"},"cell_type":"code","source":"create_contains_word_var('women')\ncreate_crosstab_type1(df_all, 'contains_word_women', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"7400db07c862dbebf20026d3644d361ec1bb9ad7"},"cell_type":"code","source":"create_contains_word_var('money')\ncreate_crosstab_type1(df_all, 'contains_word_money', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"97d9e3afab4957b649ee84f2e8bd5cfcef0f9970"},"cell_type":"code","source":"create_contains_word_var('you')\ncreate_crosstab_type1(df_all, 'contains_word_you', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"7f252fb8132e43b187a0da05e3e63c658f413f7f"},"cell_type":"code","source":"create_contains_word_var('love')\ncreate_crosstab_type1(df_all, 'contains_word_love', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b1947e3fb909ad0259fa780e4bdd600aaebd1e9a"},"cell_type":"markdown","source":"### special section - equals count"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"eb67d543ee1f7bb41aa95007a50c211861157952"},"cell_type":"code","source":"df_temp = df_all[ df_all['equals_count'] >= 1 ]\ndf_temp[['question_text', 'equals_count', 'equals_count_s1d']].sample(20)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"15e1b27e5c25fc766976c8f4d8f3d9df81064db1"},"cell_type":"markdown","source":"### experimenting / checking section - please ignore the following"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"f8a5f1e47eda8a4188d6f5ae619bc6d6c0fd6334"},"cell_type":"code","source":"if bln_create_words_csv_file:\n    print('creating words file...')\n    str_filename = 'words.csv'\n    csvfile1 = open(str_filename, 'w')\n    writer1 = csv.writer(csvfile1)\n    writer1.writerow( ['train_or_test', 'k_qid', 'target', 'position', 'word_orig', 'word_alt'] )\n\n    #df_temp = df_all[ df_all['k_qid'] > 10700000 ]\n    df_temp = df_all[ (df_all['train_or_test'] == 'train') & (df_all['k_qid'] > 10700000)]\n    df_temp.reset_index(drop=True)\n\n    int_rows = df_temp.shape[0]\n    for i in range(int_rows):\n        str_train_or_test = df_temp.iloc[i]['train_or_test']\n        int_k_qid = df_temp.iloc[i]['k_qid']\n        if str_train_or_test == 'train':\n            int_target = int(df_temp.iloc[i]['target'])\n        else:\n            int_target = None\n        str_question_text = df_temp.iloc[i]['question_text']\n        words = str_question_text.split()\n        int_position = 0\n        for word in words:\n            int_position += 1\n            word_alt = word\n            word_alt = word_alt.lower() \n            word_alt = word_alt.replace('?','')\n            word_alt = word_alt.replace(',','')\n            word_alt = word_alt.replace('.','')\n            word_alt = word_alt.replace('/','')\n            word_alt = word_alt.replace('(','')\n            word_alt = word_alt.replace(')','')\n            word_alt = word_alt.replace('\\'','')\n            word_alt = word_alt.replace('\"','')\n            word_alt = word_alt.replace('-','')\n            word_alt = word_alt.replace('\\\\','')\n            writer1.writerow( [str_train_or_test, int_k_qid, int_target, int_position, word, word_alt] )\n            if int_k_qid == 10000001 or int_k_qid == 20000001:\n                print(str_train_or_test, int_k_qid, int_target, int_position, word, word_alt)\n\n    csvfile1.close()\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"d145bdb46c1936d4ebcd4775526c23b28d91a895"},"cell_type":"code","source":"#def temp_count (row):\n#    int = row['question_text'].count('\\\\') \n#    return int\n#df_all['temp_count'] = df_all.apply(temp_count, axis=1)\n#df_train.info()\n#df_train.sample(12)\n#df_test.info()\n#df_test.sample(12)\n#df_all.info()\n#df_all.sample(12)\n\n#df_all_p01['rev_count_s1d'] = df_all_p01.apply(rev_count_s1d, axis=1)\n#df_all_p01['sessions_s1d'] = df_all_p01.apply(sessions_s1d, axis=1)\n#df_all_p01['date_min_s1d'] = df_all_p01.apply(year_month_code2, axis=1, str_input_column='date_min')\n#df_all_p01['date_min'] = pd.to_datetime(df_all_p01['date_min'].astype(str), format='%Y%m%d')\n#df_all_p01['date_diff_days'] = (df_all_p01['date_max'] - df_all_p01['date_min']).dt.days\n#df_all_p01['date_diff_days_s1d'] = df_all_p01.apply(date_diff_days_s1d, axis=1)\n#df_all_p01['totals_hits_avg_s1d'] = df_all_p01.apply(totals_hits_avg_s1d, axis=1)\n#df_all_p01['totals_pageviews_avg_s1d'] = df_all_p01.apply(totals_pageviews_avg_s1d, axis=1)\n#df_all_p01['date_min_s2d'] = df_all_p01.apply(date_min_s2d, axis=1)\n#df_all_p01['revenue_date_min_s1d'] = df_all_p01.apply(year_month_code3, axis=1, str_input_column='revenue_date_min')\n#df_all_p01['sp1_s1d'] = df_all_p01.apply(sp1_s1d, axis=1)\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"89a756ee557cda464c91b3f405b78d7978c72516"},"cell_type":"code","source":"#df_train_p01 = pd.merge(df_train_p01, df_train_id, how='left', on=['fullVisitorId'])\n# totals.timeOnSite\n#df_all.to_csv('all.csv', index=False)\n#for i in range(850000,850999):\n#    x = json.loads(df_train.iloc[i]['totals'])\n#    print(x['newVisits'])\n#df_query.sample(10)  \n#df_test_temp.info()\n#df_test_temp['sessionId'].nunique()\n#df_all.info()\n#df_temp = df_all[ df_all['transactionRevenue']>0 ]\n#df_temp.sample(10)\n#create_crosstab_type1(df_all, 'overall', int_current_crosstab)\n#df_temp = get_sample_train_data(\"device.browser\", \"20170720\")    \n#df_temp.sample(80)\n#df_all_p01['totals_transactionRevenue_sum_div'].describe()\n#df_temp = df_all[ df_all['dollar_count'] >= 1 ]\n#df_temp[['question_text', 'dollar_count', 'dollar_count_s1d']].sample(20)\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":"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}