{"cells":[{"metadata":{"_uuid":"8601bdf7aa0c55cf64e7db1737a09b9313fb9790"},"cell_type":"markdown","source":"In this notebook, we will explore"},{"metadata":{"_uuid":"2cd8479a4e435f7b67d350d26dad1a97f60d9c00"},"cell_type":"markdown","source":"1) [Explore the raw data](#Explore+Data)  \n2) [Reading the data from CSV into Pandas](#Read+CSV+Into+Dataframe)  \n3) [How to interpret JSON format and convert it into data frame](#Working+on+JSON+data)  \n4) [Reading JSON into data frames](#Load+data+as+JSON)  \n5) [Putting learning into action by creating function that reads CSV data and flattens JSON](#Putting+Together)  \n6) [Reduce the size of the data without losing data significance](#Reduce+Data+Size)  \n7) [Save data](#Save+Data)"},{"metadata":{"_uuid":"52281126662c6b14b53ff88040af3f0497c74668"},"cell_type":"markdown","source":"## Explore+Data\n### 1. Lets look at the size of each file"},{"metadata":{"trusted":true,"_uuid":"3be974a20a67ff645219de4cde36051ac4310118"},"cell_type":"code","source":"import os\nidir = \"../input/\"\nfor x in os.listdir(idir):\n        f = idir + x\n        s = os.stat(f)\n        num_lines = sum(1 for line in open(f))\n        print(x + \":\" + str(round(s.st_size / (1024 * 1024)) ) + \" MB : \" + str(num_lines) + \" lines\")\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"547fb7da8ba35b1a179229b933a2f79a24368b47"},"cell_type":"markdown","source":"### 2. Lets review  first 5 lines of the file"},{"metadata":{"trusted":true,"_uuid":"8af62a5e2ea06f53cb86ea3dab77734f3b2af4a2"},"cell_type":"code","source":"rows = 0\nfor line in open(f): \n    print(line)\n    rows += 1\n    if rows > 4:\n        break","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","collapsed":true,"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":false},"cell_type":"markdown","source":"## Read+CSV+Into+Dataframe"},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"144328ecf63f9b4e612183a4118163ad22fcfc7e"},"cell_type":"code","source":"import numpy as np \nimport pandas as pd \nifile = idir + \"train.csv\"\nnum_rows = 3\ndf = pd.read_csv(ifile, nrows = num_rows, converters={'fullVisitorId': str})\ndf.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"afd9a005e9e19029796630beeb7a2577eefb8ad1"},"cell_type":"code","source":"df.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ae54e359c4138ff18cf0d4116c0ac4c09c8a08ea"},"cell_type":"markdown","source":"## Working+on+JSON+data\n### 1. Convert strings into JSON data  \nIn the above data frame, columns with JSON data are treated as strings.  We need to convert them to JSON.<br />\nBelow code converts JSON String to JSON object"},{"metadata":{"trusted":true,"_uuid":"b3b4836142f9de3830607b05a7d18a88fe2a3738"},"cell_type":"code","source":"import json\nj = json.loads(df[\"totals\"][0])\nj","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"49105812a3ce15d57f3f8a0b56d3f981458457a5"},"cell_type":"markdown","source":"### 2. Parse json into data frame  \nThe below code will help convert json object into a dataframe."},{"metadata":{"trusted":true,"_uuid":"35b6142e981a52e152a13fc05e8d7b32de2ac9c6"},"cell_type":"code","source":"from pandas.io.json import json_normalize\njson_normalize(j)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fa67f53d78a136e1af3bb3f1c8d6bc2fb0387a12"},"cell_type":"markdown","source":"## Load+data+as+JSON\nWith the above snippets, lets load the CSV file with JSON columns in JSON format\n\n### 1. Read the dataframe with JSON converters\n"},{"metadata":{"trusted":true,"_uuid":"149a6dff537602c3de6d30b338ea4e1fb9aea758","scrolled":true},"cell_type":"code","source":"json_cols = ['device', 'geoNetwork', 'totals', 'trafficSource'] # List columns where data is stored in JSON format\n\n# Apply converter to convert JSON format data into JSON object\njson_conv = {col: json.loads for col in (json_cols)}\n\n# Read the CSV with the new converter\ndf = pd.read_csv(idir + \"train.csv\", \n    dtype={'fullVisitorId': str},\n    converters=json_conv, \n    nrows=num_rows)\n\ndf.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c12e64c6cf681b56098397b4acf08da574e2dac5"},"cell_type":"markdown","source":"### 2. Convert the json data into columns  \nThe below code takes the json code and converts it into columns"},{"metadata":{"trusted":true,"_uuid":"a1ad7ea647adf7bc79a024f005d9a936688d5fa1"},"cell_type":"code","source":"tdf = json_normalize(df[\"totals\"])\ntdf.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"23d573beea3f7405659c179a608b62e1a0bc86a7"},"cell_type":"markdown","source":"### 3. Drop original column\nWe dont need the original json column and dropping it will reduce RAM"},{"metadata":{"trusted":true,"_uuid":"24e66bfa948759a9da05a20006a2650a0044c32b"},"cell_type":"code","source":"df = df.drop(columns = [\"totals\"])\ndf.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a5f203d6341fff0a6445f8aef934dac61bfec650"},"cell_type":"markdown","source":"We can see the data frame is created with all possible columns.  NaN value in  columns where this column doesnt exist in json "},{"metadata":{"_uuid":"ed483781b37fa993eaa9e4df9914c680661e7786"},"cell_type":"markdown","source":"### 4. Adding the columns into original df  \nWe now want to add these  columns into the original dataframe.  Lets create a column name by appending the parent column name\n"},{"metadata":{"_kg_hide-input":false,"_kg_hide-output":false,"trusted":true,"_uuid":"99fbd13a57cd0f47063a8a8f70e1920fe21efcc8"},"cell_type":"code","source":"tdf.columns = [\"totals_\" + col for col in tdf.columns]\ndf = df.merge(tdf, left_index=True, right_index=True)\ndf.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"69981ad8fb7a9240ca5b2778f3ef8476b25a6237"},"cell_type":"markdown","source":"## Putting+Together"},{"metadata":{"trusted":true,"_uuid":"af2e8685ab0e2f9735f8e6a3a1665558c602bfbc"},"cell_type":"code","source":"def ld_dn_df(csv_file, json_cols, rows_to_load = 100 ): \n\n    # Apply converter to convert JSON format data into JSON object\n    json_conv = {col: json.loads for col in (json_cols)}\n\n    # Read the CSV with the new converter\n    df = pd.read_csv(csv_file, \n        dtype={'fullVisitorId': str},\n        converters=json_conv, \n        nrows=rows_to_load, \n        low_memory = False\n        )\n    \n    for jcol in json_cols: \n        tdf = json_normalize(df[jcol])\n        tdf.columns = [jcol + \"_\" + col for col in tdf.columns]\n        df = df.merge(tdf, left_index=True, right_index=True)\n        \n    df = df.drop(columns = json_cols)\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"388b60b1bb5b646adbd554dd1cb9c38dce93c8ad"},"cell_type":"code","source":"%%time\nrows_to_load = 1000000\njson_cols = [\"totals\", \"device\", \"geoNetwork\", \"trafficSource\"]\ntrain_df =  ld_dn_df(\"../input/train.csv\", json_cols, rows_to_load)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ea94df969775e8dfe8b9006876693070c6ea9a8f"},"cell_type":"code","source":"%%time\ntest_df =  ld_dn_df(\"../input/test.csv\", json_cols, rows_to_load)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ada1376de368f40b15a6db1cc11b256f5e5d177c"},"cell_type":"markdown","source":"## Reduce+Data+Size\nWe can take the below steps: <br />\n1) Reduce the long default values to simple strings<br />\n2) Download 64 bit numbers to 32 bit where possible<br />\n3) Create categorical variables from strings<br />\nThe below code addresses 1st step"},{"metadata":{"trusted":true,"_uuid":"529347e2111859fef041569fce9e3bd5b5f41622"},"cell_type":"code","source":"def replace_def_vals(df):\n    df.replace({'(not set)': np.nan,\n               'not available in demo dataset': np.nan,\n               '(not provided)': np.nan,\n               'unknown.unknown': np.nan,\n               '(none)':np.nan,\n               '/':np.nan,\n               'Not Socially Engaged':np.nan},\n              inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3bde1c9709bb1045993dc7b4dfb325490401abb2"},"cell_type":"code","source":"%%time\nreplace_def_vals(train_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"abf76c9f6c5c8bc86e77ac45aee8bb4af4aac9a2"},"cell_type":"code","source":"%%time\nreplace_def_vals(test_df)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"095d2a98f4d9a366fb79c3985e90660e9a1b9158"},"cell_type":"markdown","source":"## Save+Data\nThe below code saves the files and checks the number of records and the size."},{"metadata":{"trusted":true,"_uuid":"ac46f7a1ca04fac4ecc82f681de6e036d63e97ea"},"cell_type":"code","source":"f = './train_flat.csv'\ntrain_df.to_csv(f, index = False)\ns = os.stat(f)\nnum_lines = sum(1 for line in open(f))\nprint(x + \":\" + str(round(s.st_size / (1024 * 1024)) ) + \" MB : \" + str(num_lines) + \" lines\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"231b8dc2e6ec0f3c57564045775b36ae0c4ce99b"},"cell_type":"code","source":"f = './test_flat.csv'\ntest_df.to_csv(f, index = False)\ns = os.stat(f)\nnum_lines = sum(1 for line in open(f))\nprint(x + \":\" + str(round(s.st_size / (1024 * 1024)) ) + \" MB : \" + str(num_lines) + \" lines\")\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"759124fbd9e2f1e9ab2674136e1d632b311209b3"},"cell_type":"markdown","source":"### We reduced the size of the data to ~15% of original size!!!  \nTrain CSV has gone down from **1434 MB** to **213 MB**"},{"metadata":{"_uuid":"d1403738c3d39fdbc82f22b4a1d16d87f37895db"},"cell_type":"markdown","source":"<b>References</b><br /><br />\n\nhttps://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields<br />\nhttp://pandas.pydata.org/pandas-docs/stable/merging.html<br />\nhttps://stackoverflow.com/questions/13293810/import-pandas-dataframe-column-as-string-not-int <br />\nhttps://docs.python.org/2/library/json.html <br />\nhttps://stackoverflow.com/questions/845058/how-to-get-line-count-cheaply-in-python <br />\nhttps://pandas.pydata.org/pandas-docs/stable/generated/pandas.io.json.json_normalize.html <br />\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"}},"nbformat":4,"nbformat_minor":1}