NewTableConverse.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "NewTableConverse.ipynb",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.8.3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BWD_XuvSdvCw"
      },
      "source": [
        "### Импорт библиотек: выполнить 1 раз в начале работы"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AcCkKG7adzaa"
      },
      "source": [
        "!pip install bs4\n",
        "!pip install marshmallow\n",
        "!pip install pandas"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iga73d1Bdzab"
      },
      "source": [
        "import pandas as pd\n",
        "import os\n",
        "import re\n",
        "import traceback\n",
        "import shlex\n",
        "from bs4 import BeautifulSoup\n",
        "from abc import ABCMeta, abstractmethod\n",
        "from marshmallow import Schema, ValidationError, fields, validates, validates_schema\n",
        "from collections.abc import Callable\n",
        "from typing import List, Union\n",
        "from collections import OrderedDict"
      ],
      "execution_count": 28,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "C1lBiV0qdzac"
      },
      "source": [
        "### Подгрузить файлы с интервью (левая панель в google colab)\n",
        "### выполнить все ячейки ниже\n",
        "### выполнить функцию main (см. ниже)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "k8D92m_Adzad"
      },
      "source": [
        "## Converters"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3Ze80Gvzdzad"
      },
      "source": [
        "class Singleton(type):\n",
        "    \"\"\"\n",
        "    Metaclass that ensures only one instance of each converter is present\n",
        "    \"\"\"\n",
        "    _instances = {}\n",
        "    def __call__(cls, *args, **kwargs):\n",
        "        if cls not in cls._instances:\n",
        "            cls._instances[cls] = super(Singleton, cls).__call__(*args, **kwargs)\n",
        "        return cls._instances[cls]"
      ],
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "66c4XEGwdzad"
      },
      "source": [
        "def check_args(func:Callable) -> Callable:\n",
        "    \"\"\"\n",
        "    The decorator ensures that the file passed to the converter\n",
        "    1. exists\n",
        "    2. has the correct extension\n",
        "    3. is correctly named\n",
        "    \"\"\"\n",
        "    def wrapper(self, filename:str) -> Callable:\n",
        "        filepath = os.path.join(self.curdir, filename)\n",
        "        if not os.path.isfile(filepath):\n",
        "            raise OSError(\"File not found\")\n",
        "        filename, extension = os.path.splitext(filename)\n",
        "        if extension != self.old:\n",
        "            raise ValueError(\"Invalid file extension\")\n",
        "        if self.prefix not in filename:\n",
        "            raise ValueError(f\"The filename does not contain the required prefix {self.prefix}\")\n",
        "        return func(self, filename)\n",
        "    return wrapper"
      ],
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cQxF-g-ldzae"
      },
      "source": [
        "class AbstractConverter():\n",
        "    \"\"\"Abstract class for all converters\"\"\"\n",
        "    __slots__ = (\"prefix\", \"curdir\", \"old\", \"new\")\n",
        "    \n",
        "    def __init__(self, old:str, new:str, prefix:str=\"\") -> None:\n",
        "        self.prefix = prefix\n",
        "        self.curdir = os.getcwd()\n",
        "        self.old = \".\" + old\n",
        "        self.new = \".\" + new\n",
        "    \n",
        "    def convert(self, filename:str) -> None:\n",
        "        pass"
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ueb4cI5Pdzaf"
      },
      "source": [
        "class Docx2XmlConverter(AbstractConverter, metaclass=Singleton):\n",
        "    \"\"\"Converter from docx to xml\"\"\"\n",
        "    @check_args\n",
        "    def convert(self, filename:str) -> str:\n",
        "        \"\"\"Convertation method\n",
        "        :param filename str: name of the input file\n",
        "        :returns: name of the produced file\n",
        "        :rtype: str\n",
        "        \"\"\"\n",
        "        file = os.path.join(self.curdir, filename + self.old)\n",
        "        new_path = os.path.join(self.curdir, filename + self.new)\n",
        "        command = \"unzip -oqq %s\" % (shlex.quote(file))\n",
        "        os.system(command)\n",
        "        command_2 = \"mv word/document.xml %s\" % (shlex.quote(new_path))\n",
        "        os.system(command_2)\n",
        "        return filename + self.new\n",
        "    \n",
        "class Doc2XmlConverter(AbstractConverter, metaclass=Singleton):\n",
        "    \"\"\"Converter from doc to docbook xml format\"\"\"\n",
        "    @check_args\n",
        "    def convert(self, filename:str) -> str:\n",
        "        \"\"\"Convertation method\n",
        "        :param filename str: name of the input file\n",
        "        :returns: name of the produced file\n",
        "        :rtype: str\n",
        "        \"\"\"\n",
        "        file = os.path.join(self.curdir, filename + self.old)\n",
        "        new_path = os.path.join(self.curdir, filename + self.new)\n",
        "        command = \"antiword -x db %s > %s\" % (shlex.quote(file),\n",
        "                                              shlex.quote(new_path))\n",
        "        os.system(command)\n",
        "        return filename + self.new\n",
        "    \n",
        "class Doc2TXtConverter(AbstractConverter, metaclass=Singleton):\n",
        "    \"\"\"Converter from doc to txt format\"\"\"\n",
        "    @check_args\n",
        "    def convert(self, filename:str) -> str:\n",
        "        \"\"\"Convertation method\n",
        "        :param filename str: name of the input file\n",
        "        :returns: name of the produced file\n",
        "        :rtype: str\n",
        "        \"\"\"\n",
        "        file = os.path.join(self.curdir, filename + self.old)\n",
        "        new_path = os.path.join(self.curdir, filename + self.new)\n",
        "        command = \"antiword -t %s > %s\" % (shlex.quote(file),\n",
        "                                           shlex.quote(new_path))\n",
        "        os.system(command)\n",
        "        return filename + self.new"
      ],
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_cE-9FWddzag"
      },
      "source": [
        "def convertermaker(old:str, new:str, prefix:str=\"\") -> AbstractConverter:\n",
        "    \"\"\"Factory function for converters\"\"\"\n",
        "    mapping = {\n",
        "        \"docxxml\":Docx2XmlConverter,\n",
        "        \"docxml\":Doc2XmlConverter,\n",
        "        \"doctxt\":Doc2TXtConverter\n",
        "    }\n",
        "    try:\n",
        "        return mapping[old + new](old=old, new=new, prefix=prefix)\n",
        "    except:\n",
        "        raise KeyError(f\"No converter for the file types {old} & {new}\")"
      ],
      "execution_count": 7,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zbTUYolmdzag"
      },
      "source": [
        "## Validator"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OCuQxWaxdzag"
      },
      "source": [
        "def validationmaker(true_vil: Union[List[str], None] = None,\n",
        "                    true_year: Union[str, None] = None) -> Schema:\n",
        "    \"\"\"\n",
        "    Factory function that defines a validation schema\n",
        "    :param true_vil Union[str, None]: village name spelling to be checked\n",
        "    :param true_year Union[str, None]: correct year to be checked\n",
        "    :returns: schema to load the object into\n",
        "    :rtype: Schema\n",
        "    \"\"\"\n",
        "    class ValidationSchema(Schema):\n",
        "        prog = fields.String(required=True)\n",
        "        @validates(\"prog\")\n",
        "        def progval(self, data, **kwargs):\n",
        "            if not bool(\n",
        "            re.match(r\"[IVXa]+\", data)):\n",
        "                raise ValidationError(f\"Incorrect symbols in program: {data}\")\n",
        "        \n",
        "        quest = fields.String(required=True)\n",
        "        @validates(\"quest\")\n",
        "        def questval(self, data, **kwargs):\n",
        "            if not bool(\n",
        "            re.match(r\"[0-9.,а-я]+\", data)):\n",
        "                raise ValidationError(f\"Incorrect symbols in question: {data}\")\n",
        "            if bool(re.search(r\"доп[^.]\", data)) == True:\n",
        "                raise ValidationError(f\"Missing '.' in question: {data}\")\n",
        "        \n",
        "        vil = fields.String(required=True)\n",
        "        @validates(\"vil\")\n",
        "        def vilval(self, data, **kwargs):\n",
        "            if true_vil is not None and data not in true_vil:\n",
        "                raise ValidationError(f\"Incorrect village: {data}\")\n",
        "        \n",
        "        year = fields.String(required=True)\n",
        "        @validates(\"year\")\n",
        "        def yearval(self, data, **kwargs):\n",
        "            if true_year is not None and data != str(true_year):\n",
        "                raise ValidationError(f\"Incorrect year: {data}\")\n",
        "        \n",
        "        sob1 = fields.String(required=True)\n",
        "        sob2 = fields.String()\n",
        "        sob3 = fields.String()\n",
        "        sob4 = fields.String()\n",
        "\n",
        "        main = fields.String(required=True)\n",
        "        @validates(\"main\")\n",
        "        def val_main(self, data, **kwargs):\n",
        "            if re.search(r\"@[А-Я]{2,3}, [А-Я]{2,3}$\", data):\n",
        "                raise ValidationError(f\"Possible problems in text: {data}\")\n",
        "        \n",
        "        inf1 = fields.String(required=True)\n",
        "        inf2 = fields.String()\n",
        "        inf3 = fields.String()\n",
        "        inf4 = fields.String()\n",
        "\n",
        "        @validates_schema\n",
        "        def codeval(self, data, **kwargs):\n",
        "            for key in [\"sob1\", \"sob2\", \"sob3\", \"sob4\", \"inf1\", \"inf2\", \"inf3\", \"inf4\"]:\n",
        "                if key in data:\n",
        "                    if bool(re.search(r\"[^А-Я\\-0-9?]\", data[key])) == True:\n",
        "                        raise ValidationError(f\"Incorrect symbols in {key} code {data[key]}. Check if informant code is present.\")\n",
        "    return ValidationSchema()"
      ],
      "execution_count": 19,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "39xGb0vLdzai"
      },
      "source": [
        "## Parsers"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "G0E0eaNTdzaj"
      },
      "source": [
        "def extension_check(extension:str) -> Callable:\n",
        "    \"\"\"\n",
        "    The decorator ensures that the file passed to the converter\n",
        "    1. exists\n",
        "    2. has the correct extension (txt/xml)\n",
        "    \"\"\"\n",
        "    def inner_checker(func:Callable) -> Callable:\n",
        "        def wrapper(_, file, *args, **kwargs) -> Callable:\n",
        "            _, file_ext = os.path.splitext(file)\n",
        "            if not file_ext == extension:\n",
        "                raise OSError(f\"Invalid file type: {file_ext}\")\n",
        "            filepath = os.path.join(os.getcwd(), file)\n",
        "            if not os.path.isfile(filepath):\n",
        "                raise OSError(f\"File not found: {file}\")\n",
        "            return func(_, file, *args, **kwargs)\n",
        "        return wrapper\n",
        "    return inner_checker"
      ],
      "execution_count": 20,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UzHV6zm4dzak"
      },
      "source": [
        "class Parser(metaclass=ABCMeta):\n",
        "    @abstractmethod\n",
        "    def parse(self, file:str) -> None:\n",
        "        pass\n",
        "    \n",
        "    @staticmethod\n",
        "    def separate(lines: List[str]) -> List[List[str]]:\n",
        "        \"\"\"\n",
        "        Splits the lines into groups\n",
        "        :param List[str] lines: all lines from a file\n",
        "        :return: A list of line groups, corresponding to db entries\n",
        "        :rtype: List[List[str]]\n",
        "        \"\"\"\n",
        "        corr_lines = lines.copy()\n",
        "        expr = re.compile(r\"^[ ]+$\")\n",
        "        for i in range(len(corr_lines)): # transform lines with spaces only into empty lines\n",
        "            if re.match(expr, corr_lines[i]):\n",
        "                corr_lines[i] = \"\"\n",
        "        big_line = \"#\".join(corr_lines).strip().strip(\"#\")\n",
        "        big_line = re.sub(r\"###+\", \"##\", big_line) # Replace sequences of empty lines with one empty line\n",
        "        big_line = big_line.replace(\"\\\\\\\\\", \"\\\\\") # Replace double accent marks with single ones\n",
        "        raws = [i.split(\"#\") for i in big_line.split(\"##\")] # Split by empty lines\n",
        "        return raws\n",
        "    \n",
        "    @staticmethod\n",
        "    def analyze(raw:List[str]) -> dict:\n",
        "        \"\"\"\n",
        "        Turn a single line group into a db entry\n",
        "        :param List[str] raw: 5 lines that make a db entry\n",
        "        :returns: A dict that can be used to create a pandas dataframe\n",
        "        :rtype: dict\n",
        "        \"\"\"\n",
        "        output_dict = OrderedDict()\n",
        "        prog, quest = re.split(r\"[–\\-]\", raw[0])\n",
        "        output_dict[\"prog\"], output_dict[\"quest\"] = prog.strip().replace(\"Х\", \"X\"), quest.strip()\n",
        "        vil, year = re.split(r\"['’ʼ‘]\", raw[1])\n",
        "        output_dict[\"vil\"], output_dict[\"year\"] = vil.strip(), year.strip()\n",
        "        authors = raw[2].split(\", \")\n",
        "        for idx, key in enumerate([\"sob1\", \"sob2\", \"sob3\", \"sob4\"]):\n",
        "            if idx < len(authors):\n",
        "                output_dict[key] = authors[idx].strip()\n",
        "            else:\n",
        "                output_dict[key] = \"\"\n",
        "        output_dict[\"main\"] = \"@\".join(raw[3:-1])\n",
        "        informers = raw[-1].split(\", \")\n",
        "        for idx, key in enumerate([\"inf1\", \"inf2\", \"inf3\", \"inf4\"]):\n",
        "            if idx < len(informers):\n",
        "                output_dict[key] = informers[idx].strip()\n",
        "            else:\n",
        "                output_dict[key] = \"\"        \n",
        "        return output_dict\n",
        "    \n",
        "    def main(self, file:str, validation_schema:Schema) -> None:\n",
        "        all_lines = self.parse(file)\n",
        "        try:\n",
        "            raw_objects = self.separate(all_lines)\n",
        "            objects = []\n",
        "            for obj in raw_objects:\n",
        "                if len(obj) > 3:\n",
        "                    try:\n",
        "                        objects.append(self.analyze(obj))\n",
        "                    except Exception as err:\n",
        "                        traceback.print_exception(type(err), err, err.__traceback__)\n",
        "                        raise Exception(f\"Error parsing object\\n{obj}\")\n",
        "            _ = validation_schema.load(objects, many=True) if validation_schema else None\n",
        "        except Exception as e:      \n",
        "            print(\"----\")\n",
        "            print(e)\n",
        "            raise Exception(f\"Error processing file {file} (see above)\")\n",
        "        filename, _ = os.path.splitext(file)\n",
        "        dataframe = pd.DataFrame.from_records(objects)\n",
        "        dataframe.to_excel(\"{}.xlsx\".format(filename), index=False)\n",
        "    \n",
        "class TxtParser(Parser):\n",
        "    @extension_check(\".txt\")\n",
        "    def parse(self, file:str) -> List[str]:\n",
        "        with open(file, encoding=\"utf-8-sig\") as content:\n",
        "            text = content.read()\n",
        "        lines = text.splitlines()\n",
        "        return lines\n",
        "        \n",
        "class DocBookParser(Parser):\n",
        "    @extension_check(\".xml\")\n",
        "    def parse(self, file:str) -> List[str]:\n",
        "        with open(file, encoding=\"utf-8\") as content:\n",
        "            text = content.read()\n",
        "        bs = BeautifulSoup(text, \"html.parser\")\n",
        "        paras = [p.text for p in bs.find_all(\"para\")]\n",
        "        return paras\n",
        "    \n",
        "class DocXmlParser(Parser):\n",
        "    @extension_check(\".xml\")\n",
        "    def parse(self, file:str) -> List[str]:\n",
        "        with open(file, encoding=\"utf-8\") as content:\n",
        "            text = content.read()\n",
        "        text = text.replace(\"<w:br/>\", \"@\")\n",
        "        bs = BeautifulSoup(text, \"html.parser\")\n",
        "        paras = [line for p in bs.find_all(\"w:p\") for line in p.text.split(\"@\")]\n",
        "        return paras"
      ],
      "execution_count": 29,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Ee5I4Usmdzam"
      },
      "source": [
        "def parsermaker(option:str) -> Parser:\n",
        "    \"\"\"Factory function to produce a parser of the required type\n",
        "    :param option str: type of parser\n",
        "    :returns: parser of the specified type\n",
        "    :rtype: Parser\n",
        "    \"\"\"\n",
        "    mapping = {\n",
        "        \"txt\":TxtParser,\n",
        "        \"dbxml\":DocBookParser,\n",
        "        \"docxml\":DocXmlParser\n",
        "    }\n",
        "    try:\n",
        "        return mapping[option]()\n",
        "    except:\n",
        "        raise KeyError(f\"No parser for the type {option}\")    "
      ],
      "execution_count": 30,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P0JpZYDYdzam"
      },
      "source": [
        "## Функция main\n",
        "### Принимает на вход расширение (doc/docx)\n",
        "### Обрабатывает все файлы в данном расширении в текущей папке\n",
        "### Для ошибочных файлов пишет ошибку и пропускает"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "A3zjc1vldzam"
      },
      "source": [
        "def main(extension:str,\n",
        "         true_year:Union[int, None]=None,\n",
        "         true_vil:Union[List[str], None]=None) -> None:\n",
        "    \"\"\"Function to process the word files in the current directory\n",
        "    :param extension str: doc or docx\n",
        "    :param true_year int: year to validate (if needed)\n",
        "    :param true_vil str: village name to validate (if needed)\n",
        "    \"\"\"\n",
        "    antiword_check = 'if ! [ -x \"$(command -v antiword)\"];then apt install antiword;fi;'\n",
        "    os.system(command=antiword_check)\n",
        "    validator = validationmaker(true_vil=true_vil, true_year=true_year)\n",
        "    # validator = None\n",
        "    if extension == \"docx\":\n",
        "        converter = convertermaker(\"docx\", \"xml\")\n",
        "        parser = parsermaker(\"docxml\")\n",
        "    elif extension == \"doc\":\n",
        "        converter = convertermaker(\"doc\", \"txt\")\n",
        "        parser = parsermaker(\"txt\")\n",
        "    else:\n",
        "        raise ValueError(f\"Incorrect extension: {extension}\")\n",
        "    for file in os.listdir(os.getcwd()):\n",
        "        if file.endswith(extension):\n",
        "            try:\n",
        "                newfile = converter.convert(filename=file)\n",
        "                parser.main(file=newfile, validation_schema=validator)\n",
        "            except Exception as e:\n",
        "                print(e)\n",
        "                continue"
      ],
      "execution_count": 31,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CvgPtB_7dzan"
      },
      "source": [
        "### Следующую ячейку выполнить один раз\n",
        "### Скачать готовые таблицы на компьютер (левая панель в google colab)\n",
        "### Если есть ошибочные файлы:\n",
        "#### 1) Отредактировать\n",
        "#### 2) Выполнить ячейку для удаления всех текущих файлов (вторая снизу)\n",
        "#### 3) Подгрузить файлы заново, выполнить и скачать таблицы"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_FjFo4yUdzan"
      },
      "source": [
        "main(\"docx\", true_vil=[\"Карасуль\", \"Карасуль-Октябрьский\", \"Карасуль-Никольский\"], true_year=2022)"
      ],
      "execution_count": 37,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wDqiaqncdzao"
      },
      "source": [
        "!rm -r ./*.xml"
      ],
      "execution_count": 36,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZasP1qCnbxfq"
      },
      "source": [
        "### Упаковать в архив все таблицы"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hz-o1UOybXJN"
      },
      "source": [
        "!zip -r /content/file.zip . -i ./*.xlsx"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "frLFuR1Zb6I5"
      },
      "source": [
        "### Скачать архив"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7o6lKz_YbdgQ",
        "outputId": "c7950402-56a1-421a-a066-5a97e25befa9",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 17
        }
      },
      "source": [
        "from google.colab import files\n",
        "files.download(\"/content/file.zip\")"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "application/javascript": [
              "\n",
              "    async function download(id, filename, size) {\n",
              "      if (!google.colab.kernel.accessAllowed) {\n",
              "        return;\n",
              "      }\n",
              "      const div = document.createElement('div');\n",
              "      const label = document.createElement('label');\n",
              "      label.textContent = `Downloading \"${filename}\": `;\n",
              "      div.appendChild(label);\n",
              "      const progress = document.createElement('progress');\n",
              "      progress.max = size;\n",
              "      div.appendChild(progress);\n",
              "      document.body.appendChild(div);\n",
              "\n",
              "      const buffers = [];\n",
              "      let downloaded = 0;\n",
              "\n",
              "      const channel = await google.colab.kernel.comms.open(id);\n",
              "      // Send a message to notify the kernel that we're ready.\n",
              "      channel.send({})\n",
              "\n",
              "      for await (const message of channel.messages) {\n",
              "        // Send a message to notify the kernel that we're ready.\n",
              "        channel.send({})\n",
              "        if (message.buffers) {\n",
              "          for (const buffer of message.buffers) {\n",
              "            buffers.push(buffer);\n",
              "            downloaded += buffer.byteLength;\n",
              "            progress.value = downloaded;\n",
              "          }\n",
              "        }\n",
              "      }\n",
              "      const blob = new Blob(buffers, {type: 'application/binary'});\n",
              "      const a = document.createElement('a');\n",
              "      a.href = window.URL.createObjectURL(blob);\n",
              "      a.download = filename;\n",
              "      div.appendChild(a);\n",
              "      a.click();\n",
              "      div.remove();\n",
              "    }\n",
              "  "
            ],
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "application/javascript": [
              "download(\"download_60ce9b9a-81b3-4625-ade3-9756887afc88\", \"file.zip\", 714469)"
            ],
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_9qh-BGfPONB"
      },
      "source": [
        "### Дополнительно"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zZxqGv67PNWK",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 17
        },
        "outputId": "a49d78b1-aba7-47bc-cc82-64327c24292e"
      },
      "source": [
        "def merger():\n",
        "    targets = [i for i in os.listdir(os.getcwd()) if i.endswith(\".xlsx\")]\n",
        "    initial = pd.read_excel(targets[0], header=None, skiprows=1)\n",
        "    if len(targets) > 1:\n",
        "        for target in targets[1:]:\n",
        "            df = pd.read_excel(target, header=None, skiprows=1)\n",
        "            initial = pd.concat([initial, df], axis=0)\n",
        "    return initial\n",
        "merged = merger()\n",
        "merged.to_excel(\"merged.xlsx\", index=False)\n",
        "from google.colab import files\n",
        "files.download(\"/content/merged.xlsx\")"
      ],
      "execution_count": 38,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ],
            "application/javascript": [
              "\n",
              "    async function download(id, filename, size) {\n",
              "      if (!google.colab.kernel.accessAllowed) {\n",
              "        return;\n",
              "      }\n",
              "      const div = document.createElement('div');\n",
              "      const label = document.createElement('label');\n",
              "      label.textContent = `Downloading \"${filename}\": `;\n",
              "      div.appendChild(label);\n",
              "      const progress = document.createElement('progress');\n",
              "      progress.max = size;\n",
              "      div.appendChild(progress);\n",
              "      document.body.appendChild(div);\n",
              "\n",
              "      const buffers = [];\n",
              "      let downloaded = 0;\n",
              "\n",
              "      const channel = await google.colab.kernel.comms.open(id);\n",
              "      // Send a message to notify the kernel that we're ready.\n",
              "      channel.send({})\n",
              "\n",
              "      for await (const message of channel.messages) {\n",
              "        // Send a message to notify the kernel that we're ready.\n",
              "        channel.send({})\n",
              "        if (message.buffers) {\n",
              "          for (const buffer of message.buffers) {\n",
              "            buffers.push(buffer);\n",
              "            downloaded += buffer.byteLength;\n",
              "            progress.value = downloaded;\n",
              "          }\n",
              "        }\n",
              "      }\n",
              "      const blob = new Blob(buffers, {type: 'application/binary'});\n",
              "      const a = document.createElement('a');\n",
              "      a.href = window.URL.createObjectURL(blob);\n",
              "      a.download = filename;\n",
              "      div.appendChild(a);\n",
              "      a.click();\n",
              "      div.remove();\n",
              "    }\n",
              "  "
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ],
            "application/javascript": [
              "download(\"download_7f2af773-8227-4643-b301-23afd5001ea4\", \"merged.xlsx\", 1508032)"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "w-lVZUjLdzao"
      },
      "source": [
        "## old part"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VEdkmlrDgw_e"
      },
      "source": [
        "# class fileTable:\n",
        "#     def __init__(self, name, num):\n",
        "#         self.name = name\n",
        "#         self.num = num\n",
        "\n",
        "#     def create(self):\n",
        "#         new = open(file=self.name, mode=\"r\", encoding=\"utf-8\")\n",
        "#         indexes = [i for i in range(self.num)]\n",
        "#         self.table = pd.DataFrame(index=indexes, \n",
        "#                                     columns=[\"программа\", \"вопрос\", \\\n",
        "#                                                         \"село\", \"год\", \\\n",
        "#                                                         \"соб.1\", \"соб.2\", \\\n",
        "#                                                         \"соб.3\", \"соб.4\", \\\n",
        "#                                                         \"текст\", \"инф.1\", \"инф.2\", \\\n",
        "#                                                         \"инф.3\", \"инф.4\", \"\"])\n",
        "#         self.informants = set()\n",
        "#         self.researchers = set()\n",
        "#         for i in indexes:\n",
        "#             line1 = new.readline().replace(\"\\n\",\"\").split(\"-\")\n",
        "#             self.table.iloc[i, 0]=line1[0]\n",
        "#             self.table.iloc[i, 1]=line1[1]\n",
        "#             del line1\n",
        "#             line1 = new.readline().replace(\"\\n\",\"\")\n",
        "#             if \"'\" in line1:\n",
        "#                 line1 = line1.split(\"'\") #обрабатываем 2 типа апострофов\n",
        "#             elif \"’\" in line1:\n",
        "#                 line1 = line1.split(\"’\")\n",
        "#             self.table.iloc[i, 2]=line1[0]\n",
        "#             self.table.iloc[i, 3]=line1[1]\n",
        "#             del line1\n",
        "#             line1 = new.readline().replace(\"\\n\",\"\").split(\", \")\n",
        "#             for n in range(len(line1)):\n",
        "#                 if not line1[n] in self.researchers:\n",
        "#                     self.researchers.add(line1[n])\n",
        "#                 col = 4 + n\n",
        "#                 self.table.iloc[i, col]=line1[n]\n",
        "#             del col\n",
        "#             del line1\n",
        "#             newline = \"\"\n",
        "#             line = \"\"\n",
        "#             while newline != \"\\n\":\n",
        "#                 newline = new.readline()\n",
        "#                 newline2 = newline.replace(\"\\n\",\"\")\n",
        "#                 line = line + newline2 + \"@\"\n",
        "#                 if line[-2:] == \"@@\":\n",
        "#                     break\n",
        "#             line = line[:len(line)-2]\n",
        "#             line2 = line[line.rfind(\"@\") + 1:].split(\", \") #extracting informants\n",
        "#             line = line[:line.rfind(\"@\")] # selecting text deleted +1\n",
        "#             for n in range(len(line2)):\n",
        "#                 if not line2[n] in self.informants:\n",
        "#                     self.informants.add(line2[n])\n",
        "#                 col = 9 + n\n",
        "#                 self.table.iloc[i, col]=line2[n]#appending informants\n",
        "#             self.table.iloc[i,8] = line #appending text to the column\n",
        "#         new.close()\n",
        "\n",
        "#     def visualize(self):\n",
        "#         return self.table\n",
        "\n",
        "#     def showHead(self):\n",
        "#         print(self.table.head(10))\n",
        "\n",
        "#     def write(self):\n",
        "#         excelName = self.name.rstrip(\".txt\") + \".xlsx\"\n",
        "#         self.table.to_excel(excelName, index=False)\n",
        "#         del excelName\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FhZqkDEZgcN4"
      },
      "source": [
        "# files = [x for x in filter(lambda x : \".txt\" in x, os.listdir(os.getcwd()))]\n",
        "# for file in files:\n",
        "#     filename = file;\n",
        "#     num = int(re.search(r'\\d+', filename).group(0)) if re.search(r'\\d+', filename) else 0\n",
        "#     try:\n",
        "#         this = fileTable(filename, num)\n",
        "#         this.create()\n",
        "#         this.write()\n",
        "#     except Exception as e:\n",
        "#         print(f\"Exception has occured while processing the file {filename}:\")\n",
        "#         print(e)\n",
        "#     del this"
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}