data-prepare-1221.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "76dae54b",
   "metadata": {},
   "source": [
    "# 数据预处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1d819788",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-19T13:21:22.942827Z",
     "start_time": "2022-01-19T13:21:22.267275Z"
    }
   },
   "outputs": [],
   "source": [
    "import pymysql\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pdb\n",
    "import seaborn as sns\n",
    "from sqlalchemy import create_engine\n",
    "from matplotlib import pyplot as plt\n",
    "from sklearn.preprocessing import MinMaxScaler\n",
    "from matplotlib.pyplot import MultipleLocator\n",
    "from chinese_calendar import is_holiday, is_workday\n",
    "from statsmodels.tsa.seasonal import STL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46baa793",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-21T12:46:18.857265Z",
     "start_time": "2021-12-21T12:46:18.853256Z"
    }
   },
   "source": [
    "## 数据导入"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "a9c19077",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-21T15:14:52.810445Z",
     "start_time": "2021-12-21T15:14:52.659791Z"
    }
   },
   "outputs": [],
   "source": [
    "table_name = '福建省日负荷与气象数据'\n",
    "engine = create_engine('mysql+pymysql://root:fit4-305@localhost:3306/load_data')\n",
    "data = pd.read_sql_table(table_name=table_name, con=engine, index_col='date')\n",
    "weather_features = data.loc[:, 'temperature':'Humidex'].resample('H').interpolate(method='pad')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "28b1531a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-22T02:50:26.337271Z",
     "start_time": "2021-12-22T02:50:25.275562Z"
    }
   },
   "outputs": [],
   "source": [
    "table_name = '福建省小时负荷'\n",
    "engine = create_engine('mysql+pymysql://root:fit4-305@localhost:3306/load_data')\n",
    "data = pd.read_sql_table(table_name=table_name, con=engine, index_col='time')\n",
    "load = data[['load']].replace('\\\\N', np.nan).astype('float64')\n",
    "load = load.resample('H').interpolate(method='pad')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "9746fbba",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-19T13:31:55.078792Z",
     "start_time": "2022-01-19T13:31:55.010799Z"
    }
   },
   "outputs": [],
   "source": [
    "data = pd.read_csv('./data/dataset_0101500000.csv', sep='\\t', index_col='time')\n",
    "data.index = data.index.astype('datetime64[ns]')\n",
    "weather_features = data.loc[:, 'wea':'tembody']\n",
    "load = data[['load']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "384c6843",
   "metadata": {},
   "source": [
    "## 计算气象负荷"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "4b95bb66",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-24T11:55:22.945839Z",
     "start_time": "2021-12-24T11:55:18.369551Z"
    }
   },
   "outputs": [],
   "source": [
    "load_mean = data['load'].resample('D').mean()\n",
    "stl = STL(load_mean, period=365, robust=True)\n",
    "result_STL = stl.fit()\n",
    "load_trend = result_STL.trend.resample('H').interpolate(method='pad')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f23ad853",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-24T11:44:40.638691Z",
     "start_time": "2021-12-24T11:44:33.295138Z"
    }
   },
   "outputs": [],
   "source": [
    "stl = STL(data[['load']], period=24, robust=True)\n",
    "result_STL = stl.fit()\n",
    "load = data.load - result_STL.trend\n",
    "stl = STL(load, )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4f814a1",
   "metadata": {},
   "source": [
    "## 时间特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "d7ab3d77",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-19T13:28:41.872001Z",
     "start_time": "2022-01-19T13:28:40.717542Z"
    }
   },
   "outputs": [],
   "source": [
    "weather_features.loc[:, 'year'] = weather_features.index.year\n",
    "weather_features.loc[:, 'month'] = weather_features.index.month\n",
    "weather_features.loc[:, 'day'] = weather_features.index.day\n",
    "weather_features.loc[:, 'hour'] = weather_features.index.hour\n",
    "weather_features.loc[:, 'day_of_year'] = weather_features.index.map(lambda x: x.timetuple().tm_yday)\n",
    "weather_features.loc[:, 'week_of_year'] = weather_features.index.map(lambda x: x.isocalendar()[1])\n",
    "weather_features.loc[:, 'day_of_week'] = weather_features.index.map(lambda x: x.timetuple().tm_wday)\n",
    "weather_features.loc[:, 'is_workday'] = weather_features.index.map(lambda x: int(is_workday(x)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af254d23",
   "metadata": {},
   "source": [
    "## 保存数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "8869b16f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-01-19T13:28:46.352024Z",
     "start_time": "2022-01-19T13:28:46.213246Z"
    }
   },
   "outputs": [],
   "source": [
    "data = pd.concat([weather_features, load], axis=1, join='inner')\n",
    "data.index.name = 'time'\n",
    "data.to_csv('./data/tianjin.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.8.12"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "367.188px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}