{ "cells": [ { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "from sqlalchemy import text, delete, and_, or_, update\n", "from ZlwlAlgosCommon.utils.ProUtils import *\n", "from ZlwlAlgosCommon.service.iotp.IotpAlgoService import IotpAlgoService\n", "from ZlwlAlgosCommon.service.iotp.Beans import DataField\n", "from ZlwlAlgosCommon.orm.models import *" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "start env: dev \n" ] } ], "source": [ "import datetime,time\n", "cur_env = 'dev' # 设置运行环境\n", "app_path = \"/home/likun/project/zlwl-algos/\" # 设置app绝对路径\n", "sysUtils = SysUtils(cur_env, app_path)\n", "mysql_algo_params = sysUtils.get_cf_param('mysql-algo')\n", "mysqlUtils = MysqlUtils()\n", "mysql_algo_engine, mysql_algo_Session= mysqlUtils.get_mysql_engine(mysql_algo_params)\n", "mysql_algo_conn = mysql_algo_engine.connect()\n", "hbase_params = sysUtils.get_cf_param('hbase-datafactory')\n", "iotp_service = IotpAlgoService(hbase_params=hbase_params)" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [], "source": [ "def delay_num(num_max):\n", " val_num=0\n", " if num_max<=1:\n", " val_num=4\n", " elif num_max>1 and num_max<=2:\n", " val_num=3\n", " elif num_max>2 and num_max<=3:\n", " val_num=1\n", " elif num_max>3:\n", " val_num=0\n", " return val_num" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [], "source": [ "###多机构运算" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [], "source": [ "time_zhui=\"2023-05-12 00:00:00\"\n", "now_time=pd.to_datetime(time_zhui)\n", "date_in=now_time.strftime('%Y-%m-%d')" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "def station_income(date_in,rate_total=192):\n", " ##基于日期统计各个换电站的负载率\n", " sql=\"select organ_code,time_date,sum(charge_in) as charge_in_sum from algo_dwd_sheduling_static where time_date='{}' group by organ_code,time_date \".format(date_in) \n", " charge_in_sum=pd.read_sql(sql, mysql_algo_conn)\n", " charge_in_sum[\"day_load_rate\"]=(charge_in_sum[\"charge_in_sum\"]/rate_total).round(4)\n", " ##进站细节\n", " ##查看负载率细节\n", " sql2=\"select organ_code,time_date, time_slot,charge_in from algo_dwd_sheduling_static where time_date='{}' \".format(date_in)\n", " charge_in=pd.read_sql(sql2, mysql_algo_conn)\n", " qq=[\"20:00-20:29\",\"20:30-20:59\",\"21:00-21:29\",\"21:30-21:59\",\"22:00-22:29\",\"22:30-22:59\"]\n", " charge_in_select=charge_in[charge_in[\"time_slot\"].isin(qq)]\n", " max_values = charge_in_select.groupby(['organ_code','time_date'])['charge_in'].max()\n", " zz_max=max_values.reset_index()\n", " zz_max.columns=[\"organ_code\",\"time_date\",\"charge_in_max\"]\n", " zz_max[\"val_num\"]=zz_max[\"charge_in_max\"].apply(delay_num)\n", " zz_max[\"income\"]=zz_max[\"val_num\"]*(0.42*282)\n", " income=pd.merge(charge_in_sum,zz_max,on=[\"organ_code\",\"time_date\"],how='inner')\n", " income.to_sql(\"algo_exhange_station_income\",con=mysql_algo_conn, if_exists=\"append\",index=False)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def station_income_bms(date_in,rate_total=192):\n", " ##基于日期统计各个换电站的负载率\n", " sql=\"select organ_code,time_date,sum(charge_in) as charge_in_sum from algo_dwd_sheduling_static where time_date='{}' group by organ_code,time_date \".format(date_in) \n", " charge_in_sum=pd.read_sql(sql, mysql_algo_conn)\n", " charge_in_sum[\"day_load_rate\"]=(charge_in_sum[\"charge_in_sum\"]/rate_total).round(4)\n", " ##进站细节\n", " ##查看负载率细节\n", " sql2=\"select organ_code,time_date, time_slot,charge_in+wait_in as charge_in, in_pro_1 as pre from algo_dwd_sheduling_static where time_date='{}' \".format(date_in)\n", " charge_in=pd.read_sql(sql2, mysql_algo_conn)\n", " qq=[\"20:00-20:29\",\"20:30-20:59\",\"21:00-21:29\",\"21:30-21:59\",\"22:00-22:29\",\"22:30-22:59\"]\n", " charge_in_select=charge_in[charge_in[\"time_slot\"].isin(qq)]\n", " #max_values = charge_in_select.groupby(['organ_code','time_date'])['charge_in'].max()\n", " charge_in_select['delta']=charge_in_select['pre']-charge_in_select['charge_in']\n", " charge_in_select['delta_abs']=charge_in_select['delta'].abs()\n", " max_values = charge_in_select.groupby(['organ_code','time_date']).agg({'charge_in':'max',\n", " 'delta':'max',\n", " 'delta':'min',\n", " 'delta_abs':'mean'})\n", " \n", " zz_max=max_values.reset_index()\n", " zz_max.columns=[\"organ_code\",\"time_date\",\"charge_in_max\",\"delta_max\",\"delta_min\",\"delta_abs_mean\"]\n", " zz_max[\"val_num\"]=zz_max[\"charge_in_max\"].apply(delay_num)\n", " zz_max[\"income\"]=zz_max[\"val_num\"]*(0.42*282)\n", " income=pd.merge(charge_in_sum,zz_max,on=[\"organ_code\",\"time_date\"],how='inner')\n", " income.to_sql(\"algo_exhange_station_income\",con=mysql_algo_conn, if_exists=\"append\",index=False)" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [], "source": [ "rng1 = pd.date_range('2023-05-05 00:00:00','2023-05-22 00:00:00' , freq = '0.5H')" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [], "source": [ "st=[i.strftime('%Y-%m-%d %H:%M:%S') for i in rng1]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "for time_zhui in st:\n", " now_time=pd.to_datetime(time_zhui)\n", " date_in=now_time.strftime('%Y-%m-%d')\n", " station_income(date_in,rate_total=192)" ] }, { "cell_type": "code", "execution_count": 41, "metadata": {}, "outputs": [], "source": [ "algo=\"arima\"\n", "data_period=7" ] }, { "cell_type": "code", "execution_count": 54, "metadata": {}, "outputs": [], "source": [ "date_in=\"2022-05-05 00:00:00\"" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def station_income_flow(date_in,rate_total=144,algo='arima',data_period=7):\n", " ##基于日期统计各个换电站的负载率\n", " sql=\"select organ_code,day,sum(c_b_count) as charge_in_sum from algo_dwd_station_sheduling_static \\\n", " where day='{}' and algo='{}' and data_period={} \\\n", " group by organ_code,day \".format(date_in,algo,data_period) \n", " charge_in_sum=pd.read_sql(sql, mysql_algo_conn)\n", " charge_in_sum[\"day_load_rate\"]=(charge_in_sum[\"charge_in_sum\"]/rate_total).round(4)\n", " ##进站细节\n", " ##查看负载率细节\n", " sql2=\"select organ_code,day, slot,c_b_count as charge_in, pre from algo_dwd_station_sheduling_static where day='{}' and algo='{}' and data_period={} \".format(date_in,algo,data_period)\n", " charge_in=pd.read_sql(sql2, mysql_algo_conn)\n", " qq=[\"20:00-20:29\",\"20:30-20:59\",\"21:00-21:29\",\"21:30-21:59\",\"22:00-22:29\",\"22:30-22:59\"]\n", " charge_in_select=charge_in[charge_in[\"slot\"].isin(qq)]\n", " charge_in_select['delta']=charge_in_select['pre']-charge_in_select['charge_in']\n", " charge_in_select['delta_abs']=charge_in_select['delta'].abs()\n", " max_values = charge_in_select.groupby(['organ_code','day']).agg({'charge_in':'max',\n", " 'delta':['max','min'],\n", " 'delta_abs':'mean'})\n", " zz_max=max_values.reset_index()\n", " zz_max.columns=[\"organ_code\",\"day\",\"charge_in_max\",\"delta_max\",\"delta_min\",\"delta_abs_mean\"]\n", " zz_max[\"val_num\"]=zz_max[\"charge_in_max\"].apply(delay_num)\n", " zz_max[\"income\"]=zz_max[\"val_num\"]*(0.42*282)\n", " income=pd.merge(charge_in_sum,zz_max,on=[\"organ_code\",\"day\"],how='inner')\n", " income[\"algo\"]=algo\n", " income[\"data_period\"]=data_period\n", " income.to_sql(\"algo_exhange_station_income_flow\",con=mysql_algo_conn, if_exists=\"append\",index=False)" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [], "source": [ "def station_income_change_flow(date_in,rate_total=144,algo='arima',data_period=14):\n", " ##基于日期统计各个换电站的负载率\n", " sql=\"select organ_code,day,sum(c_b_count) as charge_in_sum from algo_dwd_station_change_static \\\n", " where day='{}' and algo='{}' and data_period={} \\\n", " group by organ_code,day \".format(date_in,algo,data_period) \n", " charge_in_sum=pd.read_sql(sql, mysql_algo_conn)\n", " charge_in_sum[\"day_load_rate\"]=(charge_in_sum[\"charge_in_sum\"]/rate_total).round(4)\n", " ##进站细节\n", " ##查看负载率细节\n", " sql2=\"select organ_code,day, slot,c_b_count as charge_in, pre from algo_dwd_station_change_static where day='{}' and algo='{}' and data_period={} \".format(date_in,algo,data_period)\n", " charge_in=pd.read_sql(sql2, mysql_algo_conn)\n", " qq=[\"20:00-20:29\",\"20:30-20:59\",\"21:00-21:29\",\"21:30-21:59\",\"22:00-22:29\",\"22:30-22:59\"]\n", " charge_in_select=charge_in[charge_in[\"slot\"].isin(qq)]\n", " charge_in_select['delta']=charge_in_select['pre']-charge_in_select['charge_in']\n", " charge_in_select['delta_abs']=charge_in_select['delta'].abs()\n", " max_values = charge_in_select.groupby(['organ_code','day']).agg({'charge_in':'max',\n", " 'delta':['max','min'],\n", " 'delta_abs':'mean'})\n", " zz_max=max_values.reset_index()\n", " zz_max.columns=[\"organ_code\",\"day\",\"charge_in_max\",\"delta_max\",\"delta_min\",\"delta_abs_mean\"]\n", " zz_max[\"val_num\"]=zz_max[\"charge_in_max\"].apply(delay_num)\n", " zz_max[\"income\"]=zz_max[\"val_num\"]*(0.42*282)\n", " income=pd.merge(charge_in_sum,zz_max,on=[\"organ_code\",\"day\"],how='inner')\n", " income[\"algo\"]=algo\n", " income[\"data_period\"]=data_period\n", " income.to_sql(\"algo_exhange_station_income_flow\",con=mysql_algo_conn, if_exists=\"append\",index=False)" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [], "source": [ "rng1 = pd.date_range('2022-03-10 00:00:00','2023-02-6 00:00:00' , freq = '24H')" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [], "source": [ "st=[i.strftime('%Y-%m-%d %H:%M:%S') for i in rng1]" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/tmp/ipykernel_602853/359384269.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", " charge_in_select['delta']=charge_in_select['pre']-charge_in_select['charge_in']\n", "/tmp/ipykernel_602853/359384269.py:15: 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", " charge_in_select['delta_abs']=charge_in_select['delta'].abs()\n" ] } ], "source": [ "for time_zhui in st:\n", " now_time=pd.to_datetime(time_zhui)\n", " date_in=now_time.strftime('%Y-%m-%d')\n", " station_income_change_flow(date_in,rate_total=192,algo='arima',data_period=14)" ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [], "source": [ "##撬装站" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [], "source": [ "def station_income_change_flow(date_in,rate_total=144,algo='tes',data_period=3,pre_time=1,organ_code=\"qiaozhuang\"):\n", " ##基于日期统计各个换电站的负载率\n", " sql=\"select organ_code,day,sum(c_b_count) as charge_in_sum from algo_dwd_station_change_static \\\n", " where day='{}' and algo='{}' and data_period={} and pre_time={} and organ_code='{}' \\\n", " group by organ_code,day \".format(date_in,algo,data_period,pre_time,organ_code) \n", " charge_in_sum=pd.read_sql(sql, mysql_algo_conn)\n", " charge_in_sum[\"day_load_rate\"]=(charge_in_sum[\"charge_in_sum\"]/rate_total).round(4)\n", " ##进站细节\n", " ##查看负载率细节\n", " sql2=\"select organ_code,day, slot,c_b_count as charge_in, pre from algo_dwd_station_change_static where day='{}' and algo='{}' and data_period={} and pre_time={} and organ_code='{}' \".format(date_in,algo,data_period,pre_time,organ_code)\n", " charge_in=pd.read_sql(sql2, mysql_algo_conn)\n", " qq=[\"20:00-20:29\",\"20:30-20:59\",\"21:00-21:29\",\"21:30-21:59\",\"22:00-22:29\",\"22:30-22:59\"]\n", " charge_in_select=charge_in[charge_in[\"slot\"].isin(qq)]\n", " charge_in_select['delta']=charge_in_select['pre']-charge_in_select['charge_in']\n", " charge_in_select['delta_abs']=charge_in_select['delta'].abs()\n", " max_values = charge_in_select.groupby(['organ_code','day']).agg({'charge_in':'max',\n", " 'delta':['max','min'],\n", " 'delta_abs':'mean'})\n", " zz_max=max_values.reset_index()\n", " zz_max.columns=[\"organ_code\",\"day\",\"charge_in_max\",\"delta_max\",\"delta_min\",\"delta_abs_mean\"]\n", " zz_max[\"val_num\"]=zz_max[\"charge_in_max\"].apply(delay_num)\n", " zz_max[\"income\"]=zz_max[\"val_num\"]*(0.42*282)\n", " income=pd.merge(charge_in_sum,zz_max,on=[\"organ_code\",\"day\"],how='inner')\n", " income[\"algo\"]=algo\n", " income[\"data_period\"]=data_period\n", " income[\"pre_time\"]=pre_time\n", " income.to_sql(\"algo_exhange_station_income_flow\",con=mysql_algo_conn, if_exists=\"append\",index=False)" ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [], "source": [ "rng1 = pd.date_range('2022-02-24 00:00:00','2023-02-13 00:00:00' , freq = '24H')" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [], "source": [ "st=[i.strftime('%Y-%m-%d %H:%M:%S') for i in rng1]" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/tmp/ipykernel_1493879/4166819398.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", " charge_in_select['delta']=charge_in_select['pre']-charge_in_select['charge_in']\n", "/tmp/ipykernel_1493879/4166819398.py:15: 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", " charge_in_select['delta_abs']=charge_in_select['delta'].abs()\n" ] }, { "ename": "ValueError", "evalue": "Wrong number of items passed 2, placement implies 1", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mKeyError\u001b[0m Traceback (most recent call last)", "File \u001b[0;32m/opt/module/anaconda2022-10/envs/py3916/lib/python3.9/site-packages/pandas/core/indexes/base.py:3361\u001b[0m, in \u001b[0;36mIndex.get_loc\u001b[0;34m(self, key, method, tolerance)\u001b[0m\n\u001b[1;32m 3360\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[0;32m-> 3361\u001b[0m \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_engine\u001b[39m.\u001b[39;49mget_loc(casted_key)\n\u001b[1;32m 3362\u001b[0m \u001b[39mexcept\u001b[39;00m \u001b[39mKeyError\u001b[39;00m \u001b[39mas\u001b[39;00m err:\n", "File \u001b[0;32m/opt/module/anaconda2022-10/envs/py3916/lib/python3.9/site-packages/pandas/_libs/index.pyx:76\u001b[0m, in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n", "File \u001b[0;32m/opt/module/anaconda2022-10/envs/py3916/lib/python3.9/site-packages/pandas/_libs/index.pyx:108\u001b[0m, in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n", "File \u001b[0;32mpandas/_libs/hashtable_class_helper.pxi:5198\u001b[0m, in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n", "File \u001b[0;32mpandas/_libs/hashtable_class_helper.pxi:5206\u001b[0m, in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n", "\u001b[0;31mKeyError\u001b[0m: 'val_num'", "\nThe above exception was the direct cause of the following exception:\n", "\u001b[0;31mKeyError\u001b[0m Traceback (most recent call last)", "File \u001b[0;32m/opt/module/anaconda2022-10/envs/py3916/lib/python3.9/site-packages/pandas/core/frame.py:3746\u001b[0m, in \u001b[0;36mDataFrame._set_item_mgr\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m 3745\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[0;32m-> 3746\u001b[0m loc \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_info_axis\u001b[39m.\u001b[39;49mget_loc(key)\n\u001b[1;32m 3747\u001b[0m \u001b[39mexcept\u001b[39;00m \u001b[39mKeyError\u001b[39;00m:\n\u001b[1;32m 3748\u001b[0m \u001b[39m# This item wasn't present, just insert at end\u001b[39;00m\n", "File \u001b[0;32m/opt/module/anaconda2022-10/envs/py3916/lib/python3.9/site-packages/pandas/core/indexes/base.py:3363\u001b[0m, in \u001b[0;36mIndex.get_loc\u001b[0;34m(self, key, method, tolerance)\u001b[0m\n\u001b[1;32m 3362\u001b[0m \u001b[39mexcept\u001b[39;00m \u001b[39mKeyError\u001b[39;00m \u001b[39mas\u001b[39;00m err:\n\u001b[0;32m-> 3363\u001b[0m \u001b[39mraise\u001b[39;00m \u001b[39mKeyError\u001b[39;00m(key) \u001b[39mfrom\u001b[39;00m \u001b[39merr\u001b[39;00m\n\u001b[1;32m 3365\u001b[0m \u001b[39mif\u001b[39;00m is_scalar(key) \u001b[39mand\u001b[39;00m isna(key) \u001b[39mand\u001b[39;00m \u001b[39mnot\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mhasnans:\n", "\u001b[0;31mKeyError\u001b[0m: 'val_num'", "\nDuring handling of the above exception, another exception occurred:\n", "\u001b[0;31mValueError\u001b[0m Traceback (most recent call last)", "Cell \u001b[0;32mIn[32], line 4\u001b[0m\n\u001b[1;32m 2\u001b[0m now_time\u001b[39m=\u001b[39mpd\u001b[39m.\u001b[39mto_datetime(time_zhui)\n\u001b[1;32m 3\u001b[0m date_in\u001b[39m=\u001b[39mnow_time\u001b[39m.\u001b[39mstrftime(\u001b[39m'\u001b[39m\u001b[39m%\u001b[39m\u001b[39mY-\u001b[39m\u001b[39m%\u001b[39m\u001b[39mm-\u001b[39m\u001b[39m%d\u001b[39;00m\u001b[39m'\u001b[39m)\n\u001b[0;32m----> 4\u001b[0m station_income_change_flow(date_in,rate_total\u001b[39m=\u001b[39;49m\u001b[39m144\u001b[39;49m,algo\u001b[39m=\u001b[39;49m\u001b[39m'\u001b[39;49m\u001b[39mtes\u001b[39;49m\u001b[39m'\u001b[39;49m,data_period\u001b[39m=\u001b[39;49m\u001b[39m3\u001b[39;49m,pre_time\u001b[39m=\u001b[39;49m\u001b[39m1\u001b[39;49m,organ_code\u001b[39m=\u001b[39;49m\u001b[39m\"\u001b[39;49m\u001b[39mfengdu2\u001b[39;49m\u001b[39m\"\u001b[39;49m)\n", "Cell \u001b[0;32mIn[31], line 21\u001b[0m, in \u001b[0;36mstation_income_change_flow\u001b[0;34m(date_in, rate_total, algo, data_period, pre_time, organ_code)\u001b[0m\n\u001b[1;32m 19\u001b[0m zz_max\u001b[39m=\u001b[39mmax_values\u001b[39m.\u001b[39mreset_index()\n\u001b[1;32m 20\u001b[0m zz_max\u001b[39m.\u001b[39mcolumns\u001b[39m=\u001b[39m[\u001b[39m\"\u001b[39m\u001b[39morgan_code\u001b[39m\u001b[39m\"\u001b[39m,\u001b[39m\"\u001b[39m\u001b[39mday\u001b[39m\u001b[39m\"\u001b[39m,\u001b[39m\"\u001b[39m\u001b[39mcharge_in_s_sum\u001b[39m\u001b[39m\"\u001b[39m,\u001b[39m\"\u001b[39m\u001b[39mcharge_in_max\u001b[39m\u001b[39m\"\u001b[39m,\u001b[39m\"\u001b[39m\u001b[39mdelta_max\u001b[39m\u001b[39m\"\u001b[39m,\u001b[39m\"\u001b[39m\u001b[39mdelta_min\u001b[39m\u001b[39m\"\u001b[39m,\u001b[39m\"\u001b[39m\u001b[39mdelta_abs_mean\u001b[39m\u001b[39m\"\u001b[39m]\n\u001b[0;32m---> 21\u001b[0m zz_max[\u001b[39m\"\u001b[39;49m\u001b[39mval_num\u001b[39;49m\u001b[39m\"\u001b[39;49m]\u001b[39m=\u001b[39mzz_max[[\u001b[39m\"\u001b[39m\u001b[39mcharge_in_s_sum\u001b[39m\u001b[39m\"\u001b[39m,\u001b[39m\"\u001b[39m\u001b[39mcharge_in_max\u001b[39m\u001b[39m\"\u001b[39m]]\u001b[39m.\u001b[39mapply(delay_num2,axis\u001b[39m=\u001b[39m\u001b[39m1\u001b[39m)\n\u001b[1;32m 22\u001b[0m zz_max[\u001b[39m\"\u001b[39m\u001b[39mincome\u001b[39m\u001b[39m\"\u001b[39m]\u001b[39m=\u001b[39mzz_max[\u001b[39m\"\u001b[39m\u001b[39mval_num\u001b[39m\u001b[39m\"\u001b[39m]\u001b[39m*\u001b[39m(\u001b[39m0.42\u001b[39m\u001b[39m*\u001b[39m\u001b[39m282\u001b[39m)\n\u001b[1;32m 23\u001b[0m income\u001b[39m=\u001b[39mpd\u001b[39m.\u001b[39mmerge(charge_in_sum,zz_max,on\u001b[39m=\u001b[39m[\u001b[39m\"\u001b[39m\u001b[39morgan_code\u001b[39m\u001b[39m\"\u001b[39m,\u001b[39m\"\u001b[39m\u001b[39mday\u001b[39m\u001b[39m\"\u001b[39m],how\u001b[39m=\u001b[39m\u001b[39m'\u001b[39m\u001b[39minner\u001b[39m\u001b[39m'\u001b[39m)\n", "File \u001b[0;32m/opt/module/anaconda2022-10/envs/py3916/lib/python3.9/site-packages/pandas/core/frame.py:3599\u001b[0m, in \u001b[0;36mDataFrame.__setitem__\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m 3597\u001b[0m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_setitem_array(key, value)\n\u001b[1;32m 3598\u001b[0m \u001b[39melif\u001b[39;00m \u001b[39misinstance\u001b[39m(value, DataFrame):\n\u001b[0;32m-> 3599\u001b[0m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_set_item_frame_value(key, value)\n\u001b[1;32m 3600\u001b[0m \u001b[39melif\u001b[39;00m is_list_like(value) \u001b[39mand\u001b[39;00m \u001b[39m1\u001b[39m \u001b[39m<\u001b[39m \u001b[39mlen\u001b[39m(\n\u001b[1;32m 3601\u001b[0m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mcolumns\u001b[39m.\u001b[39mget_indexer_for([key])\n\u001b[1;32m 3602\u001b[0m ) \u001b[39m==\u001b[39m \u001b[39mlen\u001b[39m(value):\n\u001b[1;32m 3603\u001b[0m \u001b[39m# Column to set is duplicated\u001b[39;00m\n\u001b[1;32m 3604\u001b[0m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_setitem_array([key], value)\n", "File \u001b[0;32m/opt/module/anaconda2022-10/envs/py3916/lib/python3.9/site-packages/pandas/core/frame.py:3737\u001b[0m, in \u001b[0;36mDataFrame._set_item_frame_value\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m 3735\u001b[0m \u001b[39m# now align rows\u001b[39;00m\n\u001b[1;32m 3736\u001b[0m arraylike \u001b[39m=\u001b[39m _reindex_for_setitem(value, \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mindex)\n\u001b[0;32m-> 3737\u001b[0m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_set_item_mgr(key, arraylike)\n", "File \u001b[0;32m/opt/module/anaconda2022-10/envs/py3916/lib/python3.9/site-packages/pandas/core/frame.py:3749\u001b[0m, in \u001b[0;36mDataFrame._set_item_mgr\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m 3746\u001b[0m loc \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_info_axis\u001b[39m.\u001b[39mget_loc(key)\n\u001b[1;32m 3747\u001b[0m \u001b[39mexcept\u001b[39;00m \u001b[39mKeyError\u001b[39;00m:\n\u001b[1;32m 3748\u001b[0m \u001b[39m# This item wasn't present, just insert at end\u001b[39;00m\n\u001b[0;32m-> 3749\u001b[0m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_mgr\u001b[39m.\u001b[39;49minsert(\u001b[39mlen\u001b[39;49m(\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_info_axis), key, value)\n\u001b[1;32m 3750\u001b[0m \u001b[39melse\u001b[39;00m:\n\u001b[1;32m 3751\u001b[0m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_iset_item_mgr(loc, value)\n", "File \u001b[0;32m/opt/module/anaconda2022-10/envs/py3916/lib/python3.9/site-packages/pandas/core/internals/managers.py:1162\u001b[0m, in \u001b[0;36mBlockManager.insert\u001b[0;34m(self, loc, item, value)\u001b[0m\n\u001b[1;32m 1159\u001b[0m \u001b[39melse\u001b[39;00m:\n\u001b[1;32m 1160\u001b[0m value \u001b[39m=\u001b[39m ensure_block_shape(value, ndim\u001b[39m=\u001b[39m\u001b[39mself\u001b[39m\u001b[39m.\u001b[39mndim)\n\u001b[0;32m-> 1162\u001b[0m block \u001b[39m=\u001b[39m new_block(values\u001b[39m=\u001b[39;49mvalue, ndim\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mndim, placement\u001b[39m=\u001b[39;49m\u001b[39mslice\u001b[39;49m(loc, loc \u001b[39m+\u001b[39;49m \u001b[39m1\u001b[39;49m))\n\u001b[1;32m 1164\u001b[0m \u001b[39mfor\u001b[39;00m blkno, count \u001b[39min\u001b[39;00m _fast_count_smallints(\u001b[39mself\u001b[39m\u001b[39m.\u001b[39mblknos[loc:]):\n\u001b[1;32m 1165\u001b[0m blk \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mblocks[blkno]\n", "File \u001b[0;32m/opt/module/anaconda2022-10/envs/py3916/lib/python3.9/site-packages/pandas/core/internals/blocks.py:1932\u001b[0m, in \u001b[0;36mnew_block\u001b[0;34m(values, placement, ndim, klass)\u001b[0m\n\u001b[1;32m 1929\u001b[0m placement \u001b[39m=\u001b[39m BlockPlacement(placement)\n\u001b[1;32m 1931\u001b[0m values, _ \u001b[39m=\u001b[39m extract_pandas_array(values, \u001b[39mNone\u001b[39;00m, ndim)\n\u001b[0;32m-> 1932\u001b[0m check_ndim(values, placement, ndim)\n\u001b[1;32m 1934\u001b[0m \u001b[39mif\u001b[39;00m klass \u001b[39mis\u001b[39;00m \u001b[39mNone\u001b[39;00m:\n\u001b[1;32m 1935\u001b[0m klass \u001b[39m=\u001b[39m get_block_type(values, values\u001b[39m.\u001b[39mdtype)\n", "File \u001b[0;32m/opt/module/anaconda2022-10/envs/py3916/lib/python3.9/site-packages/pandas/core/internals/blocks.py:1974\u001b[0m, in \u001b[0;36mcheck_ndim\u001b[0;34m(values, placement, ndim)\u001b[0m\n\u001b[1;32m 1969\u001b[0m \u001b[39mraise\u001b[39;00m \u001b[39mValueError\u001b[39;00m(\n\u001b[1;32m 1970\u001b[0m \u001b[39m\"\u001b[39m\u001b[39mWrong number of dimensions. \u001b[39m\u001b[39m\"\u001b[39m\n\u001b[1;32m 1971\u001b[0m \u001b[39mf\u001b[39m\u001b[39m\"\u001b[39m\u001b[39mvalues.ndim != ndim [\u001b[39m\u001b[39m{\u001b[39;00mvalues\u001b[39m.\u001b[39mndim\u001b[39m}\u001b[39;00m\u001b[39m != \u001b[39m\u001b[39m{\u001b[39;00mndim\u001b[39m}\u001b[39;00m\u001b[39m]\u001b[39m\u001b[39m\"\u001b[39m\n\u001b[1;32m 1972\u001b[0m )\n\u001b[1;32m 1973\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mlen\u001b[39m(placement) \u001b[39m!=\u001b[39m \u001b[39mlen\u001b[39m(values):\n\u001b[0;32m-> 1974\u001b[0m \u001b[39mraise\u001b[39;00m \u001b[39mValueError\u001b[39;00m(\n\u001b[1;32m 1975\u001b[0m \u001b[39mf\u001b[39m\u001b[39m\"\u001b[39m\u001b[39mWrong number of items passed \u001b[39m\u001b[39m{\u001b[39;00m\u001b[39mlen\u001b[39m(values)\u001b[39m}\u001b[39;00m\u001b[39m, \u001b[39m\u001b[39m\"\u001b[39m\n\u001b[1;32m 1976\u001b[0m \u001b[39mf\u001b[39m\u001b[39m\"\u001b[39m\u001b[39mplacement implies \u001b[39m\u001b[39m{\u001b[39;00m\u001b[39mlen\u001b[39m(placement)\u001b[39m}\u001b[39;00m\u001b[39m\"\u001b[39m\n\u001b[1;32m 1977\u001b[0m )\n\u001b[1;32m 1978\u001b[0m \u001b[39melif\u001b[39;00m ndim \u001b[39m==\u001b[39m \u001b[39m2\u001b[39m \u001b[39mand\u001b[39;00m \u001b[39mlen\u001b[39m(placement) \u001b[39m!=\u001b[39m \u001b[39m1\u001b[39m:\n\u001b[1;32m 1979\u001b[0m \u001b[39m# TODO(EA2D): special case unnecessary with 2D EAs\u001b[39;00m\n\u001b[1;32m 1980\u001b[0m \u001b[39mraise\u001b[39;00m \u001b[39mValueError\u001b[39;00m(\u001b[39m\"\u001b[39m\u001b[39mneed to split\u001b[39m\u001b[39m\"\u001b[39m)\n", "\u001b[0;31mValueError\u001b[0m: Wrong number of items passed 2, placement implies 1" ] } ], "source": [ "for time_zhui in st:\n", " now_time=pd.to_datetime(time_zhui)\n", " date_in=now_time.strftime('%Y-%m-%d')\n", " station_income_change_flow(date_in,rate_total=144,algo='tes',data_period=3,pre_time=1,organ_code=\"fengdu2\")" ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [], "source": [ "def delay_num2(zz_max):\n", " val_num=0\n", " if zz_max[\"charge_in_max\"]<=1:\n", " val_num=4\n", " elif zz_max[\"charge_in_max\"]>1 and zz_max[\"charge_in_max\"]<=2:\n", " val_num=3\n", " elif zz_max[\"charge_in_max\"]>2 and zz_max[\"charge_in_s_sum\"]<=12:\n", " val_num=2\n", " elif zz_max[\"charge_in_max\"]>2 and zz_max[\"charge_in_s_sum\"]>12 and zz_max[\"charge_in_s_sum\"]<=18:\n", " val_num=1\n", " elif zz_max[\"charge_in_max\"]>3 and zz_max[\"charge_in_s_sum\"]>18:\n", " val_num=0\n", " return val_num" ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [], "source": [ "def station_income_change_flow(date_in,rate_total=192,algo='tes',data_period=3,pre_time=1,organ_code=\"qiaozhuang\"):\n", " ##基于日期统计各个换电站的负载率\n", " sql=\"select organ_code,day,sum(c_b_count) as charge_in_sum from algo_dwd_station_change_static \\\n", " where day='{}' and algo='{}' and data_period={} and pre_time={} and organ_code='{}' \\\n", " group by organ_code,day \".format(date_in,algo,data_period,pre_time,organ_code) \n", " charge_in_sum=pd.read_sql(sql, mysql_algo_conn)\n", " charge_in_sum[\"day_load_rate\"]=(charge_in_sum[\"charge_in_sum\"]/rate_total).round(4)\n", " ##进站细节\n", " ##查看负载率细节\n", " sql2=\"select organ_code,day, slot,c_b_count as charge_in, pre from algo_dwd_station_change_static where day='{}' and algo='{}' and data_period={} and pre_time={} and organ_code='{}' \".format(date_in,algo,data_period,pre_time,organ_code)\n", " charge_in=pd.read_sql(sql2, mysql_algo_conn)\n", " qq=[\"20:00-20:29\",\"20:30-20:59\",\"21:00-21:29\",\"21:30-21:59\",\"22:00-22:29\",\"22:30-22:59\"]\n", " charge_in_select=charge_in[charge_in[\"slot\"].isin(qq)]\n", " charge_in_select['delta']=charge_in_select['pre']-charge_in_select['charge_in']\n", " charge_in_select['delta_abs']=charge_in_select['delta'].abs()\n", " max_values = charge_in_select.groupby(['organ_code','day']).agg({'charge_in':['sum','max'],\n", " 'delta':['max','min'],\n", " 'delta_abs':'mean'})\n", " zz_max=max_values.reset_index()\n", " zz_max.columns=[\"organ_code\",\"day\",\"charge_in_s_sum\",\"charge_in_max\",\"delta_max\",\"delta_min\",\"delta_abs_mean\"]\n", " zz_max[\"val_num\"]=zz_max[[\"charge_in_s_sum\",\"charge_in_max\"]].apply(delay_num2,axis=1)\n", " zz_max[\"income\"]=zz_max[\"val_num\"]*(0.42*282)\n", " income=pd.merge(charge_in_sum,zz_max,on=[\"organ_code\",\"day\"],how='inner')\n", " income[\"algo\"]=algo\n", " income[\"data_period\"]=data_period\n", " income[\"pre_time\"]=pre_time\n", " income.to_sql(\"algo_exhange_station_income_flow\",con=mysql_algo_conn, if_exists=\"append\",index=False)" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0.0225" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "0.15*0.15" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "29562.624609375" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "45480.9609375*0.65" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "31641.246652221682" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "48678.84100341797*0.65" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "26895.054" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "31641.24*0.85" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "6.929595596755504" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "5980241/863000" ] }, { "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 }