{ "cells": [ { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "# 标签原始数据获取\n", "from ZlwlAlgosCommon.utils.ProUtils import *\n", "from ZlwlAlgosCommon.service.iotp.Beans import DataField\n", "from ZlwlAlgosCommon.service.iotp.IotpAlgoService import IotpAlgoService\n", "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "start env: dev \n" ] } ], "source": [ "cur_env = 'dev' # 设置运行环境\n", "app_path = \"/home/zhuxi/project/zlwl-algos\" # 设置相对路径\n", "sysUtils = SysUtils(cur_env, app_path)\n", "hbase_params = sysUtils.get_cf_param('hbase')\n", "hbase_datafactory_params = sysUtils.get_cf_param('hbase-datafactory')\n", "iotp_service = IotpAlgoService(hbase_params=hbase_params)\n", "iotp_datafactory_service = IotpAlgoService(hbase_params=hbase_datafactory_params)\n", "\n", "mysql_datafactory_params = sysUtils.get_cf_param('mysql-datafactory')\n", "mysqlUtils = MysqlUtils()\n", "mysql_datafactory_engine, mysql_datafactory_Session= mysqlUtils.get_mysql_engine(mysql_datafactory_params)\n", "mysql_datafactory_conn = mysql_datafactory_engine.connect()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# 获取标签集数据\n", "df_tags_dataset = iotp_datafactory_service.get_dataset_tags(mysql_datafactory_conn)" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "df_sn=pd.read_excel('/home/zhuxi/project/zlwl-algos/USER/zhuxi/FaultClass/V1_0_1/t_device.xlsx')\n", "sn_list=list(df_sn['sn'])" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "['PJXCLL128N22C5001',\n", " 'PJXCLL532S234M001',\n", " 'PJXCLL532S234M002',\n", " 'PJXCLL532S234M003',\n", " 'PJXCLL532S234M004',\n", " 'PJXCLL532S234M005',\n", " 'PJXCLL532S234M006',\n", " 'PJXCLL532S234M008',\n", " 'PJXCLL532S234M016',\n", " 'PJXCLL532S234M018',\n", " 'PJXCLL532S234M021',\n", " 'PJXCLL532S234M009',\n", " 'PJXCLL532S234M010',\n", " 'PJXCLL532S234M017',\n", " 'PJXCLL532S234M022',\n", " 'PJXCLL532S234M023',\n", " 'PJXCLL532S234M007',\n", " 'PJXCLL532S234M011',\n", " 'PJXCLL532S234M012',\n", " 'PJXCLL532S234M013',\n", " 'PJXCLL532S234M014',\n", " 'PJXCLL532S234M015',\n", " 'PJXCLL532S234M019',\n", " 'PJXCLL532S234M020',\n", " 'PJXCLL532S234M024',\n", " 'PJXCLL532S234M025',\n", " 'PJXCLL532S234M026',\n", " 'PJXCLL532S234M027',\n", " 'PJXCLL532S234M028',\n", " 'PJXCLL532S234M029',\n", " 'PJXCLL532S234M030']" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sn_list" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [], "source": [ "# 根据标签 从 数据集 取数\n", "columns = [DataField.error_level, DataField.error_code, DataField.pack_crnt, DataField.pack_volt,\n", " DataField.bms_sta, DataField.cell_voltage_count, DataField.cell_temp_count, DataField.cell_voltage, DataField.cell_temp, \n", " DataField.pack_soc, DataField.other_temp_value, DataField.cell_balance,\n", " DataField.pack_soh, DataField.charge_sta]\n", "dataset=pd.DataFrame()\n", "#for idx in df_tags_dataset.index:\n", " #print(idx)\n", "for sn in sn_list:\n", " if sn[:4]=='MGMC':\n", " start_time='2023-04-01 00:00:00'\n", " end_time='2023-05-01 00:00:00'\n", " df_data = iotp_service.get_data(sn_list=[sn], columns=columns, start_time=start_time, end_time=end_time) \n", " # print(df_data)\n", " #break\n", " dataset=dataset.append(df_data)" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: []\n", "Index: []" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "dataset" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [], "source": [ "df_data.to_csv('data.csv')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "ename": "FileNotFoundError", "evalue": "[Errno 2] No such file or directory: 'data_charge copy.feather'", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mFileNotFoundError\u001b[0m Traceback (most recent call last)", "Cell \u001b[0;32mIn[4], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m read_df \u001b[39m=\u001b[39m pd\u001b[39m.\u001b[39;49mread_feather(\u001b[39m'\u001b[39;49m\u001b[39mdata_charge copy.feather\u001b[39;49m\u001b[39m'\u001b[39;49m)\n", "File \u001b[0;32m/opt/module/anaconda2022-10/envs/py3916/lib/python3.9/site-packages/pandas/io/feather_format.py:126\u001b[0m, in \u001b[0;36mread_feather\u001b[0;34m(path, columns, use_threads, storage_options)\u001b[0m\n\u001b[1;32m 123\u001b[0m import_optional_dependency(\u001b[39m\"\u001b[39m\u001b[39mpyarrow\u001b[39m\u001b[39m\"\u001b[39m)\n\u001b[1;32m 124\u001b[0m \u001b[39mfrom\u001b[39;00m \u001b[39mpyarrow\u001b[39;00m \u001b[39mimport\u001b[39;00m feather\n\u001b[0;32m--> 126\u001b[0m \u001b[39mwith\u001b[39;00m get_handle(\n\u001b[1;32m 127\u001b[0m path, \u001b[39m\"\u001b[39;49m\u001b[39mrb\u001b[39;49m\u001b[39m\"\u001b[39;49m, storage_options\u001b[39m=\u001b[39;49mstorage_options, is_text\u001b[39m=\u001b[39;49m\u001b[39mFalse\u001b[39;49;00m\n\u001b[1;32m 128\u001b[0m ) \u001b[39mas\u001b[39;00m handles:\n\u001b[1;32m 130\u001b[0m \u001b[39mreturn\u001b[39;00m feather\u001b[39m.\u001b[39mread_feather(\n\u001b[1;32m 131\u001b[0m handles\u001b[39m.\u001b[39mhandle, columns\u001b[39m=\u001b[39mcolumns, use_threads\u001b[39m=\u001b[39m\u001b[39mbool\u001b[39m(use_threads)\n\u001b[1;32m 132\u001b[0m )\n", "File \u001b[0;32m/opt/module/anaconda2022-10/envs/py3916/lib/python3.9/site-packages/pandas/io/common.py:710\u001b[0m, in \u001b[0;36mget_handle\u001b[0;34m(path_or_buf, mode, encoding, compression, memory_map, is_text, errors, storage_options)\u001b[0m\n\u001b[1;32m 701\u001b[0m handle \u001b[39m=\u001b[39m \u001b[39mopen\u001b[39m(\n\u001b[1;32m 702\u001b[0m handle,\n\u001b[1;32m 703\u001b[0m ioargs\u001b[39m.\u001b[39mmode,\n\u001b[0;32m (...)\u001b[0m\n\u001b[1;32m 706\u001b[0m newline\u001b[39m=\u001b[39m\u001b[39m\"\u001b[39m\u001b[39m\"\u001b[39m,\n\u001b[1;32m 707\u001b[0m )\n\u001b[1;32m 708\u001b[0m \u001b[39melse\u001b[39;00m:\n\u001b[1;32m 709\u001b[0m \u001b[39m# Binary mode\u001b[39;00m\n\u001b[0;32m--> 710\u001b[0m handle \u001b[39m=\u001b[39m \u001b[39mopen\u001b[39;49m(handle, ioargs\u001b[39m.\u001b[39;49mmode)\n\u001b[1;32m 711\u001b[0m handles\u001b[39m.\u001b[39mappend(handle)\n\u001b[1;32m 713\u001b[0m \u001b[39m# Convert BytesIO or file objects passed with an encoding\u001b[39;00m\n", "\u001b[0;31mFileNotFoundError\u001b[0m: [Errno 2] No such file or directory: 'data_charge copy.feather'" ] } ], "source": [ "read_df = pd.read_feather('data_charge copy.feather')" ] }, { "cell_type": "code", "execution_count": 9, "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", "
update_timecreate_bysnstart_timeend_timevinfactory_idpack_modeldevice_cell_typefactorytagchild_tag
8262023-04-09 00:28:49admin667CC9000012023-04-07 08:40:222023-04-07 09:25:41None1HY02520L骑享BMS控制故障电芯欠压
6652023-04-07 19:23:39adminPK50001A1000006502022-07-01 21:58:172022-07-01 22:41:50None1KY01710N骑享温度故障接插件过温
6772023-04-07 19:23:39adminPK50001A1000006502022-07-21 21:29:362022-07-21 23:05:34None1KY01710N骑享温度故障接插件过温
6762023-04-07 19:23:39adminPK50001A1000006502022-07-21 21:31:392022-07-21 22:58:11None1KY01710N骑享温度故障接插件过温
6752023-04-07 19:23:39adminPK50001A1000006502022-07-18 22:14:182022-07-18 22:20:38None1KY01710N骑享温度故障接插件过温
.......................................
1152023-04-07 19:17:50admin09SPE002BC011199T00000022023-03-03 10:44:262023-03-03 12:24:43LUZAGAAAXKA00630422101TKN合众温度故障温差过大
1142023-04-07 19:17:50admin09SPE002BC011199T00000022023-03-02 08:36:522023-03-02 11:09:58LUZAGAAAXKA00630422101TKN合众温度故障温差过大
1132023-04-07 19:17:50admin09SPE002BC011199P00000262023-03-07 23:06:292023-03-08 00:39:50LUZAGAAA7KA00631122101TKN合众温度故障温差过大
1122023-04-07 19:17:50admin09SPE002BC011199J00000182023-03-23 04:38:092023-03-23 06:29:50LUZAGAAAXKA00599322101TKN合众温度故障温差过大
02023-04-07 19:17:50admin001PB047000001B2301000752023-03-28 17:14:022023-03-28 19:33:02LUZAGBDA2MA00779122101TSL合众系统故障SOC卡滞
\n", "

827 rows × 12 columns

\n", "
" ], "text/plain": [ " update_time create_by sn \\\n", "826 2023-04-09 00:28:49 admin 667CC900001 \n", "665 2023-04-07 19:23:39 admin PK50001A100000650 \n", "677 2023-04-07 19:23:39 admin PK50001A100000650 \n", "676 2023-04-07 19:23:39 admin PK50001A100000650 \n", "675 2023-04-07 19:23:39 admin PK50001A100000650 \n", ".. ... ... ... \n", "115 2023-04-07 19:17:50 admin 09SPE002BC011199T0000002 \n", "114 2023-04-07 19:17:50 admin 09SPE002BC011199T0000002 \n", "113 2023-04-07 19:17:50 admin 09SPE002BC011199P0000026 \n", "112 2023-04-07 19:17:50 admin 09SPE002BC011199J0000018 \n", "0 2023-04-07 19:17:50 admin 001PB047000001B230100075 \n", "\n", " start_time end_time vin factory_id \\\n", "826 2023-04-07 08:40:22 2023-04-07 09:25:41 None 1 \n", "665 2022-07-01 21:58:17 2022-07-01 22:41:50 None 1 \n", "677 2022-07-21 21:29:36 2022-07-21 23:05:34 None 1 \n", "676 2022-07-21 21:31:39 2022-07-21 22:58:11 None 1 \n", "675 2022-07-18 22:14:18 2022-07-18 22:20:38 None 1 \n", ".. ... ... ... ... \n", "115 2023-03-03 10:44:26 2023-03-03 12:24:43 LUZAGAAAXKA006304 2 \n", "114 2023-03-02 08:36:52 2023-03-02 11:09:58 LUZAGAAAXKA006304 2 \n", "113 2023-03-07 23:06:29 2023-03-08 00:39:50 LUZAGAAA7KA006311 2 \n", "112 2023-03-23 04:38:09 2023-03-23 06:29:50 LUZAGAAAXKA005993 2 \n", "0 2023-03-28 17:14:02 2023-03-28 19:33:02 LUZAGBDA2MA007791 2 \n", "\n", " pack_model device_cell_type factory tag child_tag \n", "826 HY02520 L 骑享 BMS控制故障 电芯欠压 \n", "665 KY01710 N 骑享 温度故障 接插件过温 \n", "677 KY01710 N 骑享 温度故障 接插件过温 \n", "676 KY01710 N 骑享 温度故障 接插件过温 \n", "675 KY01710 N 骑享 温度故障 接插件过温 \n", ".. ... ... ... ... ... \n", "115 2101TK N 合众 温度故障 温差过大 \n", "114 2101TK N 合众 温度故障 温差过大 \n", "113 2101TK N 合众 温度故障 温差过大 \n", "112 2101TK N 合众 温度故障 温差过大 \n", "0 2101TS L 合众 系统故障 SOC卡滞 \n", "\n", "[827 rows x 12 columns]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_tags_dataset" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# 获取标签集数据\n", "df_tags_dataset = iotp_datafactory_service.get_dataset_tags(mysql_datafactory_conn)\n", "\n" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "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, "vscode": { "interpreter": { "hash": "916dbcbb3f70747c44a77c7bcd40155683ae19c65e1c03b4aa3499c5328201f1" } } }, "nbformat": 4, "nbformat_minor": 2 }