`_
\n_NOTE: JSON indicates many more columns will be added once these columns with json fields have been expanded_\n\nField|
Description\n---|:---\n fullVisitorId |
A unique identifier for each user of the Google Merchandise Store\n channelGrouping |
The channel via which the user came to the Store\n date |
The date on which the user visited the Store\n device |
(JSON) The specifications for the device used to access the Store\n geoNetwork |
(JSON) This section contains information about the geography of the user\n sessionId |
A unique identifier for this visit to the store\n socialEngagementType |
Engagement type, either \"Socially Engaged\" or \"Not Socially Engaged\"\n totals |
(JSON) This section contains aggregate values across the session\n trafficSource |
(JSON) This section contains information about the Traffic Source from which the session originated. \n visitId |
An identifier for this session. This is part of the value usually stored as the `_utmb` cookie, uique only to user. For unique visit ID use: fullVisitorId and visitId\n visitNumber |
The session number for this user. If this is the first session, then this is set to 1\n visitStartTime |
The timestamp (expressed as POSIX time)\n \n"},{"metadata":{"_uuid":"0354594b073383ff42a10114c0e237912c0070af"},"cell_type":"markdown","source":"##### Useful websites\nhttps://pandas.pydata.org/pandas-docs/stable/api.html#flat-file"},{"metadata":{"_uuid":"f9011805f665607b26f7e212106fac9f344d6263"},"cell_type":"markdown","source":"## Initialisation\n* import the usual helpful libs\n* initialise path names (using multiple locations)\n* read into newtrain and newtest if available, otherwise train and test"},{"metadata":{"trusted":false,"_uuid":"a858cf0b4d7234d98d4b7a7156c2aab4638569c0"},"cell_type":"code","source":"import os\nimport sys\nimport warnings\nimport numpy as np\nimport pandas as pd \nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport json\nimport re\nimport time\n\nwarnings.filterwarnings('ignore')\n#pd.set_option('display.max_rows', 500)\n#pd.set_option('display.max_colwidth', 90)\n\ndef set_path(paths):\n for path in paths:\n if os.path.exists(path):\n return path\n\nnewdata_dir = \".\"\n#newdata_dir = \"../input/ga-analytics-with-json-columns\"\ndata_dir1 = \"../input/ga-customer-revenue-prediction\"\ndata_dir2 = \"../input\"\nnewtrain_path = newdata_dir+\"/newtrain.csv\"\nnewtest_path = newdata_dir+\"/newtest.csv\"\ntrain_path = set_path([data_dir1+\"/train.csv\", data_dir2+\"/train.csv\"])\ntest_path = set_path([data_dir1+\"/test.csv\", data_dir2+\"/test.csv\"])\n\n!ls -ld $newdata_dir/*.csv $data_dir1/*.csv $data_dir2/*.csv","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"548ae846f5d4d58916827315f94720080e2bedf7"},"cell_type":"code","source":"def load_new_or_orig(newpath, path):\n new = None\n orig = None\n if os.path.exists(newpath):\n new = pd.read_csv(newpath, dtype={'fullVisitorId': 'str', 'visitId': 'str'})\n print (\"loaded\",newpath)\n elif os.path.exists(path):\n orig = pd.read_csv(path, dtype={'fullVisitorId': 'str', 'visitId': 'str'})\n print (\"loaded\",path)\n else:\n print (\"ERROR: loaded nothing\")\n return new, orig\n\nnewtrain, train = load_new_or_orig(newtrain_path, train_path)\nnewtest, test = load_new_or_orig(newtest_path, test_path)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"92986b7497a43a2a8c604868c9b27c6e9f3ca311"},"cell_type":"markdown","source":"## Generic data parsing functions\n* summary: columns for \"describe\" and default (\"basic\") should be self-explanatory\n* is_json: not useful at present since I call get_json with specific fields\n* get_json: apply(json.loads) ensures True/False are ok, but I have to convert strings to NaN explicitly\n
*NOTE: normalize will automatically quote json fields that contain a , (comma) so it's ok to convert the result back to a csv*\n* expand_json: add new columns using flattened nested json columns, drop duplicates and return a new df"},{"metadata":{"trusted":false,"_uuid":"6c72a63c845ec7752cbb553b473172ac7309efe3"},"cell_type":"code","source":"def summary(df, info=\"describe\"):\n if info == \"describe\":\n headings=['Null','Unique','dType','Type','MinMax','Mean','Std','Skew','Examples']\n else:\n headings=['Null','Unique','dType','Type','Examples']\n \n print('DataFrame shape',df.shape)\n sdf = pd.DataFrame(index=df.columns, columns=headings)\n for col in df.columns:\n sdf['Null'][col] = df[col].isna().sum()\n sdf['Unique'][col] = df[col].astype(str).unique().size\n sdf['dType'][col] = df[col].dtype\n sdf['Type'][col] = \"-\" if df[col].notna().sum() == 0 else type(df[col].dropna().iloc[0])\n sdf['Examples'][col] = \"-\" if df[col].notna().sum() == 0 else df[col].astype(str).unique() #.dropna().values\n if info == \"describe\":\n if 'float' in str(df[col].dtype) or 'int' in str(df[col].dtype):\n sdf['MinMax'][col] = str(round(df[col].min(),2))+'/'+str(round(df[col].max(),2))\n sdf['Mean'][col] = df[col].mean()\n sdf['Std'][col] = df[col].std()\n sdf['Skew'][col] = df[col].skew()\n return sdf.fillna('-')\n\n\ndef is_json(j):\n if re.match(r'^{\\\"', j):\n return True\n else:\n return False\n\ndef get_json(df, col):\n if is_json(df[col][0]) == False:\n return None\n jdf_lines = df[col].apply(json.loads) # do normalize separately or it will use just one column\n jdf = pd.io.json.json_normalize(jdf_lines).add_prefix(col+'.')\n for jcol in jdf.columns:\n jdf[jcol].replace('not available in demo dataset', np.nan, inplace=True, regex=True)\n jdf[jcol].replace('(not provided)', np.nan, inplace=True, regex=True)\n jdf[jcol].replace('(not set)', np.nan, inplace=True, regex=True)\n return jdf\n\ndef expand_json(df):\n newdf = pd.concat([df, get_json(df, 'device'),\n get_json(df, 'geoNetwork'),\n get_json(df, 'totals'),\n get_json(df, 'trafficSource')], axis=1, sort=False)\n newdf.drop(columns=['device', 'geoNetwork', 'totals', 'trafficSource'], inplace=True)\n return newdf","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"b4800af598e296f4ee6ef2ee35903064617ea144"},"cell_type":"code","source":"# Adhoc validation\n#get_json(train, 'trafficSource')\n#get_json(train, 'fullVisitorId')\n#summary(train, info=\"basic\")\n#summary(get_json(train, 'device'), info=\"basic\")\n#summary(get_json(train, 'geoNetwork'), info=\"basic\")\n#summary(get_json(train, 'totals'), info=\"basic\")\n#summary(get_json(train, 'trafficSource'), info=\"basic\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"71bab7dc0e8c6b434e33fce39b6dbd042d597c05"},"cell_type":"markdown","source":"## Create wider data frames using nested json entries\n* create newtrain/newtest and then save them as csvs; they will be smaller since we have removed duplicate json keys"},{"metadata":{"trusted":false,"_uuid":"442b442f085533a2413e1ebb2118773434e5d047"},"cell_type":"code","source":"def expand_json_to_df(newdf, df):\n newname = sys._getframe(1).f_code.co_names[1] # [0] is the function name\n if newdf is None:\n print (time.ctime(), newname, \"will contain expanded json entries\")\n newdf = expand_json(df)\n print (time.ctime(), newname, \"finished: shape =\", newdf.shape, \"vs original shape =\", df.shape)\n else:\n print (time.ctime(), newname, \"already loaded\")\n return newdf\n\nnewtrain = expand_json_to_df(newtrain, train)\nnewtest = expand_json_to_df(newtest, test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"7932750188f3d1c8335de1f10b5dbe33f990ab41"},"cell_type":"code","source":"summary(newtrain, info=\"basic\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"4beb49b1fcd573871c799fa7ac8db2e81a3f0033"},"cell_type":"code","source":"summary(newtest, info=\"basic\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"28b8a403dc26cd8c361c874a8123389b236e269f"},"cell_type":"markdown","source":"## Rewrite and Reload\n* if the directory is writable, write newtrain and newtest if they don't already exist\n* reload back into newtrain and newtest which allows pandas.read_csv to assign more appropriate types (see explanation in heading)\n
*NOTE: python3's default ecoding is \"utf-8\", but explicitly set it so we know what we're getting*"},{"metadata":{"trusted":false,"_uuid":"8266fd207ba8754a675454e2d3941b364c25ca6a"},"cell_type":"code","source":"# newtrain.csv is only 250MB (from 1.5GB) because we've removed the json repetition\n# newtest.csv is only 230MB (from 1.3GB) because we've removed the json repetition\n\nif os.access(newdata_dir, os.W_OK):\n if not os.path.exists(newtrain_path):\n newtrain.to_csv(newtrain_path, index=False, encoding=\"utf-8\")\n print (\"wrote\", newtrain_path)\n newtrain = pd.read_csv(newtrain_path)\n print (\"reloaded newtrain\")\n if not os.path.exists(newtest_path):\n newtest.to_csv(newtest_path, index=False, encoding=\"utf-8\")\n print (\"wrote\", newtest_path)\n newtest = pd.read_csv(newtest_path)\n print (\"reloaded newtest\")\nelse:\n print (newdata_dir, \"is not writable\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"85be45ec7801813f7e17fb23c8ee5b715c88b801"},"cell_type":"code","source":"!echo Quick validation of rows:\n!sed -n '$=' $newtrain_path\n!sed -n '$=' $train_path\n!sed -n '$=' $newtest_path\n!sed -n '$=' $test_path","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"de74156323dc65063128fef1b7641d62c450cf53"},"cell_type":"markdown","source":"## Simplifying newtrain and newtest\n* start with the obvious: if a column has no data for newtrain OR newtest, it is useless to both (we'll drop these)\n* there two columns not common to both: **totals.transactionRevenue** (we need); **trafficSource.campaignCode** (we'll just ignore this)"},{"metadata":{"trusted":false,"_uuid":"da5b0192d8c213322bc92c6126eae44ff8998998"},"cell_type":"code","source":"def get_unused(df):\n rows = df.shape[0]\n ddf = df.isna().sum()\n return list(ddf[ddf >= rows].index)\n \ndroplist = get_unused(newtrain)\nfor dropcol in get_unused(newtest):\n if dropcol not in droplist:\n droplist.append(dropcol)\n\nprint (\"dropping these columns from both newtrain and newtest:\")\nprint (droplist)\nnewtrain.drop(columns=droplist, inplace=True)\nnewtest.drop(columns=droplist, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"e057a128f7f1dd7ca8d5e1fb8d420c99b3f65036"},"cell_type":"code","source":"summary(newtrain, info=\"basic\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"7f50ef309c6d795150a2af7c5127e2595c11d0ef"},"cell_type":"code","source":"summary(newtest, info=\"basic\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9ca3ad2bad0ca78718bd089b04ab4bc833ca5603"},"cell_type":"markdown","source":"## EDA of newtrain data\n* **totals.transactionRevenue**: NaN vs >0 (imbalance is obvious without a graph)"},{"metadata":{"trusted":false,"_uuid":"3a8c64aac41bfe2de4f1a55e4dfb7faea60910b9"},"cell_type":"code","source":"zrows = newtrain['totals.transactionRevenue'].size\nnrows = newtrain['totals.transactionRevenue'].dropna().size\nprint (\"NaN =\", zrows, \"; >0 =\", nrows)\n#plt.bar([1,2], [zrows, nrows])\n#plt.ylabel('Rows', fontsize=15)\n#plt.xticks([1,2], [\"NaN\", \">0\"], fontsize=15, rotation=0)\n#plt.title(\"totals.transactionRevenue\", fontsize=15);","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0fa167f1e0bd423c6f55c52a7012d3641506de12"},"cell_type":"markdown","source":"## Pie chart functions\n* col_by_col_count: eg. (df, OS, session) gets session instances counted per OS (so total OS usage)\n* col_by_col_count: eg. (df, OS, revenue) gets revenue instances counted per OS\n* col_by_col_sum: eg. (df, OS, revenue) gets revenue total per OS\n* myautopct: pie is messy with 0.0 and 0.1 percent markings, so set a threshold\n* mypie: common options"},{"metadata":{"trusted":false,"_uuid":"6cb96e076fbe41b494fcaea8433729289023035a"},"cell_type":"code","source":"def col_by_col_count(df, col1, col2, threshold=0):\n return df.groupby([col1]).count()[col2].apply(lambda x: (x if x>threshold else np.nan)).dropna()\n\ndef col_by_col_sum(df, col1, col2, threshold=0):\n return df.groupby([col1]).sum(numeric_only=True)[col2].apply(lambda x: (x if x>threshold else np.nan)).dropna()\n\ndef myautopct(pct):\n return ('%.2f' % pct) if pct > 2 else ''\n\ndef mypie(df, title, angle=0):\n # autopct='%1.1f%%'\n # textprops={'size': 'small'} (Kaggle python (3.6.6) + libs didn't recognise this)\n df.plot(kind='pie', figsize=(5, 5), radius=1.2, startangle=angle, autopct=myautopct, pctdistance=0.8,\n rotatelabels=False, legend=True, explode=[0.02]*df.size);\n plt.title(title, weight='bold', size=14, x=2.0, y=-0.01);\n plt.xlabel('')\n plt.ylabel('')\n plt.legend(bbox_to_anchor=(2.5, 1.0), ncol=2, fontsize=10, fancybox=True, shadow=True);","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"114177a16652c57614c7b0f278e5aa6e3866b01b"},"cell_type":"markdown","source":"## Pie deductions\n* OSs: 19 in total, and we've removed negligable sales on Nintendo etc.\n* OS split: Windows the clear winner, no suprises there. 10 OSs below the threshold and so not represented here.\n* OS by transaction instance: people buy more on a Mac it seems\n* OS by transaction sum: more or less the above, except people spend bigger amounts when using Chrome OS and Windows"},{"metadata":{"trusted":false,"_uuid":"c36c5e44e3b296a3160c6c744c257d0c62fc0123"},"cell_type":"code","source":"newtrain['device.operatingSystem'].astype(str).unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"b2f494047bfd8f92b2c394605fe1bbb7dbed3020"},"cell_type":"code","source":"df = col_by_col_count(newtrain, 'device.operatingSystem', 'sessionId', threshold=100)\nmypie(df, 'OS prevalence', angle=100)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"ec46f9f046a7b43471948f8dd9dbd21068f48581"},"cell_type":"code","source":"df = col_by_col_count(newtrain, 'device.operatingSystem', 'totals.transactionRevenue', threshold=100)\nmypie(df, 'OS prevalence by revenue instances', angle=0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"b5f62004b6df65ac54c7349ddd99c96bb12b46bb"},"cell_type":"code","source":"df = col_by_col_sum(newtrain, 'device.operatingSystem', 'totals.transactionRevenue', threshold=100)\nmypie(df, 'OS prevalence by revenue sum', angle=10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false,"_uuid":"67dc4ba92b57f4d64bff9e03aeede7c76d100028"},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.7.0"}},"nbformat":4,"nbformat_minor":1}