{"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":"# Melakukan Import module numpy, pandas, dan seaborn\nimport numpy as np # Untuk perhitungan yang berkaitan dengan matematika\nimport pandas as pd # Untuk read csv dan processing data pada dataframe\nimport seaborn as sns # Untuk membuat grafik dari data yang telah diproses\nimport matplotlib.pyplot as plt\nsns.set(style=\"darkgrid\")\n\nimport warnings\nwarnings.filterwarnings('ignore')\n\nfrom scipy import stats # Untuk mempermudah perhitungan statistika\nfrom datetime import * # Untuk memudahkan partisi pada time\n\n# Memudahkan import csv dari input kaggle\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-04-05T07:41:56.322114Z","iopub.execute_input":"2022-04-05T07:41:56.322487Z","iopub.status.idle":"2022-04-05T07:41:57.334189Z","shell.execute_reply.started":"2022-04-05T07:41:56.322375Z","shell.execute_reply":"2022-04-05T07:41:57.333305Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan import csv dari data-data yang disediakan dengan panda.read_csv\n\ngd = pd.read_csv('../input/ashrae-energy-prediction/building_metadata.csv')\n\nen_test = pd.read_csv('../input/ashrae-energy-prediction/test.csv')\nen_train = pd.read_csv('../input/ashrae-energy-prediction/train.csv')\n\ncu_test = pd.read_csv('../input/ashrae-energy-prediction/weather_test.csv')\ncu_train = pd.read_csv('../input/ashrae-energy-prediction/weather_train.csv')","metadata":{"execution":{"iopub.status.busy":"2022-04-05T07:42:13.23795Z","iopub.execute_input":"2022-04-05T07:42:13.238221Z","iopub.status.idle":"2022-04-05T07:42:58.326329Z","shell.execute_reply.started":"2022-04-05T07:42:13.238194Z","shell.execute_reply":"2022-04-05T07:42:58.32535Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gd.head() # Melihat secara sekilas data bulding_metadata.csv","metadata":{"execution":{"iopub.status.busy":"2022-04-05T07:44:46.596895Z","iopub.execute_input":"2022-04-05T07:44:46.597229Z","iopub.status.idle":"2022-04-05T07:44:46.62784Z","shell.execute_reply.started":"2022-04-05T07:44:46.597181Z","shell.execute_reply":"2022-04-05T07:44:46.627158Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melihat informasi data frame bulding_metadata.csv,seperti banyaknya row dan column, type data setiap variabel\ngd.info()\n\n# Melihat jumlah missing values pada data frame bulding_metadata.csv\ngd.isnull().sum()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Membuat fungsi untuk melakukan reduce pada data untuk meringankan program agar tetap dapat berjalan\n\ndef reduce_mem_usage(df_train):\n    start_mem = df_train.memory_usage().sum() / 1024**2\n    print('Memory usage of dataframe is {:.2f} MB'.format(start_mem))\n    for col in df_train.columns:\n        col_type = df_train[col].dtype\n\n        if col_type != object:\n            c_min = df_train[col].min()\n            c_max = df_train[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_train[col] = df_train[col].astype(np.int8)\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df_train[col] = df_train[col].astype(np.int16)\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df_train[col] = df_train[col].astype(np.int32)\n                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n                    df_train[col] = df_train[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_train[col] = df_train[col].astype(np.float16)\n                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df_train[col] = df_train[col].astype(np.float32)\n                else:\n                    df_train[col] = df_train[col].astype(np.float64)\n        else:\n            df_train[col] = df_train[col].astype('category')\n\n    end_mem = df_train.memory_usage().sum() / 1024**2\n    print('Memory usage after optimization is: {:.2f} MB'.format(end_mem))\n    print('Decreased by {:.1f}%'.format(100 * (start_mem - end_mem) / start_mem))\n\n    return df_train","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"PREPROCESSING DATA TRAIN","metadata":{}},{"cell_type":"code","source":"# Melihat secara seklilas data pada train.csv\nen_train.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melihat informasi data frame train.csv,seperti banyaknya row dan column, type data setiap variabel\nen_train.info()\n\n# Melihat jumlah missing values pada data frame train.csv\nen_train.isnull().sum()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan left join data test.csv dengan building_metadata.csv pada column building_id\ntrain_en_gd = en_train.merge(gd, how = 'left', on = 'building_id')\n\n# Melihat secara seklilas data pada gabungan/hasil left join data test.csv dengan building_metadata.csv\ntrain_en_gd.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melihat secara seklilas data pada weather_train.csv\ncu_train.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melihat informasi pada data frame weather_train.csv\ncu_train.info()\n\n# Melihat jumlah missing values pada data frame weather_train.csv\ncu_train.isnull().sum()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan cek pola missing values yang terdapat pada cu_train\n\nsns.heatmap(cu_train.isnull(), cbar=False)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Missing values dapat diisi dengan median dari suatu data pada site tertentu dan pada waktu tertentu\n# Untuk memudahkan, waktu yang diambil adalah waktu hours setiap harinya\n# Mengambil timestamp asli, sebelum dilakukan manipulasi\ncu_train_timestamp = cu_train['timestamp']\ncu_train_timestamp = cu_train_timestamp.to_frame()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Mengisi missing values pada data yang berhubungan dengan weather\n# Mengambil hours dari timestamp untuk nanti mencari median dari weather pada hours dan pada site tertentu\n# Memanipulasi timestamp dengan menggantinya menjadi hours\ncu_train['timestamp'] = pd.to_datetime(cu_train['timestamp'])\ncu_train['timestamp'] = pd.to_datetime(cu_train['timestamp'], format = \"%Y-%m-%d %H:%M:%S\").dt.strftime('%m,%H')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Program dibawah ini akan membuat column yang berisi median dari data-data pada jam dan site tertentu\n# Nantinya column yang telah dibuat akan digunakan untuk mengisi missing values dengan median sesuai dengan variabelnya\n\ncu_traina = cu_train.groupby(['site_id','timestamp'])['air_temperature'].median()\ncu_traina = cu_traina.to_frame()\ncu_train = cu_train.merge(cu_traina, how = 'left', on = ['timestamp','site_id'])\ncu_train['air_temperature_x'] = cu_train['air_temperature_x'].fillna(cu_train['air_temperature_y'])\n\ncu_trainc = cu_train.groupby(['site_id','timestamp'])['cloud_coverage'].median()\ncu_trainc = cu_trainc.to_frame()\ncu_train = cu_train.merge(cu_trainc, how = 'left', on = ['timestamp','site_id'])\ncu_train['cloud_coverage_x'] = cu_train['cloud_coverage_x'].fillna(cu_train['cloud_coverage_y'])\n\ncu_traind = cu_train.groupby(['site_id','timestamp'])['dew_temperature'].median()\ncu_traind = cu_traind.to_frame()\ncu_train = cu_train.merge(cu_traind, how = 'left', on = ['timestamp','site_id'])\ncu_train['dew_temperature_x'] = cu_train['dew_temperature_x'].fillna(cu_train['dew_temperature_y'])\n\ncu_trainp = cu_train.groupby(['site_id','timestamp'])['precip_depth_1_hr'].median()\ncu_trainp = cu_trainp.to_frame()\ncu_train = cu_train.merge(cu_trainp, how = 'left', on = ['timestamp','site_id'])\ncu_train['precip_depth_1_hr_x'] = cu_train['precip_depth_1_hr_x'].fillna(cu_train['precip_depth_1_hr_y'])\n\ncu_trains = cu_train.groupby(['site_id','timestamp'])['sea_level_pressure'].median()\ncu_trains = cu_trains.to_frame()\ncu_train = cu_train.merge(cu_trains, how = 'left', on = ['timestamp','site_id'])\ncu_train['sea_level_pressure_x'] = cu_train['sea_level_pressure_x'].fillna(cu_train['sea_level_pressure_y'])\n\ncu_trainwd = cu_train.groupby(['site_id','timestamp'])['wind_direction'].median()\ncu_trainwd = cu_trainwd.to_frame()\ncu_train = cu_train.merge(cu_trainwd, how = 'left', on = ['timestamp','site_id'])\ncu_train['wind_direction_x'] = cu_train['wind_direction_x'].fillna(cu_train['wind_direction_y'])\n\ncu_trainws = cu_train.groupby(['site_id','timestamp'])['wind_speed'].median()\ncu_trainws = cu_trainws.to_frame()\ncu_train = cu_train.merge(cu_trainws, how = 'left', on = ['timestamp','site_id'])\ncu_train['wind_speed_x'] = cu_train['wind_speed_x'].fillna(cu_train['wind_speed_y'])","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Karena timestamp masih berbentuk jam hasil manipulasi, lakukan join timestamp awal yang disimpan\ncu_train = pd.merge(cu_train, cu_train_timestamp, left_index=True, right_index=True)\n\n# Karena masih terdapat column yang berisi median pada setiap variabel, lakukan drop column tersebut\ncu_train = cu_train.drop(columns = ['timestamp_x','air_temperature_y',\n                                  'cloud_coverage_y','dew_temperature_y',\n                                  'precip_depth_1_hr_y','sea_level_pressure_y',\n                                  'wind_direction_y', 'wind_speed_y'])\n\n# Mengurutkan column seperti awal dataframe dan melakukan rename column seperti awal\ncu_train = cu_train[['site_id', 'timestamp_y', 'air_temperature_x','cloud_coverage_x', 'dew_temperature_x', \n                    'precip_depth_1_hr_x', 'sea_level_pressure_x','wind_direction_x', 'wind_speed_x']]\ncu_train = cu_train.rename(columns={\"timestamp_y\": \"timestamp\",\"air_temperature_x\":\"air_temperature\",\n                        \"cloud_coverage_x\": \"cloud_coverage\",\"dew_temperature_x\": \"dew_temperature\",\n                        \"precip_depth_1_hr_x\": \"precip_depth_1_hr\",\"sea_level_pressure_x\": \"sea_level_pressure\",\n                        \"wind_direction_x\": \"wind_direction\",\"wind_speed_x\": \"wind_speed\"})","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan cek apakah missing values masih terdapat dalam data\ncu_train.isnull().sum()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan cek pola missing values yang terdapat pada cu_train\n\nsns.heatmap(cu_train.isnull(), cbar=False)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Missing values yang masih terdapat pada cloud_coverage dapat dilakukan fillna(0)\n# Dengan asumsi bahwa missing values terjadi karena tidak adanya cloud pada waktu tersebut, sehingga dapat diisi 0\ncu_train['cloud_coverage'] = cu_train['cloud_coverage'].fillna(0)\n\n# Missing values yang masih terdapat pada precip_depth_1_hr dapat dilakukan fill dengan median dari data tersebut\nmedian_precip = cu_test['precip_depth_1_hr'].median()\ncu_train['precip_depth_1_hr'] = cu_train['precip_depth_1_hr'].fillna(median_precip)\n\n# Missing values yang masih terdapat pada sea_level_pressure dapat dilakukan fill dengan median dari data tersebut\nmedian_sea = cu_test['sea_level_pressure'].median()\ncu_train['sea_level_pressure'] = cu_train['sea_level_pressure'].fillna(median_sea)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan cek persentase missing values pada data\ncu_train.count()/len(cu_train)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan reduce untuk data rain_en_gd dan cu_train\ntrain_en_gd = reduce_mem_usage(train_en_gd)\ncu_train = reduce_mem_usage(cu_train)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan left join data gabungan pada train_en_gd dengan data cuaca weather_train pada column timestamp dan site_id\ndf_train = train_en_gd.merge(cu_train, how = 'left', on = ['site_id','timestamp'])\n\n# Melihat sekilas pada data frame df_test yang telah digabung dari 3 dataframe\ndf_train.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melihat informasi data frame gabungan\ndf_train.info()\n\n# Melihat persentase missing values pada data frame df_train\ndf_train.count()/len(df_train)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Mencari nilai yang menjadi missing values\ndf_train_null = df_train[df_train.air_temperature.isnull()]\ndf_train_null.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Missing values terjadi karena ketiadaan data pada suatu rentang waktu timestamp weather_train.csv\n# Missing values pada tahap ini dapat dilakukan dropna() \ndf_train = df_train.dropna(subset=['air_temperature']) ","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Memastikan missing values telah di drop\nprint(df_train.isnull().sum())\n\n# Melihat kembali info dari data\nprint(df_train.info())","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Mengganti type data dari column timestamp dari object menjadi date time karena merupakan data time\ndf_train['timestamp'] = df_train['timestamp'].astype('object')\ndf_train['timestamp'] = pd.to_datetime(df_train['timestamp'])\n\n#Melihat informasi data frame untuk melihat apakah type data timestamp sudah terganti menjadi date time\ndf_train.info()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan cek sekilas kekonsistenan dari beberapa variabel yang memungkinkan terdapat ketidakkonsistenan\n\ntr_buildid = df_train['building_id'].unique()\ntr_buildid.sort()\nprint(tr_buildid)\n\ntr_met = df_train['meter'].unique()\ntr_met.sort()\nprint(tr_met)\n\ntr_siteid = df_train['site_id'].unique()\ntr_siteid.sort()\nprint(tr_siteid)\n\ntr_primaryu = df_train['primary_use'].unique()\nprint(tr_primaryu)\n\ntr_yearb = df_train['year_built'].unique()\ntr_yearb.sort()\nprint(tr_yearb)\n\ntr_floorc = df_train['floor_count'].unique()\ntr_floorc.sort()\nprint(tr_floorc)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Missing values pada year_built dan floor_count pada data df_train dapat dihiraukan, karena bukan merupakan poin penting dalam melakukan analisa data penggunaan energi","metadata":{}},{"cell_type":"markdown","source":"Mengatasi outlier pada data train karena akan dilakukan analisa statistika dasar","metadata":{}},{"cell_type":"code","source":"# Melakukan cek distribusi meter reading dengan group by site id\ndf_train_gb_site = df_train.groupby('site_id').meter_reading.mean().reset_index()\nsns.distplot(df_train_gb_site)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan cek terhadap grafik rata-rata meter reading dari seteiap site\nsns.barplot(x='site_id',y='meter_reading',data = df_train_gb_site)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan cek terhadap analisa statistika dasar dari site_id 13\ndf_train_site13 = df_train.loc[df_train['site_id'] == 13]\ndf_train_site13.describe()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan sort data berdasarkan meter reading secara descending\n\ndf_train.sort_values('meter_reading', ascending=False)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Karena terdapat banyak sekali data outlier berasal dari building_id 1099, melakukan cek terhadap data tersebut\n\ndf_train_id1099 = df_train.loc[df_train['building_id'] == 1099]\ndf_train_id1099.describe()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Membuat fungsi yang dapat mendeteksi outlier dengan konsep IQR quantile tetapi hanya pada data uppernya dan nilai dari upper_b nya\n\ndef outl_up(df,col):\n    Q1 = df[col].quantile(0.25)\n    Q3 = df[col].quantile(0.75)\n    IQR = Q3 - Q1\n    lower_b = Q1 - 1.5*IQR\n    upper_b = Q3 + 1.5*IQR\n    outl_up_data = df.loc[(df[col] > upper_b)]\n    return outl_up_data\n\ndef outl_up_nilai(df,col):\n    Q1 = df[col].quantile(0.25)\n    Q3 = df[col].quantile(0.75)\n    IQR = Q3 - Q1\n    lower_b = Q1 - 1.5*IQR\n    upper_b = Q3 + 1.5*IQR\n    outl_up_data = df.loc[(df[col] > upper_b)]\n    return upper_b","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Mencari dasar statistika pada data yang menjadi outlier upper dan memasukkan nilai upper_b nya\n\noutl_up_meter_read = outl_up(df_train_id1099,'meter_reading')\noutl_up_meter_read_b = outl_up_nilai(df_train_id1099,'meter_reading')\n\noutl_up_meter_read.describe()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan drop data outlier pada meter_reading di building_id 1099\n\nlen_raw = len(df_train)\ndf_train = df_train.drop(df_train[(df_train.meter_reading > outl_up_meter_read_b) & (df_train.building_id == 1099)].index)\nlen_clean = len(df_train)\ncount_out = len_raw - len_clean\nprint('Jumlah outlier pada column meter_reading:',count_out)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan cek terhadap grafik rata-rata meter reading dari seteiap site\ndf_train_gb_site = df_train.groupby('site_id').meter_reading.mean().reset_index()\nsns.barplot(x='site_id',y='meter_reading',data = df_train_gb_site)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Untuk data yang berkaitan dengan weather, tidak akan dibuang nilai outliernya, karena focus targetnya adalah variabel meter reading. \nBeberapa data yang berkaitan dengan weather juga tidak dapat dilakukan remove outliernya, karena:\n1) Beberapa data mengindikasikan 0 sebagai nilai ketiadaan fenomena, sehingga apabila dilakukan remove outlier akan banyak data yang hilang\n\n2) Beberapa data menghasilkan suatu nilai yang bukan merupakan suatu distribusi","metadata":{}},{"cell_type":"code","source":"# Mencari tahu berapa banyak data yang hilang akibat dari dibuangnya outlier\n\ndata_clean_count = len_raw - len(df_train)\noutlier_percentange = (1-(len(df_train))/len_raw)*100\n\nprint('Jumlah data yang hilang:',data_clean_count)\nprint('Persentase outlier yang telah dibuang:', outlier_percentange)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print('Data train yang telah dilakukan preprocessing:')\n\ndf_train","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.to_csv('eda_kelompok_a_df_train.csv', index = False)","metadata":{},"execution_count":null,"outputs":[]}]}