{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"- Some columns of 'train.csv' have json format data\n- We need to convert to dataframe for them, and save as pickle.","metadata":{}},{"cell_type":"markdown","source":"## Import","metadata":{}},{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport json\nimport pickle","metadata":{"execution":{"iopub.status.busy":"2021-06-15T16:49:45.830208Z","iopub.execute_input":"2021-06-15T16:49:45.833166Z","iopub.status.idle":"2021-06-15T16:49:45.845741Z","shell.execute_reply.started":"2021-06-15T16:49:45.833006Z","shell.execute_reply":"2021-06-15T16:49:45.844299Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train = pd.read_csv('../input/mlb-player-digital-engagement-forecasting/train.csv')\ntrain.head()\n# except for the column 'date', all columns have data of json format.","metadata":{"execution":{"iopub.status.busy":"2021-06-15T16:49:46.174687Z","iopub.execute_input":"2021-06-15T16:49:46.175089Z","iopub.status.idle":"2021-06-15T16:50:59.137022Z","shell.execute_reply.started":"2021-06-15T16:49:46.175055Z","shell.execute_reply":"2021-06-15T16:50:59.135791Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Processing","metadata":{}},{"cell_type":"code","source":"def unpack_json(df,var):\n    name = var\n    tmp = df[['date']+[name]].dropna(how='any').reset_index(drop=True)\n    temp=[]\n    for val in tmp[name]:\n        temp.append(json.loads(val))\n    tmp[name]=temp\n    \n    var_val_dict = dict(zip(tmp.iloc[0,1][0].keys(),[[] for val in tmp.iloc[0,1][0].keys()]))\n    date_list = []\n\n    for d, eng in zip(tmp['date'].values,tmp[name].values): #dfの1行ずつでループ\n        for val in eng: #1セルのリストの要素ごとにループ\n            date_list.append(d)\n            for key in val.keys(): #リストの1要素が辞書になっており、辞書内のキーでループ\n                var_val_dict[key].append(val[key])\n                #var_val_dict['date_tr'].append(id1)\n                #tqdm(zip(df['date'].values,df[name].values))\n\n    output_df = pd.DataFrame(var_val_dict)\n    output_df['date'] = date_list\n    \n    return output_df","metadata":{"execution":{"iopub.status.busy":"2021-06-15T15:51:45.290785Z","iopub.execute_input":"2021-06-15T15:51:45.291118Z","iopub.status.idle":"2021-06-15T15:51:45.310286Z","shell.execute_reply.started":"2021-06-15T15:51:45.291083Z","shell.execute_reply":"2021-06-15T15:51:45.308977Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"var_list = ['nextDayPlayerEngagement','games','rosters','playerBoxScores','teamBoxScores','transactions',\n           'standings','awards','events','playerTwitterFollowers','teamTwitterFollowers']","metadata":{"execution":{"iopub.status.busy":"2021-06-15T15:51:45.311931Z","iopub.execute_input":"2021-06-15T15:51:45.312505Z","iopub.status.idle":"2021-06-15T15:51:45.321895Z","shell.execute_reply.started":"2021-06-15T15:51:45.312463Z","shell.execute_reply":"2021-06-15T15:51:45.32098Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for var in var_list:  \n    output = unpack_json(train,var)\n    filename = var + '.pickle'\n    with open(filename, 'wb') as f:\n        pickle.dump(output, f)\n    print('output: ',filename)","metadata":{"execution":{"iopub.status.busy":"2021-06-15T15:51:45.323139Z","iopub.execute_input":"2021-06-15T15:51:45.323605Z","iopub.status.idle":"2021-06-15T15:55:16.578208Z","shell.execute_reply.started":"2021-06-15T15:51:45.323566Z","shell.execute_reply":"2021-06-15T15:55:16.5762Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Add these outputs in your notebooks!\nThank you for reading!","metadata":{}}]}