{"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":"## About this\n* Notebook for creating [unnested data sets](https://www.kaggle.com/naotaka1128/mlb-unnested).\n* I made this based on [official notebook](https://www.kaggle.com/alokpattani/mlb-player-digital-engagement-data-exploration).\n* I saved as a pickle file after reducing memory, if you want to use csv, please fork this notebook.\n\n\n## Notebook Setup","metadata":{"execution":{"iopub.execute_input":"2021-06-04T23:07:54.351665Z","iopub.status.busy":"2021-06-04T23:07:54.351302Z","iopub.status.idle":"2021-06-04T23:07:54.356206Z","shell.execute_reply":"2021-06-04T23:07:54.355039Z","shell.execute_reply.started":"2021-06-04T23:07:54.351637Z"}}},{"cell_type":"code","source":"#### Import Python Libraries and Set Script Options ####\nimport numpy as np\nimport pandas as pd\nfrom pathlib import Path\n\n# Lists all input data files from \"../input/\" directory\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))","metadata":{"_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_kg_hide-input":false,"execution":{"iopub.status.busy":"2021-06-11T10:35:27.126453Z","iopub.execute_input":"2021-06-11T10:35:27.126955Z","iopub.status.idle":"2021-06-11T10:35:27.169815Z","shell.execute_reply.started":"2021-06-11T10:35:27.12684Z","shell.execute_reply":"2021-06-11T10:35:27.168718Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def reduce_mem_usage(df, verbose=True):\n    numerics = ['int16', 'int32', 'int64', 'float16', 'float32', 'float64']\n    start_mem = df.memory_usage().sum() / 1024**2\n    for col in df.columns:\n        col_type = df[col].dtypes\n        if col_type in numerics:\n            c_min = df[col].min()\n            c_max = df[col].max()\n            if str(col_type)[:3] == 'int':\n                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n                    df[col] = df[col].astype(np.int16)\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df[col] = df[col].astype(np.int32)\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df[col] = df[col].astype(np.int64)\n                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n                    df[col] = df[col].astype(np.int64)\n            else:\n                if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n                    df[col] = df[col].astype(np.float32)\n                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df[col] = df[col].astype(np.float64)\n                else:\n                    df[col] = df[col].astype(np.float64)\n    end_mem = df.memory_usage().sum() / 1024**2\n    if verbose: print('Mem. usage decreased to {:5.2f} Mb ({:.1f}% reduction)'.format(end_mem, 100 * (start_mem - end_mem) / start_mem))\n    return df\n","metadata":{"execution":{"iopub.status.busy":"2021-06-11T10:35:27.173463Z","iopub.execute_input":"2021-06-11T10:35:27.173807Z","iopub.status.idle":"2021-06-11T10:35:27.1894Z","shell.execute_reply.started":"2021-06-11T10:35:27.173774Z","shell.execute_reply":"2021-06-11T10:35:27.188016Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Load Data","metadata":{}},{"cell_type":"code","source":"# Start with input file path\ninput_file_path = Path('/kaggle/input/mlb-player-digital-engagement-forecasting/')\n\nfiles = ['seasons', 'teams', 'players', 'awards', 'example_sample_submission']\n\nfor file in files:\n    df = pd.read_csv(input_file_path / f\"{file}.csv\")\n    #print(f\"{file}.pickle\")\n    #display(df.head(3))\n    reduce_mem_usage(df).to_pickle(f\"{file}.pickle\", protocol=4)\n    #print('\\n'*2)\n","metadata":{"execution":{"iopub.status.busy":"2021-06-11T10:37:07.368254Z","iopub.execute_input":"2021-06-11T10:37:07.368642Z","iopub.status.idle":"2021-06-11T10:37:07.566525Z","shell.execute_reply.started":"2021-06-11T10:37:07.36861Z","shell.execute_reply":"2021-06-11T10:37:07.565392Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for file in ['example_test', 'train']:\n    # drop playerTwitterFollowers, teamTwitterFollowers from example_test\n    df = pd.read_csv(input_file_path / f\"{file}.csv\").dropna(axis=1,how='all')\n    daily_data_nested_df_names = df.drop('date', axis = 1).columns.values.tolist()\n\n    for df_name in daily_data_nested_df_names:\n        date_nested_table = df[['date', df_name]]\n\n        date_nested_table = (date_nested_table[\n          ~pd.isna(date_nested_table[df_name])\n          ].\n          reset_index(drop = True)\n          )\n\n        daily_dfs_collection = []\n\n        for date_index, date_row in date_nested_table.iterrows():\n            daily_df = pd.read_json(date_row[df_name])\n\n            daily_df['dailyDataDate'] = date_row['date']\n\n            daily_dfs_collection = daily_dfs_collection + [daily_df]\n\n        # Concatenate all daily dfs into single df for each row\n        unnested_table = (pd.concat(daily_dfs_collection,\n          ignore_index = True).\n          # Set and reset index to move 'dailyDataDate' to front of df\n          set_index('dailyDataDate').\n          reset_index()\n          )\n        #print(f\"{file}_{df_name}.pickle\")\n        #display(unnested_table.head(3))\n        reduce_mem_usage(unnested_table).to_pickle(f\"{file}_{df_name}.pickle\")\n        #print('\\n'*2)\n\n        # Clean up tables and collection of daily data frames for this df\n        del(date_nested_table, daily_dfs_collection, unnested_table)\n","metadata":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2021-06-11T10:35:27.444002Z","iopub.execute_input":"2021-06-11T10:35:27.444294Z","iopub.status.idle":"2021-06-11T10:35:40.596075Z","shell.execute_reply.started":"2021-06-11T10:35:27.444265Z","shell.execute_reply":"2021-06-11T10:35:40.593754Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}