sheet1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "50eead5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import plotly.express as px\n",
    "from pyperclip import paste\n",
    "import plotly.graph_objects as go\n",
    "from plotly.subplots import make_subplots"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "261298c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2019-2021机构类型外教来源统计\n",
    "s = paste()\n",
    "l = np.array([i.split('\\t') for i in s.split('\\r\\n') if len(i.split('\\t')) > 1])\n",
    "org_type_ft_sources = pd.DataFrame(l[1:,1:], columns=l[0, 1:], index=l[1:,0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "766f7017",
   "metadata": {},
   "outputs": [],
   "source": [
    "org_type_ft_sources = org_type_ft_sources.T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "cb4ea39e",
   "metadata": {},
   "outputs": [],
   "source": [
    "org_type_ft_sources = org_type_ft_sources.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0c5c16a4",
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019年</th>\n",
       "      <td>585</td>\n",
       "      <td>256</td>\n",
       "      <td>96</td>\n",
       "      <td>91</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020年</th>\n",
       "      <td>670</td>\n",
       "      <td>242</td>\n",
       "      <td>122</td>\n",
       "      <td>91</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021年</th>\n",
       "      <td>642</td>\n",
       "      <td>213</td>\n",
       "      <td>96</td>\n",
       "      <td>86</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       外方派遣  中方招聘  中外联合招聘  外方招聘  第三方派遣\n",
       "2019年   585   256      96    91      2\n",
       "2020年   670   242     122    91      2\n",
       "2021年   642   213      96    86      2"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "org_type_ft_sources"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "baa00d03",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2019-2021项目类型外教来源统计\n",
    "s = paste()\n",
    "l = np.array([i.split('\\t') for i in s.split('\\r\\n') if len(i.split('\\t')) > 1])\n",
    "proj_type_ft_sources = pd.DataFrame(l[1:,1:], columns=l[0, 1:], index=l[1:,0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "eb11d4e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "proj_type_ft_sources = proj_type_ft_sources.T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "42885503",
   "metadata": {},
   "outputs": [],
   "source": [
    "proj_type_ft_sources = proj_type_ft_sources.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "6aa943e0",
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019年</th>\n",
       "      <td>2515</td>\n",
       "      <td>427</td>\n",
       "      <td>328</td>\n",
       "      <td>243</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020年</th>\n",
       "      <td>2417</td>\n",
       "      <td>339</td>\n",
       "      <td>286</td>\n",
       "      <td>221</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021年</th>\n",
       "      <td>2179</td>\n",
       "      <td>308</td>\n",
       "      <td>293</td>\n",
       "      <td>206</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       外方派遣  中方招聘  外方招聘  中外联合招聘  第三方派遣\n",
       "2019年  2515   427   328     243     17\n",
       "2020年  2417   339   286     221     21\n",
       "2021年  2179   308   293     206     15"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "proj_type_ft_sources"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "41f20e97",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = make_subplots(5, 2) # , specs=[[{'secondary_y': True}]]  , subplot_titles=['机构', '项目'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "0006bd82",
   "metadata": {},
   "outputs": [],
   "source": [
    "color_dic = {'外方派遣': 'red', '中方招聘': 'blue', '外方招聘': 'green', '中外联合招聘': 'pink', '第三方派遣': 'orange'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "6226cefc",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig.add_traces([go.Scatter(x=org_type_ft_sources.index, \n",
    "                   y=org_type_ft_sources['外方派遣'].values,\n",
    "                   name='机构类型_'+'外方派遣',\n",
    "#                    marker_color='red',\n",
    "                   text=org_type_ft_sources['外方派遣'].values),\n",
    "                go.Scatter(x=proj_type_ft_sources.index, \n",
    "                   y=proj_type_ft_sources['外方派遣'].values,\n",
    "                   name='项目类型_'+'外方派遣',\n",
    "#                    marker_color='blue',\n",
    "                   text=proj_type_ft_sources['外方派遣'].values)\n",
    "                ], rows=[1,1],cols=[1,2])\n",
    "\n",
    "fig.add_traces([go.Scatter(x=org_type_ft_sources.index, \n",
    "                   y=org_type_ft_sources['中方招聘'].values,\n",
    "                   name='机构类型_'+'中方招聘',\n",
    "#                    marker_color='red',\n",
    "                   text=org_type_ft_sources['中方招聘'].values),\n",
    "                go.Scatter(x=proj_type_ft_sources.index, \n",
    "                   y=proj_type_ft_sources['中方招聘'].values,\n",
    "                   name='项目类型_'+'中方招聘',\n",
    "#                    marker_color='blue',\n",
    "                   text=proj_type_ft_sources['中方招聘'].values)\n",
    "                ], rows=[2,2],cols=[1,2])\n",
    "\n",
    "\n",
    "fig.add_traces([go.Scatter(x=org_type_ft_sources.index, \n",
    "                   y=org_type_ft_sources['外方招聘'].values,\n",
    "                   name='机构类型_'+'外方招聘',\n",
    "#                    marker_color='red',\n",
    "                   text=org_type_ft_sources['外方招聘'].values),\n",
    "                go.Scatter(x=proj_type_ft_sources.index, \n",
    "                   y=proj_type_ft_sources['外方招聘'].values,\n",
    "                   name='项目类型_'+'外方招聘',\n",
    "#                    marker_color='blue',\n",
    "                   text=proj_type_ft_sources['外方招聘'].values)\n",
    "                ], rows=[3,3],cols=[1,2])\n",
    "\n",
    "fig.add_traces([go.Scatter(x=org_type_ft_sources.index, \n",
    "                   y=org_type_ft_sources['中外联合招聘'].values,\n",
    "                   name='机构类型_'+'中外联合招聘',\n",
    "#                    marker_color='red',\n",
    "                   text=org_type_ft_sources['中外联合招聘'].values),\n",
    "                go.Scatter(x=proj_type_ft_sources.index, \n",
    "                   y=proj_type_ft_sources['中外联合招聘'].values,\n",
    "                   name='项目类型_'+'中外联合招聘',\n",
    "#                    marker_color='blue',\n",
    "                   text=proj_type_ft_sources['中外联合招聘'].values)\n",
    "                ], rows=[4,4],cols=[1,2])\n",
    "\n",
    "fig.add_traces([go.Scatter(x=org_type_ft_sources.index, \n",
    "                   y=org_type_ft_sources['第三方派遣'].values,\n",
    "                   name='机构类型_'+'第三方派遣',\n",
    "#                    name=None,\n",
    "#                    marker_color='red',\n",
    "                   text=org_type_ft_sources['第三方派遣'].values),\n",
    "                go.Scatter(x=proj_type_ft_sources.index, \n",
    "                   y=proj_type_ft_sources['第三方派遣'].values,\n",
    "                   name='项目类型_'+'第三方派遣',\n",
    "#                    marker_color='blue',\n",
    "                   text=proj_type_ft_sources['第三方派遣'].values)\n",
    "                ], rows=[5,5],cols=[1,2])\n",
    "\n",
    "fig.update_layout(template='plotly_white',\n",
    "#                   title={'text': '在项目和机构类别中不同类型外教来源数量的变化', 'xref': 'paper', 'x': 0.5, 'font_size': 25},\n",
    "#                   showlegend=False\n",
    "                  )\n",
    "\n",
    "fig.write_image(r\"D:\\新建文件夹 (2)\\WeChat Files\\wxid_r5nb2w32sc5q12\\FileStorage\\File\\2021-08\\sheet1_v3.svg\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dbe5eaf9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2db2671b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b1bb75b4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3010d4f0",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3c7ae05",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4109e527",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9372e9ab",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d2e73fed",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9b5989b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "53ed03a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "org_type_ft_sources['类型'] = '机构类型'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "74394741",
   "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",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019年</th>\n",
       "      <td>585</td>\n",
       "      <td>256</td>\n",
       "      <td>96</td>\n",
       "      <td>91</td>\n",
       "      <td>2</td>\n",
       "      <td>机构类型</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020年</th>\n",
       "      <td>670</td>\n",
       "      <td>242</td>\n",
       "      <td>122</td>\n",
       "      <td>91</td>\n",
       "      <td>2</td>\n",
       "      <td>机构类型</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021年</th>\n",
       "      <td>642</td>\n",
       "      <td>213</td>\n",
       "      <td>96</td>\n",
       "      <td>86</td>\n",
       "      <td>2</td>\n",
       "      <td>机构类型</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       外方派遣  中方招聘  中外联合招聘  外方招聘  第三方派遣    类型\n",
       "2019年   585   256      96    91      2  机构类型\n",
       "2020年   670   242     122    91      2  机构类型\n",
       "2021年   642   213      96    86      2  机构类型"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "org_type_ft_sources"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "65cea0e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "proj_type_ft_sources['类型'] = '项目类型'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "a205bebd",
   "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",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019年</th>\n",
       "      <td>2515</td>\n",
       "      <td>427</td>\n",
       "      <td>328</td>\n",
       "      <td>243</td>\n",
       "      <td>17</td>\n",
       "      <td>项目类型</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020年</th>\n",
       "      <td>2417</td>\n",
       "      <td>339</td>\n",
       "      <td>286</td>\n",
       "      <td>221</td>\n",
       "      <td>21</td>\n",
       "      <td>项目类型</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021年</th>\n",
       "      <td>2179</td>\n",
       "      <td>308</td>\n",
       "      <td>293</td>\n",
       "      <td>206</td>\n",
       "      <td>15</td>\n",
       "      <td>项目类型</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       外方派遣  中方招聘  外方招聘  中外联合招聘  第三方派遣    类型\n",
       "2019年  2515   427   328     243     17  项目类型\n",
       "2020年  2417   339   286     221     21  项目类型\n",
       "2021年  2179   308   293     206     15  项目类型"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "proj_type_ft_sources"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "2978a6c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([org_type_ft_sources, proj_type_ft_sources])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "54d02ae4",
   "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",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019年</th>\n",
       "      <td>585</td>\n",
       "      <td>256</td>\n",
       "      <td>96</td>\n",
       "      <td>91</td>\n",
       "      <td>2</td>\n",
       "      <td>机构类型</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020年</th>\n",
       "      <td>670</td>\n",
       "      <td>242</td>\n",
       "      <td>122</td>\n",
       "      <td>91</td>\n",
       "      <td>2</td>\n",
       "      <td>机构类型</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021年</th>\n",
       "      <td>642</td>\n",
       "      <td>213</td>\n",
       "      <td>96</td>\n",
       "      <td>86</td>\n",
       "      <td>2</td>\n",
       "      <td>机构类型</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019年</th>\n",
       "      <td>2515</td>\n",
       "      <td>427</td>\n",
       "      <td>243</td>\n",
       "      <td>328</td>\n",
       "      <td>17</td>\n",
       "      <td>项目类型</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020年</th>\n",
       "      <td>2417</td>\n",
       "      <td>339</td>\n",
       "      <td>221</td>\n",
       "      <td>286</td>\n",
       "      <td>21</td>\n",
       "      <td>项目类型</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021年</th>\n",
       "      <td>2179</td>\n",
       "      <td>308</td>\n",
       "      <td>206</td>\n",
       "      <td>293</td>\n",
       "      <td>15</td>\n",
       "      <td>项目类型</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       外方派遣  中方招聘  中外联合招聘  外方招聘  第三方派遣    类型\n",
       "2019年   585   256      96    91      2  机构类型\n",
       "2020年   670   242     122    91      2  机构类型\n",
       "2021年   642   213      96    86      2  机构类型\n",
       "2019年  2515   427     243   328     17  项目类型\n",
       "2020年  2417   339     221   286     21  项目类型\n",
       "2021年  2179   308     206   293     15  项目类型"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7817ff38",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5b2a3ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "px.bar(df, x=df.index, y='')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2136e397",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b9cf2a50",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1c98fb5",
   "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.8"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}