{"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.7.3"},"kaggle":{"accelerator":"gpu","dataSources":[{"sourceId":9994,"databundleVersionId":752467,"sourceType":"competition"}],"dockerImageVersionId":29840,"isInternetEnabled":false,"language":"python","sourceType":"notebook","isGpuEnabled":true}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"import pandas as pd\nimport matplotlib.pyplot as plt\nimport numpy as np\nimport seaborn as sns\n%matplotlib inline","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"building_df = pd.read_csv(\"../input/ashrae-energy-prediction/building_metadata.csv\")\nweather_train = pd.read_csv(\"../input/ashrae-energy-prediction/weather_train.csv\")\ntrain = pd.read_csv(\"../input/ashrae-energy-prediction/train.csv\")","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"train = train.merge(building_df, left_on = \"building_id\", right_on = \"building_id\", how = \"left\")\ntrain = train.merge(weather_train, left_on = [\"site_id\", \"timestamp\"], right_on = [\"site_id\", \"timestamp\"], how = \"left\")\ndel weather_train","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Обнаружили незаполненный target до 20.05.2016\n# train[(train.meter_reading==0) & (train.meter==0) & (train.site_id==0)].groupby('timestamp').building_id.count() ","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"train = train.query('not (building_id <= 104 & meter == 0 & timestamp <= \"2016-05-20 17:00:00\")')","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"train['wind_direction'] = train['wind_direction'].map(lambda x: \n                                                    1 if x <= 22.5 and x >= 337.5\n                                                    else 2 if x > 22.5 and x < 67.5\n                                                    else 3 if x >= 67.5 and x <= 112.5\n                                                    else 4 if x > 112.5 and x < 157.5\n                                                    else 5 if x >= 157.5 and x <= 202.5\n                                                    else 6 if x > 202.5 and x < 247.5\n                                                    else 7 if x >= 247.5 and x <= 292.5                  \n                                                    else 8)","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"train.info()","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"train['timestamp'] = pd.to_datetime(train.timestamp)\n\ntrain['hour'] = train['timestamp'].dt.hour\ntrain['weekend'] = train['timestamp'].dt.weekday\ntrain['month'] = train['timestamp'].dt.month\ntrain.drop(['timestamp'], axis=1, inplace=True)","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"train['building_id'] = train['building_id'].astype('object')\ntrain['meter'] = train['meter'].astype('object')\ntrain['cloud_coverage'] = train['cloud_coverage'].astype('object')\ntrain['wind_direction'] = train['wind_direction'].astype('object')\ntrain['site_id'] = train['site_id'].astype('object')\ntrain['hour'] = train['hour'].astype('object')\ntrain['weekend'] = train['weekend'].astype('object')\ntrain['month'] = train['month'].astype('object')","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### NAN ","metadata":{}},{"cell_type":"code","source":"def show_nan(df):\n# Показать процент пропущенных данных по признакам\n\n    total = df.isnull().sum().sort_values(ascending=False)\n    percent = (df.isnull().sum()/df.isnull().count()).sort_values(ascending=False)\n    missing_data = pd.concat([total, percent], axis=1, keys=['Total', 'Percent'])\n    print(missing_data.head(20))","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"show_nan(train)","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"#Fill Nan value in weather dataframe by interpolation\ntrain = train.groupby('site_id').apply(lambda group: group.interpolate(limit_direction='both'))","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"train = train.drop(['year_built'], axis=1)\ntrain['floor_count'] = train['floor_count'].fillna(1)\ntrain['precip_depth_1_hr'] = train['precip_depth_1_hr'].fillna(0)\ntrain['sea_level_pressure'] = train['sea_level_pressure'].fillna(train['sea_level_pressure'].median())\ntrain['cloud_coverage'] = train['cloud_coverage'].fillna(train['cloud_coverage'].median())\ntrain['wind_speed'] = train['wind_speed'].fillna(train['wind_speed'].median())\ntrain['wind_direction'] = train['wind_direction'].fillna(train['wind_direction'].median())\ntrain['dew_temperature'] = train['dew_temperature'].fillna(train['dew_temperature'].median())\ntrain['air_temperature'] = train['air_temperature'].fillna(train['air_temperature'].median())","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# log transformation\ntrain['meter_reading'] = np.log1p(train['meter_reading'])\n\ny_train = train['meter_reading']\ntrain.drop(['meter_reading'], axis=1, inplace=True)","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"#Based on this great kernel https://www.kaggle.com/arjanso/reducing-dataframe-memory-size-by-65\ndef reduce_mem_usage(df):\n    start_mem_usg = df.memory_usage().sum() / 1024**2 \n    print(\"Memory usage of properties dataframe is :\",start_mem_usg,\" MB\")\n    for col in df.columns:\n        if df[col].dtype != object:  # Exclude strings            \n            IsInt = False\n            mx = df[col].max()\n            mn = df[col].min()\n            # test if column can be converted to an integer\n            asint = df[col].fillna(0).astype(np.int64)\n            result = (df[col] - asint)\n            result = result.sum()\n            if result > -0.01 and result < 0.01:\n                IsInt = True            \n            # Make Integer/unsigned Integer datatypes\n            if IsInt:\n                if mn >= 0:\n                    if mx < 255:\n                        df[col] = df[col].astype(np.uint8)\n                    elif mx < 65535:\n                        df[col] = df[col].astype(np.uint16)\n                    elif mx < 4294967295:\n                        df[col] = df[col].astype(np.uint32)\n                    else:\n                        df[col] = df[col].astype(np.uint64)\n                else:\n                    if mn > np.iinfo(np.int8).min and mx < np.iinfo(np.int8).max:\n                        df[col] = df[col].astype(np.int8)\n                    elif mn > np.iinfo(np.int16).min and mx < np.iinfo(np.int16).max:\n                        df[col] = df[col].astype(np.int16)\n                    elif mn > np.iinfo(np.int32).min and mx < np.iinfo(np.int32).max:\n                        df[col] = df[col].astype(np.int32)\n                    elif mn > np.iinfo(np.int64).min and mx < np.iinfo(np.int64).max:\n                        df[col] = df[col].astype(np.int64)    \n            # Make float datatypes 32 bit\n            else:\n                df[col] = df[col].astype(np.float32)           \n            \n    print(\"___MEMORY USAGE AFTER COMPLETION:___\")\n    mem_usg = df.memory_usage().sum() / 1024**2 \n    print(\"Memory usage is: \",mem_usg,\" MB\")\n    print(\"This is \",100*mem_usg/start_mem_usg,\"% of the initial size\")\n    return df","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"train = reduce_mem_usage(train)","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Преобразование признаков с отклонениями\nnumerical_features = train.select_dtypes(exclude = [\"object\"]).columns\nskewness = train[numerical_features].skew()\nskewed_features = skewness[abs(skewness) > 0.5].index\ntrain[skewed_features] = np.log1p(train[skewed_features])","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Преобразование категориальных признаков в таблицу 0 и 1 созданием фиктивных колонок\n\ntrain = pd.get_dummies(train)\nprint(train.shape)","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Нормализация признаков (одинаковый масштаб)\n\nfrom sklearn.preprocessing import StandardScaler\n\nscaler = StandardScaler()\ntrain = scaler.fit_transform(train)","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"#weather_test = pd.read_csv(\"weather_test.csv\")\n#test = pd.read_csv(\"test.csv\")","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"#test_ID = test['row_id']\n#test.drop('row_id', axis = 1, inplace = True)","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"#test = test.merge(building_df, left_on = \"building_id\", right_on = \"building_id\", how = \"left\")\n#test = test.merge(weather_test, left_on = [\"site_id\", \"timestamp\"], right_on = [\"site_id\", \"timestamp\"], how = \"left\")\n#del weather_test, building_df","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"#test = reduce_mem_usage(test)","metadata":{},"outputs":[],"execution_count":null},{"cell_type":"code","source":"","metadata":{},"outputs":[],"execution_count":null}]}