{"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":"code","source":"#This is a simple helper function to help unpack the nested data in the train.csv file for the MLB digital engagement contest.\n#The pandas code isn't optimized, it uses iterrows() that is slow and we could have been more efficient with data processing","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import os\nimport pandas as pd\n\ntrain_file = os.path.join(\"/kaggle/input/mlb-player-digital-engagement-forecasting/train.csv\")\n\ntrain_df = pd.read_csv(train_file)\n\ntrain_df.head()","metadata":{"execution":{"iopub.status.busy":"2021-06-23T17:29:40.932130Z","iopub.execute_input":"2021-06-23T17:29:40.932557Z","iopub.status.idle":"2021-06-23T17:30:37.530374Z","shell.execute_reply.started":"2021-06-23T17:29:40.932498Z","shell.execute_reply":"2021-06-23T17:30:37.529241Z"}},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Helper function to unpack json found in daily data\ndef unpack_json(json_str):\n    return pd.DataFrame() if pd.isna(json_str) else pd.read_json(json_str)","metadata":{"execution":{"iopub.status.busy":"2021-06-23T17:30:37.536366Z","iopub.execute_input":"2021-06-23T17:30:37.536721Z","iopub.status.idle":"2021-06-23T17:30:37.543043Z","shell.execute_reply.started":"2021-06-23T17:30:37.536687Z","shell.execute_reply":"2021-06-23T17:30:37.541580Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import sqlite3\nimport os\n\ndef process_nested_df_column_and_add_to_sqlite(column_header):\n    \n    unnested_df = pd.DataFrame()\n    rows_processed = 0\n\n    for i, row in train_df.iterrows():\n        entry_df = unpack_json(row[column_header])\n        if unnested_df.shape[0] == 0:\n            unnested_df = entry_df\n        else:\n            if entry_df.shape[1] > 0:\n                assert unnested_df.shape[1] == entry_df.shape[1]\n                assert sorted(unnested_df.columns.values) == sorted(entry_df.columns.values)\n                unnested_df = unnested_df.append(entry_df)\n        rows_processed += 1\n\n    print(\"rows_processed\", column_header, rows_processed)\n    \n    dest_db = os.path.join(\"/kaggle/working/mlb_features.db\")\n\n    conn = sqlite3.connect(dest_db)\n    unnested_df.to_sql(column_header, conn, index=False)\n    conn.close()","metadata":{"execution":{"iopub.status.busy":"2021-06-23T17:30:37.544813Z","iopub.execute_input":"2021-06-23T17:30:37.545450Z","iopub.status.idle":"2021-06-23T17:30:37.561248Z","shell.execute_reply.started":"2021-06-23T17:30:37.545388Z","shell.execute_reply":"2021-06-23T17:30:37.559264Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"column_headers = [c for c in train_df.columns.values if c != \"date\"]\n\nfor column_header in column_headers:\n    print(\"Processing\", column_header)\n    process_nested_df_column_and_add_to_sqlite(column_header=column_header)","metadata":{"execution":{"iopub.status.busy":"2021-06-23T17:30:37.562747Z","iopub.execute_input":"2021-06-23T17:30:37.563139Z","iopub.status.idle":"2021-06-23T18:34:55.215265Z","shell.execute_reply.started":"2021-06-23T17:30:37.563105Z","shell.execute_reply":"2021-06-23T18:34:55.213186Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_file = os.path.join(\"/kaggle/input//mlb-player-digital-engagement-forecasting/example_test.csv\")\n\ntest_df = pd.read_csv(test_file, nrows=3)\n\nprint(min(test_df[\"date\"]), max(test_df[\"date\"]))\ntest_df.head()","metadata":{"execution":{"iopub.status.busy":"2021-06-23T18:34:55.218042Z","iopub.execute_input":"2021-06-23T18:34:55.218352Z","iopub.status.idle":"2021-06-23T18:34:55.717587Z","shell.execute_reply.started":"2021-06-23T18:34:55.218322Z","shell.execute_reply":"2021-06-23T18:34:55.716702Z"},"trusted":true},"execution_count":null,"outputs":[]}]}