{"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.\nfrom datetime import datetime\nfrom IPython.core.display import display, HTML\nimport math\n#import json\nbln_ready_to_commit = True\nbln_create_estimate_files = True\nbln_upload_input_estimates = True\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":"4662989fdca7095d0f6a24b7f4bf2b72aa7b6a31"},"cell_type":"markdown","source":"### updates / notes\n* created a couple of crosstabs looking at those visitors in train with first session date between 20160801 and 20161031 - 199668 people (sp1 crosstabs)\n* created revenue count and second (session) date min summary crosstabs\n* created a file which includes min and max revenue dates for each visitor if any revenue is recorded (p02 file for train only) \n* re-running stage 1 as I want to combine train and test table listings for some future bigqueries\n* added a few analysis columns\n* (finally) started to create some crosstabs\n* created visitor level train and test data files with a few columns of interest - will add to dd8_files dataset (p01 files)\n* created train and test visitorid files, added to dd8_files dataset\n* note that my focus in this kernel is experimenting more with BigQuery, but some might find the analysis / files created useful, thus I have decided to make the kernel public.\n* ignore the dd8_all_s4.csv file as I have decided to focus on analysis at the visitor level rather than session level moving forward - this file will be removed sometime in the future.\n* created table schema files for train and test data\n* created table listings for train and test data\n* note that I am only doing quick checks so if you spot anything wrong please let me know"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"09dfd16f7892120f0561f76ff1e60d9e7773b730"},"cell_type":"code","source":"print('input:\\n', os.listdir(\"../input\"))\nprint('\\nga-customer-revenue-prediction:\\n', os.listdir(\"../input/ga-customer-revenue-prediction\"))\nprint('\\ndd8-files:\\n', os.listdir(\"../input/dd8-files\"))","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"5ca7290fd791f64a5672c114860618d0f49dd845"},"cell_type":"code","source":"import csv\nimport bq_helper\nfrom bq_helper import BigQueryHelper\n#bq_train = BigQueryHelper(\"kaggle-public-datasets\", \"ga_train_set\")\n#bq_test = BigQueryHelper(\"kaggle-public-datasets\", \"ga_test_set\")\n# https://www.kaggle.com/sohier/introduction-to-the-bq-helper-package\n#google_analytics = bq_helper.BigQueryHelper(active_project=\"bigquery-public-data\",\n#                                   dataset_name=\"data:google_analytics_sample\")\n#bq_assistant.head(\"ga_sessions_20160801\", num_rows=3)\n\nbln_run_stage1 = False\nbln_run_stage2 = False\nbln_run_stage3 = False\nbln_run_stage4 = False\nbln_run_stage5 = False\nbln_run_stage6 = False\nbln_run_stage7 = False\nbln_run_stage8 = False\nbln_run_stage9 = False\nbln_run_stage10 = False\nbln_run_stage11 = False\n    \n\nflt_query_limit = 0.1\nint_sample_records = 15\n\ncsv_query_info = open('dd8_query_info.csv', 'w')\nquery_writer = csv.writer(csv_query_info)\nquery_writer.writerow( ['query', 'size1_gb', 'size2_gb'] )\n\ndef select_from_dataset_table(str_dataset, str_table):\n    return str_dataset + \".\" + str_table  \n\ndef create_simple_query1(str_dataset, str_columns, str_table, str_where):\n    str_select_from_dataset_table = select_from_dataset_table(str_dataset, str_table)\n    query = \"SELECT \" + str_columns + \\\n        \"\"\" \\nFROM `\"\"\" + str_select_from_dataset_table + \"\"\"` \"\"\" + \\\n        \"\\nWHERE \" + str_where + \" \"\n    return query\n\ndef create_simple_query2(str_dataset, str_select_columns, str_table, str_group_by_columns):\n    str_select_from_dataset_table = select_from_dataset_table(str_dataset, str_table)\n    query = \"SELECT \" + str_select_columns + \\\n            \"\\nFROM `\" + str_select_from_dataset_table + \"` \" + \\\n            \"\\nGROUP BY \" + str_group_by_columns\n    return query\n\ndef create_simple_query3(int_start_date, int_end_date, str_train_or_test, str_select_columns, str_where):\n    str_dataset = get_train_or_test_dataset(str_train_or_test)\n\n    str_return_query = \"WITH SUB1 AS ( \\n\"\n    csv_file_read = open('../input/dd8-files/dd8_table_list_' + str_train_or_test + '.csv', 'r')\n    csv_reader = csv.reader(csv_file_read, delimiter=',')\n\n    int_row = 0\n    for row in csv_reader:\n        if int_row > 0:\n            int_id = row[0]\n            str_table = row[1]\n            int_table_date = int(str_table[-8:])\n            if int_table_date >= int_start_date and int_table_date <= int_end_date:\n                str_select_from_table = str_table\n                query_temp = create_simple_query1(str_dataset, str_select_columns, str_select_from_table, str_where)\n                if int_table_date < int_end_date:\n                    query_temp = query_temp + \"UNION ALL \\n\"\n                str_return_query = str_return_query + query_temp\n            \n        int_row += 1\n    str_return_query = str_return_query + \") \\n\"\n    return str_return_query\n\ndef create_simple_query4(str_select_columns, str_table, str_group_by_columns):\n    query = \"SELECT \" + str_select_columns + \\\n            \"\\nFROM `\" + str_table + \"` \" + \\\n            \"\\nGROUP BY \" + str_group_by_columns\n    return query\n\ndef get_flt_query_size_mb(flt_size):\n    flt_return = flt_size * 1000\n    return flt_return\n\ndef get_str_query_size_mb(flt_size):\n    str_return = str( get_flt_query_size_mb(flt_size) )\n    return str_return\n\ndef get_query_size(flt_size):\n    # assuming there is a minimum - can't check at time of setting this up (22aug2018)\n    if flt_size < 0.01:\n        return 0.01\n    else:\n        return flt_size\n\ndef get_train_or_test_dataset(str_train_or_test):\n    if str_train_or_test == 'train':\n        str_dataset = \"kaggle-public-datasets.ga_train_set\"\n    elif str_train_or_test == 'test':\n        str_dataset = \"kaggle-public-datasets.ga_test_set\"\n    else:\n        str_dataset = \"not_defined\"\n    return str_dataset\n\ndef get_train_or_test_bigqueryhelper(str_train_or_test):\n    if str_train_or_test == 'train':\n        bigqueryhelper = BigQueryHelper(active_project= \"kaggle-public-datasets\", dataset_name = \"ga_train_set\")\n    elif str_train_or_test == 'test':\n        bigqueryhelper = BigQueryHelper(active_project= \"kaggle-public-datasets\", dataset_name = \"ga_test_set\")\n    else:\n        bigqueryhelper = None\n    return bigqueryhelper\n\ndef run_queries_2(bln_create_df, str_train_or_test, int_start_date, int_end_date, str_select_columns, str_group_by_columns):\n    str_dataset = get_train_or_test_dataset(str_train_or_test)\n    bigqueryhelper = get_train_or_test_bigqueryhelper(str_train_or_test)\n    csv_file_read = open('../input/dd8-files/dd8_table_list_' + str_train_or_test + '.csv', 'r')\n\n    csv_reader = csv.reader(csv_file_read, delimiter=',')\n\n    flt_est_query_size_total = 0.0\n    int_query_count = 0\n    print('created dataframes:', bln_create_df)\n    print('sample queries run are shown below:\\n')\n    int_row = 0\n    for row in csv_reader:\n        if int_row > 0:\n            int_id = row[0]\n            str_table = row[1]\n            int_table_date = int(str_table[-8:])\n            if int_table_date >= int_start_date and int_table_date <= int_end_date:\n                str_select_from_table = str_table\n                if str_group_by_columns != \"\":\n                    query = create_simple_query2(str_dataset, str_select_columns, str_select_from_table, str_group_by_columns)\n                else:\n                    query = create_simple_query1(str_dataset, str_select_columns, str_select_from_table)\n                flt_est1_query_size = bigqueryhelper.estimate_query_size(query)\n                flt_est2_query_size = get_query_size(flt_est1_query_size) \n                flt_est_query_size_total += flt_est2_query_size\n                int_query_count += 1\n                if int_table_date == int_start_date or int_table_date == int_end_date or (int_query_count % 20 == 0):\n                    print(query)\n                    print('size1mb:', get_str_query_size_mb(flt_est1_query_size), ' size2mb: ', get_str_query_size_mb(flt_est2_query_size) ) \n                    print()\n            \n                if bln_create_df:\n                    df_temp = bigqueryhelper.query_to_pandas_safe(query, max_gb_scanned=flt_query_limit)\n                    if int_query_count == 1:\n                        df_query = df_temp\n                    else:\n                        df_query = pd.concat([df_query, df_temp])\n            \n                query_writer.writerow( [query, flt_est1_query_size, flt_est2_query_size] )\n        int_row += 1\n    csv_file_read.close()\n    print('the number of lines read in input table list file: ', int_row)\n    print('the number of queries run: ', int_query_count)\n    print('total estimated size of queries run (mb):', get_str_query_size_mb(flt_est_query_size_total) )\n    if bln_create_df:\n        return df_query\n\ndef get_sample_train_data(str_columns, str_date):\n    # darryldias 19sep2018\n    query = \"SELECT fullVisitorId, \" + str_columns + \" \\\n        FROM `kaggle-public-datasets.ga_train_set.ga_sessions_\" + str_date + \"`\" \n    ds_current = BigQueryHelper(active_project= \"kaggle-public-datasets\", dataset_name = \"ga_train_set\")\n    flt_est1_query_size = ds_current.estimate_query_size(query)\n    flt_est2_query_size = get_query_size(flt_est1_query_size)\n    print('size1', get_str_query_size_mb(flt_est1_query_size))\n    print('size2', get_str_query_size_mb(flt_est2_query_size))\n\n    df_return = ds_current.query_to_pandas_safe(query, max_gb_scanned=flt_query_limit)\n    return df_return\n    \n    \ndef add_csv_data(df_input, str_csv_file, bln_show_message):\n    if bln_show_message:\n        print ('processing ' + str_csv_file)\n    df_temp = pd.read_csv(str_csv_file)\n    df_input = pd.concat([df_input, df_temp])\n    return df_input\n\ndef get_csv_row_count(str_file):\n    csv_file_read = open(str_file, 'r')\n    csv_reader = csv.reader(csv_file_read, delimiter=',')\n    int_row = 0\n    for row in csv_reader:\n        int_row += 1\n    csv_file_read.close()\n    return int_row    \n\ndef dict_get_count(dict_input, str_key):\n    int_return = 0\n    for key, value in dict_input.items():\n        if key == str_key:\n            int_return = value\n            break\n    return int_return\n\ndef create_csv_from_dict(dict_input, str_filename, list_input_headings):\n    csvfile_w1 = open(str_filename, 'w')\n    writer1 = csv.writer(csvfile_w1)\n    writer1.writerow( list_input_headings )\n    for key, value in dict_input.items():\n        writer1.writerow( [key, value] )\n    csvfile_w1.close()\n    print('\\nCreated file ', str_filename)\n\n#bln_testing = False\n#int_start_date = 20160801\n#if bln_testing:\n#    int_end_date = 20160802\n#else:\n#    int_end_date = 20170731","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":"fae55787be49fd2c08ceaffff38b56b55d75996a"},"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', 'no rev', 'rev', '(000-025]', '(025-050]', '(050-100]', '(100+']\n    elif int_code == 2:\n        return ['overall', 'train or test', 'train or test', 'rev_sum_div_s1d', 'rev_sum_div_s1d', \\\n                'rev_sum_div_s2d', 'rev_sum_div_s2d', 'rev_sum_div_s2d', 'rev_sum_div_s2d']\n    elif int_code == 3:\n        return ['yes', 'test', 'train', 'no rev', 'rev', '(000 - 025]', '(025 - 050]', '(050 - 100]', '(100 +']\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":{"trusted":true,"_uuid":"38ae6c34ee14afb95f882536529370274268d844","_kg_hide-input":true},"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   ","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"e126b80c67f010abcf744b1e5e3ce384be76c393"},"cell_type":"code","source":"df_train_id = pd.read_csv('../input/dd8-files/dd8_visitorid_train.csv', nrows=int_read_csv_rows, dtype={'fullVisitorId': 'str'})\ndf_test_id = pd.read_csv('../input/dd8-files/dd8_visitorid_test.csv', nrows=int_read_csv_rows, dtype={'fullVisitorId': 'str'})\ndf_train_p01 = pd.read_csv('../input/dd8-files/dd8_visitor_train_p01_s08.csv', nrows=int_read_csv_rows, dtype={'fullVisitorId': 'str'})\ndf_test_p01 = pd.read_csv('../input/dd8-files/dd8_visitor_test_p01_s09.csv', nrows=int_read_csv_rows, dtype={'fullVisitorId': 'str'})\ndf_train_p02 = pd.read_csv('../input/dd8-files/dd8_visitor_train_p02_s10.csv', nrows=int_read_csv_rows, dtype={'fullVisitorId': 'str'})\ndf_train_p01['train or test'] = 'train'\ndf_test_p01['train or test'] = 'test'\ndf_train_p01 = pd.merge(df_train_p01, df_train_id, how='left', on=['fullVisitorId'])\ndf_test_p01 = pd.merge(df_test_p01, df_test_id, how='left', on=['fullVisitorId'])\ndf_train_p01 = pd.merge(df_train_p01, df_train_p02, how='left', on=['fullVisitorId'])\n\ndf_all_p01 = pd.concat([df_train_p01, df_test_p01], sort=False)\ndf_all_p01['overall'] = 'yes'\ndf_all_p01['rev_sum_div_s1d'] = df_all_p01.apply(rev_sum_div_s1d, axis=1)\ndf_all_p01['rev_sum_div_s2d'] = df_all_p01.apply(rev_sum_div_s2d, axis=1)\ndf_all_p01['rev_count_s1d'] = df_all_p01.apply(rev_count_s1d, axis=1)\n\ndf_all_p01['sessions_s1d'] = df_all_p01.apply(sessions_s1d, axis=1)\ndf_all_p01['date_min_s1d'] = df_all_p01.apply(year_month_code2, axis=1, str_input_column='date_min')\ndf_all_p01['date_max_s1d'] = df_all_p01.apply(year_month_code2, axis=1, str_input_column='date_max')\ndf_all_p01['date_min'] = pd.to_datetime(df_all_p01['date_min'].astype(str), format='%Y%m%d')\ndf_all_p01['date_max'] = pd.to_datetime(df_all_p01['date_max'].astype(str), format='%Y%m%d')\ndf_all_p01['date_diff_days'] = (df_all_p01['date_max'] - df_all_p01['date_min']).dt.days\ndf_all_p01['date_diff_days_s1d'] = df_all_p01.apply(date_diff_days_s1d, axis=1)\ndf_all_p01['totals_hits_avg_s1d'] = df_all_p01.apply(totals_hits_avg_s1d, axis=1)\ndf_all_p01['totals_pageviews_avg_s1d'] = df_all_p01.apply(totals_pageviews_avg_s1d, axis=1)\ndf_all_p01['date_min_s2d'] = df_all_p01.apply(date_min_s2d, axis=1)\ndf_all_p01['revenue_date_min_s1d'] = df_all_p01.apply(year_month_code3, axis=1, str_input_column='revenue_date_min')\ndf_all_p01['revenue_date_max_s1d'] = df_all_p01.apply(year_month_code3, axis=1, str_input_column='revenue_date_max')\ndf_all_p01['sp1_s1d'] = df_all_p01.apply(sp1_s1d, axis=1)\ndf_all_p01['sp1_s2d'] = df_all_p01.apply(sp1_s2d, axis=1)\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"5951a70c4de08f06f92346682f42e6fa5b3558e2"},"cell_type":"code","source":"create_crosstab_type1(df_all_p01, 'overall', int_important_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"352ea83bbf69688ba5aed010469bb772083d2fcc"},"cell_type":"code","source":"create_crosstab_type1(df_all_p01, 'train or test', int_important_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"6c1e69a7f393913f49799f3f0906621868b08299"},"cell_type":"code","source":"create_crosstab_type1(df_all_p01, 'rev_sum_div_s1d', int_important_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"3815da7fa57b811cd76a1d223f3baadd15a35238"},"cell_type":"code","source":"create_crosstab_type1(df_all_p01, 'rev_sum_div_s2d', int_important_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"937dc7b20da5e5975535b2ed2bafda93e29aceea"},"cell_type":"code","source":"create_crosstab_type1(df_all_p01, 'rev_count_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"71361a2fc880e05463a9381924ab0881e59cf675"},"cell_type":"code","source":"create_crosstab_type1(df_all_p01, 'sessions_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"a3affbff873dea1448896c29948eb5a6da14cb29"},"cell_type":"code","source":"create_crosstab_type1(df_all_p01, 'date_min_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"50663c732f6c6f542367224ecfe68016036a67e5"},"cell_type":"code","source":"create_crosstab_type1(df_all_p01, 'date_max_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"e5f92db9c9b6fbae863c0e9bdeff4841a772a673"},"cell_type":"code","source":"create_crosstab_type1(df_all_p01, 'date_diff_days_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"eed25cd9934d2f59cfa13a87e4647feedee06f19"},"cell_type":"code","source":"create_crosstab_type1(df_all_p01, 'totals_hits_avg_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"c53b996a08bc0de27446f28569fcb9b58db7d75e"},"cell_type":"code","source":"create_crosstab_type1(df_all_p01, 'totals_pageviews_avg_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"1fa000a06a95b3322d7549f3aed3d31c92ad6d3f"},"cell_type":"code","source":"create_crosstab_type1(df_all_p01, 'date_min_s2d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"56b79e2cd66655d6551c1a6c3debfd6f8221df93"},"cell_type":"code","source":"create_crosstab_type1(df_all_p01, 'sp1_s1d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"255b2fcb1509618471fb28aae1671a1216dcfc52"},"cell_type":"code","source":"create_crosstab_type1(df_all_p01, 'sp1_s2d', int_current_crosstab)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"a770b78e07c2e4c0b8c8e55d32ba9b0ec02b129b"},"cell_type":"code","source":"#df_all_p01.sample(10)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"95859e1f1518ef8c63b3574f4758e7de782e9352"},"cell_type":"code","source":"#df_all_p01.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d2e2fa9c7a7e2210984135f89d81d3ec4fc5c247","_kg_hide-input":true},"cell_type":"code","source":"if bln_run_stage1:\n    str_filename = 'dd8_table_list_all.csv'\n    csvfile1 = open(str_filename, 'w')\n    writer1 = csv.writer(csvfile1)\n    writer1.writerow( ['version', 'id', 'train_or_test', 'name', 'records', 'est_qry_size'] )\n    \n    str_select_columns = \"COUNT(*)\"\n    int_counter = 0\n\n    for str_train_or_test in ['train', 'test']:\n        bigqueryhelper = get_train_or_test_bigqueryhelper(str_train_or_test)\n        str_dataset = get_train_or_test_dataset(str_train_or_test)\n        list_tables = bigqueryhelper.list_tables()\n    \n        for table in list_tables:\n            int_counter += 1\n            print(int_counter, table, str_train_or_test)\n            str_select_from_table = table\n            query = create_simple_query1(str_dataset, str_select_columns, str_select_from_table)\n            flt_est_query_size = bigqueryhelper.estimate_query_size(query)\n            df_query = bigqueryhelper.query_to_pandas_safe(query, max_gb_scanned=flt_query_limit)\n            int_table_count = df_query.iloc[0][0]\n            writer1.writerow( [1, int_counter, str_train_or_test, table, int_table_count, flt_est_query_size] )\n\n    csvfile1.close()\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"793143923014fd60fd97559918652441c67aaf7a"},"cell_type":"code","source":"if bln_run_stage2:\n    ds_current = BigQueryHelper(active_project= \"kaggle-public-datasets\", dataset_name = \"ga_train_set\")\n    df_table_schema = ds_current.table_schema('ga_sessions_20170801')\n    df_table_schema.to_csv('dd8_table_schema_train.csv', index=False)\n    ds_current = BigQueryHelper(active_project= \"kaggle-public-datasets\", dataset_name = \"ga_test_set\")\n    df_table_schema = ds_current.table_schema('ga_sessions_20180430')\n    df_table_schema.to_csv('dd8_table_schema_test.csv', index=False)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"97f91f62797923596f0c428f7bc44c3e778347c3","_kg_hide-input":true},"cell_type":"code","source":"if bln_run_stage3:\n    str_train_or_test = 'test'\n    if str_train_or_test == 'train':\n        ds_current = BigQueryHelper(active_project= \"kaggle-public-datasets\", dataset_name = \"ga_train_set\")\n        str_dataset = \"kaggle-public-datasets.ga_train_set\"\n        int_start_date = 20160801\n        int_end_date = 20170801\n        str_output_csv = 'dd8_train_s3.csv'\n        str_select_columns = \"sessionId, fullVisitorId, date, totals.transactionRevenue, visitNumber\"\n        csv_file_read = open('../input/dd8-files/dd8_table_list_train.csv', 'r')\n    else:\n        ds_current = BigQueryHelper(active_project= \"kaggle-public-datasets\", dataset_name = \"ga_test_set\")\n        str_dataset = \"kaggle-public-datasets.ga_test_set\"\n        int_start_date = 20170802\n        int_end_date = 20180430\n        str_output_csv = 'dd8_test_s3.csv'\n        str_select_columns = \"sessionId, fullVisitorId, date, visitNumber\"\n        csv_file_read = open('../input/dd8-files/dd8_table_list_test.csv', 'r')\n    \n    \n    csv_reader = csv.reader(csv_file_read, delimiter=',')\n\n    print('sample queries run are shown below:')\n    int_row = 0\n    for row in csv_reader:\n        if int_row > 0:\n            int_id = row[0]\n            str_table = row[1]\n            int_table_date = int(str_table[-8:])\n            if int_table_date >= int_start_date and int_table_date <= int_end_date:\n                str_select_from_table = str_table\n                query = create_simple_query1(str_select_columns, str_select_from_table)\n                flt_est1_query_size = ds_current.estimate_query_size(query)\n                flt_est2_query_size = get_query_size(flt_est1_query_size) \n                flt_est_query_size_total += flt_est2_query_size\n                int_query_count += 1\n                if int_table_date == int_start_date or int_table_date == int_end_date or (int_query_count % 20 == 0):\n                    print(query)\n                    print('size1mb:', get_str_query_size_mb(flt_est1_query_size), ' size2mb: ', get_str_query_size_mb(flt_est2_query_size) ) \n                    print()\n            \n                df_temp = ds_current.query_to_pandas_safe(query, max_gb_scanned=flt_query_limit)\n                if int_query_count == 1:\n                    df_query = df_temp\n                else:\n                    df_query = pd.concat([df_query, df_temp])\n            \n                query_writer.writerow( [query, flt_est1_query_size, flt_est2_query_size] )\n        int_row += 1\n    df_query.to_csv(str_output_csv, index=False)\n\n    csv_file_read.close()\n    print('the number of lines read in input table list file: ', int_row)\n    print('the number of queries run: ', int_query_count)\n    print('total estimated size of queries run (mb):', get_str_query_size_mb(flt_est_query_size_total) )\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"22753cde7f2c94e68809839c10c5f977f34280e9","_kg_hide-input":true},"cell_type":"code","source":"if bln_run_stage4:\n    df_train_temp = pd.read_csv('../input/dd8-files/dd8_train_s3.csv', nrows=int_read_csv_rows, dtype={'fullVisitorId': 'str'})\n    df_test_temp = pd.read_csv('../input/dd8-files/dd8_test_s3.csv', nrows=int_read_csv_rows, dtype={'fullVisitorId': 'str'})\n    df_train_temp['train or test'] = 'train'\n    df_test_temp['train or test'] = 'test'\n    df_all = pd.concat([df_train_temp, df_test_temp], sort=False)\n    df_all['overall'] = 'yes'\n    print(df_all.groupby(['train or test'])['sessionId'].count())\n    df_all.to_csv('dd8_all_s4.csv', index=False) # fixed name after running this stage\n   ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0bb0e0811e66acba09a099acf2d0b29e88ce5320","_kg_hide-input":true},"cell_type":"code","source":"if bln_run_stage5:\n    df_all = pd.read_csv('../input/dd8-files/dd8_all_s4.csv', nrows=int_read_csv_rows, dtype={'fullVisitorId': 'str'})\n    df_all['year_month_code'] = df_all.apply(year_month_code2, axis=1)\n    print(df_all.groupby(['train or test'])['sessionId'].count(),'\\n')\n    print(df_all.groupby(['train or test', 'year_month_code'])['sessionId'].count())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"879d61d081b5137bc7f46a6b29d661d7bb454544","_kg_hide-input":true},"cell_type":"code","source":"if bln_run_stage6:\n    bln_create_dataframe = True\n    str_select_columns = \"fullVisitorId, count(*) as count\"\n    str_group_by_columns = \"fullVisitorId\"\n\n    str_train_or_test = 'train'\n    int_start_date = 20160801\n    int_end_date = 20170801\n    df_train = run_queries_2(bln_create_dataframe, str_train_or_test, int_start_date, int_end_date, str_select_columns, str_group_by_columns)\n\n    df_train = df_train.groupby(['fullVisitorId']).count().reset_index()\n    df_train['k_VisitorId']= df_train.index + 1000001\n    df_train = df_train[['fullVisitorId', 'k_VisitorId']]   \n    df_train.to_csv('dd8_visitorid_train.csv', index=False)\n    \nif bln_run_stage6:\n    print(df_train.info())\n    print(df_train.sample(10))","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"b3262543af906edd0c9685f31e14122518032d07"},"cell_type":"code","source":"if bln_run_stage7:\n    bln_create_dataframe = True\n    str_select_columns = \"fullVisitorId, count(*) as count\"\n    str_group_by_columns = \"fullVisitorId\"\n\n    str_train_or_test = 'test'\n    int_start_date = 20170802\n    int_end_date = 20180430\n    df_test = run_queries_2(bln_create_dataframe, str_train_or_test, int_start_date, int_end_date, str_select_columns, str_group_by_columns)\n    \n    df_test = df_test.groupby(['fullVisitorId']).count().reset_index()\n    df_test['k_VisitorId'] = df_test.index + 2000001\n    df_test = df_test[['fullVisitorId', 'k_VisitorId']]   \n    df_test.to_csv('dd8_visitorid_test.csv', index=False)  \n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a2dbbc915054cc2d4b75848d4055400e4e578fd8","_kg_hide-input":true},"cell_type":"code","source":"if bln_run_stage7:\n    print(df_test.info())\n    print(df_test.sample(10))\n    df_sample_submission = pd.read_csv('../input/google-analytics-customer-revenue-prediction/sample_submission.csv', \\\n                      nrows=int_read_csv_rows, dtype={'fullVisitorId': 'str'})\n    print(df_sample_submission.info())    ","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"e6a08d88cf12af8aeb0fcab60bb4cd6bbef40033"},"cell_type":"code","source":"if bln_run_stage8:\n    #bln_create_dataframe = True\n    #df_train = run_queries_2(bln_create_dataframe, str_train_or_test, int_start_date, int_end_date, str_select_columns, str_group_by_columns)\n    str_train_or_test = 'train'\n    int_start_date = 20160801\n    int_end_date = 20170801\n    str_select_columns_sub1 = \"fullVisitorId, visitNumber, date, totals.transactionRevenue as totals_transactionRevenue, \" + \\\n        \"totals.visits as totals_visits, totals.hits as totals_hits, totals.timeOnSite as totals_timeOnSite, \" + \\\n        \"totals.pageviews as totals_pageviews\"\n    str_select_columns = \"fullVisitorId, \" + \\\n                         \"COUNT(fullVisitorId) AS fullVisitorId_count, \" + \\\n                         \"SUM(totals_transactionRevenue) AS totals_transactionRevenue_sum, \" + \\\n                         \"COUNT(totals_transactionRevenue) AS totals_transactionRevenue_count, \" + \\\n                         \"AVG(totals_transactionRevenue) AS totals_transactionRevenue_avg, \" + \\\n                         \"SUM(totals_transactionRevenue)/1000000 AS totals_transactionRevenue_sum_div, \" + \\\n                         \"AVG(totals_transactionRevenue)/1000000 AS totals_transactionRevenue_avg_div, \" + \\\n                         \"MAX(visitNumber) AS visitNumber_max, \" + \\\n                         \"MIN(date) AS date_min, \" + \\\n                         \"MAX(date) AS date_max, \" + \\\n                         \"SUM(totals_visits) AS totals_visits_sum, \" + \\\n                         \"AVG(totals_hits) AS totals_hits_avg, \" + \\\n                         \"AVG(totals_timeOnSite) AS totals_timeOnSite_avg, \" + \\\n                         \"AVG(totals_pageviews) AS totals_pageviews_avg\"\n    str_select_from_table = \"SUB1\"\n    str_group_by_columns = \"fullVisitorId\"\n\n    query_sub1 = create_simple_query3(int_start_date, int_end_date, str_train_or_test, str_select_columns_sub1)\n    query = query_sub1 + create_simple_query4(str_select_columns, str_select_from_table, str_group_by_columns)\n    print('start and end of query:')\n    print(query[:1011], '\\n...\\n\\n', query[-1013:])\n\n    ds_current = BigQueryHelper(active_project= \"kaggle-public-datasets\", dataset_name = \"ga_train_set\")\n    flt_est1_query_size = ds_current.estimate_query_size(query)\n    flt_est2_query_size = get_query_size(flt_est1_query_size)\n    print('\\nsize1mb', get_str_query_size_mb(flt_est1_query_size))\n    print('size2mb', get_str_query_size_mb(flt_est2_query_size))    \n    ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9cbb576e2207718c7e3b90b6312cc5fb4917b7d8","_kg_hide-input":true},"cell_type":"code","source":"if bln_run_stage8:\n    df_train = ds_current.query_to_pandas_safe(query, max_gb_scanned=flt_query_limit)\n    print(df_train.info())\n    print('\\nTotal sessions (check):', df_train['fullVisitorId_count'].sum(), '\\n')\n    print(df_train.sample(10))\n    df_temp = df_train[ df_train['totals_transactionRevenue_sum'] > 0  ]\n    #df_temp2 = df_temp[ df_temp['totals_transactionRevenue_count'] > 1  ]\n    print(df_temp.sample(10))\n    df_train.to_csv('dd8_visitor_train_p01_s08.csv', index=False)   ","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"1cc864fe3eaa83a00c8426c1d2e40f455d22da81"},"cell_type":"code","source":"if bln_run_stage9:\n    str_train_or_test = 'test'\n    int_start_date = 20170802\n    int_end_date = 20180430\n    str_select_columns_sub1 = \"fullVisitorId, visitNumber, date, NULL as totals_transactionRevenue, \" + \\\n        \"totals.visits as totals_visits, totals.hits as totals_hits, totals.timeOnSite as totals_timeOnSite, \" + \\\n        \"totals.pageviews as totals_pageviews\"\n    str_select_columns = \"fullVisitorId, \" + \\\n                         \"COUNT(fullVisitorId) AS fullVisitorId_count, \" + \\\n                         \"NULL AS totals_transactionRevenue_sum, \" + \\\n                         \"NULL AS totals_transactionRevenue_count, \" + \\\n                         \"NULL AS totals_transactionRevenue_avg, \" + \\\n                         \"NULL AS totals_transactionRevenue_sum_div, \" + \\\n                         \"NULL AS totals_transactionRevenue_avg_div, \" + \\\n                         \"MAX(visitNumber) AS visitNumber_max, \" + \\\n                         \"MIN(date) AS date_min, \" + \\\n                         \"MAX(date) AS date_max, \" + \\\n                         \"SUM(totals_visits) AS totals_visits_sum, \" + \\\n                         \"AVG(totals_hits) AS totals_hits_avg, \" + \\\n                         \"AVG(totals_timeOnSite) AS totals_timeOnSite_avg, \" + \\\n                         \"AVG(totals_pageviews) AS totals_pageviews_avg\"\n    str_select_from_table = \"SUB1\"\n    str_group_by_columns = \"fullVisitorId\"\n\n    query_sub1 = create_simple_query3(int_start_date, int_end_date, str_train_or_test, str_select_columns_sub1)\n    query = query_sub1 + create_simple_query4(str_select_columns, str_select_from_table, str_group_by_columns)\n    print('start and end of query:')\n    print(query[:1011], '\\n...\\n\\n', query[-1013:])\n\n    bigqueryhelper = get_train_or_test_bigqueryhelper(str_train_or_test)\n    flt_est1_query_size = bigqueryhelper.estimate_query_size(query)\n    flt_est2_query_size = get_query_size(flt_est1_query_size)\n    print('\\nsize1mb', get_str_query_size_mb(flt_est1_query_size))\n    print('size2mb', get_str_query_size_mb(flt_est2_query_size))    \n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e333c7736bbdc714901ab72d5a6f51793cbfe470","_kg_hide-input":true},"cell_type":"code","source":"if bln_run_stage9:\n    df_test = bigqueryhelper.query_to_pandas_safe(query, max_gb_scanned=flt_query_limit)\n    print(df_test.info())\n    print('\\nTotal sessions (check):', df_test['fullVisitorId_count'].sum(), '\\n')\n    print(df_test.sample(10))\n    df_test.to_csv('dd8_visitor_test_p01_s09.csv', index=False)   ","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"36c363674c9645148e122d5097dfd759003db957"},"cell_type":"code","source":"if bln_run_stage10:\n    str_train_or_test = 'train'\n    int_start_date = 20160801\n    int_end_date = 20170801\n    str_select_columns_sub1 = \"fullVisitorId, date, totals.transactionRevenue as revenue\" \n    str_where_sub1 = \"totals.transactionRevenue > 0\"\n    str_select_columns = \"fullVisitorId, \" + \\\n                         \"SUM(revenue) AS revenue_sum, \" + \\\n                         \"COUNT(revenue) AS revenue_count, \" + \\\n                         \"AVG(revenue) AS revenue_avg, \" + \\\n                         \"SUM(revenue)/1000000 AS revenue_sum_div, \" + \\\n                         \"AVG(revenue)/1000000 AS revenue_avg_div, \" + \\\n                         \"MIN(date) AS revenue_date_min, \" + \\\n                         \"MAX(date) AS revenue_date_max\" \n    str_select_from_table = \"SUB1\"\n    str_group_by_columns = \"fullVisitorId\"\n\n    query_sub1 = create_simple_query3(int_start_date, int_end_date, str_train_or_test, str_select_columns_sub1, str_where_sub1)\n    query = query_sub1 + create_simple_query4(str_select_columns, str_select_from_table, str_group_by_columns)\n    print('start and end of query:')\n    print(query[:511], '\\n...\\n\\n', query[len(query)-500:])\n\n    bigqueryhelper = BigQueryHelper(active_project= \"kaggle-public-datasets\", dataset_name = \"ga_train_set\")\n    flt_est1_query_size = bigqueryhelper.estimate_query_size(query)\n    flt_est2_query_size = get_query_size(flt_est1_query_size)\n    print('\\nsize1mb', get_str_query_size_mb(flt_est1_query_size))\n    print('size2mb', get_str_query_size_mb(flt_est2_query_size))    \n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"53581692cc0bac1cc080823671a37b7f4ef0dcf2"},"cell_type":"code","source":"if bln_run_stage10:\n    df_train = bigqueryhelper.query_to_pandas_safe(query, max_gb_scanned=flt_query_limit)\n    print(df_train.info())\n    df_train.to_csv('dd8_visitor_train_p02_s10.csv', index=False)   ","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"48aa4642b0a4d79d7d5e1099c9c391dde6d979c3"},"cell_type":"code","source":"if bln_run_stage11:\n    # totals.bounces   totals.newVisits   trafficSource.referralPath   trafficSource.campaign\n    # trafficSource.source   trafficSource.adwordsClickInfo.page  trafficSource.isTrueDirect\n    # device.browser   device.operatingSystem   device.isMobile   geoNetwork.continent\n    \n    str_train_or_test = 'train'\n    int_start_date = 20160801\n    int_end_date = 20160803 #20170801\n    str_select_columns_sub1 = \"fullVisitorId, 'yes' as overall, 'train' as train_or_test, \" + \\\n                              \"'device_browser' as variable, device.browser as category\"\n    str_select_columns = \"variable, category, \" + \\\n                         \"COUNTIF(overall='yes') AS overall_count, \" + \\\n                         \"COUNTIF(train_or_test='train') AS train_count, \" + \\\n                         \"COUNTIF(train_or_test='test') AS test_count\"\n    str_select_from_table = \"SUB1\"\n    str_group_by_columns = \"variable, category\"\n\n    query_sub1 = create_simple_query3(int_start_date, int_end_date, str_train_or_test, str_select_columns_sub1)\n    query = query_sub1 + create_simple_query4(str_select_columns, str_select_from_table, str_group_by_columns)\n    print('start and end of query:')\n    print(query[:1011], '\\n...\\n\\n', query[-1013:])\n\n    bigqueryhelper = get_train_or_test_bigqueryhelper(str_train_or_test)\n    flt_est1_query_size = bigqueryhelper.estimate_query_size(query)\n    flt_est2_query_size = get_query_size(flt_est1_query_size)\n    print('\\nsize1mb', get_str_query_size_mb(flt_est1_query_size))\n    print('size2mb', get_str_query_size_mb(flt_est2_query_size))    \n    ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0e32df3ea189e8b7dcfb9b8b9678fffdc5b4e0da","_kg_hide-input":true},"cell_type":"code","source":"if bln_run_stage11:\n    df_train = bigqueryhelper.query_to_pandas_safe(query, max_gb_scanned=flt_query_limit)\n    print(df_train.info())\n    #print('\\nTotal sessions (check):', df_train['fullVisitorId_count'].sum(), '\\n')\n    print(df_train.sample(24))\n    #df_temp = df_train[ df_train['totals_transactionRevenue_sum'] > 0  ]\n    ##df_temp2 = df_temp[ df_temp['totals_transactionRevenue_count'] > 1  ]\n    #print(df_temp.sample(10))\n    #df_train.to_csv('dd8_visitor_train_p01_s08.csv', index=False)   \n    ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7fc780604b304c59a99e8d23699abe96ecdf547c","_kg_hide-input":true},"cell_type":"code","source":"if bln_run_stage11:\n    query = \"\"\"WITH SUB1 AS ( \nSELECT fullVisitorId, 'yes' as overall, 'train' as train_or_test, 'device_browser' as variable, device.browser as category \nFROM `kaggle-public-datasets.ga_train_set.ga_sessions_20160801` UNION ALL SELECT fullVisitorId, 'yes' as overall, 'train' as train_or_test, 'device_browser' as variable, device.browser as category \nFROM `kaggle-public-datasets.ga_train_set.ga_sessions_20160802` UNION ALL SELECT fullVisitorId, 'yes' as overall, 'test' as train_or_test, 'device_browser' as variable, device.browser as category \nFROM `kaggle-public-datasets.ga_test_set.ga_sessions_20180430` ) \nSELECT variable, category, COUNTIF(overall='yes') AS overall_count, COUNTIF(train_or_test='train') AS train_count, COUNTIF(train_or_test='test') AS test_count\nFROM `SUB1` \nGROUP BY variable, category\"\"\"\n\n    flt_est1_query_size = bigqueryhelper.estimate_query_size(query)\n    print(get_str_query_size_mb(flt_est1_query_size))","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"f0eefd72d116a18f843b3d71100588ecb7499de4","_kg_hide-output":true},"cell_type":"code","source":"# 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_p01[ df_all_p01['totals_pageviews_avg_s1d'] == 'unknown' ]\n#df_temp[['totals_pageviews_avg', 'totals_pageviews_avg_s1d']].sample(20)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"09833016c72d6fe7fb49179d4e94aa1ea1981e01"},"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}