{"cells":[{"metadata":{"_uuid":"8a581a38ad934b458d12dc8a3ad1458e975216b9"},"cell_type":"markdown","source":"There many great kernels about flattening the JSON blobs, but we don't want to do this everytime we load the data. "},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np \nimport pandas as pd \nimport os\nfrom pandas.io.json import json_normalize,loads\nimport time\n\ndef load_json(d):\n    return loads(d)\n\ndef json_into_dataframe(dataframe,column):\n    return json_normalize(dataframe[column].apply(load_json).tolist()).add_prefix(column +'.')\n        \ndef open_flat(filepath,columns):\n    counter = 0 \n    data= pd.read_csv(filepath, low_memory = False)\n    for column in columns :\n        print ('Unpacking ' + column)\n        temp = json_into_dataframe(dataframe = data, column = column)\n        for item in temp.columns:\n            if len(temp[item].unique()) == 1: # if a column has the same value for all rows is not significant\n                temp.drop(item, axis = 1, inplace = True)\n                print ('column '+ item + ' was dropped')\n                counter += 1\n        data = pd.concat([data,temp], axis = 1)\n        data.drop([column],inplace = True, axis = 1)\n    print ('Columns dropped :',counter)\n    return data\ntrain_path = \"../input/train.csv\"\ntest_path = \"../input/test.csv\"\ncolumns = ['totals','device','geoNetwork','trafficSource']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8158b6fbc923c9db2fde17aecc2099e397ccd62b"},"cell_type":"code","source":"\nt0 = time.time()\ntrain = open_flat(filepath = train_path ,columns = columns)\nt1 = time.time()\nprint ('time to load train set', t1-t0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a667adcce483d0cc5a9c5fe3b0a3f584049e16b8","scrolled":true},"cell_type":"code","source":"t0 = time.time()\ntest = open_flat(filepath = test_path ,columns = columns)\nt1 = time.time()\nprint ('time to load test set ', t1-t0)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d08b41d690283b9d007fca4792d882c2539a412a"},"cell_type":"markdown","source":"Let's have a look at the shape of the resulting train and test set"},{"metadata":{"trusted":true,"_uuid":"b4cc029acf950c7e07ea6d8419e8c1377cc66049"},"cell_type":"code","source":"print(train.shape)\nprint(test.shape)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cefe72ddae265bc79a1500be41a7aba765edd55f"},"cell_type":"markdown","source":"It seems there is a column in the train set ( other than the target feature totals.transactionRevenue ) , that it's not in the test set and we have to get rid of it."},{"metadata":{"trusted":true,"_uuid":"d9c95e06db7dad42bd1452d96c8b88cf7feda4a6"},"cell_type":"code","source":"for column in train.columns:\n    if column not in test.columns:\n        print(column)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3d7efa4a2a5528975d009da5eff9f7d281d026e9"},"cell_type":"code","source":"train.drop('trafficSource.campaignCode' , axis = 1 , inplace = True)\nprint(train.shape)\nprint(test.shape)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1e9f3891d485d86f3d4cca6aac047fbe9cbb4715"},"cell_type":"markdown","source":"Now we can save our DataFrames into new excel files to save some space"},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true,"scrolled":true},"cell_type":"code","source":"t0 = time.time()\nwriter = pd.ExcelWriter('train.xlsx')\ntrain.to_excel(writer,'Sheet1')\nwriter.save()\nt1 = time.time()\nprint ('time to save train dataset to excel ', (t1-t0)/60.0 , ' mins') ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3a2f6b85991e27392acd8cea5e1da0a24d69b031"},"cell_type":"code","source":"t0 = time.time()\nwriter = pd.ExcelWriter('test.xlsx')\ntest.to_excel(writer,'Sheet1')\nwriter.save()\nt1 = time.time()\nprint ('time to save test dataset to excel ', (t1-t0)/60.0 , ' mins') ","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ed430accf04d5f7fdd085d71d3301555e5a0b69d"},"cell_type":"markdown","source":"Let's see how much space we saved!"},{"metadata":{"trusted":true,"_uuid":"5d70e0790da9a6ac8545c3a2d74b484e7b477349"},"cell_type":"code","source":"print ('File size of flat train set :' + str(((os.path.getsize(\"train.xlsx\")/1024)/1024)) + ' MB')\nprint ('File size of original train set :' + str(((os.path.getsize(\"../input/train.csv\")/1024)/1024)) + ' MB')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4c65889194456598679e03e55e2a59e31e191083"},"cell_type":"code","source":"print ('File size of flat test set :' + str(((os.path.getsize(\"test.xlsx\")/1024)/1024)) + ' MB')\nprint ('File size of original test set :' + str(((os.path.getsize(\"../input/test.csv\")/1024)/1024)) + ' MB')","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}