{"cells":[{"metadata":{"_uuid":"489f511eadce516ec2e72d7f22ac15b2ce501b45"},"cell_type":"markdown","source":"Please upvote if you find notebook useful


\n\n

Problem Statement

\nIn this competition, you’re challenged to analyze a Google Merchandise Store (also known as GStore, where Google swag is sold) customer dataset to predict revenue per customer. \n\n

\nSubmissions are scored on the root mean squared error. RMSE or RMSD is defined as:\n\n![RSME or RSMD](https://wikimedia.org/api/rest_v1/media/math/render/svg/eeb88fa0f90448e9d1a67cd7a70164f674aeb300)\n\n

\n Final submission deadline: November 15, 2018\n

GStore Data Preparation

\n
    \n
  1. Data Preparation steps\n \n
  2. For detailed EDA and Baseline model, check notebook [GStore EDA](https://www.kaggle.com/rahullalu/gstore-eda-lgbm-baseline-1-4281)\n
  3. Prepared public [Dataset](https://www.kaggle.com/rahullalu/gstore-prepared-dataset) can directly be consumed in a kernel\n
\n"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"scrolled":true},"cell_type":"code","source":"#IMPORTING REQUIRED LIBRARIES\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport os\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport math\n\nfrom lightgbm.sklearn import LGBMRegressor\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.metrics import mean_squared_error\nfrom sklearn.model_selection import KFold,StratifiedKFold\n\nimport gc\ngc.enable()\n\n\nimport warnings\nwarnings.filterwarnings(\"ignore\")\n%matplotlib inline\n\n","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true,"scrolled":false},"cell_type":"code","source":"#DATASET VIEW\npath1=\"../input/\"\ndata_files=list(os.listdir(path1))\ndf_files=pd.DataFrame(data_files,columns=['File_Name'])\ndf_files['Size_in_MB']=df_files.File_Name.apply(lambda x:round(os.stat(path1+x).st_size/(1024*1024),2))\ndf_files","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5cba841622718acac545f2949d73e4f04faa3a01","scrolled":true},"cell_type":"code","source":"#All functions\n\n#FUNCTION FOR PROVIDING FEATURE SUMMARY\ndef feature_summary(df_fa):\n print('DataFrame shape')\n print('rows:',df_fa.shape[0])\n print('cols:',df_fa.shape[1])\n col_list=['Null','Unique_Count','Data_type','Max/Min','Mean','Std','Skewness','Sample_values']\n df=pd.DataFrame(index=df_fa.columns,columns=col_list)\n df['Null']=list([len(df_fa[col][df_fa[col].isnull()]) for i,col in enumerate(df_fa.columns)])\n #df['%_Null']=list([len(df_fa[col][df_fa[col].isnull()])/df_fa.shape[0]*100 for i,col in enumerate(df_fa.columns)])\n df['Unique_Count']=list([len(df_fa[col].unique()) for i,col in enumerate(df_fa.columns)])\n df['Data_type']=list([df_fa[col].dtype for i,col in enumerate(df_fa.columns)])\n for i,col in enumerate(df_fa.columns):\n if 'float' in str(df_fa[col].dtype) or 'int' in str(df_fa[col].dtype):\n df.at[col,'Max/Min']=str(round(df_fa[col].max(),2))+'/'+str(round(df_fa[col].min(),2))\n df.at[col,'Mean']=df_fa[col].mean()\n df.at[col,'Std']=df_fa[col].std()\n df.at[col,'Skewness']=df_fa[col].skew()\n df.at[col,'Sample_values']=list(df_fa[col].unique())\n \n return(df.fillna('-'))\n\n#FUNCTION FOR READING DICTIONARY ITEMS AND HANDLING KEYERROR\ndef get_val(x,col):\n try:\n y=x[col]\n except:\n y=np.nan\n return(y)\n\n#FUNCTION FOR CALCULATING RSME\ndef rsme(y,pred):\n return(mean_squared_error(y,pred)**0.5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"86a4600deb10f1dac4b21039b172f72470a503b5","scrolled":true},"cell_type":"code","source":"%%time\n#READING TRAINING AND TEST DATASET\nprint('reading train dataset...')\ndf_train=pd.read_csv(path1+'train.csv',dtype={'fullVisitorId':str,'date':str,'sessionId':str,'visitId':str,'visitStartTime':str})\nprint('reading test dataset...')\ndf_test=pd.read_csv(path1+'test.csv',dtype={'fullVisitorId':str,'date':str,'sessionId':str,'visitId':str,'visitStartTime':str})\nprint('data reading complete')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"944e90ca010087fd75c9e5bd99e4aa7d8065a2ba","scrolled":true},"cell_type":"code","source":"#CHECKING TOP FIVE TRAIN OBSERVATIONS OR ROWS\ndf_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4c279ad830babe55c2a93246976eb8bcac571414","scrolled":false},"cell_type":"code","source":"#FEATURE SUMMARY FOR TRAIN DATASET\nfeature_summary(df_train)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a09edeb18f935507c07f5b3e7f05a67aa93b71cd","scrolled":false},"cell_type":"code","source":"#CHECKING TOP 5 TEST OBSERVATIONS OR ROWS\ndf_test.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2adb6124e9bd85a751d325c0fd72d01b571d53fe","scrolled":false},"cell_type":"code","source":"#FEATURE SUMMARY FOR TEST DATASET\nfeature_summary(df_test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"735579a1b2b8d8b329c7385900eab91a5c437f91","scrolled":true},"cell_type":"code","source":"#COMBINING TRAIN AND TEST DATASET\ndf_combi=pd.concat([df_train,df_test],ignore_index=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0433805fb4784ad0e46306cdae0bb66709c6d466","scrolled":false},"cell_type":"code","source":"#STORING NUMBER OF ROWS IN TRAIN DATASET\n#AND DELETING BOTH TRAIN & TEST DATAFRAMES\n#NUMBER OF ROWS IN TRAIN DATASET WILL HELP IN SPLITING COMBINED DATASET INTO TRAIN & TEST \ntrain_len=len(df_train)\ndel df_train,df_test\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1a8fe58a5846cb16daaa677b6b1ff2fd24b91ffd","scrolled":false},"cell_type":"code","source":"#FEATURE SUMMARY FOR COMBINED DATASET\ndf_combi_fs=feature_summary(df_combi)\ndf_combi_fs","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"acdd6947598a8218bb5d0cc156b2e35eb09211d0"},"cell_type":"markdown","source":"

Handling Json Features

\n
    \n
  1. Json features device, geoNetwork,totals and trafficSource\n
  2. Will be using eval() function to convert string to Dictionary\n
  3. Extracting underlying fields under each Json field\n
  4. Converting each Json field into its underlying fields\n
\n"},{"metadata":{"trusted":true,"_uuid":"4197191c6329e21b21c58df32f8d592d50719312","scrolled":true},"cell_type":"code","source":"%%time\n#REPLACING false WITH False AND true WITH True\n#AS false AND true HAVE NO MEANING IN PYTHON\n#ALSO REPLACING 'not available in demo dataset' WITH 'NaN'\nj_fields=['device','geoNetwork','totals','trafficSource']\n\nfor col in j_fields:\n df_combi[col].replace('false','False',inplace=True,regex=True)\n df_combi[col].replace('true','True',inplace=True,regex=True)\n df_combi[col].replace('not available in demo dataset','NaN',inplace=True,regex=True)\n df_combi[col].replace('(not set)','NaN',inplace=True,regex=True)\n df_combi[col]=df_combi[col].apply(lambda x: eval(x))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ea615b7763f2dfe3a358fc1dd97a681dcf90eff6"},"cell_type":"code","source":"%%time\n#EXTRACTING FEATURE NAMES UNDER EACH JSON FEATURES\ndevice_cols=list(df_combi['device'][0].keys())\ngeoNetwork_cols=list(df_combi['geoNetwork'][0].keys())\ntotals_cols=list(df_combi['totals'][0].keys())\ntrafficSource_cols=list(df_combi['trafficSource'][0].keys())\n\nfor i in range(1,len(df_combi)):\n device_cols=list(set(device_cols) | set(list(df_combi['device'][i].keys())))\n geoNetwork_cols=list(set(geoNetwork_cols) | set(list(df_combi['geoNetwork'][i].keys())))\n totals_cols=list(set(totals_cols) | set(list(df_combi['totals'][i].keys())))\n trafficSource_cols=list(set(trafficSource_cols) | set(list(df_combi['trafficSource'][i].keys())))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e8b5c88d49c8454faa4845f667cafb442918d81a"},"cell_type":"markdown","source":"Features under device Json feature"},{"metadata":{"trusted":true,"_uuid":"2297388b2f1819ac20e55059c82bfa7c8792a307","scrolled":false,"_kg_hide-input":true},"cell_type":"code","source":"device_cols","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7585c265e2c354e5993d37f5f9d65364b44407b1"},"cell_type":"markdown","source":"Features under geoNetwork Json feature"},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"9091958b600570e920873cd3a02584779c7da595","_kg_hide-input":true},"cell_type":"code","source":"geoNetwork_cols","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7ea0c262e483165e9ed95720364ac00673daefa7"},"cell_type":"markdown","source":"Features under totals Json feature"},{"metadata":{"trusted":true,"_uuid":"03c9a90398b4e4188396fa2213e55b2e8df8fdf1","_kg_hide-input":true},"cell_type":"code","source":"totals_cols","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"65fa77b6a61791546b223893832d393e9ce58933"},"cell_type":"markdown","source":"Features under trafficSource Json feature"},{"metadata":{"trusted":true,"_uuid":"b2aa5c73a80ebcb3b568114943936f4ad6e8f7f7","scrolled":false,"_kg_hide-input":true},"cell_type":"code","source":"trafficSource_cols","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2137620a94279235dd870852ec6ad40fd384a623","scrolled":false},"cell_type":"code","source":"%%time\n#CONVERTING DEVICE JSON FEATURE INTO INDIVIDUAL SUB FEATURES UNDER IT\nfor jlist in device_cols:\n col_name='device_'+jlist\n df_combi[col_name]=df_combi['device'].apply(lambda x: get_val(x,jlist))\n\n#DROPPING device JSON FEATURE\ndf_combi.drop('device',axis=1,inplace=True)\ndisplay(df_combi.head()) ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fc62b72795957030eabadd84126d8df8e7ee39ce","scrolled":true},"cell_type":"code","source":"%%time\n#CONVERTING geoNetwork JSON FEATURE INTO INDIVIDUAL SUB FEATURES UNDER IT\nfor jlist in geoNetwork_cols:\n col_name='geoNetwork_'+jlist\n df_combi[col_name]=df_combi['geoNetwork'].apply(lambda x: get_val(x,jlist))\n \n#DROPPING geoNetwork JSON FEATURE\ndf_combi.drop('geoNetwork',axis=1,inplace=True)\ndisplay(df_combi.head())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"da2698cd9891ebeb12f59c6ad91b8a24fee3de53"},"cell_type":"code","source":"%%time\n#CONVERTING trafficSource JSON FEATURE INTO INDIVIDUAL SUB FEATURES UNDER IT\nfor jlist in trafficSource_cols:\n col_name='trafficSource_'+jlist\n df_combi[col_name]=df_combi['trafficSource'].apply(lambda x: get_val(x,jlist)) \n\n\n#DROPPING trafficSource JSON FEATURE\ndf_combi.drop('trafficSource',axis=1,inplace=True)\ndisplay(df_combi.head())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"3023ca53c9dd0849b29d887f749d02557b6836c6"},"cell_type":"code","source":"%%time\n#CONVERTING totals JSON FEATURE INTO INDIVIDUAL SUB FEATURES UNDER IT\nfor jlist in totals_cols:\n col_name='totals_'+jlist\n df_combi[col_name]=df_combi['totals'].apply(lambda x: get_val(x,jlist)) \n \n#DROPPING totals JSON FEATURE\ndf_combi.drop('totals',axis=1,inplace=True)\ndisplay(df_combi.head())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"afb0b64c354ae7a01356d69b57c42a8dbe56b071"},"cell_type":"code","source":"#DELETING COMBINED FEATURE SUMMARY DATAFRAME\ndel df_combi_fs\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2f5a272f6d01753f1d29ac723bff1af4c8d8333f","scrolled":true},"cell_type":"code","source":"#CONVERTING ALL STRING 'NaN' VALUES TO np.nan\ndf_combi.replace('NaN',np.nan,inplace=True)\n\n#CONVERTING VALUES IN FEATURE trafficSource_adwordsClickInfo AS THEY ARE AGAIN Json FEATURES\n#WE HAVE TO HANDLE trafficSource_adwordsClickInfo AS OTHER Json FEATURES\ndf_combi['trafficSource_adwordsClickInfo']=df_combi.trafficSource_adwordsClickInfo.apply(lambda x: str(x))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1c30409fe854c6e14a88630818652fd7fc1bf2ce","scrolled":false},"cell_type":"code","source":"#FEATURE SUMMARY FOR COMBINED DATASET\ndf_combi_fs=feature_summary(df_combi)\ndf_combi_fs","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"674a1ec068091d2de3a8037dec0134dd7330d35f"},"cell_type":"markdown","source":"

Dropping irrelevant features

\nDropping all the features having same value in all observations"},{"metadata":{"trusted":true,"_uuid":"f18516ce333dd6e2681bacaa3ab19f2bb7cd5eed","scrolled":false},"cell_type":"code","source":"#IDENFIYING ALL FEATURES WITH SAME VALUES IN ALL OBSERVATIONS\ncol_drop=list(df_combi_fs.index[df_combi_fs.Unique_Count==1])\nprint('No of Columns to be removed:',len(col_drop))\nprint(col_drop)\n\n#DROPPING ALL FEATURES WITH SAME VALUES IN ALL OBSERVATIONS\ndf_combi.drop(col_drop,axis=1,inplace=True)\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2ba5cc07ecc61fe32d98d41830298d6e009d1c60","scrolled":true},"cell_type":"code","source":"#FEATURE SUMMARY FOR COMBINED DATASET\ndf_combi_fs=feature_summary(df_combi)\ndf_combi_fs","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a6b51ec42ce523d6882a9fb6caf5ad2b670153a7"},"cell_type":"code","source":"#DROPPING trafficSource_campaignCode, AS IT HAS ONLY 2 OBSERVATIONS WITH SOME VALUES\ndf_combi.drop('trafficSource_campaignCode',axis=1,inplace=True)\n\n#GARBAGE COLLECTION\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ed4102c37debbd6501204c05ffed8c216a69dd36"},"cell_type":"code","source":"%%time\n#EXTRACTING FEATURES NAMES FROM Json FEATURE trafficSource_adwordsClickInfo\ndf_combi['trafficSource_adwordsClickInfo']=df_combi.trafficSource_adwordsClickInfo.apply(lambda x: eval(x))\ntsadclick_cols=list(df_combi['trafficSource_adwordsClickInfo'][0].keys())\n\nfor i in range(1,len(df_combi)):\n tsadclick_cols=list(set(tsadclick_cols) | set(list(df_combi['trafficSource_adwordsClickInfo'][i].keys())))\n\nprint('FEATURES UNDER trafficSource_adwordsClickInfo Json FEATURE\\n',tsadclick_cols)\n\n#EXTRACTING FEATURES FROM Json FEATURE trafficSource_adwordsClickInfo\nfor jlist in tsadclick_cols:\n col_name='tsadclick_'+jlist\n df_combi[col_name]=df_combi['trafficSource_adwordsClickInfo'].apply(lambda x: get_val(x,jlist))\n df_combi[col_name]=df_combi[col_name].apply(lambda x: str(x))\n \n#DROPPING Json FEATURE trafficSource_adwordsClickInfo\ndf_combi.drop('trafficSource_adwordsClickInfo',axis=1,inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"27ce7195ca14b3672257e4f80a24d1613fd5db4e","scrolled":true},"cell_type":"code","source":"#DROPPING IRRELEVANT FEATURES tsadclick_targetingCriteria AND tsadclick_criteriaParameters\ndf_combi.drop(['tsadclick_targetingCriteria','tsadclick_criteriaParameters'],axis=1,inplace=True)\n\n#GARBAGE COLLECTION\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7874863d2b4bb2410669746443643ba1e196a610"},"cell_type":"code","source":"# %%time\n# #HANDLING SURROGATES IN TRAIN DATASET\n# #THERE ARE NO SUCH ENTRIES IN TEST SET\n# #SURROGATES: VALUES CAN'T BE ENCODED/DECODED USING utf-8\n# for col in df_combi.columns:\n# if ((df_combi[col].dtype==object) & (col!='fullVisitorId')):\n# df_combi[col]=df_combi[col].apply(lambda x: np.nan if x==np.nan else str(x).encode('utf-8', 'replace').decode('utf-8'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c65254398fcb633dfb763f0e64589ef307863c45","scrolled":false},"cell_type":"code","source":"%%time\n#REPLACING 'nan' and '(not provided)' WITH np.nan\ndf_combi.replace(\"nan\",np.nan,inplace=True)\ndf_combi.replace(\"(not provided)\",np.nan,inplace=True)\ndf_combi.replace(\"(NaN)\",np.nan,inplace=True)\ndf_combi['totals_transactionRevenue'].replace(np.nan,0,inplace=True)\n\n#CONVERTING FEATURES TO CORRECT DATATYPE\ndf_combi['totals_pageviews']=df_combi['totals_pageviews'].astype('float')\ndf_combi['totals_transactionRevenue']=df_combi['totals_transactionRevenue'].astype('float')\ndf_combi['totals_hits']=df_combi['totals_hits'].astype('float')\ndf_combi['tsadclick_page']=df_combi['tsadclick_page'].astype('float')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4724cfa42b357e35aaa876aabd8eb1aa07f136b9","scrolled":true},"cell_type":"code","source":"#FEATURE SUMMARY FOR COMBINED DATASET\ndf_combi_fs=feature_summary(df_combi)\ndf_combi_fs","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7149eeedf0e33b436c6f228344f04b373de1da9a","scrolled":true},"cell_type":"code","source":"#SPLITING COMBINED DATASET BACK TO TRAIN AND TEST SETS\ntrain=df_combi[:train_len]\ntest=df_combi[train_len:]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"54f42a660df430f9310e8c00e65ec9039a46a3b9"},"cell_type":"code","source":"#FEATURE SUMMARY TRAIN SET\nfeature_summary(train)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"56e2d6d848d50e16d69569f279be05c4b90e39f6"},"cell_type":"code","source":"#FEATURE SUMMARY TEST SET\nfeature_summary(test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"66aa7999ef19a7cc59044929b812117591cd796d"},"cell_type":"code","source":"for col in train.columns:\n if ((train[col].dtype==object) & (col!='fullVisitorId')):\n train[col]=train[col].apply(lambda x: np.nan if x==np.nan else str(x).encode('utf-8', 'replace').decode('utf-8'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3e2d5b7e6dff9a4ed5aaeca3d6d069e56932c74c"},"cell_type":"code","source":"#WRITING BACK PREPARED TRAIN AND TEST DATASET\ntrain.to_csv('prepared_train.gz', compression='gzip',index=False)\ntest.to_csv('prepared_test.gz', compression='gzip',index=False)","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}