{ "cells": [ { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "start env: dev \n" ] } ], "source": [ "from datetime import datetime\n", "import time\n", "from ZlwlAlgosCommon.service.iotp.IotpAlgoService import IotpAlgoService\n", "from ZlwlAlgosCommon.utils.ProUtils import *\n", "cur_env = 'dev' # 设置运行环境\n", "app_path = \"/home/songpengfei/project/zlwl-algos/\" # 设置app绝对路径\n", "sysUtils = SysUtils(cur_env, app_path)\n", "# mysql\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", "\n", "kafka_topic_key = 'topic_task_min_10'\n", "kafka_groupid_key = 'group_id_task_min_10'\n", "\n", "hbase_params = sysUtils.get_cf_param('hbase') #-datafactory\n", "iotp_service = IotpAlgoService(hbase_params=hbase_params)\n", "\n", "kafka_params = sysUtils.get_cf_param('kafka')\n", "kafkaUtils = KafkaUtils()\n", "kafka_consumer = kafkaUtils.get_kafka_consumer(kafka_params, kafka_topic_key, kafka_groupid_key, client_id=kafka_topic_key)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from ZlwlAlgosCommon.service.iotp.IotpAlgoService import IotpAlgoService\n", "from ZlwlAlgosCommon.service.iotp.Beans import DataField\n", "columns = [ DataField.time, DataField.sn, DataField.pack_crnt, DataField.pack_volt, DataField.pack_soc, \n", " DataField.cell_voltage_count, DataField.cell_temp_count, DataField.cell_voltage, DataField.cell_temp, \n", " DataField.other_temp_value, DataField.cell_balance, DataField.bms_sta, DataField.ins_resis]\n", "sn_list = ['662CC700015','662CC700060','662CC700040','662CC700050','667CC900002','667CC900001']\n", "start_time = '2023-08-01 00:00:00'\n", "end_time = '2023-02-20 00:00:00'\n", "# df_data = iotp_service.get_data(sn_list=sn_list, columns=columns, start_time=start_time, end_time=end_time)" ] }, { "cell_type": "code", "execution_count": 15, "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", " \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", " \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", " \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", " \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", " \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", " \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", " \n", " \n", " \n", "
idpack_codeparamcreate_timecreate_byupdate_byis_deleteupdate_time
01KY01410{\"PackFullChrgVolt\":\"58\",\\r\\n\"CellFullChrgVolt...2022-12-04 00:00:00NoneNone0.02000-01-01 00:00:00
12KY01710{\"PackFullChrgVolt\":\"69.9\",\\r\\n\"CellFullChrgVo...2022-12-04 00:00:00NoneNone0.02000-01-01 00:00:00
23KY02010{\"PackFullChrgVolt\":\"69.9\",\\r\\n\"CellFullChrgVo...2022-12-04 00:00:00NoneNone0.02000-01-01 00:00:00
34LS0201A{\"PackFullChrgVolt\":\"80\",\\r\\n\"CellFullChrgVolt...2022-12-04 00:00:00NoneNone0.02000-01-01 00:00:00
45GM02010{\"PackFullChrgVolt\":\"80\",\\r\\n\"CellFullChrgVolt...2022-12-04 00:00:00NoneNone0.02000-01-01 00:00:00
56JM38420{\\r\\n\\t\"PackFullChrgVolt\": \"700\",\\r\\n\\t\"CellFu...2022-12-04 00:00:00NoneNone0.02000-01-01 00:00:00
67GM02011{\"PackFullChrgVolt\":\"80\",\\r\\n\"CellFullChrgVolt...2022-12-29 23:00:57NoneNoneNaN2022-12-29 23:01:03
78HY02520{\"PackFullChrgVolt\":\"80\",\\r\\n\"CellFullChrgVolt...NaTNoneNoneNaNNaT
89HY01620{\"PackFullChrgVolt\":\"58.7\",\\r\\n\"CellFullChrgVo...NaTNoneNoneNaNNaT
910JX19220{\\r\\n\\t\"PackFullChrgVolt\": \"700\",\\r\\n\\t\"CellFu...2022-12-04 00:00:00NoneNone0.02000-01-01 00:00:00
1011JX18020{\\r\\n\\t\"PackFullChrgVolt\": \"700\",\\r\\n\\t\"CellFu...2023-04-24 00:00:00NoneNone0.02023-04-24 00:00:00
11122101TS{\"charge_ocv_soc\":[0.0000,5.0000,10.0000,15.00...NaTNoneNoneNaNNaT
1213CL3282A{\"PackFullChrgVolt\":\"700\",\"CellFullChrgVolt\":\"...2023-04-18 00:00:00NoneNone0.02000-01-01 00:00:00
1314GY0172A{\"PackFullChrgVolt\":\"69.9\",\\r\\n\"CellFullChrgVo...2023-05-30 00:00:00NoneNone0.02023-05-30 00:00:00
1415HY0252A{\"PackFullChrgVolt\":\"80\",\\r\\n\"CellFullChrgVolt...NaTNoneNoneNaNNaT
1516ZL19220{\\r\\n\\t\"PackFullChrgVolt\": \"700\",\\r\\n\\t\"CellFu...NaTNoneNoneNaNNaT
1617CL19220{\\r\\n\\t\"PackFullChrgVolt\": \"700\",\\r\\n\\t\"CellFu...NaTNoneNoneNaNNaT
1718XMB-1{\\r\\n\\t\"PackFullChrgVolt\": \"432\",\\r\\n\\t\"CellFu...NaTNoneNoneNaNNaT
1819XMB-2{\\r\\n\\t\"PackFullChrgVolt\": \"432\",\\r\\n\\t\"CellFu...NaTNoneNoneNaNNaT
1920XML{\"PackFullChrgVolt\":\"504\",\\r\\n\"CellFullChrgVol...NaTNoneadmin0.02023-08-08 19:08:19
2021XMQ{\"PackFullChrgVolt\":\"436\",\\r\\n\"CellFullChrgVol...NaTNoneadmin0.02023-08-08 19:08:19
2122XMW{\"PackFullChrgVolt\":\"408\",\\r\\n\"CellFullChrgVol...NaTNoneadmin0.02023-08-08 19:08:19
2223XMS{\"PackFullChrgVolt\":\"412\",\\r\\n\"CellFullChrgVol...NaTNoneadmin0.02023-08-08 19:08:19
2324XMR{\"PackFullChrgVolt\":\"442\",\\r\\n\"CellFullChrgVol...NaTNoneadmin0.02023-08-08 19:08:19
2425XMY{\"PackFullChrgVolt\":\"403\",\\r\\n\"CellFullChrgVol...NaTNoneadmin0.02023-08-08 19:08:19
2526XP01{\"PackFullChrgVolt\":\"414\",\\r\\n\"CellFullChrgVol...NaTNoneadmin0.02023-08-08 19:08:19
\n", "
" ], "text/plain": [ " id pack_code param \\\n", "0 1 KY01410 {\"PackFullChrgVolt\":\"58\",\\r\\n\"CellFullChrgVolt... \n", "1 2 KY01710 {\"PackFullChrgVolt\":\"69.9\",\\r\\n\"CellFullChrgVo... \n", "2 3 KY02010 {\"PackFullChrgVolt\":\"69.9\",\\r\\n\"CellFullChrgVo... \n", "3 4 LS0201A {\"PackFullChrgVolt\":\"80\",\\r\\n\"CellFullChrgVolt... \n", "4 5 GM02010 {\"PackFullChrgVolt\":\"80\",\\r\\n\"CellFullChrgVolt... \n", "5 6 JM38420 {\\r\\n\\t\"PackFullChrgVolt\": \"700\",\\r\\n\\t\"CellFu... \n", "6 7 GM02011 {\"PackFullChrgVolt\":\"80\",\\r\\n\"CellFullChrgVolt... \n", "7 8 HY02520 {\"PackFullChrgVolt\":\"80\",\\r\\n\"CellFullChrgVolt... \n", "8 9 HY01620 {\"PackFullChrgVolt\":\"58.7\",\\r\\n\"CellFullChrgVo... \n", "9 10 JX19220 {\\r\\n\\t\"PackFullChrgVolt\": \"700\",\\r\\n\\t\"CellFu... \n", "10 11 JX18020 {\\r\\n\\t\"PackFullChrgVolt\": \"700\",\\r\\n\\t\"CellFu... \n", "11 12 2101TS {\"charge_ocv_soc\":[0.0000,5.0000,10.0000,15.00... \n", "12 13 CL3282A {\"PackFullChrgVolt\":\"700\",\"CellFullChrgVolt\":\"... \n", "13 14 GY0172A {\"PackFullChrgVolt\":\"69.9\",\\r\\n\"CellFullChrgVo... \n", "14 15 HY0252A {\"PackFullChrgVolt\":\"80\",\\r\\n\"CellFullChrgVolt... \n", "15 16 ZL19220 {\\r\\n\\t\"PackFullChrgVolt\": \"700\",\\r\\n\\t\"CellFu... \n", "16 17 CL19220 {\\r\\n\\t\"PackFullChrgVolt\": \"700\",\\r\\n\\t\"CellFu... \n", "17 18 XMB-1 {\\r\\n\\t\"PackFullChrgVolt\": \"432\",\\r\\n\\t\"CellFu... \n", "18 19 XMB-2 {\\r\\n\\t\"PackFullChrgVolt\": \"432\",\\r\\n\\t\"CellFu... \n", "19 20 XML {\"PackFullChrgVolt\":\"504\",\\r\\n\"CellFullChrgVol... \n", "20 21 XMQ {\"PackFullChrgVolt\":\"436\",\\r\\n\"CellFullChrgVol... \n", "21 22 XMW {\"PackFullChrgVolt\":\"408\",\\r\\n\"CellFullChrgVol... \n", "22 23 XMS {\"PackFullChrgVolt\":\"412\",\\r\\n\"CellFullChrgVol... \n", "23 24 XMR {\"PackFullChrgVolt\":\"442\",\\r\\n\"CellFullChrgVol... \n", "24 25 XMY {\"PackFullChrgVolt\":\"403\",\\r\\n\"CellFullChrgVol... \n", "25 26 XP01 {\"PackFullChrgVolt\":\"414\",\\r\\n\"CellFullChrgVol... \n", "\n", " create_time create_by update_by is_delete update_time \n", "0 2022-12-04 00:00:00 None None 0.0 2000-01-01 00:00:00 \n", "1 2022-12-04 00:00:00 None None 0.0 2000-01-01 00:00:00 \n", "2 2022-12-04 00:00:00 None None 0.0 2000-01-01 00:00:00 \n", "3 2022-12-04 00:00:00 None None 0.0 2000-01-01 00:00:00 \n", "4 2022-12-04 00:00:00 None None 0.0 2000-01-01 00:00:00 \n", "5 2022-12-04 00:00:00 None None 0.0 2000-01-01 00:00:00 \n", "6 2022-12-29 23:00:57 None None NaN 2022-12-29 23:01:03 \n", "7 NaT None None NaN NaT \n", "8 NaT None None NaN NaT \n", "9 2022-12-04 00:00:00 None None 0.0 2000-01-01 00:00:00 \n", "10 2023-04-24 00:00:00 None None 0.0 2023-04-24 00:00:00 \n", "11 NaT None None NaN NaT \n", "12 2023-04-18 00:00:00 None None 0.0 2000-01-01 00:00:00 \n", "13 2023-05-30 00:00:00 None None 0.0 2023-05-30 00:00:00 \n", "14 NaT None None NaN NaT \n", "15 NaT None None NaN NaT \n", "16 NaT None None NaN NaT \n", "17 NaT None None NaN NaT \n", "18 NaT None None NaN NaT \n", "19 NaT None admin 0.0 2023-08-08 19:08:19 \n", "20 NaT None admin 0.0 2023-08-08 19:08:19 \n", "21 NaT None admin 0.0 2023-08-08 19:08:19 \n", "22 NaT None admin 0.0 2023-08-08 19:08:19 \n", "23 NaT None admin 0.0 2023-08-08 19:08:19 \n", "24 NaT None admin 0.0 2023-08-08 19:08:19 \n", "25 NaT None admin 0.0 2023-08-08 19:08:19 " ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 创建数据库连接\n", "import mysql.connector\n", "connection = mysql.connector.connect(\n", " host=mysql_algo_params['host'], # 数据库主机\n", " database=mysql_algo_params['db'], # 数据库名称\n", " user=mysql_algo_params['user'], # 用户名\n", " password=mysql_algo_params['password'] # 密码\n", ")\n", "\n", "if connection.is_connected():\n", " sql = \"select * from algo_pack_param\"\n", " sql1 = \"select * from algo_list\"\n", " sql2 = \"select * from algo_adjustable_param\"\n", " cursor = connection.cursor()\n", " # 执行 SQL 查询\n", " query = \"SELECT * FROM algo_pack_param\"\n", " cursor.execute(sql)\n", " column_names = [i[0] for i in cursor.description]\n", " rows = cursor.fetchall()\n", " df_algo_pack_param = pd.DataFrame(rows, columns=column_names)\n", " cursor.execute(sql1)\n", " column_names = [i[0] for i in cursor.description]\n", " rows = cursor.fetchall()\n", " df_algo_param = pd.DataFrame(rows, columns=column_names)\n", " cursor.execute(sql2)\n", " column_names = [i[0] for i in cursor.description]\n", " rows = cursor.fetchall()\n", " df_algo_adjustable_param_all = pd.DataFrame(rows, columns=column_names)\n", "\n", "if connection.is_connected():\n", " cursor.close()\n", " connection.close()\n", "\n", "df_algo_pack_param\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import os\n", "import pandas as pd\n", "\n", "# 定义文件夹路径\n", "folder_path1 = \"D:\\\\00WorkSpace\\\\07其他\\\\欣旺达数据分析\\\\XMB-1\\\\259551\\\\1\"\n", "folder_path2 = \"D:\\\\00WorkSpace\\\\07其他\\\\欣旺达数据分析\\\\XMB-1\\\\259551\\\\2\"\n", "\n", "# 获取文件夹中的所有文件和文件夹\n", "all_files1 = os.listdir(folder_path1)\n", "all_files2 = os.listdir(folder_path2)\n", "all_data = pd.DataFrame()\n", "for file in all_files1:\n", " df = pd.read_excel(f\"{folder_path1}\\\\{file}\")\n", " if (not df.empty) and type(df.iloc[0]['time']) == str:\n", " print(file,type(df.iloc[0]['time']),df.iloc[0]['time'])\n", " else:\n", " all_data = all_data.append(df, ignore_index=True)\n", "for file in all_files2:\n", " df = pd.read_excel(f\"{folder_path2}\\\\{file}\")\n", " if type(df.iloc[0]['time']) == str:\n", " print(file,type(df.iloc[0]['time']),df.iloc[0]['time'])\n", " else:\n", " all_data = all_data.append(df, ignore_index=True)\n", "all_data.sort_values(by=['time'], inplace=True)\n", "all_data" ] }, { "cell_type": "code", "execution_count": 38, "metadata": {}, "outputs": [], "source": [ "CellVoltNums=int(algo_pack_param['CellVoltTotalCount'])\n", "CellTempNums = int(algo_pack_param['CellTempTotalCount'])\n", "cellvolt_name=['cell_voltage'+str(x) for x in range(1, CellVoltNums+1)]\n", "celltemp_name=['cell_temp'+str(x) for x in range(1, CellTempNums+1)]\n", "all_data.columns = ['id','time','time1','pack_volt','pack_crnt','pack_soc'] + cellvolt_name + celltemp_name + \\\n", "['diff_vol','cell_volt_max','max_vol_num','cell_volt_min','min_vol_num','diff_temp','cell_temp_max','max_temp_num','odo','insulation_resistance','speed','mode','dcdc']\n" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "['259551', '097002']" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import os\n", "pack_code = 'XMB-1'\n", "path_all = f\"/home/share/欣旺达/{pack_code}\"\n", "all_files1 = os.listdir(path_all)\n", "all_files1" ] }, { "cell_type": "markdown", "metadata": {}, "source": [] }, { "cell_type": "code", "execution_count": 2, "metadata": { "metadata": {} }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " A B\n", "0 1 4\n", "1 2 5\n", "2 3 6\n", " A B C\n", "0 1 4 7\n" ] } ], "source": [ "import pandas as pd\n", " \n", "df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6], 'C': [7, 8, 9]})\n", "print(df[['A', 'B']]) # 截取列'A'和'B'\n", "print(df[0:1]) # 截取第二行到第三行，不包括索引3的行" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import os\n", "import pandas as pd\n", "folder_path = '/data/common/benchi/data/LY9F49BC4MALBZ470' # 替换为您的文件夹路径\n", "\n", "for filename in os.listdir(folder_path):\n", " df_data = pd.read_pickle(f\"{folder_path}/{filename}\", compression='zip')\n", " filename.split('_')\n", " break" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df_data.loc[0,'time']" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import datetime\n", "a = datetime.datetime.strptime(f\"{a[:10]} 00:00:00\", \"%Y-%m-%d %H:%M:%S\") + datetime.timedelta(minutes=10)\n", "df_data[df_data['time']