{ "cells": [ { "cell_type": "code", "execution_count": 14, "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": 15, "metadata": {}, "outputs": [], "source": [ "sql=\"select day,slot,c_b_count from algo_dwd_station_change_flow where organ_code='qiaozhuang'\"\n", "\n", "change_data=pd.read_sql(sql, db_engine)" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/tmp/ipykernel_3113831/1568745592.py:1: SettingWithCopyWarning: \n", "A value is trying to be set on a copy of a slice from a DataFrame\n", "\n", "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n", " change_data['c_b_count'][change_data['c_b_count']>4]=4\n" ] } ], "source": [ "change_data['c_b_count'][change_data['c_b_count']>4]=4" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(5762, 3)" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "change_data.shape" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
dayslotc_b_count
02022-10-1913:30-13:591
12022-10-1914:00-14:291
22022-10-1914:30-14:591
32022-10-1915:00-15:290
42022-10-1915:30-15:590
\n", "
" ], "text/plain": [ " day slot c_b_count\n", "0 2022-10-19 13:30-13:59 1\n", "1 2022-10-19 14:00-14:29 1\n", "2 2022-10-19 14:30-14:59 1\n", "3 2022-10-19 15:00-15:29 0\n", "4 2022-10-19 15:30-15:59 0" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "change_data.head()" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(5762, 3)" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "change_data.shape" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [], "source": [ "##启动数据日期n0" ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [], "source": [ "n0=48*7" ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [], "source": [ "from statsmodels.tsa.holtwinters import ExponentialSmoothing" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/tmp/ipykernel_3113831/1455405953.py:7: SettingWithCopyWarning: \n", "A value is trying to be set on a copy of a slice from a DataFrame.\n", "Try using .loc[row_indexer,col_indexer] = value instead\n", "\n", "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n", " zz[\"pre\"]=pre\n", "/tmp/ipykernel_3113831/1455405953.py:8: SettingWithCopyWarning: \n", "A value is trying to be set on a copy of a slice from a DataFrame\n", "\n", "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n", " zz[\"pre\"][zz[\"pre\"]>=4]=3\n", "/opt/module/anaconda2022-10/envs/py3916/lib/python3.9/site-packages/pandas/core/generic.py:8870: SettingWithCopyWarning: \n", "A value is trying to be set on a copy of a slice from a DataFrame\n", "\n", "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n", " return self._update_inplace(result)\n", "/tmp/ipykernel_3113831/1455405953.py:9: SettingWithCopyWarning: \n", "A value is trying to be set on a copy of a slice from a DataFrame\n", "\n", "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n", " zz[\"pre\"][zz[\"pre\"]<0]=0\n", "/tmp/ipykernel_3113831/1455405953.py:10: SettingWithCopyWarning: \n", "A value is trying to be set on a copy of a slice from a DataFrame.\n", "Try using .loc[row_indexer,col_indexer] = value instead\n", "\n", "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n", " zz[\"pre\"]=zz[\"pre\"].astype(int)\n", "/tmp/ipykernel_3113831/1455405953.py:11: SettingWithCopyWarning: \n", "A value is trying to be set on a copy of a slice from a DataFrame.\n", "Try using .loc[row_indexer,col_indexer] = value instead\n", "\n", "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n", " zz[\"pre_time\"]=[1,2,3]\n", "/tmp/ipykernel_3113831/1455405953.py:12: SettingWithCopyWarning: \n", "A value is trying to be set on a copy of a slice from a DataFrame.\n", "Try using .loc[row_indexer,col_indexer] = value instead\n", "\n", "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n", " zz[\"organ_code\"]=\"qiaozhuang\"\n", "/tmp/ipykernel_3113831/1455405953.py:13: SettingWithCopyWarning: \n", "A value is trying to be set on a copy of a slice from a DataFrame.\n", "Try using .loc[row_indexer,col_indexer] = value instead\n", "\n", "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n", " zz[\"algo\"]=\"tes\"\n", "/tmp/ipykernel_3113831/1455405953.py:14: SettingWithCopyWarning: \n", "A value is trying to be set on a copy of a slice from a DataFrame.\n", "Try using .loc[row_indexer,col_indexer] = value instead\n", "\n", "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n", " zz[\"data_period\"]=7\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Length of values (3) does not match length of index (2)\n" ] } ], "source": [ "for i in range(len(change_data)-n0-1):\n", " try:\n", " model = ExponentialSmoothing(change_data[\"c_b_count\"][i:n0+i],trend='add',seasonal_periods=48).fit()\n", " out=model.forecast(3)\n", " pre=np.round(np.array(out))\n", " zz=change_data[n0+i:n0+i+3]\n", " zz[\"pre\"]=pre\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\"]=\"qiaozhuang\"\n", " zz[\"algo\"]=\"tes\"\n", " zz[\"data_period\"]=7\n", " zz.to_sql(\"algo_dwd_station_change_static\",con=db_engine, if_exists=\"append\",index=False)\n", " except Exception as e:\n", " print(str(e))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "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 }