prepare_database2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# The Idea"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> The CPC / IPC \"*provides a hierarchical system of language independent symbols for the classification of patents and utility models according to the different areas of technology to which they pertain*\".\n",
    "\n",
    "> To be able to identify each entry in the hierarchy, at least a **classificationn symbol** and \"**level**\"(CPC)/ \"**kind**\"(IPC) are specified.\n",
    "\n",
    "> We will iterate over the levels and create a table in our database containing the entries and the corresponding direct parent to establish  the hierarchy. We will then enrich our database with languages, dates, statistics ... which will be very useful for the visualization later on."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> **Summary**\n",
    "- Overview: IPC and CPC structure\n",
    "- IPC and CPC : from XML to database\n",
    "    - Level formatting \n",
    "    - Symbol formatting\n",
    "- IPC and CPC titles\n",
    "- IPC and CPC evolution - Technology Tree\n",
    "- IPC and CPC statistics\n",
    "- Run"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Overview: IPC and CPC structure"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    ">A complete classification symbol comprises the combined symbols representing the section, class, subclass and main group or subgroup."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![classidication](./static/classification1.PNG)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "| Symbol | IPC   | CPC   |\n",
    "|------|------|------|\n",
    "|   **Section Symbol**  | one of the capital letters A through H | the addition of section 'Y' is unique to CPC.|\n",
    "|   **Class Symbol**  | section symbol followed by a two-digit number | - |\n",
    "|   **Subclass Symbol**  | class symbol followed by a capital letter | - |\n",
    "|   **Main Group Symbol**  | subclass symbol followed by a one- to three-digit number, the slash (\"/\") and the number 00 | subclass symbol followed by a one- to four-digit number, the slash (\"/\") and the number 00 |\n",
    "|   **Subgroup Symbol**  | subclass symbol followed by the one- to three-digit number of its main group, the slash (\"/\") and a number of at least two digits other than 00 | subclass symbol followed by the one- to four-digit number of its main group, the slash (\"/\") and a two- to six-digit numbers other than 00 |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    ">The hierarchical structure is shown in the following example:\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![classidication](./static/classification2.PNG)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> **A new version of the IPC enters into force each year on January 1. The CPC is updated multiple times a years. Therefore the links have to be updated in the config.py!**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# IPC and CPC : from XML to database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# to open the xml files\n",
    "import requests, zipfile, io, os\n",
    "import tempfile\n",
    "\n",
    "# to connect to database\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy import inspect\n",
    "from sqlalchemy.engine.url import URL\n",
    "from sqlalchemy.types import String\n",
    "import sqlite3\n",
    "\n",
    "# to read, write and process the data\n",
    "import pandas as pd\n",
    "from lxml import etree as ET\n",
    "from sklearn import preprocessing\n",
    "import re\n",
    "import time\n",
    "import numpy as np\n",
    "\n",
    "# initiate configuration class and connect to database\n",
    "from config import config\n",
    "config = config()\n",
    "reqsD = config.connect_to_database()\n",
    "fileD = config.files()\n",
    "engine = create_engine(str(URL(**reqsD)) + '/patent_classification')\n",
    "conn = engine.connect()\n",
    "inspector = inspect(engine)\n",
    "# check for schema and table in database\n",
    "#if scheme in inspector.get_table_names():\n",
    "#    conn.execute('DROP TABLE patent_classification.' + scheme)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    ">We iterate over the entries and save the clasification hierarchy i.e. \n",
    "- classification symbols as indices\n",
    "- kinds\n",
    "- direct parents."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# takes approximately 15 min\n",
    "def importer(scheme, fileD, schemaL, engine, conn, to_sql):\n",
    "    with tempfile.TemporaryDirectory() as directory:\n",
    "\n",
    "        # unzip...\n",
    "        url = fileD[scheme]\n",
    "        r = requests.get(url)\n",
    "        z = zipfile.ZipFile(io.BytesIO(r.content))\n",
    "        z.extractall(directory)\n",
    "        filename = os.listdir(directory)[0] #en and fr version available, for the hierarchy struture only need one \n",
    "\n",
    "        # ...and parse file\n",
    "        parser = ET.XMLParser(remove_blank_text=True)\n",
    "        path = os.path.join(directory, filename)\n",
    "        tree = ET.parse(path, parser=parser)\n",
    "        root = tree.getroot()\n",
    "\n",
    "        # list of parent entries to iterate over\n",
    "        parentL=[root]\n",
    "\n",
    "        for parent in parentL:\n",
    "            # create dataframe for each level and parent\n",
    "            df = pd.DataFrame(columns=['kind', 'parent'])\n",
    "\n",
    "            # iterate over children entries\n",
    "            for entry in parent.iterchildren(tag='{http://www.wipo.int/classifications/ipc/masterfiles}ipcEntry'):\n",
    "\n",
    "                # add children to list of parents  \n",
    "                parentL.append(entry)\n",
    "\n",
    "                # save classification symbol\n",
    "                symbol = entry.attrib['symbol']\n",
    "                \n",
    "                # get classification level \n",
    "                kind = entry.attrib['kind']\n",
    "                \n",
    "                # save kind and symbol of direct parent\n",
    "                if kind == \"s\":\n",
    "                    # for entries of level 1 save \"IPC\" as parent\n",
    "                    df.loc[symbol, 'kind']= kind\n",
    "                    df.loc[symbol, 'parent']= scheme.upper()\n",
    "                elif kind not in ['t', 'i', 'g', 'n']:\n",
    "                    # save only relevant entries \n",
    "                    if parent.tag == '{http://www.wipo.int/classifications/ipc/masterfiles}ipcEntry':\n",
    "                        df.loc[symbol, 'kind']= kind\n",
    "                        df.loc[symbol, 'parent']= parent.attrib['symbol']\n",
    "                        \n",
    "            # write to database\n",
    "            if to_sql == 'yes':\n",
    "                df.to_sql(scheme, engine, if_exists='append', schema='patent_classification', index=True, index_label=\"symbol\")\n",
    "                "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Level formatting"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Additionally to the alphanumeric \"kinds\" of the IPC (\"s\" for section, \"c\" for class, \"u\" for subclass, \"m\" for main group and 1-9, \"A\", \"B\" for sub groups), we will use the numeric equivalent \"levels\" of the CPC (2-19) to be able to iterate over a well-ordered set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_levels(scheme, engine, to_sql):\n",
    "    \n",
    "    # save \"levels\" by mapping corresponding \"kinds\"\n",
    "    df = pd.read_sql_table(scheme, engine, schema='patent_classification', index_col='symbol')\n",
    "    df['level'] = df['kind']\n",
    "    kind_to_levelD = {'s': 2, 'c': 3, 'u': 4, 'm' :5, '1': 6, '2': 7, '3': 8, '4': 9, '5': 10,\n",
    "                      '6': 11, '7': 12, '8': 13, '9': 14, 'A': 15, 'B': 16, 'C':17}\n",
    "    df.replace({\"level\": kind_to_levelD}, inplace=True)\n",
    "    if to_sql == 'yes':\n",
    "        df.to_sql(scheme, engine, if_exists='replace', schema='patent_classification', index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Symbol formatting"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> The symbols in the xml files have not the format shown in the exemple from the beginin: it's H01F0001053000 and not H01F1/053. So we add the short format for the symbols."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# transforms H01F0001053000 to H01F1/053\n",
    "def formatting_symbols(x):\n",
    "    # for section, class, subclass return symbol\n",
    "    if len(x)<= 4:\n",
    "        return x\n",
    "    else:\n",
    "        # for group remove leading and trailing zeros\n",
    "        x = x[:4] + str(int(x[4:8])) + '/' + x[8:].rstrip('0')\n",
    "        \n",
    "        if x[-1] == '/':\n",
    "            return x + '00'\n",
    "        else:\n",
    "            return x\n",
    "\n",
    "def add_symbols_short(scheme, engine, to_sql):\n",
    "    df = pd.read_sql_table(scheme, engine, schema='patent_classification', index_col='symbol')\n",
    "    df['symbol_short'] = list(map(lambda x: formatting_symbols(x), df.index.tolist()))\n",
    "    df['parent_short'] = df['parent'].apply(lambda x: formatting_symbols(x))\n",
    "\n",
    "    if to_sql == 'yes':\n",
    "        df.to_sql(scheme, engine, if_exists='replace', schema='patent_classification', index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# IPC and CPC titles"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> We choose to not extract the titles directly from the xml files (because the syntax is not practical). The txt files containing the IPC/ CPC scheme titles in authentic languages look as follow (except that for the CPC the short symbol format is used). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                0                                                  1\n",
      "0               A                                  HUMAN NECESSITIES\n",
      "1             A01  AGRICULTURE; FORESTRY; ANIMAL HUSBANDRY; HUNTI...\n",
      "2            A01B  SOIL WORKING IN AGRICULTURE OR FORESTRY; PARTS...\n",
      "3  A01B0001000000  Hand tools (edge trimmers for lawns A01G000306...\n",
      "4  A01B0001020000                                    Spades; Shovels\n",
      "5  A01B0001040000                                         with teeth\n",
      "6  A01B0001060000                             Hoes; Hand cultivators\n",
      "7  A01B0001080000                                with a single blade\n",
      "8  A01B0001100000                            with two or more blades\n",
      "9  A01B0001120000                    with blades provided with teeth\n"
     ]
    }
   ],
   "source": [
    "titles = pd.read_csv(fileD['ipc_en'][0], sep='\\t', header=None)\n",
    "print(titles.head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    ">We concatenate the txt files from A to H (A to Y for the CPC) to a dataframe with the symbols as indices and a column of titles, which we then join to the database table. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_titles(scheme, schemeD, index, engine):\n",
    "\n",
    "    for language in list(schemeD.keys()):\n",
    "\n",
    "        # concatenate txt files to dataframe of titles \n",
    "        titles= []\n",
    "\n",
    "        # ...for the cpc it's a txt file that we have to unzip first\n",
    "        if scheme == 'cpc':\n",
    "            with tempfile.TemporaryDirectory() as directory:\n",
    "                r = requests.get(schemeD[language])\n",
    "                z = zipfile.ZipFile(io.BytesIO(r.content))\n",
    "                z.extractall(directory)\n",
    "                filenameL = [file for file in os.listdir(directory) if file.endswith('.txt')]\n",
    "                for filename in filenameL:\n",
    "                    path = os.path.join(directory, filename)\n",
    "                    df = pd.read_csv(path,  sep='\\t', header=None, names=[index, 'title_' + language])\n",
    "                    titles.append(df)\n",
    "        else:\n",
    "            for filename in schemeD[language]:\n",
    "                r = requests.get(filename)\n",
    "                byte = r.content\n",
    "                s=str(byte,'utf-8')\n",
    "                data = StringIO(s) \n",
    "                df = pd.read_csv(data,  sep='\\t', header=None, names=[index, 'title_' + language])\n",
    "                titles.append(df)\n",
    "        df_titles = pd.concat(titles, axis=0, ignore_index=True)\n",
    "        df_titles.drop_duplicates(subset=index, keep='last', inplace=True)\n",
    "\n",
    "        # append titles to database\n",
    "        df = pd.read_sql_table(scheme, engine, schema='patent_classification', index_col=index)\n",
    "        df = df.join(df_titles.set_index(index))\n",
    "        return df\n",
    "        #df.to_sql(scheme, engine, if_exists='replace', schema='patent_classification', index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# IPC and CPC evolution - Technology Tree"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> To trace the development of the technology fields over time, we save a creation date for every symbol."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def pc_evolution(scheme, index, engine, fileD, to_sql):\n",
    "    \n",
    "    # read table from database\n",
    "    df = pd.read_sql_table(scheme, engine, schema='patent_classification', index_col=index)\n",
    "\n",
    "    # read inventory of IPC/CPC ever used symbols with creation date\n",
    "    inventory = fileD[ scheme + '_inventory']\n",
    "    \n",
    "    # ...for the cpc it's a txt file that we have to unzip first\n",
    "    if scheme == 'cpc':\n",
    "        with tempfile.TemporaryDirectory() as directory:\n",
    "            r = requests.get(inventory)\n",
    "            z = zipfile.ZipFile(io.BytesIO(r.content))\n",
    "            z.extractall(directory)\n",
    "            filename = [file for file in os.listdir(directory) if file.endswith('.txt')][0]\n",
    "            path = os.path.join(directory, filename)\n",
    "            \n",
    "            pc_inventory = pd.read_csv(path, sep='\\t', names= [index, 'creation_date', 'expiration_date'], usecols=[0,1])\n",
    "    \n",
    "    # ...for the ipc we can read it directly\n",
    "    else:\n",
    "        pc_inventory = pd.read_csv(inventory, sep=';', names= [index, 'creation_date', 'expiration_date'], usecols=[0,1])\n",
    "    \n",
    "    # we join the tables...\n",
    "    df = df.join(pc_inventory.set_index(index))\n",
    "    \n",
    "    # ...and fill the creation dates of the section and class symbols\n",
    "    if scheme == 'cpc':\n",
    "        creation_data = '2013-01-01'\n",
    "    else:\n",
    "        creation_data = 19680901\n",
    "    df.fillna(value= {'creation_date': creation_data}, inplace=True)\n",
    "    \n",
    "    if to_sql == 'yes':\n",
    "        df.to_sql(scheme, engine, if_exists='replace', schema='patent_classification', index=True, dtype={'creation_date':String()})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# IPC and CPC statistics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> The WIPO offers a summary table with the number of classes, subclasses, main groups, subgroups and groups (i.e. sum of main groups and subgroups) for each section. This statistics are only given at the highest hierachical level. The tree allows us to have statistcs for each entry at each hierarchical level."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> We trace all the possible branches of the tree i.e. the paths of all the symbols from main groups and subgroups. Then we can \"count\" for each symbol of each level in how many paths it appears and deduce a size for each entry of the tree."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# returns a list with the paths of all symbols from main groups and subgroups as lists of symbols\n",
    "# takes approximately 30 min\n",
    "def get_paths(scheme, engine):\n",
    "    \n",
    "    df = pd.read_sql_table(scheme, engine, schema='patent_classification')\n",
    "    df['level'] = pd.to_numeric(df['level'])\n",
    "    grouped_level = df.groupby('level')\n",
    "    \n",
    "    pathsL = []\n",
    "    \n",
    "    # iterte df over rows\n",
    "    for index, row in df.iterrows():\n",
    "        key = row['symbol']\n",
    "        level = int(row['level'])\n",
    "        \n",
    "        # select only symbols of main groups and subgroups\n",
    "        if level >4:\n",
    "            path=[key]\n",
    "            # trace path by appending successively the corresponding direct parent to a list\n",
    "            for i in range(level , 2, -1):\n",
    "                group = grouped_level.get_group(i)\n",
    "                try:\n",
    "                    parent = group[group['symbol'] == key]['parent'].iloc[0]\n",
    "                    key = parent\n",
    "                    path.append(parent)\n",
    "                except:\n",
    "                    print(key)\n",
    "                    pass\n",
    "            path = path[::-1]\n",
    "            pathsL.append(path)\n",
    "    df = pd.DataFrame(pathsL)\n",
    "    df.to_csv(scheme + '_statistics.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def pc_statistics(pathsL, scheme, engine, to_sql):\n",
    "    \n",
    "    # convert list of paths to dataframe and fill paths with 0 to reach maximal lenght\n",
    "    paths_df = pd.read_csv('ipc_statistics.csv', index_col=0)\n",
    "    paths_df.fillna('0', inplace=True)\n",
    "\n",
    "    stat=[]\n",
    "    # iterate df over columns in order to group by each level\n",
    "    for i in range(paths_df.shape[1]):\n",
    "        grouped = paths_df.groupby([str(j) for j in range(i+1)])\n",
    "        \n",
    "        # save symbol and it's size to list\n",
    "        for key in grouped.groups.keys():\n",
    "            if '0' not in key:\n",
    "                stat.append([key[-1], len(grouped.get_group(key))])\n",
    "\n",
    "    # convert to df with the symbols and for each symbol a value corresponding to the number of groups it contains\n",
    "    stat_df = pd.DataFrame(stat, columns=['symbol', 'size'])\n",
    "    \n",
    "    # add size in % \n",
    "    stat_df['size_percent'] = round(stat_df['size'] * 100 / 74503, 3)\n",
    "    \n",
    "    # append statistics to database\n",
    "    df = pd.read_sql_table(scheme, engine, schema='patent_classification')\n",
    "    #pathsL = get_paths(df, engine)\n",
    "    df = df.set_index('symbol').join(stat_df.set_index('symbol'))\n",
    "    \n",
    "    # add normalised size for each group of children for visualization\n",
    "    df['size_normalised'] = df['size']\n",
    "    grouped = df.groupby('parent')\n",
    "    for key in grouped.groups.keys():\n",
    "        group = grouped.get_group(key)\n",
    "        x = group['size_normalised'].values.reshape(-1, 1)\n",
    "        min_max_scaler = preprocessing.MinMaxScaler(feature_range=(3, 13))\n",
    "        x_scaled = min_max_scaler.fit_transform(x)\n",
    "        index = group.index.values\n",
    "        df.loc[index, 'size_normalised'] = x_scaled\n",
    "    \n",
    "    if to_sql == 'yes':\n",
    "        df.to_sql(scheme, engine, if_exists='replace', schema='patent_classification')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Sanity checks for the statistics: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def sanity_check(df):\n",
    "    grouped = df.groupby('level')\n",
    "    for key in grouped.groups.keys():\n",
    "        group = grouped.get_group(key)\n",
    "        size = group['size'].sum()\n",
    "        print(key, size)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# From postgresql to sqlite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def postgresql_to_sqlite(scheme, engine):\n",
    "    df = pd.read_sql_table(scheme, engine, schema='patent_classification')\n",
    "    conn2 = sqlite3.connect('./patent-classification.db')\n",
    "    df.to_sql(name=scheme, schema='patent-classification', index=False, con=conn2, if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<bound method NDFrame.head of                symbol kind          parent  level symbol_short parent_short  \\\n",
      "0                   A    s             IPC      2            A          IPC   \n",
      "1                 A01    c               A      3          A01            A   \n",
      "2                A01B    u             A01      4         A01B          A01   \n",
      "3      A01B0001000000    m            A01B      5     A01B1/00         A01B   \n",
      "4      A01B0001020000    1  A01B0001000000      6     A01B1/02     A01B1/00   \n",
      "5      A01B0001040000    2  A01B0001020000      7     A01B1/04     A01B1/02   \n",
      "6      A01B0001060000    1  A01B0001000000      6     A01B1/06     A01B1/00   \n",
      "7      A01B0001080000    2  A01B0001060000      7     A01B1/08     A01B1/06   \n",
      "8      A01B0001100000    2  A01B0001060000      7      A01B1/1     A01B1/06   \n",
      "9      A01B0001120000    2  A01B0001060000      7     A01B1/12     A01B1/06   \n",
      "10     A01B0001140000    2  A01B0001060000      7     A01B1/14     A01B1/06   \n",
      "11     A01B0001160000    1  A01B0001000000      6     A01B1/16     A01B1/00   \n",
      "12     A01B0001180000    2  A01B0001160000      7     A01B1/18     A01B1/16   \n",
      "13     A01B0001200000    1  A01B0001000000      6      A01B1/2     A01B1/00   \n",
      "14     A01B0001220000    1  A01B0001000000      6     A01B1/22     A01B1/00   \n",
      "15     A01B0001240000    1  A01B0001000000      6     A01B1/24     A01B1/00   \n",
      "16     A01B0003000000    m            A01B      5     A01B3/00         A01B   \n",
      "17     A01B0003000000    m            A01B      5     A01B3/00         A01B   \n",
      "18     A01B0003000000    m            A01B      5     A01B3/00         A01B   \n",
      "19     A01B0003000000    m            A01B      5     A01B3/00         A01B   \n",
      "20     A01B0003020000    1  A01B0003000000      6     A01B3/02     A01B3/00   \n",
      "21     A01B0003040000    1  A01B0003000000      6     A01B3/04     A01B3/00   \n",
      "22     A01B0003060000    2  A01B0003040000      7     A01B3/06     A01B3/04   \n",
      "23     A01B0003080000    3  A01B0003060000      8     A01B3/08     A01B3/06   \n",
      "24     A01B0003100000    3  A01B0003060000      8      A01B3/1     A01B3/06   \n",
      "25     A01B0003120000    3  A01B0003060000      8     A01B3/12     A01B3/06   \n",
      "26     A01B0003140000    3  A01B0003060000      8     A01B3/14     A01B3/06   \n",
      "27     A01B0003160000    2  A01B0003040000      7     A01B3/16     A01B3/04   \n",
      "28     A01B0003180000    3  A01B0003160000      8     A01B3/18     A01B3/16   \n",
      "29     A01B0003200000    3  A01B0003160000      8      A01B3/2     A01B3/16   \n",
      "...               ...  ...             ...    ...          ...          ...   \n",
      "76877  H05K0003440000    1  H05K0003000000      6     H05K3/44     H05K3/00   \n",
      "76878  H05K0003460000    1  H05K0003000000      6     H05K3/46     H05K3/00   \n",
      "76879  H05K0005000000    m            H05K      5     H05K5/00         H05K   \n",
      "76880  H05K0005020000    1  H05K0005000000      6     H05K5/02     H05K5/00   \n",
      "76881  H05K0005030000    2  H05K0005020000      7     H05K5/03     H05K5/02   \n",
      "76882  H05K0005040000    1  H05K0005000000      6     H05K5/04     H05K5/00   \n",
      "76883  H05K0005060000    1  H05K0005000000      6     H05K5/06     H05K5/00   \n",
      "76884  H05K0007000000    m            H05K      5     H05K7/00         H05K   \n",
      "76885  H05K0007020000    1  H05K0007000000      6     H05K7/02     H05K7/00   \n",
      "76886  H05K0007040000    2  H05K0007020000      7     H05K7/04     H05K7/02   \n",
      "76887  H05K0007060000    2  H05K0007020000      7     H05K7/06     H05K7/02   \n",
      "76888  H05K0007080000    3  H05K0007060000      8     H05K7/08     H05K7/06   \n",
      "76889  H05K0007100000    2  H05K0007020000      7      H05K7/1     H05K7/02   \n",
      "76890  H05K0007120000    2  H05K0007020000      7     H05K7/12     H05K7/02   \n",
      "76891  H05K0007140000    1  H05K0007000000      6     H05K7/14     H05K7/00   \n",
      "76892  H05K0007160000    2  H05K0007140000      7     H05K7/16     H05K7/14   \n",
      "76893  H05K0007180000    1  H05K0007000000      6     H05K7/18     H05K7/00   \n",
      "76894  H05K0007200000    1  H05K0007000000      6      H05K7/2     H05K7/00   \n",
      "76895  H05K0009000000    m            H05K      5     H05K9/00         H05K   \n",
      "76896  H05K0010000000    m            H05K      5    H05K10/00         H05K   \n",
      "76897  H05K0011000000    m            H05K      5    H05K11/00         H05K   \n",
      "76898  H05K0011020000    1  H05K0011000000      6    H05K11/02    H05K11/00   \n",
      "76899  H05K0013000000    m            H05K      5    H05K13/00         H05K   \n",
      "76900  H05K0013020000    1  H05K0013000000      6    H05K13/02    H05K13/00   \n",
      "76901  H05K0013040000    1  H05K0013000000      6    H05K13/04    H05K13/00   \n",
      "76902  H05K0013060000    1  H05K0013000000      6    H05K13/06    H05K13/00   \n",
      "76903  H05K0013080000    1  H05K0013000000      6    H05K13/08    H05K13/00   \n",
      "76904             H99    c               H      3          H99            H   \n",
      "76905            H99Z    u             H99      4         H99Z          H99   \n",
      "76906  H99Z0099000000    m            H99Z      5    H99Z99/00         H99Z   \n",
      "\n",
      "                                                title_en  \\\n",
      "0                                      HUMAN NECESSITIES   \n",
      "1      AGRICULTURE; FORESTRY; ANIMAL HUSBANDRY; HUNTI...   \n",
      "2      SOIL WORKING IN AGRICULTURE OR FORESTRY; PARTS...   \n",
      "3      Hand tools (edge trimmers for lawns A01G000306...   \n",
      "4                                        Spades; Shovels   \n",
      "5                                             with teeth   \n",
      "6                                 Hoes; Hand cultivators   \n",
      "7                                    with a single blade   \n",
      "8                                with two or more blades   \n",
      "9                        with blades provided with teeth   \n",
      "10                                       with teeth only   \n",
      "11                             Tools for uprooting weeds   \n",
      "12                                       Tong-like tools   \n",
      "13         Combinations of different kinds of hand tools   \n",
      "14     Attaching the blades or the like to handles (h...   \n",
      "15                         for treating meadows or lawns   \n",
      "16                                               Ploughs   \n",
      "17                                               Ploughs   \n",
      "18                      Ploughs with fixed plough-shares   \n",
      "19                      Ploughs with fixed plough-shares   \n",
      "20                                    Man-driven ploughs   \n",
      "21                                  Animal-drawn ploughs   \n",
      "22     without alternating possibility, i.e. incapabl...   \n",
      "23                                         Swing ploughs   \n",
      "24            Trussed-beam ploughs; Single-wheel ploughs   \n",
      "25                                Two-wheel beam ploughs   \n",
      "26                                         Frame ploughs   \n",
      "27     Alternating ploughs, i.e. capable of making an...   \n",
      "28                                    Turn-wrest ploughs   \n",
      "29                                       Balance ploughs   \n",
      "...                                                  ...   \n",
      "76877        Manufacturing insulated metal core circuits   \n",
      "76878                 Manufacturing multi-layer circuits   \n",
      "76879  Casings, cabinets or drawers for electric appa...   \n",
      "76880                                            Details   \n",
      "76881                                             Covers   \n",
      "76882                                      Metal casings   \n",
      "76883                        Hermetically-sealed casings   \n",
      "76884  Constructional details common to different typ...   \n",
      "76885  Arrangements of circuit components or wiring o...   \n",
      "76886                              on conductive chassis   \n",
      "76887                               on insulating boards   \n",
      "76888                               on perforated boards   \n",
      "76889                  Plug-in assemblages of components   \n",
      "76890  Resilient or clamping means for holding compon...   \n",
      "76891  Mounting supporting structure in casing or on ...   \n",
      "76892                                on hinges or pivots   \n",
      "76893                      Construction of rack or frame   \n",
      "76894  Modifications to facilitate cooling, ventilati...   \n",
      "76895  Screening of apparatus or components against e...   \n",
      "76896  Arrangements for improving the operating relia...   \n",
      "76897  Combinations of a radio or television receiver...   \n",
      "76898                                      with vehicles   \n",
      "76899  Apparatus or processes specially adapted for m...   \n",
      "76900                              Feeding of components   \n",
      "76901                             Mounting of components   \n",
      "76902                                  Wiring by machine   \n",
      "76903              Monitoring manufacture of assemblages   \n",
      "76904  SUBJECT MATTER NOT OTHERWISE PROVIDED FOR IN T...   \n",
      "76905  SUBJECT MATTER NOT OTHERWISE PROVIDED FOR IN T...   \n",
      "76906  Subject matter not otherwise provided for in t...   \n",
      "\n",
      "                                                title_fr creation_date  size  \\\n",
      "0                         NÉCESSITÉS COURANTES DE LA VIE      19680901  9184   \n",
      "1      AGRICULTURE; SYLVICULTURE; ÉLEVAGE; CHASSE; PI...      19680901  1573   \n",
      "2      TRAVAIL DE LA TERRE EN AGRICULTURE OU EN SYLVI...    19680901.0   224   \n",
      "3      Outils à main (tranche-bordures pour pelouses ...    19680901.0    13   \n",
      "4                                         Bêches; Pelles    19680901.0     2   \n",
      "5                                              dentelées    19680901.0     1   \n",
      "6                                 Houes; Bineuses à main    19680901.0     5   \n",
      "7                                          à lame unique    19680901.0     1   \n",
      "8                                      à plusieurs lames    19680901.0     1   \n",
      "9                                      à lames dentelées    19680901.0     1   \n",
      "10                                    à dents uniquement    19680901.0     1   \n",
      "11                                             Sarcloirs    19680901.0     2   \n",
      "12                                  Outils du type pince    19680901.0     1   \n",
      "13     Combinaisons de différentes sortes d'outils à ...    19680901.0     1   \n",
      "14     Fixation aux manches des lames ou similaires (...    19680901.0     1   \n",
      "15            pour le traitement de prairies ou pelouses    19740701.0     1   \n",
      "16                                              Charrues    19680901.0    39   \n",
      "17                                 Charrues à socs fixes    19680901.0    39   \n",
      "18                                              Charrues    19680901.0    39   \n",
      "19                                 Charrues à socs fixes    19680901.0    39   \n",
      "20                           Charrues à traction humaine    19680901.0     1   \n",
      "21                           Charrues à traction animale    19680901.0    10   \n",
      "22     non réversibles, c. à d. incapables de faire u...    19680901.0     5   \n",
      "23                                      Charrues araires    19680901.0     1   \n",
      "24        Charrues à avant-train; Charrues à roue unique    19680901.0     1   \n",
      "25                                 Charrues à deux roues    19680901.0     1   \n",
      "26                                       Charrues à bâti    19680901.0     1   \n",
      "27     Charrues réversibles, c. à d. capables de fair...    19680901.0     4   \n",
      "28                               Charrues tourne-oreille    19680901.0     1   \n",
      "29                                      Charrues bascule    19680901.0     1   \n",
      "...                                                  ...           ...   ...   \n",
      "76877    Fabrication de circuits à âme métallique isolée    19800101.0     1   \n",
      "76878              Fabrication de circuits multi-couches    19800101.0     1   \n",
      "76879  Enveloppes, coffrets ou tiroirs pour appareils...    19680901.0     5   \n",
      "76880                                            Détails    19680901.0     2   \n",
      "76881                                 Couvercles; Capots    19680901.0     1   \n",
      "76882                             Enveloppes métalliques    19680901.0     1   \n",
      "76883                 Enveloppes scellées hermétiquement    19680901.0     1   \n",
      "76884  Détails de construction communs à différents t...    19680901.0    11   \n",
      "76885  Dispositions de composants de circuits ou du c...    19680901.0     6   \n",
      "76886                            sur châssis conducteurs    19680901.0     1   \n",
      "76887                              sur panneaux isolants    19680901.0     2   \n",
      "76888                              sur panneaux perforés    19680901.0     1   \n",
      "76889         Montage de composants à contact par fiches    19680901.0     1   \n",
      "76890  Moyens élastiques ou moyens de serrage pour fi...    19680901.0     1   \n",
      "76891  Montage de la structure de support dans l'enve...    19680901.0     2   \n",
      "76892                       sur charnières ou sur pivots    19680901.0     1   \n",
      "76893                  Structure des bâtis ou des cadres    19680901.0     1   \n",
      "76894  Modifications en vue de faciliter la réfrigéra...    19680901.0     1   \n",
      "76895  Blindage d'appareils ou de composants contre l...    19680901.0     1   \n",
      "76896  Dispositions pour améliorer la sécurité de fon...    19680901.0     1   \n",
      "76897  Combinaisons d'un récepteur de radio ou de tél...    19680901.0     2   \n",
      "76898                                 avec des véhicules    19680901.0     1   \n",
      "76899  Appareils ou procédés spécialement adaptés à l...    19680901.0     5   \n",
      "76900                         Introduction de composants    19680901.0     1   \n",
      "76901                              Montage de composants    19680901.0     1   \n",
      "76902                                Câblage par machine    19680901.0     1   \n",
      "76903           Contrôle de la fabrication des ensembles    19680901.0     1   \n",
      "76904  MATIÈRE NON PRÉVUE AILLEURS DANS LA PRÉSENTE S...      19680901     1   \n",
      "76905  MATIÈRE NON PRÉVUE AILLEURS DANS LA PRÉSENTE S...    20060101.0     1   \n",
      "76906  Matière non prévue ailleurs dans la présente s...    20060101.0     1   \n",
      "\n",
      "       size_percent  \n",
      "0            12.327  \n",
      "1             2.111  \n",
      "2             0.301  \n",
      "3             0.017  \n",
      "4             0.003  \n",
      "5             0.001  \n",
      "6             0.007  \n",
      "7             0.001  \n",
      "8             0.001  \n",
      "9             0.001  \n",
      "10            0.001  \n",
      "11            0.003  \n",
      "12            0.001  \n",
      "13            0.001  \n",
      "14            0.001  \n",
      "15            0.001  \n",
      "16            0.052  \n",
      "17            0.052  \n",
      "18            0.052  \n",
      "19            0.052  \n",
      "20            0.001  \n",
      "21            0.013  \n",
      "22            0.007  \n",
      "23            0.001  \n",
      "24            0.001  \n",
      "25            0.001  \n",
      "26            0.001  \n",
      "27            0.005  \n",
      "28            0.001  \n",
      "29            0.001  \n",
      "...             ...  \n",
      "76877         0.001  \n",
      "76878         0.001  \n",
      "76879         0.007  \n",
      "76880         0.003  \n",
      "76881         0.001  \n",
      "76882         0.001  \n",
      "76883         0.001  \n",
      "76884         0.015  \n",
      "76885         0.008  \n",
      "76886         0.001  \n",
      "76887         0.003  \n",
      "76888         0.001  \n",
      "76889         0.001  \n",
      "76890         0.001  \n",
      "76891         0.003  \n",
      "76892         0.001  \n",
      "76893         0.001  \n",
      "76894         0.001  \n",
      "76895         0.001  \n",
      "76896         0.001  \n",
      "76897         0.003  \n",
      "76898         0.001  \n",
      "76899         0.007  \n",
      "76900         0.001  \n",
      "76901         0.001  \n",
      "76902         0.001  \n",
      "76903         0.001  \n",
      "76904         0.001  \n",
      "76905         0.001  \n",
      "76906         0.001  \n",
      "\n",
      "[76907 rows x 11 columns]>\n"
     ]
    }
   ],
   "source": [
    "conn2 = sqlite3.connect('./patent-classification.db')\n",
    "import_df = pd.read_sql_query(\"SELECT * FROM\" + scheme, conn2)\n",
    "print(import_df.head)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Run"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Choose between cpc or ipc: cpc\n",
      "Overwrite database?(yes/no): yes\n"
     ]
    },
    {
     "ename": "ConnectionError",
     "evalue": "HTTPSConnectionPool(host='www.cooperativepatentclassification.org', port=443): Max retries exceeded with url: /cpc/interleaved/CPCTitleList201908.zip (Caused by NewConnectionError('<urllib3.connection.VerifiedHTTPSConnection object at 0x121ac7240>: Failed to establish a new connection: [Errno 8] nodename nor servname provided, or not known'))",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mgaierror\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/urllib3/connection.py\u001b[0m in \u001b[0;36m_new_conn\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    158\u001b[0m             conn = connection.create_connection(\n\u001b[0;32m--> 159\u001b[0;31m                 (self._dns_host, self.port), self.timeout, **extra_kw)\n\u001b[0m\u001b[1;32m    160\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/urllib3/util/connection.py\u001b[0m in \u001b[0;36mcreate_connection\u001b[0;34m(address, timeout, source_address, socket_options)\u001b[0m\n\u001b[1;32m     56\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 57\u001b[0;31m     \u001b[0;32mfor\u001b[0m \u001b[0mres\u001b[0m \u001b[0;32min\u001b[0m \u001b[0msocket\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgetaddrinfo\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mhost\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mport\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfamily\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msocket\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mSOCK_STREAM\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[0m\u001b[1;32m     58\u001b[0m         \u001b[0maf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msocktype\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mproto\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcanonname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msa\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mres\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/socket.py\u001b[0m in \u001b[0;36mgetaddrinfo\u001b[0;34m(host, port, family, type, proto, flags)\u001b[0m\n\u001b[1;32m    747\u001b[0m     \u001b[0maddrlist\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[0;32m--> 748\u001b[0;31m     \u001b[0;32mfor\u001b[0m \u001b[0mres\u001b[0m \u001b[0;32min\u001b[0m \u001b[0m_socket\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgetaddrinfo\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mhost\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mport\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfamily\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtype\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mproto\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mflags\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[0m\u001b[1;32m    749\u001b[0m         \u001b[0maf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msocktype\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mproto\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcanonname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msa\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mres\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mgaierror\u001b[0m: [Errno 8] nodename nor servname provided, or not known",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mNewConnectionError\u001b[0m                        Traceback (most recent call last)",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/urllib3/connectionpool.py\u001b[0m in \u001b[0;36murlopen\u001b[0;34m(self, method, url, body, headers, retries, redirect, assert_same_host, timeout, pool_timeout, release_conn, chunked, body_pos, **response_kw)\u001b[0m\n\u001b[1;32m    599\u001b[0m                                                   \u001b[0mbody\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mbody\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mheaders\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mheaders\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 600\u001b[0;31m                                                   chunked=chunked)\n\u001b[0m\u001b[1;32m    601\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/urllib3/connectionpool.py\u001b[0m in \u001b[0;36m_make_request\u001b[0;34m(self, conn, method, url, timeout, chunked, **httplib_request_kw)\u001b[0m\n\u001b[1;32m    342\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 343\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_validate_conn\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconn\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    344\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mSocketTimeout\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mBaseSSLError\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/urllib3/connectionpool.py\u001b[0m in \u001b[0;36m_validate_conn\u001b[0;34m(self, conn)\u001b[0m\n\u001b[1;32m    838\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mgetattr\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'sock'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m  \u001b[0;31m# AppEngine might not have  `.sock`\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 839\u001b[0;31m             \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnect\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[0m\u001b[1;32m    840\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/urllib3/connection.py\u001b[0m in \u001b[0;36mconnect\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    300\u001b[0m         \u001b[0;31m# Add certificate verification\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 301\u001b[0;31m         \u001b[0mconn\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_new_conn\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[0m\u001b[1;32m    302\u001b[0m         \u001b[0mhostname\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhost\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/urllib3/connection.py\u001b[0m in \u001b[0;36m_new_conn\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    167\u001b[0m             raise NewConnectionError(\n\u001b[0;32m--> 168\u001b[0;31m                 self, \"Failed to establish a new connection: %s\" % e)\n\u001b[0m\u001b[1;32m    169\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNewConnectionError\u001b[0m: <urllib3.connection.VerifiedHTTPSConnection object at 0x121ac7240>: Failed to establish a new connection: [Errno 8] nodename nor servname provided, or not known",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mMaxRetryError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/requests/adapters.py\u001b[0m in \u001b[0;36msend\u001b[0;34m(self, request, stream, timeout, verify, cert, proxies)\u001b[0m\n\u001b[1;32m    448\u001b[0m                     \u001b[0mretries\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmax_retries\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 449\u001b[0;31m                     \u001b[0mtimeout\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mtimeout\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    450\u001b[0m                 )\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/urllib3/connectionpool.py\u001b[0m in \u001b[0;36murlopen\u001b[0;34m(self, method, url, body, headers, retries, redirect, assert_same_host, timeout, pool_timeout, release_conn, chunked, body_pos, **response_kw)\u001b[0m\n\u001b[1;32m    637\u001b[0m             retries = retries.increment(method, url, error=e, _pool=self,\n\u001b[0;32m--> 638\u001b[0;31m                                         _stacktrace=sys.exc_info()[2])\n\u001b[0m\u001b[1;32m    639\u001b[0m             \u001b[0mretries\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msleep\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[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/urllib3/util/retry.py\u001b[0m in \u001b[0;36mincrement\u001b[0;34m(self, method, url, response, error, _pool, _stacktrace)\u001b[0m\n\u001b[1;32m    397\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mnew_retry\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mis_exhausted\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[0;32m--> 398\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mMaxRetryError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0m_pool\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0murl\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0merror\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mResponseError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcause\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[0m\u001b[1;32m    399\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mMaxRetryError\u001b[0m: HTTPSConnectionPool(host='www.cooperativepatentclassification.org', port=443): Max retries exceeded with url: /cpc/interleaved/CPCTitleList201908.zip (Caused by NewConnectionError('<urllib3.connection.VerifiedHTTPSConnection object at 0x121ac7240>: Failed to establish a new connection: [Errno 8] nodename nor servname provided, or not known'))",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mConnectionError\u001b[0m                           Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-26-bcb43c8ebf00>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     25\u001b[0m \u001b[0;31m# ..add columns for titles\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     26\u001b[0m \u001b[0;31m# takes less than 1 min\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 27\u001b[0;31m \u001b[0mget_titles\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mscheme\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mschemeD\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mengine\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     28\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     29\u001b[0m \u001b[0;31m# ..add columns for creation dates\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m<ipython-input-24-a9a5f438955a>\u001b[0m in \u001b[0;36mget_titles\u001b[0;34m(scheme, schemeD, index, engine)\u001b[0m\n\u001b[1;32m      9\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mscheme\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m'cpc'\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     10\u001b[0m             \u001b[0;32mwith\u001b[0m \u001b[0mtempfile\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mTemporaryDirectory\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mdirectory\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 11\u001b[0;31m                 \u001b[0mr\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mrequests\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mschemeD\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mlanguage\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[0m\u001b[1;32m     12\u001b[0m                 \u001b[0mz\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mzipfile\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mZipFile\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mio\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mBytesIO\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcontent\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     13\u001b[0m                 \u001b[0mz\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mextractall\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdirectory\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/requests/api.py\u001b[0m in \u001b[0;36mget\u001b[0;34m(url, params, **kwargs)\u001b[0m\n\u001b[1;32m     73\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     74\u001b[0m     \u001b[0mkwargs\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msetdefault\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'allow_redirects'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 75\u001b[0;31m     \u001b[0;32mreturn\u001b[0m \u001b[0mrequest\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'get'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0murl\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\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     76\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     77\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/requests/api.py\u001b[0m in \u001b[0;36mrequest\u001b[0;34m(method, url, **kwargs)\u001b[0m\n\u001b[1;32m     58\u001b[0m     \u001b[0;31m# cases, and look like a memory leak in others.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     59\u001b[0m     \u001b[0;32mwith\u001b[0m \u001b[0msessions\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mSession\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0msession\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 60\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrequest\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmethod\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0murl\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0murl\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\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     61\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     62\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/requests/sessions.py\u001b[0m in \u001b[0;36mrequest\u001b[0;34m(self, method, url, params, data, headers, cookies, files, auth, timeout, allow_redirects, proxies, hooks, stream, verify, cert, json)\u001b[0m\n\u001b[1;32m    531\u001b[0m         }\n\u001b[1;32m    532\u001b[0m         \u001b[0msend_kwargs\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mupdate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msettings\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 533\u001b[0;31m         \u001b[0mresp\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mprep\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0msend_kwargs\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    534\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    535\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mresp\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/requests/sessions.py\u001b[0m in \u001b[0;36msend\u001b[0;34m(self, request, **kwargs)\u001b[0m\n\u001b[1;32m    644\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    645\u001b[0m         \u001b[0;31m# Send the request\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 646\u001b[0;31m         \u001b[0mr\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0madapter\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrequest\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\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    647\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    648\u001b[0m         \u001b[0;31m# Total elapsed time of the request (approximately)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/requests/adapters.py\u001b[0m in \u001b[0;36msend\u001b[0;34m(self, request, stream, timeout, verify, cert, proxies)\u001b[0m\n\u001b[1;32m    514\u001b[0m                 \u001b[0;32mraise\u001b[0m \u001b[0mSSLError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrequest\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mrequest\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    515\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 516\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mConnectionError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrequest\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mrequest\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    517\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    518\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mClosedPoolError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mConnectionError\u001b[0m: HTTPSConnectionPool(host='www.cooperativepatentclassification.org', port=443): Max retries exceeded with url: /cpc/interleaved/CPCTitleList201908.zip (Caused by NewConnectionError('<urllib3.connection.VerifiedHTTPSConnection object at 0x121ac7240>: Failed to establish a new connection: [Errno 8] nodename nor servname provided, or not known'))"
     ]
    }
   ],
   "source": [
    "import time\n",
    "scheme = input('Choose between cpc or ipc: ').lower()\n",
    "to_sql = input('Overwrite database?(yes/no): ').lower()\n",
    "\n",
    "\n",
    "# df with symbols, kinds and direct parents..\n",
    "# takes approximately 15 min\n",
    "#importer(scheme, fileD, schemaL, engine, conn, to_sql)\n",
    "\n",
    "# ..add column for levels\n",
    "# takes less than 1 min\n",
    "#add_levels(scheme, engine, to_sql)\n",
    "\n",
    "# ..add column for short symbols\n",
    "# takes less than 1 min\n",
    "#add_symbols_short(scheme, engine, to_sql)\n",
    "\n",
    "if scheme == 'ipc':\n",
    "    schemeD = {'en' : fileD['ipc_en'], 'fr': fileD['ipc_fr']}\n",
    "    index = 'symbol'\n",
    "elif scheme == 'cpc':\n",
    "    schemeD = {'en' : fileD['cpc_en']}\n",
    "    index = 'symbol_short'\n",
    "\n",
    "# ..add columns for titles\n",
    "# takes less than 1 min\n",
    "get_titles(scheme, schemeD, index, engine)\n",
    "\n",
    "# ..add columns for creation dates\n",
    "# takes less than 1 min\n",
    "#pc_evolution(scheme, index, engine, fileD, to_sql)\n",
    "\n",
    "# ..add columns for sizes i.e. number of groups contained (in % and normalised)\n",
    "# takes approximately 30 min\n",
    "#pathsL = get_paths(scheme, engine)\n",
    "# takes less than 1 min\n",
    "#pc_statistics(pathsL, scheme, engine, to_sql)\n",
    "\n",
    "# from postgresql to sqlite\n",
    "#postgresql_to_sqlite(scheme, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Colors for branches"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1968, 1968, 1974, 1980, 1985, 1990, 1995, 2000, 2006, 2007, 2007, 2008, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019]\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "creation_date = df['creation_date'].unique().tolist()\n",
    "creation_year = [date[:4] for date in creation_date]\n",
    "creation_yearL = sorted(list(map(int, creation_year )))\n",
    "print(creation_yearL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-------------------------------- 19680901\n",
      "(139, 11)\n",
      "     symbol kind parent  level symbol_short parent_short  \\\n",
      "0         A    s    IPC      2            A          IPC   \n",
      "1       A01    c      A      3          A01            A   \n",
      "1680    A21    c      A      3          A21            A   \n",
      "1846    A22    c      A      3          A22            A   \n",
      "1919    A23    c      A      3          A23            A   \n",
      "2717    A24    c      A      3          A24            A   \n",
      "2972    A41    c      A      3          A41            A   \n",
      "3235    A42    c      A      3          A42            A   \n",
      "3282    A43    c      A      3          A43            A   \n",
      "3697    A44    c      A      3          A44            A   \n",
      "\n",
      "                                               title_en  \\\n",
      "0                                     HUMAN NECESSITIES   \n",
      "1     AGRICULTURE; FORESTRY; ANIMAL HUSBANDRY; HUNTI...   \n",
      "1680  BAKING; EQUIPMENT FOR MAKING OR PROCESSING DOU...   \n",
      "1846  BUTCHERING; MEAT TREATMENT; PROCESSING POULTRY...   \n",
      "1919  FOODS OR FOODSTUFFS; THEIR TREATMENT, NOT COVE...   \n",
      "2717   TOBACCO; CIGARS; CIGARETTES; SMOKERS' REQUISITES   \n",
      "2972                                    WEARING APPAREL   \n",
      "3235                                           HEADWEAR   \n",
      "3282                                           FOOTWEAR   \n",
      "3697                            HABERDASHERY; JEWELLERY   \n",
      "\n",
      "                                               title_fr creation_date  size  \\\n",
      "0                        NÉCESSITÉS COURANTES DE LA VIE      19680901  9184   \n",
      "1     AGRICULTURE; SYLVICULTURE; ÉLEVAGE; CHASSE; PI...      19680901  1573   \n",
      "1680  CUISSON AU FOUR; MATÉRIEL POUR LA PRÉPARATION ...      19680901   166   \n",
      "1846  BOUCHERIE; TRAITEMENT DE LA VIANDE; TRAITEMENT...      19680901    63   \n",
      "1919  ALIMENTS OU PRODUITS ALIMENTAIRES; LEUR TRAITE...      19680901   777   \n",
      "2717  TABAC; CIGARES; CIGARETTES; ARTICLES POUR FUMEURS      19680901   237   \n",
      "2972                                          VÊTEMENTS      19680901   253   \n",
      "3235                                        CHAPELLERIE      19680901    45   \n",
      "3282                                         CHAUSSURES      19680901   386   \n",
      "3697                               MERCERIE; BIJOUTERIE      19680901   128   \n",
      "\n",
      "      size_percent  \n",
      "0           12.327  \n",
      "1            2.111  \n",
      "1680         0.223  \n",
      "1846         0.085  \n",
      "1919         1.043  \n",
      "2717         0.318  \n",
      "2972         0.340  \n",
      "3235         0.060  \n",
      "3282         0.518  \n",
      "3697         0.172  \n",
      "-------------------------------- 19680901.0\n",
      "(40501, 11)\n",
      "            symbol kind          parent  level symbol_short parent_short  \\\n",
      "2             A01B    u             A01      4         A01B          A01   \n",
      "3   A01B0001000000    m            A01B      5     A01B1/00         A01B   \n",
      "4   A01B0001020000    1  A01B0001000000      6     A01B1/02     A01B1/00   \n",
      "5   A01B0001040000    2  A01B0001020000      7     A01B1/04     A01B1/02   \n",
      "6   A01B0001060000    1  A01B0001000000      6     A01B1/06     A01B1/00   \n",
      "7   A01B0001080000    2  A01B0001060000      7     A01B1/08     A01B1/06   \n",
      "8   A01B0001100000    2  A01B0001060000      7      A01B1/1     A01B1/06   \n",
      "9   A01B0001120000    2  A01B0001060000      7     A01B1/12     A01B1/06   \n",
      "10  A01B0001140000    2  A01B0001060000      7     A01B1/14     A01B1/06   \n",
      "11  A01B0001160000    1  A01B0001000000      6     A01B1/16     A01B1/00   \n",
      "\n",
      "                                             title_en  \\\n",
      "2   SOIL WORKING IN AGRICULTURE OR FORESTRY; PARTS...   \n",
      "3   Hand tools (edge trimmers for lawns A01G000306...   \n",
      "4                                     Spades; Shovels   \n",
      "5                                          with teeth   \n",
      "6                              Hoes; Hand cultivators   \n",
      "7                                 with a single blade   \n",
      "8                             with two or more blades   \n",
      "9                     with blades provided with teeth   \n",
      "10                                    with teeth only   \n",
      "11                          Tools for uprooting weeds   \n",
      "\n",
      "                                             title_fr creation_date  size  \\\n",
      "2   TRAVAIL DE LA TERRE EN AGRICULTURE OU EN SYLVI...    19680901.0   224   \n",
      "3   Outils à main (tranche-bordures pour pelouses ...    19680901.0    13   \n",
      "4                                      Bêches; Pelles    19680901.0     2   \n",
      "5                                           dentelées    19680901.0     1   \n",
      "6                              Houes; Bineuses à main    19680901.0     5   \n",
      "7                                       à lame unique    19680901.0     1   \n",
      "8                                   à plusieurs lames    19680901.0     1   \n",
      "9                                   à lames dentelées    19680901.0     1   \n",
      "10                                 à dents uniquement    19680901.0     1   \n",
      "11                                          Sarcloirs    19680901.0     2   \n",
      "\n",
      "    size_percent  \n",
      "2          0.301  \n",
      "3          0.017  \n",
      "4          0.003  \n",
      "5          0.001  \n",
      "6          0.007  \n",
      "7          0.001  \n",
      "8          0.001  \n",
      "9          0.001  \n",
      "10         0.001  \n",
      "11         0.003  \n",
      "-------------------------------- 19740701.0\n",
      "(6337, 11)\n",
      "             symbol kind          parent  level symbol_short parent_short  \\\n",
      "15   A01B0001240000    1  A01B0001000000      6     A01B1/24     A01B1/00   \n",
      "41   A01B0003421000    4  A01B0003420000      9    A01B3/421     A01B3/42   \n",
      "42   A01B0003426000    4  A01B0003420000      9    A01B3/426     A01B3/42   \n",
      "183  A01B0059041000    2  A01B0059040000      7   A01B59/041    A01B59/04   \n",
      "252  A01C0003080000    2  A01C0003060000      7     A01C3/08     A01C3/06   \n",
      "471  A01D0041140000    2  A01D0041120000      7    A01D41/14    A01D41/12   \n",
      "490  A01D0044000000    m            A01D      5    A01D44/00         A01D   \n",
      "491  A01D0044020000    1  A01D0044000000      6    A01D44/02    A01D44/00   \n",
      "503  A01D0046000000    m            A01D      5    A01D46/00         A01D   \n",
      "504  A01D0046020000    1  A01D0046000000      6    A01D46/02    A01D46/00   \n",
      "\n",
      "                                              title_en  \\\n",
      "15                       for treating meadows or lawns   \n",
      "41            with a headstock frame made in one piece   \n",
      "42    with a headstock frame made of two or more parts   \n",
      "183  preventing or limiting side-play of implements...   \n",
      "252              for manure already laying on the soil   \n",
      "471                                      Mowing tables   \n",
      "490  Harvesting of underwater plants, e.g. harvesti...   \n",
      "491                                           of laver   \n",
      "503  Picking of fruits, vegetables, hops, or the li...   \n",
      "504                                            of hops   \n",
      "\n",
      "                                              title_fr creation_date  size  \\\n",
      "15          pour le traitement de prairies ou pelouses    19740701.0     1   \n",
      "41          avec support d'attelage en une seule pièce    19740701.0     1   \n",
      "42        avec support d'attelage en plusieurs parties    19740701.0     1   \n",
      "183  avec prévention ou limitation du jeu latéral d...    19740701.0     1   \n",
      "252     pour engrais de ferme déjà disposés sur le sol    19740701.0     1   \n",
      "471                                    Tables de coupe    19740701.0     2   \n",
      "490  Récolte de la flore subaquatique, p.ex. récolt...    19740701.0     2   \n",
      "491                             des algues comestibles    19740701.0     1   \n",
      "503  Cueillette de fruits, légumes, houblon ou de p...    19740701.0    18   \n",
      "504                                         du houblon    19740701.0     1   \n",
      "\n",
      "     size_percent  \n",
      "15          0.001  \n",
      "41          0.001  \n",
      "42          0.001  \n",
      "183         0.001  \n",
      "252         0.001  \n",
      "471         0.003  \n",
      "490         0.003  \n",
      "491         0.001  \n",
      "503         0.024  \n",
      "504         0.001  \n",
      "-------------------------------- 19850101.0\n",
      "(4268, 11)\n",
      "             symbol kind          parent  level symbol_short parent_short  \\\n",
      "232  A01B0073020000    1  A01B0073000000      6    A01B73/02    A01B73/00   \n",
      "233  A01B0073040000    2  A01B0073020000      7    A01B73/04    A01B73/02   \n",
      "234  A01B0073060000    2  A01B0073020000      7    A01B73/06    A01B73/02   \n",
      "289  A01C0019020000    1  A01C0019000000      6    A01C19/02    A01C19/00   \n",
      "290  A01C0019040000    1  A01C0019000000      6    A01C19/04    A01C19/00   \n",
      "380  A01D0034000000    m            A01D      5    A01D34/00         A01D   \n",
      "383  A01D0034000000    m            A01D      5    A01D34/00         A01D   \n",
      "384  A01D0034000000    m            A01D      5    A01D34/00         A01D   \n",
      "385  A01D0034000000    m            A01D      5    A01D34/00         A01D   \n",
      "387  A01D0034020000    2  A01D0034010000      7    A01D34/02    A01D34/01   \n",
      "\n",
      "                                              title_en  \\\n",
      "232                                     Folding frames   \n",
      "233                   foldable about a horizontal axis   \n",
      "234                     foldable about a vertical axis   \n",
      "289                                         by a motor   \n",
      "290                         by a ground-engaging wheel   \n",
      "380  Harvesters or mowers for grass, cereals or oth...   \n",
      "383  Harvesters or mowers for grass, cereals or oth...   \n",
      "384  Mowers (combined with apparatus performing add...   \n",
      "385  Mowers (combined with apparatus performing add...   \n",
      "387                       having reciprocating cutters   \n",
      "\n",
      "                                              title_fr creation_date  size  \\\n",
      "232                                      Bâtis pliants    19850101.0     3   \n",
      "233              repliables autour d'un axe horizontal    19850101.0     1   \n",
      "234                repliables autour d'un axe vertical    19850101.0     1   \n",
      "289                               à l'aide d'un moteur    19850101.0     1   \n",
      "290           à l'aide d'une roue en prise avec le sol    19850101.0     1   \n",
      "380  Moissonneuses ou faucheuses pour herbages, cér...    19850101.0    72   \n",
      "383  Faucheuses (combinées avec des appareils perme...    19850101.0    72   \n",
      "384  Moissonneuses ou faucheuses pour herbages, cér...    19850101.0    72   \n",
      "385  Faucheuses (combinées avec des appareils perme...    19850101.0    72   \n",
      "387     comportant des couteaux à mouvement alternatif    19850101.0    29   \n",
      "\n",
      "     size_percent  \n",
      "232         0.004  \n",
      "233         0.001  \n",
      "234         0.001  \n",
      "289         0.001  \n",
      "290         0.001  \n",
      "380         0.097  \n",
      "383         0.097  \n",
      "384         0.097  \n",
      "385         0.097  \n",
      "387         0.039  \n",
      "-------------------------------- 20060101.0\n",
      "(1316, 11)\n",
      "              symbol kind parent  level symbol_short parent_short  \\\n",
      "236   A01B0076000000    m   A01B      5    A01B76/00         A01B   \n",
      "276   A01C0014000000    m   A01C      5    A01C14/00         A01C   \n",
      "1056  A01J0099000000    m   A01J      5    A01J99/00         A01J   \n",
      "1299  A01K0099000000    m   A01K      5    A01K99/00         A01K   \n",
      "1394  A01M0099000000    m   A01M      5    A01M99/00         A01M   \n",
      "1664            A01P    u    A01      4         A01P          A01   \n",
      "1665  A01P0001000000    m   A01P      5     A01P1/00         A01P   \n",
      "1666  A01P0003000000    m   A01P      5     A01P3/00         A01P   \n",
      "1667  A01P0005000000    m   A01P      5     A01P5/00         A01P   \n",
      "1668  A01P0007000000    m   A01P      5     A01P7/00         A01P   \n",
      "\n",
      "                                               title_en  \\\n",
      "236   Parts, details or accessories of agricultural ...   \n",
      "276   Methods or apparatus for planting not provided...   \n",
      "1056  Subject matter not provided for in other group...   \n",
      "1299  Methods or apparatus for fishing not provided ...   \n",
      "1394  Subject matter not provided for in other group...   \n",
      "1664  BIOCIDAL, PEST REPELLANT, PEST ATTRACTANT OR P...   \n",
      "1665  Disinfectants; Antimicrobial compounds or mixt...   \n",
      "1666                                         Fungicides   \n",
      "1667                                        Nematocides   \n",
      "1668                                    Arthropodicides   \n",
      "\n",
      "                                               title_fr creation_date  size  \\\n",
      "236   Parties constitutives ou accessoires des machi...    20060101.0     1   \n",
      "276   Procédés ou appareils pour la plantation non p...    20060101.0     1   \n",
      "1056  Matière non prévue dans les autres groupes de ...    20060101.0     1   \n",
      "1299  Procédés ou appareils pour la pêche non prévus...    20060101.0     1   \n",
      "1394  Matière non prévue dans les autres groupes de ...    20060101.0     1   \n",
      "1664  ACTIVITÉ BIOCIDE, ACTIVITÉ DE RÉPULSION OU D'A...    20060101.0    15   \n",
      "1665  Désinfectants; Composés antimicrobiens ou leur...    20060101.0     1   \n",
      "1666                                         Fongicides    20060101.0     1   \n",
      "1667                                        Nématicides    20060101.0     1   \n",
      "1668                                    Arthropodicides    20060101.0     3   \n",
      "\n",
      "      size_percent  \n",
      "236          0.001  \n",
      "276          0.001  \n",
      "1056         0.001  \n",
      "1299         0.001  \n",
      "1394         0.001  \n",
      "1664         0.020  \n",
      "1665         0.001  \n",
      "1666         0.001  \n",
      "1667         0.001  \n",
      "1668         0.004  \n",
      "-------------------------------- 20000101.0\n",
      "(1679, 11)\n",
      "             symbol kind          parent  level symbol_short parent_short  \\\n",
      "381  A01D0034408000    3  A01D0034404000      8   A01D34/408   A01D34/404   \n",
      "386  A01D0034010000    1  A01D0034000000      6    A01D34/01    A01D34/00   \n",
      "394  A01D0034125000    3  A01D0034020000      8   A01D34/125    A01D34/02   \n",
      "396  A01D0034135000    4  A01D0034130000      9   A01D34/135    A01D34/13   \n",
      "416  A01D0034404000    2  A01D0034010000      7   A01D34/404    A01D34/01   \n",
      "417  A01D0034412000    2  A01D0034010000      7   A01D34/412    A01D34/01   \n",
      "418  A01D0034416000    3  A01D0034412000      8   A01D34/416   A01D34/412   \n",
      "424  A01D0034490000    4  A01D0034420000      9    A01D34/49    A01D34/42   \n",
      "427  A01D0034535000    5  A01D0034520000     10   A01D34/535    A01D34/52   \n",
      "439  A01D0034685000    6  A01D0034680000     11   A01D34/685    A01D34/68   \n",
      "\n",
      "                                              title_en  \\\n",
      "381               and cooperating with counter-cutters   \n",
      "386  characterised by features relating to the type...   \n",
      "394  with means for discharging mown material (A01D...   \n",
      "396   having oppositely movable cooperating knife-bars   \n",
      "416  having cutters driven to oscillate in a horizo...   \n",
      "417                            having rotating cutters   \n",
      "418                              Flexible line cutters   \n",
      "424  with means for discharging mown material (A01D...   \n",
      "427  with cutting members pivotally attached to the...   \n",
      "439                           with two or more cutters   \n",
      "\n",
      "                                              title_fr creation_date  size  \\\n",
      "381                 et coopérant avec des contre-lames    20000101.0     1   \n",
      "386  caractérisés par des particularités relatives ...    20000101.0    67   \n",
      "394  avec des moyens pour décharger les produits fa...    20000101.0     1   \n",
      "396  comportant des tringles porte-lames à mouvemen...    20000101.0     1   \n",
      "416  comportant des couteaux entraînés pour oscille...    20000101.0     2   \n",
      "417                   comportant des couteaux rotatifs    20000101.0    34   \n",
      "418                       sous forme de fils flexibles    20000101.0     1   \n",
      "424  avec des moyens pour décharger les produits fa...    20000101.0     1   \n",
      "427  avec des organes coupants reliés de façon pivo...    20000101.0     1   \n",
      "439                               à plusieurs couteaux    20000101.0     1   \n",
      "\n",
      "     size_percent  \n",
      "381         0.001  \n",
      "386         0.090  \n",
      "394         0.001  \n",
      "396         0.001  \n",
      "416         0.003  \n",
      "417         0.046  \n",
      "418         0.001  \n",
      "424         0.001  \n",
      "427         0.001  \n",
      "439         0.001  \n",
      "-------------------------------- 19950101.0\n",
      "(2860, 11)\n",
      "             symbol kind          parent  level symbol_short parent_short  \\\n",
      "382  A61K0103200000    1  A61K0103000000      6    A61K103/2   A61K103/00   \n",
      "605  A01D0082020000    1  A01D0082000000      6    A01D82/02    A01D82/00   \n",
      "607  A01D0084020000    1  A01D0084000000      6    A01D84/02    A01D84/00   \n",
      "630  A01D0101000000    m            A01D      5   A01D101/00         A01D   \n",
      "632  A01D0101000000    m            A01D      5   A01D101/00         A01D   \n",
      "633  A01D0101000000    m            A01D      5   A01D101/00         A01D   \n",
      "634  A01D0101000000    m            A01D      5   A01D101/00         A01D   \n",
      "743  A01G0003025000    2  A01G0003020000      7    A01G3/025     A01G3/02   \n",
      "744  A01G0003033000    2  A01G0003020000      7    A01G3/033     A01G3/02   \n",
      "745  A01G0003037000    3  A01G0003033000      8    A01G3/037    A01G3/033   \n",
      "\n",
      "                                              title_en  \\\n",
      "382                                             Indium   \n",
      "605                      Rollers for crop conditioners   \n",
      "607                                with flexible tools   \n",
      "630  Indexing scheme associated with group A01D0034...   \n",
      "632  Indexing scheme associated with group A01D0034...   \n",
      "633                                        Lawn-mowers   \n",
      "634                                        Lawn-mowers   \n",
      "743               having elongated or extended handles   \n",
      "744                         having motor-driven blades   \n",
      "745          the driving means being an electric motor   \n",
      "\n",
      "                                              title_fr creation_date  size  \\\n",
      "382                                             Indium    19950101.0     1   \n",
      "605  Rouleaux pour machines de traitement de la réc...    19950101.0     1   \n",
      "607                          avec des outils flexibles    19950101.0     1   \n",
      "630  Schéma d'indexation associé au groupe A01D0034...    19950101.0     1   \n",
      "632                                  Tondeuses à gazon    19950101.0     1   \n",
      "633  Schéma d'indexation associé au groupe A01D0034...    19950101.0     1   \n",
      "634                                  Tondeuses à gazon    19950101.0     1   \n",
      "743          avec des poignées allongées ou prolongées    19950101.0     1   \n",
      "744            avec des lames entraînées par un moteur    19950101.0     2   \n",
      "745  le moyen d'entraînement étant un moteur électr...    19950101.0     1   \n",
      "\n",
      "     size_percent  \n",
      "382         0.001  \n",
      "605         0.001  \n",
      "607         0.001  \n",
      "630         0.001  \n",
      "632         0.001  \n",
      "633         0.001  \n",
      "634         0.001  \n",
      "743         0.001  \n",
      "744         0.003  \n",
      "745         0.001  \n",
      "-------------------------------- 19800101.0\n",
      "(5659, 11)\n",
      "             symbol kind          parent  level symbol_short parent_short  \\\n",
      "518  A01D0046260000    1  A01D0046000000      6    A01D46/26    A01D46/00   \n",
      "519  A01D0046280000    1  A01D0046000000      6    A01D46/28    A01D46/00   \n",
      "529  A01D0057030000    3  A01D0057020000      8    A01D57/03    A01D57/02   \n",
      "587  A01D0076000000    m            A01D      5    A01D76/00         A01D   \n",
      "588  A01D0076000000    m            A01D      5    A01D76/00         A01D   \n",
      "589  A01D0076000000    m            A01D      5    A01D76/00         A01D   \n",
      "590  A01D0076000000    m            A01D      5    A01D76/00         A01D   \n",
      "591  A01D0078000000    m            A01D      5    A01D78/00         A01D   \n",
      "592  A01D0078020000    1  A01D0078000000      6    A01D78/02    A01D78/00   \n",
      "593  A01D0078040000    2  A01D0078020000      7    A01D78/04    A01D78/02   \n",
      "\n",
      "                                              title_en  \\\n",
      "518  Devices for shaking trees or shrubs; Fruit cat...   \n",
      "519  Vintaging machines, i.e. grape harvesting mach...   \n",
      "529  with supplementary controlled movement of the ...   \n",
      "587                       Haymakers; Crop conditioners   \n",
      "588                       Haymakers; Crop conditioners   \n",
      "589  Haymakers with tines that are stationary with ...   \n",
      "590  Haymakers with tines that are stationary with ...   \n",
      "591  Haymakers with tines moving with respect to th...   \n",
      "592  with tine-carrying bars or equivalent members ...   \n",
      "593  the tine-carrying members moving obliquely or ...   \n",
      "\n",
      "                                              title_fr creation_date  size  \\\n",
      "518  Dispositifs pour secouer les arbres ou les arb...    19800101.0     1   \n",
      "519                               Machines à vendanger    19800101.0     1   \n",
      "529  comportant un mouvement commandé supplémentair...    19800101.0     1   \n",
      "587     Faneuses; Machines de traitement de la récolte    19800101.0     1   \n",
      "588  Faneuses à dents qui restent fixes par rapport...    19800101.0     1   \n",
      "589     Faneuses; Machines de traitement de la récolte    19800101.0     1   \n",
      "590  Faneuses à dents qui restent fixes par rapport...    19800101.0     1   \n",
      "591  Faneuses à dents mobiles par rapport à la mach...    19800101.0    11   \n",
      "592  à barres porte-dents ou organes équivalents qu...    19800101.0     2   \n",
      "593  les organes porte-dents se déplaçant obliqueme...    19800101.0     1   \n",
      "\n",
      "     size_percent  \n",
      "518         0.001  \n",
      "519         0.001  \n",
      "529         0.001  \n",
      "587         0.001  \n",
      "588         0.001  \n",
      "589         0.001  \n",
      "590         0.001  \n",
      "591         0.015  \n",
      "592         0.003  \n",
      "593         0.001  \n",
      "-------------------------------- 20090101.0\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(291, 11)\n",
      "              symbol kind          parent  level symbol_short parent_short  \\\n",
      "629   A01D0093000000    m            A01D      5    A01D93/00         A01D   \n",
      "1639  A01N0065030000    1  A01N0065000000      6    A01N65/03    A01N65/00   \n",
      "1640  A01N0065040000    1  A01N0065000000      6    A01N65/04    A01N65/00   \n",
      "1641  A01N0065060000    1  A01N0065000000      6    A01N65/06    A01N65/00   \n",
      "1642  A01N0065080000    1  A01N0065000000      6    A01N65/08    A01N65/00   \n",
      "1643  A01N0065100000    2  A01N0065080000      7     A01N65/1    A01N65/08   \n",
      "1644  A01N0065120000    2  A01N0065080000      7    A01N65/12    A01N65/08   \n",
      "1645  A01N0065140000    2  A01N0065080000      7    A01N65/14    A01N65/08   \n",
      "1646  A01N0065160000    2  A01N0065080000      7    A01N65/16    A01N65/08   \n",
      "1647  A01N0065180000    2  A01N0065080000      7    A01N65/18    A01N65/08   \n",
      "\n",
      "                                               title_en  \\\n",
      "629   Harvesting apparatus not provided for in other...   \n",
      "1639                                              Algae   \n",
      "1640    Pteridophyta [fern allies]; Filicophyta [ferns]   \n",
      "1641          Coniferophyta [gymnosperms], e.g. cypress   \n",
      "1642                       Magnoliopsida [dicotyledons]   \n",
      "1643  Apiaceae or Umbelliferae [Carrot family], e.g....   \n",
      "1644  Asteraceae or Compositae [Aster or Sunflower f...   \n",
      "1645  Celastraceae [Staff-tree or Bittersweet family...   \n",
      "1646  Ericaceae [Heath or Blueberry family], e.g. rh...   \n",
      "1647  Euphorbiaceae [Spurge family], e.g. ricinus [c...   \n",
      "\n",
      "                                               title_fr creation_date  size  \\\n",
      "629   Appareils pour la récolte non prévus dans les ...    20090101.0     1   \n",
      "1639                                             Algues    20090101.0     1   \n",
      "1640  Pteridophyta [fougères et apparentés]; Filicop...    20090101.0     1   \n",
      "1641         Coniferophyta [gymnospermes], p.ex. cyprès    20090101.0     1   \n",
      "1642                      Magnoliopsida [dicotylédones]    20090101.0    16   \n",
      "1643  Apiaceae ou Umbelliferae [famille de la carott...    20090101.0     1   \n",
      "1644  Asteraceae ou Compositae [famille de l'aster o...    20090101.0     1   \n",
      "1645  Celastraceae [famille du bourreau des arbres o...    20090101.0     1   \n",
      "1646  Ericaceae [famille de la bruyère ou de la myrt...    20090101.0     1   \n",
      "1647  Euphorbiaceae [famille de l'euphorbe], p.ex. R...    20090101.0     1   \n",
      "\n",
      "      size_percent  \n",
      "629          0.001  \n",
      "1639         0.001  \n",
      "1640         0.001  \n",
      "1641         0.001  \n",
      "1642         0.021  \n",
      "1643         0.001  \n",
      "1644         0.001  \n",
      "1645         0.001  \n",
      "1646         0.001  \n",
      "1647         0.001  \n",
      "-------------------------------- 20100101.0\n",
      "(402, 11)\n",
      "               symbol kind          parent  level symbol_short parent_short  \\\n",
      "649    A01F0007700000    1  A01F0007000000      6      A01F7/7     A01F7/00   \n",
      "726    A01F0029090000    1  A01F0029000000      6    A01F29/09    A01F29/00   \n",
      "3795   A44B0099000000    m            A44B      5    A44B99/00         A44B   \n",
      "8527   A62C0099000000    m            A62C      5    A62C99/00         A62C   \n",
      "10775  B01L0007040000    1  B01L0007000000      6     B01L7/04     B01L7/00   \n",
      "10780  B01L0099000000    m            B01L      5    B01L99/00         B01L   \n",
      "14829  B24D0099000000    m            B24D      5    B24D99/00         B24D   \n",
      "16907  B29D0033000000    m            B29D      5    B29D33/00         B29D   \n",
      "16908  B29D0035000000    m            B29D      5    B29D35/00         B29D   \n",
      "16909  B29D0035020000    1  B29D0035000000      6    B29D35/02    B29D35/00   \n",
      "\n",
      "                                                title_en  \\\n",
      "649                                          with flails   \n",
      "726                                              Details   \n",
      "3795   Subject matter not provided for in other group...   \n",
      "8527   Subject matter not provided for in other group...   \n",
      "10775   Heat insulating devices, e.g. jackets for flasks   \n",
      "10780  Subject matter not provided for in other group...   \n",
      "14829  Subject matter not provided for in other group...   \n",
      "16907                      Producing bushes for bearings   \n",
      "16908                                 Producing footwear   \n",
      "16909  made in one piece using a moulding technique, ...   \n",
      "\n",
      "                                                title_fr creation_date  size  \\\n",
      "649                                      avec des fléaux    20100101.0     1   \n",
      "726                                Parties constitutives    20100101.0     7   \n",
      "3795   Matière non prévue dans les autres groupes de ...    20100101.0     1   \n",
      "8527   Matière non prévue dans les autres groupes de ...    20100101.0     1   \n",
      "10775  Dispositifs d'isolation thermique, p.ex. manch...    20100101.0     1   \n",
      "10780  Matière non prévue dans les autres groupes de ...    20100101.0     1   \n",
      "14829  Matière non prévue dans les autres groupes de ...    20100101.0     1   \n",
      "16907                   Fabrication de bagues de paliers    20100101.0     1   \n",
      "16908                          Fabrication de chaussures    20100101.0     8   \n",
      "16909  fabriquées d'une seule pièce en utilisant une ...    20100101.0     2   \n",
      "\n",
      "       size_percent  \n",
      "649           0.001  \n",
      "726           0.009  \n",
      "3795          0.001  \n",
      "8527          0.001  \n",
      "10775         0.001  \n",
      "10780         0.001  \n",
      "14829         0.001  \n",
      "16907         0.001  \n",
      "16908         0.011  \n",
      "16909         0.003  \n",
      "-------------------------------- 19900101.0\n",
      "(7180, 11)\n",
      "             symbol kind          parent  level symbol_short parent_short  \\\n",
      "671  A01F0012385000    2  A01F0012300000      7   A01F12/385     A01F12/3   \n",
      "672  A01F0012390000    2  A01F0012300000      7    A01F12/39     A01F12/3   \n",
      "673  A01F0012395000    2  A01F0012300000      7   A01F12/395     A01F12/3   \n",
      "693  A01F0015070000    1  A01F0015000000      6    A01F15/07    A01F15/00   \n",
      "843  A01G0023081000    3  A01G0023080000      8   A01G23/081    A01G23/08   \n",
      "844  A01G0023083000    3  A01G0023080000      8   A01G23/083    A01G23/08   \n",
      "845  A01G0023085000    4  A01G0023083000      9   A01G23/085   A01G23/083   \n",
      "846  A01G0023087000    3  A01G0023080000      8   A01G23/087    A01G23/08   \n",
      "847  A01G0023089000    4  A01G0023087000      9   A01G23/089   A01G23/087   \n",
      "848  A01G0023090000    4  A01G0023087000      9    A01G23/09   A01G23/087   \n",
      "\n",
      "                                              title_en  \\\n",
      "671               with endless straw-conveying surface   \n",
      "672  with straw carriers in the form of rotors or d...   \n",
      "673  Conical or cylindrical straw separators with i...   \n",
      "693  Rotobalers, i.e. machines for forming cylindri...   \n",
      "843  Feller-bunchers, i.e. with bunching by felling...   \n",
      "844                                   Feller-delimbers   \n",
      "845  having the shearing head mounted on a first bo...   \n",
      "846  Shearing apparatus or the like specially adapt...   \n",
      "847                          having two or more shears   \n",
      "848                             of the percussion type   \n",
      "\n",
      "                                              title_fr creation_date  size  \\\n",
      "671  avec une surface sans fin de transport de la p...    19900101.0     1   \n",
      "672  avec des transporteurs de la paille en forme d...    19900101.0     1   \n",
      "673  Séparateurs de paille coniques ou cylindriques...    19900101.0     1   \n",
      "693  Presses enrouleuses, c. à d. machines pour for...    19900101.0     1   \n",
      "843  Abatteuses-botteleuses, c. à d. avec bottelage...    19900101.0     1   \n",
      "844                            Abatteuses-ébrancheuses    19900101.0     2   \n",
      "845  avec la tête de cisaillement montée sur une pr...    19900101.0     1   \n",
      "846  Appareils de cisaillement ou analogues spécial...    19900101.0     3   \n",
      "847                           avec plusieurs cisailles    19900101.0     1   \n",
      "848                               du type à percussion    19900101.0     1   \n",
      "\n",
      "     size_percent  \n",
      "671         0.001  \n",
      "672         0.001  \n",
      "673         0.001  \n",
      "693         0.001  \n",
      "843         0.001  \n",
      "844         0.003  \n",
      "845         0.001  \n",
      "846         0.004  \n",
      "847         0.001  \n",
      "848         0.001  \n",
      "-------------------------------- 20180101.0\n",
      "(1046, 11)\n",
      "             symbol kind          parent  level symbol_short parent_short  \\\n",
      "734  A01G0002000000    m            A01G      5     A01G2/00         A01G   \n",
      "735  A01G0002100000    1  A01G0002000000      6      A01G2/1     A01G2/00   \n",
      "736  A01G0002200000    1  A01G0002000000      6      A01G2/2     A01G2/00   \n",
      "737  A01G0002300000    1  A01G0002000000      6      A01G2/3     A01G2/00   \n",
      "738  A01G0002320000    2  A01G0002300000      7     A01G2/32      A01G2/3   \n",
      "739  A01G0002350000    2  A01G0002300000      7     A01G2/35      A01G2/3   \n",
      "740  A01G0002380000    2  A01G0002300000      7     A01G2/38      A01G2/3   \n",
      "761  A01G0009029000    2  A01G0009020000      7    A01G9/029     A01G9/02   \n",
      "762  A01G0009033000    2  A01G0009020000      7    A01G9/033     A01G9/02   \n",
      "774  A01G0009280000    1  A01G0009000000      6     A01G9/28     A01G9/00   \n",
      "\n",
      "                                              title_en  \\\n",
      "734  Vegetative propagation (plant reproduction by ...   \n",
      "735                               by means of cuttings   \n",
      "736                                           Layering   \n",
      "737             Grafting (grafting-wax A01N0003040000)   \n",
      "738                       Automatic apparatus therefor   \n",
      "739                                 Cutting; Inserting   \n",
      "740                                  Holding; Ligating   \n",
      "761  Receptacles for seedlings (growth substrates i...   \n",
      "762  Flat containers for turf, lawn or the like, e....   \n",
      "774  Raised beds; Planting beds; Edging elements fo...   \n",
      "\n",
      "                                              title_fr creation_date  size  \\\n",
      "734  Propagation végétative (reproduction de plante...    20180101.0     7   \n",
      "735                                          Bouturage    20180101.0     1   \n",
      "736                                         Marcottage    20180101.0     1   \n",
      "737           Greffe (mastic à greffer A01N0003040000)    20180101.0     4   \n",
      "738                 Appareils automatiques à cet effet    20180101.0     1   \n",
      "739                                   Coupe; Repiquage    20180101.0     1   \n",
      "740                                 Maintien; Ligature    20180101.0     1   \n",
      "761  Récipients pour plants (substrats de culture e...    20180101.0     1   \n",
      "762  Récipients plats pour gazon, pelouse ou simila...    20180101.0     1   \n",
      "774  Parterres surélevés; Platebandes; Éléments de ...    20180101.0     1   \n",
      "\n",
      "     size_percent  \n",
      "734         0.009  \n",
      "735         0.001  \n",
      "736         0.001  \n",
      "737         0.005  \n",
      "738         0.001  \n",
      "739         0.001  \n",
      "740         0.001  \n",
      "761         0.001  \n",
      "762         0.001  \n",
      "774         0.001  \n",
      "-------------------------------- 20170101.0\n",
      "(690, 11)\n",
      "              symbol kind          parent  level symbol_short parent_short  \\\n",
      "1160  A01K0061100000    1  A01K0061000000      6     A01K61/1    A01K61/00   \n",
      "1161  A01K0061130000    2  A01K0061100000      7    A01K61/13     A01K61/1   \n",
      "1162  A01K0061170000    2  A01K0061100000      7    A01K61/17     A01K61/1   \n",
      "1163  A01K0061200000    1  A01K0061000000      6     A01K61/2    A01K61/00   \n",
      "1164  A01K0061300000    1  A01K0061000000      6     A01K61/3    A01K61/00   \n",
      "1165  A01K0061400000    1  A01K0061000000      6     A01K61/4    A01K61/00   \n",
      "1166  A01K0061500000    1  A01K0061000000      6     A01K61/5    A01K61/00   \n",
      "1167  A01K0061510000    2  A01K0061500000      7    A01K61/51     A01K61/5   \n",
      "1168  A01K0061530000    3  A01K0061510000      8    A01K61/53    A01K61/51   \n",
      "1169  A01K0061540000    2  A01K0061500000      7    A01K61/54     A01K61/5   \n",
      "\n",
      "                                               title_en  \\\n",
      "1160                                            of fish   \n",
      "1161           Prevention or treatment of fish diseases   \n",
      "1162                          Hatching, e.g. incubators   \n",
      "1163      of zooplankton, e.g. water fleas or Rotatoria   \n",
      "1164           of sponges, sea urchins or sea cucumbers   \n",
      "1165               of annelids, e.g. lugworms or Eunice   \n",
      "1166  of shellfish (A01K0061300000, A01K0061400000 t...   \n",
      "1167      of gastropods, e.g. abalones or turban snails   \n",
      "1168                                   Baskets therefor   \n",
      "1169               of bivalves, e.g. oysters or mussels   \n",
      "\n",
      "                                               title_fr creation_date  size  \\\n",
      "1160                                       des poissons    20170101.0     3   \n",
      "1161  Prévention ou traitement des maladies des pois...    20170101.0     1   \n",
      "1162                        Éclosion, p.ex. incubateurs    20170101.0     1   \n",
      "1163  du zooplancton, p.ex. des cladocères ou des ro...    20170101.0     1   \n",
      "1164  des éponges, des oursins ou des concombres de mer    20170101.0     1   \n",
      "1165  des annélides, p.ex. des arénicoles ou des eun...    20170101.0     1   \n",
      "1166  des coquillages (A01K0061300000, A01K006140000...    20170101.0     8   \n",
      "1167  des gastéropodes, p.ex. des ormeaux ou des Tur...    20170101.0     2   \n",
      "1168                                Paniers à cet effet    20170101.0     1   \n",
      "1169      des bivalves, p.ex. des huîtres ou des moules    20170101.0     4   \n",
      "\n",
      "      size_percent  \n",
      "1160         0.004  \n",
      "1161         0.001  \n",
      "1162         0.001  \n",
      "1163         0.001  \n",
      "1164         0.001  \n",
      "1165         0.001  \n",
      "1166         0.011  \n",
      "1167         0.003  \n",
      "1168         0.001  \n",
      "1169         0.005  \n",
      "-------------------------------- 20110101.0\n",
      "(493, 11)\n",
      "              symbol kind          parent  level symbol_short parent_short  \\\n",
      "1346  A01M0029200000    2  A01M0029160000      7     A01M29/2    A01M29/16   \n",
      "1376  A01M0029060000    1  A01M0029000000      6    A01M29/06    A01M29/00   \n",
      "1377  A01M0029080000    2  A01M0029060000      7    A01M29/08    A01M29/06   \n",
      "1378  A01M0029100000    2  A01M0029060000      7     A01M29/1    A01M29/06   \n",
      "1379  A01M0029120000    1  A01M0029000000      6    A01M29/12    A01M29/00   \n",
      "1380  A01M0029140000    1  A01M0029000000      6    A01M29/14    A01M29/00   \n",
      "1381  A01M0029160000    1  A01M0029000000      6    A01M29/16    A01M29/00   \n",
      "1382  A01M0029180000    2  A01M0029160000      7    A01M29/18    A01M29/16   \n",
      "1383  A01M0029220000    1  A01M0029000000      6    A01M29/22    A01M29/00   \n",
      "1384  A01M0029240000    1  A01M0029000000      6    A01M29/24    A01M29/00   \n",
      "\n",
      "                                               title_en  \\\n",
      "1346  with generation of periodically explosive reports   \n",
      "1376  using visual means, e.g. scarecrows, moving el...   \n",
      "1377  using reflection, colours or films with specif...   \n",
      "1378  using light sources, e.g. lasers or flashing l...   \n",
      "1379  using odoriferous substances, e.g. aromas, phe...   \n",
      "1380                              using thermal effects   \n",
      "1381                                  using sound waves   \n",
      "1382                           using ultrasonic signals   \n",
      "1383  using vibrations (A01M0029160000 takes precede...   \n",
      "1384  using electric or magnetic effects, e.g. elect...   \n",
      "\n",
      "                                               title_fr creation_date  size  \\\n",
      "1346           avec production d'explosions périodiques    20110101.0     1   \n",
      "1376  utilisant des moyens visuels, p.ex. épouvantai...    20110101.0     3   \n",
      "1377  utilisant des reflets, des couleurs ou des fil...    20110101.0     1   \n",
      "1378  utilisant des sources lumineuses, p.ex. des la...    20110101.0     1   \n",
      "1379  utilisant des substances odorantes, p.ex. des ...    20110101.0     1   \n",
      "1380                    utilisant des effets thermiques    20110101.0     1   \n",
      "1381                        utilisant des ondes sonores    20110101.0     3   \n",
      "1382                 utilisant des signaux ultrasonores    20110101.0     1   \n",
      "1383  utilisant des vibrations (A01M0029160000 a pri...    20110101.0     1   \n",
      "1384  utilisant des effets électriques ou magnétique...    20110101.0     3   \n",
      "\n",
      "      size_percent  \n",
      "1346         0.001  \n",
      "1376         0.004  \n",
      "1377         0.001  \n",
      "1378         0.001  \n",
      "1379         0.001  \n",
      "1380         0.001  \n",
      "1381         0.004  \n",
      "1382         0.001  \n",
      "1383         0.001  \n",
      "1384         0.004  \n",
      "-------------------------------- 20160101.0\n",
      "(1027, 11)\n",
      "              symbol kind          parent  level symbol_short parent_short  \\\n",
      "2324  A23K0010000000    m            A23K      5    A23K10/00         A23K   \n",
      "2325  A23K0010100000    1  A23K0010000000      6     A23K10/1    A23K10/00   \n",
      "2326  A23K0010120000    2  A23K0010100000      7    A23K10/12     A23K10/1   \n",
      "2327  A23K0010140000    2  A23K0010100000      7    A23K10/14     A23K10/1   \n",
      "2328  A23K0010160000    2  A23K0010100000      7    A23K10/16     A23K10/1   \n",
      "2329  A23K0010180000    3  A23K0010160000      8    A23K10/18    A23K10/16   \n",
      "2330  A23K0010200000    1  A23K0010000000      6     A23K10/2    A23K10/00   \n",
      "2331  A23K0010220000    2  A23K0010200000      7    A23K10/22     A23K10/2   \n",
      "2332  A23K0010240000    2  A23K0010200000      7    A23K10/24     A23K10/2   \n",
      "2333  A23K0010260000    2  A23K0010200000      7    A23K10/26     A23K10/2   \n",
      "\n",
      "                                               title_en  \\\n",
      "2324                              Animal feeding-stuffs   \n",
      "2325  obtained by microbiological or biochemical pro...   \n",
      "2326  by fermentation of natural products, e.g. of v...   \n",
      "2327        Pretreatment of feeding-stuffs with enzymes   \n",
      "2328  Addition of microorganisms or extracts thereof...   \n",
      "2329                             of live microorganisms   \n",
      "2330  from material of animal origin (obtained by mi...   \n",
      "2331                                          from fish   \n",
      "2332                                         from blood   \n",
      "2333  from waste material, e.g. feathers, bones or s...   \n",
      "\n",
      "                                               title_fr creation_date  size  \\\n",
      "2324                 Produits alimentaires pour animaux    20160101.0    18   \n",
      "2325  obtenus par des procédés microbiologiques ou b...    20160101.0     5   \n",
      "2326  par fermentation de produits naturels, p.ex. d...    20160101.0     1   \n",
      "2327  Prétraitement de produits alimentaires avec de...    20160101.0     1   \n",
      "2328  Ajout de micro-organismes ou de leurs produits...    20160101.0     2   \n",
      "2329                        de micro-organismes vivants    20160101.0     1   \n",
      "2330  à base de matières d’origine animale (obtenus ...    20160101.0     5   \n",
      "2331                                  à base de poisson    20160101.0     1   \n",
      "2332                                     à base de sang    20160101.0     1   \n",
      "2333  à base de matières résiduelles, p.ex. de plume...    20160101.0     2   \n",
      "\n",
      "      size_percent  \n",
      "2324         0.024  \n",
      "2325         0.007  \n",
      "2326         0.001  \n",
      "2327         0.001  \n",
      "2328         0.003  \n",
      "2329         0.001  \n",
      "2330         0.007  \n",
      "2331         0.001  \n",
      "2332         0.001  \n",
      "2333         0.003  \n",
      "-------------------------------- 20190101.0\n",
      "(669, 11)\n",
      "              symbol kind          parent  level symbol_short parent_short  \\\n",
      "3133  A41D0031040000    1  A41D0031000000      6    A41D31/04    A41D31/00   \n",
      "3134  A41D0031060000    2  A41D0031040000      7    A41D31/06    A41D31/04   \n",
      "3135  A41D0031080000    2  A41D0031040000      7    A41D31/08    A41D31/04   \n",
      "3136  A41D0031100000    2  A41D0031040000      7     A41D31/1    A41D31/04   \n",
      "3137  A41D0031102000    3  A41D0031100000      8   A41D31/102     A41D31/1   \n",
      "3138  A41D0031120000    2  A41D0031040000      7    A41D31/12    A41D31/04   \n",
      "3139  A41D0031140000    2  A41D0031040000      7    A41D31/14    A41D31/04   \n",
      "3140  A41D0031180000    2  A41D0031040000      7    A41D31/18    A41D31/04   \n",
      "3141  A41D0031240000    2  A41D0031040000      7    A41D31/24    A41D31/04   \n",
      "3142  A41D0031260000    2  A41D0031040000      7    A41D31/26    A41D31/04   \n",
      "\n",
      "                                               title_en  \\\n",
      "3133           characterised by special function or use   \n",
      "3134              Thermally protective, e.g. insulating   \n",
      "3135                     Heat resistant; Fire retardant   \n",
      "3136  Impermeable to liquids, e.g. waterproof; Liqui...   \n",
      "3137                          Waterproof and breathable   \n",
      "3138                       Hygroscopic; Water retaining   \n",
      "3139  Air permeable, i.e. capable of being penetrate...   \n",
      "3140                                            Elastic   \n",
      "3141  Resistant to mechanical stress, e.g. pierce-proof   \n",
      "3142  Electrically protective, e.g. preventing stati...   \n",
      "\n",
      "                                               title_fr creation_date  size  \\\n",
      "3133  caractérisés par une fonction ou une utilisati...    20190101.0    13   \n",
      "3134               Protection thermique, p.ex. isolants    20190101.0     1   \n",
      "3135                 résistants à la chaleur; ignifuges    20190101.0     1   \n",
      "3136  imperméables aux liquides, p.ex. à l’eau; repo...    20190101.0     2   \n",
      "3137                         imperméables et respirants    20190101.0     1   \n",
      "3138                   hygroscopiques; rétenteurs d'eau    20190101.0     1   \n",
      "3139  perméables à l’air, c. à d. capables d’être pé...    20190101.0     1   \n",
      "3140                                         élastiques    20190101.0     1   \n",
      "3141  résistants à la contrainte mécanique, p.ex. ré...    20190101.0     1   \n",
      "3142  Protection électrique, p.ex. prévention de l'é...    20190101.0     1   \n",
      "\n",
      "      size_percent  \n",
      "3133         0.017  \n",
      "3134         0.001  \n",
      "3135         0.001  \n",
      "3136         0.003  \n",
      "3137         0.001  \n",
      "3138         0.001  \n",
      "3139         0.001  \n",
      "3140         0.001  \n",
      "3141         0.001  \n",
      "3142         0.001  \n",
      "-------------------------------- 20080401.0\n",
      "(25, 11)\n",
      "               symbol kind          parent  level symbol_short parent_short  \\\n",
      "3577   G05G0001440000    2  G05G0001300000      7     G05G1/44      G05G1/3   \n",
      "63012  G05G0001445000    3  G05G0001440000      8    G05G1/445     G05G1/44   \n",
      "63929  G05G0001420000    2  G05G0001300000      7     G05G1/42      G05G1/3   \n",
      "64038  G05G0001380000    2  G05G0001300000      7     G05G1/38      G05G1/3   \n",
      "64039  G05G0001400000    2  G05G0001300000      7      G05G1/4      G05G1/3   \n",
      "64049  G05G0001010000    1  G05G0001000000      6     G05G1/01     G05G1/00   \n",
      "64050  G05G0001015000    1  G05G0001000000      6    G05G1/015     G05G1/00   \n",
      "64057  G05G0001300000    1  G05G0001000000      6      G05G1/3     G05G1/00   \n",
      "64058  G05G0001320000    2  G05G0001300000      7     G05G1/32      G05G1/3   \n",
      "64059  G05G0001323000    3  G05G0001320000      8    G05G1/323     G05G1/32   \n",
      "\n",
      "                                                title_en  \\\n",
      "3577                                            pivoting   \n",
      "63012                            about a central fulcrum   \n",
      "63929                         non-pivoting, e.g. sliding   \n",
      "64038  comprising means to continuously detect pedal ...   \n",
      "64039                                         adjustable   \n",
      "64049  Arrangements of two or more controlling member...   \n",
      "64050  Arrangements for indicating the position of a ...   \n",
      "64057               Controlling members actuated by foot   \n",
      "64058                       with means to prevent injury   \n",
      "64059  means disconnecting the connection between ped...   \n",
      "\n",
      "                                                title_fr creation_date  size  \\\n",
      "3577                                           pivotants    20080401.0     2   \n",
      "63012                          autour d'un pivot central    20080401.0     1   \n",
      "63929                   non pivotants, p.ex. coulissants    20080401.0     1   \n",
      "64038  comprenant des moyens pour détecter en continu...    20080401.0     1   \n",
      "64039                                          réglables    20080401.0     2   \n",
      "64049  Agencements de plusieurs organes de commande l...    20080401.0     1   \n",
      "64050  Agencements pour indiquer la position d'un org...    20080401.0     1   \n",
      "64057          Organes de commande actionnés par le pied    20080401.0    17   \n",
      "64058          avec des moyens pour éviter les blessures    20080401.0     3   \n",
      "64059  moyens pour interrompre la liaison entre la pé...    20080401.0     1   \n",
      "\n",
      "       size_percent  \n",
      "3577          0.003  \n",
      "63012         0.001  \n",
      "63929         0.001  \n",
      "64038         0.001  \n",
      "64039         0.003  \n",
      "64049         0.001  \n",
      "64050         0.001  \n",
      "64057         0.023  \n",
      "64058         0.004  \n",
      "64059         0.001  \n",
      "-------------------------------- 20130101.0\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(615, 11)\n",
      "              symbol kind          parent  level symbol_short parent_short  \\\n",
      "4635  A47C0001035500    5  A47C0001035000     10   A47C1/0355    A47C1/035   \n",
      "6313  A61F0002070000    4  A61F0002060000      9     A61F2/07     A61F2/06   \n",
      "6352  A61F0002844000    2  A61F0002820000      7    A61F2/844     A61F2/82   \n",
      "6353  A61F0002848000    2  A61F0002820000      7    A61F2/848     A61F2/82   \n",
      "6354  A61F0002852000    2  A61F0002820000      7    A61F2/852     A61F2/82   \n",
      "6355  A61F0002856000    2  A61F0002820000      7    A61F2/856     A61F2/82   \n",
      "6358  A61F0002890000    3  A61F0002860000      8     A61F2/89     A61F2/86   \n",
      "6360  A61F0002910000    4  A61F0002900000      9     A61F2/91      A61F2/9   \n",
      "6361  A61F0002915000    5  A61F0002910000     10    A61F2/915     A61F2/91   \n",
      "6363  A61F0002930000    3  A61F0002920000      8     A61F2/93     A61F2/92   \n",
      "\n",
      "                                               title_en  \\\n",
      "4635   actuated by linkages, e.g. lazy-tongs mechanisms   \n",
      "6313                                       Stent-grafts   \n",
      "6352                         folded prior to deployment   \n",
      "6353  having means for fixation to the vessel wall, ...   \n",
      "6354            Two or more distinct overlapping stents   \n",
      "6355      Single tubular stent with side portal passage   \n",
      "6358  the wire-like elements comprising two or more ...   \n",
      "6360  made from perforated sheets or tubes, e.g. per...   \n",
      "6361  with bands having a meander structure, adjacen...   \n",
      "6363  circumferentially expandable by using ratcheti...   \n",
      "\n",
      "                                               title_fr creation_date  size  \\\n",
      "4635  actionnés par des tringles, p.ex. à mécanisme ...    20130101.0     1   \n",
      "6313                Endoprothèses déployables couvertes    20130101.0     1   \n",
      "6352                          repliés avant déploiement    20130101.0     1   \n",
      "6353  avec des moyens de fixation à la paroi du vais...    20130101.0     1   \n",
      "6354  constitués d’au moins deux stents distincts se...    20130101.0     1   \n",
      "6355  constitués d’un stent tubulaire unique pourvu ...    20130101.0     1   \n",
      "6358  les éléments filiformes comprenant au moins de...    20130101.0     1   \n",
      "6360  fabriquée à partir de feuilles perforées ou de...    20130101.0     2   \n",
      "6361  avec des bandes présentant une structure en mé...    20130101.0     1   \n",
      "6363  se déployant de manière circonférentielle en u...    20130101.0     1   \n",
      "\n",
      "      size_percent  \n",
      "4635         0.001  \n",
      "6313         0.001  \n",
      "6352         0.001  \n",
      "6353         0.001  \n",
      "6354         0.001  \n",
      "6355         0.001  \n",
      "6358         0.001  \n",
      "6360         0.003  \n",
      "6361         0.001  \n",
      "6363         0.001  \n",
      "-------------------------------- 20150101.0\n",
      "(342, 11)\n",
      "              symbol kind          parent  level symbol_short parent_short  \\\n",
      "7284  A61K0035130000    2  A61K0035120000      7    A61K35/13    A61K35/12   \n",
      "7286  A61K0035150000    3  A61K0035140000      8    A61K35/15    A61K35/14   \n",
      "7288  A61K0035170000    3  A61K0035140000      8    A61K35/17    A61K35/14   \n",
      "7290  A61K0035190000    3  A61K0035140000      8    A61K35/19    A61K35/14   \n",
      "7298  A61K0035330000    2  A61K0035120000      7    A61K35/33    A61K35/12   \n",
      "7300  A61K0035350000    2  A61K0035120000      7    A61K35/35    A61K35/12   \n",
      "7312  A61K0035510000    3  A61K0035480000      8    A61K35/51    A61K35/48   \n",
      "7315  A61K0035545000    4  A61K0035540000      9   A61K35/545    A61K35/54   \n",
      "7318  A61K0035570000    2  A61K0035560000      7    A61K35/57    A61K35/56   \n",
      "7320  A61K0035583000    3  A61K0035580000      8   A61K35/583    A61K35/58   \n",
      "\n",
      "                                               title_en  \\\n",
      "7284  Tumour cells, irrespective of tissue of origin...   \n",
      "7286  Cells of the myeloid line, e.g. granulocytes, ...   \n",
      "7288  Lymphocytes; B-cells; T-cells; Natural killer ...   \n",
      "7290                          Platelets; Megacaryocytes   \n",
      "7298                                        Fibroblasts   \n",
      "7300  Fat tissue; Adipocytes; Stromal cells; Connect...   \n",
      "7312  Umbilical cord; Umbilical cord blood; Umbilica...   \n",
      "7315  Embryonic stem cells; Pluripotent stem cells; ...   \n",
      "7318  Birds; Materials from birds, e.g. eggs, feathe...   \n",
      "7320  Snakes; Lizards, e.g. chameleons (therapeutic ...   \n",
      "\n",
      "                                               title_fr creation_date  size  \\\n",
      "7284  Cellules tumorales, quel que soit le tissu d’o...    20150101.0     1   \n",
      "7286  Cellules de la lignée des myéloïdes, p.ex. gra...    20150101.0     1   \n",
      "7288  Lymphocytes; Lymphocytes B; Lymphocytes T; Cel...    20150101.0     1   \n",
      "7290                         Plaquettes; Mégacaryocytes    20150101.0     1   \n",
      "7298                                       Fibroblastes    20150101.0     1   \n",
      "7300  Tissu graisseux; Adipocytes; Cellules stromale...    20150101.0     1   \n",
      "7312  Cordon ombilical; Sang de cordon ombilical; Ce...    20150101.0     1   \n",
      "7315  Cellules souches embryonnaires; Cellules souch...    20150101.0     1   \n",
      "7318  Oiseaux; Substances provenant des oiseaux, p.e...    20150101.0     1   \n",
      "7320  Serpents; Lézards, p.ex. caméléons (usage thér...    20150101.0     1   \n",
      "\n",
      "      size_percent  \n",
      "7284         0.001  \n",
      "7286         0.001  \n",
      "7288         0.001  \n",
      "7290         0.001  \n",
      "7298         0.001  \n",
      "7300         0.001  \n",
      "7312         0.001  \n",
      "7315         0.001  \n",
      "7318         0.001  \n",
      "7320         0.001  \n",
      "-------------------------------- 20120101.0\n",
      "(504, 11)\n",
      "              symbol kind          parent  level symbol_short parent_short  \\\n",
      "8097  A61M0099000000    m            A61M      5    A61M99/00         A61M   \n",
      "8991  A63C0010200000    2  A63C0010160000      7     A63C10/2    A63C10/16   \n",
      "9014  A63C0010000000    m            A63C      5    A63C10/00         A63C   \n",
      "9015  A63C0010020000    1  A63C0010000000      6    A63C10/02    A63C10/00   \n",
      "9016  A63C0010040000    2  A63C0010020000      7    A63C10/04    A63C10/02   \n",
      "9017  A63C0010060000    3  A63C0010040000      8    A63C10/06    A63C10/04   \n",
      "9018  A63C0010080000    2  A63C0010020000      7    A63C10/08    A63C10/02   \n",
      "9019  A63C0010100000    2  A63C0010020000      7     A63C10/1    A63C10/02   \n",
      "9020  A63C0010120000    1  A63C0010000000      6    A63C10/12    A63C10/00   \n",
      "9021  A63C0010140000    1  A63C0010000000      6    A63C10/14    A63C10/00   \n",
      "\n",
      "                                               title_en  \\\n",
      "8097  Subject matter not provided for in other group...   \n",
      "8991  in longitudinal or lateral direction relative ...   \n",
      "9014                                 Snowboard bindings   \n",
      "9015       characterised by details of the shoe holders   \n",
      "9016             Shoe holders for passing over the shoe   \n",
      "9017            Straps therefor, e.g. adjustable straps   \n",
      "9018                       Toe or heel stirrups; Clamps   \n",
      "9019  using parts which are fixed on the shoe, e.g. ...   \n",
      "9020  Yieldable or self-releasing in the event of an...   \n",
      "9021           Interfaces, e.g. in the shape of a plate   \n",
      "\n",
      "                                               title_fr creation_date  size  \\\n",
      "8097  Matière non prévue dans les autres groupes de ...    20120101.0     1   \n",
      "8991  en direction longitudinale ou latérale par rap...    20120101.0     1   \n",
      "9014                      Fixations de surfs des neiges    20120101.0    15   \n",
      "9015  caractérisées par des parties constitutives du...    20120101.0     5   \n",
      "9016           à sangles ou capot coiffant la chaussure    20120101.0     2   \n",
      "9017      Sangles à cet effet, p.ex. sangles ajustables    20120101.0     1   \n",
      "9018        Étriers avant ou arrière; Brides de serrage    20120101.0     1   \n",
      "9019  utilisant des éléments fixés à la chaussure, p...    20120101.0     1   \n",
      "9020  se relâchant ou se détachant d'elles-mêmes en ...    20120101.0     1   \n",
      "9021          Interfaces, p.ex. sous la forme de plaque    20120101.0     1   \n",
      "\n",
      "      size_percent  \n",
      "8097         0.001  \n",
      "8991         0.001  \n",
      "9014         0.020  \n",
      "9015         0.007  \n",
      "9016         0.003  \n",
      "9017         0.001  \n",
      "9018         0.001  \n",
      "9019         0.001  \n",
      "9020         0.001  \n",
      "9021         0.001  \n",
      "-------------------------------- 20070101.0\n",
      "(61, 11)\n",
      "              symbol kind          parent  level symbol_short parent_short  \\\n",
      "8535  A62D0003020000    1  A62D0003000000      6     A62D3/02     A62D3/00   \n",
      "8536  A62D0003100000    1  A62D0003000000      6      A62D3/1     A62D3/00   \n",
      "8537  A62D0003110000    2  A62D0003100000      7     A62D3/11      A62D3/1   \n",
      "8538  A62D0003115000    3  A62D0003110000      8    A62D3/115     A62D3/11   \n",
      "8539  A62D0003130000    2  A62D0003100000      7     A62D3/13      A62D3/1   \n",
      "8540  A62D0003150000    2  A62D0003100000      7     A62D3/15      A62D3/1   \n",
      "8541  A62D0003170000    2  A62D0003100000      7     A62D3/17      A62D3/1   \n",
      "8542  A62D0003172000    3  A62D0003170000      8    A62D3/172     A62D3/17   \n",
      "8543  A62D0003174000    3  A62D0003170000      8    A62D3/174     A62D3/17   \n",
      "8544  A62D0003176000    3  A62D0003170000      8    A62D3/176     A62D3/17   \n",
      "\n",
      "                                               title_en  \\\n",
      "8535  by biological methods, i.e. processes using en...   \n",
      "8536  by subjecting to electric or wave energy or pa...   \n",
      "8537    Electrochemical processes, e.g. electrodialysis   \n",
      "8538             Electrolytic degradation or conversion   \n",
      "8539                                    to sonic energy   \n",
      "8540  to particle radiation, e.g. electron beam radi...   \n",
      "8541  to electromagnetic radiation, e.g. emitted by ...   \n",
      "8542  Gamma rays, i.e. radiation having a wavelength...   \n",
      "8543  X-rays, i.e. radiation having a wavelength of ...   \n",
      "8544  Ultraviolet radiation, i.e. radiation having a...   \n",
      "\n",
      "                                               title_fr creation_date  size  \\\n",
      "8535  par des procédés biologiques, c. à d. des proc...    20070101.0     1   \n",
      "8536  par exposition à une énergie électrique ou ond...    20070101.0    11   \n",
      "8537    Procédés électrochimiques, p.ex. électrodialyse    20070101.0     2   \n",
      "8538           Dégradation ou conversion électrolytique    20070101.0     1   \n",
      "8539                              à une énergie sonique    20070101.0     1   \n",
      "8540  à un rayonnement particulaire, p.ex. à un fais...    20070101.0     1   \n",
      "8541  à un rayonnement électromagnétique, p.ex. émis...    20070101.0     5   \n",
      "8542  Rayons gamma, c. à d. rayonnement présentant u...    20070101.0     1   \n",
      "8543  Rayons X, c. à d. rayonnement présentant une l...    20070101.0     1   \n",
      "8544  Rayonnement ultraviolet, c. à d. rayonnement p...    20070101.0     1   \n",
      "\n",
      "      size_percent  \n",
      "8535         0.001  \n",
      "8536         0.015  \n",
      "8537         0.003  \n",
      "8538         0.001  \n",
      "8539         0.001  \n",
      "8540         0.001  \n",
      "8541         0.007  \n",
      "8542         0.001  \n",
      "8543         0.001  \n",
      "8544         0.001  \n",
      "-------------------------------- 20140101.0\n",
      "(629, 11)\n",
      "              symbol kind          parent  level symbol_short parent_short  \\\n",
      "9149  A63F0013200000    1  A63F0013000000      6     A63F13/2    A63F13/00   \n",
      "9150  A63F0013210000    2  A63F0013200000      7    A63F13/21     A63F13/2   \n",
      "9151  A63F0013211000    3  A63F0013210000      8   A63F13/211    A63F13/21   \n",
      "9152  A63F0013212000    3  A63F0013210000      8   A63F13/212    A63F13/21   \n",
      "9153  A63F0013213000    3  A63F0013210000      8   A63F13/213    A63F13/21   \n",
      "9154  A63F0013214000    3  A63F0013210000      8   A63F13/214    A63F13/21   \n",
      "9155  A63F0013214500    4  A63F0013214000      9  A63F13/2145   A63F13/214   \n",
      "9156  A63F0013215000    3  A63F0013210000      8   A63F13/215    A63F13/21   \n",
      "9157  A63F0013216000    3  A63F0013210000      8   A63F13/216    A63F13/21   \n",
      "9158  A63F0013217000    3  A63F0013210000      8   A63F13/217    A63F13/21   \n",
      "\n",
      "                                               title_en  \\\n",
      "9149          Input arrangements for video game devices   \n",
      "9150  characterised by their sensors, purposes or types   \n",
      "9151  using inertial sensors, e.g. accelerometers or...   \n",
      "9152  using sensors worn by the player, e.g. for mea...   \n",
      "9153  comprising photodetecting means, e.g. cameras,...   \n",
      "9154  for locating contacts on a surface, e.g. floor...   \n",
      "9155  the surface being also a display device, e.g. ...   \n",
      "9156  comprising means for detecting acoustic signal...   \n",
      "9157  using geographical information, e.g. location ...   \n",
      "9158  using environment-related information, i.e. in...   \n",
      "\n",
      "                                               title_fr creation_date  size  \\\n",
      "9149  Dispositions d'entrée pour les dispositifs de ...    20140101.0    17   \n",
      "9150  caractérisées par leurs capteurs, leurs finali...    20140101.0    11   \n",
      "9151  utilisant des capteurs d’inertie, p.ex. des ac...    20140101.0     1   \n",
      "9152  utilisant des capteurs portés par le joueur, p...    20140101.0     1   \n",
      "9153  comprenant des moyens de photo-détection, p.ex...    20140101.0     1   \n",
      "9154  pour localiser des contacts sur une surface, p...    20140101.0     2   \n",
      "9155  la surface étant aussi un dispositif d’afficha...    20140101.0     1   \n",
      "9156  comprenant des moyens de détection des signaux...    20140101.0     1   \n",
      "9157  utilisant des informations géographiques, p.ex...    20140101.0     1   \n",
      "9158  utilisant des informations liées à l’environne...    20140101.0     1   \n",
      "\n",
      "      size_percent  \n",
      "9149         0.023  \n",
      "9150         0.015  \n",
      "9151         0.001  \n",
      "9152         0.001  \n",
      "9153         0.001  \n",
      "9154         0.003  \n",
      "9155         0.001  \n",
      "9156         0.001  \n",
      "9157         0.001  \n",
      "9158         0.001  \n",
      "-------------------------------- 20071001.0\n",
      "(28, 11)\n",
      "               symbol kind          parent  level symbol_short parent_short  \\\n",
      "20569  B60K0006200000    1  B60K0006000000      6      B60K6/2     B60K6/00   \n",
      "20570  B60K0006220000    2  B60K0006200000      7     B60K6/22      B60K6/2   \n",
      "20571  B60K0006240000    3  B60K0006220000      8     B60K6/24     B60K6/22   \n",
      "20572  B60K0006260000    3  B60K0006220000      8     B60K6/26     B60K6/22   \n",
      "20574  B60K0006280000    3  B60K0006220000      8     B60K6/28     B60K6/22   \n",
      "20575  B60K0006300000    3  B60K0006220000      8      B60K6/3     B60K6/22   \n",
      "20576  B60K0006320000    3  B60K0006220000      8     B60K6/32     B60K6/22   \n",
      "20577  B60K0006340000    3  B60K0006220000      8     B60K6/34     B60K6/22   \n",
      "20578  B60K0006360000    3  B60K0006220000      8     B60K6/36     B60K6/22   \n",
      "20579  B60K0006365000    4  B60K0006360000      9    B60K6/365     B60K6/36   \n",
      "\n",
      "                                                title_en  \\\n",
      "20569  the prime-movers consisting of electric motors...   \n",
      "20570  characterised by apparatus, components or mean...   \n",
      "20571            characterised by the combustion engines   \n",
      "20572      characterised by the motors or the generators   \n",
      "20574  characterised by the electric energy storing m...   \n",
      "20575  characterised by chargeable mechanical accumul...   \n",
      "20576                    characterised by the fuel cells   \n",
      "20577  characterised by the absence of energy storing...   \n",
      "20578         characterised by the transmission gearings   \n",
      "20579               with the gears having orbital motion   \n",
      "\n",
      "                                                title_fr creation_date  size  \\\n",
      "20569  les moteurs primaires étant constitués de mote...    20071001.0    28   \n",
      "20570  caractérisés par des appareils, des organes ou...    20071001.0    14   \n",
      "20571          caractérisés par les moteurs à combustion    20071001.0     1   \n",
      "20572    caractérisés par les moteurs ou les générateurs    20071001.0     1   \n",
      "20574  caractérisés par les moyens d'accumulation d'é...    20071001.0     1   \n",
      "20575  caractérisés par des accumulateurs mécaniques ...    20071001.0     1   \n",
      "20576           caractérisés par les piles à combustible    20071001.0     1   \n",
      "20577  caractérisés par l'absence de moyens d'accumul...    20071001.0     1   \n",
      "20578    caractérisés par les transmissions à engrenages    20071001.0     2   \n",
      "20579    les pignons étant animés d'un mouvement orbital    20071001.0     1   \n",
      "\n",
      "       size_percent  \n",
      "20569         0.038  \n",
      "20570         0.019  \n",
      "20571         0.001  \n",
      "20572         0.001  \n",
      "20574         0.001  \n",
      "20575         0.001  \n",
      "20576         0.001  \n",
      "20577         0.001  \n",
      "20578         0.003  \n",
      "20579         0.001  \n",
      "-------------------------------- 20080101.0\n",
      "(146, 11)\n",
      "               symbol kind          parent  level symbol_short parent_short  \\\n",
      "74587  H04H0020020000    1  H04H0020000000      6    H04H20/02    H04H20/00   \n",
      "74588  H04H0020040000    2  H04H0020020000      7    H04H20/04    H04H20/02   \n",
      "74589  H04H0020060000    2  H04H0020020000      7    H04H20/06    H04H20/02   \n",
      "74590  H04H0020080000    2  H04H0020020000      7    H04H20/08    H04H20/02   \n",
      "74591  H04H0020100000    1  H04H0020000000      6     H04H20/1    H04H20/00   \n",
      "74592  H04H0020120000    1  H04H0020000000      6    H04H20/12    H04H20/00   \n",
      "74593  H04H0020140000    2  H04H0020120000      7    H04H20/14    H04H20/12   \n",
      "74594  H04H0020160000    1  H04H0020000000      6    H04H20/16    H04H20/00   \n",
      "74595  H04H0020180000    1  H04H0020000000      6    H04H20/18    H04H20/00   \n",
      "74596  H04H0020200000    1  H04H0020000000      6     H04H20/2    H04H20/00   \n",
      "\n",
      "                                                title_en  \\\n",
      "74587    Arrangements for relaying broadcast information   \n",
      "74588                      from field pickup units [FPU]   \n",
      "74589                           among broadcast stations   \n",
      "74590                             among terminal devices   \n",
      "74591  Arrangements for replacing or switching inform...   \n",
      "74592  Arrangements for monitoring, testing or troubl...   \n",
      "74593                          for monitoring programmes   \n",
      "74594  Arrangements for broadcast or distribution of ...   \n",
      "74595  Arrangements for synchronising broadcast or di...   \n",
      "74596  Arrangements for broadcast or distribution of ...   \n",
      "\n",
      "                                                title_fr creation_date  size  \\\n",
      "74587  Dispositions pour la retransmission des inform...    20080101.0     4   \n",
      "74588          à partir d’appareils d’émission en direct    20080101.0     1   \n",
      "74589               entre des stations de radiodiffusion    20080101.0     1   \n",
      "74590                      entre des appareils terminaux    20080101.0     1   \n",
      "74591  Dispositions pour le remplacement ou la commut...    20080101.0     1   \n",
      "74592  Dispositions de contrôle, de test ou de dépannage    20080101.0     2   \n",
      "74593                         de contrôle des programmes    20080101.0     1   \n",
      "74594  Dispositions de radiodiffusion ou de distribut...    20080101.0     1   \n",
      "74595  Dispositions de synchronisation de la radiodif...    20080101.0     1   \n",
      "74596  Dispositions de radiodiffusion ou de distribut...    20080101.0     3   \n",
      "\n",
      "       size_percent  \n",
      "74587         0.005  \n",
      "74588         0.001  \n",
      "74589         0.001  \n",
      "74590         0.001  \n",
      "74591         0.001  \n",
      "74592         0.003  \n",
      "74593         0.001  \n",
      "74594         0.001  \n",
      "74595         0.001  \n",
      "74596         0.004  \n"
     ]
    }
   ],
   "source": [
    "for date in creation_date:\n",
    "    print('--------------------------------', date)\n",
    "    print(df[df['creation_date'] == date].shape)\n",
    "    print(df[df['creation_date'] == date].head(10))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(180118, 8)\n",
      "symbol_short     object\n",
      "symbol           object\n",
      "kind             object\n",
      "parent           object\n",
      "level            object\n",
      "parent_short     object\n",
      "title_en         object\n",
      "creation_date    object\n",
      "dtype: object\n",
      "  symbol_short symbol kind parent level parent_short  \\\n",
      "0            A      A    s    CPC     2          CPC   \n",
      "1            B      B    s    CPC     2          CPC   \n",
      "2            C      C    s    CPC     2          CPC   \n",
      "3            D      D    s    CPC     2          CPC   \n",
      "4            E      E    s    CPC     2          CPC   \n",
      "\n",
      "                              title_en creation_date  \n",
      "0                    HUMAN NECESSITIES    2013-01-01  \n",
      "1  PERFORMING OPERATIONS; TRANSPORTING    2013-01-01  \n",
      "2                CHEMISTRY; METALLURGY    2013-01-01  \n",
      "3                      TEXTILES; PAPER    2013-01-01  \n",
      "4                  FIXED CONSTRUCTIONS    2013-01-01  \n"
     ]
    }
   ],
   "source": [
    "df = pd.read_sql_table('cpc', engine, schema='patent_classification')\n",
    "print(df.shape)\n",
    "print(df.dtypes)\n",
    "print(df.head(5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "print(len(df[df.duplicated(subset='symbol')]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A01D0101000000 5\n",
      "A01D0101000000 5\n",
      "A01F0025000000 5\n",
      "A01F0025000000 5\n",
      "A47C0020000000 7\n",
      "A47G0019000000 7\n",
      "A62D0101000000 5\n",
      "A62D0101000000 5\n",
      "A63B0102000000 5\n",
      "A63B0102000000 5\n",
      "B01D0024000000 5\n",
      "B01D0024000000 5\n",
      "B07C0099000000 5\n",
      "B07C0099000000 5\n",
      "B23K0101000000 5\n",
      "B23K0101000000 5\n",
      "B23K0103000000 5\n",
      "B23K0103000000 5\n",
      "B29C0065000000 5\n",
      "B29C0065000000 5\n",
      "B29K0001000000 5\n",
      "B29K0001000000 5\n",
      "B29K0007000000 5\n",
      "B29K0007000000 5\n",
      "B29K0009000000 5\n",
      "B29K0009000000 5\n",
      "B29K0019000000 5\n",
      "B29K0019000000 5\n",
      "B29K0021000000 5\n",
      "B29K0021000000 5\n",
      "B29K0023000000 5\n",
      "B29K0023000000 5\n",
      "B29K0023000000 8\n",
      "B29K0023000000 8\n",
      "B29K0023000000 8\n",
      "B29K0023000000 8\n",
      "B29K0023000000 8\n",
      "B29K0023000000 8\n",
      "B29K0023000000 7\n",
      "B29K0025000000 5\n",
      "B29K0025000000 5\n",
      "B29K0027000000 5\n",
      "B29K0027000000 5\n",
      "B29K0029000000 5\n",
      "B29K0029000000 5\n",
      "B29K0031000000 5\n",
      "B29K0031000000 5\n",
      "B29K0033000000 5\n",
      "B29K0033000000 5\n",
      "B29K0035000000 5\n",
      "B29K0035000000 5\n",
      "B29K0045000000 5\n",
      "B29K0045000000 5\n",
      "B29K0055000000 5\n",
      "B29K0055000000 5\n",
      "B29K0059000000 5\n",
      "B29K0059000000 5\n",
      "B29K0061000000 5\n",
      "B29K0061000000 5\n",
      "B29K0063000000 5\n",
      "B29K0063000000 5\n",
      "B29K0067000000 5\n",
      "B29K0067000000 5\n",
      "B29K0069000000 5\n",
      "B29K0069000000 5\n",
      "B29K0071000000 5\n",
      "B29K0071000000 5\n",
      "B29K0073000000 5\n",
      "B29K0073000000 5\n",
      "B29K0075000000 5\n",
      "B29K0075000000 5\n",
      "B29K0077000000 5\n",
      "B29K0077000000 5\n",
      "B29K0079000000 5\n",
      "B29K0079000000 5\n",
      "B29K0081000000 5\n",
      "B29K0081000000 5\n",
      "B29K0083000000 5\n",
      "B29K0083000000 5\n",
      "B29K0085000000 5\n",
      "B29K0085000000 5\n",
      "B29K0277000000 5\n",
      "B29K0277000000 5\n",
      "B29K0086000000 5\n",
      "B29K0086000000 5\n",
      "B29K0091000000 5\n",
      "B29K0091000000 5\n",
      "B29K0095000000 5\n",
      "B29K0095000000 5\n",
      "B29K0096000000 5\n",
      "B29K0096000000 5\n",
      "B29K0101000000 5\n",
      "B29K0101000000 5\n",
      "B29K0103000000 5\n",
      "B29K0103000000 5\n",
      "B29K0105000000 5\n",
      "B29K0105000000 5\n",
      "B29K0201000000 5\n",
      "B29K0201000000 5\n",
      "B29K0207000000 5\n",
      "B29K0207000000 5\n",
      "B29K0209000000 5\n",
      "B29K0209000000 5\n",
      "B29K0219000000 5\n",
      "B29K0219000000 5\n",
      "B29K0221000000 5\n",
      "B29K0221000000 5\n",
      "B29K0223000000 5\n",
      "B29K0223000000 5\n",
      "B29K0225000000 5\n",
      "B29K0225000000 5\n",
      "B29K0227000000 5\n",
      "B29K0227000000 5\n",
      "B29K0229000000 5\n",
      "B29K0229000000 5\n",
      "B29K0231000000 5\n",
      "B29K0231000000 5\n",
      "B29K0233000000 5\n",
      "B29K0233000000 5\n",
      "B29K0235000000 5\n",
      "B29K0235000000 5\n",
      "B29K0245000000 5\n",
      "B29K0245000000 5\n",
      "B29K0255000000 5\n",
      "B29K0255000000 5\n",
      "B29K0259000000 5\n",
      "B29K0259000000 5\n",
      "B29K0261000000 5\n",
      "B29K0261000000 5\n",
      "B29K0263000000 5\n",
      "B29K0263000000 5\n",
      "B29K0267000000 5\n",
      "B29K0267000000 5\n",
      "B29K0269000000 5\n",
      "B29K0269000000 5\n",
      "B29K0271000000 5\n",
      "B29K0271000000 5\n",
      "B29K0273000000 5\n",
      "B29K0273000000 5\n",
      "B29K0275000000 5\n",
      "B29K0275000000 5\n",
      "B29K0279000000 5\n",
      "B29K0279000000 5\n",
      "B29K0281000000 5\n",
      "B29K0281000000 5\n",
      "B29K0283000000 5\n",
      "B29K0283000000 5\n",
      "B29K0285000000 5\n",
      "B29K0285000000 5\n",
      "B29K0286000000 5\n",
      "B29K0286000000 5\n",
      "B29K0295000000 5\n",
      "B29K0295000000 5\n",
      "B29K0296000000 5\n",
      "B29K0296000000 5\n",
      "B29K0301000000 5\n",
      "B29K0301000000 5\n",
      "B29K0303000000 5\n",
      "B29K0303000000 5\n",
      "B29K0305000000 5\n",
      "B29K0305000000 5\n",
      "B29K0307000000 5\n",
      "B29K0307000000 5\n",
      "B29K0309000000 5\n",
      "B29K0309000000 5\n",
      "B29K0311000000 5\n",
      "B29K0311000000 5\n",
      "B29K0401000000 5\n",
      "B29K0401000000 5\n",
      "B29K0407000000 5\n",
      "B29K0407000000 5\n",
      "B29K0409000000 5\n",
      "B29K0409000000 5\n",
      "B29K0419000000 5\n",
      "B29K0419000000 5\n",
      "B29K0421000000 5\n",
      "B29K0421000000 5\n",
      "B29K0423000000 5\n",
      "B29K0423000000 5\n",
      "B29K0425000000 5\n",
      "B29K0425000000 5\n",
      "B29K0427000000 5\n",
      "B29K0427000000 5\n",
      "B29K0429000000 5\n",
      "B29K0429000000 5\n",
      "B29K0431000000 5\n",
      "B29K0431000000 5\n",
      "B29K0433000000 5\n",
      "B29K0433000000 5\n",
      "B29K0435000000 5\n",
      "B29K0435000000 5\n",
      "B29K0445000000 5\n",
      "B29K0445000000 5\n",
      "B29K0455000000 5\n",
      "B29K0455000000 5\n",
      "B29K0459000000 5\n",
      "B29K0459000000 5\n",
      "B29K0461000000 5\n",
      "B29K0461000000 5\n",
      "B29K0463000000 5\n",
      "B29K0463000000 5\n",
      "B29K0467000000 5\n",
      "B29K0467000000 5\n",
      "B29K0469000000 5\n",
      "B29K0469000000 5\n",
      "B29K0471000000 5\n",
      "B29K0471000000 5\n",
      "B29K0473000000 5\n",
      "B29K0473000000 5\n",
      "B29K0475000000 5\n",
      "B29K0475000000 5\n",
      "B29K0477000000 5\n",
      "B29K0477000000 5\n",
      "B29K0479000000 5\n",
      "B29K0479000000 5\n",
      "B29K0481000000 5\n",
      "B29K0481000000 5\n",
      "B29K0483000000 5\n",
      "B29K0483000000 5\n",
      "B29K0485000000 5\n",
      "B29K0485000000 5\n",
      "B29K0486000000 5\n",
      "B29K0486000000 5\n",
      "B29K0491000000 5\n",
      "B29K0491000000 5\n",
      "B29K0495000000 5\n",
      "B29K0495000000 5\n",
      "B29K0496000000 5\n",
      "B29K0496000000 5\n",
      "B29K0501000000 5\n",
      "B29K0501000000 5\n",
      "B29K0503000000 5\n",
      "B29K0503000000 5\n",
      "B29K0505000000 5\n",
      "B29K0505000000 5\n",
      "B29K0507000000 5\n",
      "B29K0507000000 5\n",
      "B29K0509000000 5\n",
      "B29K0509000000 5\n",
      "B29K0511000000 5\n",
      "B29K0511000000 5\n",
      "B29K0601000000 5\n",
      "B29K0601000000 5\n",
      "B29K0607000000 5\n",
      "B29K0607000000 5\n",
      "B29K0609000000 5\n",
      "B29K0609000000 5\n",
      "B29K0619000000 5\n",
      "B29K0619000000 5\n",
      "B29K0621000000 5\n",
      "B29K0621000000 5\n",
      "B29K0623000000 5\n",
      "B29K0623000000 5\n",
      "B29K0625000000 5\n",
      "B29K0625000000 5\n",
      "B29K0627000000 5\n",
      "B29K0627000000 5\n",
      "B29K0629000000 5\n",
      "B29K0629000000 5\n",
      "B29K0631000000 5\n",
      "B29K0631000000 5\n",
      "B29K0633000000 5\n",
      "B29K0633000000 5\n",
      "B29K0635000000 5\n",
      "B29K0635000000 5\n",
      "B29K0645000000 5\n",
      "B29K0645000000 5\n",
      "B29K0655000000 5\n",
      "B29K0655000000 5\n",
      "B29K0659000000 5\n",
      "B29K0659000000 5\n",
      "B29K0661000000 5\n",
      "B29K0661000000 5\n",
      "B29K0663000000 5\n",
      "B29K0663000000 5\n",
      "B29K0667000000 5\n",
      "B29K0667000000 5\n",
      "B29K0669000000 5\n",
      "B29K0669000000 5\n",
      "B29K0671000000 5\n",
      "B29K0671000000 5\n",
      "B29K0673000000 5\n",
      "B29K0673000000 5\n",
      "B29K0675000000 5\n",
      "B29K0675000000 5\n",
      "B29K0677000000 5\n",
      "B29K0677000000 5\n",
      "B29K0679000000 5\n",
      "B29K0679000000 5\n",
      "B29K0681000000 5\n",
      "B29K0681000000 5\n",
      "B29K0683000000 5\n",
      "B29K0683000000 5\n",
      "B29K0685000000 5\n",
      "B29K0685000000 5\n",
      "B29K0686000000 5\n",
      "B29K0686000000 5\n",
      "B29K0691000000 5\n",
      "B29K0691000000 5\n",
      "B29K0695000000 5\n",
      "B29K0695000000 5\n",
      "B29K0696000000 5\n",
      "B29K0696000000 5\n",
      "B29K0701000000 5\n",
      "B29K0701000000 5\n",
      "B29K0703000000 5\n",
      "B29K0703000000 5\n",
      "B29K0705000000 5\n",
      "B29K0705000000 5\n",
      "B29K0707000000 5\n",
      "B29K0707000000 5\n",
      "B29K0709000000 5\n",
      "B29K0709000000 5\n",
      "B29K0711000000 5\n",
      "B29K0711000000 5\n",
      "B29L0001000000 5\n",
      "B29L0001000000 5\n",
      "B29L0005000000 5\n",
      "B29L0005000000 5\n",
      "B29L0007000000 5\n",
      "B29L0007000000 5\n",
      "B29L0009000000 5\n",
      "B29L0009000000 5\n",
      "B29L0011000000 5\n",
      "B29L0011000000 5\n",
      "B29L0012000000 5\n",
      "B29L0012000000 5\n",
      "B29L0015000000 5\n",
      "B29L0015000000 5\n",
      "B29L0016000000 5\n",
      "B29L0016000000 5\n",
      "B29L0017000000 5\n",
      "B29L0017000000 5\n",
      "B29L0019000000 5\n",
      "B29L0019000000 5\n",
      "B29L0021000000 5\n",
      "B29L0021000000 5\n",
      "B29L0022000000 5\n",
      "B29L0022000000 5\n",
      "B29L0023000000 5\n",
      "B29L0023000000 5\n",
      "B29L0024000000 5\n",
      "B29L0024000000 5\n",
      "B29L0025000000 5\n",
      "B29L0025000000 5\n",
      "B29L0028000000 5\n",
      "B29L0028000000 5\n",
      "B29L0029000000 5\n",
      "B29L0029000000 5\n",
      "B29L0030000000 5\n",
      "B29L0030000000 5\n",
      "B29L0031000000 5\n",
      "B29L0031000000 5\n",
      "B31B0100000000 5\n",
      "B31B0100000000 5\n",
      "B31B0105000000 5\n",
      "B31B0105000000 5\n",
      "B31B0110000000 5\n",
      "B31B0110000000 5\n",
      "B31B0120000000 5\n",
      "B31B0120000000 5\n",
      "B31B0150000000 5\n",
      "B31B0150000000 5\n",
      "B31B0155000000 5\n",
      "B31B0155000000 5\n",
      "B31B0160000000 5\n",
      "B31B0160000000 5\n",
      "B31B0170000000 5\n",
      "B31B0170000000 5\n",
      "B31C0099000000 5\n",
      "B31C0099000000 5\n",
      "B32B0037000000 7\n",
      "B32B0037000000 7\n",
      "B32B0037000000 8\n",
      "B32B0037000000 8\n",
      "B32B0037000000 9\n",
      "B32B0037000000 9\n",
      "B32B0037000000 9\n",
      "B32B0038000000 7\n",
      "B32B0038000000 7\n",
      "B32B0038000000 7\n",
      "B32B0038000000 7\n",
      "B32B0038000000 7\n",
      "B60C0013000000 7\n",
      "B60D0099000000 5\n",
      "B60D0099000000 5\n",
      "B64G0099000000 5\n",
      "B64G0099000000 5\n",
      "B65D0006000000 5\n",
      "B65D0006000000 5\n",
      "B65D0006000000 5\n",
      "B65D0006000000 5\n",
      "B65D0006000000 5\n",
      "B65D0006000000 5\n",
      "B65D0008000000 5\n",
      "B65D0008000000 5\n",
      "B65D0030000000 5\n",
      "B65D0030000000 5\n",
      "B65D0030000000 5\n",
      "B65D0030000000 5\n",
      "C02F0101000000 5\n",
      "C02F0101000000 5\n",
      "C02F0103000000 5\n",
      "C02F0103000000 5\n",
      "C04B0103000000 5\n",
      "C04B0103000000 5\n",
      "C04B0111000000 5\n",
      "C04B0111000000 5\n",
      "C07G0099000000 5\n",
      "C07G0099000000 5\n",
      "C08G0101000000 5\n",
      "C08G0101000000 5\n",
      "C08H0007000000 5\n",
      "C08H0007000000 5\n",
      "C09B0067000000 5\n",
      "C09B0067000000 5\n",
      "C09J0189000000 5\n",
      "C09J0189000000 5\n",
      "C09K0101000000 5\n",
      "C09K0101000000 5\n",
      "C09K0103000000 5\n",
      "C09K0103000000 5\n",
      "C09K0105000000 5\n",
      "C09K0105000000 5\n",
      "C09K0107000000 5\n",
      "C09K0107000000 5\n",
      "C09K0109000000 5\n",
      "C09K0109000000 5\n",
      "C10M0101000000 5\n",
      "C10M0101000000 5\n",
      "C10M0103000000 5\n",
      "C10M0103000000 5\n",
      "C10M0105000000 5\n",
      "C10M0105000000 5\n",
      "C10M0107000000 5\n",
      "C10M0107000000 5\n",
      "C10N0010000000 5\n",
      "C10N0010000000 5\n",
      "C10N0020000000 5\n",
      "C10N0020000000 5\n",
      "C10N0030000000 5\n",
      "C10N0030000000 5\n",
      "C10N0040000000 5\n",
      "C10N0040000000 5\n",
      "C10N0050000000 5\n",
      "C10N0050000000 5\n",
      "C10N0060000000 5\n",
      "C10N0060000000 5\n",
      "C10N0070000000 5\n",
      "C10N0070000000 5\n",
      "C10N0080000000 5\n",
      "C10N0080000000 5\n",
      "C12C0003000000 5\n",
      "C12C0003000000 5\n",
      "C12M0001000000 5\n",
      "C12M0001000000 5\n",
      "C12M0001000000 5\n",
      "C12M0001000000 5\n",
      "C12M0001000000 5\n",
      "C12M0001000000 5\n",
      "C12M0001000000 5\n",
      "C12M0001000000 5\n",
      "C12M0001000000 5\n",
      "C12M0001000000 5\n",
      "C12M0001000000 5\n",
      "C12M0001000000 5\n",
      "C12M0001000000 5\n",
      "C12M0001000000 5\n",
      "C12M0001000000 5\n",
      "C12M0001000000 5\n",
      "C12M0001000000 5\n",
      "C12M0001000000 5\n",
      "C22B0003000000 7\n",
      "C22B0003000000 7\n",
      "C22B0003000000 7\n",
      "D06M0013000000 5\n",
      "D06M0013000000 5\n",
      "D06M0101000000 5\n",
      "D06M0101000000 5\n",
      "D21H0017000000 5\n",
      "D21H0017000000 5\n",
      "D21H0027000000 5\n",
      "D21H0027000000 5\n",
      "D21H0027000000 5\n",
      "D21H0027000000 5\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "E01D0101000000 5\n",
      "E01D0101000000 5\n",
      "E05C0017000000 7\n",
      "E21B0001000000 5\n",
      "E21B0001000000 5\n",
      "E21B0003000000 5\n",
      "E21B0003000000 5\n",
      "E21B0015000000 5\n",
      "E21B0015000000 5\n",
      "E21B0021000000 5\n",
      "E21B0021000000 5\n",
      "F04B0037000000 5\n",
      "F04B0037000000 5\n",
      "F16D0121000000 5\n",
      "F16D0121000000 5\n",
      "F16D0123000000 5\n",
      "F16D0123000000 5\n",
      "F16D0125000000 5\n",
      "F16D0125000000 5\n",
      "F16D0127000000 5\n",
      "F16D0127000000 5\n",
      "F16D0129000000 5\n",
      "F16D0129000000 5\n",
      "F16D0131000000 5\n",
      "F16D0131000000 5\n",
      "F16K0011000000 5\n",
      "F16K0011000000 5\n",
      "F16L0101000000 5\n",
      "F16L0101000000 5\n",
      "F21V0001000000 5\n",
      "F21V0001000000 5\n",
      "F21V0001000000 5\n",
      "F21V0001000000 5\n",
      "F21V0001000000 5\n",
      "F21V0001000000 5\n",
      "F21V0001000000 5\n",
      "F21V0001000000 5\n",
      "F21V0001000000 5\n",
      "F21V0001000000 5\n",
      "F21W0102000000 5\n",
      "F21W0102000000 5\n",
      "F21W0103000000 5\n",
      "F21W0103000000 5\n",
      "F21W0104000000 5\n",
      "F21W0104000000 5\n",
      "F21W0105000000 5\n",
      "F21W0105000000 5\n",
      "F21W0106000000 5\n",
      "F21W0106000000 5\n",
      "F21W0107000000 5\n",
      "F21W0107000000 5\n",
      "F21W0111000000 5\n",
      "F21W0111000000 5\n",
      "F21W0121000000 5\n",
      "F21W0121000000 5\n",
      "F21W0131000000 5\n",
      "F21W0131000000 5\n",
      "F21Y0101000000 5\n",
      "F21Y0101000000 5\n",
      "F21Y0103000000 5\n",
      "F21Y0103000000 5\n",
      "F21Y0105000000 5\n",
      "F21Y0105000000 5\n",
      "F21Y0107000000 5\n",
      "F21Y0107000000 5\n",
      "F21Y0109000000 5\n",
      "F21Y0109000000 5\n",
      "F21Y0111000000 5\n",
      "F21Y0111000000 5\n",
      "F21Y0113000000 5\n",
      "F21Y0113000000 5\n",
      "F21Y0115000000 5\n",
      "F21Y0115000000 5\n",
      "F23B0020000000 5\n",
      "F23B0020000000 5\n",
      "F23B0030000000 5\n",
      "F23B0030000000 5\n",
      "F23B0030000000 5\n",
      "F23B0030000000 5\n",
      "F23B0030000000 5\n",
      "F23B0030000000 5\n",
      "F23B0101000000 5\n",
      "F23B0101000000 5\n",
      "F23B0103000000 5\n",
      "F23B0103000000 5\n",
      "F23D0099000000 5\n",
      "F23D0099000000 5\n",
      "F24F0110000000 5\n",
      "F24F0110000000 5\n",
      "F24F0120000000 5\n",
      "F24F0120000000 5\n",
      "F24F0130000000 5\n",
      "F24F0130000000 5\n",
      "F24F0140000000 5\n",
      "F24F0140000000 5\n",
      "G01C0019000000 5\n",
      "G01C0019000000 5\n",
      "G01N0030000000 8\n",
      "G01N0030000000 8\n",
      "G02B0023000000 7\n",
      "G07D0005000000 5\n",
      "G07D0005000000 5\n",
      "G09F0003000000 7\n",
      "G09F0003000000 7\n",
      "G09F0003000000 7\n",
      "G09F0003000000 7\n",
      "G09F0003000000 7\n",
      "H01L0023000000 5\n",
      "H01L0023000000 5\n",
      "H01R0101000000 5\n",
      "H01R0101000000 5\n",
      "H01R0103000000 5\n",
      "H01R0103000000 5\n",
      "H01R0105000000 5\n",
      "H01R0105000000 5\n",
      "H01R0107000000 5\n",
      "H01R0107000000 5\n",
      "H02P0101000000 5\n",
      "H02P0101000000 5\n",
      "H02P0103000000 5\n",
      "H02P0103000000 5\n",
      "H03F0099000000 5\n",
      "H03F0099000000 5\n",
      "H03H0015000000 7\n",
      "H04J0099000000 5\n",
      "H04J0099000000 5\n",
      "H04N0101000000 5\n",
      "H04N0101000000 5\n",
      "A23G0003340000 6\n",
      "A23G0003340000 6\n",
      "A23G0003340000 6\n",
      "A47C0007620000 8\n",
      "A47C0020000000 8\n",
      "A47G0019020000 8\n",
      "A63B0102000000 6\n",
      "A63B0102000000 6\n",
      "A63B0102000000 6\n",
      "A63B0102000000 6\n",
      "A63B0102000000 6\n",
      "A63B0102000000 6\n",
      "A61B0005145000 6\n",
      "A61B0005145000 6\n",
      "A61B0005145000 6\n",
      "A61B0008080000 6\n",
      "A61B0008080000 6\n",
      "A61B0008080000 6\n",
      "A61B0008080000 6\n",
      "A61B0008080000 6\n",
      "A61B0008080000 6\n",
      "A61B0017225000 8\n",
      "A61B0017225000 8\n",
      "A61B0017225000 8\n",
      "A61C0013107000 6\n",
      "A61C0013107000 6\n",
      "A61C0013107000 6\n",
      "A61C0017060000 6\n",
      "A61C0017060000 6\n",
      "A61C0017060000 6\n",
      "A61F0002000900 7\n",
      "A61F0002000900 7\n",
      "A61F0002000900 7\n",
      "A61F0002000900 7\n",
      "A61F0002000900 7\n"
     ]
    },
    {
     "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-44-f2f0504e107f>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     14\u001b[0m         \u001b[0;31m# trace path by appending successively the corresponding direct parent to a list\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     15\u001b[0m         \u001b[0;32mfor\u001b[0m \u001b[0mi\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mrange\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mlevel\u001b[0m \u001b[0;34m,\u001b[0m \u001b[0;36m2\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[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 16\u001b[0;31m             \u001b[0mgroup\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mgrouped_level\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_group\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mi\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     17\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     18\u001b[0m                 \u001b[0mparent\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mgroup\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mgroup\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'symbol'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'parent'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0miloc\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/core/groupby/groupby.py\u001b[0m in \u001b[0;36mget_group\u001b[0;34m(self, name, obj)\u001b[0m\n\u001b[1;32m    877\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    878\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 879\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mobj\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_take\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0minds\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0maxis\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    880\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    881\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__iter__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\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[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36m_take\u001b[0;34m(self, indices, axis, is_copy)\u001b[0m\n\u001b[1;32m   2787\u001b[0m         new_data = self._data.take(indices,\n\u001b[1;32m   2788\u001b[0m                                    \u001b[0maxis\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_get_block_manager_axis\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0maxis\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[0;32m-> 2789\u001b[0;31m                                    verify=True)\n\u001b[0m\u001b[1;32m   2790\u001b[0m         \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_constructor\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnew_data\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__finalize__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2791\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/core/internals.py\u001b[0m in \u001b[0;36mtake\u001b[0;34m(self, indexer, axis, verify, convert)\u001b[0m\n\u001b[1;32m   4528\u001b[0m         \u001b[0mn\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mshape\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0maxis\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4529\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mconvert\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 4530\u001b[0;31m             \u001b[0mindexer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mmaybe_convert_indices\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mindexer\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mn\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   4531\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4532\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mverify\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/core/indexing.py\u001b[0m in \u001b[0;36mmaybe_convert_indices\u001b[0;34m(indices, n)\u001b[0m\n\u001b[1;32m   2477\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2478\u001b[0m     \u001b[0mmask\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mindices\u001b[0m \u001b[0;34m>=\u001b[0m \u001b[0mn\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m|\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mindices\u001b[0m \u001b[0;34m<\u001b[0m \u001b[0;36m0\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2479\u001b[0;31m     \u001b[0;32mif\u001b[0m \u001b[0mmask\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0many\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[0m\u001b[1;32m   2480\u001b[0m         \u001b[0;32mraise\u001b[0m \u001b[0mIndexError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"indices are out-of-bounds\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2481\u001b[0m     \u001b[0;32mreturn\u001b[0m \u001b[0mindices\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/numpy/core/_methods.py\u001b[0m in \u001b[0;36m_any\u001b[0;34m(a, axis, dtype, out, keepdims)\u001b[0m\n\u001b[1;32m     41\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     42\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0m_any\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mout\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkeepdims\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\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[0;32m---> 43\u001b[0;31m     \u001b[0;32mreturn\u001b[0m \u001b[0mumr_any\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mout\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkeepdims\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     44\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     45\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0m_all\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mout\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkeepdims\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\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[0;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "df = pd.read_sql_table('cpc', engine, schema='patent_classification')\n",
    "grouped_level = df.groupby('level')\n",
    "\n",
    "pathsL = []\n",
    "\n",
    "# iterte df over rows\n",
    "for index, row in df.iterrows():\n",
    "    key = row['symbol']\n",
    "    level = int(row['level'])\n",
    "\n",
    "    # select only symbols of main groups and subgroups\n",
    "    if level >4:\n",
    "        path=[key]\n",
    "        # trace path by appending successively the corresponding direct parent to a list\n",
    "        for i in range(level , 2, -1):\n",
    "            group = grouped_level.get_group(i)\n",
    "            try:\n",
    "                parent = group[group['symbol'] == key]['parent'].iloc[0]\n",
    "                key = parent\n",
    "                path.append(parent)\n",
    "            except:\n",
    "                print(key, level)\n",
    "                pass\n",
    "        path = path[::-1]\n",
    "        pathsL.append(path)\n",
    "paths_df = pd.DataFrame(pathsL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1968, 1974, 1980, 1985, 1990, 1995, 2000, 2006, 2007, 2007, 2008, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019]\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_sql_table('ipc', engine, schema='patent_classification')\n",
    "dateL = list(df['creation_date'].unique())\n",
    "dateL = sorted([int(str(date)[:4]) for date in dateL])\n",
    "print(dateL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(75287, 12)\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_sql_table('ipc', engine, schema='patent_classification')\n",
    "print(df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "19680901 (39374, 12)\n",
      "19740701 (6271, 12)\n",
      "19800101 (5608, 12)\n",
      "19850101 (4187, 12)\n",
      "19900101 (7117, 12)\n",
      "19950101 (2824, 12)\n",
      "20000101 (1658, 12)\n",
      "20060101 (1307, 12)\n",
      "20070101 (58, 12)\n",
      "20071001 (28, 12)\n",
      "20080101 (146, 12)\n",
      "20080401 (25, 12)\n",
      "20090101 (291, 12)\n",
      "20100101 (402, 12)\n",
      "20110101 (493, 12)\n",
      "20120101 (501, 12)\n",
      "20130101 (615, 12)\n",
      "20140101 (626, 12)\n",
      "20150101 (336, 12)\n",
      "20160101 (1027, 12)\n",
      "20170101 (684, 12)\n",
      "20180101 (1040, 12)\n",
      "20190101 (669, 12)\n"
     ]
    }
   ],
   "source": [
    "grouped = df.groupby('creation_date')\n",
    "keys = list(grouped.groups.keys())\n",
    "for key in keys:\n",
    "    group = grouped.get_group(key)\n",
    "    print(key, group.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}