{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"markdown","source":"# Kernel for saving files "},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"\nimport os\nimport pandas as pd\nimport numpy as np\nfrom pandas.io.json import json_normalize\nimport json\nimport time\nimport warnings\n\n#from pycountry_convert import ( map_countries, country_name_to_country_alpha3,)\nimport pytz as pytz\nimport datetime\n\n#Plotting\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nplt.style.use('ggplot')\n\n#Sklearn\nfrom sklearn.preprocessing import LabelEncoder\nfrom sklearn.metrics import mean_squared_error\nfrom sklearn.model_selection import KFold\n\n#lgm and graph viz\nimport graphviz \nimport lightgbm as lgb\n\nwarnings.filterwarnings('ignore')\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c42cfbfdc125abd790b0d6a7680c7757ff88f534"},"cell_type":"code","source":"os.listdir('../input')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"86e32750a035abf3b7909e41ce174b220534ca00"},"cell_type":"code","source":"def load_df(csv_path='../input/train.csv', nrows=None):\n    JSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\n      \n    df = pd.read_csv(csv_path, \n                     converters={column: json.loads for column in JSON_COLUMNS}, \n                     dtype={'fullVisitorId': 'str', 'visitId':'str', 'visitStartTime':'str', 'date':'str'}, \n                     nrows=nrows)\n\n    #Normalize JSON colunmns and drop\n    for column in JSON_COLUMNS:\n        column_as_df = json_normalize(df[column])\n        column_as_df.columns = [f\"{column}.{subcolumn}\" for subcolumn in column_as_df.columns]\n        df = df.drop(column, axis=1).merge(column_as_df, right_index=True, left_index=True)\n    return df\n\n\ndef drop_constant_cols(df):\n    ## Drop constant columns\n    const_cols = [c for c in df.columns if df[c].nunique(dropna=False) == 1]\n    df.drop(const_cols, axis=1, inplace=True)\n    \n    #this columnm is only in train data\n    try:\n        df.drop('trafficSource.campaignCode', axis=1, inplace=True)   \n    except:\n        None   \n    \n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1aef970d84363cef5d9bdcd8a9323c8a15012035"},"cell_type":"code","source":"%%time\n#Load\ntrain_df = load_df(csv_path='../input/ga-customer-revenue-prediction/train.csv', nrows = None)\ndrop_constant_cols(train_df)\n\ntest_df = load_df(csv_path='../input/ga-customer-revenue-prediction/test.csv', nrows = None)\ndrop_constant_cols(test_df)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0c356db4f53a6daab75c38d42ca889229a900e85"},"cell_type":"code","source":"# Extract target values and Ids\ncat_cols = ['channelGrouping','device.browser',\n       'device.deviceCategory', 'device.isMobile', 'device.operatingSystem',\n       'geoNetwork.city', 'geoNetwork.continent', 'geoNetwork.country',\n       'geoNetwork.metro', 'geoNetwork.networkDomain', 'geoNetwork.region',\n       'geoNetwork.subContinent','trafficSource.adContent',\n       'trafficSource.adwordsClickInfo.adNetworkType',\n       'trafficSource.adwordsClickInfo.gclId',\n       'trafficSource.adwordsClickInfo.isVideoAd',\n       'trafficSource.adwordsClickInfo.page',\n       'trafficSource.adwordsClickInfo.slot', 'trafficSource.campaign',\n       'trafficSource.isTrueDirect', 'trafficSource.keyword',\n       'trafficSource.medium', 'trafficSource.referralPath',\n       'trafficSource.source'  ]\n\n\nnum_cols = ['visitNumber', 'totals.bounces', 'totals.hits',\n            'totals.newVisits', 'totals.pageviews', \n            '_local_hourofday'  ]\n\ninteraction_cols = ['totals.hits / totals.pageviews', 'totals.hits * totals.pageviews',\n       'totals.hits - totals.pageviews']\n\nvisitStartTime = ['visitStartTime']\n\ntime_cols = ['_dayofweek', '_monthofyear', '_dayofyear']\n\nID_cols = ['date', 'fullVisitorId', 'sessionId', 'visitId']\n\ntarget_col = ['totals.transactionRevenue']\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"25c8783561e78ebf27efe981d5715cdd7cdf305a"},"cell_type":"code","source":"#Load\ngeocode_df= pd.read_csv('../input/geocoded-data/geocodes_timezones.csv')\n\ndef time_zone_converter(x):\n    \n    try:\n        return pytz.country_timezones(x)[0]\n    except AttributeError:\n        return np.nan\n   \n\ndef time_localizer(s):\n    #format of series [time,zone]\n    try:\n        tz =pytz.timezone(s[1])\n        return pytz.utc.localize(s[0], is_dst=None).astimezone(tz)\n    except:\n        return np.nan\n    \ndef remove_missing_vals(x):\n    remove_list = ['(not set)', 'not available in demo dataset','unknown.unknown']\n    if x in remove_list:\n        return ''\n    else:\n        return x \n    \ndef map_timezone(x):   \n    try:\n        return timezone_dict[x]\n    except KeyError:\n        return 'UTC'\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2dc15ad883aaee95fb3846d8db1a5bfc790d068d"},"cell_type":"code","source":"%%time\ntrain_df['visitStartTime'] = pd.to_datetime(train_df['visitStartTime'], unit = 's')\ntest_df['visitStartTime'] = pd.to_datetime(test_df['visitStartTime'], unit = 's')\n\n#Generate foreign key '_search_term' by concatenating city, region, country\ntrain_df['_search_term'] = train_df['geoNetwork.city'].map(remove_missing_vals) + ' ' + train_df['geoNetwork.region'].map(remove_missing_vals) + ' ' + train_df['geoNetwork.country'].map(remove_missing_vals)\ntest_df['_search_term'] = test_df['geoNetwork.city'].map(remove_missing_vals) + ' ' + test_df['geoNetwork.region'].map(remove_missing_vals) + ' ' + test_df['geoNetwork.country'].map(remove_missing_vals)\n\n#Set global variable, needed for map_timezone function\nglobal timezone_dict\ntimezone_dict = dict(zip(geocode_df['search_term'], geocode_df['timeZoneId']))\n\n#Map timezones\ntrain_df['_timeZoneId'] = train_df['_search_term'].map(map_timezone)\ntest_df['_timeZoneId'] = test_df['_search_term'].map(map_timezone)\n  \n#Create time zone aware column\ntrain_df['_local_time'] = train_df[['visitStartTime', '_timeZoneId']].apply(time_localizer, axis = 1).astype(str)\ntest_df['_local_time'] = test_df[['visitStartTime', '_timeZoneId']].apply(time_localizer, axis = 1).astype(str)  \n\n#Localize hour time\ntrain_df['_local_hourofday'] = train_df['_local_time'].str[11:13]\ntest_df['_local_hourofday'] = test_df['_local_time'].str[11:13]\n\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"55966834f103987d878d69d365169529bd3111ee"},"cell_type":"markdown","source":"## Time since last visit "},{"metadata":{"trusted":true,"_uuid":"733667372e2e0e5a345d2aa39f6d77bc62900c0f"},"cell_type":"code","source":"%%time\ntrain_df['_time_since_last_visit'] = train_df[['fullVisitorId','visitStartTime']].groupby('fullVisitorId')['visitStartTime'].diff()\ntest_df['_time_since_last_visit'] = test_df[['fullVisitorId','visitStartTime']].groupby('fullVisitorId')['visitStartTime'].diff()\n\n#Replace 0 with very large number, as we are interested in small / medium values\ntrain_df['_time_since_last_visit'] = train_df['_time_since_last_visit'].fillna(999999999)\ntest_df['_time_since_last_visit'] = test_df['_time_since_last_visit'].fillna(999999999)\n\n#Convert to numeric\ntrain_df['_time_since_last_visit'] = pd.to_numeric(train_df['_time_since_last_visit'])\ntest_df['_time_since_last_visit'] = pd.to_numeric(test_df['_time_since_last_visit'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b0858ee9d3f8bb8ab4a2d3fea41d97c3579032b3"},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5dad9be8d9a480ea07dc5790cea6a23f7f8076d8"},"cell_type":"markdown","source":"## Label encoding "},{"metadata":{"trusted":true,"_uuid":"6c25aebcd282e94b145220b1ed80511e891cafa6"},"cell_type":"code","source":"# %%time\n# #Categorical encoding\n# for c in cat_cols:\n#     #Convert NAs to unknown\n#     train_df[c] = train_df[c].fillna('unknown')\n#     test_df[c] = test_df[c].fillna('unknown')\n    \n#     #Encode\n#     lbl = LabelEncoder()\n#     lbl.fit( list(train_df[c].values.astype('str')) + list(test_df[c].values.astype('str')))\n#     train_df[c] = lbl.transform(list(train_df[c].values.astype('str')))\n#     test_df[c] = lbl.transform(list(test_df[c].values.astype('str')))\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cfb85ebf4eeb667caa0347fd6096255a0d2312fa"},"cell_type":"code","source":"train_df.to_pickle('train_flat_local_cat_enc.pkl')\ntest_df.to_pickle('test_flat_local_cat_enc.pkl')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7718524a7fc1bef76fe5cc9ddd57235f31324c33"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"68f7e2fbeff8252dbd91915a0825447e66a3a538"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8d72682efb76800ef7723298bf0bd39fa0d9c0b1"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"78b3d3fb30291a3b79e742406f1d5346db4402bf"},"cell_type":"code","source":"","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}