輸送手段簡易区分_v2.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "輸送手段簡易区分_v2.ipynb",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "ランタイム→すべてのセルを実行→[ファイル選択]ボタンをクリックし、CSVファイル（実績明細****r1.csv）を読込む。\\\n",
        "明細のワードから、Air,Sea,Truckを類推し、それぞれの金額合計を求め、最終的に \n",
        "全運賃に対する比率を求めています。（集計結果として表示します。）\\\n",
        "※AirとSeaで重複選択されたレコード金額のみSeaから除くなどしていますが、あくまでざっくりです。\\\n",
        "結果をcsv保存する場合は[csv_output：□]にチェックを入れて実行してください。\n"
      ],
      "metadata": {
        "id": "GCWDoRgAa0aD"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#@title ライブラリのインポート\n",
        "import pandas as pd # 基本ライブラリ\n",
        "import numpy as np # 基本ライブラリ\n",
        "import matplotlib.pyplot as plt # グラフ描画用\n",
        "import seaborn as sns; sns.set() # グラフ描画用\n",
        "import warnings\n",
        "warnings.filterwarnings('ignore')\n",
        "import string as str"
      ],
      "metadata": {
        "cellView": "form",
        "id": "UdA0-0CVBUHR"
      },
      "execution_count": 1,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": 34,
      "metadata": {
        "colab": {
          "resources": {
            "http://localhost:8080/nbextensions/google.colab/files.js": {
              "data": "Ly8gQ29weXJpZ2h0IDIwMTcgR29vZ2xlIExMQwovLwovLyBMaWNlbnNlZCB1bmRlciB0aGUgQXBhY2hlIExpY2Vuc2UsIFZlcnNpb24gMi4wICh0aGUgIkxpY2Vuc2UiKTsKLy8geW91IG1heSBub3QgdXNlIHRoaXMgZmlsZSBleGNlcHQgaW4gY29tcGxpYW5jZSB3aXRoIHRoZSBMaWNlbnNlLgovLyBZb3UgbWF5IG9idGFpbiBhIGNvcHkgb2YgdGhlIExpY2Vuc2UgYXQKLy8KLy8gICAgICBodHRwOi8vd3d3LmFwYWNoZS5vcmcvbGljZW5zZXMvTElDRU5TRS0yLjAKLy8KLy8gVW5sZXNzIHJlcXVpcmVkIGJ5IGFwcGxpY2FibGUgbGF3IG9yIGFncmVlZCB0byBpbiB3cml0aW5nLCBzb2Z0d2FyZQovLyBkaXN0cmlidXRlZCB1bmRlciB0aGUgTGljZW5zZSBpcyBkaXN0cmlidXRlZCBvbiBhbiAiQVMgSVMiIEJBU0lTLAovLyBXSVRIT1VUIFdBUlJBTlRJRVMgT1IgQ09ORElUSU9OUyBPRiBBTlkgS0lORCwgZWl0aGVyIGV4cHJlc3Mgb3IgaW1wbGllZC4KLy8gU2VlIHRoZSBMaWNlbnNlIGZvciB0aGUgc3BlY2lmaWMgbGFuZ3VhZ2UgZ292ZXJuaW5nIHBlcm1pc3Npb25zIGFuZAovLyBsaW1pdGF0aW9ucyB1bmRlciB0aGUgTGljZW5zZS4KCi8qKgogKiBAZmlsZW92ZXJ2aWV3IEhlbHBlcnMgZm9yIGdvb2dsZS5jb2xhYiBQeXRob24gbW9kdWxlLgogKi8KKGZ1bmN0aW9uKHNjb3BlKSB7CmZ1bmN0aW9uIHNwYW4odGV4dCwgc3R5bGVBdHRyaWJ1dGVzID0ge30pIHsKICBjb25zdCBlbGVtZW50ID0gZG9jdW1lbnQuY3JlYXRlRWxlbWVudCgnc3BhbicpOwogIGVsZW1lbnQudGV4dENvbnRlbnQgPSB0ZXh0OwogIGZvciAoY29uc3Qga2V5IG9mIE9iamVjdC5rZXlzKHN0eWxlQXR0cmlidXRlcykpIHsKICAgIGVsZW1lbnQuc3R5bGVba2V5XSA9IHN0eWxlQXR0cmlidXRlc1trZXldOwogIH0KICByZXR1cm4gZWxlbWVudDsKfQoKLy8gTWF4IG51bWJlciBvZiBieXRlcyB3aGljaCB3aWxsIGJlIHVwbG9hZGVkIGF0IGEgdGltZS4KY29uc3QgTUFYX1BBWUxPQURfU0laRSA9IDEwMCAqIDEwMjQ7CgpmdW5jdGlvbiBfdXBsb2FkRmlsZXMoaW5wdXRJZCwgb3V0cHV0SWQpIHsKICBjb25zdCBzdGVwcyA9IHVwbG9hZEZpbGVzU3RlcChpbnB1dElkLCBvdXRwdXRJZCk7CiAgY29uc3Qgb3V0cHV0RWxlbWVudCA9IGRvY3VtZW50LmdldEVsZW1lbnRCeUlkKG91dHB1dElkKTsKICAvLyBDYWNoZSBzdGVwcyBvbiB0aGUgb3V0cHV0RWxlbWVudCB0byBtYWtlIGl0IGF2YWlsYWJsZSBmb3IgdGhlIG5leHQgY2FsbAogIC8vIHRvIHVwbG9hZEZpbGVzQ29udGludWUgZnJvbSBQeXRob24uCiAgb3V0cHV0RWxlbWVudC5zdGVwcyA9IHN0ZXBzOwoKICByZXR1cm4gX3VwbG9hZEZpbGVzQ29udGludWUob3V0cHV0SWQpOwp9CgovLyBUaGlzIGlzIHJvdWdobHkgYW4gYXN5bmMgZ2VuZXJhdG9yIChub3Qgc3VwcG9ydGVkIGluIHRoZSBicm93c2VyIHlldCksCi8vIHdoZXJlIHRoZXJlIGFyZSBtdWx0aXBsZSBhc3luY2hyb25vdXMgc3RlcHMgYW5kIHRoZSBQeXRob24gc2lkZSBpcyBnb2luZwovLyB0byBwb2xsIGZvciBjb21wbGV0aW9uIG9mIGVhY2ggc3RlcC4KLy8gVGhpcyB1c2VzIGEgUHJvbWlzZSB0byBibG9jayB0aGUgcHl0aG9uIHNpZGUgb24gY29tcGxldGlvbiBvZiBlYWNoIHN0ZXAsCi8vIHRoZW4gcGFzc2VzIHRoZSByZXN1bHQgb2YgdGhlIHByZXZpb3VzIHN0ZXAgYXMgdGhlIGlucHV0IHRvIHRoZSBuZXh0IHN0ZXAuCmZ1bmN0aW9uIF91cGxvYWRGaWxlc0NvbnRpbnVlKG91dHB1dElkKSB7CiAgY29uc3Qgb3V0cHV0RWxlbWVudCA9IGRvY3VtZW50LmdldEVsZW1lbnRCeUlkKG91dHB1dElkKTsKICBjb25zdCBzdGVwcyA9IG91dHB1dEVsZW1lbnQuc3RlcHM7CgogIGNvbnN0IG5leHQgPSBzdGVwcy5uZXh0KG91dHB1dEVsZW1lbnQubGFzdFByb21pc2VWYWx1ZSk7CiAgcmV0dXJuIFByb21pc2UucmVzb2x2ZShuZXh0LnZhbHVlLnByb21pc2UpLnRoZW4oKHZhbHVlKSA9PiB7CiAgICAvLyBDYWNoZSB0aGUgbGFzdCBwcm9taXNlIHZhbHVlIHRvIG1ha2UgaXQgYXZhaWxhYmxlIHRvIHRoZSBuZXh0CiAgICAvLyBzdGVwIG9mIHRoZSBnZW5lcmF0b3IuCiAgICBvdXRwdXRFbGVtZW50Lmxhc3RQcm9taXNlVmFsdWUgPSB2YWx1ZTsKICAgIHJldHVybiBuZXh0LnZhbHVlLnJlc3BvbnNlOwogIH0pOwp9CgovKioKICogR2VuZXJhdG9yIGZ1bmN0aW9uIHdoaWNoIGlzIGNhbGxlZCBiZXR3ZWVuIGVhY2ggYXN5bmMgc3RlcCBvZiB0aGUgdXBsb2FkCiAqIHByb2Nlc3MuCiAqIEBwYXJhbSB7c3RyaW5nfSBpbnB1dElkIEVsZW1lbnQgSUQgb2YgdGhlIGlucHV0IGZpbGUgcGlja2VyIGVsZW1lbnQuCiAqIEBwYXJhbSB7c3RyaW5nfSBvdXRwdXRJZCBFbGVtZW50IElEIG9mIHRoZSBvdXRwdXQgZGlzcGxheS4KICogQHJldHVybiB7IUl0ZXJhYmxlPCFPYmplY3Q+fSBJdGVyYWJsZSBvZiBuZXh0IHN0ZXBzLgogKi8KZnVuY3Rpb24qIHVwbG9hZEZpbGVzU3RlcChpbnB1dElkLCBvdXRwdXRJZCkgewogIGNvbnN0IGlucHV0RWxlbWVudCA9IGRvY3VtZW50LmdldEVsZW1lbnRCeUlkKGlucHV0SWQpOwogIGlucHV0RWxlbWVudC5kaXNhYmxlZCA9IGZhbHNlOwoKICBjb25zdCBvdXRwdXRFbGVtZW50ID0gZG9jdW1lbnQuZ2V0RWxlbWVudEJ5SWQob3V0cHV0SWQpOwogIG91dHB1dEVsZW1lbnQuaW5uZXJIVE1MID0gJyc7CgogIGNvbnN0IHBpY2tlZFByb21pc2UgPSBuZXcgUHJvbWlzZSgocmVzb2x2ZSkgPT4gewogICAgaW5wdXRFbGVtZW50LmFkZEV2ZW50TGlzdGVuZXIoJ2NoYW5nZScsIChlKSA9PiB7CiAgICAgIHJlc29sdmUoZS50YXJnZXQuZmlsZXMpOwogICAgfSk7CiAgfSk7CgogIGNvbnN0IGNhbmNlbCA9IGRvY3VtZW50LmNyZWF0ZUVsZW1lbnQoJ2J1dHRvbicpOwogIGlucHV0RWxlbWVudC5wYXJlbnRFbGVtZW50LmFwcGVuZENoaWxkKGNhbmNlbCk7CiAgY2FuY2VsLnRleHRDb250ZW50ID0gJ0NhbmNlbCB1cGxvYWQnOwogIGNvbnN0IGNhbmNlbFByb21pc2UgPSBuZXcgUHJvbWlzZSgocmVzb2x2ZSkgPT4gewogICAgY2FuY2VsLm9uY2xpY2sgPSAoKSA9PiB7CiAgICAgIHJlc29sdmUobnVsbCk7CiAgICB9OwogIH0pOwoKICAvLyBXYWl0IGZvciB0aGUgdXNlciB0byBwaWNrIHRoZSBmaWxlcy4KICBjb25zdCBmaWxlcyA9IHlpZWxkIHsKICAgIHByb21pc2U6IFByb21pc2UucmFjZShbcGlja2VkUHJvbWlzZSwgY2FuY2VsUHJvbWlzZV0pLAogICAgcmVzcG9uc2U6IHsKICAgICAgYWN0aW9uOiAnc3RhcnRpbmcnLAogICAgfQogIH07CgogIGNhbmNlbC5yZW1vdmUoKTsKCiAgLy8gRGlzYWJsZSB0aGUgaW5wdXQgZWxlbWVudCBzaW5jZSBmdXJ0aGVyIHBpY2tzIGFyZSBub3QgYWxsb3dlZC4KICBpbnB1dEVsZW1lbnQuZGlzYWJsZWQgPSB0cnVlOwoKICBpZiAoIWZpbGVzKSB7CiAgICByZXR1cm4gewogICAgICByZXNwb25zZTogewogICAgICAgIGFjdGlvbjogJ2NvbXBsZXRlJywKICAgICAgfQogICAgfTsKICB9CgogIGZvciAoY29uc3QgZmlsZSBvZiBmaWxlcykgewogICAgY29uc3QgbGkgPSBkb2N1bWVudC5jcmVhdGVFbGVtZW50KCdsaScpOwogICAgbGkuYXBwZW5kKHNwYW4oZmlsZS5uYW1lLCB7Zm9udFdlaWdodDogJ2JvbGQnfSkpOwogICAgbGkuYXBwZW5kKHNwYW4oCiAgICAgICAgYCgke2ZpbGUudHlwZSB8fCAnbi9hJ30pIC0gJHtmaWxlLnNpemV9IGJ5dGVzLCBgICsKICAgICAgICBgbGFzdCBtb2RpZmllZDogJHsKICAgICAgICAgICAgZmlsZS5sYXN0TW9kaWZpZWREYXRlID8gZmlsZS5sYXN0TW9kaWZpZWREYXRlLnRvTG9jYWxlRGF0ZVN0cmluZygpIDoKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgJ24vYSd9IC0gYCkpOwogICAgY29uc3QgcGVyY2VudCA9IHNwYW4oJzAlIGRvbmUnKTsKICAgIGxpLmFwcGVuZENoaWxkKHBlcmNlbnQpOwoKICAgIG91dHB1dEVsZW1lbnQuYXBwZW5kQ2hpbGQobGkpOwoKICAgIGNvbnN0IGZpbGVEYXRhUHJvbWlzZSA9IG5ldyBQcm9taXNlKChyZXNvbHZlKSA9PiB7CiAgICAgIGNvbnN0IHJlYWRlciA9IG5ldyBGaWxlUmVhZGVyKCk7CiAgICAgIHJlYWRlci5vbmxvYWQgPSAoZSkgPT4gewogICAgICAgIHJlc29sdmUoZS50YXJnZXQucmVzdWx0KTsKICAgICAgfTsKICAgICAgcmVhZGVyLnJlYWRBc0FycmF5QnVmZmVyKGZpbGUpOwogICAgfSk7CiAgICAvLyBXYWl0IGZvciB0aGUgZGF0YSB0byBiZSByZWFkeS4KICAgIGxldCBmaWxlRGF0YSA9IHlpZWxkIHsKICAgICAgcHJvbWlzZTogZmlsZURhdGFQcm9taXNlLAogICAgICByZXNwb25zZTogewogICAgICAgIGFjdGlvbjogJ2NvbnRpbnVlJywKICAgICAgfQogICAgfTsKCiAgICAvLyBVc2UgYSBjaHVua2VkIHNlbmRpbmcgdG8gYXZvaWQgbWVzc2FnZSBzaXplIGxpbWl0cy4gU2VlIGIvNjIxMTU2NjAuCiAgICBsZXQgcG9zaXRpb24gPSAwOwogICAgZG8gewogICAgICBjb25zdCBsZW5ndGggPSBNYXRoLm1pbihmaWxlRGF0YS5ieXRlTGVuZ3RoIC0gcG9zaXRpb24sIE1BWF9QQVlMT0FEX1NJWkUpOwogICAgICBjb25zdCBjaHVuayA9IG5ldyBVaW50OEFycmF5KGZpbGVEYXRhLCBwb3NpdGlvbiwgbGVuZ3RoKTsKICAgICAgcG9zaXRpb24gKz0gbGVuZ3RoOwoKICAgICAgY29uc3QgYmFzZTY0ID0gYnRvYShTdHJpbmcuZnJvbUNoYXJDb2RlLmFwcGx5KG51bGwsIGNodW5rKSk7CiAgICAgIHlpZWxkIHsKICAgICAgICByZXNwb25zZTogewogICAgICAgICAgYWN0aW9uOiAnYXBwZW5kJywKICAgICAgICAgIGZpbGU6IGZpbGUubmFtZSwKICAgICAgICAgIGRhdGE6IGJhc2U2NCwKICAgICAgICB9LAogICAgICB9OwoKICAgICAgbGV0IHBlcmNlbnREb25lID0gZmlsZURhdGEuYnl0ZUxlbmd0aCA9PT0gMCA/CiAgICAgICAgICAxMDAgOgogICAgICAgICAgTWF0aC5yb3VuZCgocG9zaXRpb24gLyBmaWxlRGF0YS5ieXRlTGVuZ3RoKSAqIDEwMCk7CiAgICAgIHBlcmNlbnQudGV4dENvbnRlbnQgPSBgJHtwZXJjZW50RG9uZX0lIGRvbmVgOwoKICAgIH0gd2hpbGUgKHBvc2l0aW9uIDwgZmlsZURhdGEuYnl0ZUxlbmd0aCk7CiAgfQoKICAvLyBBbGwgZG9uZS4KICB5aWVsZCB7CiAgICByZXNwb25zZTogewogICAgICBhY3Rpb246ICdjb21wbGV0ZScsCiAgICB9CiAgfTsKfQoKc2NvcGUuZ29vZ2xlID0gc2NvcGUuZ29vZ2xlIHx8IHt9OwpzY29wZS5nb29nbGUuY29sYWIgPSBzY29wZS5nb29nbGUuY29sYWIgfHwge307CnNjb3BlLmdvb2dsZS5jb2xhYi5fZmlsZXMgPSB7CiAgX3VwbG9hZEZpbGVzLAogIF91cGxvYWRGaWxlc0NvbnRpbnVlLAp9Owp9KShzZWxmKTsK",
              "ok": true,
              "headers": [
                [
                  "content-type",
                  "application/javascript"
                ]
              ],
              "status": 200,
              "status_text": ""
            }
          },
          "base_uri": "https://localhost:8080/",
          "height": 78
        },
        "cellView": "form",
        "id": "sqevyHtuAXdg",
        "outputId": "73de831f-036f-47f6-bdd3-819663a74813"
      },
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "\n",
              "     <input type=\"file\" id=\"files-07b5e95b-43d2-412e-8bd1-5dc39eb7fe7e\" name=\"files[]\" multiple disabled\n",
              "        style=\"border:none\" />\n",
              "     <output id=\"result-07b5e95b-43d2-412e-8bd1-5dc39eb7fe7e\">\n",
              "      Upload widget is only available when the cell has been executed in the\n",
              "      current browser session. Please rerun this cell to enable.\n",
              "      </output>\n",
              "      <script src=\"/nbextensions/google.colab/files.js\"></script> "
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Saving 実績明細一覧2018r1.csv to 実績明細一覧2018r1 (1).csv\n"
          ]
        }
      ],
      "source": [
        "#@title CSV読込み\n",
        "from google.colab import files\n",
        "uploaded = files.upload()#Upload\n",
        "target = list(uploaded.keys())[0]\n",
        "df = pd.read_csv(target)"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#@title 「明細テキスト」記載文字から輸送手段を簡易区分 → Air, Sea, Truck列に結果格納\n",
        "df['Air']=df['明細テキスト'].str.contains('Air',case=False)|df['明細テキスト'].str.contains('DHL',case=False)|df['明細テキスト'].str.contains('FedEx',case=False)|df['明細テキスト'].str.contains('海外送金',case=False)|df['明細テキスト'].str.contains('エス',case=False)|df['明細テキスト'].str.contains('台湾',case=False)|df['明細テキスト'].str.contains('ベトナム',case=False)|df['明細テキスト'].str.contains('インド',case=False)|df['明細テキスト'].str.contains('エス',case=False)\n",
        "df['Sea']=df['明細テキスト'].str.contains('sea',case=False)|df['明細テキスト'].str.contains('hub',case=False)|df['明細テキスト'].str.contains('NECL-HK',case=False)|df['明細テキスト'].str.contains('日通',case=False)\n",
        "df['Truck']=df['明細テキスト'].str.contains('ヤマト',case=False)|df['明細テキスト'].str.contains('郵便',case=False)|df['明細テキスト'].str.contains('営業所',case=False)|df['明細テキスト'].str.contains('福山',case=False)|df['明細テキスト'].str.contains('共立',case=False)|df['明細テキスト'].str.contains('佐川',case=False)|df['明細テキスト'].str.contains('西濃',case=False)|df['明細テキスト'].str.contains('びわイチ',case=False)|df['明細テキスト'].str.contains('通運',case=False)|df['明細テキスト'].str.contains('サンオクト',case=False)|df['明細テキスト'].str.contains('オフロム',case=False)|df['明細テキスト'].str.contains('セイノー',case=False)|df['明細テキスト'].str.contains('マイクロテック',case=False)|df['明細テキスト'].str.contains('ロックサービス',case=False)|df['明細テキスト'].str.contains('郵便',case=False)|df['明細テキスト'].str.contains('営業所',case=False)|df['明細テキスト'].str.contains('福山',case=False)|df['明細テキスト'].str.contains('共立',case=False)|df['明細テキスト'].str.contains('佐川',case=False)|df['明細テキスト'].str.contains('西濃',case=False)|df['明細テキスト'].str.contains('びわイチ',case=False)|df['明細テキスト'].str.contains('通運',case=False)|df['明細テキスト'].str.contains('サンオクト',case=False)|df['明細テキスト'].str.contains('オフロム',case=False)|df['明細テキスト'].str.contains('セイノー',case=False)|df['明細テキスト'].str.contains('マイクロテック',case=False)|df['明細テキスト'].str.contains('ジオ',case=False)|df['明細テキスト'].str.contains('共立',case=False)|df['明細テキスト'].str.contains('佐川',case=False)|df['明細テキスト'].str.contains('西濃',case=False)|df['明細テキスト'].str.contains('びわイチ',case=False)|df['明細テキスト'].str.contains('通運',case=False)|df['明細テキスト'].str.contains('サンオクト',case=False)|df['明細テキスト'].str.contains('オフロム',case=False)|df['明細テキスト'].str.contains('セイノー',case=False)|df['明細テキスト'].str.contains('マイクロテック',case=False)|df['明細テキスト'].str.contains('ロックサービス',case=False)|df['明細テキスト'].str.contains('郵便',case=False)|df['明細テキスト'].str.contains('営業所',case=False)|df['明細テキスト'].str.contains('福山',case=False)|df['明細テキスト'].str.contains('共立',case=False)|df['明細テキスト'].str.contains('佐川',case=False)|df['明細テキスト'].str.contains('西濃',case=False)|df['明細テキスト'].str.contains('びわイチ',case=False)|df['明細テキスト'].str.contains('通運',case=False)|df['明細テキスト'].str.contains('サンオクト',case=False)|df['明細テキスト'].str.contains('オフロム',case=False)|df['明細テキスト'].str.contains('セイノー',case=False)|df['明細テキスト'].str.contains('マイクロテック',case=False)|df['明細テキスト'].str.contains('因幡',case=False)\n",
        "df"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 884
        },
        "cellView": "form",
        "id": "y71Crj2sMfzV",
        "outputId": "ddb353c4-dd7d-4848-89a5-e48e5c188da8"
      },
      "execution_count": 35,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "           伝票起票日  会計期間        伝票番号  勘定グループ 勘定グループ名                   原価センタ  \\\n",
              "0       2018/1/5     1  9900004196    5304      運賃   A1000000000013 照明S&M課   \n",
              "1       2018/1/5     1  9900034190    5304      運賃   A1000000000013 照明S&M課   \n",
              "2       2018/1/5     1  9900034203    5304      運賃   A1000000000013 照明S&M課   \n",
              "3       2018/1/5     1  9900034698    5304      運賃   A1000000000013 照明S&M課   \n",
              "4       2018/1/5     1  9900034972    5304      運賃   A1000000000013 照明S&M課   \n",
              "...          ...   ...         ...     ...     ...                     ...   \n",
              "5320  2018/12/31    12  9900127486    7304      運賃    A1000000000024 SEC配賦   \n",
              "5321  2018/12/31    12  9900127486    7304      運賃    A1000000000024 SEC配賦   \n",
              "5322  2018/12/31    12  9900127486    7304      運賃    A1000000000024 SEC配賦   \n",
              "5323  2018/12/31    12  9900127486    7304      運賃    A1000000000024 SEC配賦   \n",
              "5324  2018/12/31    12  9900127493    5304      運賃  A1000000000048 VDS 開発課   \n",
              "\n",
              "      WBSコード 製商品企画コード Fコスト  稟議書番号  \\\n",
              "0        NaN      NaN  NaN    NaN   \n",
              "1        NaN      DZZ  NaN    NaN   \n",
              "2        NaN      DZZ  NaN    NaN   \n",
              "3        NaN      DZZ  NaN    NaN   \n",
              "4        NaN      DZZ  NaN    NaN   \n",
              "...      ...      ...  ...    ...   \n",
              "5320     NaN      NaN  NaN    NaN   \n",
              "5321     NaN      NaN  NaN    NaN   \n",
              "5322     NaN      NaN  NaN    NaN   \n",
              "5323     NaN      NaN  NaN    NaN   \n",
              "5324     NaN      NaN  NaN    NaN   \n",
              "\n",
              "                                             明細テキスト       金額    Air    Sea  \\\n",
              "0             本　社/㈱ハンワ　着払　因幡電機産業㈱大阪物流センターよりLA-22LED      900  False  False   \n",
              "1                           因幡電機産業株式会社 TEN:20460906     -500  False  False   \n",
              "2                           因幡電機産業株式会社 TEN:20460908     -500  False  False   \n",
              "3                        新明電材株式会社　営業本部 TEN:20460948     -600  False  False   \n",
              "4                      株式会社　タカショーデジテック TEN:20460990     -500  False  False   \n",
              "...                                             ...      ...    ...    ...   \n",
              "5320         38【1/21払】 NECL-HK-1812A0097  USD184.22    20816  False   True   \n",
              "5321             39【1/21払】 NECL-HK-1812A0098  USD73     8249  False   True   \n",
              "5322          3【1/21払】 NECL-HK-1811S0558  USD396.15    44764  False   True   \n",
              "5323  64【1/21払】 NECL-HK-1812A0328 Air運賃 USD10469.28  1183028   True   True   \n",
              "5324                              TAIWAN MITACHI/運賃     2616  False  False   \n",
              "\n",
              "      Truck  \n",
              "0      True  \n",
              "1      True  \n",
              "2      True  \n",
              "3     False  \n",
              "4     False  \n",
              "...     ...  \n",
              "5320  False  \n",
              "5321  False  \n",
              "5322  False  \n",
              "5323  False  \n",
              "5324  False  \n",
              "\n",
              "[5325 rows x 15 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-82478401-1bec-4dbb-b7a5-19512c191651\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>伝票起票日</th>\n",
              "      <th>会計期間</th>\n",
              "      <th>伝票番号</th>\n",
              "      <th>勘定グループ</th>\n",
              "      <th>勘定グループ名</th>\n",
              "      <th>原価センタ</th>\n",
              "      <th>WBSコード</th>\n",
              "      <th>製商品企画コード</th>\n",
              "      <th>Fコスト</th>\n",
              "      <th>稟議書番号</th>\n",
              "      <th>明細テキスト</th>\n",
              "      <th>金額</th>\n",
              "      <th>Air</th>\n",
              "      <th>Sea</th>\n",
              "      <th>Truck</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2018/1/5</td>\n",
              "      <td>1</td>\n",
              "      <td>9900004196</td>\n",
              "      <td>5304</td>\n",
              "      <td>運賃</td>\n",
              "      <td>A1000000000013 照明S&amp;M課</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>本　社/㈱ハンワ　着払　因幡電機産業㈱大阪物流センターよりLA-22LED</td>\n",
              "      <td>900</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2018/1/5</td>\n",
              "      <td>1</td>\n",
              "      <td>9900034190</td>\n",
              "      <td>5304</td>\n",
              "      <td>運賃</td>\n",
              "      <td>A1000000000013 照明S&amp;M課</td>\n",
              "      <td>NaN</td>\n",
              "      <td>DZZ</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>因幡電機産業株式会社 TEN:20460906</td>\n",
              "      <td>-500</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2018/1/5</td>\n",
              "      <td>1</td>\n",
              "      <td>9900034203</td>\n",
              "      <td>5304</td>\n",
              "      <td>運賃</td>\n",
              "      <td>A1000000000013 照明S&amp;M課</td>\n",
              "      <td>NaN</td>\n",
              "      <td>DZZ</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>因幡電機産業株式会社 TEN:20460908</td>\n",
              "      <td>-500</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2018/1/5</td>\n",
              "      <td>1</td>\n",
              "      <td>9900034698</td>\n",
              "      <td>5304</td>\n",
              "      <td>運賃</td>\n",
              "      <td>A1000000000013 照明S&amp;M課</td>\n",
              "      <td>NaN</td>\n",
              "      <td>DZZ</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>新明電材株式会社　営業本部 TEN:20460948</td>\n",
              "      <td>-600</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2018/1/5</td>\n",
              "      <td>1</td>\n",
              "      <td>9900034972</td>\n",
              "      <td>5304</td>\n",
              "      <td>運賃</td>\n",
              "      <td>A1000000000013 照明S&amp;M課</td>\n",
              "      <td>NaN</td>\n",
              "      <td>DZZ</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>株式会社　タカショーデジテック TEN:20460990</td>\n",
              "      <td>-500</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5320</th>\n",
              "      <td>2018/12/31</td>\n",
              "      <td>12</td>\n",
              "      <td>9900127486</td>\n",
              "      <td>7304</td>\n",
              "      <td>運賃</td>\n",
              "      <td>A1000000000024 SEC配賦</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>38【1/21払】 NECL-HK-1812A0097  USD184.22</td>\n",
              "      <td>20816</td>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5321</th>\n",
              "      <td>2018/12/31</td>\n",
              "      <td>12</td>\n",
              "      <td>9900127486</td>\n",
              "      <td>7304</td>\n",
              "      <td>運賃</td>\n",
              "      <td>A1000000000024 SEC配賦</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>39【1/21払】 NECL-HK-1812A0098  USD73</td>\n",
              "      <td>8249</td>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5322</th>\n",
              "      <td>2018/12/31</td>\n",
              "      <td>12</td>\n",
              "      <td>9900127486</td>\n",
              "      <td>7304</td>\n",
              "      <td>運賃</td>\n",
              "      <td>A1000000000024 SEC配賦</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>3【1/21払】 NECL-HK-1811S0558  USD396.15</td>\n",
              "      <td>44764</td>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5323</th>\n",
              "      <td>2018/12/31</td>\n",
              "      <td>12</td>\n",
              "      <td>9900127486</td>\n",
              "      <td>7304</td>\n",
              "      <td>運賃</td>\n",
              "      <td>A1000000000024 SEC配賦</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>64【1/21払】 NECL-HK-1812A0328 Air運賃 USD10469.28</td>\n",
              "      <td>1183028</td>\n",
              "      <td>True</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5324</th>\n",
              "      <td>2018/12/31</td>\n",
              "      <td>12</td>\n",
              "      <td>9900127493</td>\n",
              "      <td>5304</td>\n",
              "      <td>運賃</td>\n",
              "      <td>A1000000000048 VDS 開発課</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>TAIWAN MITACHI/運賃</td>\n",
              "      <td>2616</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5325 rows × 15 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-82478401-1bec-4dbb-b7a5-19512c191651')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-82478401-1bec-4dbb-b7a5-19512c191651 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-82478401-1bec-4dbb-b7a5-19512c191651');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 35
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#@title 集計\n",
        "Air_count = df[df.Air == True].Air.sum()\n",
        "Sea_count = df[df.Sea == True].Sea.sum()\n",
        "Truck_count = df[df.Truck == True].Truck.sum()\n",
        "Other_count =len(df) - Air_count - Sea_count - Truck_count\n",
        "\n",
        "Air_cost = df[df.Air == True].金額.sum()\n",
        "Pre_Sea_cost = df[df.Sea == True].金額.sum()\n",
        "Air_Sea_cost = df[(df.Air == True) & (df.Sea == True)].金額.sum()\n",
        "Sea_cost = Pre_Sea_cost - Air_Sea_cost\n",
        "Truck_cost = df[df.Truck == True].金額.sum()\n",
        "Cost_sum = df['金額'].values.sum()\n",
        "Other_cost = Cost_sum - Air_cost - Sea_cost - Truck_cost\n",
        "Cost_sum2 = Air_cost + Sea_cost + Truck_cost\n",
        "\n",
        "Air_ratio = Air_cost/Cost_sum\n",
        "Sea_ratio = Sea_cost/Cost_sum\n",
        "Truck_ratio = Truck_cost/Cost_sum\n",
        "Other_ratio = Other_cost/Cost_sum\n",
        "\n",
        "Air_ratio2 = Air_cost/Cost_sum2\n",
        "Sea_ratio2 = Sea_cost/Cost_sum2\n",
        "Truck_ratio2 = Truck_cost/Cost_sum2\n",
        "\n",
        "\n",
        "print('Summary')\n",
        "print('---------------------------------------------------------------')\n",
        "print('■Air：\\t\\t',df[df.Air == True].Air.sum(),'\\t\\t件')\n",
        "print('■Sea：\\t\\t',df[df.Sea == True].Sea.sum(),'\\t\\t件')\n",
        "print('■Truck：\\t',df[df.Truck == True].Truck.sum(),'\\t\\t件')\n",
        "print('■Other：\\t',Other_count,'\\t\\t件')\n",
        "print('■Total：\\t',len(df),'\\t\\t件')\n",
        "print('---------------------------------------------------------------')\n",
        "print('■Air：\\t\\t',df[df.Air == True].金額.sum(),'\\t円')\n",
        "print('■Sea：\\t\\t',df[df.Sea == True].金額.sum(),'\\t円')\n",
        "print('■Truck：\\t',df[df.Truck == True].金額.sum(),'\\t円')\n",
        "print('■Other：\\t',Other_cost,'\\t円')\n",
        "print('■Total：\\t',df['金額'].values.sum(),'\\t円')\n",
        "print('---------------------------------------------------------------')\n",
        "print('■Air：\\t\\t',\"{:.1%}\".format(Air_ratio))\n",
        "print('■Sea：\\t\\t',\"{:.1%}\".format(Sea_ratio))\n",
        "print('■Truck：\\t',\"{:.1%}\".format(Truck_ratio))\n",
        "print('■Other：\\t',\"{:.1%}\".format(Other_ratio))\n",
        "print('■Total：\\t',\"{:.1%}\".format(Cost_sum/Cost_sum))\n",
        "print('---------------------------------------------------------------')\n",
        "print('[推定] 輸送手段金額比率')\n",
        "print('■Air：\\t\\t',\"{:.1%}\".format(Air_ratio2))\n",
        "print('■Sea：\\t\\t',\"{:.1%}\".format(Sea_ratio2))\n",
        "print('■Truck：\\t',\"{:.1%}\".format(Truck_ratio2))\n",
        "print('---------------------------------------------------------------')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "cellView": "form",
        "id": "mMx-5OpK_i5M",
        "outputId": "4bcfb5e7-9411-4091-adda-a1e342708a2e"
      },
      "execution_count": 36,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Summary\n",
            "---------------------------------------------------------------\n",
            "■Air：\t\t 576 \t\t件\n",
            "■Sea：\t\t 1515 \t\t件\n",
            "■Truck：\t 1713 \t\t件\n",
            "■Other：\t 1521 \t\t件\n",
            "■Total：\t 5325 \t\t件\n",
            "---------------------------------------------------------------\n",
            "■Air：\t\t 26460095 \t円\n",
            "■Sea：\t\t 115590013 \t円\n",
            "■Truck：\t 45869974 \t円\n",
            "■Other：\t 85745088 \t円\n",
            "■Total：\t 261907675 \t円\n",
            "---------------------------------------------------------------\n",
            "■Air：\t\t 10.1%\n",
            "■Sea：\t\t 39.6%\n",
            "■Truck：\t 17.5%\n",
            "■Other：\t 32.7%\n",
            "■Total：\t 100.0%\n",
            "---------------------------------------------------------------\n",
            "[推定] 輸送手段金額比率\n",
            "■Air：\t\t 15.0%\n",
            "■Sea：\t\t 58.9%\n",
            "■Truck：\t 26.0%\n",
            "---------------------------------------------------------------\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#@title 輸送区分結果 csv出力（☑ ＝実行）\n",
        "\n",
        "csv_output = False #@param {type:\"boolean\"}\n",
        "\n",
        "#csv出力\n",
        "if csv_output == True:\n",
        "  df.to_csv('transport_class_data.csv',index=False)\n",
        "  from google.colab import files\n",
        "  files.download('transport_class_data.csv')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 17
        },
        "cellView": "form",
        "id": "ZMQ2CKN5X1DM",
        "outputId": "92008922-1b40-481e-91f7-e681e1bc994f"
      },
      "execution_count": 58,
      "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_1fa6f947-9b2e-4b98-b2c6-9c17fdcab058\", \"transport_class_data.csv\", 830188)"
            ]
          },
          "metadata": {}
        }
      ]
    }
  ]
}