{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"markdown","source":"This notebook loads train_v2.csv/test_v2.csv file and flatten the json fields. "},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"import pandas as pd \nimport numpy as np \nimport json \nfrom ast import literal_eval","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4a61452380781d24ef75a339e3125e5222386096"},"cell_type":"code","source":"df = pd.read_csv('../input/train_v2.csv', nrows=10000)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"40025f6231d9d49dfeaaf07e8a7f29884fe22901"},"cell_type":"code","source":"df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d60dfff02ec7af417e1f5e4aba4485e06d0735bf"},"cell_type":"markdown","source":"We have 6 special columns which should be flattened. `device`, `geoNetwork`, `totals`,`trafficSource` is standard json fields and can't be easily flattened by json module; `customDimensions` and `hit` columns can not be processed by json module, bu can be processed by ast.literal_eval. "},{"metadata":{"_uuid":"dc18b6850e760b11d8403b26e3d822eb95324ab1"},"cell_type":"markdown","source":"## device, geoNetwork, totals and trafficSource"},{"metadata":{"trusted":true,"_uuid":"f112cc5048f30b64f747070c41ca76e13f1b7a5f"},"cell_type":"code","source":"json_cols = ['device', 'geoNetwork', 'totals', 'trafficSource']\n\ndef parse_json_col(raw_str):\n    return pd.Series(json.loads(raw_str))\n\nfor col in json_cols:\n    parsed_df = df[col].apply(parse_json_col)\n    parsed_df.columns = [f'{col}_{x}' for x in parsed_df.columns]\n    df = pd.concat([df, parsed_df], axis=1)\n    df.drop(col, axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0bfb5fe5c204649aa60da8f5dce10a78f8cc10cc"},"cell_type":"code","source":"df.shape","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a7209acac5715210c9867cdef510286691bf1142"},"cell_type":"markdown","source":"Let's check the new columns. We found that `trafficSource_adwordsClickInfo` is also a json column which should be flattened."},{"metadata":{"trusted":true,"_uuid":"9ec2db5f5e418c21f163687f850f6e19c5349f46"},"cell_type":"code","source":"df.filter(regex='.*_.*', axis=1).head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a28c27d881cdf53c830dcd6dc05e5d080117499c"},"cell_type":"code","source":"trafficSource_adwordsClickInfo_df = df.trafficSource_adwordsClickInfo.apply(pd.Series)\ntrafficSource_adwordsClickInfo_df.columns = [f'trafficSource_adwordsClickInfo_{x}' for x in trafficSource_adwordsClickInfo_df.columns]\ndf = pd.concat([df, trafficSource_adwordsClickInfo_df], axis=1)\ndf.drop('trafficSource_adwordsClickInfo', axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2a1a16aa642e15abf1b0e91d5baef947d99bee27"},"cell_type":"code","source":"df.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d8289b52af00d37e4414c82b0b11a4b6438f74b7"},"cell_type":"code","source":"df.filter(regex='trafficSource_adwordsClickInfo_.*', axis=1).head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f57750762c792deb8517f001e8dbb2b1a471be78"},"cell_type":"markdown","source":"## customDimensions"},{"metadata":{"trusted":true,"_uuid":"8bcea224022516feecfae9274b2fe4e8d7f18479"},"cell_type":"code","source":"# for customDimensions and hits columns\ndef parse_special_col(raw_str):\n    lst = literal_eval(raw_str)\n    if isinstance(lst, list) and lst:\n        return pd.Series(lst[0])\n    else:\n        return pd.Series({})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7703c23fbd9b3697e2ad30b5b904d4889989e749"},"cell_type":"code","source":"customDimensions_df = df.customDimensions.apply(parse_special_col)\ncustomDimensions_df.columns = [f'customDimensions_{x}' for x in customDimensions_df.columns]\ndf = pd.concat([df, customDimensions_df], axis=1)\ndf.drop('customDimensions', axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"567dfdeb0598ebf0aaf100f31e38a93266b85a8c"},"cell_type":"code","source":"df.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"37602f2e1e4ae5e97b70b4d31c0a6173491d3755"},"cell_type":"code","source":"df.filter(regex='customDimensions_.*', axis=1).head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3c875bb50da2678f05c280f8f6fe72c52bc5dcc6"},"cell_type":"markdown","source":"## hits"},{"metadata":{"trusted":true,"_uuid":"cd44dffda5b6c4581fe106383c7742dda31cff80"},"cell_type":"code","source":"hits_df = df.hits.apply(parse_special_col)\nhits_df.columns = [f'hits_{x}' for x in hits_df.columns]\ndf = pd.concat([df, hits_df], axis=1)\ndf.drop('hits', axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7dd2a31bb36db554682823c6672531182e15f365"},"cell_type":"code","source":"df.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"274d0b3d43b0bba1b230cccce985900cbd78a6c6"},"cell_type":"code","source":"df.filter(regex='hits_.*', axis=1).head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"aee82ecd24cc09db0989cdc9b128303c33019e15"},"cell_type":"markdown","source":"`hits_experiment`, `hits_customVariables`, `hits_customMetrics`, `hits_publisher_infos`, `hits_customDimensions` are empty, we can drop it."},{"metadata":{"trusted":true,"_uuid":"72c8e4938d2c8e9c41cd3510ac4ac88b8dd448cd"},"cell_type":"code","source":"df.drop(['hits_experiment', 'hits_customVariables', 'hits_customMetrics', 'hits_publisher_infos', 'hits_customDimensions'], axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c06e9a024380c12e88d898c28a161b5da01c3e61"},"cell_type":"markdown","source":"`hits_page`, `hits_transaction`, `hits_item`, `hits_appInfo`, `hits_exceptionInfo`, `hits_eCommerceAction`, `hits_social`, `hits_contentGroup`, `hits_promotionActionInfo` are python dict, we can should flatten it."},{"metadata":{"trusted":true,"_uuid":"f14430890c9719659d1cb406999571b98a09b35c"},"cell_type":"code","source":"dict_cols = ['hits_page', 'hits_transaction', 'hits_item', 'hits_appInfo', \n        'hits_exceptionInfo', 'hits_eCommerceAction', 'hits_social', 'hits_contentGroup', 'hits_promotionActionInfo']\nfor col in dict_cols:\n    parsed_df = hits_df[col].apply(pd.Series)\n    parsed_df.columns = [f'{col}_{x}' for x in parsed_df.columns]\n    df = pd.concat([df, parsed_df], axis=1)\n    df.drop(col, axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"959d1b3f7bca9bbae94de2a9419cf40f172d9024"},"cell_type":"markdown","source":"`hits_product`, `hits_promotion` are python list, we should flatten it."},{"metadata":{"trusted":true,"_uuid":"fd1b7a2ea237cb7f5f898d8f1e0dba8b1a42a87c"},"cell_type":"code","source":"def parse_list(x):\n    if isinstance(x, list) and x:\n        return pd.Series(x[0])\n    else:\n        return pd.Series({})\n    \nfor col in ['hits_product', 'hits_promotion']:\n    parsed_df = hits_df[col].apply(parse_list)\n    parsed_df.columns = [f'{col}_{x}' for x in parsed_df.columns]\n    df = pd.concat([df, parsed_df], axis=1)\n    df.drop(col, axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7d0410e59b9e34564946f1858a5f12bee757ed88"},"cell_type":"code","source":"df.shape","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"39d300e4c637971a87abc369ca29e94928fdccf2"},"cell_type":"markdown","source":"## Pack it to a function\nI have put the code in one function so you can copy it easily."},{"metadata":{"trusted":true,"_uuid":"aea1c5b21587b3033e2ce42fa00feca4cadbc7c9"},"cell_type":"code","source":"def flatten(in_csv, out_csv, nrows=None):\n    df = pd.read_csv(in_csv, dtype=np.object, nrows=nrows)\n    # json columns\n    json_cols = ['device', 'geoNetwork', 'totals', 'trafficSource']\n\n    def parse_json_col(raw_str):\n        return pd.Series(json.loads(raw_str))\n    \n    for col in json_cols:\n        parsed_df = df[col].apply(parse_json_col)\n        parsed_df.columns = [f'{col}_{x}' for x in parsed_df.columns]\n        df = pd.concat([df, parsed_df], axis=1)\n        df.drop(col, axis=1, inplace=True)\n    \n    # trafficSource_adwordsClickInfo\n    trafficSource_adwordsClickInfo_df = df.trafficSource_adwordsClickInfo.apply(pd.Series)\n    trafficSource_adwordsClickInfo_df.columns = [f'trafficSource_adwordsClickInfo_{x}' for x in trafficSource_adwordsClickInfo_df.columns]\n    df = pd.concat([df, trafficSource_adwordsClickInfo_df], axis=1)\n    df.drop('trafficSource_adwordsClickInfo', axis=1, inplace=True)\n\n    # customDimensions\n    def parse_customDimensions(raw_str):\n        lst = literal_eval(raw_str)\n        if isinstance(lst, list) and lst:\n            return pd.Series(lst[0])\n        else:\n            return pd.Series({})\n    \n    customDimensions_df = df.customDimensions.apply(parse_customDimensions)\n    customDimensions_df.columns = [f'customDimensions_{x}' for x in customDimensions_df.columns]\n    df = pd.concat([df, customDimensions_df], axis=1)\n    df.drop('customDimensions', axis=1, inplace=True)\n\n    # hits\n    def parse_hits(raw_str):\n        lst = literal_eval(raw_str)\n        if isinstance(lst, list) and lst:\n            return pd.Series(lst[0])\n        else:\n            return pd.Series({})\n    \n    hits_df = df.hits.apply(parse_hits)\n    hits_df.columns = [f'hits_{x}' for x in hits_df.columns]\n    df = pd.concat([df, hits_df], axis=1)\n    df.drop('hits', axis=1, inplace=True)\n\n    # 'hits_page', 'hits_transaction', 'hits_item', 'hits_appInfo',\n    # 'hits_exceptionInfo', 'hits_eCommerceAction', 'hits_social', 'hits_contentGroup', 'hits_promotionActionInfo'\n    dict_cols = ['hits_page', 'hits_transaction', 'hits_item', 'hits_appInfo', \n        'hits_exceptionInfo', 'hits_eCommerceAction', 'hits_social', 'hits_contentGroup', 'hits_promotionActionInfo']\n    for col in dict_cols:\n        parsed_df = hits_df[col].apply(pd.Series)\n        parsed_df.columns = [f'{col}_{x}' for x in parsed_df.columns]\n        df = pd.concat([df, parsed_df], axis=1)\n        df.drop(col, axis=1, inplace=True)\n    \n    # 'hits_experiment', 'hits_customVariables', 'hits_customMetrics', 'hits_publisher_infos', 'hits_customDimensions' are empty\n    df.drop(['hits_experiment', 'hits_customVariables', 'hits_customMetrics', 'hits_publisher_infos', 'hits_customDimensions'], axis=1, inplace=True)\n\n    # 'hits_product', 'hits_promotion'\n    def parse_list(x):\n        if isinstance(x, list) and x:\n            return pd.Series(x[0])\n        else:\n            return pd.Series({})\n    \n    for col in ['hits_product', 'hits_promotion']:\n        parsed_df = hits_df[col].apply(parse_list)\n        parsed_df.columns = [f'{col}_{x}' for x in parsed_df.columns]\n        df = pd.concat([df, parsed_df], axis=1)\n        df.drop(col, axis=1, inplace=True)\n\n    df.to_csv(out_csv, index=False)\n\n    return df.shape\n\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"138e948d9e47fe6abff06ef08e27f1e15b141dfb"},"cell_type":"markdown","source":"It takes about 1~2h to flatten the train_v2.csv and test_v2.csv. Have fun!"}],"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}