{"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":"# Ashrae - Beginner EDA\n\nThis is an EDA for Ashrae Energy Prediction Competition's data.","metadata":{}},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport pickle\nfrom tqdm import tqdm\n\n%matplotlib inline\n\nimport warnings\nwarnings.filterwarnings('ignore')\n\nimport seaborn as sb","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:21:26.436551Z","iopub.execute_input":"2021-11-26T17:21:26.437466Z","iopub.status.idle":"2021-11-26T17:21:27.471810Z","shell.execute_reply.started":"2021-11-26T17:21:26.437389Z","shell.execute_reply":"2021-11-26T17:21:27.470530Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Cleansing, merging and shrinking data","metadata":{}},{"cell_type":"code","source":"df_train = pd.read_csv('../input/ashrae-energy-prediction/train.csv')\ndf_test = pd.read_csv('../input/ashrae-energy-prediction/test.csv')\ndf_building = pd.read_csv('../input/ashrae-energy-prediction/building_metadata.csv')\ndf_w_train = pd.read_csv('../input/ashrae-energy-prediction/weather_train.csv')\ndf_w_test = pd.read_csv('../input/ashrae-energy-prediction/weather_test.csv')","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:21:27.474839Z","iopub.execute_input":"2021-11-26T17:21:27.475442Z","iopub.status.idle":"2021-11-26T17:22:27.923390Z","shell.execute_reply.started":"2021-11-26T17:21:27.475212Z","shell.execute_reply":"2021-11-26T17:22:27.922016Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def reduce_df_mem_usage(df, verbose=True):\n    '''\n    Function that reduces memory cosumption of pandas dataframe reducing number of bits for floats and numbers\n\n    Parameters\n    ----------\n    df : pandas.data_frame \n        The dataframe to be shrinked\n    verbose: boolean, optional\n        if function should print the memory reduction\n    \n    Returns\n    ----------\n    df : pandas.data_frame \n        The shrinked dataframe\n    '''\n\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.int8)\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df[col] = df[col].astype(np.int16)\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df[col] = df[col].astype(np.int32)\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.float16)\n                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df[col] = df[col].astype(np.float32)\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\ndef generate_date_parts_features(df, timestamp_col='timestamp'):\n    df['dt_timestamp'] = pd.to_datetime(df[timestamp_col], format='%Y-%m-%d %H:%M:%S')\n    df['dt_weekday'] = df['dt_timestamp'].dt.weekday\n    df['dt_quarter'] = df['dt_timestamp'].dt.quarter\n    df['dt_year'] = df['dt_timestamp'].dt.year\n    df['dt_month'] = df['dt_timestamp'].dt.month\n    df['dt_day'] = df['dt_timestamp'].dt.day\n    df['dt_dayofyear'] = df['dt_timestamp'].dt.dayofyear\n    df['dt_weekofyear'] = df['dt_timestamp'].dt.weekofyear\n    df['dt_hour'] = df['dt_timestamp'].dt.hour\n    return df    ","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:22:27.925852Z","iopub.execute_input":"2021-11-26T17:22:27.926450Z","iopub.status.idle":"2021-11-26T17:22:27.952840Z","shell.execute_reply.started":"2021-11-26T17:22:27.926388Z","shell.execute_reply":"2021-11-26T17:22:27.950843Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train = reduce_df_mem_usage(df_train)\ndf_test = reduce_df_mem_usage(df_test)\ndf_w_train = reduce_df_mem_usage(df_w_train)\ndf_w_test = reduce_df_mem_usage(df_w_test)","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:22:27.954712Z","iopub.execute_input":"2021-11-26T17:22:27.955099Z","iopub.status.idle":"2021-11-26T17:22:29.954305Z","shell.execute_reply.started":"2021-11-26T17:22:27.955045Z","shell.execute_reply":"2021-11-26T17:22:29.953491Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train = generate_date_parts_features(df_train)\ndf_test = generate_date_parts_features(df_test)","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:22:29.958832Z","iopub.execute_input":"2021-11-26T17:22:29.959443Z","iopub.status.idle":"2021-11-26T17:23:36.476558Z","shell.execute_reply.started":"2021-11-26T17:22:29.959364Z","shell.execute_reply":"2021-11-26T17:23:36.475799Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"n_train = len(df_train)\nn_test = len(df_test)\nprint(n_train, n_test)","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:23:36.478897Z","iopub.execute_input":"2021-11-26T17:23:36.479399Z","iopub.status.idle":"2021-11-26T17:23:36.485143Z","shell.execute_reply.started":"2021-11-26T17:23:36.479335Z","shell.execute_reply":"2021-11-26T17:23:36.484184Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train = pd.merge(df_train, df_building, left_on='building_id', right_on='building_id', how='left')\ndf_test = pd.merge(df_test, df_building, left_on='building_id', right_on='building_id', how='left')\ndf_train = pd.merge(df_train, df_w_train, left_on=['site_id', 'timestamp'], right_on=['site_id', 'timestamp'], how='left')\ndf_test = pd.merge(df_test, df_w_test, left_on=['site_id', 'timestamp'], right_on=['site_id', 'timestamp'], how='left')\ndf_train = reduce_df_mem_usage(df_train)\ndf_test = reduce_df_mem_usage(df_test)","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:23:36.486756Z","iopub.execute_input":"2021-11-26T17:23:36.487099Z","iopub.status.idle":"2021-11-26T17:24:54.145203Z","shell.execute_reply.started":"2021-11-26T17:23:36.487033Z","shell.execute_reply":"2021-11-26T17:24:54.143864Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.head()","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:24:54.148844Z","iopub.execute_input":"2021-11-26T17:24:54.149348Z","iopub.status.idle":"2021-11-26T17:24:54.196591Z","shell.execute_reply.started":"2021-11-26T17:24:54.149298Z","shell.execute_reply":"2021-11-26T17:24:54.195671Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.tail()","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:24:54.197924Z","iopub.execute_input":"2021-11-26T17:24:54.198257Z","iopub.status.idle":"2021-11-26T17:24:54.233742Z","shell.execute_reply.started":"2021-11-26T17:24:54.198204Z","shell.execute_reply":"2021-11-26T17:24:54.232748Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test.head()","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:24:54.235369Z","iopub.execute_input":"2021-11-26T17:24:54.235687Z","iopub.status.idle":"2021-11-26T17:24:54.272947Z","shell.execute_reply.started":"2021-11-26T17:24:54.235639Z","shell.execute_reply":"2021-11-26T17:24:54.271704Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test.tail()","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:24:54.274535Z","iopub.execute_input":"2021-11-26T17:24:54.274907Z","iopub.status.idle":"2021-11-26T17:24:54.308854Z","shell.execute_reply.started":"2021-11-26T17:24:54.274853Z","shell.execute_reply":"2021-11-26T17:24:54.307864Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.dt_timestamp.describe()","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:24:54.310573Z","iopub.execute_input":"2021-11-26T17:24:54.310922Z","iopub.status.idle":"2021-11-26T17:24:54.842150Z","shell.execute_reply.started":"2021-11-26T17:24:54.310865Z","shell.execute_reply":"2021-11-26T17:24:54.840871Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test.dt_timestamp.describe()","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:24:54.844107Z","iopub.execute_input":"2021-11-26T17:24:54.844404Z","iopub.status.idle":"2021-11-26T17:24:56.065647Z","shell.execute_reply.started":"2021-11-26T17:24:54.844359Z","shell.execute_reply":"2021-11-26T17:24:56.064541Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Basic EDA","metadata":{}},{"cell_type":"code","source":"pd.options.display.float_format = '{:,.3f}'.format\ndf_train.meter_reading.describe()","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:24:56.068863Z","iopub.execute_input":"2021-11-26T17:24:56.069433Z","iopub.status.idle":"2021-11-26T17:24:56.795277Z","shell.execute_reply.started":"2021-11-26T17:24:56.069226Z","shell.execute_reply":"2021-11-26T17:24:56.794193Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## looks like an error outlier. Let's check top 5 values\ndf_train.meter_reading.sort_values().tail(5)","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:24:56.796666Z","iopub.execute_input":"2021-11-26T17:24:56.796949Z","iopub.status.idle":"2021-11-26T17:25:04.136609Z","shell.execute_reply.started":"2021-11-26T17:24:56.796900Z","shell.execute_reply":"2021-11-26T17:25:04.135686Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's investigate\n","metadata":{}},{"cell_type":"code","source":"df_train.iloc[8905140]","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:25:04.138075Z","iopub.execute_input":"2021-11-26T17:25:04.138342Z","iopub.status.idle":"2021-11-26T17:25:04.147003Z","shell.execute_reply.started":"2021-11-26T17:25:04.138296Z","shell.execute_reply":"2021-11-26T17:25:04.146050Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train[df_train.building_id==1099].groupby(['building_id','meter'])['meter_reading'].agg(['max', 'min', 'mean', 'std'])","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:25:04.148697Z","iopub.execute_input":"2021-11-26T17:25:04.149288Z","iopub.status.idle":"2021-11-26T17:25:07.074873Z","shell.execute_reply.started":"2021-11-26T17:25:04.149212Z","shell.execute_reply":"2021-11-26T17:25:07.073964Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Actualy depending on meter type values oscilate too much","metadata":{}},{"cell_type":"markdown","source":"### Investigate target variable","metadata":{}},{"cell_type":"code","source":"## Plot some buildings consumption\nfrom IPython.display import display\n\nrandom_buildings = [1018] # = df_building.sample(10)['building_id'].values\nfor b_id in random_buildings:\n    sb.lineplot(x='dt_timestamp', y='meter_reading', data=df_train[(df_train.building_id==b_id)&(df_train.meter==1)], hue='meter' ).set_title('Building_id = %s' % str(b_id))\n    plt.show()","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:25:07.076348Z","iopub.execute_input":"2021-11-26T17:25:07.076657Z","iopub.status.idle":"2021-11-26T17:25:09.085718Z","shell.execute_reply.started":"2021-11-26T17:25:07.076596Z","shell.execute_reply":"2021-11-26T17:25:09.084717Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"It is clear that some meters are inversely proportional to others and that there is some interpolated data","metadata":{}},{"cell_type":"code","source":"## Some buildings have no data for some period. For instance Building_id == 1442. Let's investigate\nsb.lineplot(x='dt_timestamp', y='meter_reading', data=df_train[df_train.building_id==1442], hue='meter' ).set_title('Building_id =1442')","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:25:09.088053Z","iopub.execute_input":"2021-11-26T17:25:09.088405Z","iopub.status.idle":"2021-11-26T17:25:11.844701Z","shell.execute_reply.started":"2021-11-26T17:25:09.088351Z","shell.execute_reply":"2021-11-26T17:25:11.843726Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_building_1442 = df_train[((df_train.building_id==1442)&(df_train.dt_timestamp > '2016-02-10')&(df_train.dt_timestamp < '2016-03-30'))]\nplt.figure(figsize=(16, 6))\nsb.lineplot(x='dt_timestamp', y='meter_reading', data=df_building_1442[df_building_1442.meter==0]).set_title('Building_id =1442 between Feb and Mar')","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:25:11.846822Z","iopub.execute_input":"2021-11-26T17:25:11.847174Z","iopub.status.idle":"2021-11-26T17:25:12.944654Z","shell.execute_reply.started":"2021-11-26T17:25:11.847114Z","shell.execute_reply":"2021-11-26T17:25:12.943792Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Target was clearly interpolated. Let's try to see why\ndf_building_1442[df_building_1442.meter==0].head(50)","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:25:12.946156Z","iopub.execute_input":"2021-11-26T17:25:12.946472Z","iopub.status.idle":"2021-11-26T17:25:12.984156Z","shell.execute_reply.started":"2021-11-26T17:25:12.946414Z","shell.execute_reply":"2021-11-26T17:25:12.983371Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Actually seaborn interpolated the missing data. Some series has missing values. In 1442 Building example above, there's only values for a few days","metadata":{}},{"cell_type":"markdown","source":"### Build a simple model to check feature importance","metadata":{}},{"cell_type":"code","source":"from sklearn.ensemble import RandomForestRegressor\n\nX = df_train.copy()\n\ny = df_train['meter_reading']\nX = X.drop(['meter_reading','timestamp', 'dt_timestamp'], axis=1)\n\nX = X.fillna(-999)\n\n# Label encoder for categorical features\nfor c in X.columns[X.dtypes == 'object']:\n    X[c] = X[c].factorize()[0]\n    \nrf = RandomForestRegressor(max_depth=5)\nrf.fit(X,y)\nplt.plot(rf.feature_importances_)\nplt.xticks(np.arange(X.shape[1]), X.columns.tolist(), rotation=90);","metadata":{"execution":{"iopub.status.busy":"2021-11-26T17:25:12.985489Z","iopub.execute_input":"2021-11-26T17:25:12.985794Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* ### Investigate Nulls","metadata":{}},{"cell_type":"code","source":"## Number of NaN on each column\ndf_train.isnull().sum(axis=0)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test.isnull().sum(axis=0)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## check if there are buildings in test set not present on train data\ntrain_buildings = df_train.building_id.unique()\ntest_buildings = df_test.building_id.unique()\nprint(set(train_buildings) == set(test_buildings))","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Some thoughts\n\n* Investigate Holidays (which country?)\n* Probably we cannot treat each meter series independently, because they are correlated\n* Investigate site_id variable.\n* Several missing data in some dates for some buildings. Interpolate?\n* Several nan in weather sensors. Interpolate?\n* Generate aggregate metrics for weather sensors. What the best windows size? Hyperparameter?","metadata":{}},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]}]}