DaseTalentDocs/工具&文件/机试成绩导出.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b59dad38",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "\n",
    "class FileTypeError(TypeError):\n",
    "    pass\n",
    "\n",
    "\n",
    "class ContestScore:\n",
    "    # for eval(cur_info) in gen_max_cases_\n",
    "    global null\n",
    "    null = ''\n",
    "\n",
    "    def __init__(self, ):\n",
    "        self.rank_path = ''\n",
    "        self.submission_path = ''\n",
    "        self.problem_path = ''\n",
    "        self.ranks_df = None\n",
    "        self.submission_df = None\n",
    "        self.problem_df = None\n",
    "        self.problem_names = []\n",
    "        self.problem_ids = []\n",
    "        self.contest_score = None\n",
    "\n",
    "        # for calculate max cases\n",
    "        self.target_problem = 0\n",
    "        self.gen_all_cases = True\n",
    "\n",
    "    def load_data(self, rank_path, submission_path, problem_path):\n",
    "        # load data\n",
    "        self.ranks_df = ContestScore.read_dataframe(rank_path)\n",
    "        self.submission_df = ContestScore.read_dataframe(submission_path)\n",
    "        self.rank_path = rank_path\n",
    "        self.submission_path = submission_path\n",
    "        rank_columns = self.ranks_df.columns.tolist()\n",
    "        self.problem_names = rank_columns[6:]\n",
    "\n",
    "        # process ranks\n",
    "        drop_data = ['User ID', 'Real Name', 'Total Submission'] + self.problem_names\n",
    "        self.contest_score = self.ranks_df.drop(axis=1, columns=drop_data)\n",
    "\n",
    "        # calculate max cases\n",
    "        self.contest_score['通过用例数'] = self.contest_score.apply(self.gen_max_cases, axis=1)\n",
    "\n",
    "        # get problem_names and problem_ids\n",
    "        # calculate problem cases\n",
    "        if problem_path:\n",
    "            self.get_problems(problem_path)\n",
    "            self.calculate()\n",
    "        else:\n",
    "            print(\"Please Set Problem names and ids by set_problems(names, ids) function\")\n",
    "\n",
    "        # sort scores\n",
    "        self.sort()\n",
    "\n",
    "    def get_problems(self, problem_path):\n",
    "        self.problem_df = ContestScore.read_dataframe(problem_path)\n",
    "        self.problem_names = self.problem_df.title.to_list()\n",
    "        self.problem_ids = self.problem_df.id.to_list()\n",
    "        self.problem_path = problem_path\n",
    "\n",
    "    def gen_max_cases(self, df):\n",
    "        self.gen_all_cases = True\n",
    "        return self.gen_max_cases_(df)\n",
    "\n",
    "    def set_problems(self, problem_names, problem_ids):\n",
    "        self.problem_names = problem_names\n",
    "        self.problem_ids = problem_ids\n",
    "\n",
    "    def calculate(self):\n",
    "        # calculate max cases for certain problems\n",
    "        self.gen_all_cases = False\n",
    "        for index, p_id in enumerate(self.problem_ids):\n",
    "            self.target_problem = p_id\n",
    "            p_name = self.problem_names[index]\n",
    "            self.contest_score[p_name] = self.contest_score.apply(self.gen_max_cases_problem, axis=1)\n",
    "\n",
    "    def gen_max_cases_problem(self, df):\n",
    "        self.gen_all_cases = False\n",
    "        return self.gen_max_cases_(df)\n",
    "\n",
    "    def gen_max_cases_(self, df):\n",
    "        cur_max_cases = {}\n",
    "        for i in range(len(self.submission_df)):\n",
    "            if str(self.submission_df.iloc[i]['username']) == str(df['Username']) \\\n",
    "                    and (self.gen_all_cases or self.target_problem == self.submission_df.iloc[i]['problem_id']):\n",
    "                try:\n",
    "                    cur_info = self.submission_df.iloc[i]['info']\n",
    "                    cur_problem_id = self.submission_df.iloc[i]['problem_id']\n",
    "                    cur_score = sum(list(map(lambda x: x['result'] == 0, eval(cur_info)['data'])))\n",
    "\n",
    "                    if not (cur_problem_id in cur_max_cases.keys()):\n",
    "                        cur_max_cases[cur_problem_id] = cur_score\n",
    "                    elif cur_score > cur_max_cases[cur_problem_id]:\n",
    "                        cur_max_cases[cur_problem_id] = cur_score\n",
    "                except:\n",
    "                    continue\n",
    "        return sum(cur_max_cases.values())\n",
    "\n",
    "    def sort(self, by=['AC', '通过用例数', 'Total Time'], ascending=[False, False, True]):\n",
    "        self.contest_score = self.contest_score.sort_values(by=by, ascending=ascending)\n",
    "        return self.contest_score\n",
    "\n",
    "    def save(self, path):\n",
    "        self.contest_score.to_excel(path, index=False)\n",
    "        return f\"File Saved at {path} as Excel\"\n",
    "\n",
    "    @staticmethod\n",
    "    def read_dataframe(path):\n",
    "        filetype = path.split('.')[-1]\n",
    "        dataframe = None\n",
    "        if filetype == 'csv':\n",
    "            dataframe = pd.read_csv(path)\n",
    "        elif filetype == 'xlsx':\n",
    "            dataframe = pd.read_excel(path)\n",
    "        else:\n",
    "            FileTypeError(filetype)\n",
    "        return dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "09049d66",
   "metadata": {},
   "outputs": [],
   "source": [
    "test = ContestScore()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "73373376",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[0m\u001b[38;5;167m机试成绩导出.ipynb\u001b[0m    \u001b[38;5;78mproblem_202207021234.csv\u001b[0m\r\n",
      "\u001b[38;5;33;1mcontent-68-rank.xlsx\u001b[0m  \u001b[38;5;78msubmission_202207021235.csv\u001b[0m\r\n"
     ]
    }
   ],
   "source": [
    "%ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2b0cedc1",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'File Saved at Result.xlsx as Excel'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load data and calculate scores , ranks automatically\n",
    "test.load_data(rank_path='./content-68-rank.xlsx', submission_path='./submission_202207021235.csv', problem_path='./problem_202207021234.csv')\n",
    "\n",
    "# retun the result by a pandas DataFrame\n",
    "test.contest_score\n",
    "\n",
    "# Save result as excel\n",
    "test.save('Result.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "50e6428e",
   "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.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}