{"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.11.6"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":81933,"databundleVersionId":9643020,"sourceType":"competition"}],"dockerImageVersionId":30786,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"### Step 1.1\n\nTo start working with the House Prices dataset, you will need to import the required libraries, and read the data into a pandas DataFrame.\n\n\n\n- Import the following libraries using import statements.\n\n  - **numpy** (for multidimensional array computation) with the alias **np**\n\n  - **pandas** (for data manipulation) with the alias **pd**\n\n  - **matplotlib.pyplot** (for data visualization) with the alias **plt**\n\n\n\nNote: Run a code cell by clicking on the cell and using the keyboard shortcut &lt;Shift&gt; + &lt;Enter&gt;.","metadata":{}},{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        # print(os.path.join(dirname, filename))\n        pass\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:52:26.571988Z","iopub.execute_input":"2024-11-12T17:52:26.572398Z","iopub.status.idle":"2024-11-12T17:52:27.367528Z","shell.execute_reply.started":"2024-11-12T17:52:26.572359Z","shell.execute_reply":"2024-11-12T17:52:27.366431Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Put your code here\n\nimport numpy as np\n\nimport pandas as pd\n\nimport os\n\nimport matplotlib.pyplot as plt","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:49:22.313997Z","iopub.execute_input":"2024-11-12T17:49:22.314608Z","iopub.status.idle":"2024-11-12T17:49:22.321254Z","shell.execute_reply.started":"2024-11-12T17:49:22.314567Z","shell.execute_reply":"2024-11-12T17:49:22.319778Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### Step 1.2\n\n- Activate CoW by setting `pd.options.mode.copy_on_write` as `True`","metadata":{}},{"cell_type":"code","source":"# Put your code here\n\npd.options.mode.copy_on_write = True","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:49:24.132824Z","iopub.execute_input":"2024-11-12T17:49:24.133198Z","iopub.status.idle":"2024-11-12T17:49:24.138019Z","shell.execute_reply.started":"2024-11-12T17:49:24.133164Z","shell.execute_reply":"2024-11-12T17:49:24.136927Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### Step 1.3\n\n- Read the csv file 'house-train.csv' using Pandas' **read_csv** function (<a href=\"https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html\">pandas.read_csv</a>) to create a DataFrame called **df** with default settings (i.e. the only argument is the file name).\n","metadata":{}},{"cell_type":"code","source":"data_path = '/kaggle/input/child-mind-institute-problematic-internet-use'\n\ncsv_path = os.path.join(data_path, 'train.csv')","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:50:42.272761Z","iopub.execute_input":"2024-11-12T17:50:42.27326Z","iopub.status.idle":"2024-11-12T17:50:42.28169Z","shell.execute_reply.started":"2024-11-12T17:50:42.273212Z","shell.execute_reply":"2024-11-12T17:50:42.280041Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df = pd.read_csv(csv_path)\n\ndf","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:50:45.907052Z","iopub.execute_input":"2024-11-12T17:50:45.907459Z","iopub.status.idle":"2024-11-12T17:50:45.974618Z","shell.execute_reply.started":"2024-11-12T17:50:45.907421Z","shell.execute_reply":"2024-11-12T17:50:45.973674Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### Step 1.4\n\n- Display information of dataframe **df** using **info** function (<a href=\"https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.info.html\">pandas.DataFrame.info</a>) of pandas library.","metadata":{}},{"cell_type":"code","source":"df.info()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:50:48.335903Z","iopub.execute_input":"2024-11-12T17:50:48.336299Z","iopub.status.idle":"2024-11-12T17:50:48.370149Z","shell.execute_reply.started":"2024-11-12T17:50:48.336262Z","shell.execute_reply":"2024-11-12T17:50:48.368935Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### Step 1.5\n\n\n\nNext, we will explore the features briefly.\n\n\n\n#### Step 1.5.1\n\n- Use **select_dtypes** function (<a href=\"https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.select_dtypes.html\">pandas.DataFrame.select_dtypes</a>) of pandas library to\n\nget and print the features (excluding SalePrice and Id) that are ***numerical*** (i.e. not categorical).\n\n\n\n*Note: you may also use other pandas functions.*","metadata":{}},{"cell_type":"code","source":"# Put your code here\n\ndf_num = df.select_dtypes(exclude='object')\n\ndf_num.columns","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:50:49.783381Z","iopub.execute_input":"2024-11-12T17:50:49.783818Z","iopub.status.idle":"2024-11-12T17:50:49.793793Z","shell.execute_reply.started":"2024-11-12T17:50:49.783778Z","shell.execute_reply":"2024-11-12T17:50:49.792473Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"#### Step 1.5.2\n\n- How many numerical features are there?\n\n- Write a Python statement to print it.","metadata":{}},{"cell_type":"code","source":"# Put your code here\n\nprint(len(df_num.columns))","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:50:50.923543Z","iopub.execute_input":"2024-11-12T17:50:50.923978Z","iopub.status.idle":"2024-11-12T17:50:50.92958Z","shell.execute_reply.started":"2024-11-12T17:50:50.923937Z","shell.execute_reply":"2024-11-12T17:50:50.928411Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"#### Step 1.5.3\n\n- Use **select_dtypes** function (<a href=\"https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.select_dtypes.html\">pandas.DataFrame.select_dtypes</a>) of pandas library to\n\nget the features that are ***categorical***.\n\n\n\n*Note: you may also use other pandas functions.*","metadata":{}},{"cell_type":"code","source":"# Put your code here\n\ndf_cat = df.select_dtypes(include='object').drop('id', axis=1)\n\ndf_cat.columns","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:50:52.152648Z","iopub.execute_input":"2024-11-12T17:50:52.153944Z","iopub.status.idle":"2024-11-12T17:50:52.163588Z","shell.execute_reply.started":"2024-11-12T17:50:52.153875Z","shell.execute_reply":"2024-11-12T17:50:52.162441Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"#### Step 1.5.4\n\n- How many categorical features are there?\n\n- Write a Python statement to print it.","metadata":{}},{"cell_type":"code","source":"# Put your code here\n\nprint(len(df_cat.columns))","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:50:53.173686Z","iopub.execute_input":"2024-11-12T17:50:53.174082Z","iopub.status.idle":"2024-11-12T17:50:53.179307Z","shell.execute_reply.started":"2024-11-12T17:50:53.174042Z","shell.execute_reply":"2024-11-12T17:50:53.178286Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<a name=\"part2\"></a>\n\n## Step 2: Cleaning data: Handling missing values (40 points)","metadata":{}},{"cell_type":"markdown","source":"### Step 2.1\n\nFirst, we will find the features with missing values.\n\n\n\n### Step 2.1.1\n\n- Evaluate the data quality and perform missing values assessment using **isnull** or **isna** function (<a href=\"https://pandas.pydata.org/pandas-docs/stable/generated/pandas.isnull.html\">pandas.isnull</a>) and **sum** function (<a href=\"https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.sum.html\">pandas.DataFrame.sum</a>) of pandas library.\n\n- Print the features with missing values and also the corresponding numbers of missing values.\n\n\n\n*Note: you may also use other pandas functions.*","metadata":{}},{"cell_type":"code","source":"df.isna().sum().sort_values(ascending=False).head(50)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:50:54.719281Z","iopub.execute_input":"2024-11-12T17:50:54.720187Z","iopub.status.idle":"2024-11-12T17:50:54.737209Z","shell.execute_reply.started":"2024-11-12T17:50:54.720145Z","shell.execute_reply":"2024-11-12T17:50:54.736002Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Put your code here\n\ndf_na = df.isna().sum()\n\ndf_na[df_na > 0].sort_values(ascending=False)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:50:55.262764Z","iopub.execute_input":"2024-11-12T17:50:55.263156Z","iopub.status.idle":"2024-11-12T17:50:55.280039Z","shell.execute_reply.started":"2024-11-12T17:50:55.263121Z","shell.execute_reply":"2024-11-12T17:50:55.278856Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### Step 2.2\n\nThere are two features with missing value: <br />\n\n<strong>Physical-Diastolic_BP</strong>, <strong>Physical-Systolic_BP</strong><br />\n\n\n\n#### Step 2.2.1\n\nIf we look at those instances with missing values of **Physical-Diastolic_BP**, we can observe that those instances will also have values of **Physical-Systolic_BP** missing.\n\n- Select (print) the rows with missing values of Physical-Diastolic_BP, and print those rows with missing values of Physical-Systolic_BP values.","metadata":{}},{"cell_type":"code","source":"#Put your code here\n\nfrom IPython.display import display\n\ndisplay(df[df['Physical-Diastolic_BP'].isna()])\n\ndisplay(df[df['Physical-Systolic_BP'].isna()])\n\ndisplay(df[(df['Physical-Diastolic_BP'].isna()) & (df['Physical-Systolic_BP'].isna())])","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:50:56.506078Z","iopub.execute_input":"2024-11-12T17:50:56.506473Z","iopub.status.idle":"2024-11-12T17:50:56.606546Z","shell.execute_reply.started":"2024-11-12T17:50:56.506435Z","shell.execute_reply":"2024-11-12T17:50:56.605319Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"#### Step 2.2.2\n\n\n\nThen, let's look at the feature: <strong>Physical-Diastolic_BP</strong>.\n\n\n\nTo do this, first evaluate the frequency distribution of **Physical-Diastolic_BP** by creating a histogram.\n\n\n\n- Create a histogram showing the frequency distribution of **Physical-Diastolic_BP**.","metadata":{}},{"cell_type":"code","source":"# Put your code here\n\nfig, axes = plt.subplots(1, 2, figsize=(10, 5))\n\naxes[0].set_title('Frequency Distribution of Physical-Diastolic_BP')\n\naxes[0].hist(df['Physical-Diastolic_BP'], bins=30)\n\naxes[1].set_title('Frequency Distribution of Physical-Systolic_BP')\n\naxes[1].hist(df['Physical-Systolic_BP'], bins=30)\n\n\n\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:50:57.704193Z","iopub.execute_input":"2024-11-12T17:50:57.704592Z","iopub.status.idle":"2024-11-12T17:50:58.249071Z","shell.execute_reply.started":"2024-11-12T17:50:57.704554Z","shell.execute_reply":"2024-11-12T17:50:58.247875Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df['Physical-Diastolic_BP'].median()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:50:58.767725Z","iopub.execute_input":"2024-11-12T17:50:58.76813Z","iopub.status.idle":"2024-11-12T17:50:58.777529Z","shell.execute_reply.started":"2024-11-12T17:50:58.768092Z","shell.execute_reply":"2024-11-12T17:50:58.776404Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"#### Step 2.2.3\n\nUse the median value of the **Physical-Diastolic_BP** feature and the median value of the **Physical-Systolic_BP** to impute the missing values. Again, **fillna** function (<a href=\"https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.fillna.html\">pandas.DataFrame.fillna</a>)","metadata":{}},{"cell_type":"code","source":"df_grp_age_sex = df.groupby(['Basic_Demos-Age', 'Basic_Demos-Sex'])\n\nfill_na_dict = {'Physical-Diastolic_BP': df_grp_age_sex['Physical-Diastolic_BP'].transform('median'),\n\n                'Physical-Systolic_BP': df_grp_age_sex['Physical-Systolic_BP'].transform('median')}\n\ndf.fillna(fill_na_dict, inplace=True)\n\nrow_to_drop = df[(df['Physical-Diastolic_BP'].isna()) | (df['Physical-Systolic_BP'].isna())].index\n\ndf.drop(row_to_drop, inplace=True)\n\ndf['Physical-Diastolic_BP'].isna().value_counts()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:50:59.728779Z","iopub.execute_input":"2024-11-12T17:50:59.729208Z","iopub.status.idle":"2024-11-12T17:50:59.765481Z","shell.execute_reply.started":"2024-11-12T17:50:59.729168Z","shell.execute_reply":"2024-11-12T17:50:59.763972Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### Step 2.3\n\nThere are two features with missing value: <br />\n\n<strong>Physical-BMI</strong>, and <strong>Physical-Weight</strong><br />\n\n\n\n#### Step 2.3.1\n\nIf we look at those instances with missing values of **Physical-BMI**, we can observe that those instances will also have values of **Physical-Weight** missing.\n\n- Select (print) the rows with missing values of Physical-BMI, and print those rows with missing values of Physical-Weight values.","metadata":{}},{"cell_type":"code","source":"bmi_null = df['Physical-BMI'].isna()\n\nbmi_zero = df['Physical-BMI'] == 0\n\nweight_null = df['Physical-Weight'].isna()\n\nweight_zero = df['Physical-Weight'] == 0\n\n\n\ndisplay(df[bmi_null | bmi_zero])\n\ndisplay(df[weight_null | weight_zero])\n\ndisplay(df[(bmi_null | bmi_zero) & (weight_null | weight_zero)])","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:00.626883Z","iopub.execute_input":"2024-11-12T17:51:00.627299Z","iopub.status.idle":"2024-11-12T17:51:00.717115Z","shell.execute_reply.started":"2024-11-12T17:51:00.62726Z","shell.execute_reply":"2024-11-12T17:51:00.716081Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df_grp_age_sex = df.groupby(['Basic_Demos-Age', 'Basic_Demos-Sex'])\n\nfill_na_dict = {'Physical-Height': df_grp_age_sex['Physical-Height'].transform('median'),\n\n                'Physical-Weight': df_grp_age_sex['Physical-Weight'].transform('median')}\n\ndf.fillna(value=fill_na_dict, inplace=True)\n\ndf['Physical-Height'].isna().value_counts()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:01.07861Z","iopub.execute_input":"2024-11-12T17:51:01.079736Z","iopub.status.idle":"2024-11-12T17:51:01.095074Z","shell.execute_reply.started":"2024-11-12T17:51:01.079684Z","shell.execute_reply":"2024-11-12T17:51:01.093866Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"There is still a row with null value, which concerns a 22-year-old male who is the only sample in the age-and-sex group. We simply drop that sample.","metadata":{}},{"cell_type":"code","source":"df.drop(df.loc[df['Physical-Height'].isna()].index, inplace=True)\n\ndf['Physical-Height'].isna().value_counts()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:01.872741Z","iopub.execute_input":"2024-11-12T17:51:01.873136Z","iopub.status.idle":"2024-11-12T17:51:01.89222Z","shell.execute_reply.started":"2024-11-12T17:51:01.873097Z","shell.execute_reply":"2024-11-12T17:51:01.891135Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"Now that the null values of both Physical-Height and Physical-Weight are filled, the null values of **Physical-BMI** can be filled by the following formula:\n\n\n\nBMI = 703 x mass (lbs) / height^2 (in)","metadata":{}},{"cell_type":"code","source":"df.fillna(value={'Physical-BMI': 703 * df['Physical-Weight'] / df['Physical-Height'] ** 2}, inplace=True)\n\ndf['Physical-BMI'].isna().value_counts()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:02.503153Z","iopub.execute_input":"2024-11-12T17:51:02.503568Z","iopub.status.idle":"2024-11-12T17:51:02.516222Z","shell.execute_reply.started":"2024-11-12T17:51:02.503527Z","shell.execute_reply":"2024-11-12T17:51:02.514886Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"#### Step 2.3.2\n\nUse median by age and sex to fill the null values of **Physical-Waist_Circumference**","metadata":{}},{"cell_type":"code","source":"df_grp_age_sex = df.groupby(['Basic_Demos-Age', 'Basic_Demos-Sex'])\n\nfill_na_dict = {'Physical-Waist_Circumference': df_grp_age_sex['Physical-Waist_Circumference'].transform('median')}\n\ndf.fillna(value=fill_na_dict, inplace=True)\n\ndf['Physical-Waist_Circumference'].isna().value_counts()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:03.149762Z","iopub.execute_input":"2024-11-12T17:51:03.150158Z","iopub.status.idle":"2024-11-12T17:51:03.164785Z","shell.execute_reply.started":"2024-11-12T17:51:03.150122Z","shell.execute_reply":"2024-11-12T17:51:03.163212Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### Step 2.4\n\n\n\n#### Step 2.4.1\n\nSimilarly, use median by age and sex to fill the null values of **Physical-HeartRate**.","metadata":{}},{"cell_type":"code","source":"df_grp_age_sex = df.groupby(['Basic_Demos-Age', 'Basic_Demos-Sex'])\n\nfill_na_dict = {'Physical-HeartRate': df_grp_age_sex['Physical-HeartRate'].transform('median')}\n\ndf.fillna(value={'Physical-HeartRate': df['Physical-HeartRate'].median()}, inplace=True)\n\ndf['Physical-HeartRate'].isna().value_counts()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:03.837644Z","iopub.execute_input":"2024-11-12T17:51:03.838043Z","iopub.status.idle":"2024-11-12T17:51:03.853063Z","shell.execute_reply.started":"2024-11-12T17:51:03.838009Z","shell.execute_reply":"2024-11-12T17:51:03.850881Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### Step 2.5\n\nFitness features.\n\n\n\n#### Step 2.5.1\n\nUse median by age and sex to fill the null values of\n\n- **Fitness_Endurance-Time_Mins**\n\n- **Fitness_Endurance-Time_Sec**\n\n- **FGC-FGC_CU**\n\n- **FGC-FGC_GSND**\n\n- **FGC-FGC_GSD**\n\n- **FGC-FGC_PU**\n\n- **FGC-FGC_SRL**\n\n- **FGC-FGC_SRR**\n\n- **FGC-FGC_TL**","metadata":{}},{"cell_type":"code","source":"df_grp_age_sex = df.groupby(['Basic_Demos-Age', 'Basic_Demos-Sex'])\n\nfill_na_list = ['Fitness_Endurance-Time_Mins', 'Fitness_Endurance-Time_Sec',\n\n                'FGC-FGC_CU', 'FGC-FGC_GSND', 'FGC-FGC_GSD',\n\n                'FGC-FGC_PU', 'FGC-FGC_SRL', 'FGC-FGC_SRR', 'FGC-FGC_TL']\n\nfill_na_dict = {feature: df_grp_age_sex[feature].transform('median') for feature in fill_na_list}\n\ndf.fillna(value=fill_na_dict, inplace=True)\n\ndf.fillna(value={feature: 0 for feature in fill_na_list}, inplace=True)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:04.49977Z","iopub.execute_input":"2024-11-12T17:51:04.500257Z","iopub.status.idle":"2024-11-12T17:51:04.52676Z","shell.execute_reply.started":"2024-11-12T17:51:04.500206Z","shell.execute_reply":"2024-11-12T17:51:04.52569Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"Create a new column **Fitness_Endurace-Time**, which is the total time of endurance","metadata":{}},{"cell_type":"code","source":"df['Fitness_Endurance-Time'] = df['Fitness_Endurance-Time_Mins'] * 60 + df['Fitness_Endurance-Time_Sec']","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:05.12947Z","iopub.execute_input":"2024-11-12T17:51:05.129893Z","iopub.status.idle":"2024-11-12T17:51:05.13714Z","shell.execute_reply.started":"2024-11-12T17:51:05.129844Z","shell.execute_reply":"2024-11-12T17:51:05.135729Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"#### Step 2.5.2\n\nUse the mode of **Fitness_Endurance-Max_Stage** by age and sex to fill its null value","metadata":{}},{"cell_type":"code","source":"# df_grp_age_sex = df.groupby(['Basic_Demos-Age', 'Basic_Demos-Sex'])\n\n# fill_na_dict = {'Fitness_Endurance-Time_Mins': df_grp_age_sex['Fitness_Endurance-Time_Mins'].transform('median')}\n\n# df_fitness = df.fillna(value=fill_na_dict)\n\n# df_fitness.fillna({'Fitness_Endurance-Time_Mins': 0}, inplace=True)\n\n# # df_fitness.loc[df_fitness['Fitness_Endurance-Time_Mins'].isna(), ['Basic_Demos-Age', 'Basic_Demos-Sex', 'Fitness_Endurance-Time_Mins']]\n\n# # df_fitness.loc[df_fitness['Fitness_Endurance-Time_Mins']==0, ['Basic_Demos-Age', 'Basic_Demos-Sex', 'Fitness_Endurance-Time_Mins', 'Fitness_Endurance-Max_Stage']]\n\n# # df_fitness.loc[df_fitness['Fitness_Endurance-Max_Stage'].isna(), ['Basic_Demos-Age', 'Basic_Demos-Sex', 'Fitness_Endurance-Time_Mins', 'Fitness_Endurance-Max_Stage']]\n\n# df_fitness.loc[df_fitness['Fitness_Endurance-Time_Mins']==0, ['Fitness_Endurance-Max_Stage']] = 0","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:05.78769Z","iopub.execute_input":"2024-11-12T17:51:05.788089Z","iopub.status.idle":"2024-11-12T17:51:05.793428Z","shell.execute_reply.started":"2024-11-12T17:51:05.788051Z","shell.execute_reply":"2024-11-12T17:51:05.792303Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# df_fitness_grp_age_sex = df_fitness.groupby(['Basic_Demos-Age', 'Basic_Demos-Sex'])\n\n# df_fitness['Fitness_Endurance-Max_Stage'] = df_fitness_grp_age_sex['Fitness_Endurance-Max_Stage'].apply(lambda x: x.fillna(x.mode()))\n\n# df_fitness['Fitness_Endurance-Max_Stage'].isna().value_counts()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:06.101371Z","iopub.execute_input":"2024-11-12T17:51:06.101791Z","iopub.status.idle":"2024-11-12T17:51:06.10773Z","shell.execute_reply.started":"2024-11-12T17:51:06.101753Z","shell.execute_reply":"2024-11-12T17:51:06.106512Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# df_fitness_grp_age_sex['Fitness_Endurance-Max_Stage'].apply(lambda x: x.fillna(x.mode())).reset_index(level=[0,1],drop=True).isna()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:06.49494Z","iopub.execute_input":"2024-11-12T17:51:06.495326Z","iopub.status.idle":"2024-11-12T17:51:06.499793Z","shell.execute_reply.started":"2024-11-12T17:51:06.495288Z","shell.execute_reply":"2024-11-12T17:51:06.498764Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# df.loc[(df['Basic_Demos-Age']==5) & (df['Basic_Demos-Sex']==0), ['Basic_Demos-Age', 'Basic_Demos-Sex', 'Fitness_Endurance-Time_Mins']]","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:06.868068Z","iopub.execute_input":"2024-11-12T17:51:06.868838Z","iopub.status.idle":"2024-11-12T17:51:06.873321Z","shell.execute_reply.started":"2024-11-12T17:51:06.868779Z","shell.execute_reply":"2024-11-12T17:51:06.872173Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# df_grp_age_sex['Fitness_Endurance-Time_Mins'].transform('median')","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:07.227161Z","iopub.execute_input":"2024-11-12T17:51:07.227555Z","iopub.status.idle":"2024-11-12T17:51:07.233613Z","shell.execute_reply.started":"2024-11-12T17:51:07.227517Z","shell.execute_reply":"2024-11-12T17:51:07.232527Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# df_grp_age_sex = df.groupby(['Basic_Demos-Age', 'Basic_Demos-Sex'])\n\n# fill_na_list = ['Fitness_Endurance-Time_Mins', 'Fitness_Endurance-Time_Sec',\n\n#                 'FGC-FGC_CU', 'FGC-FGC_GSND', 'FGC-FGC_GSD', 'FGC-FGC_PU',\n\n#                 'FGC-FGC_SRL', 'FGC-FGC_SRR', 'FGC-FGC_TL']\n\n# fill_na_dict = {feature: df_grp_age_sex[feature].transform('median') for feature in fill_na_list}\n\n# df.fillna(value=fill_na_dict, inplace=True)\n\n# df['Fitness_Endurance-Time_Mins'].isna().value_counts()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:07.602887Z","iopub.execute_input":"2024-11-12T17:51:07.603286Z","iopub.status.idle":"2024-11-12T17:51:07.608403Z","shell.execute_reply.started":"2024-11-12T17:51:07.603248Z","shell.execute_reply":"2024-11-12T17:51:07.607071Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"#### Step 2.5.3\n\nUse median by age and sex to fill the null values of\n\n- **BIA-BIA_BMC**\n\n- **BIA-BIA_BMI**\n\n- **BIA-BIA_BMR**\n\n- **BIA-BIA_DEE**\n\n- **BIA-BIA_ECW**\n\n- **BIA-BIA_FFM**\n\n- **BIA-BIA_FFMI**\n\n- **BIA-BIA_FMI**\n\n- **BIA-BIA_Fat**\n\n- **BIA-BIA_ICW**\n\n- **BIA-BIA_LDM**\n\n- **BIA-BIA_LST**\n\n- **BIA-BIA_SMM**\n\n- **BIA-BIA_TBW**","metadata":{}},{"cell_type":"code","source":"df_grp_age_sex = df.groupby(['Basic_Demos-Age', 'Basic_Demos-Sex'])\n\nfill_na_list = ['BIA-BIA_BMC',\n\n'BIA-BIA_BMI',\n\n'BIA-BIA_BMR',\n\n'BIA-BIA_DEE',\n\n'BIA-BIA_ECW',\n\n'BIA-BIA_FFM',\n\n'BIA-BIA_FFMI',\n\n'BIA-BIA_FMI',\n\n'BIA-BIA_Fat',\n\n'BIA-BIA_ICW',\n\n'BIA-BIA_LDM',\n\n'BIA-BIA_LST',\n\n'BIA-BIA_SMM',\n\n'BIA-BIA_TBW'\n\n]\n\nfill_na_dict = {feature: df_grp_age_sex[feature].transform('median') for feature in fill_na_list}\n\ndf.fillna(value=fill_na_dict, inplace=True)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:08.623916Z","iopub.execute_input":"2024-11-12T17:51:08.62441Z","iopub.status.idle":"2024-11-12T17:51:08.655325Z","shell.execute_reply.started":"2024-11-12T17:51:08.624366Z","shell.execute_reply":"2024-11-12T17:51:08.653853Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"#### Step 2.5.4\n\nMerge the **PAQ_A-PAQ_A_Total** and the **PAQ_C-PAQ_C_Total** into one column, since one is for adolescent and the other is for child","metadata":{}},{"cell_type":"code","source":"df.loc[(~df['PAQ_A-PAQ_A_Total'].isna()) & (~df['PAQ_C-PAQ_C_Total'].isna()), ['PAQ_A-PAQ_A_Total', 'PAQ_C-PAQ_C_Total']]","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:09.659807Z","iopub.execute_input":"2024-11-12T17:51:09.660224Z","iopub.status.idle":"2024-11-12T17:51:09.675113Z","shell.execute_reply.started":"2024-11-12T17:51:09.660184Z","shell.execute_reply":"2024-11-12T17:51:09.674028Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"There is a row with both values, which is abnormal and we drop that row","metadata":{}},{"cell_type":"code","source":"df.drop(df.loc[(~df['PAQ_A-PAQ_A_Total'].isna()) & (~df['PAQ_C-PAQ_C_Total'].isna())].index, inplace=True)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:10.680782Z","iopub.execute_input":"2024-11-12T17:51:10.681719Z","iopub.status.idle":"2024-11-12T17:51:10.695676Z","shell.execute_reply.started":"2024-11-12T17:51:10.681666Z","shell.execute_reply":"2024-11-12T17:51:10.694705Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df['PAQ_Total'] = df['PAQ_A-PAQ_A_Total'].fillna(df['PAQ_C-PAQ_C_Total'])\n\ndf['PAQ_Total'].isna().value_counts()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:11.144675Z","iopub.execute_input":"2024-11-12T17:51:11.145071Z","iopub.status.idle":"2024-11-12T17:51:11.157084Z","shell.execute_reply.started":"2024-11-12T17:51:11.145035Z","shell.execute_reply":"2024-11-12T17:51:11.15554Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df_grp_age_sex = df.groupby(['Basic_Demos-Age', 'Basic_Demos-Sex'])\n\nfill_na_dict = {'PAQ_Total': df_grp_age_sex['PAQ_Total'].transform('median')}\n\ndf.fillna(value=fill_na_dict, inplace=True)\n\ndf.fillna(value={'PAQ_Total': 0}, inplace=True)\n\ndf['PAQ_Total'].isna().value_counts()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:11.718089Z","iopub.execute_input":"2024-11-12T17:51:11.718508Z","iopub.status.idle":"2024-11-12T17:51:11.733546Z","shell.execute_reply.started":"2024-11-12T17:51:11.71847Z","shell.execute_reply":"2024-11-12T17:51:11.732482Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"#### Step 2.5.5\n\nUse median by age and sex to fill the null values of\n\n- **SDS-SDS_Total_Raw**\n\n- **SDS-SDS_Total_T**","metadata":{}},{"cell_type":"code","source":"df_grp_age_sex = df.groupby(['Basic_Demos-Age', 'Basic_Demos-Sex'])\n\nfill_na_list = [\n\n    'SDS-SDS_Total_Raw',\n\n    'SDS-SDS_Total_T'\n\n]\n\nfill_na_dict = {feature: df_grp_age_sex[feature].transform('median') for feature in fill_na_list}\n\ndf.fillna(value=fill_na_dict, inplace=True)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:13.303037Z","iopub.execute_input":"2024-11-12T17:51:13.303421Z","iopub.status.idle":"2024-11-12T17:51:13.315528Z","shell.execute_reply.started":"2024-11-12T17:51:13.303384Z","shell.execute_reply":"2024-11-12T17:51:13.314289Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"#### Step 2.5.6\n\nUse median by age and sex to fill the null values of\n\n- **PreInt_EduHx-computerinternet_hoursday**","metadata":{}},{"cell_type":"code","source":"df.fillna(value={'PreInt_EduHx-computerinternet_hoursday': df['PreInt_EduHx-computerinternet_hoursday'].mode()[0]}, inplace=True)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:14.443267Z","iopub.execute_input":"2024-11-12T17:51:14.443703Z","iopub.status.idle":"2024-11-12T17:51:14.453287Z","shell.execute_reply.started":"2024-11-12T17:51:14.443648Z","shell.execute_reply":"2024-11-12T17:51:14.452096Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"#### Step 2.5.7\n\nUse median by age and sex to fill the null values of\n\n- **CGAS-CGAS_Score**","metadata":{}},{"cell_type":"code","source":"df_grp_age_sex = df.groupby(['Basic_Demos-Age', 'Basic_Demos-Sex'])\n\nfill_na_dict = {'CGAS-CGAS_Score': df_grp_age_sex['CGAS-CGAS_Score'].transform('median')}\n\ndf.fillna(value=fill_na_dict, inplace=True)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:15.449586Z","iopub.execute_input":"2024-11-12T17:51:15.45001Z","iopub.status.idle":"2024-11-12T17:51:15.459981Z","shell.execute_reply.started":"2024-11-12T17:51:15.449969Z","shell.execute_reply":"2024-11-12T17:51:15.458707Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### Step 2.6\n\nDrop columns\n\n\n\n#### Step 2.6.1\n\nDrop columns with null values","metadata":{}},{"cell_type":"code","source":"columns_to_drop = df.columns[df.isna().sum() > 0]\n\ndf.drop(columns=columns_to_drop, inplace=True)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:16.363048Z","iopub.execute_input":"2024-11-12T17:51:16.363821Z","iopub.status.idle":"2024-11-12T17:51:16.378165Z","shell.execute_reply.started":"2024-11-12T17:51:16.363781Z","shell.execute_reply":"2024-11-12T17:51:16.377308Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"#### Step 2.6.2\n\nDrop the following redundant columns\n\n- **Physical-BMI**\n\n- **Fitness_Endurance-Time_Mins**\n\n- **Fitness_Endurance-Time_Sec**","metadata":{}},{"cell_type":"code","source":"columns_to_drop = [\n\n    'Basic_Demos-Enroll_Season',\n\n    'Physical-BMI',\n\n    'Fitness_Endurance-Time_Mins',\n\n    'Fitness_Endurance-Time_Sec'\n\n]\n\ndf.drop(columns=columns_to_drop, inplace=True)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:17.278408Z","iopub.execute_input":"2024-11-12T17:51:17.278911Z","iopub.status.idle":"2024-11-12T17:51:17.287701Z","shell.execute_reply.started":"2024-11-12T17:51:17.278869Z","shell.execute_reply":"2024-11-12T17:51:17.286054Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### Step 2.7\n\nConvert **Basic_Demos-Sex** and **PreInt_EduHx-computerinternet_hoursday** to category","metadata":{}},{"cell_type":"code","source":"df['Basic_Demos-Sex'] = df['Basic_Demos-Sex'].astype('category')\n\ndf['PreInt_EduHx-computerinternet_hoursday'] = df['PreInt_EduHx-computerinternet_hoursday'].astype('category')\n\ndf['PreInt_EduHx-computerinternet_hoursday'] = df['PreInt_EduHx-computerinternet_hoursday'].cat.set_categories([0, 1, 2, 3], ordered=True)\n\ndf['PreInt_EduHx-computerinternet_hoursday'].cat.ordered","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:18.211027Z","iopub.execute_input":"2024-11-12T17:51:18.211444Z","iopub.status.idle":"2024-11-12T17:51:18.222287Z","shell.execute_reply.started":"2024-11-12T17:51:18.211407Z","shell.execute_reply":"2024-11-12T17:51:18.221234Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df['PreInt_EduHx-computerinternet_hoursday'].value_counts()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:18.675757Z","iopub.execute_input":"2024-11-12T17:51:18.676141Z","iopub.status.idle":"2024-11-12T17:51:18.685857Z","shell.execute_reply.started":"2024-11-12T17:51:18.676107Z","shell.execute_reply":"2024-11-12T17:51:18.684711Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## Step 3 Read parquet file","metadata":{}},{"cell_type":"code","source":"parquet_path = os.path.join(data_path, 'series_train.parquet')\n\nid = 'id=0a418b57'\n\nid_parquet_path = os.path.join(parquet_path, id, 'part-0.parquet')","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:19.708107Z","iopub.execute_input":"2024-11-12T17:51:19.70848Z","iopub.status.idle":"2024-11-12T17:51:19.713727Z","shell.execute_reply.started":"2024-11-12T17:51:19.708446Z","shell.execute_reply":"2024-11-12T17:51:19.712607Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"parquet = pd.read_parquet(id_parquet_path)\n\nparquet.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-12T17:51:22.33893Z","iopub.execute_input":"2024-11-12T17:51:22.339589Z","iopub.status.idle":"2024-11-12T17:51:22.563244Z","shell.execute_reply.started":"2024-11-12T17:51:22.339551Z","shell.execute_reply":"2024-11-12T17:51:22.561745Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"import json","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"with open('./data/parquet.json', 'w') as f:\n\n    json.dump(parquet['enmo'].to_dict(), f, indent=4)","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"parquet.iloc[[213289]]","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"parquet[parquet['enmo'] == 0]","metadata":{},"outputs":[],"execution_count":null}]}