CSVで一致するカラムを参照してコピー.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Untitled63.ipynb",
      "provenance": [],
      "toc_visible": true,
      "authorship_tag": "ABX9TyPCqT1dJzaVGwtlA9t+peLr",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/ykitaguchi77/Colab_Scripts/blob/master/CSV%E3%81%A7%E4%B8%80%E8%87%B4%E3%81%99%E3%82%8B%E3%82%AB%E3%83%A9%E3%83%A0%E3%82%92%E5%8F%82%E7%85%A7%E3%81%97%E3%81%A6%E3%82%B3%E3%83%94%E3%83%BC.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "98xd9oXBglXT",
        "outputId": "8ea7efad-b8e5-457b-dc5b-d45bfcda913f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        }
      },
      "source": [
        "#google driveをcolabolatoryにマウント\n",
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Drive already mounted at /content/drive; to attempt to forcibly remount, call drive.mount(\"/content/drive\", force_remount=True).\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2WKArVad3Coj",
        "outputId": "b98fb538-e5df-48d4-e6d5-148646443052",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 483
        }
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "def openCSV(csv_path):\n",
        "    df = pd.read_csv(csv_path, encoding=\"shift-jis\")\n",
        "    df = df.drop(df.columns[[0]], axis=1) #1列目を削除する\n",
        "    print(df)\n",
        "    return df\n",
        "\n",
        "#CSVを開く （csv1:編集するファイル、csv2:参照するファイル）\n",
        "csv1_path = '/content/drive/My Drive/Grav_bootcamp/Posttrain_result_2.csv'\n",
        "csv2_path = '/content/drive/My Drive/EfficientNet_ImageNet_558_eval.csv'\n",
        "\n",
        "df1, df2 = openCSV(csv1_path), openCSV(csv2_path) #CSVを開く"
      ],
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "     image_name Label  ...  専門医の過半数が正答しAIが誤答 専門医の全員が正解しAIのみが誤答\n",
            "0          57.0  grav  ...               NaN               NaN\n",
            "1          66.0  cont  ...               NaN               NaN\n",
            "2         118.0  cont  ...               NaN               NaN\n",
            "3         122.0  grav  ...               1.0               NaN\n",
            "4         132.0  cont  ...               NaN               NaN\n",
            "..          ...   ...  ...               ...               ...\n",
            "104      7745.0  grav  ...               NaN               NaN\n",
            "105      7763.0  grav  ...               NaN               NaN\n",
            "106      8079.0  grav  ...               NaN               NaN\n",
            "107      8120.0  cont  ...               NaN               NaN\n",
            "108         NaN   NaN  ...              12.0               7.0\n",
            "\n",
            "[109 rows x 13 columns]\n",
            "     image_name Label EfficientNet  model_prob\n",
            "0            57  grav         grav    0.998741\n",
            "1            66  cont         cont    0.062215\n",
            "2           118  cont         cont    0.348831\n",
            "3           122  grav         grav    0.829932\n",
            "4           132  cont         grav    0.856254\n",
            "..          ...   ...          ...         ...\n",
            "103        7726  cont         cont    0.000185\n",
            "104        7745  grav         grav    0.993950\n",
            "105        7763  grav         grav    0.991949\n",
            "106        8079  grav         grav    0.999974\n",
            "107        8120  cont         cont    0.002501\n",
            "\n",
            "[108 rows x 4 columns]\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WMzNM2Ptgah8",
        "outputId": "626109a7-888b-40b0-e8fe-802b7b34ab8d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 372
        }
      },
      "source": [
        "def transpose_value(df1, df2, col1, col2):\n",
        "    k=0\n",
        "    for i in range(len(df1)):\n",
        "        img_num = df1.iloc[k,0]  #CSV1つ目の画像番号を取得\n",
        "        pred = df2[df2['image_name'] == img_num].iloc[0,col2]   #この画像番号に対応する２つ目CSVの行（の●列目）の値を取得\n",
        "        df1.iloc[k, col1] = pred #CSV1つ目の所定の場所に代入\n",
        "        k+=1\n",
        "    return df1\n",
        "\n",
        "#1つ目CSVと2つ目CSVとの列対応を入力\n",
        "col1, col2 = 3, 5 #kitaguchi\n",
        "df1 = transpose_value(df1, df2, col1, col2)\n",
        "col1, col2 = 4, 2 #inoue\n",
        "df1 = transpose_value(df1, df2, col1, col2)\n",
        "col1, col2 = 5, 3 #kohzaki\n",
        "df1 = transpose_value(df1, df2, col1, col2)\n",
        "\n",
        "print(df1)\n",
        "\n",
        "\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "error",
          "ename": "NameError",
          "evalue": "ignored",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-12-ab923b7dd8b5>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     10\u001b[0m \u001b[0;31m#1つ目CSVと2つ目CSVとの列対応を入力\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     11\u001b[0m \u001b[0mcol1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcol2\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;36m3\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m5\u001b[0m \u001b[0;31m#kitaguchi\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 12\u001b[0;31m \u001b[0mdf1\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mtranspose_value\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdf1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdf2\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcol1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcol2\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     13\u001b[0m \u001b[0mcol1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcol2\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;36m4\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m2\u001b[0m \u001b[0;31m#inoue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     14\u001b[0m \u001b[0mdf1\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mtranspose_value\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdf1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdf2\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcol1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcol2\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-12-ab923b7dd8b5>\u001b[0m in \u001b[0;36mtranspose_value\u001b[0;34m(df1, df2, col1, col2)\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mtranspose_value\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdf1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdf2\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcol1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcol2\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      2\u001b[0m     \u001b[0mk\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m     \u001b[0;32mfor\u001b[0m \u001b[0mi\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mrange\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdf\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      4\u001b[0m         \u001b[0mimg_num\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdf1\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0miloc\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m  \u001b[0;31m#CSV1つ目の画像番号を取得\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m         \u001b[0mpred\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdf2\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mdf2\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'image_name'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0mimg_num\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[0mcol2\u001b[0m\u001b[0;34m]\u001b[0m   \u001b[0;31m#この画像番号に対応する２つ目CSVの行（の●列目）の値を取得\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mNameError\u001b[0m: name 'df' is not defined"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cNIlta32-tvY"
      },
      "source": [
        "#**CSVとして保存**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LWTILIBW33_n"
      },
      "source": [
        "csv_path = '/content/drive/My Drive/Grav_bootcamp/Posttrain_result.csv'\n",
        "csv = df1.to_csv(csv_path)\n"
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}