{"cells":[{"metadata":{"_uuid":"9d4dd5b9fefceef4b0f1596f4e03579036935d68"},"cell_type":"markdown","source":"# Preliminary data processing"},{"metadata":{"_uuid":"dbe6f9f1df0f0aa7109cfeaf38c83aa4022e0846","trusted":true},"cell_type":"code","source":"import os\nimport json\nimport numpy as np\nimport pandas as pd\nfrom pandas.io.json import json_normalize","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"98eab841bb3d158014b1d2181a51d3303e2a5aa4","trusted":true},"cell_type":"code","source":"def load_df(csv_path='../input/train.csv', nrows=None):\n    JSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\n    print(f\"read from file '{csv_path}'...\")\n    df = pd.read_csv(csv_path,\n                     converters={\n                         column: json.loads for column in JSON_COLUMNS},\n                     dtype={'fullVisitorId': 'str'},  # Important!!\n                     nrows=nrows)\n    print(\"convert columns from json format to plain text...\")\n    for column in JSON_COLUMNS:\n        column_as_df = json_normalize(df[column])\n        column_as_df.columns = [\n            f\"{column}.{subcolumn}\" for subcolumn in column_as_df.columns]\n        df = df.drop(column, axis=1).merge(\n            column_as_df, right_index=True, left_index=True)\n    print(f\"Loaded data from '{os.path.basename(csv_path)}'. Shape: {df.shape}\")\n    return df","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9c3ae9eceeda7a207da0bb5999a0a977054103bc"},"cell_type":"markdown","source":"## Load data"},{"metadata":{"_uuid":"142294dddd4996d47feaa02221d43ac72389f2f5","trusted":true},"cell_type":"code","source":"%%time\ntrain_df = load_df()\ntest_df = load_df(\"../input/test.csv\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e4ba4e2ec8694e8bd2ca1025e66e5d7ab1a40d2d","trusted":true},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d6ee635296285a56505e0f66220195b06f6bb332","trusted":true},"cell_type":"code","source":"test_df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"75c62e2930a6d7e1c1ba7f633520136ea5a5328b"},"cell_type":"markdown","source":"Columns not present in test data:"},{"metadata":{"_uuid":"3cb824e7f1a477f0dccb7ab05748dfcd4d004ea4","trusted":true},"cell_type":"code","source":"set(train_df.columns).difference(set(test_df.columns))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f64dfd70cb9201c2b73f67dc3530ceb7c264486b","trusted":true},"cell_type":"code","source":"train_df.drop('trafficSource.campaignCode', axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a74c8b01beb8452de9dff3b4fa477b5924c87068"},"cell_type":"markdown","source":"## Features with constant values: "},{"metadata":{"_uuid":"8f5dda5e4c6b9842a57f57080f31c9f63b3c2274","trusted":true},"cell_type":"code","source":"nconst_cols_train = [c for c in train_df.columns if train_df[c].nunique(dropna=False) == 1]\nprint(f\"Number of features with constant value in train.csv = {len(nconst_cols_train)}\")\nprint(nconst_cols_train)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1a517f68242669ac3307d78ca6e6cc26413cbdd3","trusted":true},"cell_type":"code","source":"nconst_cols_test = [c for c in test_df.columns if test_df[c].nunique(dropna=False) == 1]\nprint(\n    f\"Number of features with constant value in test.csv = {len(nconst_cols_test)}\")\nprint(nconst_cols_test)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"602a45ae46aa16788db2edec92aff881958b0dad","trusted":true},"cell_type":"code","source":"print(\n    f\"Is two features lists match? {set(nconst_cols_train).intersection(set(nconst_cols_test)) == set(nconst_cols_train)}\"\n)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"039ae66468364e4faf9da7260898bf2e0e11a35d"},"cell_type":"markdown","source":"Remove features with constant values:"},{"metadata":{"_uuid":"2d8fc18ab251c2d3beaf808420e709ed3918c511","trusted":true},"cell_type":"code","source":"train_df.drop(nconst_cols_train, axis=1, inplace=True)\ntest_df.drop(nconst_cols_test, axis=1, inplace=True)\nprint(f\"Train shape: {train_df.shape}\")\nprint(f\"Test shape: {test_df.shape}\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b662bf30884608761f6810838dee0df378cb88d3","trusted":true},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ecbc33d278f460d61d32b5fa11aa77d2893410d5"},"cell_type":"markdown","source":"## Unique values"},{"metadata":{"_uuid":"fffbed2540eb4348bc12425bcbaca0f99543cf21","trusted":true},"cell_type":"code","source":"def unique_table(df: pd.DataFrame, show_result=False) -> pd.DataFrame:\n    ret_val = pd.DataFrame(columns=[\"Column\", \"N\", \"Values\"])\n    for column in df.columns:\n        uniq = df[column].unique()\n        ret_val = ret_val.append({\n            \"Column\": column,\n            \"N\": len(uniq),\n            \"Values\": uniq\n        },\n            ignore_index=True)\n    if show_result:\n        for row in ret_val.values:\n            print(\"=\" * 80)\n            print(f\"Column - '{row[0]}' has {row[1]} unique values.\")\n            print(f\"Unique values in '{row[0]}':\")\n            print(\"-\" * 80)\n            print(row[2])\n    return ret_val","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"840f6b0ff9902a7a2aff9598df82704d7950169e","trusted":true},"cell_type":"code","source":"u_val_train = unique_table(train_df)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ab97e42ff2c8ce9a1c55803642110b4314d69ef1","trusted":true},"cell_type":"code","source":"u_val_test = unique_table(test_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cb2ac6420f0ec24fb328eb304858f4f0bdd79722"},"cell_type":"code","source":"sv_u_val = u_val_train.merge(u_val_test, how=\"left\", on=\"Column\", suffixes=(\"_train\",\"_test\"))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"252e226c643334df8d016d1df7fe403e3e2aabcf"},"cell_type":"code","source":"def diff_values(x):\n    try:\n        return len(set(x[0]).difference(set(x[1])))\n    except:\n        return \"\"\n    \ndef match_values(x):\n    try:\n        return len(set(x[0]).intersection(set(x[1])))\n    except:\n        return \"\"\n                                      ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"514171b38c0e58c606d7c6d0dfb4b8e6102a2c88"},"cell_type":"code","source":"sv_u_val[\"Diff_train\"] = sv_u_val[[\"Values_train\", \"Values_test\"]].apply(diff_values, axis=1)\nsv_u_val[\"Diff_test\"] = sv_u_val[[\"Values_test\", \"Values_train\"]].apply(diff_values, axis=1)\nsv_u_val[\"Matching\"] = sv_u_val[[\"Values_train\", \"Values_test\"]].apply(match_values, axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d083b31e5a7032cdac97fd5d7a6c73b18673e285"},"cell_type":"code","source":"sv_u_val","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"30a2eadb3f922b3cdc83146612723eda76a660b7"},"cell_type":"markdown","source":"Replace for device.isMobile:"},{"metadata":{"_uuid":"7a0340ca68aaf148d0284cf1387186a6626598c2","trusted":true},"cell_type":"code","source":"train_df[\"device.isMobile\"] = train_df[\"device.isMobile\"].astype(\"int\")\ntest_df[\"device.isMobile\"] = test_df[\"device.isMobile\"].astype(\"int\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6b47b80160370e90fc38bde6421808bd570ecdde"},"cell_type":"markdown","source":"## Save data"},{"metadata":{"_uuid":"25f6b089602fc10fabb74c7a968db18d1f101a51","trusted":true},"cell_type":"code","source":"train_df.to_csv(\"GA_train.csv\", index=False)\ntest_df.to_csv(\"GA_test.csv\", index=False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e9deb89643c976d20dd6e9d41c4cf5a00413ad5c"},"cell_type":"markdown","source":"**Commands for loading data:**\n```python\ndf_train = pd.read_csv(\"GA_train.csv\", dtype={'fullVisitorId': str})\ndf_test = pd.read_csv(\"GA_test.csv\", dtype={'fullVisitorId': str})\n```\n*If you want to replace dots in column names with underscores:*\n```python\ndf_train.columns = df_train.columns.str.replace(\".\", \"_\")\ndf_test.columns = df_test.columns.str.replace(\".\", \"_\")\n```"}],"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"},"latex_envs":{"LaTeX_envs_menu_present":true,"autoclose":false,"autocomplete":true,"bibliofile":"biblio.bib","cite_by":"apalike","current_citInitial":1,"eqLabelWithNumbers":true,"eqNumInitial":1,"hotkeys":{"equation":"Ctrl-E","itemize":"Ctrl-I"},"labels_anchors":false,"latex_user_defs":false,"report_style_numbering":false,"user_envs_cfg":false},"nbTranslate":{"displayLangs":["*"],"hotkey":"alt-t","langInMainMenu":true,"sourceLang":"en","targetLang":"fr","useGoogleTranslate":true}},"nbformat":4,"nbformat_minor":1}