51-75問.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a2e0950e",
   "metadata": {},
   "outputs": [],
   "source": [
    "using DataFramesMeta\n",
    "using StatsBase\n",
    "using CSV\n",
    "\n",
    "dir = \"/Users/fujisetakumi/_private/data_science/100knocks-preprocess/data/\"\n",
    "df_customers  = CSV.read(dir * \"customers.csv\", DataFrame)\n",
    "df_categories = CSV.read(dir * \"categories.csv\", DataFrame)\n",
    "df_products   = CSV.read(dir * \"products.csv\", DataFrame)\n",
    "df_geocodes   = CSV.read(dir * \"geocodes.csv\", DataFrame)\n",
    "df_receipts   = CSV.read(dir * \"receipts.csv\", DataFrame)\n",
    "df_stores     = CSV.read(dir * \"stores.csv\", DataFrame)\n",
    ";\n",
    "ENV[\"COLUMNS\"] = 1000\n",
    ";"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f1a4aa03",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">receipt_no</th><th style = \"text-align: left;\">receipt_sub_no</th><th style = \"text-align: left;\">sales_day</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"String\" style = \"text-align: left;\">String</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">112</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">03</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">1132</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">18</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">1102</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">12</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: right;\">1132</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">05</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: right;\">1102</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">21</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: right;\">1112</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">05</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: right;\">1102</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">05</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: right;\">1102</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">22</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: right;\">1112</td><td style = \"text-align: right;\">2</td><td style = \"text-align: left;\">04</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: right;\">1102</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">10</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& receipt\\_no & receipt\\_sub\\_no & sales\\_day\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & String\\\\\n",
       "\t\\hline\n",
       "\t1 & 112 & 1 & 03 \\\\\n",
       "\t2 & 1132 & 2 & 18 \\\\\n",
       "\t3 & 1102 & 1 & 12 \\\\\n",
       "\t4 & 1132 & 1 & 05 \\\\\n",
       "\t5 & 1102 & 2 & 21 \\\\\n",
       "\t6 & 1112 & 1 & 05 \\\\\n",
       "\t7 & 1102 & 2 & 05 \\\\\n",
       "\t8 & 1102 & 1 & 22 \\\\\n",
       "\t9 & 1112 & 2 & 04 \\\\\n",
       "\t10 & 1102 & 1 & 10 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m receipt_no \u001b[0m\u001b[1m receipt_sub_no \u001b[0m\u001b[1m sales_day \u001b[0m\n",
       "     │\u001b[90m Int64      \u001b[0m\u001b[90m Int64          \u001b[0m\u001b[90m String    \u001b[0m\n",
       "─────┼───────────────────────────────────────\n",
       "   1 │        112               1  03\n",
       "   2 │       1132               2  18\n",
       "   3 │       1102               1  12\n",
       "   4 │       1132               1  05\n",
       "   5 │       1102               2  21\n",
       "   6 │       1112               1  05\n",
       "   7 │       1102               2  05\n",
       "   8 │       1102               1  22\n",
       "   9 │       1112               2  04\n",
       "  10 │       1102               1  10"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-051 エポック秒から日だけ取り出して0埋め\n",
    "using Dates\n",
    "@chain df_receipts begin\n",
    "    @rtransform(:sales_date = Date(unix2datetime(:sales_epoch)))\n",
    "    @rtransform(:sales_day = string(Dates.day(:sales_date), pad = 2))\n",
    "    select(:receipt_no, :receipt_sub_no, :sales_day)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b4a9ad15",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">customer_id</th><th style = \"text-align: left;\">amount_sum</th><th style = \"text-align: left;\">gt2000</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">CS006214000001</td><td style = \"text-align: right;\">7364</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS008415000097</td><td style = \"text-align: right;\">1895</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS028414000014</td><td style = \"text-align: right;\">6222</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS025415000050</td><td style = \"text-align: right;\">5736</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS003515000195</td><td style = \"text-align: right;\">5412</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS024514000042</td><td style = \"text-align: right;\">533</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS040415000178</td><td style = \"text-align: right;\">6149</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS027514000015</td><td style = \"text-align: right;\">2788</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS025415000134</td><td style = \"text-align: right;\">4902</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS021515000126</td><td style = \"text-align: right;\">9693</td><td style = \"text-align: right;\">1</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& customer\\_id & amount\\_sum & gt2000\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & CS006214000001 & 7364 & 1 \\\\\n",
       "\t2 & CS008415000097 & 1895 & 0 \\\\\n",
       "\t3 & CS028414000014 & 6222 & 1 \\\\\n",
       "\t4 & CS025415000050 & 5736 & 1 \\\\\n",
       "\t5 & CS003515000195 & 5412 & 1 \\\\\n",
       "\t6 & CS024514000042 & 533 & 0 \\\\\n",
       "\t7 & CS040415000178 & 6149 & 1 \\\\\n",
       "\t8 & CS027514000015 & 2788 & 1 \\\\\n",
       "\t9 & CS025415000134 & 4902 & 1 \\\\\n",
       "\t10 & CS021515000126 & 9693 & 1 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m customer_id    \u001b[0m\u001b[1m amount_sum \u001b[0m\u001b[1m gt2000 \u001b[0m\n",
       "     │\u001b[90m String15       \u001b[0m\u001b[90m Int64      \u001b[0m\u001b[90m Int64  \u001b[0m\n",
       "─────┼────────────────────────────────────\n",
       "   1 │ CS006214000001        7364       1\n",
       "   2 │ CS008415000097        1895       0\n",
       "   3 │ CS028414000014        6222       1\n",
       "   4 │ CS025415000050        5736       1\n",
       "   5 │ CS003515000195        5412       1\n",
       "   6 │ CS024514000042         533       0\n",
       "   7 │ CS040415000178        6149       1\n",
       "   8 │ CS027514000015        2788       1\n",
       "   9 │ CS025415000134        4902       1\n",
       "  10 │ CS021515000126        9693       1"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-052 売上金額が2000円以上か否かで2値化\n",
    "@chain df_receipts begin\n",
    "    @rsubset(occursin(r\"^[^Z]\", :customer_id))\n",
    "    groupby(:customer_id)\n",
    "    combine(:amount => sum)\n",
    "    @rtransform(:gt2000 = :amount_sum > 2000 ? 1 : 0)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ad434374",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>2×2 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">tokyo</th><th style = \"text-align: left;\">tokyo_length</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">0</td><td style = \"text-align: right;\">3906</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">4400</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& tokyo & tokyo\\_length\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 0 & 3906 \\\\\n",
       "\t2 & 1 & 4400 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m2×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m tokyo \u001b[0m\u001b[1m tokyo_length \u001b[0m\n",
       "     │\u001b[90m Int64 \u001b[0m\u001b[90m Int64        \u001b[0m\n",
       "─────┼─────────────────────\n",
       "   1 │     0          3906\n",
       "   2 │     1          4400"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-053\n",
    "@chain df_customers begin\n",
    "    @rtransform(:tokyo = convert(Int, \n",
    "        100 <= parse(Int, :postal_cd[1:3]) <= 209))\n",
    "    # unique で行数が増えないようにする\n",
    "    innerjoin(unique(df_receipts, :customer_id), on= :customer_id => :customer_id)\n",
    "    groupby(:tokyo)\n",
    "    combine(:tokyo => length)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ec1ff533",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">customer_id</th><th style = \"text-align: left;\">address</th><th style = \"text-align: left;\">prefecture_cd</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"String\" style = \"text-align: left;\">String</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">CS021313000114</td><td style = \"text-align: left;\">神奈川県伊勢原市粟窪**********</td><td style = \"text-align: right;\">14</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS037613000071</td><td style = \"text-align: left;\">東京都江東区南砂**********</td><td style = \"text-align: right;\">13</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS031415000172</td><td style = \"text-align: left;\">東京都渋谷区代々木**********</td><td style = \"text-align: right;\">13</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS028811000001</td><td style = \"text-align: left;\">神奈川県横浜市泉区和泉町**********</td><td style = \"text-align: right;\">14</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS001215000145</td><td style = \"text-align: left;\">東京都大田区仲六郷**********</td><td style = \"text-align: right;\">13</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS020401000016</td><td style = \"text-align: left;\">東京都板橋区若木**********</td><td style = \"text-align: right;\">13</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS015414000103</td><td style = \"text-align: left;\">東京都江東区北砂**********</td><td style = \"text-align: right;\">13</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS029403000008</td><td style = \"text-align: left;\">千葉県浦安市海楽**********</td><td style = \"text-align: right;\">12</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS015804000004</td><td style = \"text-align: left;\">東京都江東区北砂**********</td><td style = \"text-align: right;\">13</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS033513000180</td><td style = \"text-align: left;\">神奈川県横浜市旭区善部町**********</td><td style = \"text-align: right;\">14</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& customer\\_id & address & prefecture\\_cd\\\\\n",
       "\t\\hline\n",
       "\t& String15 & String & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & CS021313000114 & 神奈川県伊勢原市粟窪********** & 14 \\\\\n",
       "\t2 & CS037613000071 & 東京都江東区南砂********** & 13 \\\\\n",
       "\t3 & CS031415000172 & 東京都渋谷区代々木********** & 13 \\\\\n",
       "\t4 & CS028811000001 & 神奈川県横浜市泉区和泉町********** & 14 \\\\\n",
       "\t5 & CS001215000145 & 東京都大田区仲六郷********** & 13 \\\\\n",
       "\t6 & CS020401000016 & 東京都板橋区若木********** & 13 \\\\\n",
       "\t7 & CS015414000103 & 東京都江東区北砂********** & 13 \\\\\n",
       "\t8 & CS029403000008 & 千葉県浦安市海楽********** & 12 \\\\\n",
       "\t9 & CS015804000004 & 東京都江東区北砂********** & 13 \\\\\n",
       "\t10 & CS033513000180 & 神奈川県横浜市旭区善部町********** & 14 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m customer_id    \u001b[0m\u001b[1m address                           \u001b[0m\u001b[1m prefecture_cd \u001b[0m\n",
       "     │\u001b[90m String15       \u001b[0m\u001b[90m String                            \u001b[0m\u001b[90m Int64         \u001b[0m\n",
       "─────┼──────────────────────────────────────────────────────────────────\n",
       "   1 │ CS021313000114  神奈川県伊勢原市粟窪**********                14\n",
       "   2 │ CS037613000071  東京都江東区南砂**********                    13\n",
       "   3 │ CS031415000172  東京都渋谷区代々木**********                  13\n",
       "   4 │ CS028811000001  神奈川県横浜市泉区和泉町********…             14\n",
       "   5 │ CS001215000145  東京都大田区仲六郷**********                  13\n",
       "   6 │ CS020401000016  東京都板橋区若木**********                    13\n",
       "   7 │ CS015414000103  東京都江東区北砂**********                    13\n",
       "   8 │ CS029403000008  千葉県浦安市海楽**********                    12\n",
       "   9 │ CS015804000004  東京都江東区北砂**********                    13\n",
       "  10 │ CS033513000180  神奈川県横浜市旭区善部町********…             14"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-054\n",
    "@chain df_customers begin\n",
    "    @rtransform(:prefecture_cd = @chain :address begin\n",
    "            if occursin(r\"埼玉県\", _)\n",
    "                11\n",
    "            elseif occursin(r\"千葉県\", _)\n",
    "                12\n",
    "            elseif occursin(r\"東京都\", _)\n",
    "                13\n",
    "            elseif occursin(r\"神奈川県\", _)\n",
    "                14\n",
    "            end\n",
    "        end)\n",
    "    select(:customer_id, :address, :prefecture_cd)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "67fe64b6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>8307×3 DataFrame</span></div><div style = \"float: right;\"><span style = \"font-style: italic;\">8282 rows omitted</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">customer_id</th><th style = \"text-align: left;\">amount_sum</th><th style = \"text-align: left;\">rank</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">CS001113000004</td><td style = \"text-align: right;\">1298</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: right;\">626</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: right;\">3044</td><td style = \"text-align: right;\">3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS001205000004</td><td style = \"text-align: right;\">1988</td><td style = \"text-align: right;\">3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS001205000006</td><td style = \"text-align: right;\">3337</td><td style = \"text-align: right;\">3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS001211000025</td><td style = \"text-align: right;\">456</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS001212000027</td><td style = \"text-align: right;\">448</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS001212000031</td><td style = \"text-align: right;\">296</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS001212000046</td><td style = \"text-align: right;\">228</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS001212000070</td><td style = \"text-align: right;\">456</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">11</td><td style = \"text-align: left;\">CS001212000098</td><td style = \"text-align: right;\">383</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">12</td><td style = \"text-align: left;\">CS001213000018</td><td style = \"text-align: right;\">243</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">13</td><td style = \"text-align: left;\">CS001213000020</td><td style = \"text-align: right;\">343</td><td style = \"text-align: right;\">1</td></tr><tr><td style = \"text-align: right;\">&vellip;</td><td style = \"text-align: right;\">&vellip;</td><td style = \"text-align: right;\">&vellip;</td><td style = \"text-align: right;\">&vellip;</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8296</td><td style = \"text-align: left;\">CS049605000005</td><td style = \"text-align: right;\">1856</td><td style = \"text-align: right;\">3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8297</td><td style = \"text-align: left;\">CS049613000002</td><td style = \"text-align: right;\">999</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8298</td><td style = \"text-align: left;\">CS049613000006</td><td style = \"text-align: right;\">768</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8299</td><td style = \"text-align: left;\">CS049614000003</td><td style = \"text-align: right;\">308</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8300</td><td style = \"text-align: left;\">CS049815000003</td><td style = \"text-align: right;\">306</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8301</td><td style = \"text-align: left;\">CS050415000007</td><td style = \"text-align: right;\">2770</td><td style = \"text-align: right;\">3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8302</td><td style = \"text-align: left;\">CS051212000001</td><td style = \"text-align: right;\">336</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8303</td><td style = \"text-align: left;\">CS051513000004</td><td style = \"text-align: right;\">551</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8304</td><td style = \"text-align: left;\">CS051515000002</td><td style = \"text-align: right;\">265</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8305</td><td style = \"text-align: left;\">CS052212000002</td><td style = \"text-align: right;\">192</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8306</td><td style = \"text-align: left;\">CS052514000001</td><td style = \"text-align: right;\">178</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8307</td><td style = \"text-align: left;\">ZZ000000000000</td><td style = \"text-align: right;\">12395003</td><td style = \"text-align: right;\">4</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& customer\\_id & amount\\_sum & rank\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & CS001113000004 & 1298 & 2 \\\\\n",
       "\t2 & CS001114000005 & 626 & 2 \\\\\n",
       "\t3 & CS001115000010 & 3044 & 3 \\\\\n",
       "\t4 & CS001205000004 & 1988 & 3 \\\\\n",
       "\t5 & CS001205000006 & 3337 & 3 \\\\\n",
       "\t6 & CS001211000025 & 456 & 1 \\\\\n",
       "\t7 & CS001212000027 & 448 & 1 \\\\\n",
       "\t8 & CS001212000031 & 296 & 1 \\\\\n",
       "\t9 & CS001212000046 & 228 & 1 \\\\\n",
       "\t10 & CS001212000070 & 456 & 1 \\\\\n",
       "\t11 & CS001212000098 & 383 & 1 \\\\\n",
       "\t12 & CS001213000018 & 243 & 1 \\\\\n",
       "\t13 & CS001213000020 & 343 & 1 \\\\\n",
       "\t14 & CS001214000009 & 4685 & 4 \\\\\n",
       "\t15 & CS001214000017 & 4132 & 4 \\\\\n",
       "\t16 & CS001214000024 & 335 & 1 \\\\\n",
       "\t17 & CS001214000048 & 2374 & 3 \\\\\n",
       "\t18 & CS001214000052 & 5639 & 4 \\\\\n",
       "\t19 & CS001214000059 & 248 & 1 \\\\\n",
       "\t20 & CS001214000063 & 123 & 1 \\\\\n",
       "\t21 & CS001215000005 & 735 & 2 \\\\\n",
       "\t22 & CS001215000040 & 3496 & 3 \\\\\n",
       "\t23 & CS001215000061 & 588 & 2 \\\\\n",
       "\t24 & CS001215000080 & 273 & 1 \\\\\n",
       "\t25 & CS001215000097 & 2288 & 3 \\\\\n",
       "\t26 & CS001215000133 & 556 & 2 \\\\\n",
       "\t27 & CS001215000145 & 875 & 2 \\\\\n",
       "\t28 & CS001215000158 & 1349 & 2 \\\\\n",
       "\t29 & CS001215000170 & 534 & 1 \\\\\n",
       "\t30 & CS001304000004 & 1248 & 2 \\\\\n",
       "\t$\\dots$ & $\\dots$ & $\\dots$ & $\\dots$ \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m8307×3 DataFrame\u001b[0m\n",
       "\u001b[1m  Row \u001b[0m│\u001b[1m customer_id    \u001b[0m\u001b[1m amount_sum \u001b[0m\u001b[1m rank  \u001b[0m\n",
       "      │\u001b[90m String15       \u001b[0m\u001b[90m Int64      \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "──────┼───────────────────────────────────\n",
       "    1 │ CS001113000004        1298      2\n",
       "    2 │ CS001114000005         626      2\n",
       "    3 │ CS001115000010        3044      3\n",
       "    4 │ CS001205000004        1988      3\n",
       "    5 │ CS001205000006        3337      3\n",
       "    6 │ CS001211000025         456      1\n",
       "    7 │ CS001212000027         448      1\n",
       "    8 │ CS001212000031         296      1\n",
       "    9 │ CS001212000046         228      1\n",
       "   10 │ CS001212000070         456      1\n",
       "   11 │ CS001212000098         383      1\n",
       "  ⋮   │       ⋮             ⋮         ⋮\n",
       " 8298 │ CS049613000006         768      2\n",
       " 8299 │ CS049614000003         308      1\n",
       " 8300 │ CS049815000003         306      1\n",
       " 8301 │ CS050415000007        2770      3\n",
       " 8302 │ CS051212000001         336      1\n",
       " 8303 │ CS051513000004         551      2\n",
       " 8304 │ CS051515000002         265      1\n",
       " 8305 │ CS052212000002         192      1\n",
       " 8306 │ CS052514000001         178      1\n",
       " 8307 │ ZZ000000000000    12395003      4\n",
       "\u001b[36m                         8286 rows omitted\u001b[0m"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-055 合計金額の四分位点\n",
    "stats = @chain df_receipts begin\n",
    "    groupby(:customer_id)\n",
    "    combine(:amount => sum)\n",
    "    sort(:amount_sum)\n",
    "end\n",
    "_, pct25, pct50, pct75 = nquantile(stats.amount_sum, 4)\n",
    "@chain stats begin\n",
    "    @rtransform(:rank = @chain :amount_sum begin\n",
    "            if _ < pct25\n",
    "                1\n",
    "            elseif _ < pct50\n",
    "                2\n",
    "            elseif _ < pct75\n",
    "                3\n",
    "            else\n",
    "                4\n",
    "            end\n",
    "        end)\n",
    "    sort(:customer_id)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "0f5067fa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">customer_id</th><th style = \"text-align: left;\">birth_day</th><th style = \"text-align: left;\">generation</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">CS021313000114</td><td style = \"text-align: left;\">1981-04-29</td><td style = \"text-align: right;\">30</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS037613000071</td><td style = \"text-align: left;\">1952-04-01</td><td style = \"text-align: right;\">60</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS031415000172</td><td style = \"text-align: left;\">1976-10-04</td><td style = \"text-align: right;\">40</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS028811000001</td><td style = \"text-align: left;\">1933-03-27</td><td style = \"text-align: right;\">60</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS001215000145</td><td style = \"text-align: left;\">1995-03-29</td><td style = \"text-align: right;\">20</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS020401000016</td><td style = \"text-align: left;\">1974-09-15</td><td style = \"text-align: right;\">40</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS015414000103</td><td style = \"text-align: left;\">1977-08-09</td><td style = \"text-align: right;\">40</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS029403000008</td><td style = \"text-align: left;\">1973-08-17</td><td style = \"text-align: right;\">40</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS015804000004</td><td style = \"text-align: left;\">1931-05-02</td><td style = \"text-align: right;\">60</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS033513000180</td><td style = \"text-align: left;\">1962-07-11</td><td style = \"text-align: right;\">50</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& customer\\_id & birth\\_day & generation\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Date & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & CS021313000114 & 1981-04-29 & 30 \\\\\n",
       "\t2 & CS037613000071 & 1952-04-01 & 60 \\\\\n",
       "\t3 & CS031415000172 & 1976-10-04 & 40 \\\\\n",
       "\t4 & CS028811000001 & 1933-03-27 & 60 \\\\\n",
       "\t5 & CS001215000145 & 1995-03-29 & 20 \\\\\n",
       "\t6 & CS020401000016 & 1974-09-15 & 40 \\\\\n",
       "\t7 & CS015414000103 & 1977-08-09 & 40 \\\\\n",
       "\t8 & CS029403000008 & 1973-08-17 & 40 \\\\\n",
       "\t9 & CS015804000004 & 1931-05-02 & 60 \\\\\n",
       "\t10 & CS033513000180 & 1962-07-11 & 50 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m customer_id    \u001b[0m\u001b[1m birth_day  \u001b[0m\u001b[1m generation \u001b[0m\n",
       "     │\u001b[90m String15       \u001b[0m\u001b[90m Date       \u001b[0m\u001b[90m Int64      \u001b[0m\n",
       "─────┼────────────────────────────────────────\n",
       "   1 │ CS021313000114  1981-04-29          30\n",
       "   2 │ CS037613000071  1952-04-01          60\n",
       "   3 │ CS031415000172  1976-10-04          40\n",
       "   4 │ CS028811000001  1933-03-27          60\n",
       "   5 │ CS001215000145  1995-03-29          20\n",
       "   6 │ CS020401000016  1974-09-15          40\n",
       "   7 │ CS015414000103  1977-08-09          40\n",
       "   8 │ CS029403000008  1973-08-17          40\n",
       "   9 │ CS015804000004  1931-05-02          60\n",
       "  10 │ CS033513000180  1962-07-11          50"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-056 年代\n",
    "@chain df_customers begin\n",
    "    @rtransform(:generation = @chain floor(Int, :age / 10) * 10 _ > 60 ? 60 : _)\n",
    "    select(:customer_id, :birth_day, :generation)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "d028231b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>18×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">gender_cd</th><th style = \"text-align: left;\">generation</th><th style = \"text-align: left;\">cd</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"String\" style = \"text-align: left;\">String</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">0</td><td style = \"text-align: right;\">10</td><td style = \"text-align: left;\">0g10</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">0</td><td style = \"text-align: right;\">20</td><td style = \"text-align: left;\">0g20</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">0</td><td style = \"text-align: right;\">30</td><td style = \"text-align: left;\">0g30</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: right;\">0</td><td style = \"text-align: right;\">40</td><td style = \"text-align: left;\">0g40</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: right;\">0</td><td style = \"text-align: right;\">50</td><td style = \"text-align: left;\">0g50</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: right;\">0</td><td style = \"text-align: right;\">60</td><td style = \"text-align: left;\">0g60</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">10</td><td style = \"text-align: left;\">1g10</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">20</td><td style = \"text-align: left;\">1g20</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">30</td><td style = \"text-align: left;\">1g30</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">40</td><td style = \"text-align: left;\">1g40</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">11</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">50</td><td style = \"text-align: left;\">1g50</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">12</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">60</td><td style = \"text-align: left;\">1g60</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">13</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">10</td><td style = \"text-align: left;\">9g10</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">14</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">20</td><td style = \"text-align: left;\">9g20</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">15</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">30</td><td style = \"text-align: left;\">9g30</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">16</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">40</td><td style = \"text-align: left;\">9g40</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">17</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">50</td><td style = \"text-align: left;\">9g50</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">18</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">60</td><td style = \"text-align: left;\">9g60</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& gender\\_cd & generation & cd\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & String\\\\\n",
       "\t\\hline\n",
       "\t1 & 0 & 10 & 0g10 \\\\\n",
       "\t2 & 0 & 20 & 0g20 \\\\\n",
       "\t3 & 0 & 30 & 0g30 \\\\\n",
       "\t4 & 0 & 40 & 0g40 \\\\\n",
       "\t5 & 0 & 50 & 0g50 \\\\\n",
       "\t6 & 0 & 60 & 0g60 \\\\\n",
       "\t7 & 1 & 10 & 1g10 \\\\\n",
       "\t8 & 1 & 20 & 1g20 \\\\\n",
       "\t9 & 1 & 30 & 1g30 \\\\\n",
       "\t10 & 1 & 40 & 1g40 \\\\\n",
       "\t11 & 1 & 50 & 1g50 \\\\\n",
       "\t12 & 1 & 60 & 1g60 \\\\\n",
       "\t13 & 9 & 10 & 9g10 \\\\\n",
       "\t14 & 9 & 20 & 9g20 \\\\\n",
       "\t15 & 9 & 30 & 9g30 \\\\\n",
       "\t16 & 9 & 40 & 9g40 \\\\\n",
       "\t17 & 9 & 50 & 9g50 \\\\\n",
       "\t18 & 9 & 60 & 9g60 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m18×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m gender_cd \u001b[0m\u001b[1m generation \u001b[0m\u001b[1m cd     \u001b[0m\n",
       "     │\u001b[90m Int64     \u001b[0m\u001b[90m Int64      \u001b[0m\u001b[90m String \u001b[0m\n",
       "─────┼───────────────────────────────\n",
       "   1 │         0          10  0g10\n",
       "   2 │         0          20  0g20\n",
       "   3 │         0          30  0g30\n",
       "   4 │         0          40  0g40\n",
       "   5 │         0          50  0g50\n",
       "   6 │         0          60  0g60\n",
       "   7 │         1          10  1g10\n",
       "   8 │         1          20  1g20\n",
       "   9 │         1          30  1g30\n",
       "  10 │         1          40  1g40\n",
       "  11 │         1          50  1g50\n",
       "  12 │         1          60  1g60\n",
       "  13 │         9          10  9g10\n",
       "  14 │         9          20  9g20\n",
       "  15 │         9          30  9g30\n",
       "  16 │         9          40  9g40\n",
       "  17 │         9          50  9g50\n",
       "  18 │         9          60  9g60"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-057\n",
    "@chain df_customers begin\n",
    "    @rtransform(:generation = @chain floor(Int, :age / 10) * 10 _ > 60 ? 60 : _)\n",
    "    select(:gender_cd, :generation)\n",
    "    sort([:gender_cd, :generation])\n",
    "    @rtransform(:cd = string(:gender_cd, \"g\", :generation))\n",
    "    unique\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "2803eb16",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×4 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">customer_id</th><th style = \"text-align: left;\">gender_cd_0</th><th style = \"text-align: left;\">gender_cd_1</th><th style = \"text-align: left;\">gender_cd_9</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Bool\" style = \"text-align: left;\">Bool</th><th title = \"Bool\" style = \"text-align: left;\">Bool</th><th title = \"Bool\" style = \"text-align: left;\">Bool</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">CS021313000114</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">false</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS037613000071</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS031415000172</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">false</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS028811000001</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">false</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS001215000145</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">false</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS020401000016</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">false</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS015414000103</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">false</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS029403000008</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">false</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS015804000004</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">false</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS033513000180</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">false</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& customer\\_id & gender\\_cd\\_0 & gender\\_cd\\_1 & gender\\_cd\\_9\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Bool & Bool & Bool\\\\\n",
       "\t\\hline\n",
       "\t1 & CS021313000114 & 0 & 1 & 0 \\\\\n",
       "\t2 & CS037613000071 & 0 & 0 & 1 \\\\\n",
       "\t3 & CS031415000172 & 0 & 1 & 0 \\\\\n",
       "\t4 & CS028811000001 & 0 & 1 & 0 \\\\\n",
       "\t5 & CS001215000145 & 0 & 1 & 0 \\\\\n",
       "\t6 & CS020401000016 & 1 & 0 & 0 \\\\\n",
       "\t7 & CS015414000103 & 0 & 1 & 0 \\\\\n",
       "\t8 & CS029403000008 & 1 & 0 & 0 \\\\\n",
       "\t9 & CS015804000004 & 1 & 0 & 0 \\\\\n",
       "\t10 & CS033513000180 & 0 & 1 & 0 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×4 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m customer_id    \u001b[0m\u001b[1m gender_cd_0 \u001b[0m\u001b[1m gender_cd_1 \u001b[0m\u001b[1m gender_cd_9 \u001b[0m\n",
       "     │\u001b[90m String15       \u001b[0m\u001b[90m Bool        \u001b[0m\u001b[90m Bool        \u001b[0m\u001b[90m Bool        \u001b[0m\n",
       "─────┼───────────────────────────────────────────────────────\n",
       "   1 │ CS021313000114        false         true        false\n",
       "   2 │ CS037613000071        false        false         true\n",
       "   3 │ CS031415000172        false         true        false\n",
       "   4 │ CS028811000001        false         true        false\n",
       "   5 │ CS001215000145        false         true        false\n",
       "   6 │ CS020401000016         true        false        false\n",
       "   7 │ CS015414000103        false         true        false\n",
       "   8 │ CS029403000008         true        false        false\n",
       "   9 │ CS015804000004         true        false        false\n",
       "  10 │ CS033513000180        false         true        false"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-058 性別を横持ちにする(ダミー変数化。one-hot encoding)\n",
    "# 参考: https://yjunechoe.github.io/posts/2022-11-13-dataframes-jl-and-accessories/\n",
    "@chain df_customers begin\n",
    "    select(:customer_id, :gender_cd)\n",
    "    @aside gender_cds = unique(df_customers.gender_cd) |> sort\n",
    "    transform(:gender_cd => ByRow(v -> gender_cds .== v)\n",
    "        => Symbol.(:gender_cd_, gender_cds))\n",
    "    select(Not(:gender_cd))\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "85d14574",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×4 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">customer_id</th><th style = \"text-align: left;\">gender_cd_1</th><th style = \"text-align: left;\">gender_cd_9</th><th style = \"text-align: left;\">gender_cd_0</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Bool\" style = \"text-align: left;\">Bool</th><th title = \"Bool\" style = \"text-align: left;\">Bool</th><th title = \"Bool\" style = \"text-align: left;\">Bool</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">CS021313000114</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">false</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS037613000071</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">false</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS031415000172</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">false</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS028811000001</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">false</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS001215000145</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">false</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS020401000016</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS015414000103</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">false</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS029403000008</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS015804000004</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS033513000180</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">false</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& customer\\_id & gender\\_cd\\_1 & gender\\_cd\\_9 & gender\\_cd\\_0\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Bool & Bool & Bool\\\\\n",
       "\t\\hline\n",
       "\t1 & CS021313000114 & 1 & 0 & 0 \\\\\n",
       "\t2 & CS037613000071 & 0 & 1 & 0 \\\\\n",
       "\t3 & CS031415000172 & 1 & 0 & 0 \\\\\n",
       "\t4 & CS028811000001 & 1 & 0 & 0 \\\\\n",
       "\t5 & CS001215000145 & 1 & 0 & 0 \\\\\n",
       "\t6 & CS020401000016 & 0 & 0 & 1 \\\\\n",
       "\t7 & CS015414000103 & 1 & 0 & 0 \\\\\n",
       "\t8 & CS029403000008 & 0 & 0 & 1 \\\\\n",
       "\t9 & CS015804000004 & 0 & 0 & 1 \\\\\n",
       "\t10 & CS033513000180 & 1 & 0 & 0 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×4 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m customer_id    \u001b[0m\u001b[1m gender_cd_1 \u001b[0m\u001b[1m gender_cd_9 \u001b[0m\u001b[1m gender_cd_0 \u001b[0m\n",
       "     │\u001b[90m String15       \u001b[0m\u001b[90m Bool        \u001b[0m\u001b[90m Bool        \u001b[0m\u001b[90m Bool        \u001b[0m\n",
       "─────┼───────────────────────────────────────────────────────\n",
       "   1 │ CS021313000114         true        false        false\n",
       "   2 │ CS037613000071        false         true        false\n",
       "   3 │ CS031415000172         true        false        false\n",
       "   4 │ CS028811000001         true        false        false\n",
       "   5 │ CS001215000145         true        false        false\n",
       "   6 │ CS020401000016        false        false         true\n",
       "   7 │ CS015414000103         true        false        false\n",
       "   8 │ CS029403000008        false        false         true\n",
       "   9 │ CS015804000004        false        false         true\n",
       "  10 │ CS033513000180         true        false        false"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@chain df_customers begin\n",
    "    select(:customer_id, :gender_cd)\n",
    "    @rtransform(\n",
    "        :gender_cd_col = string(\"gender_cd_\", :gender_cd),\n",
    "        :gender_cd_val = true)\n",
    "    unstack(:gender_cd_col, :gender_cd_val, fill=false)\n",
    "    select(Not(:gender_cd))\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "78eb801c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">customer_id</th><th style = \"text-align: left;\">amount_sum</th><th style = \"text-align: left;\">amount_sum_std</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">CS001113000004</td><td style = \"text-align: right;\">1298</td><td style = \"text-align: right;\">-0.45935</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: right;\">626</td><td style = \"text-align: right;\">-0.706348</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: right;\">3044</td><td style = \"text-align: right;\">0.182403</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS001205000004</td><td style = \"text-align: right;\">1988</td><td style = \"text-align: right;\">-0.205737</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS001205000006</td><td style = \"text-align: right;\">3337</td><td style = \"text-align: right;\">0.290096</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS001211000025</td><td style = \"text-align: right;\">456</td><td style = \"text-align: right;\">-0.768832</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS001212000027</td><td style = \"text-align: right;\">448</td><td style = \"text-align: right;\">-0.771773</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS001212000031</td><td style = \"text-align: right;\">296</td><td style = \"text-align: right;\">-0.827641</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS001212000046</td><td style = \"text-align: right;\">228</td><td style = \"text-align: right;\">-0.852635</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS001212000070</td><td style = \"text-align: right;\">456</td><td style = \"text-align: right;\">-0.768832</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& customer\\_id & amount\\_sum & amount\\_sum\\_std\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & CS001113000004 & 1298 & -0.45935 \\\\\n",
       "\t2 & CS001114000005 & 626 & -0.706348 \\\\\n",
       "\t3 & CS001115000010 & 3044 & 0.182403 \\\\\n",
       "\t4 & CS001205000004 & 1988 & -0.205737 \\\\\n",
       "\t5 & CS001205000006 & 3337 & 0.290096 \\\\\n",
       "\t6 & CS001211000025 & 456 & -0.768832 \\\\\n",
       "\t7 & CS001212000027 & 448 & -0.771773 \\\\\n",
       "\t8 & CS001212000031 & 296 & -0.827641 \\\\\n",
       "\t9 & CS001212000046 & 228 & -0.852635 \\\\\n",
       "\t10 & CS001212000070 & 456 & -0.768832 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m customer_id    \u001b[0m\u001b[1m amount_sum \u001b[0m\u001b[1m amount_sum_std \u001b[0m\n",
       "     │\u001b[90m String15       \u001b[0m\u001b[90m Int64      \u001b[0m\u001b[90m Float64        \u001b[0m\n",
       "─────┼────────────────────────────────────────────\n",
       "   1 │ CS001113000004        1298       -0.45935\n",
       "   2 │ CS001114000005         626       -0.706348\n",
       "   3 │ CS001115000010        3044        0.182403\n",
       "   4 │ CS001205000004        1988       -0.205737\n",
       "   5 │ CS001205000006        3337        0.290096\n",
       "   6 │ CS001211000025         456       -0.768832\n",
       "   7 │ CS001212000027         448       -0.771773\n",
       "   8 │ CS001212000031         296       -0.827641\n",
       "   9 │ CS001212000046         228       -0.852635\n",
       "  10 │ CS001212000070         456       -0.768832"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-059 平均０、標準偏差１に標準化(Zスコア)\n",
    "@chain df_receipts begin\n",
    "    @rsubset(occursin(r\"^[^Z]\", :customer_id))\n",
    "    groupby(:customer_id)\n",
    "    combine(:amount => sum)\n",
    "    @transform(:amount_sum_std = zscore(:amount_sum))\n",
    "    sort(:customer_id)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "d8f029dc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">customer_id</th><th style = \"text-align: left;\">amount_sum</th><th style = \"text-align: left;\">amount_scale</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">CS001113000004</td><td style = \"text-align: right;\">1298</td><td style = \"text-align: right;\">0.0533542</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: right;\">626</td><td style = \"text-align: right;\">0.0241571</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: right;\">3044</td><td style = \"text-align: right;\">0.129214</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS001205000004</td><td style = \"text-align: right;\">1988</td><td style = \"text-align: right;\">0.0833333</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS001205000006</td><td style = \"text-align: right;\">3337</td><td style = \"text-align: right;\">0.141945</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS001211000025</td><td style = \"text-align: right;\">456</td><td style = \"text-align: right;\">0.0167709</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS001212000027</td><td style = \"text-align: right;\">448</td><td style = \"text-align: right;\">0.0164234</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS001212000031</td><td style = \"text-align: right;\">296</td><td style = \"text-align: right;\">0.00981926</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS001212000046</td><td style = \"text-align: right;\">228</td><td style = \"text-align: right;\">0.00686479</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS001212000070</td><td style = \"text-align: right;\">456</td><td style = \"text-align: right;\">0.0167709</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& customer\\_id & amount\\_sum & amount\\_scale\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & CS001113000004 & 1298 & 0.0533542 \\\\\n",
       "\t2 & CS001114000005 & 626 & 0.0241571 \\\\\n",
       "\t3 & CS001115000010 & 3044 & 0.129214 \\\\\n",
       "\t4 & CS001205000004 & 1988 & 0.0833333 \\\\\n",
       "\t5 & CS001205000006 & 3337 & 0.141945 \\\\\n",
       "\t6 & CS001211000025 & 456 & 0.0167709 \\\\\n",
       "\t7 & CS001212000027 & 448 & 0.0164234 \\\\\n",
       "\t8 & CS001212000031 & 296 & 0.00981926 \\\\\n",
       "\t9 & CS001212000046 & 228 & 0.00686479 \\\\\n",
       "\t10 & CS001212000070 & 456 & 0.0167709 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m customer_id    \u001b[0m\u001b[1m amount_sum \u001b[0m\u001b[1m amount_scale \u001b[0m\n",
       "     │\u001b[90m String15       \u001b[0m\u001b[90m Int64      \u001b[0m\u001b[90m Float64      \u001b[0m\n",
       "─────┼──────────────────────────────────────────\n",
       "   1 │ CS001113000004        1298    0.0533542\n",
       "   2 │ CS001114000005         626    0.0241571\n",
       "   3 │ CS001115000010        3044    0.129214\n",
       "   4 │ CS001205000004        1988    0.0833333\n",
       "   5 │ CS001205000006        3337    0.141945\n",
       "   6 │ CS001211000025         456    0.0167709\n",
       "   7 │ CS001212000027         448    0.0164234\n",
       "   8 │ CS001212000031         296    0.00981926\n",
       "   9 │ CS001212000046         228    0.00686479\n",
       "  10 │ CS001212000070         456    0.0167709"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-060 最小値0、最大値1に正規化\n",
    "@chain df_receipts begin\n",
    "    @rsubset(occursin(r\"^[^Z]\", :customer_id))\n",
    "    groupby(:customer_id)\n",
    "    combine(:amount => sum)\n",
    "    @aside min_amount = minimum(_.amount_sum)\n",
    "    @aside max_amount = maximum(_.amount_sum)\n",
    "    @rtransform(:amount_scale = \n",
    "        (:amount_sum - min_amount) / (max_amount - min_amount))\n",
    "    sort(:customer_id)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "271f7231",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">customer_id</th><th style = \"text-align: left;\">amount_sum</th><th style = \"text-align: left;\">amount_sum_log10</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">CS001113000004</td><td style = \"text-align: right;\">1298</td><td style = \"text-align: right;\">3.11327</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: right;\">626</td><td style = \"text-align: right;\">2.79657</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: right;\">3044</td><td style = \"text-align: right;\">3.48344</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS001205000004</td><td style = \"text-align: right;\">1988</td><td style = \"text-align: right;\">3.29842</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS001205000006</td><td style = \"text-align: right;\">3337</td><td style = \"text-align: right;\">3.52336</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS001211000025</td><td style = \"text-align: right;\">456</td><td style = \"text-align: right;\">2.65896</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS001212000027</td><td style = \"text-align: right;\">448</td><td style = \"text-align: right;\">2.65128</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS001212000031</td><td style = \"text-align: right;\">296</td><td style = \"text-align: right;\">2.47129</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS001212000046</td><td style = \"text-align: right;\">228</td><td style = \"text-align: right;\">2.35793</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS001212000070</td><td style = \"text-align: right;\">456</td><td style = \"text-align: right;\">2.65896</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& customer\\_id & amount\\_sum & amount\\_sum\\_log10\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & CS001113000004 & 1298 & 3.11327 \\\\\n",
       "\t2 & CS001114000005 & 626 & 2.79657 \\\\\n",
       "\t3 & CS001115000010 & 3044 & 3.48344 \\\\\n",
       "\t4 & CS001205000004 & 1988 & 3.29842 \\\\\n",
       "\t5 & CS001205000006 & 3337 & 3.52336 \\\\\n",
       "\t6 & CS001211000025 & 456 & 2.65896 \\\\\n",
       "\t7 & CS001212000027 & 448 & 2.65128 \\\\\n",
       "\t8 & CS001212000031 & 296 & 2.47129 \\\\\n",
       "\t9 & CS001212000046 & 228 & 2.35793 \\\\\n",
       "\t10 & CS001212000070 & 456 & 2.65896 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m customer_id    \u001b[0m\u001b[1m amount_sum \u001b[0m\u001b[1m amount_sum_log10 \u001b[0m\n",
       "     │\u001b[90m String15       \u001b[0m\u001b[90m Int64      \u001b[0m\u001b[90m Float64          \u001b[0m\n",
       "─────┼──────────────────────────────────────────────\n",
       "   1 │ CS001113000004        1298           3.11327\n",
       "   2 │ CS001114000005         626           2.79657\n",
       "   3 │ CS001115000010        3044           3.48344\n",
       "   4 │ CS001205000004        1988           3.29842\n",
       "   5 │ CS001205000006        3337           3.52336\n",
       "   6 │ CS001211000025         456           2.65896\n",
       "   7 │ CS001212000027         448           2.65128\n",
       "   8 │ CS001212000031         296           2.47129\n",
       "   9 │ CS001212000046         228           2.35793\n",
       "  10 │ CS001212000070         456           2.65896"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-061 常用対数化\n",
    "@chain df_receipts begin\n",
    "    @subset(occursin.(r\"^[^Z]\", :customer_id))\n",
    "    groupby(:customer_id)\n",
    "    combine(:amount => sum)\n",
    "    @transform(:amount_sum_log10 = log10.(:amount_sum))\n",
    "    sort(:customer_id)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "5bdb09a0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">customer_id</th><th style = \"text-align: left;\">amount_sum</th><th style = \"text-align: left;\">amount_sum_log</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">CS001113000004</td><td style = \"text-align: right;\">1298</td><td style = \"text-align: right;\">7.16858</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: right;\">626</td><td style = \"text-align: right;\">6.43935</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: right;\">3044</td><td style = \"text-align: right;\">8.02093</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS001205000004</td><td style = \"text-align: right;\">1988</td><td style = \"text-align: right;\">7.59488</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS001205000006</td><td style = \"text-align: right;\">3337</td><td style = \"text-align: right;\">8.11283</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS001211000025</td><td style = \"text-align: right;\">456</td><td style = \"text-align: right;\">6.12249</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS001212000027</td><td style = \"text-align: right;\">448</td><td style = \"text-align: right;\">6.10479</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS001212000031</td><td style = \"text-align: right;\">296</td><td style = \"text-align: right;\">5.69036</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS001212000046</td><td style = \"text-align: right;\">228</td><td style = \"text-align: right;\">5.42935</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS001212000070</td><td style = \"text-align: right;\">456</td><td style = \"text-align: right;\">6.12249</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& customer\\_id & amount\\_sum & amount\\_sum\\_log\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & CS001113000004 & 1298 & 7.16858 \\\\\n",
       "\t2 & CS001114000005 & 626 & 6.43935 \\\\\n",
       "\t3 & CS001115000010 & 3044 & 8.02093 \\\\\n",
       "\t4 & CS001205000004 & 1988 & 7.59488 \\\\\n",
       "\t5 & CS001205000006 & 3337 & 8.11283 \\\\\n",
       "\t6 & CS001211000025 & 456 & 6.12249 \\\\\n",
       "\t7 & CS001212000027 & 448 & 6.10479 \\\\\n",
       "\t8 & CS001212000031 & 296 & 5.69036 \\\\\n",
       "\t9 & CS001212000046 & 228 & 5.42935 \\\\\n",
       "\t10 & CS001212000070 & 456 & 6.12249 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m customer_id    \u001b[0m\u001b[1m amount_sum \u001b[0m\u001b[1m amount_sum_log \u001b[0m\n",
       "     │\u001b[90m String15       \u001b[0m\u001b[90m Int64      \u001b[0m\u001b[90m Float64        \u001b[0m\n",
       "─────┼────────────────────────────────────────────\n",
       "   1 │ CS001113000004        1298         7.16858\n",
       "   2 │ CS001114000005         626         6.43935\n",
       "   3 │ CS001115000010        3044         8.02093\n",
       "   4 │ CS001205000004        1988         7.59488\n",
       "   5 │ CS001205000006        3337         8.11283\n",
       "   6 │ CS001211000025         456         6.12249\n",
       "   7 │ CS001212000027         448         6.10479\n",
       "   8 │ CS001212000031         296         5.69036\n",
       "   9 │ CS001212000046         228         5.42935\n",
       "  10 │ CS001212000070         456         6.12249"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-062 自然対数化\n",
    "@chain df_receipts begin\n",
    "    @rsubset(occursin(r\"^[^Z]\", :customer_id))\n",
    "    groupby(:customer_id)\n",
    "    combine(:amount => sum)\n",
    "    @rtransform(:amount_sum_log = log(:amount_sum))\n",
    "    sort(:customer_id)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7b6e9572",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×4 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">product_cd</th><th style = \"text-align: left;\">unit_price</th><th style = \"text-align: left;\">unit_cost</th><th style = \"text-align: left;\">unit_profit</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">P040101001</td><td style = \"text-align: right;\">198</td><td style = \"text-align: right;\">149</td><td style = \"text-align: right;\">49</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">P040101002</td><td style = \"text-align: right;\">218</td><td style = \"text-align: right;\">164</td><td style = \"text-align: right;\">54</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">P040101003</td><td style = \"text-align: right;\">230</td><td style = \"text-align: right;\">173</td><td style = \"text-align: right;\">57</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">P040101004</td><td style = \"text-align: right;\">248</td><td style = \"text-align: right;\">186</td><td style = \"text-align: right;\">62</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">P040101005</td><td style = \"text-align: right;\">268</td><td style = \"text-align: right;\">201</td><td style = \"text-align: right;\">67</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">P040101006</td><td style = \"text-align: right;\">298</td><td style = \"text-align: right;\">224</td><td style = \"text-align: right;\">74</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">P040101007</td><td style = \"text-align: right;\">338</td><td style = \"text-align: right;\">254</td><td style = \"text-align: right;\">84</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">P040101008</td><td style = \"text-align: right;\">420</td><td style = \"text-align: right;\">315</td><td style = \"text-align: right;\">105</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">P040101009</td><td style = \"text-align: right;\">498</td><td style = \"text-align: right;\">374</td><td style = \"text-align: right;\">124</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">P040101010</td><td style = \"text-align: right;\">580</td><td style = \"text-align: right;\">435</td><td style = \"text-align: right;\">145</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& product\\_cd & unit\\_price & unit\\_cost & unit\\_profit\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64? & Int64? & Int64?\\\\\n",
       "\t\\hline\n",
       "\t1 & P040101001 & 198 & 149 & 49 \\\\\n",
       "\t2 & P040101002 & 218 & 164 & 54 \\\\\n",
       "\t3 & P040101003 & 230 & 173 & 57 \\\\\n",
       "\t4 & P040101004 & 248 & 186 & 62 \\\\\n",
       "\t5 & P040101005 & 268 & 201 & 67 \\\\\n",
       "\t6 & P040101006 & 298 & 224 & 74 \\\\\n",
       "\t7 & P040101007 & 338 & 254 & 84 \\\\\n",
       "\t8 & P040101008 & 420 & 315 & 105 \\\\\n",
       "\t9 & P040101009 & 498 & 374 & 124 \\\\\n",
       "\t10 & P040101010 & 580 & 435 & 145 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×4 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m product_cd \u001b[0m\u001b[1m unit_price \u001b[0m\u001b[1m unit_cost \u001b[0m\u001b[1m unit_profit \u001b[0m\n",
       "     │\u001b[90m String15   \u001b[0m\u001b[90m Int64?     \u001b[0m\u001b[90m Int64?    \u001b[0m\u001b[90m Int64?      \u001b[0m\n",
       "─────┼────────────────────────────────────────────────\n",
       "   1 │ P040101001         198        149           49\n",
       "   2 │ P040101002         218        164           54\n",
       "   3 │ P040101003         230        173           57\n",
       "   4 │ P040101004         248        186           62\n",
       "   5 │ P040101005         268        201           67\n",
       "   6 │ P040101006         298        224           74\n",
       "   7 │ P040101007         338        254           84\n",
       "   8 │ P040101008         420        315          105\n",
       "   9 │ P040101009         498        374          124\n",
       "  10 │ P040101010         580        435          145"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-063 各商品の利益額\n",
    "@chain df_products begin\n",
    "    select(:product_cd, :unit_price, :unit_cost)\n",
    "    @rtransform(:unit_profit = :unit_price - :unit_cost)\n",
    "    sort(:product_cd)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "ddf86b4b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>1×1 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">unit_profit_ratio_mean</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Float64\" style = \"text-align: left;\">Float64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">0.249114</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|c}\n",
       "\t& unit\\_profit\\_ratio\\_mean\\\\\n",
       "\t\\hline\n",
       "\t& Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 0.249114 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m1×1 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m unit_profit_ratio_mean \u001b[0m\n",
       "     │\u001b[90m Float64                \u001b[0m\n",
       "─────┼────────────────────────\n",
       "   1 │               0.249114"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-064 利益率の全体平均\n",
    "@chain df_products begin\n",
    "    select(:product_cd, :unit_price, :unit_cost)\n",
    "    @rtransform(:unit_profit_ratio = \n",
    "        (:unit_price - :unit_cost) / :unit_price)\n",
    "    # @subset(ismissing.(:unit_profit_ratio)) # =>b 7件存在\n",
    "    dropmissing(:unit_profit_ratio)\n",
    "    combine(:unit_profit_ratio => mean)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "65955e67",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×4 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">product_cd</th><th style = \"text-align: left;\">unit_cost</th><th style = \"text-align: left;\">unit_new_price</th><th style = \"text-align: left;\">unit_new_profit</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Union{Missing, Float64}\" style = \"text-align: left;\">Float64?</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">P040101001</td><td style = \"text-align: right;\">149</td><td style = \"text-align: right;\">212</td><td style = \"text-align: right;\">0.29717</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">P040101002</td><td style = \"text-align: right;\">164</td><td style = \"text-align: right;\">234</td><td style = \"text-align: right;\">0.299145</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">P040101003</td><td style = \"text-align: right;\">173</td><td style = \"text-align: right;\">247</td><td style = \"text-align: right;\">0.299595</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">P040101004</td><td style = \"text-align: right;\">186</td><td style = \"text-align: right;\">265</td><td style = \"text-align: right;\">0.298113</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">P040101005</td><td style = \"text-align: right;\">201</td><td style = \"text-align: right;\">287</td><td style = \"text-align: right;\">0.299652</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">P040101006</td><td style = \"text-align: right;\">224</td><td style = \"text-align: right;\">320</td><td style = \"text-align: right;\">0.3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">P040101007</td><td style = \"text-align: right;\">254</td><td style = \"text-align: right;\">362</td><td style = \"text-align: right;\">0.298343</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">P040101008</td><td style = \"text-align: right;\">315</td><td style = \"text-align: right;\">450</td><td style = \"text-align: right;\">0.3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">P040101009</td><td style = \"text-align: right;\">374</td><td style = \"text-align: right;\">534</td><td style = \"text-align: right;\">0.299625</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">P040101010</td><td style = \"text-align: right;\">435</td><td style = \"text-align: right;\">621</td><td style = \"text-align: right;\">0.299517</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& product\\_cd & unit\\_cost & unit\\_new\\_price & unit\\_new\\_profit\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64? & Int64 & Float64?\\\\\n",
       "\t\\hline\n",
       "\t1 & P040101001 & 149 & 212 & 0.29717 \\\\\n",
       "\t2 & P040101002 & 164 & 234 & 0.299145 \\\\\n",
       "\t3 & P040101003 & 173 & 247 & 0.299595 \\\\\n",
       "\t4 & P040101004 & 186 & 265 & 0.298113 \\\\\n",
       "\t5 & P040101005 & 201 & 287 & 0.299652 \\\\\n",
       "\t6 & P040101006 & 224 & 320 & 0.3 \\\\\n",
       "\t7 & P040101007 & 254 & 362 & 0.298343 \\\\\n",
       "\t8 & P040101008 & 315 & 450 & 0.3 \\\\\n",
       "\t9 & P040101009 & 374 & 534 & 0.299625 \\\\\n",
       "\t10 & P040101010 & 435 & 621 & 0.299517 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×4 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m product_cd \u001b[0m\u001b[1m unit_cost \u001b[0m\u001b[1m unit_new_price \u001b[0m\u001b[1m unit_new_profit \u001b[0m\n",
       "     │\u001b[90m String15   \u001b[0m\u001b[90m Int64?    \u001b[0m\u001b[90m Int64          \u001b[0m\u001b[90m Float64?        \u001b[0m\n",
       "─────┼────────────────────────────────────────────────────────\n",
       "   1 │ P040101001        149             212         0.29717\n",
       "   2 │ P040101002        164             234         0.299145\n",
       "   3 │ P040101003        173             247         0.299595\n",
       "   4 │ P040101004        186             265         0.298113\n",
       "   5 │ P040101005        201             287         0.299652\n",
       "   6 │ P040101006        224             320         0.3\n",
       "   7 │ P040101007        254             362         0.298343\n",
       "   8 │ P040101008        315             450         0.3\n",
       "   9 │ P040101009        374             534         0.299625\n",
       "  10 │ P040101010        435             621         0.299517"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-065 利益率３０％になる単価: 切り捨て\n",
    "@chain df_products begin\n",
    "    select(:product_cd, :unit_cost)\n",
    "    @rtransform(:unit_new_price = \n",
    "        ismissing(:unit_cost) ? 0 : floor(Int, :unit_cost / 0.7))\n",
    "    @rtransform(:unit_new_profit = \n",
    "        (:unit_new_price - :unit_cost) / :unit_new_price)\n",
    "    sort(:product_cd)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "4cb6c374",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×4 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">product_cd</th><th style = \"text-align: left;\">unit_cost</th><th style = \"text-align: left;\">unit_new_price</th><th style = \"text-align: left;\">unit_new_profit</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Union{Missing, Float64}\" style = \"text-align: left;\">Float64?</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">P040101001</td><td style = \"text-align: right;\">149</td><td style = \"text-align: right;\">213</td><td style = \"text-align: right;\">0.300469</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">P040101002</td><td style = \"text-align: right;\">164</td><td style = \"text-align: right;\">234</td><td style = \"text-align: right;\">0.299145</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">P040101003</td><td style = \"text-align: right;\">173</td><td style = \"text-align: right;\">247</td><td style = \"text-align: right;\">0.299595</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">P040101004</td><td style = \"text-align: right;\">186</td><td style = \"text-align: right;\">266</td><td style = \"text-align: right;\">0.300752</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">P040101005</td><td style = \"text-align: right;\">201</td><td style = \"text-align: right;\">287</td><td style = \"text-align: right;\">0.299652</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">P040101006</td><td style = \"text-align: right;\">224</td><td style = \"text-align: right;\">320</td><td style = \"text-align: right;\">0.3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">P040101007</td><td style = \"text-align: right;\">254</td><td style = \"text-align: right;\">363</td><td style = \"text-align: right;\">0.300275</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">P040101008</td><td style = \"text-align: right;\">315</td><td style = \"text-align: right;\">450</td><td style = \"text-align: right;\">0.3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">P040101009</td><td style = \"text-align: right;\">374</td><td style = \"text-align: right;\">534</td><td style = \"text-align: right;\">0.299625</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">P040101010</td><td style = \"text-align: right;\">435</td><td style = \"text-align: right;\">621</td><td style = \"text-align: right;\">0.299517</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& product\\_cd & unit\\_cost & unit\\_new\\_price & unit\\_new\\_profit\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64? & Int64 & Float64?\\\\\n",
       "\t\\hline\n",
       "\t1 & P040101001 & 149 & 213 & 0.300469 \\\\\n",
       "\t2 & P040101002 & 164 & 234 & 0.299145 \\\\\n",
       "\t3 & P040101003 & 173 & 247 & 0.299595 \\\\\n",
       "\t4 & P040101004 & 186 & 266 & 0.300752 \\\\\n",
       "\t5 & P040101005 & 201 & 287 & 0.299652 \\\\\n",
       "\t6 & P040101006 & 224 & 320 & 0.3 \\\\\n",
       "\t7 & P040101007 & 254 & 363 & 0.300275 \\\\\n",
       "\t8 & P040101008 & 315 & 450 & 0.3 \\\\\n",
       "\t9 & P040101009 & 374 & 534 & 0.299625 \\\\\n",
       "\t10 & P040101010 & 435 & 621 & 0.299517 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×4 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m product_cd \u001b[0m\u001b[1m unit_cost \u001b[0m\u001b[1m unit_new_price \u001b[0m\u001b[1m unit_new_profit \u001b[0m\n",
       "     │\u001b[90m String15   \u001b[0m\u001b[90m Int64?    \u001b[0m\u001b[90m Int64          \u001b[0m\u001b[90m Float64?        \u001b[0m\n",
       "─────┼────────────────────────────────────────────────────────\n",
       "   1 │ P040101001        149             213         0.300469\n",
       "   2 │ P040101002        164             234         0.299145\n",
       "   3 │ P040101003        173             247         0.299595\n",
       "   4 │ P040101004        186             266         0.300752\n",
       "   5 │ P040101005        201             287         0.299652\n",
       "   6 │ P040101006        224             320         0.3\n",
       "   7 │ P040101007        254             363         0.300275\n",
       "   8 │ P040101008        315             450         0.3\n",
       "   9 │ P040101009        374             534         0.299625\n",
       "  10 │ P040101010        435             621         0.299517"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-066 利益率３０％になる単価: 丸め\n",
    "@chain df_products begin\n",
    "    select(:product_cd, :unit_cost)\n",
    "    @rtransform(:unit_new_price =\n",
    "        ismissing(:unit_cost) ? 0 : round(Int, :unit_cost / 0.7))\n",
    "    @rtransform(:unit_new_profit = \n",
    "        (:unit_new_price - :unit_cost) / :unit_new_price)\n",
    "    sort(:product_cd)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "ff2977b7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×4 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">product_cd</th><th style = \"text-align: left;\">unit_cost</th><th style = \"text-align: left;\">unit_new_price</th><th style = \"text-align: left;\">unit_new_profit</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Union{Missing, Float64}\" style = \"text-align: left;\">Float64?</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">P040101001</td><td style = \"text-align: right;\">149</td><td style = \"text-align: right;\">213</td><td style = \"text-align: right;\">0.300469</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">P040101002</td><td style = \"text-align: right;\">164</td><td style = \"text-align: right;\">235</td><td style = \"text-align: right;\">0.302128</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">P040101003</td><td style = \"text-align: right;\">173</td><td style = \"text-align: right;\">248</td><td style = \"text-align: right;\">0.302419</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">P040101004</td><td style = \"text-align: right;\">186</td><td style = \"text-align: right;\">266</td><td style = \"text-align: right;\">0.300752</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">P040101005</td><td style = \"text-align: right;\">201</td><td style = \"text-align: right;\">288</td><td style = \"text-align: right;\">0.302083</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">P040101006</td><td style = \"text-align: right;\">224</td><td style = \"text-align: right;\">320</td><td style = \"text-align: right;\">0.3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">P040101007</td><td style = \"text-align: right;\">254</td><td style = \"text-align: right;\">363</td><td style = \"text-align: right;\">0.300275</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">P040101008</td><td style = \"text-align: right;\">315</td><td style = \"text-align: right;\">451</td><td style = \"text-align: right;\">0.301552</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">P040101009</td><td style = \"text-align: right;\">374</td><td style = \"text-align: right;\">535</td><td style = \"text-align: right;\">0.300935</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">P040101010</td><td style = \"text-align: right;\">435</td><td style = \"text-align: right;\">622</td><td style = \"text-align: right;\">0.300643</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& product\\_cd & unit\\_cost & unit\\_new\\_price & unit\\_new\\_profit\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64? & Int64 & Float64?\\\\\n",
       "\t\\hline\n",
       "\t1 & P040101001 & 149 & 213 & 0.300469 \\\\\n",
       "\t2 & P040101002 & 164 & 235 & 0.302128 \\\\\n",
       "\t3 & P040101003 & 173 & 248 & 0.302419 \\\\\n",
       "\t4 & P040101004 & 186 & 266 & 0.300752 \\\\\n",
       "\t5 & P040101005 & 201 & 288 & 0.302083 \\\\\n",
       "\t6 & P040101006 & 224 & 320 & 0.3 \\\\\n",
       "\t7 & P040101007 & 254 & 363 & 0.300275 \\\\\n",
       "\t8 & P040101008 & 315 & 451 & 0.301552 \\\\\n",
       "\t9 & P040101009 & 374 & 535 & 0.300935 \\\\\n",
       "\t10 & P040101010 & 435 & 622 & 0.300643 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×4 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m product_cd \u001b[0m\u001b[1m unit_cost \u001b[0m\u001b[1m unit_new_price \u001b[0m\u001b[1m unit_new_profit \u001b[0m\n",
       "     │\u001b[90m String15   \u001b[0m\u001b[90m Int64?    \u001b[0m\u001b[90m Int64          \u001b[0m\u001b[90m Float64?        \u001b[0m\n",
       "─────┼────────────────────────────────────────────────────────\n",
       "   1 │ P040101001        149             213         0.300469\n",
       "   2 │ P040101002        164             235         0.302128\n",
       "   3 │ P040101003        173             248         0.302419\n",
       "   4 │ P040101004        186             266         0.300752\n",
       "   5 │ P040101005        201             288         0.302083\n",
       "   6 │ P040101006        224             320         0.3\n",
       "   7 │ P040101007        254             363         0.300275\n",
       "   8 │ P040101008        315             451         0.301552\n",
       "   9 │ P040101009        374             535         0.300935\n",
       "  10 │ P040101010        435             622         0.300643"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-066 利益率３０％になる単価: 切り上げ\n",
    "@chain df_products begin\n",
    "    select(:product_cd, :unit_cost)\n",
    "    @rtransform(:unit_new_price =\n",
    "        ismissing(:unit_cost) ? 0 : ceil(Int, :unit_cost / 0.7))\n",
    "    @rtransform(:unit_new_profit = \n",
    "        (:unit_new_price - :unit_cost) / :unit_new_price)\n",
    "    sort(:product_cd)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "ce838f66",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">product_cd</th><th style = \"text-align: left;\">unit_price</th><th style = \"text-align: left;\">tax_price</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">P040101001</td><td style = \"text-align: right;\">198</td><td style = \"text-align: right;\">217</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">P040101002</td><td style = \"text-align: right;\">218</td><td style = \"text-align: right;\">239</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">P040101003</td><td style = \"text-align: right;\">230</td><td style = \"text-align: right;\">253</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">P040101004</td><td style = \"text-align: right;\">248</td><td style = \"text-align: right;\">272</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">P040101005</td><td style = \"text-align: right;\">268</td><td style = \"text-align: right;\">294</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">P040101006</td><td style = \"text-align: right;\">298</td><td style = \"text-align: right;\">327</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">P040101007</td><td style = \"text-align: right;\">338</td><td style = \"text-align: right;\">371</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">P040101008</td><td style = \"text-align: right;\">420</td><td style = \"text-align: right;\">462</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">P040101009</td><td style = \"text-align: right;\">498</td><td style = \"text-align: right;\">547</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">P040101010</td><td style = \"text-align: right;\">580</td><td style = \"text-align: right;\">638</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& product\\_cd & unit\\_price & tax\\_price\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64? & Int64?\\\\\n",
       "\t\\hline\n",
       "\t1 & P040101001 & 198 & 217 \\\\\n",
       "\t2 & P040101002 & 218 & 239 \\\\\n",
       "\t3 & P040101003 & 230 & 253 \\\\\n",
       "\t4 & P040101004 & 248 & 272 \\\\\n",
       "\t5 & P040101005 & 268 & 294 \\\\\n",
       "\t6 & P040101006 & 298 & 327 \\\\\n",
       "\t7 & P040101007 & 338 & 371 \\\\\n",
       "\t8 & P040101008 & 420 & 462 \\\\\n",
       "\t9 & P040101009 & 498 & 547 \\\\\n",
       "\t10 & P040101010 & 580 & 638 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m product_cd \u001b[0m\u001b[1m unit_price \u001b[0m\u001b[1m tax_price \u001b[0m\n",
       "     │\u001b[90m String15   \u001b[0m\u001b[90m Int64?     \u001b[0m\u001b[90m Int64?    \u001b[0m\n",
       "─────┼───────────────────────────────────\n",
       "   1 │ P040101001         198        217\n",
       "   2 │ P040101002         218        239\n",
       "   3 │ P040101003         230        253\n",
       "   4 │ P040101004         248        272\n",
       "   5 │ P040101005         268        294\n",
       "   6 │ P040101006         298        327\n",
       "   7 │ P040101007         338        371\n",
       "   8 │ P040101008         420        462\n",
       "   9 │ P040101009         498        547\n",
       "  10 │ P040101010         580        638"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-068 消費税\n",
    "@chain df_products begin\n",
    "    select(:product_cd, :unit_price)\n",
    "    @rtransform(:tax_price = ismissing(:unit_price) ? missing\n",
    "        : floor(Int, :unit_price * 1.1))\n",
    "    sort(:product_cd)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "a8cd7d63",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×5 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">customer_id</th><th style = \"text-align: left;\">category_major_cd</th><th style = \"text-align: left;\">amount_sum</th><th style = \"text-align: left;\">all_amount_sum</th><th style = \"text-align: left;\">amount_ratio</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">CS001113000004</td><td style = \"text-align: right;\">7</td><td style = \"text-align: right;\">1298</td><td style = \"text-align: right;\">1298</td><td style = \"text-align: right;\">1.0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: right;\">7</td><td style = \"text-align: right;\">486</td><td style = \"text-align: right;\">626</td><td style = \"text-align: right;\">0.776358</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: right;\">7</td><td style = \"text-align: right;\">2694</td><td style = \"text-align: right;\">3044</td><td style = \"text-align: right;\">0.88502</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS001205000004</td><td style = \"text-align: right;\">7</td><td style = \"text-align: right;\">346</td><td style = \"text-align: right;\">1988</td><td style = \"text-align: right;\">0.174044</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS001205000006</td><td style = \"text-align: right;\">7</td><td style = \"text-align: right;\">2004</td><td style = \"text-align: right;\">3337</td><td style = \"text-align: right;\">0.600539</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS001212000027</td><td style = \"text-align: right;\">7</td><td style = \"text-align: right;\">200</td><td style = \"text-align: right;\">448</td><td style = \"text-align: right;\">0.446429</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS001212000031</td><td style = \"text-align: right;\">7</td><td style = \"text-align: right;\">296</td><td style = \"text-align: right;\">296</td><td style = \"text-align: right;\">1.0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS001212000046</td><td style = \"text-align: right;\">7</td><td style = \"text-align: right;\">108</td><td style = \"text-align: right;\">228</td><td style = \"text-align: right;\">0.473684</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS001212000070</td><td style = \"text-align: right;\">7</td><td style = \"text-align: right;\">308</td><td style = \"text-align: right;\">456</td><td style = \"text-align: right;\">0.675439</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS001213000018</td><td style = \"text-align: right;\">7</td><td style = \"text-align: right;\">145</td><td style = \"text-align: right;\">243</td><td style = \"text-align: right;\">0.596708</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccccc}\n",
       "\t& customer\\_id & category\\_major\\_cd & amount\\_sum & all\\_amount\\_sum & amount\\_ratio\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64 & Int64 & Int64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & CS001113000004 & 7 & 1298 & 1298 & 1.0 \\\\\n",
       "\t2 & CS001114000005 & 7 & 486 & 626 & 0.776358 \\\\\n",
       "\t3 & CS001115000010 & 7 & 2694 & 3044 & 0.88502 \\\\\n",
       "\t4 & CS001205000004 & 7 & 346 & 1988 & 0.174044 \\\\\n",
       "\t5 & CS001205000006 & 7 & 2004 & 3337 & 0.600539 \\\\\n",
       "\t6 & CS001212000027 & 7 & 200 & 448 & 0.446429 \\\\\n",
       "\t7 & CS001212000031 & 7 & 296 & 296 & 1.0 \\\\\n",
       "\t8 & CS001212000046 & 7 & 108 & 228 & 0.473684 \\\\\n",
       "\t9 & CS001212000070 & 7 & 308 & 456 & 0.675439 \\\\\n",
       "\t10 & CS001213000018 & 7 & 145 & 243 & 0.596708 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×5 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m customer_id    \u001b[0m\u001b[1m category_major_cd \u001b[0m\u001b[1m amount_sum \u001b[0m\u001b[1m all_amount_sum \u001b[0m\u001b[1m amount_ratio \u001b[0m\n",
       "     │\u001b[90m String15       \u001b[0m\u001b[90m Int64             \u001b[0m\u001b[90m Int64      \u001b[0m\u001b[90m Int64          \u001b[0m\u001b[90m Float64      \u001b[0m\n",
       "─────┼─────────────────────────────────────────────────────────────────────────────\n",
       "   1 │ CS001113000004                  7        1298            1298      1.0\n",
       "   2 │ CS001114000005                  7         486             626      0.776358\n",
       "   3 │ CS001115000010                  7        2694            3044      0.88502\n",
       "   4 │ CS001205000004                  7         346            1988      0.174044\n",
       "   5 │ CS001205000006                  7        2004            3337      0.600539\n",
       "   6 │ CS001212000027                  7         200             448      0.446429\n",
       "   7 │ CS001212000031                  7         296             296      1.0\n",
       "   8 │ CS001212000046                  7         108             228      0.473684\n",
       "   9 │ CS001212000070                  7         308             456      0.675439\n",
       "  10 │ CS001213000018                  7         145             243      0.596708"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-069 売上金額合計のうち瓶詰缶詰の比率\n",
    "categories_amounts = @chain df_receipts begin\n",
    "    innerjoin(df_products, on= :product_cd => :product_cd,\n",
    "        makeunique=true)\n",
    "    groupby([:customer_id, :category_major_cd])\n",
    "    combine(:amount => sum)\n",
    "    sort([:customer_id, :category_major_cd])\n",
    "end\n",
    "\n",
    "customers_amounts = @chain categories_amounts begin\n",
    "    groupby(:customer_id)\n",
    "    combine(:amount_sum => sum => :all_amount_sum)\n",
    "end\n",
    "\n",
    "@chain categories_amounts begin\n",
    "    @rsubset(:category_major_cd == 7)\n",
    "    innerjoin(customers_amounts, on= :customer_id => :customer_id)\n",
    "    @rtransform(:amount_ratio = :amount_sum / :all_amount_sum)\n",
    "    sort(:customer_id)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "6d5a3f37",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×4 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">customer_id</th><th style = \"text-align: left;\">sales_ymd</th><th style = \"text-align: left;\">application_date</th><th style = \"text-align: left;\">elapsed_days</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Day\" style = \"text-align: left;\">Day</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">CS001113000004</td><td style = \"text-align: left;\">2019-03-08</td><td style = \"text-align: left;\">2015-11-05</td><td style = \"text-align: left;\">1219 days</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS001113000004</td><td style = \"text-align: left;\">2019-03-08</td><td style = \"text-align: left;\">2015-11-05</td><td style = \"text-align: left;\">1219 days</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: left;\">2018-05-03</td><td style = \"text-align: left;\">2016-04-12</td><td style = \"text-align: left;\">751 days</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: left;\">2018-05-03</td><td style = \"text-align: left;\">2016-04-12</td><td style = \"text-align: left;\">751 days</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: left;\">2019-07-31</td><td style = \"text-align: left;\">2016-04-12</td><td style = \"text-align: left;\">1205 days</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: left;\">2019-07-31</td><td style = \"text-align: left;\">2016-04-12</td><td style = \"text-align: left;\">1205 days</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: left;\">2019-04-05</td><td style = \"text-align: left;\">2015-04-17</td><td style = \"text-align: left;\">1449 days</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: left;\">2018-07-01</td><td style = \"text-align: left;\">2015-04-17</td><td style = \"text-align: left;\">1171 days</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: left;\">2017-12-28</td><td style = \"text-align: left;\">2015-04-17</td><td style = \"text-align: left;\">986 days</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: left;\">2019-04-05</td><td style = \"text-align: left;\">2015-04-17</td><td style = \"text-align: left;\">1449 days</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& customer\\_id & sales\\_ymd & application\\_date & elapsed\\_days\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Date & Date & Day\\\\\n",
       "\t\\hline\n",
       "\t1 & CS001113000004 & 2019-03-08 & 2015-11-05 & 1219 days \\\\\n",
       "\t2 & CS001113000004 & 2019-03-08 & 2015-11-05 & 1219 days \\\\\n",
       "\t3 & CS001114000005 & 2018-05-03 & 2016-04-12 & 751 days \\\\\n",
       "\t4 & CS001114000005 & 2018-05-03 & 2016-04-12 & 751 days \\\\\n",
       "\t5 & CS001114000005 & 2019-07-31 & 2016-04-12 & 1205 days \\\\\n",
       "\t6 & CS001114000005 & 2019-07-31 & 2016-04-12 & 1205 days \\\\\n",
       "\t7 & CS001115000010 & 2019-04-05 & 2015-04-17 & 1449 days \\\\\n",
       "\t8 & CS001115000010 & 2018-07-01 & 2015-04-17 & 1171 days \\\\\n",
       "\t9 & CS001115000010 & 2017-12-28 & 2015-04-17 & 986 days \\\\\n",
       "\t10 & CS001115000010 & 2019-04-05 & 2015-04-17 & 1449 days \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×4 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m customer_id    \u001b[0m\u001b[1m sales_ymd  \u001b[0m\u001b[1m application_date \u001b[0m\u001b[1m elapsed_days \u001b[0m\n",
       "     │\u001b[90m String15       \u001b[0m\u001b[90m Date       \u001b[0m\u001b[90m Date             \u001b[0m\u001b[90m Day          \u001b[0m\n",
       "─────┼────────────────────────────────────────────────────────────\n",
       "   1 │ CS001113000004  2019-03-08  2015-11-05        1219 days\n",
       "   2 │ CS001113000004  2019-03-08  2015-11-05        1219 days\n",
       "   3 │ CS001114000005  2018-05-03  2016-04-12        751 days\n",
       "   4 │ CS001114000005  2018-05-03  2016-04-12        751 days\n",
       "   5 │ CS001114000005  2019-07-31  2016-04-12        1205 days\n",
       "   6 │ CS001114000005  2019-07-31  2016-04-12        1205 days\n",
       "   7 │ CS001115000010  2019-04-05  2015-04-17        1449 days\n",
       "   8 │ CS001115000010  2018-07-01  2015-04-17        1171 days\n",
       "   9 │ CS001115000010  2017-12-28  2015-04-17        986 days\n",
       "  10 │ CS001115000010  2019-04-05  2015-04-17        1449 days"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-070 経過日数\n",
    "using Dates\n",
    "@chain df_receipts begin\n",
    "    innerjoin(df_customers, on= :customer_id => :customer_id,\n",
    "        makeunique=true)\n",
    "    @rtransform(:sales_ymd = Date(string(:sales_ymd), \"YYYYmmdd\"))\n",
    "    @rtransform(:application_date = Date(string(:application_date), \"YYYYmmdd\"))\n",
    "    select(:customer_id, :sales_ymd, :application_date)\n",
    "    @rtransform(:elapsed_days = :sales_ymd - :application_date)\n",
    "    sort(:customer_id)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "94d4c146",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×4 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">customer_id</th><th style = \"text-align: left;\">sales_ymd</th><th style = \"text-align: left;\">application_date</th><th style = \"text-align: left;\">elapsed_month</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">CS001113000004</td><td style = \"text-align: left;\">2019-03-08</td><td style = \"text-align: left;\">2015-11-05</td><td style = \"text-align: right;\">40</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS001113000004</td><td style = \"text-align: left;\">2019-03-08</td><td style = \"text-align: left;\">2015-11-05</td><td style = \"text-align: right;\">40</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: left;\">2018-05-03</td><td style = \"text-align: left;\">2016-04-12</td><td style = \"text-align: right;\">25</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: left;\">2018-05-03</td><td style = \"text-align: left;\">2016-04-12</td><td style = \"text-align: right;\">25</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: left;\">2019-07-31</td><td style = \"text-align: left;\">2016-04-12</td><td style = \"text-align: right;\">40</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: left;\">2019-07-31</td><td style = \"text-align: left;\">2016-04-12</td><td style = \"text-align: right;\">40</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: left;\">2019-04-05</td><td style = \"text-align: left;\">2015-04-17</td><td style = \"text-align: right;\">48</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: left;\">2018-07-01</td><td style = \"text-align: left;\">2015-04-17</td><td style = \"text-align: right;\">39</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: left;\">2017-12-28</td><td style = \"text-align: left;\">2015-04-17</td><td style = \"text-align: right;\">32</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: left;\">2019-04-05</td><td style = \"text-align: left;\">2015-04-17</td><td style = \"text-align: right;\">48</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& customer\\_id & sales\\_ymd & application\\_date & elapsed\\_month\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Date & Date & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & CS001113000004 & 2019-03-08 & 2015-11-05 & 40 \\\\\n",
       "\t2 & CS001113000004 & 2019-03-08 & 2015-11-05 & 40 \\\\\n",
       "\t3 & CS001114000005 & 2018-05-03 & 2016-04-12 & 25 \\\\\n",
       "\t4 & CS001114000005 & 2018-05-03 & 2016-04-12 & 25 \\\\\n",
       "\t5 & CS001114000005 & 2019-07-31 & 2016-04-12 & 40 \\\\\n",
       "\t6 & CS001114000005 & 2019-07-31 & 2016-04-12 & 40 \\\\\n",
       "\t7 & CS001115000010 & 2019-04-05 & 2015-04-17 & 48 \\\\\n",
       "\t8 & CS001115000010 & 2018-07-01 & 2015-04-17 & 39 \\\\\n",
       "\t9 & CS001115000010 & 2017-12-28 & 2015-04-17 & 32 \\\\\n",
       "\t10 & CS001115000010 & 2019-04-05 & 2015-04-17 & 48 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×4 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m customer_id    \u001b[0m\u001b[1m sales_ymd  \u001b[0m\u001b[1m application_date \u001b[0m\u001b[1m elapsed_month \u001b[0m\n",
       "     │\u001b[90m String15       \u001b[0m\u001b[90m Date       \u001b[0m\u001b[90m Date             \u001b[0m\u001b[90m Int64         \u001b[0m\n",
       "─────┼─────────────────────────────────────────────────────────────\n",
       "   1 │ CS001113000004  2019-03-08  2015-11-05                   40\n",
       "   2 │ CS001113000004  2019-03-08  2015-11-05                   40\n",
       "   3 │ CS001114000005  2018-05-03  2016-04-12                   25\n",
       "   4 │ CS001114000005  2018-05-03  2016-04-12                   25\n",
       "   5 │ CS001114000005  2019-07-31  2016-04-12                   40\n",
       "   6 │ CS001114000005  2019-07-31  2016-04-12                   40\n",
       "   7 │ CS001115000010  2019-04-05  2015-04-17                   48\n",
       "   8 │ CS001115000010  2018-07-01  2015-04-17                   39\n",
       "   9 │ CS001115000010  2017-12-28  2015-04-17                   32\n",
       "  10 │ CS001115000010  2019-04-05  2015-04-17                   48"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-071 経過月数\n",
    "using Dates\n",
    "@chain df_receipts begin\n",
    "    innerjoin(df_customers, on= :customer_id => :customer_id,\n",
    "        makeunique=true)\n",
    "    @rtransform(:sales_ymd = Date(string(:sales_ymd), \"YYYYmmdd\"))\n",
    "    @rtransform(:application_date = Date(string(:application_date), \"YYYYmmdd\"))\n",
    "    select(:customer_id, :sales_ymd, :application_date)\n",
    "    @rtransform(:elapsed_month = \n",
    "        Dates.value(:sales_ymd - :application_date) / 30\n",
    "        |> floor |> Int)\n",
    "    sort(:customer_id)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "e1621b3e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×4 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">customer_id</th><th style = \"text-align: left;\">sales_ymd</th><th style = \"text-align: left;\">application_date</th><th style = \"text-align: left;\">elapsed_year</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">CS001113000004</td><td style = \"text-align: left;\">2019-03-08</td><td style = \"text-align: left;\">2015-11-05</td><td style = \"text-align: right;\">3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS001113000004</td><td style = \"text-align: left;\">2019-03-08</td><td style = \"text-align: left;\">2015-11-05</td><td style = \"text-align: right;\">3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: left;\">2018-05-03</td><td style = \"text-align: left;\">2016-04-12</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: left;\">2018-05-03</td><td style = \"text-align: left;\">2016-04-12</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: left;\">2019-07-31</td><td style = \"text-align: left;\">2016-04-12</td><td style = \"text-align: right;\">3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: left;\">2019-07-31</td><td style = \"text-align: left;\">2016-04-12</td><td style = \"text-align: right;\">3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: left;\">2019-04-05</td><td style = \"text-align: left;\">2015-04-17</td><td style = \"text-align: right;\">3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: left;\">2018-07-01</td><td style = \"text-align: left;\">2015-04-17</td><td style = \"text-align: right;\">3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: left;\">2017-12-28</td><td style = \"text-align: left;\">2015-04-17</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: left;\">2019-04-05</td><td style = \"text-align: left;\">2015-04-17</td><td style = \"text-align: right;\">3</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& customer\\_id & sales\\_ymd & application\\_date & elapsed\\_year\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Date & Date & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & CS001113000004 & 2019-03-08 & 2015-11-05 & 3 \\\\\n",
       "\t2 & CS001113000004 & 2019-03-08 & 2015-11-05 & 3 \\\\\n",
       "\t3 & CS001114000005 & 2018-05-03 & 2016-04-12 & 2 \\\\\n",
       "\t4 & CS001114000005 & 2018-05-03 & 2016-04-12 & 2 \\\\\n",
       "\t5 & CS001114000005 & 2019-07-31 & 2016-04-12 & 3 \\\\\n",
       "\t6 & CS001114000005 & 2019-07-31 & 2016-04-12 & 3 \\\\\n",
       "\t7 & CS001115000010 & 2019-04-05 & 2015-04-17 & 3 \\\\\n",
       "\t8 & CS001115000010 & 2018-07-01 & 2015-04-17 & 3 \\\\\n",
       "\t9 & CS001115000010 & 2017-12-28 & 2015-04-17 & 2 \\\\\n",
       "\t10 & CS001115000010 & 2019-04-05 & 2015-04-17 & 3 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×4 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m customer_id    \u001b[0m\u001b[1m sales_ymd  \u001b[0m\u001b[1m application_date \u001b[0m\u001b[1m elapsed_year \u001b[0m\n",
       "     │\u001b[90m String15       \u001b[0m\u001b[90m Date       \u001b[0m\u001b[90m Date             \u001b[0m\u001b[90m Int64        \u001b[0m\n",
       "─────┼────────────────────────────────────────────────────────────\n",
       "   1 │ CS001113000004  2019-03-08  2015-11-05                   3\n",
       "   2 │ CS001113000004  2019-03-08  2015-11-05                   3\n",
       "   3 │ CS001114000005  2018-05-03  2016-04-12                   2\n",
       "   4 │ CS001114000005  2018-05-03  2016-04-12                   2\n",
       "   5 │ CS001114000005  2019-07-31  2016-04-12                   3\n",
       "   6 │ CS001114000005  2019-07-31  2016-04-12                   3\n",
       "   7 │ CS001115000010  2019-04-05  2015-04-17                   3\n",
       "   8 │ CS001115000010  2018-07-01  2015-04-17                   3\n",
       "   9 │ CS001115000010  2017-12-28  2015-04-17                   2\n",
       "  10 │ CS001115000010  2019-04-05  2015-04-17                   3"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-072 経過年数\n",
    "using Dates\n",
    "@chain df_receipts begin\n",
    "    innerjoin(df_customers, on= :customer_id => :customer_id,\n",
    "        makeunique=true)\n",
    "    @rtransform(:sales_ymd = Date(string(:sales_ymd), \"YYYYmmdd\"))\n",
    "    @rtransform(:application_date = Date(string(:application_date), \"YYYYmmdd\"))\n",
    "    select(:customer_id, :sales_ymd, :application_date)\n",
    "    @rtransform(:elapsed_year = \n",
    "        Dates.value(:sales_ymd - :application_date) / 365\n",
    "        |> floor |> Int)\n",
    "    sort(:customer_id)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "6a1017b0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×4 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">customer_id</th><th style = \"text-align: left;\">sales_ymd</th><th style = \"text-align: left;\">application_date</th><th style = \"text-align: left;\">elapsed_unixtime</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">CS001113000004</td><td style = \"text-align: left;\">2019-03-08</td><td style = \"text-align: left;\">2015-11-05</td><td style = \"text-align: right;\">1.05322e8</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS001113000004</td><td style = \"text-align: left;\">2019-03-08</td><td style = \"text-align: left;\">2015-11-05</td><td style = \"text-align: right;\">1.05322e8</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: left;\">2018-05-03</td><td style = \"text-align: left;\">2016-04-12</td><td style = \"text-align: right;\">6.48864e7</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: left;\">2018-05-03</td><td style = \"text-align: left;\">2016-04-12</td><td style = \"text-align: right;\">6.48864e7</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: left;\">2019-07-31</td><td style = \"text-align: left;\">2016-04-12</td><td style = \"text-align: right;\">1.04112e8</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS001114000005</td><td style = \"text-align: left;\">2019-07-31</td><td style = \"text-align: left;\">2016-04-12</td><td style = \"text-align: right;\">1.04112e8</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: left;\">2019-04-05</td><td style = \"text-align: left;\">2015-04-17</td><td style = \"text-align: right;\">1.25194e8</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: left;\">2018-07-01</td><td style = \"text-align: left;\">2015-04-17</td><td style = \"text-align: right;\">1.01174e8</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: left;\">2017-12-28</td><td style = \"text-align: left;\">2015-04-17</td><td style = \"text-align: right;\">8.51904e7</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS001115000010</td><td style = \"text-align: left;\">2019-04-05</td><td style = \"text-align: left;\">2015-04-17</td><td style = \"text-align: right;\">1.25194e8</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& customer\\_id & sales\\_ymd & application\\_date & elapsed\\_unixtime\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Date & Date & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & CS001113000004 & 2019-03-08 & 2015-11-05 & 1.05322e8 \\\\\n",
       "\t2 & CS001113000004 & 2019-03-08 & 2015-11-05 & 1.05322e8 \\\\\n",
       "\t3 & CS001114000005 & 2018-05-03 & 2016-04-12 & 6.48864e7 \\\\\n",
       "\t4 & CS001114000005 & 2018-05-03 & 2016-04-12 & 6.48864e7 \\\\\n",
       "\t5 & CS001114000005 & 2019-07-31 & 2016-04-12 & 1.04112e8 \\\\\n",
       "\t6 & CS001114000005 & 2019-07-31 & 2016-04-12 & 1.04112e8 \\\\\n",
       "\t7 & CS001115000010 & 2019-04-05 & 2015-04-17 & 1.25194e8 \\\\\n",
       "\t8 & CS001115000010 & 2018-07-01 & 2015-04-17 & 1.01174e8 \\\\\n",
       "\t9 & CS001115000010 & 2017-12-28 & 2015-04-17 & 8.51904e7 \\\\\n",
       "\t10 & CS001115000010 & 2019-04-05 & 2015-04-17 & 1.25194e8 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×4 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m customer_id    \u001b[0m\u001b[1m sales_ymd  \u001b[0m\u001b[1m application_date \u001b[0m\u001b[1m elapsed_unixtime \u001b[0m\n",
       "     │\u001b[90m String15       \u001b[0m\u001b[90m Date       \u001b[0m\u001b[90m Date             \u001b[0m\u001b[90m Float64          \u001b[0m\n",
       "─────┼────────────────────────────────────────────────────────────────\n",
       "   1 │ CS001113000004  2019-03-08  2015-11-05               1.05322e8\n",
       "   2 │ CS001113000004  2019-03-08  2015-11-05               1.05322e8\n",
       "   3 │ CS001114000005  2018-05-03  2016-04-12               6.48864e7\n",
       "   4 │ CS001114000005  2018-05-03  2016-04-12               6.48864e7\n",
       "   5 │ CS001114000005  2019-07-31  2016-04-12               1.04112e8\n",
       "   6 │ CS001114000005  2019-07-31  2016-04-12               1.04112e8\n",
       "   7 │ CS001115000010  2019-04-05  2015-04-17               1.25194e8\n",
       "   8 │ CS001115000010  2018-07-01  2015-04-17               1.01174e8\n",
       "   9 │ CS001115000010  2017-12-28  2015-04-17               8.51904e7\n",
       "  10 │ CS001115000010  2019-04-05  2015-04-17               1.25194e8"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-073 エポック秒による経過時間\n",
    "using Dates\n",
    "@chain df_receipts begin\n",
    "    innerjoin(df_customers, on= :customer_id => :customer_id,\n",
    "        makeunique=true)\n",
    "    @rtransform(:sales_ymd = Date(string(:sales_ymd), \"YYYYmmdd\"))\n",
    "    @rtransform(:application_date = Date(string(:application_date), \"YYYYmmdd\"))\n",
    "    select(:customer_id, :sales_ymd, :application_date)\n",
    "    @rtransform(:elapsed_unixtime = \n",
    "        datetime2unix(:sales_ymd |> DateTime) \n",
    "        - datetime2unix(:application_date |> DateTime))\n",
    "    sort(:customer_id)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "90a71c40",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×5 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">receipt_no</th><th style = \"text-align: left;\">sales_ymd</th><th style = \"text-align: left;\">dayabbr</th><th style = \"text-align: left;\">last_monday</th><th style = \"text-align: left;\">elapsed_day_of_week</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"String\" style = \"text-align: left;\">String</th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">112</td><td style = \"text-align: left;\">2018-11-03</td><td style = \"text-align: left;\">Sat</td><td style = \"text-align: left;\">2018-10-29</td><td style = \"text-align: right;\">5</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">1132</td><td style = \"text-align: left;\">2018-11-18</td><td style = \"text-align: left;\">Sun</td><td style = \"text-align: left;\">2018-11-12</td><td style = \"text-align: right;\">6</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">1102</td><td style = \"text-align: left;\">2017-07-12</td><td style = \"text-align: left;\">Wed</td><td style = \"text-align: left;\">2017-07-10</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: right;\">1132</td><td style = \"text-align: left;\">2019-02-05</td><td style = \"text-align: left;\">Tue</td><td style = \"text-align: left;\">2019-02-04</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: right;\">1102</td><td style = \"text-align: left;\">2018-08-21</td><td style = \"text-align: left;\">Tue</td><td style = \"text-align: left;\">2018-08-20</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: right;\">1112</td><td style = \"text-align: left;\">2019-06-05</td><td style = \"text-align: left;\">Wed</td><td style = \"text-align: left;\">2019-06-03</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: right;\">1102</td><td style = \"text-align: left;\">2018-12-05</td><td style = \"text-align: left;\">Wed</td><td style = \"text-align: left;\">2018-12-03</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: right;\">1102</td><td style = \"text-align: left;\">2019-09-22</td><td style = \"text-align: left;\">Sun</td><td style = \"text-align: left;\">2019-09-16</td><td style = \"text-align: right;\">6</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: right;\">1112</td><td style = \"text-align: left;\">2017-05-04</td><td style = \"text-align: left;\">Thu</td><td style = \"text-align: left;\">2017-05-01</td><td style = \"text-align: right;\">3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: right;\">1102</td><td style = \"text-align: left;\">2019-10-10</td><td style = \"text-align: left;\">Thu</td><td style = \"text-align: left;\">2019-10-07</td><td style = \"text-align: right;\">3</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccccc}\n",
       "\t& receipt\\_no & sales\\_ymd & dayabbr & last\\_monday & elapsed\\_day\\_of\\_week\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Date & String & Date & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 112 & 2018-11-03 & Sat & 2018-10-29 & 5 \\\\\n",
       "\t2 & 1132 & 2018-11-18 & Sun & 2018-11-12 & 6 \\\\\n",
       "\t3 & 1102 & 2017-07-12 & Wed & 2017-07-10 & 2 \\\\\n",
       "\t4 & 1132 & 2019-02-05 & Tue & 2019-02-04 & 1 \\\\\n",
       "\t5 & 1102 & 2018-08-21 & Tue & 2018-08-20 & 1 \\\\\n",
       "\t6 & 1112 & 2019-06-05 & Wed & 2019-06-03 & 2 \\\\\n",
       "\t7 & 1102 & 2018-12-05 & Wed & 2018-12-03 & 2 \\\\\n",
       "\t8 & 1102 & 2019-09-22 & Sun & 2019-09-16 & 6 \\\\\n",
       "\t9 & 1112 & 2017-05-04 & Thu & 2017-05-01 & 3 \\\\\n",
       "\t10 & 1102 & 2019-10-10 & Thu & 2019-10-07 & 3 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×5 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m receipt_no \u001b[0m\u001b[1m sales_ymd  \u001b[0m\u001b[1m dayabbr \u001b[0m\u001b[1m last_monday \u001b[0m\u001b[1m elapsed_day_of_week \u001b[0m\n",
       "     │\u001b[90m Int64      \u001b[0m\u001b[90m Date       \u001b[0m\u001b[90m String  \u001b[0m\u001b[90m Date        \u001b[0m\u001b[90m Int64               \u001b[0m\n",
       "─────┼───────────────────────────────────────────────────────────────────\n",
       "   1 │        112  2018-11-03  Sat      2018-10-29                     5\n",
       "   2 │       1132  2018-11-18  Sun      2018-11-12                     6\n",
       "   3 │       1102  2017-07-12  Wed      2017-07-10                     2\n",
       "   4 │       1132  2019-02-05  Tue      2019-02-04                     1\n",
       "   5 │       1102  2018-08-21  Tue      2018-08-20                     1\n",
       "   6 │       1112  2019-06-05  Wed      2019-06-03                     2\n",
       "   7 │       1102  2018-12-05  Wed      2018-12-03                     2\n",
       "   8 │       1102  2019-09-22  Sun      2019-09-16                     6\n",
       "   9 │       1112  2017-05-04  Thu      2017-05-01                     3\n",
       "  10 │       1102  2019-10-10  Thu      2019-10-07                     3"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-074 当該週の月曜日からの経過日数\n",
    "using Dates\n",
    "@chain df_receipts begin\n",
    "    select(:receipt_no, :sales_ymd)\n",
    "    @rtransform(:sales_ymd = Date(string(:sales_ymd), \"YYYYmmdd\"))\n",
    "    @rtransform(:dayabbr = dayabbr(:sales_ymd))\n",
    "    @rtransform(:last_monday = firstdayofweek(:sales_ymd))\n",
    "    @rtransform(:elapsed_day_of_week = dayofweek(:sales_ymd) - 1)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "76334fe6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×11 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">customer_id</th><th style = \"text-align: left;\">customer_name</th><th style = \"text-align: left;\">gender_cd</th><th style = \"text-align: left;\">gender</th><th style = \"text-align: left;\">birth_day</th><th style = \"text-align: left;\">age</th><th style = \"text-align: left;\">postal_cd</th><th style = \"text-align: left;\">address</th><th style = \"text-align: left;\">application_store_cd</th><th style = \"text-align: left;\">application_date</th><th style = \"text-align: left;\">status_cd</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"String31\" style = \"text-align: left;\">String31</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"String7\" style = \"text-align: left;\">String7</th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"String\" style = \"text-align: left;\">String</th><th title = \"String7\" style = \"text-align: left;\">String7</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"String15\" style = \"text-align: left;\">String15</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">CS006515000100</td><td style = \"text-align: left;\">天野 明慶</td><td style = \"text-align: right;\">9</td><td style = \"text-align: left;\">不明</td><td style = \"text-align: left;\">1963-08-01</td><td style = \"text-align: right;\">55</td><td style = \"text-align: left;\">224-0065</td><td style = \"text-align: left;\">神奈川県横浜市都筑区高山**********</td><td style = \"text-align: left;\">S14006</td><td style = \"text-align: right;\">20150211</td><td style = \"text-align: left;\">8-20090510-B</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS040513000154</td><td style = \"text-align: left;\">諏訪 朝陽</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">女性</td><td style = \"text-align: left;\">1961-07-11</td><td style = \"text-align: right;\">57</td><td style = \"text-align: left;\">226-0016</td><td style = \"text-align: left;\">神奈川県横浜市緑区霧が丘**********</td><td style = \"text-align: left;\">S14040</td><td style = \"text-align: right;\">20150717</td><td style = \"text-align: left;\">C-20091220-A</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS016202000003</td><td style = \"text-align: left;\">小川 明慶</td><td style = \"text-align: right;\">0</td><td style = \"text-align: left;\">男性</td><td style = \"text-align: left;\">1990-05-01</td><td style = \"text-align: right;\">28</td><td style = \"text-align: left;\">180-0021</td><td style = \"text-align: left;\">東京都武蔵野市桜堤**********</td><td style = \"text-align: left;\">S13016</td><td style = \"text-align: right;\">20150717</td><td style = \"text-align: left;\">0-00000000-0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS013615000073</td><td style = \"text-align: left;\">寺田 由美子</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">女性</td><td style = \"text-align: left;\">1953-05-12</td><td style = \"text-align: right;\">65</td><td style = \"text-align: left;\">261-0026</td><td style = \"text-align: left;\">千葉県千葉市美浜区幕張西**********</td><td style = \"text-align: left;\">S12013</td><td style = \"text-align: right;\">20150714</td><td style = \"text-align: left;\">0-00000000-0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS026414000062</td><td style = \"text-align: left;\">岡本 ひかり</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">女性</td><td style = \"text-align: left;\">1970-09-27</td><td style = \"text-align: right;\">48</td><td style = \"text-align: left;\">251-0046</td><td style = \"text-align: left;\">神奈川県藤沢市辻堂西海岸**********</td><td style = \"text-align: left;\">S14026</td><td style = \"text-align: right;\">20150610</td><td style = \"text-align: left;\">F-20101013-F</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS040413000143</td><td style = \"text-align: left;\">梶原 夏希</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">女性</td><td style = \"text-align: left;\">1973-05-23</td><td style = \"text-align: right;\">45</td><td style = \"text-align: left;\">226-0018</td><td style = \"text-align: left;\">神奈川県横浜市緑区長津田みなみ台**********</td><td style = \"text-align: left;\">S14040</td><td style = \"text-align: right;\">20150708</td><td style = \"text-align: left;\">0-00000000-0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS034515000235</td><td style = \"text-align: left;\">松田 倫子</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">女性</td><td style = \"text-align: left;\">1967-01-23</td><td style = \"text-align: right;\">52</td><td style = \"text-align: left;\">216-0001</td><td style = \"text-align: left;\">神奈川県川崎市宮前区野川**********</td><td style = \"text-align: left;\">S14034</td><td style = \"text-align: right;\">20150924</td><td style = \"text-align: left;\">0-00000000-0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS004315000319</td><td style = \"text-align: left;\">金田 陽子</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">女性</td><td style = \"text-align: left;\">1984-02-23</td><td style = \"text-align: right;\">35</td><td style = \"text-align: left;\">176-0025</td><td style = \"text-align: left;\">東京都練馬区中村南**********</td><td style = \"text-align: left;\">S13004</td><td style = \"text-align: right;\">20160410</td><td style = \"text-align: left;\">2-20090925-1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS004313000062</td><td style = \"text-align: left;\">三輪 美優</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">女性</td><td style = \"text-align: left;\">1985-07-21</td><td style = \"text-align: right;\">33</td><td style = \"text-align: left;\">176-0024</td><td style = \"text-align: left;\">東京都練馬区中村**********</td><td style = \"text-align: left;\">S13004</td><td style = \"text-align: right;\">20151010</td><td style = \"text-align: left;\">0-00000000-0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS020411000065</td><td style = \"text-align: left;\">豊島 恵子</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">女性</td><td style = \"text-align: left;\">1973-12-14</td><td style = \"text-align: right;\">45</td><td style = \"text-align: left;\">174-0043</td><td style = \"text-align: left;\">東京都板橋区坂下**********</td><td style = \"text-align: left;\">S13020</td><td style = \"text-align: right;\">20161105</td><td style = \"text-align: left;\">0-00000000-0</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccccccccccc}\n",
       "\t& customer\\_id & customer\\_name & gender\\_cd & gender & birth\\_day & age & postal\\_cd & address & application\\_store\\_cd & application\\_date & status\\_cd\\\\\n",
       "\t\\hline\n",
       "\t& String15 & String31 & Int64 & String7 & Date & Int64 & String15 & String & String7 & Int64 & String15\\\\\n",
       "\t\\hline\n",
       "\t1 & CS006515000100 & 天野 明慶 & 9 & 不明 & 1963-08-01 & 55 & 224-0065 & 神奈川県横浜市都筑区高山********** & S14006 & 20150211 & 8-20090510-B \\\\\n",
       "\t2 & CS040513000154 & 諏訪 朝陽 & 1 & 女性 & 1961-07-11 & 57 & 226-0016 & 神奈川県横浜市緑区霧が丘********** & S14040 & 20150717 & C-20091220-A \\\\\n",
       "\t3 & CS016202000003 & 小川 明慶 & 0 & 男性 & 1990-05-01 & 28 & 180-0021 & 東京都武蔵野市桜堤********** & S13016 & 20150717 & 0-00000000-0 \\\\\n",
       "\t4 & CS013615000073 & 寺田 由美子 & 1 & 女性 & 1953-05-12 & 65 & 261-0026 & 千葉県千葉市美浜区幕張西********** & S12013 & 20150714 & 0-00000000-0 \\\\\n",
       "\t5 & CS026414000062 & 岡本 ひかり & 1 & 女性 & 1970-09-27 & 48 & 251-0046 & 神奈川県藤沢市辻堂西海岸********** & S14026 & 20150610 & F-20101013-F \\\\\n",
       "\t6 & CS040413000143 & 梶原 夏希 & 1 & 女性 & 1973-05-23 & 45 & 226-0018 & 神奈川県横浜市緑区長津田みなみ台********** & S14040 & 20150708 & 0-00000000-0 \\\\\n",
       "\t7 & CS034515000235 & 松田 倫子 & 1 & 女性 & 1967-01-23 & 52 & 216-0001 & 神奈川県川崎市宮前区野川********** & S14034 & 20150924 & 0-00000000-0 \\\\\n",
       "\t8 & CS004315000319 & 金田 陽子 & 1 & 女性 & 1984-02-23 & 35 & 176-0025 & 東京都練馬区中村南********** & S13004 & 20160410 & 2-20090925-1 \\\\\n",
       "\t9 & CS004313000062 & 三輪 美優 & 1 & 女性 & 1985-07-21 & 33 & 176-0024 & 東京都練馬区中村********** & S13004 & 20151010 & 0-00000000-0 \\\\\n",
       "\t10 & CS020411000065 & 豊島 恵子 & 1 & 女性 & 1973-12-14 & 45 & 174-0043 & 東京都板橋区坂下********** & S13020 & 20161105 & 0-00000000-0 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×11 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m customer_id    \u001b[0m\u001b[1m customer_name \u001b[0m\u001b[1m gender_cd \u001b[0m\u001b[1m gender  \u001b[0m\u001b[1m birth_day  \u001b[0m\u001b[1m age   \u001b[0m\u001b[1m postal_cd \u001b[0m\u001b[1m address                           \u001b[0m\u001b[1m application_store_cd \u001b[0m\u001b[1m application_date \u001b[0m\u001b[1m status_cd    \u001b[0m\n",
       "     │\u001b[90m String15       \u001b[0m\u001b[90m String31      \u001b[0m\u001b[90m Int64     \u001b[0m\u001b[90m String7 \u001b[0m\u001b[90m Date       \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m String15  \u001b[0m\u001b[90m String                            \u001b[0m\u001b[90m String7              \u001b[0m\u001b[90m Int64            \u001b[0m\u001b[90m String15     \u001b[0m\n",
       "─────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────\n",
       "   1 │ CS006515000100  天野 明慶              9  不明     1963-08-01     55  224-0065   神奈川県横浜市都筑区高山********…  S14006                        20150211  8-20090510-B\n",
       "   2 │ CS040513000154  諏訪 朝陽              1  女性     1961-07-11     57  226-0016   神奈川県横浜市緑区霧が丘********…  S14040                        20150717  C-20091220-A\n",
       "   3 │ CS016202000003  小川 明慶              0  男性     1990-05-01     28  180-0021   東京都武蔵野市桜堤**********       S13016                        20150717  0-00000000-0\n",
       "   4 │ CS013615000073  寺田 由美子            1  女性     1953-05-12     65  261-0026   千葉県千葉市美浜区幕張西********…  S12013                        20150714  0-00000000-0\n",
       "   5 │ CS026414000062  岡本 ひかり            1  女性     1970-09-27     48  251-0046   神奈川県藤沢市辻堂西海岸********…  S14026                        20150610  F-20101013-F\n",
       "   6 │ CS040413000143  梶原 夏希              1  女性     1973-05-23     45  226-0018   神奈川県横浜市緑区長津田みなみ台…  S14040                        20150708  0-00000000-0\n",
       "   7 │ CS034515000235  松田 倫子              1  女性     1967-01-23     52  216-0001   神奈川県川崎市宮前区野川********…  S14034                        20150924  0-00000000-0\n",
       "   8 │ CS004315000319  金田 陽子              1  女性     1984-02-23     35  176-0025   東京都練馬区中村南**********       S13004                        20160410  2-20090925-1\n",
       "   9 │ CS004313000062  三輪 美優              1  女性     1985-07-21     33  176-0024   東京都練馬区中村**********         S13004                        20151010  0-00000000-0\n",
       "  10 │ CS020411000065  豊島 恵子              1  女性     1973-12-14     45  174-0043   東京都板橋区坂下**********         S13020                        20161105  0-00000000-0"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-075 ランダムに1%のデータを取得。うち10件を表示\n",
    "using Random\n",
    "count = nrow(df_customers)\n",
    "df_customers[shuffle(1:count)[1:(count ÷ 100)], :] |> \n",
    "    df -> first(df, 10)"
   ]
  }
 ],
 "metadata": {
  "@webio": {
   "lastCommId": null,
   "lastKernelId": null
  },
  "kernelspec": {
   "display_name": "Julia 1.8.0-rc3",
   "language": "julia",
   "name": "julia-1.8"
  },
  "language_info": {
   "file_extension": ".jl",
   "mimetype": "application/julia",
   "name": "julia",
   "version": "1.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}