{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "### start to get data PK504B10100004341 from 2021-10-30 00:00:00 to 2021-10-31 00:00:00\n",
      "# get data from 2021-10-30 00:00:00 to 2021-10-31 00:00:00......... \n",
      "all data-getting done, bms_count is 3754, gps_count is 0, system_count is 0, accum_count is 0 \n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py:4441: 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 super().rename(\n"
     ]
    },
    {
     "ename": "TypeError",
     "evalue": "drop() got an unexpected keyword argument 'column'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-1-8edc7c0a33b7>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     82\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     83\u001b[0m     \u001b[1;31m# 记录偏差超过3的电芯编号\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 84\u001b[1;33m     \u001b[0mdf_all_result\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0msta\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0minstorage\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msn\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdf_result_1\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdf_result_2\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     85\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     86\u001b[0m     \u001b[1;31m# all_result = pd.concat([all_result, alarm_result])\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\WORK\\QX\\PROJECT\\data_analyze_platform\\LIB\\MIDDLE\\OutlierDetection\\VoltOutlier\\V_1_0_0\\sta.py\u001b[0m in \u001b[0;36minstorage\u001b[1;34m(sn, df_voltdiff_result, df_volt_result)\u001b[0m\n\u001b[0;32m    112\u001b[0m     \u001b[0mtime_list\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    113\u001b[0m     \u001b[0mtype_list\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 114\u001b[1;33m     \u001b[0mdf_result\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf_voltdiff_result\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcopy\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdrop\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcolumn\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'time'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    115\u001b[0m     \u001b[0mtime_list_1\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf_voltdiff_result\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'time'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    116\u001b[0m     \u001b[0mdf_result\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf_result\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdf_result\u001b[0m\u001b[1;33m>\u001b[0m\u001b[1;36m3\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m|\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mdf_result\u001b[0m\u001b[1;33m<\u001b[0m\u001b[1;33m-\u001b[0m\u001b[1;36m3\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdropna\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0maxis\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mhow\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'all'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdropna\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0maxis\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mhow\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'all'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mTypeError\u001b[0m: drop() got an unexpected keyword argument 'column'"
     ]
    }
   ],
   "source": [
    "# 获取数据\n",
    "import sys\n",
    "from LIB.BACKEND import DBManager\n",
    "from LIB.MIDDLE.OutlierDetection.VoltOutlier.V_1_0_0 import sta\n",
    "import pymysql\n",
    "import pandas as pd\n",
    "from matplotlib import pyplot as plt\n",
    "\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei']\n",
    "plt.rcParams['axes.unicode_minus'] = False\n",
    "\n",
    "# 更新sn列表\n",
    "host='rm-bp10j10qy42bzy0q7.mysql.rds.aliyuncs.com'\n",
    "port=3306\n",
    "db='qixiang_oss'\n",
    "user='qixiang_oss'\n",
    "password='Qixiang2021'\n",
    "conn = pymysql.connect(host=host, port=port, user=user, password=password, database=db)\n",
    "cursor = conn.cursor()\n",
    "cursor.execute(\"select sn, imei, add_time from app_device\")\n",
    "res = cursor.fetchall()\n",
    "df_sn = pd.DataFrame(res, columns=['sn', 'imei', 'add_time'])\n",
    "df_sn = df_sn.reset_index(drop=True)\n",
    "conn.close();\n",
    "\n",
    "window = 50\n",
    "step = 10\n",
    "window2 = 5\n",
    "step2 = 3\n",
    "volt_column = ['单体电压1', '单体电压2',\n",
    "    '单体电压3', '单体电压4','单体电压5', '单体电压6','单体电压7', '单体电压8','单体电压9', '单体电压10', '单体电压11', '单体电压12',\n",
    "    '单体电压13', '单体电压14','单体电压15', '单体电压16','单体电压17', '单体电压18','单体电压19', '单体电压20']\n",
    "columns = ['时间戳', '单体电压1', '单体电压2','单体电压3', '单体电压4','单体电压5', '单体电压6','单体电压7', '单体电压8','单体电压9', '单体电压10', '单体电压11', '单体电压12',\n",
    "'单体电压13', '单体电压14','单体电压15', '单体电压16','单体电压17', '单体电压18','单体电压19', '单体电压20']\n",
    "dbManager = DBManager.DBManager()\n",
    "\n",
    "all_result = pd.DataFrame(columns=['sn', 'suptype', 'type', 'num', 'alarm_time'])\n",
    "for sn in df_sn['sn'].tolist()[0:3]:\n",
    "    st = '2021-10-30 00:00:00'\n",
    "    et = '2021-10-31 00:00:00'\n",
    "\n",
    "    \n",
    "    df_data = dbManager.get_data(sn=sn, start_time=st, end_time=et, data_groups=['bms'])\n",
    "    # \n",
    "    df_bms = df_data['bms']\n",
    "\n",
    "    df_ori = df_bms[columns]\n",
    "    df_ori.rename(columns = {'时间戳':'time'}, inplace=True)\n",
    "    df = df_ori.drop_duplicates(subset=['time']) # 删除时间相同的数据\n",
    "    df = df.reset_index(drop=True)\n",
    "    df_result_1,time_list_1 = sta.cal_voltdiff_uniform(df,volt_column, window=window, step=step, window2=window2, step2=step2)\n",
    "    df_result_2,time_list_2 = sta.cal_volt_uniform(df,volt_column, window=window, step=step)\n",
    "\n",
    "    df_result_1['time'] = time_list_1\n",
    "    df_result_2['time'] = time_list_2\n",
    "    # second_list = [((pd.to_datetime(x)-pd.to_datetime(time_list[0])).total_seconds()) for x in time_list]  # 距离第一个点的分钟数\n",
    "\n",
    "    # fig = plt.figure(figsize=(20,10))\n",
    "\n",
    "    # length = len(df_result)\n",
    "    # for column in volt_column:\n",
    "    #     # plt.plot([x for x in range(0, length)], df_result[column][0:length], label=column)\n",
    "    #     plt.scatter(time_list[:], df_result[column][:length], label=column)\n",
    "\n",
    "    # plt.legend()\n",
    "    # plt.title('{}电压离群（电压滤波窗口大小:{}, 步进大小:{}'.format(sn, window, step, window2, step2))\n",
    "    # sn = sn.replace('/','_')\n",
    "    # plt.savefig('./结果/新数据/' + sn + '.png')\n",
    "    # plt.close()\n",
    "\n",
    "    # 报警\n",
    "    # df_result_1['time'] = time_list_1\n",
    "    # alarm_result = sta.alarm(df_result_1, volt_column,  alarm_window=20, alarm_ratio=0.9, alarm_threshold=3.5)\n",
    "    # alarm_result['sn'] = [sn] * len(alarm_result)\n",
    "    # alarm_result['suptype'] = ['电压变化量离群'] * len(alarm_result)\n",
    "    # all_result = pd.concat([all_result, alarm_result])\n",
    "\n",
    "    # df_result_2['time'] = time_list_2\n",
    "    # alarm_result = sta.alarm(df_result_2, volt_column,  alarm_window=20, alarm_ratio=0.9, alarm_threshold=3.5)\n",
    "    # alarm_result['sn'] = [sn] * len(alarm_result)\n",
    "    # alarm_result['suptype'] = ['电压离群'] * len(alarm_result)\n",
    "\n",
    "    # 记录偏差超过3的电芯编号\n",
    "    df_all_result = sta.instorage(sn, df_result_1, df_result_2)\n",
    "    \n",
    "    # all_result = pd.concat([all_result, alarm_result])\n",
    "    # all_result.to_csv('./result_volt.csv')\n",
    "    break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "    df_all_result = pd.DataFrame(columns=['sn', 'time', 'cellnum', 'value', 'type'])\n",
    "    \n",
    "    value_list = []\n",
    "    cellnum_list = []\n",
    "    time_list = []\n",
    "    type_list = []\n",
    "    df_result = df_result_1[volt_column].copy()\n",
    "    df_result = df_result[(df_result>3) | (df_result<-3)].dropna(axis=0, how='all').dropna(axis=1, how='all')\n",
    "    for column in df_result.columns:\n",
    "        df = df_result[[column]].dropna(axis=0, how='all')\n",
    "        value_list.extend(df[column].tolist())\n",
    "        cellnum_list.extend([column]*len(df))\n",
    "        time_list.extend([time_list_1[x] for x in df.index])\n",
    "    length_1 = len(value_list)\n",
    "    \n",
    "\n",
    "\n",
    "    df_result = df_result_2[volt_column].copy()\n",
    "    df_result = df_result[(df_result>3) | (df_result<-3)].dropna(axis=0, how='all').dropna(axis=1, how='all')\n",
    "    for column in df_result.columns:\n",
    "        df = df_result[[column]].dropna(axis=0, how='all')\n",
    "        value_list.extend(df[column].tolist())\n",
    "        cellnum_list.extend([column]*len(df))\n",
    "        time_list.extend([time_list_2[x] for x in df.index])\n",
    "\n",
    "    length_2 = len(value_list) - length_1\n",
    "    type_list.extend(['电压变化量离群'] * length_1)\n",
    "    type_list.extend(['电压离群'] * length_2)\n",
    "    df_all_result['sn'] = [sn] * len(value_list)\n",
    "    df_all_result['cellnum'] = cellnum_list\n",
    "    df_all_result['value'] = value_list\n",
    "    df_all_result['time'] = time_list\n",
    "    df_all_result['type'] = type_list\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3754"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_bms)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "b3ba2566441a7c06988d0923437866b63cedc61552a5af99d1f4fb67d367b25f"
  },
  "kernelspec": {
   "display_name": "Python 3.8.8 64-bit ('base': conda)",
   "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.8.8"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}