word_freq.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "1.对txt格式的年报，进行词频分析\n",
    "2.分析数据转换为pandas.dataframe格式，合并到最终表格中\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "21129\n",
      "['../txt/2008/600298_2008.txt', '../txt/2008/600626_2008.txt', '../txt/2008/600330_2008.txt', '../txt/2008/600366_2008.txt']\n"
     ]
    }
   ],
   "source": [
    "#!jupyter nbconvert --to script word_freq.ipynb\n",
    "import os\n",
    "#探索活动\t探索/探究/研究/研发/科研，搜寻/搜索，变化/变异，风险/冒险，实验，创新/设计，灵活，发现\n",
    "#开发活动\t利用/应用/开发，提炼/精炼/精益，稳定，选择，生产/投产，效率/成本，改进/改良，执行/实施\n",
    "freq_words = [\"探索\",\"探究\",\"研究\",\"研发\",\"科研\",\"搜寻\",\"搜索\",\"变化\",\"变异\",\"风险\",\"冒险\",\"实验\",\"创新\",\"设计\",\"灵活\",\"发现\",\n",
    "              \"利用\",\"应用\",\"开发\",\"提炼\",\"精炼\",\"精益\",\"稳定\",\"选择\",\"生产\",\"投产\",\"效率\",\"成本\",\"改进\",\"改良\",\"执行\",\"实施\"]\n",
    "              \n",
    "def find_files(base_dir,postfix='.pdf'):\n",
    "    files = []\n",
    "    for f in os.listdir(base_dir):\n",
    "        sub_dir = os.path.join(base_dir,f)\n",
    "        if not os.path.isdir(sub_dir):\n",
    "            print('{sub_dir} is not dir')\n",
    "            continue\n",
    "        files += [os.path.join(sub_dir,f) for f in os.listdir(sub_dir) if f.endswith(postfix)]\n",
    "    return files\n",
    "\n",
    "base_dir='../txt/'\n",
    "txt_files = find_files(base_dir,'.txt')\n",
    "print(len(txt_files))\n",
    "print(txt_files[:4])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "# read file to one line\n",
    "def read_content(file):\n",
    "    try:   \n",
    "        lines=[];\n",
    "        with open(file, 'r', encoding='utf-8') as f:\n",
    "            for line in f.readlines():                          #依次读取每行  \n",
    "                line = line.strip()\n",
    "                if len(line)>0:\n",
    "                    lines.append(line)\n",
    "\n",
    "        content=\"\".join(lines)\n",
    "        return content\n",
    "    except:\n",
    "        pass\n",
    "    \n",
    "    return \"\"\n",
    "\n",
    "\n",
    "# test the function\n",
    "file = '../scraping/content/300354_2015.txt'\n",
    "content=read_content(file)\n",
    "print(content[:256])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'探索': 0, '探究': 0, '研究': 0, '研发': 0, '科研': 0, '搜寻': 0, '搜索': 0, '变化': 0, '变异': 0, '风险': 0, '冒险': 0, '实验': 0, '创新': 0, '设计': 0, '灵活': 0, '发现': 0, '利用': 0, '应用': 0, '开发': 0, '提炼': 0, '精炼': 0, '精益': 0, '稳定': 0, '选择': 0, '生产': 0, '投产': 0, '效率': 0, '成本': 0, '改进': 0, '改良': 0, '执行': 0, '实施': 0}\n"
     ]
    }
   ],
   "source": [
    "def word_freq(content,freq_words):\n",
    "    freq_words_dict = {k:0 for k in freq_words}\n",
    "    start = 0\n",
    "    while start < len(content)-3:\n",
    "        word = content[start:start+2] # 取两个词\n",
    "        if word in freq_words_dict: freq_words_dict[word]+=1\n",
    "        start+=2\n",
    "    return freq_words_dict\n",
    "\n",
    "# test the function\n",
    "freq_words_dict= word_freq(content,freq_words)\n",
    "print(freq_words_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 12%|█▏        | 2507/21129 [02:15<17:09, 18.09it/s]"
     ]
    },
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-6-89337cbf7e44>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mfile\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mtqdm\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtxt_files\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m     \u001b[0mcontent\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mread_content\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfile\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 7\u001b[0;31m     \u001b[0mfreq_words_dict\u001b[0m\u001b[0;34m=\u001b[0m \u001b[0mword_freq\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcontent\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mfreq_words\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      8\u001b[0m     \u001b[0mcode\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0myear\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mos\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpath\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbasename\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfile\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreplace\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'.txt'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m''\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msplit\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'_'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      9\u001b[0m     \u001b[0mfreq_words_dict\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Symbol'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcode\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m<ipython-input-3-dd8ee6a60dee>\u001b[0m in \u001b[0;36mword_freq\u001b[0;34m(content, freq_words)\u001b[0m\n\u001b[1;32m      3\u001b[0m     \u001b[0mstart\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;36m0\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m     \u001b[0;32mwhile\u001b[0m \u001b[0mstart\u001b[0m \u001b[0;34m<\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcontent\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m-\u001b[0m\u001b[0;36m3\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 5\u001b[0;31m         \u001b[0mword\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcontent\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mstart\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0mstart\u001b[0m\u001b[0;34m+\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;31m# 取两个词\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      6\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mword\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mfreq_words_dict\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mfreq_words_dict\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mword\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m+=\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      7\u001b[0m         \u001b[0mstart\u001b[0m\u001b[0;34m+=\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "from tqdm import tqdm\n",
    "import pandas as pd\n",
    "records=[]\n",
    "# for file in tqdm(txt_files[:4]):\n",
    "for file in tqdm(txt_files):\n",
    "    content=read_content(file)\n",
    "    freq_words_dict= word_freq(content,freq_words)\n",
    "    code,year = os.path.basename(file).replace('.txt','').split('_')\n",
    "    freq_words_dict['Symbol']=code\n",
    "    freq_words_dict['EndDate']=year+'-12-31'\n",
    "    records.append(freq_words_dict)\n",
    "    \n",
    "\n",
    "df = pd.DataFrame(records)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Symbol</th>\n",
       "      <th>EndDate</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>0</th>\n",
       "      <td>300354</td>\n",
       "      <td>2015-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>41</td>\n",
       "      <td>31</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>603041</td>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>60</td>\n",
       "      <td>22</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>38</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>002426</td>\n",
       "      <td>2013-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>300381</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>115</td>\n",
       "      <td>32</td>\n",
       "      <td>16</td>\n",
       "      <td>6</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>002285</td>\n",
       "      <td>2012-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Symbol     EndDate  冒险  创新  利用  发现  变化  变异  实施  实验 ...   生产  研发  研究  科研  \\\n",
       "0  300354  2015-12-31   0  13   4   5   7   0  27   5 ...   41  31  19   2   \n",
       "1  603041  2017-12-31   0   8   4   6  13   0  23   0 ...   60  22  12   1   \n",
       "2  002426  2013-12-31   0   2   1   1   2   0   0   0 ...    2   3   0   0   \n",
       "3  300381  2016-12-31   0   9   6   8   6   0  22   4 ...  115  32  16   6   \n",
       "4  002285  2012-12-31   0   0   3   1   5   0   2   0 ...    0   0   1   0   \n",
       "\n",
       "   稳定  精炼  精益  设计  选择  风险  \n",
       "0   4   0   0   6   4  21  \n",
       "1  38   0   1   7   1  42  \n",
       "2   0   0   0   0   0   2  \n",
       "3  18   0   0   1   5  36  \n",
       "4   0   0   0   2   1   5  \n",
       "\n",
       "[5 rows x 34 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# cols = df.columns.tolist()\n",
    "# cols.remove('Symbol')\n",
    "# cols.insert(0,'Symbol')\n",
    "# df = df[cols]\n",
    "# df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "filename='./研究数据_out.xlsx'\n",
    "df2 = pd.read_excel(filename,index_col=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Unnamed: 0', 'Symbol', 'EndDate', 'ShortName', 'IndustryName', 'IndustryCode', 'RegisterAddress', 'Zipcode', 'EstablishDate', 'Listexg', 'Ucpd', 'Ucsvr', 'Nshld', 'Empnum', 'RDPersonRatio', 'RDSpendSumRatio', 'Nshrttl', 'Nshrstt', 'Rat_SO', 'Nshrsms', 'TotalAssets', 'TotalLiability', 'IntangibleAsset', 'NetProfit', 'OperationProfit', 'F050201B', 'F050202B', 'F050203B', 'F050204C', 'F100501A', 'F100801A', 'F100802A', 'F100901A', 'F100902A', 'F100903A', 'F100904A', '备注', '冒险', '创新', '利用', '发现', '变化', '变异', '实施', '实验', '应用', '开发', '成本', '执行', '投产', '探究', '探索', '提炼', '搜寻', '搜索', '改良', '改进', '效率', '灵活', '生产', '研发', '研究', '科研', '稳定', '精炼', '精益', '设计', '选择', '风险']\n",
      "['Unnamed: 0', 'Symbol', 'EndDate', 'ShortName', 'IndustryName', 'IndustryCode', 'RegisterAddress', 'Zipcode', 'EstablishDate', 'Listexg', 'Ucpd', 'Ucsvr', 'Nshld', 'Empnum', 'RDPersonRatio', 'RDSpendSumRatio', 'Nshrttl', 'Nshrstt', 'Rat_SO', 'Nshrsms', 'TotalAssets', 'TotalLiability', 'IntangibleAsset', 'NetProfit', 'OperationProfit', 'F050201B', 'F050202B', 'F050203B', 'F050204C', 'F100501A', 'F100801A', 'F100802A', 'F100901A', 'F100902A', 'F100903A', 'F100904A']\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>Symbol</th>\n",
       "      <th>EndDate</th>\n",
       "      <th>ShortName</th>\n",
       "      <th>IndustryName</th>\n",
       "      <th>IndustryCode</th>\n",
       "      <th>RegisterAddress</th>\n",
       "      <th>Zipcode</th>\n",
       "      <th>EstablishDate</th>\n",
       "      <th>Listexg</th>\n",
       "      <th>Ucpd</th>\n",
       "      <th>...</th>\n",
       "      <th>F050202B</th>\n",
       "      <th>F050203B</th>\n",
       "      <th>F050204C</th>\n",
       "      <th>F100501A</th>\n",
       "      <th>F100801A</th>\n",
       "      <th>F100802A</th>\n",
       "      <th>F100901A</th>\n",
       "      <th>F100902A</th>\n",
       "      <th>F100903A</th>\n",
       "      <th>F100904A</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</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>总资产净利润率（ROA）B'</td>\n",
       "      <td>总资产净利润率（ROA）C'</td>\n",
       "      <td>总资产净利润率（ROA）TTM'</td>\n",
       "      <td>市值有形资产比'</td>\n",
       "      <td>市值A'</td>\n",
       "      <td>市值B'</td>\n",
       "      <td>托宾Q值A'</td>\n",
       "      <td>托宾Q值B'</td>\n",
       "      <td>托宾Q值C'</td>\n",
       "      <td>托宾Q值D'</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000001</td>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>货币金融服务</td>\n",
       "      <td>J66</td>\n",
       "      <td>广东省深圳市罗湖区深南东路5047号</td>\n",
       "      <td>518001</td>\n",
       "      <td>1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.007478</td>\n",
       "      <td>0.007478</td>\n",
       "      <td>0.007478</td>\n",
       "      <td>0.070564</td>\n",
       "      <td>3.25469e+12</td>\n",
       "      <td>3.25479e+12</td>\n",
       "      <td>0.0703</td>\n",
       "      <td>0.070566</td>\n",
       "      <td>1.00194</td>\n",
       "      <td>1.00574</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000001</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>货币金融服务</td>\n",
       "      <td>J66</td>\n",
       "      <td>广东省深圳市罗湖区深南东路5047号</td>\n",
       "      <td>518001</td>\n",
       "      <td>1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.008277</td>\n",
       "      <td>0.008277</td>\n",
       "      <td>0.008277</td>\n",
       "      <td>0.053125</td>\n",
       "      <td>2.9143e+12</td>\n",
       "      <td>2.90751e+12</td>\n",
       "      <td>0.052905</td>\n",
       "      <td>0.053127</td>\n",
       "      <td>0.984452</td>\n",
       "      <td>0.988582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000001</td>\n",
       "      <td>2015-12-31</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>货币金融服务</td>\n",
       "      <td>J66</td>\n",
       "      <td>广东省深圳市罗湖区深南东路5047号</td>\n",
       "      <td>518001</td>\n",
       "      <td>1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.009317</td>\n",
       "      <td>0.009317</td>\n",
       "      <td>0.009317</td>\n",
       "      <td>0.068774</td>\n",
       "      <td>2.51545e+12</td>\n",
       "      <td>2.51721e+12</td>\n",
       "      <td>0.068429</td>\n",
       "      <td>0.068772</td>\n",
       "      <td>1.00401</td>\n",
       "      <td>1.00906</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000001</td>\n",
       "      <td>2014-12-31</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>货币金融服务</td>\n",
       "      <td>J66</td>\n",
       "      <td>广东省深圳市罗湖区深南东路5047号</td>\n",
       "      <td>518001</td>\n",
       "      <td>1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.009711</td>\n",
       "      <td>0.009711</td>\n",
       "      <td>0.009711</td>\n",
       "      <td>0.083259</td>\n",
       "      <td>2.22953e+12</td>\n",
       "      <td>2.23648e+12</td>\n",
       "      <td>0.082769</td>\n",
       "      <td>0.083258</td>\n",
       "      <td>1.02288</td>\n",
       "      <td>1.02893</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Symbol     EndDate ShortName IndustryName IndustryCode     RegisterAddress  \\\n",
       "0   证券代码'     统计截止日期'     股票简称'        行业名称'        行业代码'             注册具体地址'   \n",
       "1  000001  2017-12-31      平安银行       货币金融服务          J66  广东省深圳市罗湖区深南东路5047号   \n",
       "2  000001  2016-12-31      平安银行       货币金融服务          J66  广东省深圳市罗湖区深南东路5047号   \n",
       "3  000001  2015-12-31      平安银行       货币金融服务          J66  广东省深圳市罗湖区深南东路5047号   \n",
       "4  000001  2014-12-31      平安银行       货币金融服务          J66  广东省深圳市罗湖区深南东路5047号   \n",
       "\n",
       "     Zipcode EstablishDate Listexg               Ucpd  ...        F050202B  \\\n",
       "0  办公地址邮政编码'       公司成立日期'  上市交易所'  实际控制人是否担任董事长或总经理'  ...  总资产净利润率（ROA）B'   \n",
       "1     518001          1987     NaN                NaN  ...        0.007478   \n",
       "2     518001          1987     NaN                NaN  ...        0.008277   \n",
       "3     518001          1987     NaN                NaN  ...        0.009317   \n",
       "4     518001          1987     NaN                NaN  ...        0.009711   \n",
       "\n",
       "         F050203B          F050204C  F100501A     F100801A     F100802A  \\\n",
       "0  总资产净利润率（ROA）C'  总资产净利润率（ROA）TTM'  市值有形资产比'         市值A'         市值B'   \n",
       "1        0.007478          0.007478  0.070564  3.25469e+12  3.25479e+12   \n",
       "2        0.008277          0.008277  0.053125   2.9143e+12  2.90751e+12   \n",
       "3        0.009317          0.009317  0.068774  2.51545e+12  2.51721e+12   \n",
       "4        0.009711          0.009711  0.083259  2.22953e+12  2.23648e+12   \n",
       "\n",
       "   F100901A  F100902A  F100903A  F100904A  \n",
       "0    托宾Q值A'    托宾Q值B'    托宾Q值C'    托宾Q值D'  \n",
       "1    0.0703  0.070566   1.00194   1.00574  \n",
       "2  0.052905  0.053127  0.984452  0.988582  \n",
       "3  0.068429  0.068772   1.00401   1.00906  \n",
       "4  0.082769  0.083258   1.02288   1.02893  \n",
       "\n",
       "[5 rows x 35 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 过滤中文字段，避免历史数据造成的干扰\n",
    "df2.columns.tolist()\n",
    "import re\n",
    "chs_re = re.compile(\"[\\u4e00-\\u9fa5]+\")\n",
    "print(df2.columns.tolist())\n",
    "filer_columns = list(filter(lambda x:not chs_re.match(x), df2.columns.tolist()))\n",
    "print(filer_columns)\n",
    "df2_ = df2[filer_columns[1:]]\n",
    "df2_.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "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>Symbol</th>\n",
       "      <th>EndDate</th>\n",
       "      <th>ShortName</th>\n",
       "      <th>IndustryName</th>\n",
       "      <th>IndustryCode</th>\n",
       "      <th>RegisterAddress</th>\n",
       "      <th>Zipcode</th>\n",
       "      <th>EstablishDate</th>\n",
       "      <th>Listexg</th>\n",
       "      <th>Ucpd</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>0</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000001</td>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>货币金融服务</td>\n",
       "      <td>J66</td>\n",
       "      <td>广东省深圳市罗湖区深南东路5047号</td>\n",
       "      <td>518001</td>\n",
       "      <td>1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000001</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>货币金融服务</td>\n",
       "      <td>J66</td>\n",
       "      <td>广东省深圳市罗湖区深南东路5047号</td>\n",
       "      <td>518001</td>\n",
       "      <td>1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000001</td>\n",
       "      <td>2015-12-31</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>货币金融服务</td>\n",
       "      <td>J66</td>\n",
       "      <td>广东省深圳市罗湖区深南东路5047号</td>\n",
       "      <td>518001</td>\n",
       "      <td>1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000001</td>\n",
       "      <td>2014-12-31</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>货币金融服务</td>\n",
       "      <td>J66</td>\n",
       "      <td>广东省深圳市罗湖区深南东路5047号</td>\n",
       "      <td>518001</td>\n",
       "      <td>1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 67 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Symbol     EndDate ShortName IndustryName IndustryCode     RegisterAddress  \\\n",
       "0   证券代码'     统计截止日期'     股票简称'        行业名称'        行业代码'             注册具体地址'   \n",
       "1  000001  2017-12-31      平安银行       货币金融服务          J66  广东省深圳市罗湖区深南东路5047号   \n",
       "2  000001  2016-12-31      平安银行       货币金融服务          J66  广东省深圳市罗湖区深南东路5047号   \n",
       "3  000001  2015-12-31      平安银行       货币金融服务          J66  广东省深圳市罗湖区深南东路5047号   \n",
       "4  000001  2014-12-31      平安银行       货币金融服务          J66  广东省深圳市罗湖区深南东路5047号   \n",
       "\n",
       "     Zipcode EstablishDate Listexg               Ucpd  ...  生产  研发  研究  科研  \\\n",
       "0  办公地址邮政编码'       公司成立日期'  上市交易所'  实际控制人是否担任董事长或总经理'  ... NaN NaN NaN NaN   \n",
       "1     518001          1987     NaN                NaN  ... NaN NaN NaN NaN   \n",
       "2     518001          1987     NaN                NaN  ... NaN NaN NaN NaN   \n",
       "3     518001          1987     NaN                NaN  ... NaN NaN NaN NaN   \n",
       "4     518001          1987     NaN                NaN  ... NaN NaN NaN NaN   \n",
       "\n",
       "   稳定  精炼  精益  设计  选择  风险  \n",
       "0 NaN NaN NaN NaN NaN NaN  \n",
       "1 NaN NaN NaN NaN NaN NaN  \n",
       "2 NaN NaN NaN NaN NaN NaN  \n",
       "3 NaN NaN NaN NaN NaN NaN  \n",
       "4 NaN NaN NaN NaN NaN NaN  \n",
       "\n",
       "[5 rows x 67 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = df2_.merge(df,how='left',on=['Symbol','EndDate'])\n",
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.to_excel(filename.replace('.xlsx','_words.xlsx'))"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}