{"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":"## Example Notebook 2: data processing & feature engineer","metadata":{}},{"cell_type":"markdown","source":"In this notebook we will be inputing missing values and exploring some simple feature engineer ideas to apply to this dataset. We will be using the dataset resulting from the notebook [notebook](). Remember these are just some ideas, feel free to play with the data try different combination of features!\n\n#### Table of content\n- Data processing\n    - Handling missing values\n- Feature engineer\n    - Temporal features\n    - Summary metrics\n    - Clustering","metadata":{}},{"cell_type":"markdown","source":"## Imports & files","metadata":{}},{"cell_type":"markdown","source":"We are going to use the dataset created in the [previous notebook](https://www.kaggle.com/code/mariofrei/example-notebook-1-basic-ml/notebook).","metadata":{}},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\npd.set_option('display.max_rows', None)","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:44:50.541510Z","iopub.execute_input":"2023-10-12T01:44:50.541999Z","iopub.status.idle":"2023-10-12T01:44:50.936775Z","shell.execute_reply.started":"2023-10-12T01:44:50.541957Z","shell.execute_reply":"2023-10-12T01:44:50.935615Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Load data\ndf_train = pd.read_csv(\"/kaggle/input/ipm3211-nb1-output/nb1_train.csv\", index_col=[\"time\"])\ndf_test = pd.read_csv(\"/kaggle/input/ipm3211-nb1-output/nb1_test.csv\", index_col=[\"time\"])","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:45:28.315202Z","iopub.execute_input":"2023-10-12T01:45:28.315676Z","iopub.status.idle":"2023-10-12T01:45:28.396196Z","shell.execute_reply.started":"2023-10-12T01:45:28.315647Z","shell.execute_reply":"2023-10-12T01:45:28.394918Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.info()","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:45:39.468236Z","iopub.execute_input":"2023-10-12T01:45:39.468739Z","iopub.status.idle":"2023-10-12T01:45:39.520774Z","shell.execute_reply.started":"2023-10-12T01:45:39.468701Z","shell.execute_reply":"2023-10-12T01:45:39.519458Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"As we are predicting `q_noise_nearby`, `q_noise_kind`, `q_earphones` and `q_thermal_preference` we will only keep rows where that value is not missing; it makes not sens inputing the target, we want that to be a real value.","metadata":{}},{"cell_type":"code","source":"df_train = df_train[(~df_train.q_noise_nearby.isna()) | (~df_train.q_noise_kind.isna()) | (~df_train.q_earphones.isna()) | (~df_train.q_thermal_preference.isna())]","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:46:52.011730Z","iopub.execute_input":"2023-10-12T01:46:52.012120Z","iopub.status.idle":"2023-10-12T01:46:52.020646Z","shell.execute_reply.started":"2023-10-12T01:46:52.012090Z","shell.execute_reply":"2023-10-12T01:46:52.019358Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Data processing","metadata":{}},{"cell_type":"markdown","source":"### Handling missing values","metadata":{}},{"cell_type":"markdown","source":"First, let's check the missing values:","metadata":{}},{"cell_type":"code","source":"df_train.isna().sum() / len(df_train) * 100","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:46:55.210838Z","iopub.execute_input":"2023-10-12T01:46:55.211598Z","iopub.status.idle":"2023-10-12T01:46:55.224023Z","shell.execute_reply.started":"2023-10-12T01:46:55.211557Z","shell.execute_reply":"2023-10-12T01:46:55.222918Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The features that have more than 50% missing values will be droped. Remember that this is just an example of the technique, you can use the strategy you want to train your model. Here we will be following what we learned in [this notebook](https://www.kaggle.com/code/ponybiam/preprocessing-3-handling-missing-values/notebook).","metadata":{}},{"cell_type":"code","source":"# Define the threshold for missing values (50% in this case)\nthreshold = 0.5\n\n# Calculate the percentage of missing values in each column\nmissing_percentage = df_train.isna().sum() / len(df_train)\n\n# Select columns with missing values less than the threshold\nselected_columns = missing_percentage[missing_percentage < threshold].index\n\n# Extract the columns from the DataFrame\ndf_selected = df_train[selected_columns]\n\ndf_selected.columns","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:46:57.429141Z","iopub.execute_input":"2023-10-12T01:46:57.429494Z","iopub.status.idle":"2023-10-12T01:46:57.445776Z","shell.execute_reply.started":"2023-10-12T01:46:57.429467Z","shell.execute_reply":"2023-10-12T01:46:57.444993Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now we will split our dataset in several data sets: `target` columns (we don't want to fill these missing values), `numerical` columns and `categorical` columns.","metadata":{}},{"cell_type":"code","source":"# Target columns\ntarget_columns = ['q_earphones', 'q_noise_nearby','q_thermal_preference', 'q_noise_kind']\n\n# Get a list of numerical column names\nnumerical_columns = list(df_selected.select_dtypes(include=['number']).columns)\nprint(\"numerical columns: \", numerical_columns)\n\n# Get a list of categorical column names\ncategorical_columns = list(df_selected.select_dtypes(exclude=['number']).columns)\nprint(\"categorical columns: \", categorical_columns)","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:46:59.398701Z","iopub.execute_input":"2023-10-12T01:46:59.399059Z","iopub.status.idle":"2023-10-12T01:46:59.406329Z","shell.execute_reply.started":"2023-10-12T01:46:59.399032Z","shell.execute_reply":"2023-10-12T01:46:59.405091Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's remove the target columns from the categorical columns list:","metadata":{}},{"cell_type":"code","source":"for item in target_columns:\n    categorical_columns.remove(item)","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:47:00.843006Z","iopub.execute_input":"2023-10-12T01:47:00.843549Z","iopub.status.idle":"2023-10-12T01:47:00.848008Z","shell.execute_reply.started":"2023-10-12T01:47:00.843520Z","shell.execute_reply":"2023-10-12T01:47:00.846804Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"target = df_selected[target_columns]\n\nnumerical_train = df_selected[numerical_columns]\ncategorical_train = df_selected[categorical_columns]\n\nnumerical_test = df_test[numerical_columns]\ncategorical_test = df_test[categorical_columns]","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:47:02.069028Z","iopub.execute_input":"2023-10-12T01:47:02.069597Z","iopub.status.idle":"2023-10-12T01:47:02.078724Z","shell.execute_reply.started":"2023-10-12T01:47:02.069568Z","shell.execute_reply":"2023-10-12T01:47:02.077808Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The features that are not a target can be filled with different techniques. Here we will fill missing values using `SimpleImputer`. We will input the mean in the numerical columns an the mode (most frequent value) in the categorical.","metadata":{}},{"cell_type":"code","source":"from sklearn.impute import SimpleImputer","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:47:03.428891Z","iopub.execute_input":"2023-10-12T01:47:03.429655Z","iopub.status.idle":"2023-10-12T01:47:04.490705Z","shell.execute_reply.started":"2023-10-12T01:47:03.429621Z","shell.execute_reply":"2023-10-12T01:47:04.489488Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Imputation in numerical features\nmy_imputer = SimpleImputer(strategy='mean')\ninput_df_train_num = pd.DataFrame(my_imputer.fit_transform(numerical_train))\ninput_df_test_num = pd.DataFrame(my_imputer.fit_transform(numerical_test))\n\n# Imputation removed column names; put them back\ninput_df_train_num.columns = numerical_train.columns\ninput_df_test_num.columns = numerical_test.columns\n\n# Imputation removed index; put it back\ninput_df_train_num[\"time\"] = numerical_train.index\ninput_df_train_num.set_index(\"time\", inplace=True)\n\ninput_df_test_num[\"time\"] = numerical_test.index\ninput_df_test_num.set_index(\"time\", inplace=True)","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:47:04.492924Z","iopub.execute_input":"2023-10-12T01:47:04.493492Z","iopub.status.idle":"2023-10-12T01:47:04.513066Z","shell.execute_reply.started":"2023-10-12T01:47:04.493463Z","shell.execute_reply":"2023-10-12T01:47:04.511890Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Imputation in categorical features\nmy_imputer = SimpleImputer(strategy='most_frequent')\ninput_df_train_cat = pd.DataFrame(my_imputer.fit_transform(categorical_train))\ninput_df_test_cat = pd.DataFrame(my_imputer.fit_transform(categorical_test))\n\n# Imputation removed column names; put them back\ninput_df_train_cat.columns = categorical_train.columns\ninput_df_test_cat.columns = categorical_test.columns\n\n# Imputation removed index; put it back\ninput_df_train_cat[\"time\"] = categorical_train.index\ninput_df_train_cat.set_index(\"time\", inplace=True)\n\ninput_df_test_cat[\"time\"] = categorical_test.index\ninput_df_test_cat.set_index(\"time\", inplace=True)","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:47:05.029219Z","iopub.execute_input":"2023-10-12T01:47:05.029594Z","iopub.status.idle":"2023-10-12T01:47:05.048312Z","shell.execute_reply.started":"2023-10-12T01:47:05.029564Z","shell.execute_reply":"2023-10-12T01:47:05.046432Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"And now we just build up again our dataset:","metadata":{}},{"cell_type":"code","source":"# Merge with numrical\ndf_train_input = pd.merge(target, input_df_train_num, how=\"inner\", left_index=True, right_index=True)\n\n# Merge with categorical\ndf_train_input = pd.merge(df_train_input, input_df_train_cat, how=\"inner\", left_index=True, right_index=True)\ndf_test_input = pd.merge(input_df_test_num, input_df_train_cat, how=\"inner\", left_index=True, right_index=True)","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:47:06.668063Z","iopub.execute_input":"2023-10-12T01:47:06.668430Z","iopub.status.idle":"2023-10-12T01:47:06.682630Z","shell.execute_reply.started":"2023-10-12T01:47:06.668402Z","shell.execute_reply":"2023-10-12T01:47:06.681662Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train_input.info()","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:47:07.621556Z","iopub.execute_input":"2023-10-12T01:47:07.622628Z","iopub.status.idle":"2023-10-12T01:47:07.639067Z","shell.execute_reply.started":"2023-10-12T01:47:07.622593Z","shell.execute_reply":"2023-10-12T01:47:07.637536Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Feature engineer","metadata":{}},{"cell_type":"markdown","source":"### Simple features","metadata":{}},{"cell_type":"markdown","source":"When you have a **temporal feature**, like timestamp, you can use it to create new features based on that. Extracting day, month, hour will help the model to find seasonal behaviours. Let's do it for this dataset:","metadata":{}},{"cell_type":"code","source":"# We reset the index first\ndf_train_input.reset_index(inplace=True)\ndf_test_input.reset_index(inplace=True)\n\n# Convert to timestamp object\ndf_train_input[\"time\"] = pd.to_datetime(df_train_input.time, format=\"mixed\")\ndf_test_input[\"time\"] = pd.to_datetime(df_test_input.time, format=\"mixed\")","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:47:10.675540Z","iopub.execute_input":"2023-10-12T01:47:10.676325Z","iopub.status.idle":"2023-10-12T01:47:10.707676Z","shell.execute_reply.started":"2023-10-12T01:47:10.676291Z","shell.execute_reply":"2023-10-12T01:47:10.706784Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Months, from 1 to 12\ndf_train_input[\"month\"] = df_train_input.time.dt.month\ndf_test_input[\"month\"] = df_test_input.time.dt.month\n\n# Day, from 1 to 31\ndf_train_input[\"day\"] = df_train_input.time.dt.day\ndf_test_input[\"day\"] = df_test_input.time.dt.day\n\n# Week day, from 0 (monday) to 6 (sunday)\ndf_train_input[\"weekday\"] = df_train_input.time.dt.weekday\ndf_test_input[\"weekday\"] = df_test_input.time.dt.weekday\n\n# Hour\ndf_train_input[\"hour\"] = df_train_input.time.dt.hour\ndf_test_input[\"day\"] = df_test_input.time.dt.day\n\n# 5 and 6 are saturday and sunday\ndf_train_input[\"is_weekend\"] = [weekday in [5,6] for weekday in df_train_input.weekday]\ndf_test_input[\"is_weekend\"] = [weekday in [5,6] for weekday in df_test_input.weekday]\n\n# check it out\ndf_train_input.head().T","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:47:11.781896Z","iopub.execute_input":"2023-10-12T01:47:11.782543Z","iopub.status.idle":"2023-10-12T01:47:11.817421Z","shell.execute_reply.started":"2023-10-12T01:47:11.782501Z","shell.execute_reply":"2023-10-12T01:47:11.816302Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Summary metrics","metadata":{}},{"cell_type":"markdown","source":"In the case of **continuous features**, there are some strategies you can use. One of them is feeding the model with new features that are a summary of a continuous one. For example, we can calculate de monthly mean of the air temperature and add it as a new feature:","metadata":{}},{"cell_type":"code","source":"# Group by month and aggregate by the median (returns a series)\nmedian_temp_train = df_train_input.groupby(\"month\")[\"ws_air_temperature\"].median()\nmedian_temp_test = df_test_input.groupby(\"month\")[\"ws_air_temperature\"].median()\n\n# Convert it to a dataframe\nmedian_temp_train = pd.DataFrame(median_temp_train)\nmedian_temp_test = pd.DataFrame(median_temp_test)\n\n# And rename the column (keeps the aggregation column name, airTemperature)\nmedian_temp_train.rename(columns={\"ws_air_temperature\": \"month_median_temp\"}, inplace=True)\nmedian_temp_test.rename(columns={\"ws_air_temperature\": \"month_median_temp\"}, inplace=True)\n\n# Check it out\nmedian_temp_train","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:47:15.914052Z","iopub.execute_input":"2023-10-12T01:47:15.914409Z","iopub.status.idle":"2023-10-12T01:47:15.929090Z","shell.execute_reply.started":"2023-10-12T01:47:15.914383Z","shell.execute_reply":"2023-10-12T01:47:15.927982Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Left merge on month\ndf_train_input = pd.merge(df_train_input, median_temp_train, how=\"left\", on=\"month\")\ndf_test_input = pd.merge(df_test_input, median_temp_test, how=\"left\", on=\"month\")\n\n# Check it out\ndf_train_input.head()","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:47:17.212035Z","iopub.execute_input":"2023-10-12T01:47:17.212962Z","iopub.status.idle":"2023-10-12T01:47:17.261420Z","shell.execute_reply.started":"2023-10-12T01:47:17.212920Z","shell.execute_reply":"2023-10-12T01:47:17.260353Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Clustering","metadata":{}},{"cell_type":"markdown","source":"**Continuous features** may be the most versatile type of columns for feature engineer, there are several techniques to play with. Clustering is a common and simple one to use. We will select some weather features and find clusters and use that id as a feature for the model. We will be following what we learned in [this notebook](https://www.kaggle.com/code/ponybiam/feature-engineering-2-clustering-pca/notebook).","metadata":{}},{"cell_type":"code","source":"from sklearn.cluster import KMeans\nfrom sklearn.preprocessing import MinMaxScaler","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:47:19.267416Z","iopub.execute_input":"2023-10-12T01:47:19.268204Z","iopub.status.idle":"2023-10-12T01:47:19.460601Z","shell.execute_reply.started":"2023-10-12T01:47:19.268165Z","shell.execute_reply":"2023-10-12T01:47:19.459687Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cols = [\"ws_air_temperature\", \"ws_relative_humidity\", \"ws_wind_speed\"]","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:47:19.739725Z","iopub.execute_input":"2023-10-12T01:47:19.740738Z","iopub.status.idle":"2023-10-12T01:47:19.745113Z","shell.execute_reply.started":"2023-10-12T01:47:19.740702Z","shell.execute_reply":"2023-10-12T01:47:19.743791Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Select the columns to scale\nX = df_train_input[cols].copy()","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:47:20.027231Z","iopub.execute_input":"2023-10-12T01:47:20.027566Z","iopub.status.idle":"2023-10-12T01:47:20.034281Z","shell.execute_reply.started":"2023-10-12T01:47:20.027541Z","shell.execute_reply":"2023-10-12T01:47:20.033049Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create the scaler\nscaler = MinMaxScaler()\n\n# Fit your data (returns an array, we are creating a dataframe here)\nX_scaled = pd.DataFrame(scaler.fit_transform(X))\n\n# Add the columns names (where removed in previous step)\nX_scaled.columns = X.columns\n\n# Check it out\nX_scaled.head()","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:47:20.867274Z","iopub.execute_input":"2023-10-12T01:47:20.867651Z","iopub.status.idle":"2023-10-12T01:47:20.882735Z","shell.execute_reply.started":"2023-10-12T01:47:20.867619Z","shell.execute_reply":"2023-10-12T01:47:20.881489Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create cluster objet\nkmeans = KMeans(n_clusters=3)\n\n# Fit your data\nX_scaled[\"Cluster\"] = kmeans.fit_predict(X_scaled)\n\n# Add column with the clusters found\nX_scaled[\"Cluster\"] = X_scaled[\"Cluster\"].astype(\"int\")\n\n#Check it out\nX_scaled.head()","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:47:22.019525Z","iopub.execute_input":"2023-10-12T01:47:22.019903Z","iopub.status.idle":"2023-10-12T01:47:22.134185Z","shell.execute_reply.started":"2023-10-12T01:47:22.019870Z","shell.execute_reply":"2023-10-12T01:47:22.133377Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Add cluster feature to train dats\ndf_train_input[\"cluster\"] = X_scaled.Cluster","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:47:23.293418Z","iopub.execute_input":"2023-10-12T01:47:23.294525Z","iopub.status.idle":"2023-10-12T01:47:23.300742Z","shell.execute_reply.started":"2023-10-12T01:47:23.294489Z","shell.execute_reply":"2023-10-12T01:47:23.299385Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"> ### IDEA\n> what about trying out another set of weather features? what about combining it with non-weather features? Remember that **this technique only works for continuous features**.","metadata":{}},{"cell_type":"markdown","source":"## Export csv","metadata":{}},{"cell_type":"markdown","source":"Now that you input the missing values and created new features you can save that csv and train different models!","metadata":{}},{"cell_type":"code","source":"df_train_input.to_csv(\"df_train_input_fe.csv\", index=False)\ndf_test_input.to_csv(\"df_test_input_fe.csv\", index=False)","metadata":{"execution":{"iopub.status.busy":"2023-10-12T01:47:26.539593Z","iopub.execute_input":"2023-10-12T01:47:26.539953Z","iopub.status.idle":"2023-10-12T01:47:26.652762Z","shell.execute_reply.started":"2023-10-12T01:47:26.539926Z","shell.execute_reply":"2023-10-12T01:47:26.651873Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Tips & suggestions","metadata":{}},{"cell_type":"markdown","source":"What we did here is just a simple example of what you can do with this data. Feel free to explore, play, create a bunch of features, use different techniques to input missing values and run a model with different data sets. The secret of this field is to try thing out and see what happens!","metadata":{}}]}