{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import  pandas  as  pd\n",
    "from pandasql import sqldf \n",
    "from sqlalchemy import create_engine\n",
    "import statsmodels.api as sm \n",
    "import numpy as np\n",
    "\n",
    "mysql_user = 'root'\n",
    "mysql_password = 'qx123456'\n",
    "mysql_host = 'algo-dev-internal.li-ai.com.cn'\n",
    "mysql_port = 3306\n",
    "mysql_db = 'algo'\n",
    "\n",
    "db_engine = create_engine(\"mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8\".format(mysql_user, mysql_password, mysql_host, mysql_port, mysql_db),\n",
    "                            pool_recycle=7200,pool_size=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql=\"select  day,slot,c_b_count,c_e_count  from   algo_dwd_station_sheduling_base\"\n",
    "\n",
    "charge_data=pd.read_sql(sql, db_engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "n0=48*14\n",
    "t=0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(16000):\n",
    "    try:\n",
    "        data_in=charge_data[i:n0+i]\n",
    "        rol_mean = data_in['c_b_count'].rolling(window=48).mean()\n",
    "        rol_mean = rol_mean.dropna()\n",
    "        model = sm.tsa.ARIMA(rol_mean, order=(4,0,0),enforce_stationarity=False) \n",
    "        results = model.fit() \n",
    "        rol_mean_pre=results.forecast(3).values\n",
    "        sum_b1=np.sum(data_in[\"c_b_count\"].tail(47))\n",
    "        sum_b2=np.sum(data_in[\"c_b_count\"].tail(46))\n",
    "        sum_b3=np.sum(data_in[\"c_b_count\"].tail(45))\n",
    "        pre1=rol_mean_pre[0]*48-sum_b1\n",
    "        pre2=rol_mean_pre[1]*48-sum_b2-pre1\n",
    "        pre3=rol_mean_pre[2]*48-sum_b3-pre2\n",
    "        zz=charge_data[n0+i:n0+i+3]\n",
    "        zz[\"pre\"]=[pre1,pre2,pre3]\n",
    "        zz[\"pre\"]=zz[\"pre\"].round()\n",
    "        zz[\"pre\"][zz[\"pre\"]>=4]=3\n",
    "        zz[\"pre\"][zz[\"pre\"]<0]=0\n",
    "        zz[\"pre\"]=zz[\"pre\"].astype(int)\n",
    "        zz[\"pre_time\"]=[1,2,3]\n",
    "        zz[\"organ_code\"]=\"fengdu2\"\n",
    "        zz[\"algo\"]=\"arima\"\n",
    "        zz[\"data_period\"]=14\n",
    "        zz.to_sql(\"algo_dwd_station_sheduling_static\",con=db_engine, if_exists=\"append\",index=False)\n",
    "        t=i\n",
    "        print(t)\n",
    "        #zz_t = zz_t.append(zz)\n",
    "    \n",
    "    except Exception as e:\n",
    "        print(str(e))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py3916",
   "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.9.16"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}