{"cells":[{"metadata":{"_uuid":"93c439be32a97fb1b17a4661703fc596398a5269"},"cell_type":"markdown","source":"# Tabularize Data\n\nConvert from json to a flattened table.\n\nI wrote this before learning about:\n\n    from pandas.io.json import json_normalize\n    \nbut it works (though slow)\n\nPublished because results needed for next script to be published when I fix some bugs. If you're doing it yourself, you'll probably prefer json_normalize."},{"metadata":{"_uuid":"9459b94284ebfe1903d6e6d2a0ccbf8dd9ae4734"},"cell_type":"markdown","source":"## Imports"},{"metadata":{"trusted":true,"_uuid":"29f990e0a3000964d644fd95cdd9bd35c6fb478d"},"cell_type":"code","source":"import pandas as pd\nx=pd;print(x.__name__, \"version:\", x.__version__)\nimport numpy as np\nx=np;print(x.__name__, \"version:\", x.__version__)\nimport matplotlib\nimport matplotlib.pyplot as plt\nx=matplotlib;print(x.__name__, \"version:\", x.__version__)\nimport seaborn as sns\nx=sns;print(x.__name__, \"version:\", x.__version__)\nfrom sklearn.model_selection import train_test_split\nfrom sklearn import linear_model, kernel_ridge, cluster, model_selection\nimport os, sys, math, datetime, shutil, pickle, itertools, json\nfrom IPython.core.interactiveshell import InteractiveShell","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fa8551aa13559f8f657d4ec77b7c36d5b820eeca"},"cell_type":"markdown","source":"## Settings"},{"metadata":{"trusted":true,"_uuid":"6da747220ac8ac81a295ae21965dc8a604e0e4fb"},"cell_type":"code","source":"InteractiveShell.ast_node_interactivity = \"all\" # this causes all lines of a notebook cell to show output, not just last line\n%matplotlib inline\npd.set_option('display.max_columns', 100) #default of 10 columns is just too small to see much\npd.set_option('display.max_rows', 10)\nplt.style.use('seaborn-poster') #makes the graphs bigger; important for my 4K monitor\nsns.set(palette='deep')\nsns.set(context='poster')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d71cc48679edb80dae94ea2b60e1fefd59b49bcf"},"cell_type":"markdown","source":"## Load the data"},{"metadata":{"trusted":true,"_uuid":"1fa7775f4d6ca2443ec51a96da23b1550ce6a570"},"cell_type":"code","source":"input_prefix = '../input/'\nval_prefix = './val_'\noutput_prefix = './'\n\nid = \"fullVisitorId\"\ndf_train = pd.read_csv(input_prefix + \"train.csv\", index_col=id, dtype={id:str})\ndf_test = pd.read_csv(input_prefix + \"test.csv\", index_col=id, dtype={id:str})","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"22bdc9980f37b017885dbad6779851a5dd87f13f"},"cell_type":"markdown","source":"## Remove trivial (constant) columns"},{"metadata":{"trusted":false,"_uuid":"fd68e665a2c38285d3208c58e53ba4ba15a17bb7"},"cell_type":"code","source":"json_cols = ['device', 'geoNetwork', 'totals', 'trafficSource']#from inspection of data\n\nother_cols = list(set(df_train.columns).union(set(df_test.columns)) - set(json_cols))# all columns except json columns\nfor col in other_cols:\n    if len(set(df_train[col].unique()).union(set(df_test[col].unique()))) <= 1:# if all values are the same value, the column is not needed\n        val = df_train[col].iloc[0]\n        del df_train[col]\n        del df_test[col]\n        print(\"Removing trivial column: %r with unique value: %r\" % (col, val))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cc8226699db947c4c81d114c156e33906215e166"},"cell_type":"markdown","source":"## Split json columns into separate fields\n\nBy inspection, the columns 'device', 'geoNetwork', 'totals', 'trafficSource' of each row are json strings that we need to split into columns.\n\nIt appears not all json records of a column have the same keys, so some random sampling is done.  To do it right, use:\n    from pandas.io.json import json_normalize -- this \"seems\" to get them all (I had done multiple runs with larger samples and put keys found by them in by hand).\n\nRemove any trivial columns that were just added.\n\nAnd repeat till all json data is flattened recursively.\n"},{"metadata":{"trusted":false,"_uuid":"eef675386bba723c7e7efd8993aee40ad4f8a02d"},"cell_type":"code","source":"#just for reproduceability\nnp.random.seed(2718281828)\n\ncolumns = json_cols #start processing with the json_columns known by inspection\nwhile len(columns) > 0: #while there are json columns left to process\n    additional_json_cols = []#if a newly-generated column is itself a json column (from nesting), we need to process it the same way\n    for col in columns:\n        print(\"Getting keys for column:\", col)\n        print(\"  Train column from string to dict:\", col)\n        d = df_train[col].apply(json.loads)#get the dicts of all entries (strings) of this column\n        print(\"  Test column from string to dict:\", col)\n        t = df_test[col].apply(json.loads)\n\n        print(\"  Union of %s train col keys:\" % len(d), col)\n        keys = {x for x in d[0]}# try to find all keys, considering that keys may be missing in some rows\n        if col == 'totals':\n            #make sure we don't miss this one!  It is the target.\n            keys.add('transactionRevenue')\n            #add keys found in previous run\n            keys = keys.union({'newVisits', 'bounces', 'transactionRevenue', 'pageviews', 'visits', 'hits'})\n        elif col == 'device':\n            #add keys found in previous run\n            keys = keys.union({'mobileInputSelector', 'mobileDeviceMarketingName', 'browserVersion', 'mobileDeviceModel', 'flashVersion', 'language', 'screenResolution', 'operatingSystem', 'mobileDeviceBranding', \n                               'browser', 'browserSize', 'isMobile', 'deviceCategory', 'operatingSystemVersion', 'mobileDeviceInfo', 'screenColors'})\n        elif col == 'geoNetwork':\n            #add keys found in previous run\n            keys = keys.union({'cityId', 'longitude', 'networkLocation', 'latitude', 'country', 'metro', 'networkDomain', 'region', 'city', 'continent', 'subContinent'})\n        elif col == 'trafficSource':\n            #add keys found in previous run\n            keys = keys.union({'isTrueDirect', 'medium', 'source', 'adwordsClickInfo', 'campaign', 'adContent', 'keyword', 'referralPath'})\n        elif col == 'trafficSource_adwordsClickInfo':\n            #add keys found in previous run\n            keys = keys.union({'slot', 'gclId', 'targetingCriteria', 'isVideoAd', 'criteriaParameters', 'page', 'adNetworkType'})\n\n        #random samples to make sure we got all the keys\n        samples = np.random.randint(1, len(d) - 1, 1000)#increase this to ensure getting all keys\n        if col == 'totals' or col == 'trafficSource' or col == 'trafficSource_adwordsClickInfo':\n            samples = np.random.randint(1, len(d) - 1, 5000)#increase this to ensure getting all keys\n        count = 0\n        for i in samples:\n            count += 1\n            if count % 20000 == 0:\n                print(\"    rows processed:\", i)\n            if len(keys) != len(d[i]):#may have missing keys to add\n                old_keys = keys\n                keys = keys.union({x for x in d[i]})\n                if len(keys - old_keys) > 0:\n                    print(\"%s new keys added\" % len(keys - old_keys))\n            else:\n                for x in d[i]:\n                    if x not in keys:#definitely have missing keys to add\n                        old_keys = keys\n                        keys = keys.union({x for x in d[i]})\n                        print(\"%s new keys added\" % len(keys - old_keys))\n                        break\n\n        print(\"  Union of %s test col keys:\" % len(t), col)\n        count = 0\n        for i in np.random.randint(1, len(t) - 1, 100):#increase this to ensure getting all keys\n            count += 1\n            if count % 20000 == 0:\n                print(\"    rows processed:\", i)\n            if len(keys) != len(t[i]):\n                old_keys = keys\n                keys = keys.union({x for x in t[i]})\n                if len(keys - old_keys) > 0:\n                    print(\"%s new keys added\" % len(keys - old_keys))\n            else:\n                for x in t[i]:\n                    if x not in keys:\n                        old_keys = keys\n                        keys = keys.union({x for x in t[i]})\n                        print(\"%s new keys added\" % len(keys - old_keys))\n                        break\n\n        print(\"Found %s keys for train/test column %s; replacing with keyed columns; keys = %r\" % (len(keys), col, keys))\n        keylist = list(keys)\n        keylist.sort() #for reproduceability\n        \n        for key in keylist:#now, add new fields of type column_key and delete the original column.  For missing values, use 'XNA' string; this will be replaced with something better on a column-by-column basis in the next script\n            new_field = col + \"_\" + key\n            df_train[new_field] = d.apply(lambda x:x.get(key, 'XNA'))#use 'XNA' string for missing values\n            df_test[new_field] = t.apply(lambda x:x.get(key, 'XNA'))\n\n            #some json structures have extra recursive depth; make value into a string and re-process\n            try:\n                l = len(set(df_train[new_field].unique()).union(set(df_test[new_field].unique())))# l==1 means column is trivial\n            except TypeError: # most likely: found a dict as a value, which \"unique\" doesn't like, so need to recurse\n                def to_str(x):\n                    if isinstance(x, dict):\n                        return json.dumps(x)\n                    else:\n                        return json.dumps(dict())\n                print(\"Recursively processing new column:\", new_field)\n                df_train[new_field] = df_train[new_field].apply(to_str)#convert to string first\n                df_test[new_field] = df_test[new_field].apply(to_str)\n                additional_json_cols.append(new_field)#add to columns that need to be processed again\n                l = len(set(df_train[new_field].unique()).union(set(df_test[new_field].unique())))\n\n            #make sure the column isn't trivial (just one constant value)\n            if l <= 1:\n                val = df_train[new_field].iloc[0]\n                del df_train[new_field]\n                del df_test[new_field]\n                if new_field in additional_json_cols:\n                    additional_json_cols.remove(new_field)\n                print(\"Removing trivial column: %r with unique value: %r\" % (new_field, val))\n        del df_train[col]\n        del df_test[col]\n    columns = additional_json_cols","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7c9042aafe055220da9bd3fb501ad553898f79af"},"cell_type":"markdown","source":"## Check for columns that, looked at as categorical, have the same values with perhaps different labels.\nThis can find false positives, e.g. if you have a column \"value\" and a column \"log_value\" that is the logarithm of the first, they will be found to be equivalent.  So in real life, secondary testing is needed.\n\nBut for this data, it finds none so I skipped the secondary testing."},{"metadata":{"trusted":false,"_uuid":"34639552c3a6c91c4a8af4244ef1af32a189dc1d"},"cell_type":"code","source":"#check for equivalent columns \n\nequivalent_columns = set()\nfor i in range(len(df_train.columns)):\n    for j in range(i):\n        col_i = df_train.columns[i]\n        col_j = df_train.columns[j]\n        if (df_train[col_i].factorize()[0] == df_train[col_j].factorize()[0]).all():\n            found = False\n            for x in equivalent_columns:\n                if col_i in x or col_j in x:\n                    x.add(col_i)\n                    x.add(col_j)\n                    found = True\n                    break\n            if not found:\n                equivalent_columns.add({col_i, col_j})\n\nequivalent_columns#possibly-equivalent columns","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e2f200d430969a2bc7d5ecaf30474e330dee095e"},"cell_type":"markdown","source":"## The cleaned and tabularized data saved here (zipped, makes it save/load faster and use less disk space)\n'XNA' used for nulls, but that is handled in the next script, to be published when some bugs are zapped."},{"metadata":{"trusted":false,"_uuid":"e9889f1c297afca3295888bf97b068bb1215b364"},"cell_type":"code","source":"df_train.to_csv(output_prefix + \"train_clean.csv.zip\", compression='zip')\ndf_test.to_csv(output_prefix + \"test_clean.csv.zip\", compression='zip')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fcaf1782f3f0d2551e06ce7ac4b69cb5fdba50e0"},"cell_type":"markdown","source":"## Create a validation set for local scoring by splitting by visitStartTime"},{"metadata":{"trusted":false,"_uuid":"d0bbed176bf293f8848c0467862b4b04886227ea"},"cell_type":"code","source":"split = int(df_train[['visitStartTime']].describe().loc['75%', 'visitStartTime'])\ndf_valtrain = df_train[df_train['visitStartTime'] < split]\ndf_valtest = df_train[df_train['visitStartTime'] >= split]","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"9a0f9e7165f187126e6ef809307f46c34f8898de"},"cell_type":"code","source":"df_valtrain.to_csv(val_prefix + \"train_clean.csv.zip\", compression='zip')\ndf_valtest.to_csv(val_prefix + \"test_clean.csv.zip\", compression='zip')","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}