{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.13.15","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[],"dockerImageVersionId":28755,"isInternetEnabled":false,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session\n\n# Use the kagglehub client library to attach Kaggle resources like competitions, datasets, and models to your session\n# Learn more about kagglehub: https://github.com/Kaggle/kagglehub/blob/main/README.md\n\nimport kagglehub\n# kagglehub.dataset_download('<owner>/<dataset-slug>')","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"execution":{"iopub.status.busy":"2026-10-10T19:40:34.685636Z","iopub.execute_input":"2026-10-10T19:40:34.686175Z","iopub.status.idle":"2026-10-10T19:40:34.697079Z","shell.execute_reply.started":"2026-10-10T19:40:34.686136Z","shell.execute_reply":"2026-10-10T19:40:34.695652Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# 数据处理、内存回收与可视化\nimport pandas as pd\nimport numpy as np\nimport os\nimport gc\nimport copy\nimport warnings\n\n# 本赛用 LightGBM 回归；CatBoost 可作对照，这里只 import LGB\nimport lightgbm as lgb\nfrom lightgbm import LGBMRegressor\nfrom sklearn.metrics import mean_squared_log_error\nfrom sklearn.model_selection import StratifiedKFold, KFold\nfrom tqdm.notebook import tqdm\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\nwarnings.filterwarnings('ignore')\npd.set_option(\"display.max_columns\", 500)\n%matplotlib inline\n","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-10-10T19:40:34.700759Z","iopub.execute_input":"2026-10-10T19:40:34.701456Z","iopub.status.idle":"2026-10-10T19:40:34.736431Z","shell.execute_reply.started":"2026-10-10T19:40:34.701418Z","shell.execute_reply":"2026-10-10T19:40:34.735422Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# parse_dates：后面要做时区对齐和按小时特征，必须是 datetime 而不是字符串\ntrain = pd.read_csv(\"/kaggle/input/competitions/ashrae-energy-prediction/train.csv\", parse_dates=[\"timestamp\"])\ntest = pd.read_csv(\"/kaggle/input/competitions/ashrae-energy-prediction/test.csv\", parse_dates=[\"timestamp\"])\nbuilding = pd.read_csv('/kaggle/input/competitions/ashrae-energy-prediction/building_metadata.csv')\nweather_train = pd.read_csv('/kaggle/input/competitions/ashrae-energy-prediction/weather_train.csv', parse_dates=[\"timestamp\"])\nweather_test = pd.read_csv(\"/kaggle/input/competitions/ashrae-energy-prediction/weather_test.csv\", parse_dates=[\"timestamp\"])","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-10-10T19:40:34.737691Z","iopub.execute_input":"2026-10-10T19:40:34.738702Z","iopub.status.idle":"2026-10-10T19:41:15.025511Z","shell.execute_reply.started":"2026-10-10T19:40:34.738661Z","shell.execute_reply":"2026-10-10T19:41:15.024365Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# 把建筑元数据接到读数上，才能按 site / 用途 / 表计筛选\ntrain_plot = train.merge(building, on='building_id', how='left')\n\nsite = 0  # 建筑物的地点\nmeter_type = 1  # 仪表的类型：0电 1冷水 2蒸汽 3热水\nprimary_use = 'Education'  # 建筑物的用途\n\n# 该筛选条件下有多少栋楼，决定子图行数（每行两栋）\nr = int(np.ceil(len(train_plot[(train_plot['site_id'] == site) & (train_plot['primary_use'] == primary_use) & (train_plot['meter'] == meter_type)]['building_id'].value_counts(dropna=False).index.to_list())/2))\nfig, axes = plt.subplots(r,2,figsize=(14, 36), dpi=100)\nfor i, building_id in enumerate(train_plot[(train_plot['site_id'] == site) & (train_plot['primary_use'] == primary_use) & (train_plot['meter'] == meter_type)]['building_id'].value_counts(dropna=False).index.to_list()):\n    # 小时均值：看日内尖峰和持续为 0 的坏段\n    train_plot[(train_plot['site_id'] == site) & (train_plot['primary_use'] == primary_use) & (train_plot['meter'] == meter_type) &\n               (train_plot['building_id'] == building_id )][['timestamp', 'meter_reading']].set_index('timestamp').resample('H').mean()['meter_reading'].plot(ax=axes[i%r][i//r],\n               alpha=0.8, label='By hour', color='tab:blue').set_ylabel('Mean meter reading', fontsize=13);\n    # 日均值：看季节性\n    train_plot[(train_plot['site_id'] == site) & (train_plot['primary_use'] == primary_use) & (train_plot['meter'] == meter_type) &\n               (train_plot['building_id'] == building_id )][['timestamp', 'meter_reading']].set_index('timestamp').resample('D').mean()['meter_reading'].plot(ax=axes[i%r][i//r],\n               alpha=1, label='By day', color='tab:orange').set_xlabel('');\n    axes[i%r][i//r].legend();\n    axes[i%r][i//r].set_title('building_id: ' + str(building_id ), fontsize=13);\n    plt.subplots_adjust(hspace=0.45)\n\ndel train_plot,fig,axes,r\ngc.collect();","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-10-10T19:41:15.027441Z","iopub.execute_input":"2026-10-10T19:41:15.027957Z","iopub.status.idle":"2026-10-10T19:42:08.805116Z","shell.execute_reply.started":"2026-10-10T19:41:15.027908Z","shell.execute_reply":"2026-10-10T19:42:08.804054Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"def compress_dataframe(df):\n    '''把 object/bool/整型/浮点压到更小的 numpy 类型，并顺手把 object 编成整数类别码。\n    本赛 train 约 2000 万行，不 downcast 很容易在 Kaggle 内存上限附近爆掉。'''\n    result = df.copy()\n    for col in result.columns:\n        col_data = result[col]\n        dn = col_data.dtype.name\n        if dn == \"object\":\n            # 类别字符串 → 整数码；后面 LightGBM 可当 categorical 用\n            result[col] = pd.to_numeric(col_data.astype(\"category\").cat.codes, downcast=\"integer\")\n        elif dn == \"bool\":\n            result[col] = col_data.astype(\"int8\")\n        elif dn.startswith(\"int\") or (col_data.round() == col_data).all():\n            result[col] = pd.to_numeric(col_data, downcast=\"integer\")\n        else:\n            result[col] = pd.to_numeric(col_data, downcast='float')\n        return result","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-10-10T19:42:08.807746Z","iopub.execute_input":"2026-10-10T19:42:08.808079Z","iopub.status.idle":"2026-10-10T19:42:08.818739Z","shell.execute_reply.started":"2026-10-10T19:42:08.808052Z","shell.execute_reply":"2026-10-10T19:42:08.817489Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"def set_time(df):\n    # 把绝对时间换成「2016-01-01 起的整点小时」，后面异常检测、天气 join 都用这个整数键\n    df.timestamp = (df.timestamp - pd.to_datetime(\"2016-01-01\")).dt.total_seconds() // 3600\n    return df\n\n# 各 site 相对 UTC 的时差（小时）。天气时间戳是当地或混用，不校正会出现「夜间高峰」错位。\n# https://www.kaggle.com/patrick0302/locate-cities-according-weather-temperature\nsite_GMT_offsets = [-5, 0, -7, -5, -8, 0, -5, -5, -5, -6, -7, -5, 0, -6, -5, -5]\n\ndef weather_set_time(df,time_zone):\n    df.timestamp = (df.timestamp - pd.to_datetime(\"2016-01-01\")).dt.total_seconds() // 3600\n\n    GMT_offset_map = {site: offset for site, offset in enumerate(site_GMT_offsets)}\n    df.timestamp = df.timestamp + df.site_id.map(GMT_offset_map)\n    site_dfs = []\n    for site_id in df.site_id.unique():\n        # reindex 到该年全部小时：缺测小时会变成 NaN，才能插值，而不是静默跳过\n        site_df = df[df.site_id == site_id].set_index(\"timestamp\").reindex(time_zone)\n        site_df.site_id = site_id\n        for col in [c for c in site_df.columns if c != \"site_id\"]:\n            site_df[f\"had_{col}\"] = ~site_df[col].isna()  # 保留「原本有没有观测」\n            site_df[col] = site_df[col].interpolate(limit_direction='both', method='linear')\n            # 两端仍缺的用全局中位数填；注释里写中位数，对应的是这一行而不是 interpolate\n            site_df[col] = site_df[col].fillna(df[col].median())\n        site_dfs.append(site_df)\n    df = pd.concat(site_dfs).reset_index()\n    for col in df.columns:\n        if df[col].isna().any(): df[f\"had_{col}\"] = ~df[col].isna()\n    return df\n\ndef _add_time_features(X):\n    # timestamp 已是小时序号：整除 24 得星期近似，取模得小时\n    return X.assign(tm_day_of_week=((X.timestamp // 24) % 7), tm_hour_of_day=(X.timestamp % 24))\n\n# 建筑元数据缺失填 -1 再压缩；building_id 作索引便于 join\nbuilding = compress_dataframe(building.fillna(-1)).set_index(\"building_id\")\n\ntrain = compress_dataframe(set_time(train))\ntest = compress_dataframe(set_time(test)).set_index(\"row_id\")\n# 2016 闰年 8784 小时；测试段接到 26304（约三年）\nweather_train = compress_dataframe(weather_set_time(weather_train,range(8784))).set_index([\"site_id\", \"timestamp\"])\nweather_test = compress_dataframe(weather_set_time(weather_test,range(8784,26304))).set_index([\"site_id\", \"timestamp\"])","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-10-10T19:42:08.820157Z","iopub.execute_input":"2026-10-10T19:42:08.820554Z","iopub.status.idle":"2026-10-10T19:42:20.737633Z","shell.execute_reply.started":"2026-10-10T19:42:08.820516Z","shell.execute_reply":"2026-10-10T19:42:20.736278Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"def combined_data(df,weather):\n    # 先按 building_id 接面积/用途，再按 (site_id, timestamp) 接天气；仍缺的填 -1\n    df = compress_dataframe(df.join(building, on=\"building_id\").join(weather,\n        on=[\"site_id\", \"timestamp\"]).fillna(-1))\n    return df.drop(columns=[\"meter_reading\"]),df.meter_reading","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-10-10T19:42:20.739168Z","iopub.execute_input":"2026-10-10T19:42:20.739528Z","iopub.status.idle":"2026-10-10T19:42:20.745845Z","shell.execute_reply.started":"2026-10-10T19:42:20.739499Z","shell.execute_reply":"2026-10-10T19:42:20.744588Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# 按表计物理含义清「不该出现的 0」和已知坏段，避免模型学会错误的恒 0\ndef make_is_bad_zero(Xy_subset, min_interval=48, summer_start=3000, summer_end=7500):\n    # 夏天约为第 125–312 天（小时序号 3000–7500）\n\n    meter = Xy_subset.meter_id.iloc[0]\n    is_zero = Xy_subset.meter_reading == 0\n    if meter == 0:\n        # 电表几乎不应长期为 0，全部视为坏样本\n        return is_zero\n\n    transitions = (is_zero != is_zero.shift(1))  # 0 ↔ 非 0 的切换点\n    all_sequence_ids = transitions.cumsum()\n    ids = all_sequence_ids[is_zero].rename(\"ids\")\n    if meter in [2, 3]:\n        # 蒸汽/热水夏天关停可以是真 0；非夏天且连续 >=48h 的 0 当坏值\n        keep = set(ids[(Xy_subset.timestamp < summer_start) |\n                       (Xy_subset.timestamp > summer_end)].unique())\n        is_bad = ids.isin(keep) & (ids.map(ids.value_counts()) >= min_interval)\n    elif meter == 1:\n        time_ids = ids.to_frame().join(Xy_subset.timestamp).set_index(\"timestamp\").ids\n        is_bad = ids.map(ids.value_counts()) >= min_interval\n        # 冷水冬天关停：若年初年末同一段 0 贯穿，不删\n        jan_id = time_ids.get(0, False)\n        dec_id = time_ids.get(8283, False)\n        if (jan_id and dec_id and jan_id == time_ids.get(500, False) and\n                dec_id == time_ids.get(8783, False)):\n            is_bad = is_bad & (~(ids.isin(set([jan_id, dec_id]))))\n    else:\n        raise Exception(f\"Unexpected meter type: {meter}\")\n\n    result = is_zero.copy()\n    result.update(is_bad)\n    return result\n\ndef find_bad_zeros(X, y):\n    # 返回仅包含应该删除的行的 Index\n    Xy = X.assign(meter_reading=y, meter_id=X.meter)\n    is_bad_zero = Xy.groupby([\"building_id\", \"meter\"]).apply(make_is_bad_zero)\n    return is_bad_zero[is_bad_zero].index.droplevel([0, 1])\n\ndef find_bad_sitezero(X):\n    # Site 0 电表前期单位是 kBTU 而非 kWh，官方说明这段不可用\n    return X[(X.timestamp < 3378) & (X.site_id == 0) & (X.meter == 0)].index\n\ndef find_bad_building1099(X, y):\n    # building 1099 蒸汽读数异常高，属于已知离群\n    return X[(X.building_id == 1099) & (X.meter == 2) & (y > 3e4)].index\n\ndef find_bad_rows(X, y):\n    return find_bad_zeros(X, y).union(find_bad_sitezero(X)).union(find_bad_building1099(X, y))","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-10-10T19:42:20.747406Z","iopub.execute_input":"2026-10-10T19:42:20.748208Z","iopub.status.idle":"2026-10-10T19:42:20.773725Z","shell.execute_reply.started":"2026-10-10T19:42:20.748163Z","shell.execute_reply":"2026-10-10T19:42:20.771996Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"X, y = combined_data(train,weather_train)\n\nbad_rows = find_bad_rows(X, y)\npd.Series(bad_rows.sort_values()).to_csv(\"rows_to_drop.csv\", header=False, index=False)\n\nX = X.drop(index=bad_rows)\ny = y.reindex_like(X)\n\nX = _add_time_features(X)\nX = compress_dataframe(X)\n# 小时序号已拆成星期/小时，原 timestamp 不再进模型，避免被当成连续大整数\nX = X.drop(columns=\"timestamp\")\n\ndel bad_rows,train,weather_train\ngc.collect();","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-10-10T19:42:20.775167Z","iopub.execute_input":"2026-10-10T19:42:20.775954Z","iopub.status.idle":"2026-10-10T19:43:29.140172Z","shell.execute_reply.started":"2026-10-10T19:42:20.775895Z","shell.execute_reply":"2026-10-10T19:43:29.138662Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# 对齐 RMSLE：log1p(y) 上的 RMSE ≈ 原空间 RMSLE；预测时再 expm1\ny = np.log1p(y)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-10-10T19:43:29.142123Z","iopub.execute_input":"2026-10-10T19:43:29.143464Z","iopub.status.idle":"2026-10-10T19:43:29.533788Z","shell.execute_reply.started":"2026-10-10T19:43:29.143425Z","shell.execute_reply":"2026-10-10T19:43:29.532726Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"params = {\n    'task': 'train',\n    'boosting_type': 'gbdt',\n    'objective': 'regression',\n    'metric': 'rmse',  # 目标已是 log1p，RMSE 即 RMSLE\n    'num_leaves': 40,  # leaf-wise：叶子数比 max_depth 更能约束复杂度\n    'subsample':0.8,\n    'learning_rate': 0.03,\n    'verbose': 1,\n    'lambda_l2':3\n}\n\n# 教学用很小的树数，kernel 能跑完；冲分需要上千 round + early stopping\nnum_trees = 10\n\n# 高基数或二元标记列交给 LGB 的类别分裂，不要当连续值切阈值\ncategorical_features=['building_id', 'site_id', 'primary_use', 'had_air_temperature', 'had_cloud_coverage',\n                      'had_dew_temperature', 'had_precip_depth_1_hr','had_sea_level_pressure', 'had_wind_direction',\n                      'had_wind_speed', 'tm_day_of_week', 'tm_hour_of_day']","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-10-10T19:43:29.537089Z","iopub.execute_input":"2026-10-10T19:43:29.537476Z","iopub.status.idle":"2026-10-10T19:43:29.544702Z","shell.execute_reply.started":"2026-10-10T19:43:29.537446Z","shell.execute_reply":"2026-10-10T19:43:29.543432Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"n_splits = 3\n\n# 四种表计量级和季节性差很大，分 meter 各训一套，比单模型硬吃全部更稳\nfor val in X['meter'].unique():\n    X1 = X[X['meter'] == val].drop(columns=['meter'])\n    kf = StratifiedKFold(n_splits=n_splits, shuffle=True, random_state=42)\n    t = 0\n    for train_index, test_index in kf.split(X1, X1['tm_hour_of_day']):\n        # 用小时分层，让每折昼夜比例接近；严格防泄漏还应按 building 或按月份切\n        train_features = X1.iloc[train_index]\n        train_target = y[X1.iloc[train_index].index]\n\n        test_features = X1.iloc[test_index]\n        test_target = y[X1.iloc[test_index].index]\n\n        d_train = lgb.Dataset(train_features, train_target, categorical_feature=categorical_features)\n        d_eval = lgb.Dataset(test_features,test_target, categorical_feature=categorical_features)\n        print(\"Building model meter :\",val,'fold:',t)\n\n        md = lgb.train(params, d_train, num_boost_round=num_trees, valid_sets=(d_train, d_eval),\n                       callbacks=[lgb.early_stopping(200), lgb.log_evaluation(20)])\n        md.save_model('lgb_val{}_fold{}.bin'.format(val,t))\n        t += 1\n    del X1\n\ndel d_train, d_eval, train_features, test_features, md\ngc.collect();","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-10-10T19:43:29.546084Z","iopub.execute_input":"2026-10-10T19:43:29.546404Z","iopub.status.idle":"2026-10-10T19:43:42.394145Z","shell.execute_reply.started":"2026-10-10T19:43:29.546378Z","shell.execute_reply":"2026-10-10T19:43:42.392369Z"}},"outputs":[],"execution_count":null}]}