第10章 不定期更新的例子.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 第10章 不定期更新的例子"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 一、评委打分\n",
    "#### 某比赛有1000名选手，300位评委打分，每个选手由三个不同的评委打分，每位评委打10位选手的分\n",
    "#### 现在需要将各个评委的编号转到列索引，行索引不变，表格内容为打分分数，缺失值（即选手i没有被评委j打分）用'-'填充"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>打分一</th>\n",
       "      <th>评委二</th>\n",
       "      <th>打分二</th>\n",
       "      <th>评委三</th>\n",
       "      <th>打分三</th>\n",
       "    </tr>\n",
       "    <tr>\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>1</th>\n",
       "      <td>Judge_248</td>\n",
       "      <td>75</td>\n",
       "      <td>Judge_171</td>\n",
       "      <td>33</td>\n",
       "      <td>Judge_5</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Judge_39</td>\n",
       "      <td>60</td>\n",
       "      <td>Judge_207</td>\n",
       "      <td>38</td>\n",
       "      <td>Judge_63</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Judge_244</td>\n",
       "      <td>99</td>\n",
       "      <td>Judge_171</td>\n",
       "      <td>49</td>\n",
       "      <td>Judge_89</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Judge_163</td>\n",
       "      <td>76</td>\n",
       "      <td>Judge_221</td>\n",
       "      <td>44</td>\n",
       "      <td>Judge_142</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Judge_206</td>\n",
       "      <td>70</td>\n",
       "      <td>Judge_112</td>\n",
       "      <td>100</td>\n",
       "      <td>Judge_260</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            评委一  打分一        评委二  打分二        评委三  打分三\n",
       "选手编号                                                \n",
       "1     Judge_248   75  Judge_171   33    Judge_5   77\n",
       "2      Judge_39   60  Judge_207   38   Judge_63   74\n",
       "3     Judge_244   99  Judge_171   49   Judge_89   93\n",
       "4     Judge_163   76  Judge_221   44  Judge_142   92\n",
       "5     Judge_206   70  Judge_112  100  Judge_260   70"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/Competition.csv',index_col='选手编号')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 【方法一】思维量较大，有技巧性，对Pandas依赖较少"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "时间为：0.762\n"
     ]
    },
    {
     "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>Judge_1</th>\n",
       "      <th>Judge_2</th>\n",
       "      <th>Judge_3</th>\n",
       "      <th>Judge_4</th>\n",
       "      <th>Judge_5</th>\n",
       "      <th>Judge_6</th>\n",
       "      <th>Judge_7</th>\n",
       "      <th>Judge_8</th>\n",
       "      <th>Judge_9</th>\n",
       "      <th>Judge_10</th>\n",
       "      <th>...</th>\n",
       "      <th>Judge_291</th>\n",
       "      <th>Judge_292</th>\n",
       "      <th>Judge_293</th>\n",
       "      <th>Judge_294</th>\n",
       "      <th>Judge_295</th>\n",
       "      <th>Judge_296</th>\n",
       "      <th>Judge_297</th>\n",
       "      <th>Judge_298</th>\n",
       "      <th>Judge_299</th>\n",
       "      <th>Judge_300</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>77</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 300 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Judge_1 Judge_2 Judge_3 Judge_4 Judge_5 Judge_6 Judge_7 Judge_8 Judge_9  \\\n",
       "1       -       -       -       -      77       -       -       -       -   \n",
       "2       -       -       -       -       -       -       -       -       -   \n",
       "3       -       -       -       -       -       -       -       -       -   \n",
       "4       -       -       -       -       -       -       -       -       -   \n",
       "5       -       -       -       -       -       -       -       -       -   \n",
       "\n",
       "  Judge_10  ... Judge_291 Judge_292 Judge_293 Judge_294 Judge_295 Judge_296  \\\n",
       "1        -  ...         -         -         -         -         -         -   \n",
       "2        -  ...         -         -         -         -         -         -   \n",
       "3        -  ...         -         -         -         -         -         -   \n",
       "4        -  ...         -         -         -         -         -         -   \n",
       "5        -  ...         -         -         -         -         -         -   \n",
       "\n",
       "  Judge_297 Judge_298 Judge_299 Judge_300  \n",
       "1         -         -         -         -  \n",
       "2         -         -         -         -  \n",
       "3         -         -         -         -  \n",
       "4         -         -         -         -  \n",
       "5         -         -         -         -  \n",
       "\n",
       "[5 rows x 300 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t0=time.perf_counter()\n",
    "############################################################################################\n",
    "L,k = [],1\n",
    "for i in range(301):\n",
    "    judge = 'Judge_%d'%i\n",
    "    result = df[(df.iloc[:,0::2]==judge).any(1)]\n",
    "    L_temp = (result.iloc[:,0::2]==judge).values*result.iloc[:,1::2].values\n",
    "    L.append(list(zip(result.index.tolist(),list(L_temp.max(axis=1)))))\n",
    "L.pop(0)\n",
    "df_result = pd.DataFrame([['-']*1000]*300,index=['Judge_%d'%i for i in range(1,301)]\n",
    "                         ,columns=['%d'%i for i in range(1,1001)])\n",
    "for i in L:\n",
    "    for j in i:\n",
    "        df_result.at['Judge_%d'%k,'%d'%j[0]] = j[1]\n",
    "    k += 1\n",
    "############################################################################################\n",
    "t1=time.perf_counter()\n",
    "print('时间为：%.3f'%(t1-t0))\n",
    "df_result.T.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 【方法二】思路简单，但运行时间较长"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "时间为：14.938\n"
     ]
    },
    {
     "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>judge</th>\n",
       "      <th>Judge_1</th>\n",
       "      <th>Judge_2</th>\n",
       "      <th>Judge_3</th>\n",
       "      <th>Judge_4</th>\n",
       "      <th>Judge_5</th>\n",
       "      <th>Judge_6</th>\n",
       "      <th>Judge_7</th>\n",
       "      <th>Judge_8</th>\n",
       "      <th>Judge_9</th>\n",
       "      <th>Judge_10</th>\n",
       "      <th>...</th>\n",
       "      <th>Judge_291</th>\n",
       "      <th>Judge_292</th>\n",
       "      <th>Judge_293</th>\n",
       "      <th>Judge_294</th>\n",
       "      <th>Judge_295</th>\n",
       "      <th>Judge_296</th>\n",
       "      <th>Judge_297</th>\n",
       "      <th>Judge_298</th>\n",
       "      <th>Judge_299</th>\n",
       "      <th>Judge_300</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>index</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\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>1</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>77</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 300 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "judge Judge_1 Judge_2 Judge_3 Judge_4 Judge_5 Judge_6 Judge_7 Judge_8 Judge_9  \\\n",
       "index                                                                           \n",
       "1           -       -       -       -      77       -       -       -       -   \n",
       "2           -       -       -       -       -       -       -       -       -   \n",
       "3           -       -       -       -       -       -       -       -       -   \n",
       "4           -       -       -       -       -       -       -       -       -   \n",
       "5           -       -       -       -       -       -       -       -       -   \n",
       "\n",
       "judge Judge_10  ... Judge_291 Judge_292 Judge_293 Judge_294 Judge_295  \\\n",
       "index           ...                                                     \n",
       "1            -  ...         -         -         -         -         -   \n",
       "2            -  ...         -         -         -         -         -   \n",
       "3            -  ...         -         -         -         -         -   \n",
       "4            -  ...         -         -         -         -         -   \n",
       "5            -  ...         -         -         -         -         -   \n",
       "\n",
       "judge Judge_296 Judge_297 Judge_298 Judge_299 Judge_300  \n",
       "index                                                    \n",
       "1             -         -         -         -         -  \n",
       "2             -         -         -         -         -  \n",
       "3             -         -         -         -         -  \n",
       "4             -         -         -         -         -  \n",
       "5             -         -         -         -         -  \n",
       "\n",
       "[5 rows x 300 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t0=time.perf_counter()\n",
    "############################################################################################\n",
    "judge = np.array([[df.iloc[:,0:2].values],[df.iloc[:,2:4].values],[df.iloc[:,4:6].values]]).reshape(6000)[0::2]\n",
    "score = np.array([[df.iloc[:,0:2].values],[df.iloc[:,2:4].values],[df.iloc[:,4:6].values]]).reshape(6000)[1::2]\n",
    "df_result = pd.DataFrame({'judge':judge,'score':score}\n",
    "                         ,index=np.array([range(1,1001)]*3).reshape(3000)).reset_index()\n",
    "df_result = pd.crosstab(index=df_result['index'],columns=df_result['judge'],values=df_result['score']\n",
    "                     ,aggfunc=np.sum).fillna('-').T.reindex(['Judge_%d'%i for i in range(1,301)]).T\n",
    "############################################################################################\n",
    "t1=time.perf_counter()\n",
    "print('时间为：%.3f'%(t1-t0))\n",
    "df_result.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 【方法三】基本与方法二类似，但借助pivot函数大幅提高速度"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "时间为：0.197\n"
     ]
    },
    {
     "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>judge</th>\n",
       "      <th>Judge_1</th>\n",
       "      <th>Judge_2</th>\n",
       "      <th>Judge_3</th>\n",
       "      <th>Judge_4</th>\n",
       "      <th>Judge_5</th>\n",
       "      <th>Judge_6</th>\n",
       "      <th>Judge_7</th>\n",
       "      <th>Judge_8</th>\n",
       "      <th>Judge_9</th>\n",
       "      <th>Judge_10</th>\n",
       "      <th>...</th>\n",
       "      <th>Judge_291</th>\n",
       "      <th>Judge_292</th>\n",
       "      <th>Judge_293</th>\n",
       "      <th>Judge_294</th>\n",
       "      <th>Judge_295</th>\n",
       "      <th>Judge_296</th>\n",
       "      <th>Judge_297</th>\n",
       "      <th>Judge_298</th>\n",
       "      <th>Judge_299</th>\n",
       "      <th>Judge_300</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>index</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\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>1</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>77</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 300 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "judge Judge_1 Judge_2 Judge_3 Judge_4 Judge_5 Judge_6 Judge_7 Judge_8 Judge_9  \\\n",
       "index                                                                           \n",
       "1           -       -       -       -      77       -       -       -       -   \n",
       "2           -       -       -       -       -       -       -       -       -   \n",
       "3           -       -       -       -       -       -       -       -       -   \n",
       "4           -       -       -       -       -       -       -       -       -   \n",
       "5           -       -       -       -       -       -       -       -       -   \n",
       "\n",
       "judge Judge_10  ... Judge_291 Judge_292 Judge_293 Judge_294 Judge_295  \\\n",
       "index           ...                                                     \n",
       "1            -  ...         -         -         -         -         -   \n",
       "2            -  ...         -         -         -         -         -   \n",
       "3            -  ...         -         -         -         -         -   \n",
       "4            -  ...         -         -         -         -         -   \n",
       "5            -  ...         -         -         -         -         -   \n",
       "\n",
       "judge Judge_296 Judge_297 Judge_298 Judge_299 Judge_300  \n",
       "index                                                    \n",
       "1             -         -         -         -         -  \n",
       "2             -         -         -         -         -  \n",
       "3             -         -         -         -         -  \n",
       "4             -         -         -         -         -  \n",
       "5             -         -         -         -         -  \n",
       "\n",
       "[5 rows x 300 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t0=time.perf_counter()\n",
    "############################################################################################\n",
    "judge = np.array([[df.iloc[:,0:2].values],[df.iloc[:,2:4].values],[df.iloc[:,4:6].values]]).reshape(6000)[0::2]\n",
    "score = np.array([[df.iloc[:,0:2].values],[df.iloc[:,2:4].values],[df.iloc[:,4:6].values]]).reshape(6000)[1::2]\n",
    "df_result = pd.DataFrame({'judge':judge,'score':score}\n",
    "                         ,index=np.array([range(1,1001)]*3).reshape(3000)).reset_index()\n",
    "df_result = df_result.pivot(index='index',columns='judge'\n",
    "                    ,values='score').T.reindex(['Judge_%d'%i for i in range(1,301)]).T.fillna('-')\n",
    "############################################################################################\n",
    "t1=time.perf_counter()\n",
    "print('时间为：%.3f'%(t1-t0))\n",
    "df_result.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 二、企业收入熵指数\n",
    "#### 一个企业的产业多元化水平可以由收入熵指数计算衡量，其公式为$-\\Sigma P_i \\ln{P_i}$，其中i表示第i个收入类型，$P_i$表示该类型收入额所占整个收入额的比重（因此$\\Sigma P_i=1$），现在需要对Company.csv中的公司计算它们的年度收入熵，需要利用Company_data.csv中不同收入类型销售额的数据（证券代码都是六位，第一列数字需要补零），请计算结果并保存到data文件夹下\n",
    "#### 注意：不是所有要求计算的公司都会在data文件中出现，反之亦然；某公司某年的数据若含有缺失值，请基于收入熵公式选择一种合理的计算方式"
   ]
  },
  {
   "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>日期</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>#000007</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>#000403</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>#000408</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>#000408</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>#000426</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      证券代码    日期\n",
       "0  #000007  2014\n",
       "1  #000403  2015\n",
       "2  #000408  2016\n",
       "3  #000408  2017\n",
       "4  #000426  2015"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_c = pd.read_csv('data/Company.csv')\n",
    "df_c.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>日期</th>\n",
       "      <th>收入类型</th>\n",
       "      <th>收入额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2008/12/31</td>\n",
       "      <td>1</td>\n",
       "      <td>1.084218e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2008/12/31</td>\n",
       "      <td>2</td>\n",
       "      <td>1.259789e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2008/12/31</td>\n",
       "      <td>3</td>\n",
       "      <td>1.451312e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2008/12/31</td>\n",
       "      <td>4</td>\n",
       "      <td>1.063843e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2008/12/31</td>\n",
       "      <td>5</td>\n",
       "      <td>8.513880e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   证券代码          日期  收入类型           收入额\n",
       "0     1  2008/12/31     1  1.084218e+10\n",
       "1     1  2008/12/31     2  1.259789e+10\n",
       "2     1  2008/12/31     3  1.451312e+10\n",
       "3     1  2008/12/31     4  1.063843e+09\n",
       "4     1  2008/12/31     5  8.513880e+08"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/Company_data.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 【参考答案】"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_c = pd.read_csv('data/Company.csv')\n",
    "df = pd.read_csv('data/Company_data.csv')\n",
    "df['证券代码'] = df['证券代码'].apply(lambda x:'#'+'0'*(6-len(str(x)))+str(x))\n",
    "df['日期'] = pd.to_datetime(df['日期']).dt.year\n",
    "df_new = df[df['证券代码'].apply(lambda x:True if x in df_c['证券代码'].values else False)]\n",
    "result = pd.merge(df_c, df_new.groupby(['证券代码','日期'])['收入额'].agg(lambda x:sum([\n",
    "    -i*np.log(i) for i in x[x>0]/sum(x[x>0])])).reset_index(), on=['证券代码','日期'], how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>日期</th>\n",
       "      <th>收入熵</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>#000007</td>\n",
       "      <td>2014</td>\n",
       "      <td>3.070462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>#000403</td>\n",
       "      <td>2015</td>\n",
       "      <td>2.790585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>#000408</td>\n",
       "      <td>2016</td>\n",
       "      <td>2.818541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>#000408</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>#000426</td>\n",
       "      <td>2015</td>\n",
       "      <td>3.084266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1043</th>\n",
       "      <td>#600978</td>\n",
       "      <td>2011</td>\n",
       "      <td>3.319059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1044</th>\n",
       "      <td>#600978</td>\n",
       "      <td>2014</td>\n",
       "      <td>2.788100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1045</th>\n",
       "      <td>#600978</td>\n",
       "      <td>2015</td>\n",
       "      <td>3.012628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1046</th>\n",
       "      <td>#600978</td>\n",
       "      <td>2016</td>\n",
       "      <td>3.021157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1047</th>\n",
       "      <td>#600978</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1048 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         证券代码    日期       收入熵\n",
       "0     #000007  2014  3.070462\n",
       "1     #000403  2015  2.790585\n",
       "2     #000408  2016  2.818541\n",
       "3     #000408  2017       NaN\n",
       "4     #000426  2015  3.084266\n",
       "...       ...   ...       ...\n",
       "1043  #600978  2011  3.319059\n",
       "1044  #600978  2014  2.788100\n",
       "1045  #600978  2015  3.012628\n",
       "1046  #600978  2016  3.021157\n",
       "1047  #600978  2017       NaN\n",
       "\n",
       "[1048 rows x 3 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.rename(columns={'收入额':'收入熵'})"
   ]
  }
 ],
 "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}