Programe1_type2-predict.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "fca531fe",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:13:41.669219Z",
     "start_time": "2024-09-24T19:13:41.132448Z"
    }
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "983eafdd",
   "metadata": {},
   "source": [
    "## 读入数据，并对2基站情况数据进行合并与拆解"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "fdea2945",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:13:42.742633Z",
     "start_time": "2024-09-24T19:13:42.719271Z"
    }
   },
   "outputs": [],
   "source": [
    "resource=pd.read_csv('RAWDATA/test_set_2_2ap.csv')\n",
    "\n",
    "   #删掉异常的行值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "38fd28cb",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:13:44.861432Z",
     "start_time": "2024-09-24T19:13:44.852390Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 64 entries, 0 to 63\n",
      "Data columns (total 46 columns):\n",
      " #   Column                       Non-Null Count  Dtype  \n",
      "---  ------                       --------------  -----  \n",
      " 0   test_id                      64 non-null     int64  \n",
      " 1   test_dur                     64 non-null     int64  \n",
      " 2   loc_id                       64 non-null     object \n",
      " 3   protocol                     64 non-null     object \n",
      " 4   pkt_len                      64 non-null     int64  \n",
      " 5   bss_id                       64 non-null     int64  \n",
      " 6   ap_name                      64 non-null     object \n",
      " 7   ap_mac                       64 non-null     object \n",
      " 8   ap_id                        64 non-null     object \n",
      " 9   pd                           64 non-null     int64  \n",
      " 10  ed                           64 non-null     int64  \n",
      " 11  nav                          64 non-null     int64  \n",
      " 12  eirp                         64 non-null     int64  \n",
      " 13  ap_from_ap_0_sum_ant_rssi    32 non-null     object \n",
      " 14  ap_from_ap_0_max_ant_rssi    32 non-null     object \n",
      " 15  ap_from_ap_0_mean_ant_rssi   32 non-null     object \n",
      " 16  ap_from_ap_1_sum_ant_rssi    32 non-null     object \n",
      " 17  ap_from_ap_1_max_ant_rssi    32 non-null     object \n",
      " 18  ap_from_ap_1_mean_ant_rssi   32 non-null     object \n",
      " 19  sta_mac                      64 non-null     object \n",
      " 20  sta_id                       64 non-null     object \n",
      " 21  sta_to_ap_0_sum_ant_rssi     64 non-null     object \n",
      " 22  sta_to_ap_0_max_ant_rssi     64 non-null     object \n",
      " 23  sta_to_ap_0_mean_ant_rssi    64 non-null     object \n",
      " 24  sta_to_ap_1_sum_ant_rssi     64 non-null     object \n",
      " 25  sta_to_ap_1_max_ant_rssi     64 non-null     object \n",
      " 26  sta_to_ap_1_mean_ant_rssi    64 non-null     object \n",
      " 27  sta_from_ap_0_sum_ant_rssi   64 non-null     object \n",
      " 28  sta_from_ap_0_max_ant_rssi   64 non-null     object \n",
      " 29  sta_from_ap_0_mean_ant_rssi  64 non-null     object \n",
      " 30  sta_from_ap_1_sum_ant_rssi   64 non-null     object \n",
      " 31  sta_from_ap_1_max_ant_rssi   64 non-null     object \n",
      " 32  sta_from_ap_1_mean_ant_rssi  64 non-null     object \n",
      " 33  sta_from_sta_0_rssi          32 non-null     float64\n",
      " 34  sta_from_sta_1_rssi          32 non-null     float64\n",
      " 35  nss                          0 non-null      float64\n",
      " 36  mcs                          0 non-null      float64\n",
      " 37  per                          0 non-null      float64\n",
      " 38  num_ampdu                    0 non-null      float64\n",
      " 39  ppdu_dur                     0 non-null      float64\n",
      " 40  other_air_time               0 non-null      float64\n",
      " 41  seq_time                     0 non-null      float64\n",
      " 42  throughput                   0 non-null      float64\n",
      " 43  predict nss                  0 non-null      float64\n",
      " 44  predict mcs                  0 non-null      float64\n",
      " 45  error%                       0 non-null      float64\n",
      "dtypes: float64(13), int64(8), object(25)\n",
      "memory usage: 23.1+ KB\n"
     ]
    }
   ],
   "source": [
    "resource.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "32bffc6f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:14:00.760589Z",
     "start_time": "2024-09-24T19:14:00.755470Z"
    }
   },
   "outputs": [],
   "source": [
    "columns=[0,1,2,4,6,7,8,12,19,20,22,23,25,26,28,29,31,32,35,36,37,38,39,40,42,43,44,45]\n",
    "resource=resource.drop(resource.columns[columns],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1c6ebc09",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:14:02.743830Z",
     "start_time": "2024-09-24T19:14:02.735290Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 64 entries, 0 to 63\n",
      "Data columns (total 18 columns):\n",
      " #   Column                      Non-Null Count  Dtype  \n",
      "---  ------                      --------------  -----  \n",
      " 0   protocol                    64 non-null     object \n",
      " 1   bss_id                      64 non-null     int64  \n",
      " 2   pd                          64 non-null     int64  \n",
      " 3   ed                          64 non-null     int64  \n",
      " 4   nav                         64 non-null     int64  \n",
      " 5   ap_from_ap_0_sum_ant_rssi   32 non-null     object \n",
      " 6   ap_from_ap_0_max_ant_rssi   32 non-null     object \n",
      " 7   ap_from_ap_0_mean_ant_rssi  32 non-null     object \n",
      " 8   ap_from_ap_1_sum_ant_rssi   32 non-null     object \n",
      " 9   ap_from_ap_1_max_ant_rssi   32 non-null     object \n",
      " 10  ap_from_ap_1_mean_ant_rssi  32 non-null     object \n",
      " 11  sta_to_ap_0_sum_ant_rssi    64 non-null     object \n",
      " 12  sta_to_ap_1_sum_ant_rssi    64 non-null     object \n",
      " 13  sta_from_ap_0_sum_ant_rssi  64 non-null     object \n",
      " 14  sta_from_ap_1_sum_ant_rssi  64 non-null     object \n",
      " 15  sta_from_sta_0_rssi         32 non-null     float64\n",
      " 16  sta_from_sta_1_rssi         32 non-null     float64\n",
      " 17  seq_time                    0 non-null      float64\n",
      "dtypes: float64(3), int64(4), object(11)\n",
      "memory usage: 9.1+ KB\n"
     ]
    }
   ],
   "source": [
    "resource.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "830cd31d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:14:05.627827Z",
     "start_time": "2024-09-24T19:14:05.604148Z"
    }
   },
   "outputs": [],
   "source": [
    "# 合并操作\n",
    "for i in range(resource.shape[0]):\n",
    "    if resource.iloc[i,list(resource.columns).index('bss_id')]==0:\n",
    "        resource.iloc[i,list(resource.columns).index('ap_from_ap_0_sum_ant_rssi')]=resource.iloc[i,list(resource.columns).index('ap_from_ap_1_sum_ant_rssi')]\n",
    "        resource.iloc[i,list(resource.columns).index('ap_from_ap_0_max_ant_rssi')]=resource.iloc[i,list(resource.columns).index('ap_from_ap_1_max_ant_rssi')]\n",
    "        resource.iloc[i,list(resource.columns).index('ap_from_ap_0_mean_ant_rssi')]=resource.iloc[i,list(resource.columns).index('ap_from_ap_1_mean_ant_rssi')]\n",
    "        resource.iloc[i,list(resource.columns).index('sta_from_sta_0_rssi')]=resource.iloc[i,list(resource.columns).index('sta_from_sta_1_rssi')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "22da7af2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:14:07.725077Z",
     "start_time": "2024-09-24T19:14:07.721188Z"
    }
   },
   "outputs": [],
   "source": [
    "resource=resource.drop(['ap_from_ap_1_sum_ant_rssi','ap_from_ap_1_max_ant_rssi',\n",
    "                       'ap_from_ap_1_mean_ant_rssi','sta_from_sta_1_rssi'],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "6feb6b0d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:14:09.126172Z",
     "start_time": "2024-09-24T19:14:09.118220Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 64 entries, 0 to 63\n",
      "Data columns (total 14 columns):\n",
      " #   Column                      Non-Null Count  Dtype  \n",
      "---  ------                      --------------  -----  \n",
      " 0   protocol                    64 non-null     object \n",
      " 1   bss_id                      64 non-null     int64  \n",
      " 2   pd                          64 non-null     int64  \n",
      " 3   ed                          64 non-null     int64  \n",
      " 4   nav                         64 non-null     int64  \n",
      " 5   ap_from_ap_0_sum_ant_rssi   64 non-null     object \n",
      " 6   ap_from_ap_0_max_ant_rssi   64 non-null     object \n",
      " 7   ap_from_ap_0_mean_ant_rssi  64 non-null     object \n",
      " 8   sta_to_ap_0_sum_ant_rssi    64 non-null     object \n",
      " 9   sta_to_ap_1_sum_ant_rssi    64 non-null     object \n",
      " 10  sta_from_ap_0_sum_ant_rssi  64 non-null     object \n",
      " 11  sta_from_ap_1_sum_ant_rssi  64 non-null     object \n",
      " 12  sta_from_sta_0_rssi         64 non-null     float64\n",
      " 13  seq_time                    0 non-null      float64\n",
      "dtypes: float64(2), int64(4), object(8)\n",
      "memory usage: 7.1+ KB\n"
     ]
    }
   ],
   "source": [
    "resource.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "43229b67",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:14:12.853874Z",
     "start_time": "2024-09-24T19:14:12.848378Z"
    }
   },
   "outputs": [],
   "source": [
    "# 定义管理rssi中异常值数据，并创造出新特征值\n",
    "def cal_pd(apmax,pd):\n",
    "    apmax = eval(apmax)\n",
    "    comparison_result = [1 if x >= pd else 0 for x in apmax]\n",
    "    proportion_of_ones = sum(comparison_result) / len(apmax)\n",
    "    return proportion_of_ones\n",
    "\n",
    "def cal_ed(apmax,ed):\n",
    "    apmax = eval(apmax)\n",
    "    comparison_result = [1 if x >= ed else 0 for x in apmax]\n",
    "    proportion_of_ones = sum(comparison_result) / len(apmax)\n",
    "    return proportion_of_ones\n",
    "\n",
    "def cal_nav(apmean,nav):\n",
    "    apmean = eval(apmean)\n",
    "    comparison_result = [1 if x >= nav else 0 for x in apmean]\n",
    "\n",
    "    # 计算 1 的占比\n",
    "    proportion_of_ones = sum(comparison_result) / len(apmean)\n",
    "    return proportion_of_ones    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "e96a8bfc",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:14:14.355720Z",
     "start_time": "2024-09-24T19:14:14.225348Z"
    }
   },
   "outputs": [],
   "source": [
    "resource['pro_pd'] = resource.apply(lambda row: cal_pd(row['ap_from_ap_0_max_ant_rssi'], row['pd']), axis=1)\n",
    "resource['pro_ed'] = resource.apply(lambda row: cal_ed(row['ap_from_ap_0_max_ant_rssi'], row['ed']), axis=1)\n",
    "resource['pro_nav'] = resource.apply(lambda row: cal_nav(row['ap_from_ap_0_mean_ant_rssi'], row['nav']), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "8ef6f7bd",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:14:16.246849Z",
     "start_time": "2024-09-24T19:14:16.242526Z"
    }
   },
   "outputs": [],
   "source": [
    "drop_columns=['pd','ed','ap_from_ap_0_mean_ant_rssi','ap_from_ap_0_max_ant_rssi']\n",
    "\n",
    "# drop_columns=['pd','ed','ap_from_ap_0_mean_ant_rssi','ap_from_ap_0_max_ant_rssi'\n",
    "#               ,'sta_to_ap_0_max_ant_rssi','sta_to_ap_0_mean_ant_rssi'\n",
    "#               ,'sta_to_ap_1_max_ant_rssi','sta_to_ap_1_mean_ant_rssi'\n",
    "#              ,'sta_from_ap_0_max_ant_rssi','sta_from_ap_0_mean_ant_rssi'\n",
    "#              ,'sta_from_ap_1_max_ant_rssi','sta_from_ap_1_mean_ant_rssi']\n",
    "\n",
    "resource=resource.drop(drop_columns,axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "90a592e7",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:14:17.775799Z",
     "start_time": "2024-09-24T19:14:17.766930Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 64 entries, 0 to 63\n",
      "Data columns (total 13 columns):\n",
      " #   Column                      Non-Null Count  Dtype  \n",
      "---  ------                      --------------  -----  \n",
      " 0   protocol                    64 non-null     object \n",
      " 1   bss_id                      64 non-null     int64  \n",
      " 2   nav                         64 non-null     int64  \n",
      " 3   ap_from_ap_0_sum_ant_rssi   64 non-null     object \n",
      " 4   sta_to_ap_0_sum_ant_rssi    64 non-null     object \n",
      " 5   sta_to_ap_1_sum_ant_rssi    64 non-null     object \n",
      " 6   sta_from_ap_0_sum_ant_rssi  64 non-null     object \n",
      " 7   sta_from_ap_1_sum_ant_rssi  64 non-null     object \n",
      " 8   sta_from_sta_0_rssi         64 non-null     float64\n",
      " 9   seq_time                    0 non-null      float64\n",
      " 10  pro_pd                      64 non-null     float64\n",
      " 11  pro_ed                      64 non-null     float64\n",
      " 12  pro_nav                     64 non-null     float64\n",
      "dtypes: float64(5), int64(2), object(6)\n",
      "memory usage: 6.6+ KB\n"
     ]
    }
   ],
   "source": [
    "resource.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "1c93b1ec",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:14:20.319969Z",
     "start_time": "2024-09-24T19:14:20.085603Z"
    }
   },
   "outputs": [],
   "source": [
    "# 对RSSI求平均值处理\n",
    "for i in range(3,8):\n",
    "    resource.iloc[:,i]=resource.iloc[:,i].apply(lambda x: sum(eval(x))/len(eval(x)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "a0093abb",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:14:28.540250Z",
     "start_time": "2024-09-24T19:14:26.538155Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\Anaconda\\Lib\\site-packages\\sklearn\\cluster\\_kmeans.py:1412: FutureWarning: The default value of `n_init` will change from 10 to 'auto' in 1.4. Set the value of `n_init` explicitly to suppress the warning\n",
      "  super()._check_params_vs_input(X, default_n_init=10)\n",
      "D:\\Anaconda\\Lib\\site-packages\\sklearn\\cluster\\_kmeans.py:1436: UserWarning: KMeans is known to have a memory leak on Windows with MKL, when there are less chunks than available threads. You can avoid it by setting the environment variable OMP_NUM_THREADS=1.\n",
      "  warnings.warn(\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cluster centers:\n",
      "          0         1         2\n",
      "0  0.956025  0.004927  0.984550\n",
      "1  0.064212  0.004366  0.057344\n",
      "2  0.180631  0.003470  0.461771\n",
      "cluster\n",
      "0    47\n",
      "1    13\n",
      "2     4\n",
      "Name: count, dtype: int64\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\ShenFei\\AppData\\Local\\Temp\\ipykernel_19596\\2563942475.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",
      "  df['cluster'] = kmeans.fit_predict(data_scaled)\n"
     ]
    }
   ],
   "source": [
    "# 选择pro的聚类个数\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from sklearn.cluster import KMeans\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.metrics import silhouette_score\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "data_scaled=resource.iloc[:,[10,11,12]]\n",
    "\n",
    "df=data_scaled\n",
    "k = 3\n",
    "kmeans = KMeans(n_clusters=k, random_state=42)\n",
    "df['cluster'] = kmeans.fit_predict(data_scaled)\n",
    "\n",
    "# 查看每个簇的平均特征值\n",
    "cluster_centers = pd.DataFrame(kmeans.cluster_centers_)\n",
    "# columns=['pro_pd', 'pro_ed', 'pro_nav']\n",
    "\n",
    "print(f\"Cluster centers:\\n{cluster_centers}\")\n",
    "\n",
    "# 查看每个簇的样本数量\n",
    "print(df['cluster'].value_counts())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "56e8b531",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:14:34.868839Z",
     "start_time": "2024-09-24T19:14:34.865332Z"
    }
   },
   "outputs": [],
   "source": [
    "resource['state']=df['cluster']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "6e23f777",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:14:37.518447Z",
     "start_time": "2024-09-24T19:14:37.509931Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 64 entries, 0 to 63\n",
      "Data columns (total 14 columns):\n",
      " #   Column                      Non-Null Count  Dtype  \n",
      "---  ------                      --------------  -----  \n",
      " 0   protocol                    64 non-null     object \n",
      " 1   bss_id                      64 non-null     int64  \n",
      " 2   nav                         64 non-null     int64  \n",
      " 3   ap_from_ap_0_sum_ant_rssi   64 non-null     object \n",
      " 4   sta_to_ap_0_sum_ant_rssi    64 non-null     object \n",
      " 5   sta_to_ap_1_sum_ant_rssi    64 non-null     object \n",
      " 6   sta_from_ap_0_sum_ant_rssi  64 non-null     object \n",
      " 7   sta_from_ap_1_sum_ant_rssi  64 non-null     object \n",
      " 8   sta_from_sta_0_rssi         64 non-null     float64\n",
      " 9   seq_time                    0 non-null      float64\n",
      " 10  pro_pd                      64 non-null     float64\n",
      " 11  pro_ed                      64 non-null     float64\n",
      " 12  pro_nav                     64 non-null     float64\n",
      " 13  state                       64 non-null     int32  \n",
      "dtypes: float64(5), int32(1), int64(2), object(6)\n",
      "memory usage: 6.9+ KB\n"
     ]
    }
   ],
   "source": [
    "resource.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "0b2e6f77",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:14:41.916791Z",
     "start_time": "2024-09-24T19:14:41.899255Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0     16.503333\n",
      "1     24.575000\n",
      "2     16.503333\n",
      "3     24.575000\n",
      "4     14.677632\n",
      "        ...    \n",
      "59    54.850000\n",
      "60    59.875000\n",
      "61    56.450000\n",
      "62    59.875000\n",
      "63    56.450000\n",
      "Name: sinr, Length: 64, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# 信干比采取dbm直接相减得到db\n",
    "def func(x, y, state):\n",
    "    if state == 0:\n",
    "        return x + 100\n",
    "    else:\n",
    "        return x - y\n",
    "\n",
    "    \n",
    "# 创建一个新列来存储 SINR 计算的结果\n",
    "sinr = []\n",
    "df = resource\n",
    "# 遍历 DataFrame 的每一行\n",
    "for i in range(len(df)):\n",
    "    if df.iloc[i, list(df.columns).index('bss_id')] == 1:  # ap1\n",
    "        result = func(df.iloc[i, list(df.columns).index('sta_from_ap_1_sum_ant_rssi')], df.iloc[i, list(df.columns).index('sta_from_ap_0_sum_ant_rssi')],df.iloc[i, list(df.columns).index('state')])\n",
    "    else:  # ap2\n",
    "        result = func(df.iloc[i, list(df.columns).index('sta_from_ap_0_sum_ant_rssi')], df.iloc[i, list(df.columns).index('sta_from_ap_1_sum_ant_rssi')],df.iloc[i, list(df.columns).index('state')])\n",
    "    sinr.append(result)\n",
    "\n",
    "# 将计算结果添加为新列\n",
    "df['sinr'] = sinr\n",
    "\n",
    "# 输出结果\n",
    "print(df['sinr'])\n",
    "resource = resource.drop('bss_id',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "91e6a373",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:14:48.348470Z",
     "start_time": "2024-09-24T19:14:48.343439Z"
    }
   },
   "outputs": [],
   "source": [
    "resource = resource.loc[resource['sinr'] > 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "5a715ab2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:14:50.591533Z",
     "start_time": "2024-09-24T19:14:50.587226Z"
    }
   },
   "outputs": [],
   "source": [
    "resource.iloc[:,0]=resource.iloc[:,0].astype(str).map({'tcp':1,'udp':0})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8e2034d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T16:53:09.190290Z",
     "start_time": "2024-09-24T16:53:09.186568Z"
    }
   },
   "source": [
    "# read from here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "bf68aef5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:18:47.349395Z",
     "start_time": "2024-09-24T19:18:46.923214Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 379 entries, 0 to 378\n",
      "Data columns (total 13 columns):\n",
      " #   Column                      Non-Null Count  Dtype  \n",
      "---  ------                      --------------  -----  \n",
      " 0   protocol                    379 non-null    int64  \n",
      " 1   nav                         379 non-null    int64  \n",
      " 2   ap_from_ap_0_sum_ant_rssi   379 non-null    float64\n",
      " 3   sta_to_ap_0_sum_ant_rssi    379 non-null    float64\n",
      " 4   sta_to_ap_1_sum_ant_rssi    379 non-null    float64\n",
      " 5   sta_from_ap_0_sum_ant_rssi  379 non-null    float64\n",
      " 6   sta_from_ap_1_sum_ant_rssi  379 non-null    float64\n",
      " 7   sta_from_sta_0_rssi         379 non-null    int64  \n",
      " 8   pro_pd                      379 non-null    float64\n",
      " 9   pro_ed                      379 non-null    float64\n",
      " 10  pro_nav                     379 non-null    float64\n",
      " 11  state                       379 non-null    int64  \n",
      " 12  sinr                        379 non-null    float64\n",
      "dtypes: float64(9), int64(4)\n",
      "memory usage: 38.6 KB\n"
     ]
    }
   ],
   "source": [
    "# resource.to_csv('programe1_predict_test_2_type_2.csv')\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "resource = pd.read_excel('programe1_2ap_processed.xlsx')\n",
    "resource = resource.sample(frac=1).reset_index(drop=True)\n",
    "\n",
    "target=resource['seq_time']\n",
    "resource=resource.drop('seq_time',axis=1)\n",
    "resource.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "e6764829",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:18:57.863546Z",
     "start_time": "2024-09-24T19:18:50.883261Z"
    }
   },
   "outputs": [],
   "source": [
    "from sklearn.ensemble import RandomForestRegressor\n",
    "\n",
    "rfg=RandomForestRegressor(n_estimators=100,criterion='friedman_mse',random_state=0,oob_score=False)\n",
    "\n",
    "\n",
    "# target_0_phy=pd.DataFrame(target_0_phy)\n",
    "\n",
    "rfg=rfg.fit(resource,target)\n",
    "\n",
    "# rfg0=RandomForestRegressor(n_estimators=100,criterion='friedman_mse',random_state=0,oob_score=False)\n",
    "# rfg0=rfg0.fit(resource,target_0.to_numpy())\n",
    "from sklearn.model_selection import cross_val_score\n",
    "\n",
    "res=cross_val_score(rfg,resource,target,cv=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "9af5d3f8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:19:03.009192Z",
     "start_time": "2024-09-24T19:19:03.004103Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0.87119611, 0.92985955, 0.89552621, 0.92080221, 0.776792  ,\n",
       "       0.95553315, 0.82261539, 0.79558939, 0.86823936, 0.91384649])"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "e0d609c0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:19:04.834792Z",
     "start_time": "2024-09-24T19:19:04.829671Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.8749999863348752"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74f03026",
   "metadata": {},
   "source": [
    "# XGBoost"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "4475d25f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:09:07.036611Z",
     "start_time": "2024-09-24T17:09:06.711000Z"
    }
   },
   "outputs": [],
   "source": [
    "from xgboost import XGBRegressor as XGBR\n",
    "reg=XGBR(n_estimators=100).fit(resource.to_numpy(),target.to_numpy())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "3d3060e1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:09:16.875915Z",
     "start_time": "2024-09-24T17:09:15.546503Z"
    }
   },
   "outputs": [],
   "source": [
    "res=cross_val_score(reg,resource.to_numpy(),target.to_numpy(),cv=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "6c39e86d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:09:21.138327Z",
     "start_time": "2024-09-24T17:09:21.133334Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.834313413362888"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "81bc5462",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:09:23.163371Z",
     "start_time": "2024-09-24T17:09:23.158292Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0.917585  , 0.82404495, 0.89327948, 0.92782213, 0.72041921,\n",
       "       0.89140723, 0.71889142, 0.79739348, 0.95518596, 0.69710528])"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec760ebd",
   "metadata": {},
   "source": [
    "# 岭回归"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "278751cc",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:09:36.853897Z",
     "start_time": "2024-09-24T17:09:36.787809Z"
    }
   },
   "outputs": [],
   "source": [
    "from sklearn.linear_model import Ridge\n",
    "\n",
    "regcv=Ridge(alpha=1)\n",
    "regcv=regcv.fit(resource,target)\n",
    "# regcv.score(resource_merge,target_merge)\n",
    "\n",
    "res=cross_val_score(regcv,resource,target,cv=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "da6a4b81",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:09:38.492968Z",
     "start_time": "2024-09-24T17:09:38.488503Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.8519286230798354"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "09243b45",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:09:40.512984Z",
     "start_time": "2024-09-24T17:09:40.508642Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0.87765508, 0.89335851, 0.90123807, 0.82937039, 0.74822159,\n",
       "       0.82066127, 0.85470823, 0.7702948 , 0.94442736, 0.87935094])"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6fdf5dd0",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "# 构造predict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "34ce6ec0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:48:49.020072Z",
     "start_time": "2024-09-24T17:48:49.003236Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "predict=pd.read_csv('./RAWDATA/test_set_1_2ap.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1bb140df",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:48:49.725150Z",
     "start_time": "2024-09-24T17:48:49.717037Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 80 entries, 0 to 79\n",
      "Data columns (total 46 columns):\n",
      " #   Column                       Non-Null Count  Dtype  \n",
      "---  ------                       --------------  -----  \n",
      " 0   test_id                      80 non-null     int64  \n",
      " 1   test_dur                     80 non-null     int64  \n",
      " 2   loc_id                       80 non-null     object \n",
      " 3   protocol                     80 non-null     object \n",
      " 4   pkt_len                      80 non-null     int64  \n",
      " 5   bss_id                       80 non-null     int64  \n",
      " 6   ap_name                      80 non-null     object \n",
      " 7   ap_mac                       80 non-null     object \n",
      " 8   ap_id                        80 non-null     object \n",
      " 9   pd                           80 non-null     int64  \n",
      " 10  ed                           80 non-null     int64  \n",
      " 11  nav                          80 non-null     int64  \n",
      " 12  eirp                         80 non-null     int64  \n",
      " 13  ap_from_ap_0_sum_ant_rssi    40 non-null     object \n",
      " 14  ap_from_ap_0_max_ant_rssi    40 non-null     object \n",
      " 15  ap_from_ap_0_mean_ant_rssi   40 non-null     object \n",
      " 16  ap_from_ap_1_sum_ant_rssi    40 non-null     object \n",
      " 17  ap_from_ap_1_max_ant_rssi    40 non-null     object \n",
      " 18  ap_from_ap_1_mean_ant_rssi   40 non-null     object \n",
      " 19  sta_mac                      80 non-null     object \n",
      " 20  sta_id                       80 non-null     object \n",
      " 21  sta_to_ap_0_sum_ant_rssi     80 non-null     object \n",
      " 22  sta_to_ap_0_max_ant_rssi     80 non-null     object \n",
      " 23  sta_to_ap_0_mean_ant_rssi    80 non-null     object \n",
      " 24  sta_to_ap_1_sum_ant_rssi     80 non-null     object \n",
      " 25  sta_to_ap_1_max_ant_rssi     80 non-null     object \n",
      " 26  sta_to_ap_1_mean_ant_rssi    80 non-null     object \n",
      " 27  sta_from_ap_0_sum_ant_rssi   80 non-null     object \n",
      " 28  sta_from_ap_0_max_ant_rssi   80 non-null     object \n",
      " 29  sta_from_ap_0_mean_ant_rssi  80 non-null     object \n",
      " 30  sta_from_ap_1_sum_ant_rssi   80 non-null     object \n",
      " 31  sta_from_ap_1_max_ant_rssi   80 non-null     object \n",
      " 32  sta_from_ap_1_mean_ant_rssi  80 non-null     object \n",
      " 33  sta_from_sta_0_rssi          0 non-null      float64\n",
      " 34  sta_from_sta_1_rssi          0 non-null      float64\n",
      " 35  nss                          80 non-null     int64  \n",
      " 36  mcs                          80 non-null     int64  \n",
      " 37  per                          80 non-null     float64\n",
      " 38  num_ampdu                    0 non-null      float64\n",
      " 39  ppdu_dur                     0 non-null      float64\n",
      " 40  other_air_time               0 non-null      float64\n",
      " 41  seq_time                     0 non-null      float64\n",
      " 42  throughput                   0 non-null      float64\n",
      " 43  predict seq_time             0 non-null      float64\n",
      " 44  predict throughput           0 non-null      float64\n",
      " 45  error%                       0 non-null      float64\n",
      "dtypes: float64(11), int64(10), object(25)\n",
      "memory usage: 28.9+ KB\n"
     ]
    }
   ],
   "source": [
    "predict.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4b23e15d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:50:52.871221Z",
     "start_time": "2024-09-24T17:50:52.865944Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "columns=[0,1,2,4,6,7,8,12,19,20,22,23,25,26,28,29,31,32,35,36,37,38,39,40,42,43,44,45]\n",
    "predict=predict.drop(predict.columns[columns],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "628f15d9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:52:25.943444Z",
     "start_time": "2024-09-24T17:52:25.915317Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# 合并操作\n",
    "for i in range(predict.shape[0]):\n",
    "    if predict.iloc[i,list(predict.columns).index('bss_id')]==0:\n",
    "        predict.iloc[i,list(predict.columns).index('ap_from_ap_0_sum_ant_rssi')]=predict.iloc[i,list(predict.columns).index('ap_from_ap_1_sum_ant_rssi')]\n",
    "        predict.iloc[i,list(predict.columns).index('ap_from_ap_0_max_ant_rssi')]=predict.iloc[i,list(predict.columns).index('ap_from_ap_1_max_ant_rssi')]\n",
    "        predict.iloc[i,list(predict.columns).index('ap_from_ap_0_mean_ant_rssi')]=predict.iloc[i,list(predict.columns).index('ap_from_ap_1_mean_ant_rssi')]\n",
    "        predict.iloc[i,list(predict.columns).index('sta_from_sta_0_rssi')]=predict.iloc[i,list(predict.columns).index('sta_from_sta_1_rssi')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f84f2397",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:52:52.478696Z",
     "start_time": "2024-09-24T17:52:52.475286Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "predict=predict.drop(['ap_from_ap_1_sum_ant_rssi','ap_from_ap_1_max_ant_rssi',\n",
    "                       'ap_from_ap_1_mean_ant_rssi','sta_from_sta_1_rssi'],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ef3714e9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:52:57.484314Z",
     "start_time": "2024-09-24T17:52:57.476178Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 80 entries, 0 to 79\n",
      "Data columns (total 14 columns):\n",
      " #   Column                      Non-Null Count  Dtype  \n",
      "---  ------                      --------------  -----  \n",
      " 0   protocol                    80 non-null     object \n",
      " 1   bss_id                      80 non-null     int64  \n",
      " 2   pd                          80 non-null     int64  \n",
      " 3   ed                          80 non-null     int64  \n",
      " 4   nav                         80 non-null     int64  \n",
      " 5   ap_from_ap_0_sum_ant_rssi   80 non-null     object \n",
      " 6   ap_from_ap_0_max_ant_rssi   80 non-null     object \n",
      " 7   ap_from_ap_0_mean_ant_rssi  80 non-null     object \n",
      " 8   sta_to_ap_0_sum_ant_rssi    80 non-null     object \n",
      " 9   sta_to_ap_1_sum_ant_rssi    80 non-null     object \n",
      " 10  sta_from_ap_0_sum_ant_rssi  80 non-null     object \n",
      " 11  sta_from_ap_1_sum_ant_rssi  80 non-null     object \n",
      " 12  sta_from_sta_0_rssi         0 non-null      float64\n",
      " 13  seq_time                    0 non-null      float64\n",
      "dtypes: float64(2), int64(4), object(8)\n",
      "memory usage: 8.9+ KB\n"
     ]
    }
   ],
   "source": [
    "predict.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f1b2dc22",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:55:15.620355Z",
     "start_time": "2024-09-24T17:55:15.614332Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# 定义管理rssi中异常值数据，并创造出新特征值\n",
    "def cal_pd(apmax,pd):\n",
    "    apmax = eval(apmax)\n",
    "    comparison_result = [1 if x >= pd else 0 for x in apmax]\n",
    "    proportion_of_ones = sum(comparison_result) / len(apmax)\n",
    "    return proportion_of_ones\n",
    "\n",
    "def cal_ed(apmax,ed):\n",
    "    apmax = eval(apmax)\n",
    "    comparison_result = [1 if x >= ed else 0 for x in apmax]\n",
    "    proportion_of_ones = sum(comparison_result) / len(apmax)\n",
    "    return proportion_of_ones\n",
    "\n",
    "def cal_nav(apmean,nav):\n",
    "    apmean = eval(apmean)\n",
    "    comparison_result = [1 if x >= nav else 0 for x in apmean]\n",
    "\n",
    "    # 计算 1 的占比\n",
    "    proportion_of_ones = sum(comparison_result) / len(apmean)\n",
    "    return proportion_of_ones    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f9e399d7",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:55:15.998840Z",
     "start_time": "2024-09-24T17:55:15.965707Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "predict['pro_pd'] = predict.apply(lambda row: cal_pd(row['ap_from_ap_0_max_ant_rssi'], row['pd']), axis=1)\n",
    "predict['pro_ed'] = predict.apply(lambda row: cal_ed(row['ap_from_ap_0_max_ant_rssi'], row['ed']), axis=1)\n",
    "predict['pro_nav'] = predict.apply(lambda row: cal_nav(row['ap_from_ap_0_mean_ant_rssi'], row['nav']), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ca53782b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:55:50.131839Z",
     "start_time": "2024-09-24T17:55:50.127518Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "drop_columns=['pd','ed','ap_from_ap_0_mean_ant_rssi','ap_from_ap_0_max_ant_rssi']\n",
    "\n",
    "# drop_columns=['pd','ed','ap_from_ap_0_mean_ant_rssi','ap_from_ap_0_max_ant_rssi'\n",
    "#               ,'sta_to_ap_0_max_ant_rssi','sta_to_ap_0_mean_ant_rssi'\n",
    "#               ,'sta_to_ap_1_max_ant_rssi','sta_to_ap_1_mean_ant_rssi'\n",
    "#              ,'sta_from_ap_0_max_ant_rssi','sta_from_ap_0_mean_ant_rssi'\n",
    "#              ,'sta_from_ap_1_max_ant_rssi','sta_from_ap_1_mean_ant_rssi']\n",
    "\n",
    "predict=predict.drop(drop_columns,axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "d914dc0f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:56:36.695844Z",
     "start_time": "2024-09-24T17:56:36.642407Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# 对RSSI求平均值处理\n",
    "for i in range(3,8):\n",
    "    predict.iloc[:,i]=predict.iloc[:,i].apply(lambda x: sum(eval(x))/len(eval(x)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "8043374d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:58:24.485796Z",
     "start_time": "2024-09-24T17:58:22.365438Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\Anaconda\\Lib\\site-packages\\sklearn\\cluster\\_kmeans.py:1412: FutureWarning: The default value of `n_init` will change from 10 to 'auto' in 1.4. Set the value of `n_init` explicitly to suppress the warning\n",
      "  super()._check_params_vs_input(X, default_n_init=10)\n",
      "D:\\Anaconda\\Lib\\site-packages\\sklearn\\cluster\\_kmeans.py:1436: UserWarning: KMeans is known to have a memory leak on Windows with MKL, when there are less chunks than available threads. You can avoid it by setting the environment variable OMP_NUM_THREADS=1.\n",
      "  warnings.warn(\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cluster centers:\n",
      "          0             1         2\n",
      "0  0.053833  2.124077e-03  0.005862\n",
      "1  0.876570  1.424501e-03  0.893614\n",
      "2  0.772115 -4.336809e-19  0.107937\n",
      "cluster\n",
      "1    36\n",
      "0    34\n",
      "2    10\n",
      "Name: count, dtype: int64\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\ShenFei\\AppData\\Local\\Temp\\ipykernel_18632\\3987966281.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",
      "  df['cluster'] = kmeans.fit_predict(data_scaled)\n"
     ]
    }
   ],
   "source": [
    "# 选择pro的聚类个数\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from sklearn.cluster import KMeans\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.metrics import silhouette_score\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "data_scaled=predict.iloc[:,[10,11,12]]\n",
    "\n",
    "df=data_scaled\n",
    "k = 3\n",
    "kmeans = KMeans(n_clusters=k, random_state=42)\n",
    "df['cluster'] = kmeans.fit_predict(data_scaled)\n",
    "\n",
    "# 查看每个簇的平均特征值\n",
    "cluster_centers = pd.DataFrame(kmeans.cluster_centers_)\n",
    "# columns=['pro_pd', 'pro_ed', 'pro_nav']\n",
    "\n",
    "print(f\"Cluster centers:\\n{cluster_centers}\")\n",
    "\n",
    "# 查看每个簇的样本数量\n",
    "print(df['cluster'].value_counts())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "adb45f72",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:58:51.737798Z",
     "start_time": "2024-09-24T17:58:51.734017Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "predict['state']=df['cluster']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "10f13ec2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:58:58.327713Z",
     "start_time": "2024-09-24T17:58:58.319327Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 80 entries, 0 to 79\n",
      "Data columns (total 14 columns):\n",
      " #   Column                      Non-Null Count  Dtype  \n",
      "---  ------                      --------------  -----  \n",
      " 0   protocol                    80 non-null     object \n",
      " 1   bss_id                      80 non-null     int64  \n",
      " 2   nav                         80 non-null     int64  \n",
      " 3   ap_from_ap_0_sum_ant_rssi   80 non-null     object \n",
      " 4   sta_to_ap_0_sum_ant_rssi    80 non-null     object \n",
      " 5   sta_to_ap_1_sum_ant_rssi    80 non-null     object \n",
      " 6   sta_from_ap_0_sum_ant_rssi  80 non-null     object \n",
      " 7   sta_from_ap_1_sum_ant_rssi  80 non-null     object \n",
      " 8   sta_from_sta_0_rssi         0 non-null      float64\n",
      " 9   seq_time                    0 non-null      float64\n",
      " 10  pro_pd                      80 non-null     float64\n",
      " 11  pro_ed                      80 non-null     float64\n",
      " 12  pro_nav                     80 non-null     float64\n",
      " 13  state                       80 non-null     int32  \n",
      "dtypes: float64(5), int32(1), int64(2), object(6)\n",
      "memory usage: 8.6+ KB\n"
     ]
    }
   ],
   "source": [
    "predict.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "9adadc9b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:59:43.329941Z",
     "start_time": "2024-09-24T17:59:43.310582Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0     27.700000\n",
      "1     31.050000\n",
      "2     27.700000\n",
      "3     31.050000\n",
      "4     28.625000\n",
      "        ...    \n",
      "75    16.843590\n",
      "76    31.207143\n",
      "77    19.727632\n",
      "78    31.207143\n",
      "79    19.727632\n",
      "Name: sinr, Length: 80, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# 信干比采取dbm直接相减得到db\n",
    "def func(x, y, state):\n",
    "    if state == 0:\n",
    "        return x + 100\n",
    "    else:\n",
    "        return x - y\n",
    "\n",
    "    \n",
    "# 创建一个新列来存储 SINR 计算的结果\n",
    "sinr = []\n",
    "df = predict\n",
    "# 遍历 DataFrame 的每一行\n",
    "for i in range(len(df)):\n",
    "    if df.iloc[i, list(df.columns).index('bss_id')] == 1:  # ap1\n",
    "        result = func(df.iloc[i, list(df.columns).index('sta_from_ap_1_sum_ant_rssi')], df.iloc[i, list(df.columns).index('sta_from_ap_0_sum_ant_rssi')],df.iloc[i, list(df.columns).index('state')])\n",
    "    else:  # ap2\n",
    "        result = func(df.iloc[i, list(df.columns).index('sta_from_ap_0_sum_ant_rssi')], df.iloc[i, list(df.columns).index('sta_from_ap_1_sum_ant_rssi')],df.iloc[i, list(df.columns).index('state')])\n",
    "    sinr.append(result)\n",
    "\n",
    "# 将计算结果添加为新列\n",
    "df['sinr'] = sinr\n",
    "\n",
    "# 输出结果\n",
    "print(df['sinr'])\n",
    "predict = predict.drop('bss_id',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "ea257d62",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T18:00:39.866648Z",
     "start_time": "2024-09-24T18:00:39.862126Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "predict.iloc[:,0]=predict.iloc[:,0].astype(str).map({'tcp':1,'udp':0})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "539bca4c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T18:01:51.576224Z",
     "start_time": "2024-09-24T18:01:51.569219Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# predict.to_csv('programe1_predict_type2.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c1e0eb9",
   "metadata": {},
   "source": [
    "# predict from here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "1f72a270",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:11:14.913949Z",
     "start_time": "2024-09-24T19:11:14.905878Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 379 entries, 0 to 378\n",
      "Data columns (total 13 columns):\n",
      " #   Column                      Non-Null Count  Dtype  \n",
      "---  ------                      --------------  -----  \n",
      " 0   protocol                    379 non-null    int64  \n",
      " 1   nav                         379 non-null    int64  \n",
      " 2   ap_from_ap_0_sum_ant_rssi   379 non-null    float64\n",
      " 3   sta_to_ap_0_sum_ant_rssi    379 non-null    float64\n",
      " 4   sta_to_ap_1_sum_ant_rssi    379 non-null    float64\n",
      " 5   sta_from_ap_0_sum_ant_rssi  379 non-null    float64\n",
      " 6   sta_from_ap_1_sum_ant_rssi  379 non-null    float64\n",
      " 7   sta_from_sta_0_rssi         379 non-null    int64  \n",
      " 8   pro_pd                      379 non-null    float64\n",
      " 9   pro_ed                      379 non-null    float64\n",
      " 10  pro_nav                     379 non-null    float64\n",
      " 11  state                       379 non-null    int64  \n",
      " 12  sinr                        379 non-null    float64\n",
      "dtypes: float64(9), int64(4)\n",
      "memory usage: 38.6 KB\n"
     ]
    }
   ],
   "source": [
    "resource.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "25143a29",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:18:00.300084Z",
     "start_time": "2024-09-24T19:18:00.294466Z"
    }
   },
   "outputs": [],
   "source": [
    "predict=pd.read_csv('programe1_predict_test_2_type_2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "d8d26125",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:19:12.315763Z",
     "start_time": "2024-09-24T19:19:12.307079Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 64 entries, 0 to 63\n",
      "Data columns (total 13 columns):\n",
      " #   Column                      Non-Null Count  Dtype  \n",
      "---  ------                      --------------  -----  \n",
      " 0   protocol                    64 non-null     int64  \n",
      " 1   nav                         64 non-null     int64  \n",
      " 2   ap_from_ap_0_sum_ant_rssi   64 non-null     float64\n",
      " 3   sta_to_ap_0_sum_ant_rssi    64 non-null     float64\n",
      " 4   sta_to_ap_1_sum_ant_rssi    64 non-null     float64\n",
      " 5   sta_from_ap_0_sum_ant_rssi  64 non-null     float64\n",
      " 6   sta_from_ap_1_sum_ant_rssi  64 non-null     float64\n",
      " 7   sta_from_sta_0_rssi         64 non-null     int64  \n",
      " 8   pro_pd                      64 non-null     float64\n",
      " 9   pro_ed                      64 non-null     float64\n",
      " 10  pro_nav                     64 non-null     float64\n",
      " 11  state                       64 non-null     int64  \n",
      " 12  sinr                        64 non-null     float64\n",
      "dtypes: float64(9), int64(4)\n",
      "memory usage: 6.6 KB\n"
     ]
    }
   ],
   "source": [
    "predict.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "35705ffd",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:18:10.666139Z",
     "start_time": "2024-09-24T19:18:10.662137Z"
    }
   },
   "outputs": [],
   "source": [
    "predict=predict.drop('seq_time',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "76178ae3",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T18:09:58.583814Z",
     "start_time": "2024-09-24T18:09:58.578313Z"
    }
   },
   "outputs": [],
   "source": [
    "resource=resource.drop('sta_from_sta_0_rssi',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "bc089f66",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T18:10:35.864775Z",
     "start_time": "2024-09-24T18:10:28.126352Z"
    }
   },
   "outputs": [],
   "source": [
    "from sklearn.ensemble import RandomForestRegressor\n",
    "\n",
    "rfg=RandomForestRegressor(n_estimators=100,criterion='friedman_mse',random_state=0,oob_score=False)\n",
    "\n",
    "\n",
    "rfg=rfg.fit(resource,target)\n",
    "\n",
    "from sklearn.model_selection import cross_val_score\n",
    "\n",
    "res=cross_val_score(rfg,resource,target,cv=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "3f3ed5cb",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T18:10:40.276615Z",
     "start_time": "2024-09-24T18:10:40.269009Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.8722996397546288"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "9593d582",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:19:21.232940Z",
     "start_time": "2024-09-24T19:19:21.219648Z"
    }
   },
   "outputs": [],
   "source": [
    "res=rfg.predict(predict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "b6f8397b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:19:34.513548Z",
     "start_time": "2024-09-24T19:19:34.486068Z"
    }
   },
   "outputs": [],
   "source": [
    "pd.DataFrame(res).to_excel('programe1_test_2_type2_res.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "79a4aa0e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7c7e1bd2",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4d177b3",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5bffb7fa",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e510440",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.4"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}