{"cells":[{"metadata":{"trusted":true,"_uuid":"a8ccba5990ce8b787055f80ffd86f46da766db6f"},"cell_type":"markdown","source":"# A way to input ALL columns(include hits) from v2 data\n* * *\nThanks julian for the amazing kernels, which helped me a lot.     \nThe purpose of this kernel is to share a piece of code when I'm working with v2 data. This code is used to load v2 data (including hits) into pandas.       \n      \nThis code is not perfect, because if you load all the data, you need to use at least 32G of memory, and it takes you an hour. I have been trying to find some optimization methods to break through the GIL. But before that, it is recommended that you save the dataframe to another format and write it to the hard disk after running.\n\n**WARNING**！Loading all train data requires at least 32G of memory, and if you convert it to feather format, it will consume up to 42G of memory. So I highly recommend running this code on the server.\n\nMy native language is not English, so if you want to discuss, maybe my reply will be slower, sorry。^-^"},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"import gc\nimport json\nimport numpy as np\nimport pandas as pd\nfrom pandas.io.json import json_normalize\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9faf2b2294b8e149b01d81688850119ee227e1e9"},"cell_type":"code","source":"import os\nprint(os.listdir(\"../input\"))\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a1865c9fa4ed1fe38583b06a6f526816c78bacfb"},"cell_type":"code","source":"def todict(dic, key, value):\n    if key in dic:\n        dic[key].append(value)\n    else:\n        dic[key] = [value]\n    return dic\n\n\ndef resolve_json(hitsdic, hits_json, key='NoneName'):\n    if type(hits_json) == list:\n        if len(hits_json) == 0:\n            pass\n        else:\n            for subjson in hits_json:\n                hitsdic = resolve_json(hitsdic, subjson)\n    elif type(hits_json) == dict:\n        for i in hits_json.keys():\n            hitsdic = resolve_json(hitsdic, hits_json[i],i)\n    else:\n        hitsdic = todict(hitsdic, key, hits_json)\n    return hitsdic\n\n\ndef complex_replace(x):\n    dic = {}\n    return resolve_json(dic, json.loads(x.replace('\\'','\\\"'). \\\n                                        replace('TRUE','true'). \\\n                                        replace('True','true'). \\\n                                        replace('FALSE','false'). \\\n                                        replace('False','false'). \\\n                                        replace(', \\\"',', !&~'). \\\n                                        replace('\\\", ','!&~, '). \\\n                                        replace('\\\": ','!&~: '). \\\n                                        replace(': \\\"',': !&~'). \\\n                                        replace(' {\\\"',' {!&~'). \\\n                                        replace('\\\"}, ','!&~}, '). \\\n                                        replace('[{\\\"','[{!&~'). \\\n                                        replace('\\\"}]','!&~}]'). \\\n                                        replace('\\\"','_'). \\\n                                        replace('!&~','\\\"'). \\\n                                        encode('gbk','ignore'). \\\n                                        decode('utf-8','ignore'). \\\n                                        replace('\\\\','')))\n\n\ndef replace(x):\n    return  json.loads(x)\n\n\ndef load_df(csv_path, nrows=None, chunksize=10_000, percent=100):\n    n=1\n    df_list = []\n    feature = ['device', 'hits', 'customDimensions', 'geoNetwork', 'totals', 'trafficSource']\n    chunk = pd.read_csv(csv_path,\n                        nrows=nrows, \n                        chunksize=chunksize, \n                        dtype={'fullVisitorId': 'str'}) # Important!!\n    for subchunk in chunk:\n        for column in feature:\n            if column in ['customDimensions','hits']:\n                column_as_df = json_normalize(subchunk[column].apply(complex_replace))\n            else:\n                column_as_df = json_normalize(subchunk[column].apply(replace))\n            column_as_df.columns = [f'{column}_{subcolumn}' for subcolumn in column_as_df.columns]\n            subchunk.drop(column, axis=1, inplace=True)\n            subchunk = subchunk.reset_index(drop=True).merge(column_as_df,\n                                           right_index=True,\n                                           left_index=True)\n        n = n+1\n        df_list.append(subchunk.astype('str'))\n        del column_as_df, subchunk\n    return pd.concat(df_list, ignore_index=True, sort=True)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a4c895f9afd748dfdd0dbe9b2554a1a0ec2807ba"},"cell_type":"code","source":"# If you want to load all the data, change 1_000 to None and change chunksize.\ntrain = load_df('../input/train_v2.csv',nrows=1_000, chunksize=100)\ntrain.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4e7b4a195cc10fbd14ae211b9440235de2cdff26"},"cell_type":"markdown","source":"\noh~,Another important thing, for the complete input hits and customDimensions, I read it as a python list, but the feather format does not recognize python list, in order to convert the dataframe to the feather format, I converted all the data into python string. So for ease of use, it is recommended to use eval() for each column.    \n     \nfinally,I wish you all a good time.(*^_^*)"}],"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}