46. 计算每个学生成绩最相似的10个学生.ipynb · mengyuan/ant-learn-pandas

12月28日，「开源中国源创会年终盛典」珠海站再次回归！点击免费报名参会

forked from peiss/ant-learn-pandas

加入 Gitee

与超过 1200万开发者一起发现、参与优秀开源项目，私有仓库也完全免费：）

免费加入

文件

该仓库未声明开源许可证文件（LICENSE），使用请关注具体项目描述及其代码上游依赖。

克隆/下载

46. 计算每个学生成绩最相似的10个学生.ipynb 24.18 KB

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 计算每个学生成绩最相似的10个学生"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"./datas/student_grade/学生成绩.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>Python程序设计基础</th>\n",
       "      <th>计算机导论</th>\n",
       "      <th>离散数学</th>\n",
       "      <th>数据结构</th>\n",
       "      <th>C语言程序设计</th>\n",
       "      <th>java语言程序设计</th>\n",
       "      <th>算法导论</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>小明1</td>\n",
       "      <td>54</td>\n",
       "      <td>73</td>\n",
       "      <td>98</td>\n",
       "      <td>69</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>小明2</td>\n",
       "      <td>51</td>\n",
       "      <td>70</td>\n",
       "      <td>83</td>\n",
       "      <td>93</td>\n",
       "      <td>93</td>\n",
       "      <td>93</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>小明3</td>\n",
       "      <td>84</td>\n",
       "      <td>61</td>\n",
       "      <td>100</td>\n",
       "      <td>84</td>\n",
       "      <td>60</td>\n",
       "      <td>97</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>小明4</td>\n",
       "      <td>71</td>\n",
       "      <td>56</td>\n",
       "      <td>57</td>\n",
       "      <td>89</td>\n",
       "      <td>52</td>\n",
       "      <td>62</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>小明5</td>\n",
       "      <td>78</td>\n",
       "      <td>61</td>\n",
       "      <td>54</td>\n",
       "      <td>96</td>\n",
       "      <td>83</td>\n",
       "      <td>100</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    姓名  Python程序设计基础  计算机导论  离散数学  数据结构  C语言程序设计  java语言程序设计  算法导论\n",
       "0  小明1            54     73    98    69       76          78    67\n",
       "1  小明2            51     70    83    93       93          93    96\n",
       "2  小明3            84     61   100    84       60          97    71\n",
       "3  小明4            71     56    57    89       52          62    93\n",
       "4  小明5            78     61    54    96       83         100    56"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60, 8)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. 进行学生成绩的笛卡尔积"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"one\"] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>Python程序设计基础</th>\n",
       "      <th>计算机导论</th>\n",
       "      <th>离散数学</th>\n",
       "      <th>数据结构</th>\n",
       "      <th>C语言程序设计</th>\n",
       "      <th>java语言程序设计</th>\n",
       "      <th>算法导论</th>\n",
       "      <th>one</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>小明1</td>\n",
       "      <td>54</td>\n",
       "      <td>73</td>\n",
       "      <td>98</td>\n",
       "      <td>69</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>小明2</td>\n",
       "      <td>51</td>\n",
       "      <td>70</td>\n",
       "      <td>83</td>\n",
       "      <td>93</td>\n",
       "      <td>93</td>\n",
       "      <td>93</td>\n",
       "      <td>96</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>小明3</td>\n",
       "      <td>84</td>\n",
       "      <td>61</td>\n",
       "      <td>100</td>\n",
       "      <td>84</td>\n",
       "      <td>60</td>\n",
       "      <td>97</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    姓名  Python程序设计基础  计算机导论  离散数学  数据结构  C语言程序设计  java语言程序设计  算法导论  one\n",
       "0  小明1            54     73    98    69       76          78    67    1\n",
       "1  小明2            51     70    83    93       93          93    96    1\n",
       "2  小明3            84     61   100    84       60          97    71    1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge = pd.merge(left=df, right=df, left_on=\"one\", right_on=\"one\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名_x</th>\n",
       "      <th>Python程序设计基础_x</th>\n",
       "      <th>计算机导论_x</th>\n",
       "      <th>离散数学_x</th>\n",
       "      <th>数据结构_x</th>\n",
       "      <th>C语言程序设计_x</th>\n",
       "      <th>java语言程序设计_x</th>\n",
       "      <th>算法导论_x</th>\n",
       "      <th>one</th>\n",
       "      <th>姓名_y</th>\n",
       "      <th>Python程序设计基础_y</th>\n",
       "      <th>计算机导论_y</th>\n",
       "      <th>离散数学_y</th>\n",
       "      <th>数据结构_y</th>\n",
       "      <th>C语言程序设计_y</th>\n",
       "      <th>java语言程序设计_y</th>\n",
       "      <th>算法导论_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>小明1</td>\n",
       "      <td>54</td>\n",
       "      <td>73</td>\n",
       "      <td>98</td>\n",
       "      <td>69</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>小明1</td>\n",
       "      <td>54</td>\n",
       "      <td>73</td>\n",
       "      <td>98</td>\n",
       "      <td>69</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>小明1</td>\n",
       "      <td>54</td>\n",
       "      <td>73</td>\n",
       "      <td>98</td>\n",
       "      <td>69</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>小明2</td>\n",
       "      <td>51</td>\n",
       "      <td>70</td>\n",
       "      <td>83</td>\n",
       "      <td>93</td>\n",
       "      <td>93</td>\n",
       "      <td>93</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>小明1</td>\n",
       "      <td>54</td>\n",
       "      <td>73</td>\n",
       "      <td>98</td>\n",
       "      <td>69</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>小明3</td>\n",
       "      <td>84</td>\n",
       "      <td>61</td>\n",
       "      <td>100</td>\n",
       "      <td>84</td>\n",
       "      <td>60</td>\n",
       "      <td>97</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  姓名_x  Python程序设计基础_x  计算机导论_x  离散数学_x  数据结构_x  C语言程序设计_x  java语言程序设计_x  \\\n",
       "0  小明1              54       73      98      69         76            78   \n",
       "1  小明1              54       73      98      69         76            78   \n",
       "2  小明1              54       73      98      69         76            78   \n",
       "\n",
       "   算法导论_x  one 姓名_y  Python程序设计基础_y  计算机导论_y  离散数学_y  数据结构_y  C语言程序设计_y  \\\n",
       "0      67    1  小明1              54       73      98      69         76   \n",
       "1      67    1  小明2              51       70      83      93         93   \n",
       "2      67    1  小明3              84       61     100      84         60   \n",
       "\n",
       "   java语言程序设计_y  算法导论_y  \n",
       "0            78      67  \n",
       "1            93      96  \n",
       "2            97      71  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3600, 17)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. 两两计算相似度"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Python程序设计基础', '计算机导论', '离散数学', '数据结构', 'C语言程序设计', 'java语言程序设计', '算法导论']"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns = list(df.columns)\n",
    "columns.remove(\"姓名\")\n",
    "columns.remove(\"one\")\n",
    "columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def sim_fun(row):\n",
    "    sim_value = 0.0\n",
    "    for column in columns:\n",
    "        sim_value += abs(int(row[column+\"_x\"]) - int(row[column+\"_y\"]))\n",
    "    return sim_value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge[\"sim\"] = df_merge.apply(sim_fun, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名_x</th>\n",
       "      <th>Python程序设计基础_x</th>\n",
       "      <th>计算机导论_x</th>\n",
       "      <th>离散数学_x</th>\n",
       "      <th>数据结构_x</th>\n",
       "      <th>C语言程序设计_x</th>\n",
       "      <th>java语言程序设计_x</th>\n",
       "      <th>算法导论_x</th>\n",
       "      <th>one</th>\n",
       "      <th>姓名_y</th>\n",
       "      <th>Python程序设计基础_y</th>\n",
       "      <th>计算机导论_y</th>\n",
       "      <th>离散数学_y</th>\n",
       "      <th>数据结构_y</th>\n",
       "      <th>C语言程序设计_y</th>\n",
       "      <th>java语言程序设计_y</th>\n",
       "      <th>算法导论_y</th>\n",
       "      <th>sim</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>小明1</td>\n",
       "      <td>54</td>\n",
       "      <td>73</td>\n",
       "      <td>98</td>\n",
       "      <td>69</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>小明1</td>\n",
       "      <td>54</td>\n",
       "      <td>73</td>\n",
       "      <td>98</td>\n",
       "      <td>69</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>67</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>小明1</td>\n",
       "      <td>54</td>\n",
       "      <td>73</td>\n",
       "      <td>98</td>\n",
       "      <td>69</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>小明2</td>\n",
       "      <td>51</td>\n",
       "      <td>70</td>\n",
       "      <td>83</td>\n",
       "      <td>93</td>\n",
       "      <td>93</td>\n",
       "      <td>93</td>\n",
       "      <td>96</td>\n",
       "      <td>106.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>小明1</td>\n",
       "      <td>54</td>\n",
       "      <td>73</td>\n",
       "      <td>98</td>\n",
       "      <td>69</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>小明3</td>\n",
       "      <td>84</td>\n",
       "      <td>61</td>\n",
       "      <td>100</td>\n",
       "      <td>84</td>\n",
       "      <td>60</td>\n",
       "      <td>97</td>\n",
       "      <td>71</td>\n",
       "      <td>98.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  姓名_x  Python程序设计基础_x  计算机导论_x  离散数学_x  数据结构_x  C语言程序设计_x  java语言程序设计_x  \\\n",
       "0  小明1              54       73      98      69         76            78   \n",
       "1  小明1              54       73      98      69         76            78   \n",
       "2  小明1              54       73      98      69         76            78   \n",
       "\n",
       "   算法导论_x  one 姓名_y  Python程序设计基础_y  计算机导论_y  离散数学_y  数据结构_y  C语言程序设计_y  \\\n",
       "0      67    1  小明1              54       73      98      69         76   \n",
       "1      67    1  小明2              51       70      83      93         93   \n",
       "2      67    1  小明3              84       61     100      84         60   \n",
       "\n",
       "   java语言程序设计_y  算法导论_y    sim  \n",
       "0            78      67    0.0  \n",
       "1            93      96  106.0  \n",
       "2            97      71   98.0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge = df_merge[df_merge[\"姓名_x\"] != df_merge[\"姓名_y\"]].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名_x</th>\n",
       "      <th>Python程序设计基础_x</th>\n",
       "      <th>计算机导论_x</th>\n",
       "      <th>离散数学_x</th>\n",
       "      <th>数据结构_x</th>\n",
       "      <th>C语言程序设计_x</th>\n",
       "      <th>java语言程序设计_x</th>\n",
       "      <th>算法导论_x</th>\n",
       "      <th>one</th>\n",
       "      <th>姓名_y</th>\n",
       "      <th>Python程序设计基础_y</th>\n",
       "      <th>计算机导论_y</th>\n",
       "      <th>离散数学_y</th>\n",
       "      <th>数据结构_y</th>\n",
       "      <th>C语言程序设计_y</th>\n",
       "      <th>java语言程序设计_y</th>\n",
       "      <th>算法导论_y</th>\n",
       "      <th>sim</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>小明1</td>\n",
       "      <td>54</td>\n",
       "      <td>73</td>\n",
       "      <td>98</td>\n",
       "      <td>69</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>小明2</td>\n",
       "      <td>51</td>\n",
       "      <td>70</td>\n",
       "      <td>83</td>\n",
       "      <td>93</td>\n",
       "      <td>93</td>\n",
       "      <td>93</td>\n",
       "      <td>96</td>\n",
       "      <td>106.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>小明1</td>\n",
       "      <td>54</td>\n",
       "      <td>73</td>\n",
       "      <td>98</td>\n",
       "      <td>69</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>小明3</td>\n",
       "      <td>84</td>\n",
       "      <td>61</td>\n",
       "      <td>100</td>\n",
       "      <td>84</td>\n",
       "      <td>60</td>\n",
       "      <td>97</td>\n",
       "      <td>71</td>\n",
       "      <td>98.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>小明1</td>\n",
       "      <td>54</td>\n",
       "      <td>73</td>\n",
       "      <td>98</td>\n",
       "      <td>69</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>小明4</td>\n",
       "      <td>71</td>\n",
       "      <td>56</td>\n",
       "      <td>57</td>\n",
       "      <td>89</td>\n",
       "      <td>52</td>\n",
       "      <td>62</td>\n",
       "      <td>93</td>\n",
       "      <td>161.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  姓名_x  Python程序设计基础_x  计算机导论_x  离散数学_x  数据结构_x  C语言程序设计_x  java语言程序设计_x  \\\n",
       "1  小明1              54       73      98      69         76            78   \n",
       "2  小明1              54       73      98      69         76            78   \n",
       "3  小明1              54       73      98      69         76            78   \n",
       "\n",
       "   算法导论_x  one 姓名_y  Python程序设计基础_y  计算机导论_y  离散数学_y  数据结构_y  C语言程序设计_y  \\\n",
       "1      67    1  小明2              51       70      83      93         93   \n",
       "2      67    1  小明3              84       61     100      84         60   \n",
       "3      67    1  小明4              71       56      57      89         52   \n",
       "\n",
       "   java语言程序设计_y  算法导论_y    sim  \n",
       "1            93      96  106.0  \n",
       "2            97      71   98.0  \n",
       "3            62      93  161.0  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. 计算每个学生的TOP N的学生"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_top_student(df_sub):\n",
    "    df_sort = df_sub.sort_values(by=\"sim\", ascending=False).head(10)\n",
    "    names = \",\".join(list(df_sort[\"姓名_y\"]))\n",
    "    sims = \",\".join([str(x) for x in list(df_sort[\"sim\"])])\n",
    "    return pd.Series({\"names\": names, \"sims\": sims})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_result = df_merge.groupby(\"姓名_x\").apply(get_top_student)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_result.to_excel(\"./datas/student_grade/相似计算结果.xlsx\", index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}

一键复制编辑原始数据按行查看历史

提交于 2021-04-20 22:40 . init

马建仓 AI 助手

尝试更多

代码解读

代码找茬

代码优化

https://gitee.com/touch-all-in-one-machine/ant-learn-pandas.git

git@gitee.com:touch-all-in-one-machine/ant-learn-pandas.git

touch-all-in-one-machine

ant-learn-pandas

master

mengyuan/ant-learn-pandas

简介

发行版

贡献者

近期动态

mengyuan/ant-learn-pandas .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

mengyuan/ant-learn-pandas