Programe1_type2-重构.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "fca531fe",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:25:36.185670Z",
     "start_time": "2024-09-24T17:25:35.838677Z"
    }
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "983eafdd",
   "metadata": {},
   "source": [
    "## 读入数据，并对2基站情况数据进行合并与拆解"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "fdea2945",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:25:36.920865Z",
     "start_time": "2024-09-24T17:25:36.887258Z"
    }
   },
   "outputs": [],
   "source": [
    "part1=pd.read_csv('RAWDATA/training_set_2ap_loc0_nav82.csv')\n",
    "part2=pd.read_csv('RAWDATA/training_set_2ap_loc0_nav86.csv')\n",
    "part3=pd.read_csv('RAWDATA/training_set_2ap_loc1_nav82.csv')\n",
    "part4=pd.read_csv('RAWDATA/training_set_2ap_loc1_nav86_new.csv')\n",
    "part5=pd.read_csv('RAWDATA/training_set_2ap_loc2_nav82.csv')\n",
    "\n",
    "resource=pd.concat([part1,part2,part3,part4,part5],axis=0)\n",
    "resource=resource.iloc[:390,:]    #删掉异常的行值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "38fd28cb",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:25:37.497049Z",
     "start_time": "2024-09-24T17:25:37.490753Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 390 entries, 0 to 77\n",
      "Data columns (total 43 columns):\n",
      " #   Column                       Non-Null Count  Dtype  \n",
      "---  ------                       --------------  -----  \n",
      " 0   test_id                      390 non-null    int64  \n",
      " 1   test_dur                     390 non-null    int64  \n",
      " 2   loc_id                       390 non-null    object \n",
      " 3   protocol                     390 non-null    object \n",
      " 4   pkt_len                      390 non-null    int64  \n",
      " 5   bss_id                       390 non-null    int64  \n",
      " 6   ap_name                      390 non-null    object \n",
      " 7   ap_mac                       390 non-null    object \n",
      " 8   ap_id                        390 non-null    object \n",
      " 9   pd                           390 non-null    int64  \n",
      " 10  ed                           390 non-null    int64  \n",
      " 11  nav                          390 non-null    int64  \n",
      " 12  eirp                         390 non-null    int64  \n",
      " 13  ap_from_ap_0_sum_ant_rssi    195 non-null    object \n",
      " 14  ap_from_ap_0_max_ant_rssi    195 non-null    object \n",
      " 15  ap_from_ap_0_mean_ant_rssi   195 non-null    object \n",
      " 16  ap_from_ap_1_sum_ant_rssi    195 non-null    object \n",
      " 17  ap_from_ap_1_max_ant_rssi    195 non-null    object \n",
      " 18  ap_from_ap_1_mean_ant_rssi   195 non-null    object \n",
      " 19  sta_mac                      390 non-null    object \n",
      " 20  sta_id                       390 non-null    object \n",
      " 21  sta_to_ap_0_sum_ant_rssi     390 non-null    object \n",
      " 22  sta_to_ap_0_max_ant_rssi     390 non-null    object \n",
      " 23  sta_to_ap_0_mean_ant_rssi    390 non-null    object \n",
      " 24  sta_to_ap_1_sum_ant_rssi     390 non-null    object \n",
      " 25  sta_to_ap_1_max_ant_rssi     390 non-null    object \n",
      " 26  sta_to_ap_1_mean_ant_rssi    390 non-null    object \n",
      " 27  sta_from_ap_0_sum_ant_rssi   390 non-null    object \n",
      " 28  sta_from_ap_0_max_ant_rssi   390 non-null    object \n",
      " 29  sta_from_ap_0_mean_ant_rssi  390 non-null    object \n",
      " 30  sta_from_ap_1_sum_ant_rssi   390 non-null    object \n",
      " 31  sta_from_ap_1_max_ant_rssi   390 non-null    object \n",
      " 32  sta_from_ap_1_mean_ant_rssi  390 non-null    object \n",
      " 33  sta_from_sta_0_rssi          195 non-null    float64\n",
      " 34  sta_from_sta_1_rssi          195 non-null    float64\n",
      " 35  nss                          390 non-null    float64\n",
      " 36  mcs                          390 non-null    float64\n",
      " 37  per                          390 non-null    float64\n",
      " 38  num_ampdu                    390 non-null    float64\n",
      " 39  ppdu_dur                     390 non-null    float64\n",
      " 40  other_air_time               390 non-null    float64\n",
      " 41  seq_time                     390 non-null    float64\n",
      " 42  throughput                   390 non-null    float64\n",
      "dtypes: float64(10), int64(8), object(25)\n",
      "memory usage: 134.1+ KB\n"
     ]
    }
   ],
   "source": [
    "resource.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "32bffc6f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:25:54.508614Z",
     "start_time": "2024-09-24T17:25:54.503273Z"
    }
   },
   "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]\n",
    "resource=resource.drop(resource.columns[columns],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1c6ebc09",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:25:58.024976Z",
     "start_time": "2024-09-24T17:25:58.016471Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 390 entries, 0 to 77\n",
      "Data columns (total 18 columns):\n",
      " #   Column                      Non-Null Count  Dtype  \n",
      "---  ------                      --------------  -----  \n",
      " 0   protocol                    390 non-null    object \n",
      " 1   bss_id                      390 non-null    int64  \n",
      " 2   pd                          390 non-null    int64  \n",
      " 3   ed                          390 non-null    int64  \n",
      " 4   nav                         390 non-null    int64  \n",
      " 5   ap_from_ap_0_sum_ant_rssi   195 non-null    object \n",
      " 6   ap_from_ap_0_max_ant_rssi   195 non-null    object \n",
      " 7   ap_from_ap_0_mean_ant_rssi  195 non-null    object \n",
      " 8   ap_from_ap_1_sum_ant_rssi   195 non-null    object \n",
      " 9   ap_from_ap_1_max_ant_rssi   195 non-null    object \n",
      " 10  ap_from_ap_1_mean_ant_rssi  195 non-null    object \n",
      " 11  sta_to_ap_0_sum_ant_rssi    390 non-null    object \n",
      " 12  sta_to_ap_1_sum_ant_rssi    390 non-null    object \n",
      " 13  sta_from_ap_0_sum_ant_rssi  390 non-null    object \n",
      " 14  sta_from_ap_1_sum_ant_rssi  390 non-null    object \n",
      " 15  sta_from_sta_0_rssi         195 non-null    float64\n",
      " 16  sta_from_sta_1_rssi         195 non-null    float64\n",
      " 17  seq_time                    390 non-null    float64\n",
      "dtypes: float64(3), int64(4), object(11)\n",
      "memory usage: 57.9+ KB\n"
     ]
    }
   ],
   "source": [
    "resource.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "830cd31d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:02:32.719042Z",
     "start_time": "2024-09-24T17:02:32.638653Z"
    }
   },
   "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": 34,
   "id": "22da7af2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:02:33.653278Z",
     "start_time": "2024-09-24T17:02:33.648494Z"
    }
   },
   "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": 35,
   "id": "6feb6b0d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:02:34.790223Z",
     "start_time": "2024-09-24T17:02:34.782715Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 390 entries, 0 to 77\n",
      "Data columns (total 14 columns):\n",
      " #   Column                      Non-Null Count  Dtype  \n",
      "---  ------                      --------------  -----  \n",
      " 0   protocol                    390 non-null    object \n",
      " 1   bss_id                      390 non-null    int64  \n",
      " 2   pd                          390 non-null    int64  \n",
      " 3   ed                          390 non-null    int64  \n",
      " 4   nav                         390 non-null    int64  \n",
      " 5   ap_from_ap_0_sum_ant_rssi   390 non-null    object \n",
      " 6   ap_from_ap_0_max_ant_rssi   390 non-null    object \n",
      " 7   ap_from_ap_0_mean_ant_rssi  390 non-null    object \n",
      " 8   sta_to_ap_0_sum_ant_rssi    390 non-null    object \n",
      " 9   sta_to_ap_1_sum_ant_rssi    390 non-null    object \n",
      " 10  sta_from_ap_0_sum_ant_rssi  390 non-null    object \n",
      " 11  sta_from_ap_1_sum_ant_rssi  390 non-null    object \n",
      " 12  sta_from_sta_0_rssi         390 non-null    float64\n",
      " 13  seq_time                    390 non-null    float64\n",
      "dtypes: float64(2), int64(4), object(8)\n",
      "memory usage: 45.7+ KB\n"
     ]
    }
   ],
   "source": [
    "resource.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "43229b67",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:02:35.898251Z",
     "start_time": "2024-09-24T17:02:35.892150Z"
    }
   },
   "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": 37,
   "id": "e96a8bfc",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:02:37.108578Z",
     "start_time": "2024-09-24T17:02:36.956335Z"
    }
   },
   "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": 38,
   "id": "8ef6f7bd",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:02:37.960570Z",
     "start_time": "2024-09-24T17:02:37.956136Z"
    }
   },
   "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": 39,
   "id": "90a592e7",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:02:38.802617Z",
     "start_time": "2024-09-24T17:02:38.794152Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 390 entries, 0 to 77\n",
      "Data columns (total 13 columns):\n",
      " #   Column                      Non-Null Count  Dtype  \n",
      "---  ------                      --------------  -----  \n",
      " 0   protocol                    390 non-null    object \n",
      " 1   bss_id                      390 non-null    int64  \n",
      " 2   nav                         390 non-null    int64  \n",
      " 3   ap_from_ap_0_sum_ant_rssi   390 non-null    object \n",
      " 4   sta_to_ap_0_sum_ant_rssi    390 non-null    object \n",
      " 5   sta_to_ap_1_sum_ant_rssi    390 non-null    object \n",
      " 6   sta_from_ap_0_sum_ant_rssi  390 non-null    object \n",
      " 7   sta_from_ap_1_sum_ant_rssi  390 non-null    object \n",
      " 8   sta_from_sta_0_rssi         390 non-null    float64\n",
      " 9   seq_time                    390 non-null    float64\n",
      " 10  pro_pd                      390 non-null    float64\n",
      " 11  pro_ed                      390 non-null    float64\n",
      " 12  pro_nav                     390 non-null    float64\n",
      "dtypes: float64(5), int64(2), object(6)\n",
      "memory usage: 42.7+ KB\n"
     ]
    }
   ],
   "source": [
    "resource.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "1c93b1ec",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:02:40.247892Z",
     "start_time": "2024-09-24T17:02:39.888705Z"
    }
   },
   "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": 41,
   "id": "a0093abb",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:02:45.412943Z",
     "start_time": "2024-09-24T17:02:44.900059Z"
    }
   },
   "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=2.\n",
      "  warnings.warn(\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cluster centers:\n",
      "          0         1         2\n",
      "0  0.897140  0.003123  0.924633\n",
      "1  0.063595  0.001679  0.034990\n",
      "2  0.770176  0.001579  0.150017\n",
      "cluster\n",
      "0    179\n",
      "1    170\n",
      "2     41\n",
      "Name: count, dtype: int64\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\ShenFei\\AppData\\Local\\Temp\\ipykernel_6228\\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": 42,
   "id": "56e8b531",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:02:47.478851Z",
     "start_time": "2024-09-24T17:02:47.475312Z"
    }
   },
   "outputs": [],
   "source": [
    "resource['state']=df['cluster']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "6e23f777",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:02:48.300620Z",
     "start_time": "2024-09-24T17:02:48.292143Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 390 entries, 0 to 77\n",
      "Data columns (total 14 columns):\n",
      " #   Column                      Non-Null Count  Dtype  \n",
      "---  ------                      --------------  -----  \n",
      " 0   protocol                    390 non-null    object \n",
      " 1   bss_id                      390 non-null    int64  \n",
      " 2   nav                         390 non-null    int64  \n",
      " 3   ap_from_ap_0_sum_ant_rssi   390 non-null    object \n",
      " 4   sta_to_ap_0_sum_ant_rssi    390 non-null    object \n",
      " 5   sta_to_ap_1_sum_ant_rssi    390 non-null    object \n",
      " 6   sta_from_ap_0_sum_ant_rssi  390 non-null    object \n",
      " 7   sta_from_ap_1_sum_ant_rssi  390 non-null    object \n",
      " 8   sta_from_sta_0_rssi         390 non-null    float64\n",
      " 9   seq_time                    390 non-null    float64\n",
      " 10  pro_pd                      390 non-null    float64\n",
      " 11  pro_ed                      390 non-null    float64\n",
      " 12  pro_nav                     390 non-null    float64\n",
      " 13  state                       390 non-null    int32  \n",
      "dtypes: float64(5), int32(1), int64(2), object(6)\n",
      "memory usage: 44.2+ KB\n"
     ]
    }
   ],
   "source": [
    "resource.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "0b2e6f77",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:02:49.429307Z",
     "start_time": "2024-09-24T17:02:49.368714Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0     36.107692\n",
      "1     30.800000\n",
      "2     36.107692\n",
      "3     30.800000\n",
      "4     35.445588\n",
      "        ...    \n",
      "73    50.575000\n",
      "74    47.450000\n",
      "75    51.800000\n",
      "76    47.450000\n",
      "77    51.800000\n",
      "Name: sinr, Length: 390, 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": 45,
   "id": "91e6a373",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:02:50.360298Z",
     "start_time": "2024-09-24T17:02:50.354802Z"
    }
   },
   "outputs": [],
   "source": [
    "resource = resource.loc[resource['sinr'] > 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "5a715ab2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T17:02:51.655884Z",
     "start_time": "2024-09-24T17:02:51.651505Z"
    }
   },
   "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": 17,
   "id": "bf68aef5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:10:48.594874Z",
     "start_time": "2024-09-24T19:10:48.498855Z"
    }
   },
   "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_excel('programe1_2ap_processed.xlsx')\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": 18,
   "id": "e6764829",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:10:57.659467Z",
     "start_time": "2024-09-24T19:10:50.697069Z"
    }
   },
   "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": 20,
   "id": "9af5d3f8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:11:01.983490Z",
     "start_time": "2024-09-24T19:11:01.978262Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0.93366191, 0.94592084, 0.88200633, 0.94613386, 0.78499902,\n",
       "       0.74348904, 0.96421297, 0.84615034, 0.79132326, 0.92697379])"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "e0d609c0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T19:11:04.294762Z",
     "start_time": "2024-09-24T19:11:04.289106Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.8764871368827404"
      ]
     },
     "execution_count": 21,
     "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": 3,
   "id": "25143a29",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T18:07:31.717676Z",
     "start_time": "2024-09-24T18:07:31.707548Z"
    }
   },
   "outputs": [],
   "source": [
    "predict=pd.read_csv('programe1_predict_type2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d8d26125",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T18:07:39.111867Z",
     "start_time": "2024-09-24T18:07:39.103340Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 80 entries, 0 to 79\n",
      "Data columns (total 13 columns):\n",
      " #   Column                      Non-Null Count  Dtype  \n",
      "---  ------                      --------------  -----  \n",
      " 0   protocol                    80 non-null     int64  \n",
      " 1   nav                         80 non-null     int64  \n",
      " 2   ap_from_ap_0_sum_ant_rssi   80 non-null     float64\n",
      " 3   sta_to_ap_0_sum_ant_rssi    80 non-null     float64\n",
      " 4   sta_to_ap_1_sum_ant_rssi    80 non-null     float64\n",
      " 5   sta_from_ap_0_sum_ant_rssi  80 non-null     float64\n",
      " 6   sta_from_ap_1_sum_ant_rssi  80 non-null     float64\n",
      " 7   seq_time                    0 non-null      float64\n",
      " 8   pro_pd                      80 non-null     float64\n",
      " 9   pro_ed                      80 non-null     float64\n",
      " 10  pro_nav                     80 non-null     float64\n",
      " 11  state                       80 non-null     int64  \n",
      " 12  sinr                        80 non-null     float64\n",
      "dtypes: float64(10), int64(3)\n",
      "memory usage: 8.3 KB\n"
     ]
    }
   ],
   "source": [
    "predict.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "35705ffd",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T18:08:55.664317Z",
     "start_time": "2024-09-24T18:08:55.659821Z"
    }
   },
   "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": 10,
   "id": "9593d582",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T18:11:45.436107Z",
     "start_time": "2024-09-24T18:11:45.421683Z"
    }
   },
   "outputs": [],
   "source": [
    "res=rfg.predict(predict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b6f8397b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-24T18:12:25.850911Z",
     "start_time": "2024-09-24T18:12:25.819915Z"
    }
   },
   "outputs": [],
   "source": [
    "pd.DataFrame(res).to_excel('programe1_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
}