pandas_quiz.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "XfmMtuxKYRoO"
   },
   "source": [
    "# Pandas確認課題\n",
    "\n",
    "このPandas確認問題は、データサイエンス100本ノックの問題で最低限必要な問題を抜粋したものですが、[Introduction_to_Pandas](./11_Introduction_to_Pandas.ipynb) に掲載されていない機能が必要な問題もあります。\n",
    "初めて触るライブラリを調べながら使うというのはよくある光景です。この課題では皆さんにもそれに挑戦していただきます。  \n",
    "ヒントとして検索キーワードなどを載せておくので、自力で調べながら解いてみましょう。  \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "9ktBdoeha1jL"
   },
   "source": [
    "## 必要モジュールのインポート\n",
    "\n",
    "この問題で使うモジュールをインポートします．"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "2IhABxEEaq19"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "llLJCZTEa5Rb"
   },
   "source": [
    "## データの読み込み"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "uN-SvpE_a50E"
   },
   "outputs": [],
   "source": [
    "df_customer = pd.read_csv('https://raw.githubusercontent.com/The-Japan-DataScientist-Society/100knocks-preprocess/master/docker/work/data/customer.csv')\n",
    "df_product = pd.read_csv('https://raw.githubusercontent.com/The-Japan-DataScientist-Society/100knocks-preprocess/master/docker/work/data/product.csv')\n",
    "df_receipt = pd.read_csv('https://raw.githubusercontent.com/The-Japan-DataScientist-Society/100knocks-preprocess/master/docker/work/data/receipt.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "xm5FMZJobU3Y"
   },
   "source": [
    "---\n",
    "## 問1. 条件抽出\n",
    "> P-006: レシート明細データフレーム「df_receipt」から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上数量（quantity）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ。\n",
    "> - 顧客ID（customer_id）が\"CS018205000001\"\n",
    "> - 売上金額（amount）が1,000以上または売上数量（quantity）が5以上"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 198
    },
    "colab_type": "code",
    "id": "KSOu6AWabVaD",
    "outputId": "91faaad5-57ca-4a67-c046-195afa4d21df"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sales_ymd</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>product_cd</th>\n",
       "      <th>quantity</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>20180911</td>\n",
       "      <td>CS018205000001</td>\n",
       "      <td>P071401012</td>\n",
       "      <td>1</td>\n",
       "      <td>2200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9843</th>\n",
       "      <td>20180414</td>\n",
       "      <td>CS018205000001</td>\n",
       "      <td>P060104007</td>\n",
       "      <td>6</td>\n",
       "      <td>600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21110</th>\n",
       "      <td>20170614</td>\n",
       "      <td>CS018205000001</td>\n",
       "      <td>P050206001</td>\n",
       "      <td>5</td>\n",
       "      <td>990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68117</th>\n",
       "      <td>20190226</td>\n",
       "      <td>CS018205000001</td>\n",
       "      <td>P071401020</td>\n",
       "      <td>1</td>\n",
       "      <td>2200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72254</th>\n",
       "      <td>20180911</td>\n",
       "      <td>CS018205000001</td>\n",
       "      <td>P071401005</td>\n",
       "      <td>1</td>\n",
       "      <td>1100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       sales_ymd     customer_id  product_cd  quantity  amount\n",
       "36      20180911  CS018205000001  P071401012         1    2200\n",
       "9843    20180414  CS018205000001  P060104007         6     600\n",
       "21110   20170614  CS018205000001  P050206001         5     990\n",
       "68117   20190226  CS018205000001  P071401020         1    2200\n",
       "72254   20180911  CS018205000001  P071401005         1    1100"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_extracted=df_receipt.loc[(df_receipt[\"customer_id\"] == \"CS018205000001\") & ((df_receipt[\"amount\"] >= 1000) | (df_receipt[\"quantity\"] >= 5)), [\"sales_ymd\", \"customer_id\", \"product_cd\", \"quantity\", \"amount\"]]\n",
    "df_extracted"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "7WsKq3Voj0LF"
   },
   "source": [
    "---\n",
    "## 問2. ソート\n",
    "> P-18: 顧客データフレーム（df_customer）を生年月日（birth_day）で若い順にソートし、先頭5件を全項目表示せよ。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 694
    },
    "colab_type": "code",
    "id": "uLdYmXgdjxaw",
    "outputId": "7a064e8c-4db3-4350-a212-688cb9b49980"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>customer_name</th>\n",
       "      <th>gender_cd</th>\n",
       "      <th>gender</th>\n",
       "      <th>birth_day</th>\n",
       "      <th>age</th>\n",
       "      <th>postal_cd</th>\n",
       "      <th>address</th>\n",
       "      <th>application_store_cd</th>\n",
       "      <th>application_date</th>\n",
       "      <th>status_cd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15639</th>\n",
       "      <td>CS035114000004</td>\n",
       "      <td>大村 美里</td>\n",
       "      <td>1</td>\n",
       "      <td>女性</td>\n",
       "      <td>2007-11-25</td>\n",
       "      <td>11</td>\n",
       "      <td>156-0053</td>\n",
       "      <td>東京都世田谷区桜**********</td>\n",
       "      <td>S13035</td>\n",
       "      <td>20150619</td>\n",
       "      <td>6-20091205-6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7468</th>\n",
       "      <td>CS022103000002</td>\n",
       "      <td>福山 はじめ</td>\n",
       "      <td>9</td>\n",
       "      <td>不明</td>\n",
       "      <td>2007-10-02</td>\n",
       "      <td>11</td>\n",
       "      <td>249-0006</td>\n",
       "      <td>神奈川県逗子市逗子**********</td>\n",
       "      <td>S14022</td>\n",
       "      <td>20160909</td>\n",
       "      <td>0-00000000-0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10745</th>\n",
       "      <td>CS002113000009</td>\n",
       "      <td>柴田 真悠子</td>\n",
       "      <td>1</td>\n",
       "      <td>女性</td>\n",
       "      <td>2007-09-17</td>\n",
       "      <td>11</td>\n",
       "      <td>184-0014</td>\n",
       "      <td>東京都小金井市貫井南町**********</td>\n",
       "      <td>S13002</td>\n",
       "      <td>20160304</td>\n",
       "      <td>0-00000000-0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19811</th>\n",
       "      <td>CS004115000014</td>\n",
       "      <td>松井 京子</td>\n",
       "      <td>1</td>\n",
       "      <td>女性</td>\n",
       "      <td>2007-08-09</td>\n",
       "      <td>11</td>\n",
       "      <td>165-0031</td>\n",
       "      <td>東京都中野区上鷺宮**********</td>\n",
       "      <td>S13004</td>\n",
       "      <td>20161120</td>\n",
       "      <td>1-20081231-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7039</th>\n",
       "      <td>CS002114000010</td>\n",
       "      <td>山内 遥</td>\n",
       "      <td>1</td>\n",
       "      <td>女性</td>\n",
       "      <td>2007-06-03</td>\n",
       "      <td>11</td>\n",
       "      <td>184-0015</td>\n",
       "      <td>東京都小金井市貫井北町**********</td>\n",
       "      <td>S13002</td>\n",
       "      <td>20160920</td>\n",
       "      <td>6-20100510-1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          customer_id customer_name  gender_cd gender   birth_day  age  \\\n",
       "15639  CS035114000004         大村 美里          1     女性  2007-11-25   11   \n",
       "7468   CS022103000002        福山 はじめ          9     不明  2007-10-02   11   \n",
       "10745  CS002113000009        柴田 真悠子          1     女性  2007-09-17   11   \n",
       "19811  CS004115000014         松井 京子          1     女性  2007-08-09   11   \n",
       "7039   CS002114000010          山内 遥          1     女性  2007-06-03   11   \n",
       "\n",
       "      postal_cd                address application_store_cd  application_date  \\\n",
       "15639  156-0053     東京都世田谷区桜**********               S13035          20150619   \n",
       "7468   249-0006    神奈川県逗子市逗子**********               S14022          20160909   \n",
       "10745  184-0014  東京都小金井市貫井南町**********               S13002          20160304   \n",
       "19811  165-0031    東京都中野区上鷺宮**********               S13004          20161120   \n",
       "7039   184-0015  東京都小金井市貫井北町**********               S13002          20160920   \n",
       "\n",
       "          status_cd  \n",
       "15639  6-20091205-6  \n",
       "7468   0-00000000-0  \n",
       "10745  0-00000000-0  \n",
       "19811  1-20081231-1  \n",
       "7039   6-20100510-1  "
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customer.sort_values(by=\"birth_day\", ascending=False).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "1lRGtDSphhyQ"
   },
   "source": [
    "---\n",
    "## 問3. 全件数\n",
    "> P-021: レシート明細データフレーム（df_receipt）に対し、件数をカウントせよ。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 35
    },
    "colab_type": "code",
    "id": "m-ihL_fVhhyQ",
    "outputId": "da0669b7-2c34-4dc6-becb-4da002103202"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "104681"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_receipt)\n",
    "#df_receipt.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "MkjDW-oyhhyS"
   },
   "source": [
    "## 問4. ユニーク件数\n",
    "> P-022: レシート明細データフレーム（df_receipt）の顧客ID（customer_id）に対し、ユニーク件数をカウントせよ。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 35
    },
    "colab_type": "code",
    "id": "0xSI9r8UhhyS",
    "outputId": "0c5a57f4-2088-4109-a40e-eb76c4845af0"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8307"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_receipt[\"customer_id\"].nunique()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary>ヒント</summary>\n",
    "「ユニーク」というのはそのまま検索に使える単語です。  \n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "h9E9b_yUhhyq"
   },
   "source": [
    "---\n",
    "## 問5. 〇〇ごとに集計\n",
    "> P-035: レシート明細データフレーム（df_receipt）に対し、顧客ID（customer_id）ごとに売上金額（amount）を合計して全顧客の平均を求め、平均以上に買い物をしている顧客を抽出せよ。ただし、顧客IDが\"Z\"から始まるのものは非会員を表すため、除外して計算すること。なお、データは先頭5件だけ表示せよ。\n",
    "\n",
    "会員のみを抽出する方法は、例えば以下の2通りの方法があります。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_receipt_only_member = df_receipt[~df_receipt[\"customer_id\"].str.startswith(\"Z\")]\n",
    "df_receipt_only_member = df_receipt.query(\"not customer_id.str.startswith('Z')\", engine=\"python\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 225
    },
    "colab_type": "code",
    "id": "7lYKkmsohhyq",
    "outputId": "06bbc1ea-d6d0-4841-a6dd-1598974714b6"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "customer_id\n",
       "CS001115000010    3044\n",
       "CS001205000006    3337\n",
       "CS001214000009    4685\n",
       "CS001214000017    4132\n",
       "CS001214000052    5639\n",
       "Name: amount, dtype: int64"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum_amount= df_receipt_only_member.groupby(\"customer_id\")[\"amount\"].sum() #顧客ごとの合計売上金額\n",
    "average_amount = sum_amount.mean() #全顧客の平均購入金額\n",
    "amount_above_average = sum_amount[sum_amount>= average_amount] #平均以上に買い物をしている\n",
    "amount_above_average.head(5)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary>ヒント1</summary>\n",
    "「pandas 要素ごと 集計」 などで今回使える機能に関する記事が見つかります。\n",
    "</details>"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary>ヒント2</summary>\n",
    "メソッド名は \"groupby\" です。\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "iNO7ESvWhhyw"
   },
   "source": [
    "---\n",
    "## 問6. DataFrameの結合\n",
    "> P-038: 顧客データフレーム（df_customer）とレシート明細データフレーム（df_receipt）から、各顧客ごとの売上金額合計を求めよ。ただし、買い物の実績がない顧客については売上金額を0として表示させること。また、顧客は性別コード（gender_cd）が女性（1）であるものを対象とし、非会員（顧客IDが'Z'から始まるもの）は除外すること。なお、結果は先頭5件だけ表示せよ。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_customer_only_member = df_customer[~df_customer[\"customer_id\"].str.startswith(\"Z\")]\n",
    "df_customer_only_member = df_customer.query(\"not customer_id.str.startswith('Z')\", engine=\"python\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>customer_name</th>\n",
       "      <th>gender_cd</th>\n",
       "      <th>gender</th>\n",
       "      <th>birth_day</th>\n",
       "      <th>age</th>\n",
       "      <th>postal_cd</th>\n",
       "      <th>address</th>\n",
       "      <th>application_store_cd</th>\n",
       "      <th>application_date</th>\n",
       "      <th>status_cd</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CS021313000114</td>\n",
       "      <td>大野 あや子</td>\n",
       "      <td>1</td>\n",
       "      <td>女性</td>\n",
       "      <td>1981-04-29</td>\n",
       "      <td>37</td>\n",
       "      <td>259-1113</td>\n",
       "      <td>神奈川県伊勢原市粟窪**********</td>\n",
       "      <td>S14021</td>\n",
       "      <td>20150905</td>\n",
       "      <td>0-00000000-0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CS031415000172</td>\n",
       "      <td>宇多田 貴美子</td>\n",
       "      <td>1</td>\n",
       "      <td>女性</td>\n",
       "      <td>1976-10-04</td>\n",
       "      <td>42</td>\n",
       "      <td>151-0053</td>\n",
       "      <td>東京都渋谷区代々木**********</td>\n",
       "      <td>S13031</td>\n",
       "      <td>20150529</td>\n",
       "      <td>D-20100325-C</td>\n",
       "      <td>5088.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CS028811000001</td>\n",
       "      <td>堀井 かおり</td>\n",
       "      <td>1</td>\n",
       "      <td>女性</td>\n",
       "      <td>1933-03-27</td>\n",
       "      <td>86</td>\n",
       "      <td>245-0016</td>\n",
       "      <td>神奈川県横浜市泉区和泉町**********</td>\n",
       "      <td>S14028</td>\n",
       "      <td>20160115</td>\n",
       "      <td>0-00000000-0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CS001215000145</td>\n",
       "      <td>田崎 美紀</td>\n",
       "      <td>1</td>\n",
       "      <td>女性</td>\n",
       "      <td>1995-03-29</td>\n",
       "      <td>24</td>\n",
       "      <td>144-0055</td>\n",
       "      <td>東京都大田区仲六郷**********</td>\n",
       "      <td>S13001</td>\n",
       "      <td>20170605</td>\n",
       "      <td>6-20090929-2</td>\n",
       "      <td>875.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CS015414000103</td>\n",
       "      <td>奥野 陽子</td>\n",
       "      <td>1</td>\n",
       "      <td>女性</td>\n",
       "      <td>1977-08-09</td>\n",
       "      <td>41</td>\n",
       "      <td>136-0073</td>\n",
       "      <td>東京都江東区北砂**********</td>\n",
       "      <td>S13015</td>\n",
       "      <td>20150722</td>\n",
       "      <td>B-20100609-B</td>\n",
       "      <td>3122.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      customer_id customer_name  gender_cd gender   birth_day  age postal_cd  \\\n",
       "0  CS021313000114        大野 あや子          1     女性  1981-04-29   37  259-1113   \n",
       "1  CS031415000172       宇多田 貴美子          1     女性  1976-10-04   42  151-0053   \n",
       "2  CS028811000001        堀井 かおり          1     女性  1933-03-27   86  245-0016   \n",
       "3  CS001215000145         田崎 美紀          1     女性  1995-03-29   24  144-0055   \n",
       "4  CS015414000103         奥野 陽子          1     女性  1977-08-09   41  136-0073   \n",
       "\n",
       "                  address application_store_cd  application_date  \\\n",
       "0    神奈川県伊勢原市粟窪**********               S14021          20150905   \n",
       "1     東京都渋谷区代々木**********               S13031          20150529   \n",
       "2  神奈川県横浜市泉区和泉町**********               S14028          20160115   \n",
       "3     東京都大田区仲六郷**********               S13001          20170605   \n",
       "4      東京都江東区北砂**********               S13015          20150722   \n",
       "\n",
       "      status_cd  amount  \n",
       "0  0-00000000-0     0.0  \n",
       "1  D-20100325-C  5088.0  \n",
       "2  0-00000000-0     0.0  \n",
       "3  6-20090929-2   875.0  \n",
       "4  B-20100609-B  3122.0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customer_female= df_customer[(~df_customer[\"customer_id\"].str.startswith(\"Z\")) & (df_customer[\"gender_cd\"]==1)] #女性のみ\n",
    "sum_amount= df_receipt_only_member.groupby(\"customer_id\")[\"amount\"].sum() #顧客ごとの合計売上金額\n",
    "merged_data=pd.merge(df_customer_female, sum_amount, on=\"customer_id\",how=\"left\") #\"customer_id\"をもとにデータフレームを統合\n",
    "merged_data[\"amount\"]=merged_data[\"amount\"].fillna(0)#amountの欠損値を0に置き換え\n",
    "\n",
    "merged_data.head(5)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary>ヒント1</summary>\n",
    "タイトル通り 「pandas DataFrame 結合」などと調べれば必要な機能に関する記事が見つかります。  \n",
    "</details>\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary>ヒント2</summary>\n",
    "\"merge\", \"join\"という似たメソッドがあります。  \n",
    "今回の場合\"merge\"が便利でしょう。\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "umDmd8kohhzA"
   },
   "source": [
    "---\n",
    "## 問7. 時系列データ\n",
    "> P-046: 顧客データフレーム（df_customer）の申し込み日（application_date）はYYYYMMD形式の文字列型でデータを保有している。これを日付型（dateやdatetime）に変換し、顧客ID（customer_id）とともに抽出せよ。なお、データは先頭5件を表示せよ。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 348
    },
    "colab_type": "code",
    "id": "pVAxV-TWhhzA",
    "outputId": "2d2e2281-7181-41b4-81e4-a9e834b93927"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>application_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CS021313000114</td>\n",
       "      <td>2015-09-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CS037613000071</td>\n",
       "      <td>2015-04-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CS031415000172</td>\n",
       "      <td>2015-05-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CS028811000001</td>\n",
       "      <td>2016-01-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CS001215000145</td>\n",
       "      <td>2017-06-05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      customer_id application_date\n",
       "0  CS021313000114       2015-09-05\n",
       "1  CS037613000071       2015-04-14\n",
       "2  CS031415000172       2015-05-29\n",
       "3  CS028811000001       2016-01-15\n",
       "4  CS001215000145       2017-06-05"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customer[\"application_date\"]=pd.to_datetime(df_customer[\"application_date\"], format='%Y%m%d') \n",
    "df_customer[[\"customer_id\",\"application_date\"]].head(5)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary>ヒント1</summary>\n",
    "「pandas datetime」などで該当の機能が見つかるかと思います。\n",
    "</details>\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary>ヒント2</summary>\n",
    "\"pd.to_datetime\"というメソッドが使えるでしょう。このメソッドを適用する際ですが、for文を使わずに実装しましょう。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "9v_q6BLjhhzU"
   },
   "source": [
    "---\n",
    "## 問8. 関数\n",
    "> P-061: レシート明細データフレーム（df_receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、合計した売上金額を常用対数化（底=10）して顧客ID、売上金額合計とともに表示せよ。ただし、顧客IDが\"Z\"から始まるのものは非会員を表すため、除外して計算すること。なお、結果は先頭5件を表示せよ。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 437
    },
    "colab_type": "code",
    "id": "d5_2HQ-2hhzU",
    "outputId": "56ba3b92-2071-4a8d-c555-d9007bb43316"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>amount</th>\n",
       "      <th>log_amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>CS001113000004</th>\n",
       "      <td>1298</td>\n",
       "      <td>3.113275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CS001114000005</th>\n",
       "      <td>626</td>\n",
       "      <td>2.796574</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CS001115000010</th>\n",
       "      <td>3044</td>\n",
       "      <td>3.483445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CS001205000004</th>\n",
       "      <td>1988</td>\n",
       "      <td>3.298416</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CS001205000006</th>\n",
       "      <td>3337</td>\n",
       "      <td>3.523356</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                amount  log_amount\n",
       "customer_id                       \n",
       "CS001113000004    1298    3.113275\n",
       "CS001114000005     626    2.796574\n",
       "CS001115000010    3044    3.483445\n",
       "CS001205000004    1988    3.298416\n",
       "CS001205000006    3337    3.523356"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum_amount= df_receipt_only_member.groupby(\"customer_id\")[\"amount\"].sum() #顧客ごとの合計売上金額\n",
    "pd.DataFrame({\n",
    "    \"amount\":sum_amount,\n",
    "    \"log_amount\":np.log10(sum_amount)\n",
    "}).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "boe923CMhhzq"
   },
   "source": [
    "---\n",
    "## 問9. 欠損数\n",
    "> P-079: 商品データフレーム（df_product）の各項目に対し、欠損数を確認せよ。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 169
    },
    "colab_type": "code",
    "id": "bxl__vC5hhzq",
    "outputId": "d8bb408a-6897-4e5a-8416-ce67a8a5fce4"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "product_cd            0\n",
       "category_major_cd     0\n",
       "category_medium_cd    0\n",
       "category_small_cd     0\n",
       "unit_price            7\n",
       "unit_cost             7\n",
       "dtype: int64"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_product.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "afprSqIvhhzs"
   },
   "source": [
    "---\n",
    "## 問10. 欠損値の除去\n",
    "> P-080: 商品データフレーム（df_product）のいずれかの項目に欠損が発生しているレコードを全て削除した新たなdf_product_1を作成せよ。なお、削除前後の件数を表示させ、前設問で確認した件数だけ減少していることも確認すること。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "q3_9sLdHhhzt"
   },
   "outputs": [],
   "source": [
    "df_product_1 = df_product.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "qFNnYstw1vTG"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10030, 10023)"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_product), len(df_product_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "GtiO20ZKhhzu"
   },
   "source": [
    "---\n",
    "## 問11. 欠損値の穴埋め\n",
    "> P-081: 単価（unit_price）と原価（unit_cost）の欠損値について、それぞれの平均値で補完した新たなdf_product_2を作成せよ。なお、平均値について1円未満は四捨五入とせよ。補完実施後、各項目について欠損が生じていないことも確認すること。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "puFf-7Ewhhzu"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "product_cd            0\n",
       "category_major_cd     0\n",
       "category_medium_cd    0\n",
       "category_small_cd     0\n",
       "unit_price            0\n",
       "unit_cost             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mean_unit_price = round(df_product[\"unit_price\"].mean()) #平均単価\n",
    "mean_unit_cost = round(df_product[\"unit_cost\"].mean()) #平均原価\n",
    "df_product_2=df_product.fillna({\"unit_price\":mean_unit_price,\"unit_cost\":mean_unit_cost}) #欠損値に平均単価、平均原価をそれぞれ代入\n",
    "df_product_2.isnull().sum() #欠損値の有無を確認"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 余談\n",
    "ChatGPTやBing AIに聞けば大抵のことは教えてくれます。  \n",
    "何回か入力文章を吟味しないといけないこともありますが、知らないことを調べる場合は自分で検索するよりも早いです。  \n",
    "ただ、ChatGPTなどは嘘をつく場合があるので、自分でソースを参照する姿勢は必要です。  \n",
    "\n",
    "これはBingAIの回答例です。  \n",
    "\n",
    "![BingAIの回答例](./imgs/pandas/BingAI.png)"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "name": "pandas_quiz.ipynb",
   "provenance": []
  },
  "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.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}