{"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","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_count":null,"outputs":[]},{"cell_type":"code","source":"gd.head() # Melihat secara sekilas data bulding_metadata.csv","metadata":{},"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 TEST","metadata":{}},{"cell_type":"code","source":"# Melihat secara sekilas data pada test.csv\nen_test.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melihat informasi data frame test.csv,seperti banyaknya row dan column, type data setiap variabel\nen_test.info()\n\n# Melihat jumlah missing values pada data frame test.csv\nen_test.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\ntest_en_gd = en_test.merge(gd, how = 'left', on = 'building_id')\n\n# Melihat secara sekilas data pada gabungan/hasil left join data test.csv dengan building_metadata.csv\ntest_en_gd.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melihat secara seklilas data pada weather_test.csv\ncu_test.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melihat informasi pada data frame weather_test.csv\ncu_test.info()\n\n# Melihat jumlah missing values pada data frame weather_test.csv\ncu_test.isnull().sum()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan cek pola missing values yang terdapat pada cu_test\n\nsns.heatmap(cu_test.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_test_timestamp = cu_test['timestamp']\ncu_test_timestamp = cu_test_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_test['timestamp'] = pd.to_datetime(cu_test['timestamp'])\ncu_test['timestamp'] = pd.to_datetime(cu_test['timestamp'], format = \"%Y-%m-%d %H:%M:%S\").dt.strftime('%m,%H')\ncu_test['timestamp'].tail()\n\n# 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_testa = cu_test.groupby(['site_id','timestamp'])['air_temperature'].median()\ncu_testa = cu_testa.to_frame()\ncu_test = cu_test.merge(cu_testa, how = 'left', on = ['timestamp','site_id'])\ncu_test['air_temperature_x'] = cu_test['air_temperature_x'].fillna(cu_test['air_temperature_y'])\n\ncu_testc = cu_test.groupby(['site_id','timestamp'])['cloud_coverage'].median()\ncu_testc = cu_testc.to_frame()\ncu_test = cu_test.merge(cu_testc, how = 'left', on = ['timestamp','site_id'])\ncu_test['cloud_coverage_x'] = cu_test['cloud_coverage_x'].fillna(cu_test['cloud_coverage_y'])\n\ncu_testd = cu_test.groupby(['site_id','timestamp'])['dew_temperature'].median()\ncu_testd = cu_testd.to_frame()\ncu_test = cu_test.merge(cu_testd, how = 'left', on = ['timestamp','site_id'])\ncu_test['dew_temperature_x'] = cu_test['dew_temperature_x'].fillna(cu_test['dew_temperature_y'])\n\ncu_testp = cu_test.groupby(['site_id','timestamp'])['precip_depth_1_hr'].median()\ncu_testp = cu_testp.to_frame()\ncu_test = cu_test.merge(cu_testp, how = 'left', on = ['timestamp','site_id'])\ncu_test['precip_depth_1_hr_x'] = cu_test['precip_depth_1_hr_x'].fillna(cu_test['precip_depth_1_hr_y'])\n\ncu_tests = cu_test.groupby(['site_id','timestamp'])['sea_level_pressure'].median()\ncu_tests = cu_tests.to_frame()\ncu_test = cu_test.merge(cu_tests, how = 'left', on = ['timestamp','site_id'])\ncu_test['sea_level_pressure_x'] = cu_test['sea_level_pressure_x'].fillna(cu_test['sea_level_pressure_y'])\n\ncu_testwd = cu_test.groupby(['site_id','timestamp'])['wind_direction'].median()\ncu_testwd = cu_testwd.to_frame()\ncu_test = cu_test.merge(cu_testwd, how = 'left', on = ['timestamp','site_id'])\ncu_test['wind_direction_x'] = cu_test['wind_direction_x'].fillna(cu_test['wind_direction_y'])\n\ncu_testws = cu_test.groupby(['site_id','timestamp'])['wind_speed'].median()\ncu_testws = cu_testws.to_frame()\ncu_test = cu_test.merge(cu_testws, how = 'left', on = ['timestamp','site_id'])\ncu_test['wind_speed_x'] = cu_test['wind_speed_x'].fillna(cu_test['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_test = pd.merge(cu_test, cu_test_timestamp, left_index=True, right_index=True)\n\n# Karena masih terdapat column yang berisi median pada setiap variabel, lakukan drop column tersebut\ncu_test = cu_test.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_test = cu_test[['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_test = cu_test.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_test.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_test.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_test['cloud_coverage'] = cu_test['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_test['precip_depth_1_hr'] = cu_test['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_test['sea_level_pressure'] = cu_test['sea_level_pressure'].fillna(median_sea)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan cek persentase missing values pada data\ncu_test.count()/len(cu_test)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan reduce untuk data test_en_gd dan cu_test\ntest_en_gd = reduce_mem_usage(test_en_gd)\ncu_test = reduce_mem_usage(cu_test)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan left join data gabungan pada test_en_gd dengan data cuaca weather_test pada column timestamp dan site_id\ndf_test = test_en_gd.merge(cu_test, how = 'left', on = ['site_id','timestamp'])\n\n# Melihat sekilas pada data frame df_test yang telah digabung dari 3 dataframe\ndf_test.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melihat informasi data frame pada gabungan/hasil left join data \ndf_test.info()\n\n# Melihat persentase missing values pada data frame df_test\ndf_test.count()/len(df_test)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Mencari nilai yang menjadi missing values\ndf_test_null = df_test[df_test.air_temperature.isnull()]\ndf_test_null.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Missing values terjadi karena ketiadaan data pada suatu rentang waktu timestamp weather_test.csv\n# Missing values pada tahap ini dapat dilakukan dropna() \ndf_test = df_test.dropna(subset=['air_temperature']) ","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan cek apakah missing values pada data yang berkaitan dengan weather telah terhapus\nprint(df_test.isnull().sum())\n\n# Melakukan cek info data df_test\nprint(df_test.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_test['timestamp'] = df_test['timestamp'].astype('object')\ndf_test['timestamp'] = pd.to_datetime(df_test['timestamp'])\n\n#Melihat informasi data frame untuk melihat apakah type data timestamp sudah terganti menjadi date time\ndf_test.info()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Melakukan cek sekilas kekonsistenan dari beberapa variabel yang memungkinkan terdapat ketidakkonsistenan\n\nts_met = df_test['meter'].unique()\nts_met.sort()\nprint(ts_met)\n\nts_siteid = df_test['site_id'].unique()\nts_siteid.sort()\nprint(ts_siteid)\n\nts_primaryu = df_test['primary_use'].unique()\nprint(ts_primaryu)\n\nts_yearb = df_test['year_built'].unique()\nts_yearb.sort()\nprint(ts_yearb)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Missing values pada year_built dan floor_count pada data df_test dapat dihiraukan, karena bukan merupakan poin penting dalam melakukan analisa data penggunaan energi\n\nUntuk data test outlier dapat dibiarkan karena tidak akan dilakukan analisa statistik","metadata":{}},{"cell_type":"code","source":"print('Data test yang telah dilakukan preprocessing:')\ndf_test","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test.to_csv('eda_kelompok_a_df_test.csv', index = False)","metadata":{},"execution_count":null,"outputs":[]}]}