python作业/HW4体彩抽奖.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\anaconda\\lib\\site-packages\\openpyxl\\styles\\stylesheet.py:226: UserWarning: Workbook contains no default style, apply openpyxl's default\n",
      "  warn(\"Workbook contains no default style, apply openpyxl's default\")\n"
     ]
    }
   ],
   "source": [
    "import random as rd\n",
    "import pandas as pd\n",
    "\n",
    "\n",
    "def readnum():\n",
    "    fd = pd.read_excel('体彩大乐透-问卷统计详情.xlsx')\n",
    "    name = fd.iloc[5:, 0].values\n",
    "    f5 = fd.iloc[5:, 8].values\n",
    "    b2 = fd.iloc[5:, 9].values\n",
    "    return name, f5, b2\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    name, f5, b2 = readnum()\n",
    "    # print(name, f5, b2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>前五个数字</th>\n",
       "      <th>后两个数字</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>王子尧</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>肖晖</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>陈静</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>方泓杰</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>姜洁</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>黄中阳</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>孙鑫媛</td>\n",
       "      <td>25,22,32,01,08</td>\n",
       "      <td>10,08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>冯晓莹</td>\n",
       "      <td>35,11,58,52,09</td>\n",
       "      <td>01,12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>王宏宙</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>张艺方</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>159 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      姓名           前五个数字  后两个数字\n",
       "0    王子尧             NaN    NaN\n",
       "1     肖晖             NaN    NaN\n",
       "2     陈静             NaN    NaN\n",
       "3    方泓杰             NaN    NaN\n",
       "4     姜洁             NaN    NaN\n",
       "..   ...             ...    ...\n",
       "154  黄中阳             NaN    NaN\n",
       "155  孙鑫媛  25,22,32,01,08  10,08\n",
       "156  冯晓莹  35,11,58,52,09  01,12\n",
       "157  王宏宙             NaN    NaN\n",
       "158  张艺方             NaN    NaN\n",
       "\n",
       "[159 rows x 3 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({'姓名': name, '前五个数字': f5, '后两个数字': b2})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "df = df.dropna()  # 去除Nan值\n",
    "## 去除nan值之后，重置顺序\n",
    "df = pd.DataFrame({'姓名': list(df['姓名']), '前五个数字': list(df['前五个数字']), '后两个数字': list(df['后两个数字'])})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 返回bool列表，方便后续去除数字不够，网址的同学\n",
    "def TFfun(df, string, num):\n",
    "    alist = []\n",
    "    for i in df[string]:\n",
    "        if len(i) > 40:\n",
    "            alist.append(False)\n",
    "            continue\n",
    "        count = 0\n",
    "        for j in i:\n",
    "            try:\n",
    "                j = int(j)\n",
    "            except:\n",
    "                j = str(j)\n",
    "                if j == ',' or j == '，':\n",
    "                    count += 1\n",
    "        alist.append(count == num)\n",
    "    return alist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 去除那些数字的不够的同学\n",
    "droplist5 = TFfun(df, '前五个数字', 4)\n",
    "droplist2 = TFfun(df, '后两个数字', 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 手动实现交集\n",
    "TFlist = []\n",
    "for i in range(len(droplist2)):\n",
    "    if droplist2[i] == droplist5[i] and droplist2[i] == True:\n",
    "        TFlist.append(True)\n",
    "    else:\n",
    "        TFlist.append(False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 筛选出合格的抽奖同学\n",
    "newdf = df.iloc[TFlist]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 提取抽奖的号码\n",
    "\"\"\"\n",
    "每人的抽奖号码中前面的数字通过后面的逗号或空格区别，因为他们没法变成整型，最坏一数字通过循环结束区别\n",
    "\"\"\"\n",
    "def coulist1(newdf, string):\n",
    "    list5 = []\n",
    "    for i in newdf[string]:\n",
    "        temp = []\n",
    "        temp1 = []\n",
    "        for j in i:\n",
    "            try:\n",
    "                j = int(j)\n",
    "                temp.append(j)\n",
    "            except:\n",
    "                if temp == []:\n",
    "                    continue\n",
    "                a = ''\n",
    "                for i in temp:\n",
    "                    a = a + str(i)\n",
    "                temp1.append(a)\n",
    "                temp = []\n",
    "        a = ''\n",
    "        for i in temp:\n",
    "            a = a + str(i)\n",
    "        temp1.append(a)\n",
    "        list5.append(temp1)\n",
    "    return list5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "list5 = coulist1(newdf, '前五个数字')\n",
    "list2 = coulist1(newdf, '后两个数字')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 把list5 的元素转成整型\n",
    "for i in range(len(list5[0][:])):\n",
    "    for j in range(len(list5)):\n",
    "        list5[j][i] = int(list5[j][i])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 把list2 的元素转成整型\n",
    "for i in range(len(list2[0][:])):\n",
    "    for j in range(len(list2)):\n",
    "        list2[j][i] = int(list2[j][i])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>name</th>\n",
       "      <th>f5</th>\n",
       "      <th>b2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>巴桑次仁</td>\n",
       "      <td>[6, 23, 32, 20, 9]</td>\n",
       "      <td>[8, 12]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>王婉慈</td>\n",
       "      <td>[2, 35, 22, 17, 6]</td>\n",
       "      <td>[5, 11]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>涂昕</td>\n",
       "      <td>[7, 27, 1, 5, 21]</td>\n",
       "      <td>[7, 27]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>高冬晓</td>\n",
       "      <td>[17, 25, 33, 49, 7]</td>\n",
       "      <td>[7, 11]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>柏然</td>\n",
       "      <td>[12, 5, 33, 25, 22]</td>\n",
       "      <td>[11, 3]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>卢妍</td>\n",
       "      <td>[21, 12, 8, 19, 7, ]</td>\n",
       "      <td>[5, 6, ]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>叶如锦</td>\n",
       "      <td>[1, 4, 10, 33, 22]</td>\n",
       "      <td>[4, 10]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>刘子毅</td>\n",
       "      <td>[1, 2, 3, 4, 5]</td>\n",
       "      <td>[1, 2]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>孙鑫媛</td>\n",
       "      <td>[25, 22, 32, 1, 8]</td>\n",
       "      <td>[10, 8]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>冯晓莹</td>\n",
       "      <td>[35, 11, 58, 52, 9]</td>\n",
       "      <td>[1, 12]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>108 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     name                    f5        b2\n",
       "0    巴桑次仁    [6, 23, 32, 20, 9]   [8, 12]\n",
       "1     王婉慈    [2, 35, 22, 17, 6]   [5, 11]\n",
       "2      涂昕     [7, 27, 1, 5, 21]   [7, 27]\n",
       "3     高冬晓   [17, 25, 33, 49, 7]   [7, 11]\n",
       "4      柏然   [12, 5, 33, 25, 22]   [11, 3]\n",
       "..    ...                   ...       ...\n",
       "103    卢妍  [21, 12, 8, 19, 7, ]  [5, 6, ]\n",
       "104   叶如锦    [1, 4, 10, 33, 22]   [4, 10]\n",
       "105   刘子毅       [1, 2, 3, 4, 5]    [1, 2]\n",
       "106   孙鑫媛    [25, 22, 32, 1, 8]   [10, 8]\n",
       "107   冯晓莹   [35, 11, 58, 52, 9]   [1, 12]\n",
       "\n",
       "[108 rows x 3 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#重新生成一个DataFrame\n",
    "coujiang = pd.DataFrame({'name': list(newdf['姓名']), 'f5': list5, 'b2': list2})\n",
    "coujiang"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "#随机生成一个抽奖列表\n",
    "\"\"\"\n",
    "随机生成，如果已经存在，再重新生成一个，直到没有或满\n",
    "\"\"\"\n",
    "def coulist(num, st, end):\n",
    "    coulist = []\n",
    "    count = 0\n",
    "    while True:\n",
    "        temp = rd.randint(st, end)\n",
    "        if temp in coulist:\n",
    "            continue\n",
    "        coulist.append(temp)\n",
    "        count += 1\n",
    "        if count == num:\n",
    "            break\n",
    "    return coulist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "def jiangxiang(f5num, b2num):\n",
    "    if f5num == 5 and b2num == 2:\n",
    "        return '一等奖'\n",
    "    if f5num == 5 and b2num == 1:\n",
    "        return '二等奖'\n",
    "    if f5num == 5 and b2num == 0:\n",
    "        return '三等奖'\n",
    "    if f5num == 4 and b2num == 2:\n",
    "        return '四等奖'\n",
    "    if f5num == 4 and b2num == 1:\n",
    "        return '五等奖'\n",
    "    if f5num == 3 and b2num == 2:\n",
    "        return '六等奖'\n",
    "    if f5num == 4 and b2num == 0:\n",
    "        return '七等奖'\n",
    "    if f5num == 3 and b2num == 1:\n",
    "        return '八等奖'\n",
    "    if f5num == 2 and b2num == 2:\n",
    "        return '八等奖'\n",
    "    if f5num == 3 and b2num == 0:\n",
    "        return '九等奖'\n",
    "    if f5num == 2 and b2num == 1:\n",
    "        return '九等奖'\n",
    "    if f5num == 1 and b2num == 2:\n",
    "        return '九等奖'\n",
    "    if f5num == 0 and b2num == 2:\n",
    "        return '九等奖'\n",
    "    else:\n",
    "        return 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 查看是否中奖，中啥奖\n",
    "\"\"\"\n",
    "通过计算号码相同的个数实现\n",
    "\"\"\"\n",
    "def cou(f2l, b2l):\n",
    "    f5num = 0\n",
    "    b2num = 0\n",
    "    for i in f2l:\n",
    "        if i in f5coulist:\n",
    "            f5num += 1\n",
    "    for i in b2l:\n",
    "        if i in b2coulist:\n",
    "            b2num += 1\n",
    "    return jiangxiang(f5num, b2num)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 创建一个字典计数器，再把它添加到一个列表中，再拿另外一个列表放名字，通过元素的位置来区分，每人的中奖情况\n",
    "ZJdict = {}\n",
    "ZJlist = []\n",
    "ZJname = []\n",
    "def zhongjiangmingdan(ZJname,ZJlist,ZJdict,f2l,b2l,i):\n",
    "    if i not in ZJname:\n",
    "       ZJname.append(i)\n",
    "       ZJdict={}\n",
    "       ZJdict[cou(f2l,b2l)] = 1\n",
    "       ZJlist.append(ZJdict)\n",
    "    else:\n",
    "      inDEX=ZJname.index(i)\n",
    "      if cou(f2l,b2l) not in ZJlist[inDEX]:\n",
    "          ZJlist[inDEX][cou(f2l,b2l)]=1\n",
    "      else:\n",
    "          ZJlist[inDEX][cou(f2l,b2l)] += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 抽一千次\n",
    "for _ in range(1000):\n",
    "    f5coulist = coulist(5, 1, 35)\n",
    "    b2coulist = coulist(2, 1, 12)\n",
    "    for i in range(len(coujiang)):\n",
    "        if cou(coujiang.loc[i]['f5'],coujiang.loc[i]['b2']) !=0:\n",
    "            zhongjiangmingdan(ZJname,ZJlist,ZJdict,coujiang.loc[i]['f5'],coujiang.loc[i]['b2'],coujiang.loc[i]['name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "#把中奖结果放到DataFrame中\n",
    "import numpy as np\n",
    "ds1=pd.DataFrame.from_dict(ZJlist[:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "namedf=pd.DataFrame({'姓名':ZJname})\n",
    "ds2=ds1.join(namedf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>九等奖</th>\n",
       "      <th>八等奖</th>\n",
       "      <th>七等奖</th>\n",
       "      <th>六等奖</th>\n",
       "      <th>五等奖</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>涂昕</td>\n",
       "      <td>43</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>丁高祥</td>\n",
       "      <td>67</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>徐可晗</td>\n",
       "      <td>73</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>骆婉华</td>\n",
       "      <td>31</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>姜文涵</td>\n",
       "      <td>40</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>方舟</td>\n",
       "      <td>55</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>杨可盈</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>刘凡</td>\n",
       "      <td>27</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>吴政泽</td>\n",
       "      <td>31</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>蒋昊</td>\n",
       "      <td>16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>108 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      姓名  九等奖  八等奖  七等奖  六等奖  五等奖\n",
       "0     涂昕   43  3.0  0.0  0.0  1.0\n",
       "1    丁高祥   67  8.0  0.0  0.0  0.0\n",
       "2    徐可晗   73  7.0  0.0  0.0  0.0\n",
       "3    骆婉华   31  2.0  0.0  0.0  0.0\n",
       "4    姜文涵   40  5.0  0.0  0.0  1.0\n",
       "..   ...  ...  ...  ...  ...  ...\n",
       "103   方舟   55  4.0  0.0  0.0  0.0\n",
       "104  杨可盈    3  0.0  0.0  0.0  0.0\n",
       "105   刘凡   27  0.0  0.0  0.0  0.0\n",
       "106  吴政泽   31  2.0  0.0  0.0  0.0\n",
       "107   蒋昊   16  0.0  1.0  0.0  0.0\n",
       "\n",
       "[108 rows x 6 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#重新排列列的顺序\n",
    "ds2=ds2[['姓名','九等奖','八等奖','七等奖','六等奖','五等奖']]\n",
    "ds2=ds2.replace(np.nan,0)\n",
    "ds2"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}