76-100問.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": "96e831ef",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×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;\">gender_cd</th><th style = \"text-align: left;\">gender_cd_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;\">298</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;\">1791</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">107</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& gender\\_cd & gender\\_cd\\_length\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 0 & 298 \\\\\n",
       "\t2 & 1 & 1791 \\\\\n",
       "\t3 & 9 & 107 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m gender_cd \u001b[0m\u001b[1m gender_cd_length \u001b[0m\n",
       "     │\u001b[90m Int64     \u001b[0m\u001b[90m Int64            \u001b[0m\n",
       "─────┼─────────────────────────────\n",
       "   1 │         0               298\n",
       "   2 │         1              1791\n",
       "   3 │         9               107"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-076 層化抽出(10%)\n",
    "stats = @chain df_customers begin\n",
    "    groupby(:gender_cd)\n",
    "    combine(:gender_cd => length => :count)\n",
    "    @rtransform(:sample_limit = :count ÷ 10)\n",
    "    select(Not(:count))\n",
    "end\n",
    "\n",
    "customers_10pct = @chain df_customers begin\n",
    "    groupby(:gender_cd)\n",
    "    @transform(:row_in_gender = 1:length(:gender_cd))\n",
    "    innerjoin(stats, on= :gender_cd => :gender_cd, makeunique=true)\n",
    "    @rsubset(:row_in_gender ≤ :sample_limit)\n",
    "    sort([:gender_cd, :row_in_gender])\n",
    "    select(Not([:row_in_gender, :sample_limit]))\n",
    "end\n",
    "\n",
    "@chain customers_10pct begin\n",
    "    groupby(:gender_cd)\n",
    "    combine(:gender_cd => length)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6ae59cf9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>1×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;\">amount_sum</th><th style = \"text-align: left;\">amount_sum_log</th><th style = \"text-align: left;\">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><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;\">ZZ000000000000</td><td style = \"text-align: right;\">12395003</td><td style = \"text-align: right;\">16.3328</td><td style = \"text-align: right;\">7.96361</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& customer\\_id & amount\\_sum & amount\\_sum\\_log & std\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64 & Float64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & ZZ000000000000 & 12395003 & 16.3328 & 7.96361 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m1×4 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\u001b[1m std     \u001b[0m\n",
       "     │\u001b[90m String15       \u001b[0m\u001b[90m Int64      \u001b[0m\u001b[90m Float64        \u001b[0m\u001b[90m Float64 \u001b[0m\n",
       "─────┼─────────────────────────────────────────────────────\n",
       "   1 │ ZZ000000000000    12395003         16.3328  7.96361"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-077 外れ値の抽出(対数化した上で平均と標準偏差を計算し、3σを超えたものを抽出)\n",
    "@chain df_receipts begin\n",
    "    groupby(:customer_id)\n",
    "    combine(:amount => sum)\n",
    "    @transform(:amount_sum_log = log.(:amount_sum))\n",
    "    @transform(:std = zscore(:amount_sum_log))\n",
    "    @rsubset(abs(:std) > 3)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ef2622c8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×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;\">customer_id</th><th style = \"text-align: left;\">amount_sum</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></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">CS001414000048</td><td style = \"text-align: right;\">8584</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS001605000009</td><td style = \"text-align: right;\">18925</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS002415000594</td><td style = \"text-align: right;\">9568</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS004414000181</td><td style = \"text-align: right;\">9584</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS005415000137</td><td style = \"text-align: right;\">8734</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS006414000001</td><td style = \"text-align: right;\">9156</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS006414000029</td><td style = \"text-align: right;\">9179</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS006415000105</td><td style = \"text-align: right;\">10042</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS006415000147</td><td style = \"text-align: right;\">12723</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS006415000157</td><td style = \"text-align: right;\">10648</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& customer\\_id & amount\\_sum\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & CS001414000048 & 8584 \\\\\n",
       "\t2 & CS001605000009 & 18925 \\\\\n",
       "\t3 & CS002415000594 & 9568 \\\\\n",
       "\t4 & CS004414000181 & 9584 \\\\\n",
       "\t5 & CS005415000137 & 8734 \\\\\n",
       "\t6 & CS006414000001 & 9156 \\\\\n",
       "\t7 & CS006414000029 & 9179 \\\\\n",
       "\t8 & CS006415000105 & 10042 \\\\\n",
       "\t9 & CS006415000147 & 12723 \\\\\n",
       "\t10 & CS006415000157 & 10648 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m customer_id    \u001b[0m\u001b[1m amount_sum \u001b[0m\n",
       "     │\u001b[90m String15       \u001b[0m\u001b[90m Int64      \u001b[0m\n",
       "─────┼────────────────────────────\n",
       "   1 │ CS001414000048        8584\n",
       "   2 │ CS001605000009       18925\n",
       "   3 │ CS002415000594        9568\n",
       "   4 │ CS004414000181        9584\n",
       "   5 │ CS005415000137        8734\n",
       "   6 │ CS006414000001        9156\n",
       "   7 │ CS006414000029        9179\n",
       "   8 │ CS006415000105       10042\n",
       "   9 │ CS006415000147       12723\n",
       "  10 │ CS006415000157       10648"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-078 外れ値の抽出(第1四分位と第3四分位の差であるIQRを用いて抽出)\n",
    "@chain df_receipts begin\n",
    "    @rsubset(occursin(r\"^[^Z]\", :customer_id))\n",
    "    groupby(:customer_id)\n",
    "    combine(:amount => sum)\n",
    "    @aside quartiles = quantile(_.amount_sum)\n",
    "    @aside IQR = quartiles[4] - quartiles[2]\n",
    "    @rsubset(:amount_sum < (quartiles[2] - 1.5*IQR)\n",
    "        || (quartiles[4] + 1.5*IQR) < :amount_sum)\n",
    "    sort(:customer_id)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "859475f7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>6×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;\">variable</th><th style = \"text-align: left;\">nmissing</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Symbol\" style = \"text-align: left;\">Symbol</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;\">product_cd</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">category_major_cd</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;\">category_medium_cd</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">category_small_cd</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">unit_price</td><td style = \"text-align: right;\">7</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">unit_cost</td><td style = \"text-align: right;\">7</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& variable & nmissing\\\\\n",
       "\t\\hline\n",
       "\t& Symbol & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & product\\_cd & 0 \\\\\n",
       "\t2 & category\\_major\\_cd & 0 \\\\\n",
       "\t3 & category\\_medium\\_cd & 0 \\\\\n",
       "\t4 & category\\_small\\_cd & 0 \\\\\n",
       "\t5 & unit\\_price & 7 \\\\\n",
       "\t6 & unit\\_cost & 7 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m variable           \u001b[0m\u001b[1m nmissing \u001b[0m\n",
       "     │\u001b[90m Symbol             \u001b[0m\u001b[90m Int64    \u001b[0m\n",
       "─────┼──────────────────────────────\n",
       "   1 │ product_cd                 0\n",
       "   2 │ category_major_cd          0\n",
       "   3 │ category_medium_cd         0\n",
       "   4 │ category_small_cd          0\n",
       "   5 │ unit_price                 7\n",
       "   6 │ unit_cost                  7"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-079 欠損値の確認\n",
    "# filter(x -> any(ismissing, x), df_products)\n",
    "describe(df_products, :nmissing)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4d947b3e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "nrow(df_products) = 10030\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10023×6 DataFrame</span></div><div style = \"float: right;\"><span style = \"font-style: italic;\">9998 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;\">product_cd</th><th style = \"text-align: left;\">category_major_cd</th><th style = \"text-align: left;\">category_medium_cd</th><th style = \"text-align: left;\">category_small_cd</th><th style = \"text-align: left;\">unit_price</th><th style = \"text-align: left;\">unit_cost</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 = \"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;\">P040101001</td><td style = \"text-align: right;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">198</td><td style = \"text-align: right;\">149</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">218</td><td style = \"text-align: right;\">164</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">230</td><td style = \"text-align: right;\">173</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">248</td><td style = \"text-align: right;\">186</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">268</td><td style = \"text-align: right;\">201</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">298</td><td style = \"text-align: right;\">224</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">338</td><td style = \"text-align: right;\">254</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">420</td><td style = \"text-align: right;\">315</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">498</td><td style = \"text-align: right;\">374</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">580</td><td style = \"text-align: right;\">435</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">11</td><td style = \"text-align: left;\">P040102001</td><td style = \"text-align: right;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40102</td><td style = \"text-align: right;\">268</td><td style = \"text-align: right;\">201</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">12</td><td style = \"text-align: left;\">P040102002</td><td style = \"text-align: right;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40102</td><td style = \"text-align: right;\">268</td><td style = \"text-align: right;\">201</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">13</td><td style = \"text-align: left;\">P040102003</td><td style = \"text-align: right;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40102</td><td style = \"text-align: right;\">278</td><td style = \"text-align: right;\">209</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><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;\">10012</td><td style = \"text-align: left;\">P091501043</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">915</td><td style = \"text-align: right;\">91501</td><td style = \"text-align: right;\">800</td><td style = \"text-align: right;\">600</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10013</td><td style = \"text-align: left;\">P091501044</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">915</td><td style = \"text-align: right;\">91501</td><td style = \"text-align: right;\">880</td><td style = \"text-align: right;\">660</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10014</td><td style = \"text-align: left;\">P091501045</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">915</td><td style = \"text-align: right;\">91501</td><td style = \"text-align: right;\">980</td><td style = \"text-align: right;\">735</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10015</td><td style = \"text-align: left;\">P091501046</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">915</td><td style = \"text-align: right;\">91501</td><td style = \"text-align: right;\">980</td><td style = \"text-align: right;\">735</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10016</td><td style = \"text-align: left;\">P091501047</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">915</td><td style = \"text-align: right;\">91501</td><td style = \"text-align: right;\">1240</td><td style = \"text-align: right;\">930</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10017</td><td style = \"text-align: left;\">P091501048</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">915</td><td style = \"text-align: right;\">91501</td><td style = \"text-align: right;\">1360</td><td style = \"text-align: right;\">1020</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10018</td><td style = \"text-align: left;\">P091501049</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">915</td><td style = \"text-align: right;\">91501</td><td style = \"text-align: right;\">1380</td><td style = \"text-align: right;\">1035</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10019</td><td style = \"text-align: left;\">P091503001</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">915</td><td style = \"text-align: right;\">91503</td><td style = \"text-align: right;\">280</td><td style = \"text-align: right;\">210</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10020</td><td style = \"text-align: left;\">P091503002</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">915</td><td style = \"text-align: right;\">91503</td><td style = \"text-align: right;\">680</td><td style = \"text-align: right;\">510</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10021</td><td style = \"text-align: left;\">P091503003</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">915</td><td style = \"text-align: right;\">91503</td><td style = \"text-align: right;\">1080</td><td style = \"text-align: right;\">810</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10022</td><td style = \"text-align: left;\">P091503004</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">915</td><td style = \"text-align: right;\">91503</td><td style = \"text-align: right;\">1130</td><td style = \"text-align: right;\">848</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10023</td><td style = \"text-align: left;\">P091503005</td><td style = \"text-align: right;\">9</td><td style = \"text-align: right;\">915</td><td style = \"text-align: right;\">91503</td><td style = \"text-align: right;\">1280</td><td style = \"text-align: right;\">960</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccccc}\n",
       "\t& product\\_cd & category\\_major\\_cd & category\\_medium\\_cd & category\\_small\\_cd & unit\\_price & unit\\_cost\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64 & Int64 & Int64 & Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & P040101001 & 4 & 401 & 40101 & 198 & 149 \\\\\n",
       "\t2 & P040101002 & 4 & 401 & 40101 & 218 & 164 \\\\\n",
       "\t3 & P040101003 & 4 & 401 & 40101 & 230 & 173 \\\\\n",
       "\t4 & P040101004 & 4 & 401 & 40101 & 248 & 186 \\\\\n",
       "\t5 & P040101005 & 4 & 401 & 40101 & 268 & 201 \\\\\n",
       "\t6 & P040101006 & 4 & 401 & 40101 & 298 & 224 \\\\\n",
       "\t7 & P040101007 & 4 & 401 & 40101 & 338 & 254 \\\\\n",
       "\t8 & P040101008 & 4 & 401 & 40101 & 420 & 315 \\\\\n",
       "\t9 & P040101009 & 4 & 401 & 40101 & 498 & 374 \\\\\n",
       "\t10 & P040101010 & 4 & 401 & 40101 & 580 & 435 \\\\\n",
       "\t11 & P040102001 & 4 & 401 & 40102 & 268 & 201 \\\\\n",
       "\t12 & P040102002 & 4 & 401 & 40102 & 268 & 201 \\\\\n",
       "\t13 & P040102003 & 4 & 401 & 40102 & 278 & 209 \\\\\n",
       "\t14 & P040102004 & 4 & 401 & 40102 & 318 & 239 \\\\\n",
       "\t15 & P040102005 & 4 & 401 & 40102 & 318 & 239 \\\\\n",
       "\t16 & P040102006 & 4 & 401 & 40102 & 318 & 239 \\\\\n",
       "\t17 & P040102007 & 4 & 401 & 40102 & 338 & 254 \\\\\n",
       "\t18 & P040102008 & 4 & 401 & 40102 & 358 & 269 \\\\\n",
       "\t19 & P040102009 & 4 & 401 & 40102 & 358 & 269 \\\\\n",
       "\t20 & P040102010 & 4 & 401 & 40102 & 358 & 269 \\\\\n",
       "\t21 & P040102011 & 4 & 401 & 40102 & 358 & 269 \\\\\n",
       "\t22 & P040102012 & 4 & 401 & 40102 & 358 & 269 \\\\\n",
       "\t23 & P040102013 & 4 & 401 & 40102 & 398 & 299 \\\\\n",
       "\t24 & P040102014 & 4 & 401 & 40102 & 398 & 299 \\\\\n",
       "\t25 & P040102015 & 4 & 401 & 40102 & 398 & 299 \\\\\n",
       "\t26 & P040102016 & 4 & 401 & 40102 & 398 & 299 \\\\\n",
       "\t27 & P040102017 & 4 & 401 & 40102 & 438 & 329 \\\\\n",
       "\t28 & P040102018 & 4 & 401 & 40102 & 498 & 374 \\\\\n",
       "\t29 & P040102019 & 4 & 401 & 40102 & 598 & 449 \\\\\n",
       "\t30 & P040102020 & 4 & 401 & 40102 & 980 & 735 \\\\\n",
       "\t$\\dots$ & $\\dots$ & $\\dots$ & $\\dots$ & $\\dots$ & $\\dots$ & $\\dots$ \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10023×6 DataFrame\u001b[0m\n",
       "\u001b[1m   Row \u001b[0m│\u001b[1m product_cd \u001b[0m\u001b[1m category_major_cd \u001b[0m\u001b[1m category_medium_cd \u001b[0m\u001b[1m category_small_cd \u001b[0m\u001b[1m unit_price \u001b[0m\u001b[1m unit_cost \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 Int64      \u001b[0m\u001b[90m Int64     \u001b[0m\n",
       "───────┼─────────────────────────────────────────────────────────────────────────────────────────────\n",
       "     1 │ P040101001                  4                 401              40101         198        149\n",
       "     2 │ P040101002                  4                 401              40101         218        164\n",
       "     3 │ P040101003                  4                 401              40101         230        173\n",
       "     4 │ P040101004                  4                 401              40101         248        186\n",
       "     5 │ P040101005                  4                 401              40101         268        201\n",
       "     6 │ P040101006                  4                 401              40101         298        224\n",
       "     7 │ P040101007                  4                 401              40101         338        254\n",
       "     8 │ P040101008                  4                 401              40101         420        315\n",
       "     9 │ P040101009                  4                 401              40101         498        374\n",
       "    10 │ P040101010                  4                 401              40101         580        435\n",
       "    11 │ P040102001                  4                 401              40102         268        201\n",
       "   ⋮   │     ⋮               ⋮                  ⋮                   ⋮              ⋮           ⋮\n",
       " 10014 │ P091501045                  9                 915              91501         980        735\n",
       " 10015 │ P091501046                  9                 915              91501         980        735\n",
       " 10016 │ P091501047                  9                 915              91501        1240        930\n",
       " 10017 │ P091501048                  9                 915              91501        1360       1020\n",
       " 10018 │ P091501049                  9                 915              91501        1380       1035\n",
       " 10019 │ P091503001                  9                 915              91503         280        210\n",
       " 10020 │ P091503002                  9                 915              91503         680        510\n",
       " 10021 │ P091503003                  9                 915              91503        1080        810\n",
       " 10022 │ P091503004                  9                 915              91503        1130        848\n",
       " 10023 │ P091503005                  9                 915              91503        1280        960\n",
       "\u001b[36m                                                                                   10002 rows omitted\u001b[0m"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-080 欠損値を除外\n",
    "@show nrow(df_products)\n",
    "dropmissing(df_products)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9250fe67",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>6×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;\">variable</th><th style = \"text-align: left;\">nmissing</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Symbol\" style = \"text-align: left;\">Symbol</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;\">product_cd</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">category_major_cd</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;\">category_medium_cd</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">category_small_cd</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">unit_price</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">unit_cost</td><td style = \"text-align: right;\">0</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& variable & nmissing\\\\\n",
       "\t\\hline\n",
       "\t& Symbol & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & product\\_cd & 0 \\\\\n",
       "\t2 & category\\_major\\_cd & 0 \\\\\n",
       "\t3 & category\\_medium\\_cd & 0 \\\\\n",
       "\t4 & category\\_small\\_cd & 0 \\\\\n",
       "\t5 & unit\\_price & 0 \\\\\n",
       "\t6 & unit\\_cost & 0 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m variable           \u001b[0m\u001b[1m nmissing \u001b[0m\n",
       "     │\u001b[90m Symbol             \u001b[0m\u001b[90m Int64    \u001b[0m\n",
       "─────┼──────────────────────────────\n",
       "   1 │ product_cd                 0\n",
       "   2 │ category_major_cd          0\n",
       "   3 │ category_medium_cd         0\n",
       "   4 │ category_small_cd          0\n",
       "   5 │ unit_price                 0\n",
       "   6 │ unit_cost                  0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×6 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;\">category_major_cd</th><th style = \"text-align: left;\">category_medium_cd</th><th style = \"text-align: left;\">category_small_cd</th><th style = \"text-align: left;\">unit_price</th><th style = \"text-align: left;\">unit_cost</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 = \"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;\">P040101001</td><td style = \"text-align: right;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">198</td><td style = \"text-align: right;\">149</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">218</td><td style = \"text-align: right;\">164</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">230</td><td style = \"text-align: right;\">173</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">248</td><td style = \"text-align: right;\">186</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">268</td><td style = \"text-align: right;\">201</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">298</td><td style = \"text-align: right;\">224</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">338</td><td style = \"text-align: right;\">254</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">420</td><td style = \"text-align: right;\">315</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">498</td><td style = \"text-align: right;\">374</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">580</td><td style = \"text-align: right;\">435</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccccc}\n",
       "\t& product\\_cd & category\\_major\\_cd & category\\_medium\\_cd & category\\_small\\_cd & unit\\_price & unit\\_cost\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64 & Int64 & Int64 & Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & P040101001 & 4 & 401 & 40101 & 198 & 149 \\\\\n",
       "\t2 & P040101002 & 4 & 401 & 40101 & 218 & 164 \\\\\n",
       "\t3 & P040101003 & 4 & 401 & 40101 & 230 & 173 \\\\\n",
       "\t4 & P040101004 & 4 & 401 & 40101 & 248 & 186 \\\\\n",
       "\t5 & P040101005 & 4 & 401 & 40101 & 268 & 201 \\\\\n",
       "\t6 & P040101006 & 4 & 401 & 40101 & 298 & 224 \\\\\n",
       "\t7 & P040101007 & 4 & 401 & 40101 & 338 & 254 \\\\\n",
       "\t8 & P040101008 & 4 & 401 & 40101 & 420 & 315 \\\\\n",
       "\t9 & P040101009 & 4 & 401 & 40101 & 498 & 374 \\\\\n",
       "\t10 & P040101010 & 4 & 401 & 40101 & 580 & 435 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×6 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m product_cd \u001b[0m\u001b[1m category_major_cd \u001b[0m\u001b[1m category_medium_cd \u001b[0m\u001b[1m category_small_cd \u001b[0m\u001b[1m unit_price \u001b[0m\u001b[1m unit_cost \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 Int64      \u001b[0m\u001b[90m Int64     \u001b[0m\n",
       "─────┼─────────────────────────────────────────────────────────────────────────────────────────────\n",
       "   1 │ P040101001                  4                 401              40101         198        149\n",
       "   2 │ P040101002                  4                 401              40101         218        164\n",
       "   3 │ P040101003                  4                 401              40101         230        173\n",
       "   4 │ P040101004                  4                 401              40101         248        186\n",
       "   5 │ P040101005                  4                 401              40101         268        201\n",
       "   6 │ P040101006                  4                 401              40101         298        224\n",
       "   7 │ P040101007                  4                 401              40101         338        254\n",
       "   8 │ P040101008                  4                 401              40101         420        315\n",
       "   9 │ P040101009                  4                 401              40101         498        374\n",
       "  10 │ P040101010                  4                 401              40101         580        435"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-081 欠損値を平均値で補完\n",
    "@chain df_products begin\n",
    "    @aside mean_price = round(Int, mean(skipmissing(_.unit_price)))\n",
    "    @aside mean_cost = round(Int, mean(skipmissing(_.unit_cost)))\n",
    "    @rtransform(\n",
    "        :unit_price = coalesce(:unit_price, mean_price),\n",
    "        :unit_cost  = coalesce(:unit_cost, mean_cost),\n",
    "    )\n",
    "    @aside describe(_, :nmissing) |> display\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0cc92036",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>6×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;\">variable</th><th style = \"text-align: left;\">nmissing</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Symbol\" style = \"text-align: left;\">Symbol</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;\">product_cd</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">category_major_cd</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;\">category_medium_cd</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">category_small_cd</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">unit_price</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">unit_cost</td><td style = \"text-align: right;\">0</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& variable & nmissing\\\\\n",
       "\t\\hline\n",
       "\t& Symbol & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & product\\_cd & 0 \\\\\n",
       "\t2 & category\\_major\\_cd & 0 \\\\\n",
       "\t3 & category\\_medium\\_cd & 0 \\\\\n",
       "\t4 & category\\_small\\_cd & 0 \\\\\n",
       "\t5 & unit\\_price & 0 \\\\\n",
       "\t6 & unit\\_cost & 0 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m variable           \u001b[0m\u001b[1m nmissing \u001b[0m\n",
       "     │\u001b[90m Symbol             \u001b[0m\u001b[90m Int64    \u001b[0m\n",
       "─────┼──────────────────────────────\n",
       "   1 │ product_cd                 0\n",
       "   2 │ category_major_cd          0\n",
       "   3 │ category_medium_cd         0\n",
       "   4 │ category_small_cd          0\n",
       "   5 │ unit_price                 0\n",
       "   6 │ unit_cost                  0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×6 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;\">category_major_cd</th><th style = \"text-align: left;\">category_medium_cd</th><th style = \"text-align: left;\">category_small_cd</th><th style = \"text-align: left;\">unit_price</th><th style = \"text-align: left;\">unit_cost</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 = \"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;\">P040101001</td><td style = \"text-align: right;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">198</td><td style = \"text-align: right;\">149</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">218</td><td style = \"text-align: right;\">164</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">230</td><td style = \"text-align: right;\">173</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">248</td><td style = \"text-align: right;\">186</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">268</td><td style = \"text-align: right;\">201</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">298</td><td style = \"text-align: right;\">224</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">338</td><td style = \"text-align: right;\">254</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">420</td><td style = \"text-align: right;\">315</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">498</td><td style = \"text-align: right;\">374</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">580</td><td style = \"text-align: right;\">435</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccccc}\n",
       "\t& product\\_cd & category\\_major\\_cd & category\\_medium\\_cd & category\\_small\\_cd & unit\\_price & unit\\_cost\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64 & Int64 & Int64 & Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & P040101001 & 4 & 401 & 40101 & 198 & 149 \\\\\n",
       "\t2 & P040101002 & 4 & 401 & 40101 & 218 & 164 \\\\\n",
       "\t3 & P040101003 & 4 & 401 & 40101 & 230 & 173 \\\\\n",
       "\t4 & P040101004 & 4 & 401 & 40101 & 248 & 186 \\\\\n",
       "\t5 & P040101005 & 4 & 401 & 40101 & 268 & 201 \\\\\n",
       "\t6 & P040101006 & 4 & 401 & 40101 & 298 & 224 \\\\\n",
       "\t7 & P040101007 & 4 & 401 & 40101 & 338 & 254 \\\\\n",
       "\t8 & P040101008 & 4 & 401 & 40101 & 420 & 315 \\\\\n",
       "\t9 & P040101009 & 4 & 401 & 40101 & 498 & 374 \\\\\n",
       "\t10 & P040101010 & 4 & 401 & 40101 & 580 & 435 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×6 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m product_cd \u001b[0m\u001b[1m category_major_cd \u001b[0m\u001b[1m category_medium_cd \u001b[0m\u001b[1m category_small_cd \u001b[0m\u001b[1m unit_price \u001b[0m\u001b[1m unit_cost \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 Int64      \u001b[0m\u001b[90m Int64     \u001b[0m\n",
       "─────┼─────────────────────────────────────────────────────────────────────────────────────────────\n",
       "   1 │ P040101001                  4                 401              40101         198        149\n",
       "   2 │ P040101002                  4                 401              40101         218        164\n",
       "   3 │ P040101003                  4                 401              40101         230        173\n",
       "   4 │ P040101004                  4                 401              40101         248        186\n",
       "   5 │ P040101005                  4                 401              40101         268        201\n",
       "   6 │ P040101006                  4                 401              40101         298        224\n",
       "   7 │ P040101007                  4                 401              40101         338        254\n",
       "   8 │ P040101008                  4                 401              40101         420        315\n",
       "   9 │ P040101009                  4                 401              40101         498        374\n",
       "  10 │ P040101010                  4                 401              40101         580        435"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-082 欠損値を中央値で補完\n",
    "@chain df_products begin\n",
    "    @aside median_price = round(Int, median(_.unit_price |> skipmissing))\n",
    "    @aside median_cost = round(Int, median(_.unit_cost |> skipmissing))\n",
    "    @rtransform(\n",
    "        :unit_price = coalesce(:unit_price, median_price),\n",
    "        :unit_cost = coalesce(:unit_cost, median_cost),\n",
    "    )\n",
    "    @aside display(describe(_, :nmissing))\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "803586cc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>6×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;\">variable</th><th style = \"text-align: left;\">nmissing</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Symbol\" style = \"text-align: left;\">Symbol</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;\">product_cd</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">category_major_cd</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;\">category_medium_cd</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">category_small_cd</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">unit_price</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">unit_cost</td><td style = \"text-align: right;\">0</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& variable & nmissing\\\\\n",
       "\t\\hline\n",
       "\t& Symbol & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & product\\_cd & 0 \\\\\n",
       "\t2 & category\\_major\\_cd & 0 \\\\\n",
       "\t3 & category\\_medium\\_cd & 0 \\\\\n",
       "\t4 & category\\_small\\_cd & 0 \\\\\n",
       "\t5 & unit\\_price & 0 \\\\\n",
       "\t6 & unit\\_cost & 0 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m variable           \u001b[0m\u001b[1m nmissing \u001b[0m\n",
       "     │\u001b[90m Symbol             \u001b[0m\u001b[90m Int64    \u001b[0m\n",
       "─────┼──────────────────────────────\n",
       "   1 │ product_cd                 0\n",
       "   2 │ category_major_cd          0\n",
       "   3 │ category_medium_cd         0\n",
       "   4 │ category_small_cd          0\n",
       "   5 │ unit_price                 0\n",
       "   6 │ unit_cost                  0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×6 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;\">category_major_cd</th><th style = \"text-align: left;\">category_medium_cd</th><th style = \"text-align: left;\">category_small_cd</th><th style = \"text-align: left;\">unit_price</th><th style = \"text-align: left;\">unit_cost</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 = \"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;\">P040101001</td><td style = \"text-align: right;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">198</td><td style = \"text-align: right;\">149</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">218</td><td style = \"text-align: right;\">164</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">230</td><td style = \"text-align: right;\">173</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">248</td><td style = \"text-align: right;\">186</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">268</td><td style = \"text-align: right;\">201</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">298</td><td style = \"text-align: right;\">224</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">338</td><td style = \"text-align: right;\">254</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">420</td><td style = \"text-align: right;\">315</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">498</td><td style = \"text-align: right;\">374</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">580</td><td style = \"text-align: right;\">435</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccccc}\n",
       "\t& product\\_cd & category\\_major\\_cd & category\\_medium\\_cd & category\\_small\\_cd & unit\\_price & unit\\_cost\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64 & Int64 & Int64 & Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & P040101001 & 4 & 401 & 40101 & 198 & 149 \\\\\n",
       "\t2 & P040101002 & 4 & 401 & 40101 & 218 & 164 \\\\\n",
       "\t3 & P040101003 & 4 & 401 & 40101 & 230 & 173 \\\\\n",
       "\t4 & P040101004 & 4 & 401 & 40101 & 248 & 186 \\\\\n",
       "\t5 & P040101005 & 4 & 401 & 40101 & 268 & 201 \\\\\n",
       "\t6 & P040101006 & 4 & 401 & 40101 & 298 & 224 \\\\\n",
       "\t7 & P040101007 & 4 & 401 & 40101 & 338 & 254 \\\\\n",
       "\t8 & P040101008 & 4 & 401 & 40101 & 420 & 315 \\\\\n",
       "\t9 & P040101009 & 4 & 401 & 40101 & 498 & 374 \\\\\n",
       "\t10 & P040101010 & 4 & 401 & 40101 & 580 & 435 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×6 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m product_cd \u001b[0m\u001b[1m category_major_cd \u001b[0m\u001b[1m category_medium_cd \u001b[0m\u001b[1m category_small_cd \u001b[0m\u001b[1m unit_price \u001b[0m\u001b[1m unit_cost \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 Int64      \u001b[0m\u001b[90m Int64     \u001b[0m\n",
       "─────┼─────────────────────────────────────────────────────────────────────────────────────────────\n",
       "   1 │ P040101001                  4                 401              40101         198        149\n",
       "   2 │ P040101002                  4                 401              40101         218        164\n",
       "   3 │ P040101003                  4                 401              40101         230        173\n",
       "   4 │ P040101004                  4                 401              40101         248        186\n",
       "   5 │ P040101005                  4                 401              40101         268        201\n",
       "   6 │ P040101006                  4                 401              40101         298        224\n",
       "   7 │ P040101007                  4                 401              40101         338        254\n",
       "   8 │ P040101008                  4                 401              40101         420        315\n",
       "   9 │ P040101009                  4                 401              40101         498        374\n",
       "  10 │ P040101010                  4                 401              40101         580        435"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-083 欠損値を各商品のカテゴリ小区分コードごとの中央値で補完\n",
    "stats = @chain df_products begin\n",
    "    dropmissing\n",
    "    groupby(:category_small_cd)\n",
    "    combine(:unit_price => median, :unit_cost => median)\n",
    "    @rtransform(\n",
    "        :unit_price_median = round(Int, :unit_price_median),\n",
    "        :unit_cost_median = round(Int, :unit_cost_median),\n",
    "    )\n",
    "end\n",
    "\n",
    "@chain df_products begin\n",
    "    innerjoin(stats, on= :category_small_cd)\n",
    "    @rtransform(\n",
    "        :unit_price = coalesce(:unit_price, :unit_price_median),\n",
    "        :unit_cost = coalesce(:unit_cost, :unit_cost_median),\n",
    "    )\n",
    "    select(Not([:unit_price_median, :unit_cost_median]))\n",
    "    @aside describe(_, :nmissing) |> display\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a61a2bb5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>4×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;\">variable</th><th style = \"text-align: left;\">nmissing</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Symbol\" style = \"text-align: left;\">Symbol</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;\">customer_id</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">amount_all</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;\">amount_2019</td><td style = \"text-align: right;\">0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">amount_2019_ratio</td><td style = \"text-align: right;\">0</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& variable & nmissing\\\\\n",
       "\t\\hline\n",
       "\t& Symbol & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & customer\\_id & 0 \\\\\n",
       "\t2 & amount\\_all & 0 \\\\\n",
       "\t3 & amount\\_2019 & 0 \\\\\n",
       "\t4 & amount\\_2019\\_ratio & 0 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m4×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m variable          \u001b[0m\u001b[1m nmissing \u001b[0m\n",
       "     │\u001b[90m Symbol            \u001b[0m\u001b[90m Int64    \u001b[0m\n",
       "─────┼─────────────────────────────\n",
       "   1 │ customer_id               0\n",
       "   2 │ amount_all                0\n",
       "   3 │ amount_2019               0\n",
       "   4 │ amount_2019_ratio         0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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;\">amount_all</th><th style = \"text-align: left;\">amount_2019</th><th style = \"text-align: left;\">amount_2019_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 = \"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;\">CS031415000172</td><td style = \"text-align: right;\">5088</td><td style = \"text-align: right;\">2971</td><td style = \"text-align: right;\">0.583923</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS001215000145</td><td style = \"text-align: right;\">875</td><td style = \"text-align: right;\">0</td><td style = \"text-align: right;\">0.0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS015414000103</td><td style = \"text-align: right;\">3122</td><td style = \"text-align: right;\">874</td><td style = \"text-align: right;\">0.279949</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS033513000180</td><td style = \"text-align: right;\">868</td><td style = \"text-align: right;\">0</td><td style = \"text-align: right;\">0.0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS011215000048</td><td style = \"text-align: right;\">3444</td><td style = \"text-align: right;\">248</td><td style = \"text-align: right;\">0.0720093</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS040412000191</td><td style = \"text-align: right;\">210</td><td style = \"text-align: right;\">0</td><td style = \"text-align: right;\">0.0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS029415000023</td><td style = \"text-align: right;\">5167</td><td style = \"text-align: right;\">3767</td><td style = \"text-align: right;\">0.72905</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS009315000023</td><td style = \"text-align: right;\">780</td><td style = \"text-align: right;\">0</td><td style = \"text-align: right;\">0.0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS035415000029</td><td style = \"text-align: right;\">7504</td><td style = \"text-align: right;\">5823</td><td style = \"text-align: right;\">0.775986</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS015315000033</td><td style = \"text-align: right;\">576</td><td style = \"text-align: right;\">0</td><td style = \"text-align: right;\">0.0</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& customer\\_id & amount\\_all & amount\\_2019 & amount\\_2019\\_ratio\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64 & Int64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & CS031415000172 & 5088 & 2971 & 0.583923 \\\\\n",
       "\t2 & CS001215000145 & 875 & 0 & 0.0 \\\\\n",
       "\t3 & CS015414000103 & 3122 & 874 & 0.279949 \\\\\n",
       "\t4 & CS033513000180 & 868 & 0 & 0.0 \\\\\n",
       "\t5 & CS011215000048 & 3444 & 248 & 0.0720093 \\\\\n",
       "\t6 & CS040412000191 & 210 & 0 & 0.0 \\\\\n",
       "\t7 & CS029415000023 & 5167 & 3767 & 0.72905 \\\\\n",
       "\t8 & CS009315000023 & 780 & 0 & 0.0 \\\\\n",
       "\t9 & CS035415000029 & 7504 & 5823 & 0.775986 \\\\\n",
       "\t10 & CS015315000033 & 576 & 0 & 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 amount_all \u001b[0m\u001b[1m amount_2019 \u001b[0m\u001b[1m amount_2019_ratio \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 │ CS031415000172        5088         2971          0.583923\n",
       "   2 │ CS001215000145         875            0          0.0\n",
       "   3 │ CS015414000103        3122          874          0.279949\n",
       "   4 │ CS033513000180         868            0          0.0\n",
       "   5 │ CS011215000048        3444          248          0.0720093\n",
       "   6 │ CS040412000191         210            0          0.0\n",
       "   7 │ CS029415000023        5167         3767          0.72905\n",
       "   8 │ CS009315000023         780            0          0.0\n",
       "   9 │ CS035415000029        7504         5823          0.775986\n",
       "  10 │ CS015315000033         576            0          0.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-084 各顧客の全期間の売上金額に占める2019年の割合\n",
    "# 2019年とそれ以外を横持ちして集計\n",
    "stats = @chain df_receipts begin\n",
    "    @rtransform(:sales_year = parse(Int, string(:sales_ymd)[1:4]))\n",
    "    @rtransform(:is_2019 = :sales_year == 2019)\n",
    "    groupby([:customer_id, :is_2019])\n",
    "    combine(:amount => sum)\n",
    "    @rtransform(:is_2019 = :is_2019 ? \"amount_2019\" : \"amount_else\")\n",
    "    unstack(:customer_id, :is_2019, :amount_sum)\n",
    "end\n",
    "\n",
    "@chain df_customers begin\n",
    "    leftjoin(stats, on= :customer_id => :customer_id)\n",
    "    @rtransform(\n",
    "        :amount_2019 = coalesce(:amount_2019, 0),\n",
    "        :amount_else = coalesce(:amount_else, 0),\n",
    "    )\n",
    "    @rtransform(:amount_all = :amount_2019 + :amount_else)\n",
    "    @rtransform(:amount_2019_ratio = :amount_2019 / :amount_all)\n",
    "    select(:customer_id, :amount_all, :amount_2019, :amount_2019_ratio)\n",
    "    @aside describe(_, :nmissing) |> display\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "b4c1eefa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×13 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><th style = \"text-align: left;\">latitude</th><th style = \"text-align: left;\">longitude</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 = \"Dates.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><th title = \"Float64\" style = \"text-align: left;\">Float64</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;\">CS020301000012</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;\">1985-08-20</td><td style = \"text-align: right;\">33</td><td style = \"text-align: left;\">332-0031</td><td style = \"text-align: left;\">埼玉県川口市青木**********</td><td style = \"text-align: left;\">S13020</td><td style = \"text-align: right;\">20161018</td><td style = \"text-align: left;\">0-00000000-0</td><td style = \"text-align: right;\">35.8088</td><td style = \"text-align: right;\">139.727</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS051412000011</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;\">1969-06-04</td><td style = \"text-align: right;\">49</td><td style = \"text-align: left;\">332-0023</td><td style = \"text-align: left;\">埼玉県川口市飯塚**********</td><td style = \"text-align: left;\">S13051</td><td style = \"text-align: right;\">20180702</td><td style = \"text-align: left;\">0-00000000-0</td><td style = \"text-align: right;\">35.7965</td><td style = \"text-align: right;\">139.711</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS051412000012</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;\">1977-03-13</td><td style = \"text-align: right;\">42</td><td style = \"text-align: left;\">332-0023</td><td style = \"text-align: left;\">埼玉県川口市飯塚**********</td><td style = \"text-align: left;\">S13051</td><td style = \"text-align: right;\">20180511</td><td style = \"text-align: left;\">0-00000000-0</td><td style = \"text-align: right;\">35.7965</td><td style = \"text-align: right;\">139.711</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS051212000001</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;\">1991-10-19</td><td style = \"text-align: right;\">27</td><td style = \"text-align: left;\">332-0023</td><td style = \"text-align: left;\">埼玉県川口市飯塚**********</td><td style = \"text-align: left;\">S13051</td><td style = \"text-align: right;\">20180509</td><td style = \"text-align: left;\">2-20101018-4</td><td style = \"text-align: right;\">35.7965</td><td style = \"text-align: right;\">139.711</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS020112000003</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;\">2004-05-13</td><td style = \"text-align: right;\">14</td><td style = \"text-align: left;\">332-0015</td><td style = \"text-align: left;\">埼玉県川口市川口**********</td><td style = \"text-align: left;\">S13020</td><td style = \"text-align: right;\">20151116</td><td style = \"text-align: left;\">0-00000000-0</td><td style = \"text-align: right;\">35.8023</td><td style = \"text-align: right;\">139.716</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS020212000016</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;\">1991-08-08</td><td style = \"text-align: right;\">27</td><td style = \"text-align: left;\">332-0015</td><td style = \"text-align: left;\">埼玉県川口市川口**********</td><td style = \"text-align: left;\">S13020</td><td style = \"text-align: right;\">20150122</td><td style = \"text-align: left;\">4-20100207-3</td><td style = \"text-align: right;\">35.8023</td><td style = \"text-align: right;\">139.716</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS051512000001</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;\">1959-11-21</td><td style = \"text-align: right;\">59</td><td style = \"text-align: left;\">332-0015</td><td style = \"text-align: left;\">埼玉県川口市川口**********</td><td style = \"text-align: left;\">S13051</td><td style = \"text-align: right;\">20190325</td><td style = \"text-align: left;\">0-00000000-0</td><td style = \"text-align: right;\">35.8023</td><td style = \"text-align: right;\">139.716</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS020212000004</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;\">1989-12-11</td><td style = \"text-align: right;\">29</td><td style = \"text-align: left;\">332-0015</td><td style = \"text-align: left;\">埼玉県川口市川口**********</td><td style = \"text-align: left;\">S13020</td><td style = \"text-align: right;\">20150814</td><td style = \"text-align: left;\">C-20090906-9</td><td style = \"text-align: right;\">35.8023</td><td style = \"text-align: right;\">139.716</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS051502000001</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;\">1961-10-07</td><td style = \"text-align: right;\">57</td><td style = \"text-align: left;\">332-0015</td><td style = \"text-align: left;\">埼玉県川口市川口**********</td><td style = \"text-align: left;\">S13051</td><td style = \"text-align: right;\">20180209</td><td style = \"text-align: left;\">0-00000000-0</td><td style = \"text-align: right;\">35.8023</td><td style = \"text-align: right;\">139.716</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS020212000008</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;\">1997-01-05</td><td style = \"text-align: right;\">22</td><td style = \"text-align: left;\">332-0015</td><td style = \"text-align: left;\">埼玉県川口市川口**********</td><td style = \"text-align: left;\">S13020</td><td style = \"text-align: right;\">20150105</td><td style = \"text-align: left;\">0-00000000-0</td><td style = \"text-align: right;\">35.8023</td><td style = \"text-align: right;\">139.716</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccccccccccccc}\n",
       "\t& customer\\_id & customer\\_name & gender\\_cd & gender & birth\\_day & age & postal\\_cd & address & application\\_store\\_cd & application\\_date & status\\_cd & latitude & longitude\\\\\n",
       "\t\\hline\n",
       "\t& String15 & String31 & Int64 & String7 & Date & Int64 & String15 & String & String7 & Int64 & String15 & Float64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & CS020301000012 & 都築 育二 & 0 & 男性 & 1985-08-20 & 33 & 332-0031 & 埼玉県川口市青木********** & S13020 & 20161018 & 0-00000000-0 & 35.8088 & 139.727 \\\\\n",
       "\t2 & CS051412000011 & 奥村 愛 & 1 & 女性 & 1969-06-04 & 49 & 332-0023 & 埼玉県川口市飯塚********** & S13051 & 20180702 & 0-00000000-0 & 35.7965 & 139.711 \\\\\n",
       "\t3 & CS051412000012 & おかやま 未華子 & 1 & 女性 & 1977-03-13 & 42 & 332-0023 & 埼玉県川口市飯塚********** & S13051 & 20180511 & 0-00000000-0 & 35.7965 & 139.711 \\\\\n",
       "\t4 & CS051212000001 & 美木 瞬 & 1 & 女性 & 1991-10-19 & 27 & 332-0023 & 埼玉県川口市飯塚********** & S13051 & 20180509 & 2-20101018-4 & 35.7965 & 139.711 \\\\\n",
       "\t5 & CS020112000003 & 荒川 まなみ & 1 & 女性 & 2004-05-13 & 14 & 332-0015 & 埼玉県川口市川口********** & S13020 & 20151116 & 0-00000000-0 & 35.8023 & 139.716 \\\\\n",
       "\t6 & CS020212000016 & 藤沢 恵梨香 & 1 & 女性 & 1991-08-08 & 27 & 332-0015 & 埼玉県川口市川口********** & S13020 & 20150122 & 4-20100207-3 & 35.8023 & 139.716 \\\\\n",
       "\t7 & CS051512000001 & 田原 夏希 & 1 & 女性 & 1959-11-21 & 59 & 332-0015 & 埼玉県川口市川口********** & S13051 & 20190325 & 0-00000000-0 & 35.8023 & 139.716 \\\\\n",
       "\t8 & CS020212000004 & 前田 美佐 & 1 & 女性 & 1989-12-11 & 29 & 332-0015 & 埼玉県川口市川口********** & S13020 & 20150814 & C-20090906-9 & 35.8023 & 139.716 \\\\\n",
       "\t9 & CS051502000001 & 若山 哲平 & 0 & 男性 & 1961-10-07 & 57 & 332-0015 & 埼玉県川口市川口********** & S13051 & 20180209 & 0-00000000-0 & 35.8023 & 139.716 \\\\\n",
       "\t10 & CS020212000008 & 板垣 瞳 & 1 & 女性 & 1997-01-05 & 22 & 332-0015 & 埼玉県川口市川口********** & S13020 & 20150105 & 0-00000000-0 & 35.8023 & 139.716 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×13 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\u001b[1m latitude \u001b[0m\u001b[1m longitude \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\u001b[90m Float64  \u001b[0m\u001b[90m Float64   \u001b[0m\n",
       "─────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────\n",
       "   1 │ CS020301000012  都築 育二                0  男性     1985-08-20     33  332-0031   埼玉県川口市青木**********  S13020                        20161018  0-00000000-0   35.8088    139.727\n",
       "   2 │ CS051412000011  奥村 愛                  1  女性     1969-06-04     49  332-0023   埼玉県川口市飯塚**********  S13051                        20180702  0-00000000-0   35.7965    139.711\n",
       "   3 │ CS051412000012  おかやま 未華子          1  女性     1977-03-13     42  332-0023   埼玉県川口市飯塚**********  S13051                        20180511  0-00000000-0   35.7965    139.711\n",
       "   4 │ CS051212000001  美木 瞬                  1  女性     1991-10-19     27  332-0023   埼玉県川口市飯塚**********  S13051                        20180509  2-20101018-4   35.7965    139.711\n",
       "   5 │ CS020112000003  荒川 まなみ              1  女性     2004-05-13     14  332-0015   埼玉県川口市川口**********  S13020                        20151116  0-00000000-0   35.8023    139.716\n",
       "   6 │ CS020212000016  藤沢 恵梨香              1  女性     1991-08-08     27  332-0015   埼玉県川口市川口**********  S13020                        20150122  4-20100207-3   35.8023    139.716\n",
       "   7 │ CS051512000001  田原 夏希                1  女性     1959-11-21     59  332-0015   埼玉県川口市川口**********  S13051                        20190325  0-00000000-0   35.8023    139.716\n",
       "   8 │ CS020212000004  前田 美佐                1  女性     1989-12-11     29  332-0015   埼玉県川口市川口**********  S13020                        20150814  C-20090906-9   35.8023    139.716\n",
       "   9 │ CS051502000001  若山 哲平                0  男性     1961-10-07     57  332-0015   埼玉県川口市川口**********  S13051                        20180209  0-00000000-0   35.8023    139.716\n",
       "  10 │ CS020212000008  板垣 瞳                  1  女性     1997-01-05     22  332-0015   埼玉県川口市川口**********  S13020                        20150105  0-00000000-0   35.8023    139.716"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-085 郵便番号で結合。複数紐づく場合は緯度経度を平均値の使用\n",
    "latlngs = @chain df_geocodes begin\n",
    "    groupby(:postal_cd)\n",
    "    combine(\n",
    "        :latitude => mean => :latitude,\n",
    "        :longitude => mean => :longitude\n",
    "    )\n",
    "end\n",
    "\n",
    "@chain df_customers begin\n",
    "    innerjoin(latlngs, on= :postal_cd => :postal_cd)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "69763c77",
   "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;\">address</th><th style = \"text-align: left;\">store_address</th><th style = \"text-align: left;\">distance_to_store</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 = \"String\" style = \"text-align: left;\">String</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;\">CS020301000012</td><td style = \"text-align: left;\">埼玉県川口市青木**********</td><td style = \"text-align: left;\">東京都北区十条仲原三丁目</td><td style = \"text-align: right;\">4.7222</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS051412000011</td><td style = \"text-align: left;\">埼玉県川口市飯塚**********</td><td style = \"text-align: left;\">東京都板橋区大原町</td><td style = \"text-align: right;\">3.40719</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS051412000012</td><td style = \"text-align: left;\">埼玉県川口市飯塚**********</td><td style = \"text-align: left;\">東京都板橋区大原町</td><td style = \"text-align: right;\">3.40719</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">CS051212000001</td><td style = \"text-align: left;\">埼玉県川口市飯塚**********</td><td style = \"text-align: left;\">東京都板橋区大原町</td><td style = \"text-align: right;\">3.40719</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">CS020112000003</td><td style = \"text-align: left;\">埼玉県川口市川口**********</td><td style = \"text-align: left;\">東京都北区十条仲原三丁目</td><td style = \"text-align: right;\">3.94494</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">CS020212000016</td><td style = \"text-align: left;\">埼玉県川口市川口**********</td><td style = \"text-align: left;\">東京都北区十条仲原三丁目</td><td style = \"text-align: right;\">3.94494</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">CS051512000001</td><td style = \"text-align: left;\">埼玉県川口市川口**********</td><td style = \"text-align: left;\">東京都板橋区大原町</td><td style = \"text-align: right;\">4.1509</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">CS020212000004</td><td style = \"text-align: left;\">埼玉県川口市川口**********</td><td style = \"text-align: left;\">東京都北区十条仲原三丁目</td><td style = \"text-align: right;\">3.94494</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">CS051502000001</td><td style = \"text-align: left;\">埼玉県川口市川口**********</td><td style = \"text-align: left;\">東京都板橋区大原町</td><td style = \"text-align: right;\">4.1509</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">CS020212000008</td><td style = \"text-align: left;\">埼玉県川口市川口**********</td><td style = \"text-align: left;\">東京都北区十条仲原三丁目</td><td style = \"text-align: right;\">3.94494</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& customer\\_id & address & store\\_address & distance\\_to\\_store\\\\\n",
       "\t\\hline\n",
       "\t& String15 & String & String & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & CS020301000012 & 埼玉県川口市青木********** & 東京都北区十条仲原三丁目 & 4.7222 \\\\\n",
       "\t2 & CS051412000011 & 埼玉県川口市飯塚********** & 東京都板橋区大原町 & 3.40719 \\\\\n",
       "\t3 & CS051412000012 & 埼玉県川口市飯塚********** & 東京都板橋区大原町 & 3.40719 \\\\\n",
       "\t4 & CS051212000001 & 埼玉県川口市飯塚********** & 東京都板橋区大原町 & 3.40719 \\\\\n",
       "\t5 & CS020112000003 & 埼玉県川口市川口********** & 東京都北区十条仲原三丁目 & 3.94494 \\\\\n",
       "\t6 & CS020212000016 & 埼玉県川口市川口********** & 東京都北区十条仲原三丁目 & 3.94494 \\\\\n",
       "\t7 & CS051512000001 & 埼玉県川口市川口********** & 東京都板橋区大原町 & 4.1509 \\\\\n",
       "\t8 & CS020212000004 & 埼玉県川口市川口********** & 東京都北区十条仲原三丁目 & 3.94494 \\\\\n",
       "\t9 & CS051502000001 & 埼玉県川口市川口********** & 東京都板橋区大原町 & 4.1509 \\\\\n",
       "\t10 & CS020212000008 & 埼玉県川口市川口********** & 東京都北区十条仲原三丁目 & 3.94494 \\\\\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 address                    \u001b[0m\u001b[1m store_address            \u001b[0m\u001b[1m distance_to_store \u001b[0m\n",
       "     │\u001b[90m String15       \u001b[0m\u001b[90m String                     \u001b[0m\u001b[90m String                   \u001b[0m\u001b[90m Float64           \u001b[0m\n",
       "─────┼─────────────────────────────────────────────────────────────────────────────────────────\n",
       "   1 │ CS020301000012  埼玉県川口市青木**********  東京都北区十条仲原三丁目            4.7222\n",
       "   2 │ CS051412000011  埼玉県川口市飯塚**********  東京都板橋区大原町                  3.40719\n",
       "   3 │ CS051412000012  埼玉県川口市飯塚**********  東京都板橋区大原町                  3.40719\n",
       "   4 │ CS051212000001  埼玉県川口市飯塚**********  東京都板橋区大原町                  3.40719\n",
       "   5 │ CS020112000003  埼玉県川口市川口**********  東京都北区十条仲原三丁目            3.94494\n",
       "   6 │ CS020212000016  埼玉県川口市川口**********  東京都北区十条仲原三丁目            3.94494\n",
       "   7 │ CS051512000001  埼玉県川口市川口**********  東京都板橋区大原町                  4.1509\n",
       "   8 │ CS020212000004  埼玉県川口市川口**********  東京都北区十条仲原三丁目            3.94494\n",
       "   9 │ CS051502000001  埼玉県川口市川口**********  東京都板橋区大原町                  4.1509\n",
       "  10 │ CS020212000008  埼玉県川口市川口**********  東京都北区十条仲原三丁目            3.94494"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-086 顧客と店舗の距離を算出\n",
    "latlngs = @chain df_geocodes begin\n",
    "    groupby(:postal_cd)\n",
    "    combine(:latitude => mean => :latitude, :longitude => mean => :longitude)\n",
    "end\n",
    "\n",
    "stores_latlngs = @chain df_stores begin\n",
    "    select(:store_cd, :address => :store_address, \n",
    "        :latitude => :store_latitude, :longitude => :store_longitude)\n",
    "end\n",
    "\n",
    "@chain df_customers begin\n",
    "    innerjoin(latlngs, on= :postal_cd => :postal_cd)\n",
    "    innerjoin(stores_latlngs, on= :application_store_cd => :store_cd)\n",
    "    @rtransform(:distance_to_store = begin\n",
    "        ϕ₁, ϕ₂ = deg2rad.([:latitude, :store_latitude])\n",
    "        λ₁, λ₂  = deg2rad.([:longitude, :store_longitude])\n",
    "        6371 * acos(sin(ϕ₁)*sin(ϕ₂) + cos(ϕ₁)*cos(ϕ₂)*cos(λ₁ - λ₂))\n",
    "    end)\n",
    "    select(:customer_id, :address, :store_address, :distance_to_store)\n",
    "    first(10)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "174ce0fb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"all: 21971, refined: 21941, dup: 30\""
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>5×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 = \"Dates.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;\">CS037211000016</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;\">1993-04-24</td><td style = \"text-align: right;\">25</td><td style = \"text-align: left;\">101-0025</td><td style = \"text-align: left;\">東京都千代田区神田佐久間町**********</td><td style = \"text-align: left;\">S13037</td><td style = \"text-align: right;\">20150531</td><td style = \"text-align: left;\">0-00000000-0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">CS015301000001</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;\">1982-03-23</td><td style = \"text-align: right;\">37</td><td style = \"text-align: left;\">101-0025</td><td style = \"text-align: left;\">東京都千代田区神田佐久間町**********</td><td style = \"text-align: left;\">S13015</td><td style = \"text-align: right;\">20150216</td><td style = \"text-align: left;\">0-00000000-0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">CS037112000007</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;\">2004-01-20</td><td style = \"text-align: right;\">15</td><td style = \"text-align: left;\">101-0031</td><td style = \"text-align: left;\">東京都千代田区東神田**********</td><td style = \"text-align: left;\">S13037</td><td style = \"text-align: right;\">20151108</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;\">CS015311000003</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-08-12</td><td style = \"text-align: right;\">33</td><td style = \"text-align: left;\">101-0031</td><td style = \"text-align: left;\">東京都千代田区東神田**********</td><td style = \"text-align: left;\">S13015</td><td style = \"text-align: right;\">20141226</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;\">CS015301000007</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;\">1983-08-09</td><td style = \"text-align: right;\">35</td><td style = \"text-align: left;\">101-0032</td><td style = \"text-align: left;\">東京都千代田区岩本町**********</td><td style = \"text-align: left;\">S13015</td><td style = \"text-align: right;\">20151014</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 & CS037211000016 & 大森 杏 & 1 & 女性 & 1993-04-24 & 25 & 101-0025 & 東京都千代田区神田佐久間町********** & S13037 & 20150531 & 0-00000000-0 \\\\\n",
       "\t2 & CS015301000001 & 米倉 聖陽 & 0 & 男性 & 1982-03-23 & 37 & 101-0025 & 東京都千代田区神田佐久間町********** & S13015 & 20150216 & 0-00000000-0 \\\\\n",
       "\t3 & CS037112000007 & 寺西 さやか & 1 & 女性 & 2004-01-20 & 15 & 101-0031 & 東京都千代田区東神田********** & S13037 & 20151108 & 0-00000000-0 \\\\\n",
       "\t4 & CS015311000003 & 藤沢 知世 & 1 & 女性 & 1985-08-12 & 33 & 101-0031 & 東京都千代田区東神田********** & S13015 & 20141226 & 0-00000000-0 \\\\\n",
       "\t5 & CS015301000007 & 大畑 秀隆 & 0 & 男性 & 1983-08-09 & 35 & 101-0032 & 東京都千代田区岩本町********** & S13015 & 20151014 & 0-00000000-0 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m5×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 │ CS037211000016  大森 杏                1  女性     1993-04-24     25  101-0025   東京都千代田区神田佐久間町******…  S13037                        20150531  0-00000000-0\n",
       "   2 │ CS015301000001  米倉 聖陽              0  男性     1982-03-23     37  101-0025   東京都千代田区神田佐久間町******…  S13015                        20150216  0-00000000-0\n",
       "   3 │ CS037112000007  寺西 さやか            1  女性     2004-01-20     15  101-0031   東京都千代田区東神田**********     S13037                        20151108  0-00000000-0\n",
       "   4 │ CS015311000003  藤沢 知世              1  女性     1985-08-12     33  101-0031   東京都千代田区東神田**********     S13015                        20141226  0-00000000-0\n",
       "   5 │ CS015301000007  大畑 秀隆              0  男性     1983-08-09     35  101-0032   東京都千代田区岩本町**********     S13015                        20151014  0-00000000-0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-087 名前と郵便番号で名寄せ。売上金額が最も高い顧客IDを残す。rank\n",
    "ranked_ids = @chain df_customers begin\n",
    "    leftjoin(df_receipts, on= :customer_id => :customer_id)\n",
    "    groupby([:customer_id, :customer_name, :postal_cd])\n",
    "    combine(:amount => sum)\n",
    "    groupby([:postal_cd, :customer_name])\n",
    "    @transform(:rank = ismissing(:amount_sum)\n",
    "        ? competerank(:amount_sum, rev=true)\n",
    "        : competerank(:customer_id))\n",
    "    sort([:postal_cd, :customer_name, :rank])\n",
    "end\n",
    "\n",
    "refined_customers = @chain df_customers begin\n",
    "    innerjoin(ranked_ids, on= :customer_id => :customer_id, makeunique=true)\n",
    "    @rsubset(:rank == 1)\n",
    "    select(names(df_customers))\n",
    "end\n",
    "    \n",
    "string(\n",
    "    \"all: \",       nrow(df_customers),\n",
    "    \", refined: \", nrow(refined_customers),\n",
    "    \", dup: \",     nrow(@chain ranked_ids @rsubset(:rank > 1)),\n",
    ") |> display\n",
    "first(refined_customers, 5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "cfe32657",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2-element Vector{Int64}:\n",
       " 21941\n",
       " 21971"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-088\n",
    "length.(unique.(\n",
    "    [refined_customers.customer_id, df_customers.customer_id]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "9b68e7ef",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "nrow(base) = 8306\n",
      "limit = (df_receipts.customer_id |> unique) |> length = 8307\n",
      "(limit * 0.2, limit * 0.8) = (1661.4, 6645.6)\n",
      "(antijoin(df_receipts, df_customers, on = :customer_id)).customer_id |> unique = String15[\"ZZ000000000000\"]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "3-element Vector{Int64}:\n",
       "  1661\n",
       "  6645\n",
       " 21971"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "8306"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-089 売上実績がある顧客を8:2の割合でランダムに分割\n",
    "using Random\n",
    "base = @chain df_customers begin\n",
    "    semijoin(df_receipts, on= :customer_id => :customer_id)\n",
    "    @aside random_numbers = randperm(nrow(_))\n",
    "    @transform(:random_id = random_numbers)\n",
    "end\n",
    "\n",
    "customers_20pct = @chain base @rsubset(:random_id ≤ nrow(base) * 0.2)\n",
    "customers_80pct = antijoin(base, customers_20pct, on= :random_id)\n",
    "\n",
    "# 以下、検算\n",
    "# df_receipts　に　\"ZZ000000000000\"　というダミーの顧客IDがあり、df_customers と1件合わない。\n",
    "@show nrow(base) # => 8306\n",
    "@show limit = df_receipts.customer_id |> unique |> length # => 8307\n",
    "@show limit * 0.2, limit * 0.8\n",
    "@show antijoin(df_receipts, df_customers, on= :customer_id).customer_id |> unique\n",
    "\n",
    "nrow.([customers_20pct, customers_80pct, df_customers]) |> display\n",
    "nrow(customers_20pct) + nrow(customers_80pct)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "84043e51",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(df_train_1, df_test_1) = split_date(monthly_amounts) = (13×2 DataFrame\n",
      " Row │ sales_ym  amount_sum\n",
      "     │ String    Int64\n",
      "─────┼──────────────────────\n",
      "   1 │ 201701        902056\n",
      "   2 │ 201702        764413\n",
      "   3 │ 201703        962945\n",
      "   4 │ 201704        847566\n",
      "   5 │ 201705        884010\n",
      "   6 │ 201706        894242\n",
      "   7 │ 201707        959205\n",
      "   8 │ 201708        954836\n",
      "   9 │ 201709        902037\n",
      "  10 │ 201710        905739\n",
      "  11 │ 201711        932157\n",
      "  12 │ 201712        939654\n",
      "  13 │ 201801        944509, 7×2 DataFrame\n",
      " Row │ sales_ym  amount_sum\n",
      "     │ String    Int64\n",
      "─────┼──────────────────────\n",
      "   1 │ 201801        944509\n",
      "   2 │ 201802        864128\n",
      "   3 │ 201803        946588\n",
      "   4 │ 201804        937099\n",
      "   5 │ 201805       1004438\n",
      "   6 │ 201806       1012329\n",
      "   7 │ 201807       1058472)\n",
      "(df_train_2, df_test_2) = split_date(monthly_amounts, 1) = (13×2 DataFrame\n",
      " Row │ sales_ym  amount_sum\n",
      "     │ String    Int64\n",
      "─────┼──────────────────────\n",
      "   1 │ 201707        959205\n",
      "   2 │ 201708        954836\n",
      "   3 │ 201709        902037\n",
      "   4 │ 201710        905739\n",
      "   5 │ 201711        932157\n",
      "   6 │ 201712        939654\n",
      "   7 │ 201801        944509\n",
      "   8 │ 201802        864128\n",
      "   9 │ 201803        946588\n",
      "  10 │ 201804        937099\n",
      "  11 │ 201805       1004438\n",
      "  12 │ 201806       1012329\n",
      "  13 │ 201807       1058472, 7×2 DataFrame\n",
      " Row │ sales_ym  amount_sum\n",
      "     │ String    Int64\n",
      "─────┼──────────────────────\n",
      "   1 │ 201807       1058472\n",
      "   2 │ 201808       1045793\n",
      "   3 │ 201809        977114\n",
      "   4 │ 201810       1069939\n",
      "   5 │ 201811        967479\n",
      "   6 │ 201812       1016425\n",
      "   7 │ 201901       1064085)\n",
      "(df_train_3, df_test_3) = split_date(monthly_amounts, 2) = (13×2 DataFrame\n",
      " Row │ sales_ym  amount_sum\n",
      "     │ String    Int64\n",
      "─────┼──────────────────────\n",
      "   1 │ 201801        944509\n",
      "   2 │ 201802        864128\n",
      "   3 │ 201803        946588\n",
      "   4 │ 201804        937099\n",
      "   5 │ 201805       1004438\n",
      "   6 │ 201806       1012329\n",
      "   7 │ 201807       1058472\n",
      "   8 │ 201808       1045793\n",
      "   9 │ 201809        977114\n",
      "  10 │ 201810       1069939\n",
      "  11 │ 201811        967479\n",
      "  12 │ 201812       1016425\n",
      "  13 │ 201901       1064085, 7×2 DataFrame\n",
      " Row │ sales_ym  amount_sum\n",
      "     │ String    Int64\n",
      "─────┼──────────────────────\n",
      "   1 │ 201901       1064085\n",
      "   2 │ 201902        959538\n",
      "   3 │ 201903       1093753\n",
      "   4 │ 201904       1044210\n",
      "   5 │ 201905       1111985\n",
      "   6 │ 201906       1089063\n",
      "   7 │ 201907       1118371)\n"
     ]
    }
   ],
   "source": [
    "# P-090 月ごとの金額を集計し、学習用に12ヶ月、テスト用に6ヶ月の時系列モデルを構築\n",
    "monthly_amounts = @chain df_receipts begin\n",
    "    @rtransform(:sales_ym = string(:sales_ymd)[1:6])\n",
    "    groupby(:sales_ym)\n",
    "    combine(:amount => sum)\n",
    "    sort(:sales_ym)\n",
    "end\n",
    "\n",
    "function split_date(df, offset_scale = 0; train_size = 12, test_size = 6, slide_window = 6)\n",
    "    train_start = 1 + offset_scale * slide_window\n",
    "    test_start  = train_start + train_size\n",
    "    return df[train_start:test_start, :], df[test_start:(test_start + test_size), :]\n",
    "end\n",
    "\n",
    "@show df_train_1, df_test_1 = split_date(monthly_amounts)\n",
    "@show df_train_2, df_test_2 = split_date(monthly_amounts, 1)\n",
    "@show df_train_3, df_test_3 = split_date(monthly_amounts, 2)\n",
    ";"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "eb729b50",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>13×7 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;\">variable</th><th style = \"text-align: left;\">mean</th><th style = \"text-align: left;\">min</th><th style = \"text-align: left;\">median</th><th style = \"text-align: left;\">max</th><th style = \"text-align: left;\">nmissing</th><th style = \"text-align: left;\">eltype</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Symbol\" style = \"text-align: left;\">Symbol</th><th title = \"Union{Nothing, Float64}\" style = \"text-align: left;\">Union…</th><th title = \"Any\" style = \"text-align: left;\">Any</th><th title = \"Any\" style = \"text-align: left;\">Any</th><th title = \"Any\" style = \"text-align: left;\">Any</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"DataType\" style = \"text-align: left;\">DataType</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">customer_id</td><td style = \"font-style: italic; text-align: left;\"></td><td style = \"text-align: left;\">CS001105000001</td><td style = \"font-style: italic; text-align: left;\"></td><td style = \"text-align: left;\">CS052514000001</td><td style = \"text-align: right;\">0</td><td style = \"text-align: left;\">String15</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">customer_name</td><td style = \"font-style: italic; text-align: left;\"></td><td style = \"text-align: left;\">おかやま あさみ</td><td style = \"font-style: italic; text-align: left;\"></td><td style = \"text-align: left;\">黒谷 麻緒</td><td style = \"text-align: right;\">0</td><td style = \"text-align: left;\">String31</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">gender_cd</td><td style = \"text-align: left;\">1.27871</td><td style = \"text-align: left;\">0</td><td style = \"text-align: left;\">1.0</td><td style = \"text-align: left;\">9</td><td style = \"text-align: right;\">0</td><td style = \"text-align: left;\">Int64</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">gender</td><td style = \"font-style: italic; text-align: left;\"></td><td style = \"text-align: left;\">不明</td><td style = \"font-style: italic; text-align: left;\"></td><td style = \"text-align: left;\">男性</td><td style = \"text-align: right;\">0</td><td style = \"text-align: left;\">String7</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">birth_day</td><td style = \"font-style: italic; text-align: left;\"></td><td style = \"text-align: left;\">1928-11-26</td><td style = \"text-align: left;\">1969-03-03</td><td style = \"text-align: left;\">2007-11-25</td><td style = \"text-align: right;\">0</td><td style = \"text-align: left;\">Date</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">age</td><td style = \"text-align: left;\">50.4663</td><td style = \"text-align: left;\">11</td><td style = \"text-align: left;\">50.0</td><td style = \"text-align: left;\">90</td><td style = \"text-align: right;\">0</td><td style = \"text-align: left;\">Int64</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">7</td><td style = \"text-align: left;\">postal_cd</td><td style = \"font-style: italic; text-align: left;\"></td><td style = \"text-align: left;\">101-0025</td><td style = \"font-style: italic; text-align: left;\"></td><td style = \"text-align: left;\">359-1132</td><td style = \"text-align: right;\">0</td><td style = \"text-align: left;\">String15</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">8</td><td style = \"text-align: left;\">address</td><td style = \"font-style: italic; text-align: left;\"></td><td style = \"text-align: left;\">千葉県佐倉市ユーカリが丘**********</td><td style = \"font-style: italic; text-align: left;\"></td><td style = \"text-align: left;\">神奈川県鎌倉市西鎌倉**********</td><td style = \"text-align: right;\">0</td><td style = \"text-align: left;\">String</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">9</td><td style = \"text-align: left;\">application_store_cd</td><td style = \"font-style: italic; text-align: left;\"></td><td style = \"text-align: left;\">S12007</td><td style = \"font-style: italic; text-align: left;\"></td><td style = \"text-align: left;\">S14050</td><td style = \"text-align: right;\">0</td><td style = \"text-align: left;\">String7</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">10</td><td style = \"text-align: left;\">application_date</td><td style = \"text-align: left;\">2.01558e7</td><td style = \"text-align: left;\">20131203</td><td style = \"text-align: left;\">2.01509e7</td><td style = \"text-align: left;\">20190319</td><td style = \"text-align: right;\">0</td><td style = \"text-align: left;\">Int64</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">11</td><td style = \"text-align: left;\">status_cd</td><td style = \"font-style: italic; text-align: left;\"></td><td style = \"text-align: left;\">0-00000000-0</td><td style = \"font-style: italic; text-align: left;\"></td><td style = \"text-align: left;\">F-20101030-F</td><td style = \"text-align: right;\">0</td><td style = \"text-align: left;\">String15</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">12</td><td style = \"text-align: left;\">purchased</td><td style = \"text-align: left;\">0.5</td><td style = \"text-align: left;\">false</td><td style = \"text-align: left;\">0.5</td><td style = \"text-align: left;\">true</td><td style = \"text-align: right;\">0</td><td style = \"text-align: left;\">Bool</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">13</td><td style = \"text-align: left;\">rank</td><td style = \"text-align: left;\">4153.5</td><td style = \"text-align: left;\">1</td><td style = \"text-align: left;\">4153.5</td><td style = \"text-align: left;\">8306</td><td style = \"text-align: right;\">0</td><td style = \"text-align: left;\">Int64</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccccccc}\n",
       "\t& variable & mean & min & median & max & nmissing & eltype\\\\\n",
       "\t\\hline\n",
       "\t& Symbol & Union… & Any & Any & Any & Int64 & DataType\\\\\n",
       "\t\\hline\n",
       "\t1 & customer\\_id &  & CS001105000001 &  & CS052514000001 & 0 & String15 \\\\\n",
       "\t2 & customer\\_name &  & おかやま あさみ &  & 黒谷 麻緒 & 0 & String31 \\\\\n",
       "\t3 & gender\\_cd & 1.27871 & 0 & 1.0 & 9 & 0 & Int64 \\\\\n",
       "\t4 & gender &  & 不明 &  & 男性 & 0 & String7 \\\\\n",
       "\t5 & birth\\_day &  & 1928-11-26 & 1969-03-03 & 2007-11-25 & 0 & Date \\\\\n",
       "\t6 & age & 50.4663 & 11 & 50.0 & 90 & 0 & Int64 \\\\\n",
       "\t7 & postal\\_cd &  & 101-0025 &  & 359-1132 & 0 & String15 \\\\\n",
       "\t8 & address &  & 千葉県佐倉市ユーカリが丘********** &  & 神奈川県鎌倉市西鎌倉********** & 0 & String \\\\\n",
       "\t9 & application\\_store\\_cd &  & S12007 &  & S14050 & 0 & String7 \\\\\n",
       "\t10 & application\\_date & 2.01558e7 & 20131203 & 2.01509e7 & 20190319 & 0 & Int64 \\\\\n",
       "\t11 & status\\_cd &  & 0-00000000-0 &  & F-20101030-F & 0 & String15 \\\\\n",
       "\t12 & purchased & 0.5 & 0 & 0.5 & 1 & 0 & Bool \\\\\n",
       "\t13 & rank & 4153.5 & 1 & 4153.5 & 8306 & 0 & Int64 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m13×7 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m variable             \u001b[0m\u001b[1m mean      \u001b[0m\u001b[1m min                               \u001b[0m\u001b[1m median     \u001b[0m\u001b[1m max                            \u001b[0m\u001b[1m nmissing \u001b[0m\u001b[1m eltype   \u001b[0m\n",
       "     │\u001b[90m Symbol               \u001b[0m\u001b[90m Union…    \u001b[0m\u001b[90m Any                               \u001b[0m\u001b[90m Any        \u001b[0m\u001b[90m Any                            \u001b[0m\u001b[90m Int64    \u001b[0m\u001b[90m DataType \u001b[0m\n",
       "─────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────\n",
       "   1 │ customer_id          \u001b[90m           \u001b[0m CS001105000001                    \u001b[90m            \u001b[0m CS052514000001                         0  String15\n",
       "   2 │ customer_name        \u001b[90m           \u001b[0m おかやま あさみ                   \u001b[90m            \u001b[0m 黒谷 麻緒                              0  String31\n",
       "   3 │ gender_cd             1.27871    0                                  1.0         9                                      0  Int64\n",
       "   4 │ gender               \u001b[90m           \u001b[0m 不明                              \u001b[90m            \u001b[0m 男性                                   0  String7\n",
       "   5 │ birth_day            \u001b[90m           \u001b[0m 1928-11-26                         1969-03-03  2007-11-25                             0  Date\n",
       "   6 │ age                   50.4663    11                                 50.0        90                                     0  Int64\n",
       "   7 │ postal_cd            \u001b[90m           \u001b[0m 101-0025                          \u001b[90m            \u001b[0m 359-1132                               0  String15\n",
       "   8 │ address              \u001b[90m           \u001b[0m 千葉県佐倉市ユーカリが丘********… \u001b[90m            \u001b[0m 神奈川県鎌倉市西鎌倉**********         0  String\n",
       "   9 │ application_store_cd \u001b[90m           \u001b[0m S12007                            \u001b[90m            \u001b[0m S14050                                 0  String7\n",
       "  10 │ application_date      2.01558e7  20131203                           2.01509e7   20190319                               0  Int64\n",
       "  11 │ status_cd            \u001b[90m           \u001b[0m 0-00000000-0                      \u001b[90m            \u001b[0m F-20101030-F                           0  String15\n",
       "  12 │ purchased             0.5        false                              0.5         true                                   0  Bool\n",
       "  13 │ rank                  4153.5     1                                  4153.5      8306                                   0  Int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "2-element Vector{Int64}:\n",
       " 8306\n",
       " 8306"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-091 売上実績がある顧客と無い顧客で、1:1となるようにアンダーサンプリングする\n",
    "sampled_customers = @chain df_customers begin\n",
    "    @aside purchased_ids = @chain _ begin\n",
    "        semijoin(df_receipts, on= :customer_id => :customer_id)\n",
    "        @rtransform(:purchased = true)\n",
    "        select(:customer_id, :purchased)\n",
    "    end\n",
    "    leftjoin(purchased_ids, on= :customer_id)\n",
    "    @rtransform(:purchased = ismissing(:purchased) ? false : true)\n",
    "    groupby(:purchased)\n",
    "    @transform(:rank = competerank(:customer_id))\n",
    "    @aside limit = minimum(nrow.([purchased_ids, @rsubset(_, :purchased == false)]))\n",
    "    @rsubset(:rank ≤ limit)\n",
    "end\n",
    "\n",
    "describe(sampled_customers) |> display\n",
    "\n",
    "nrow.([@rsubset(sampled_customers, :purchased == true),\n",
    "       @rsubset(sampled_customers, :purchased == false)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "7cfece03",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×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;\">gender</th><th style = \"text-align: left;\">gender_cd</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"String7\" style = \"text-align: left;\">String7</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;\">女性</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;\">不明</td><td style = \"text-align: right;\">9</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">男性</td><td style = \"text-align: right;\">0</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& gender & gender\\_cd\\\\\n",
       "\t\\hline\n",
       "\t& String7 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 女性 & 1 \\\\\n",
       "\t2 & 不明 & 9 \\\\\n",
       "\t3 & 男性 & 0 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m gender  \u001b[0m\u001b[1m gender_cd \u001b[0m\n",
       "     │\u001b[90m String7 \u001b[0m\u001b[90m Int64     \u001b[0m\n",
       "─────┼────────────────────\n",
       "   1 │ 女性             1\n",
       "   2 │ 不明             9\n",
       "   3 │ 男性             0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×10 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;\">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 = \"Dates.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;\">CS021313000114</td><td style = \"text-align: left;\">大野 あや子</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">1981-04-29</td><td style = \"text-align: right;\">37</td><td style = \"text-align: left;\">259-1113</td><td style = \"text-align: left;\">神奈川県伊勢原市粟窪**********</td><td style = \"text-align: left;\">S14021</td><td style = \"text-align: right;\">20150905</td><td style = \"text-align: left;\">0-00000000-0</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;\">9</td><td style = \"text-align: left;\">1952-04-01</td><td style = \"text-align: right;\">66</td><td style = \"text-align: left;\">136-0076</td><td style = \"text-align: left;\">東京都江東区南砂**********</td><td style = \"text-align: left;\">S13037</td><td style = \"text-align: right;\">20150414</td><td style = \"text-align: left;\">0-00000000-0</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;\">1</td><td style = \"text-align: left;\">1976-10-04</td><td style = \"text-align: right;\">42</td><td style = \"text-align: left;\">151-0053</td><td style = \"text-align: left;\">東京都渋谷区代々木**********</td><td style = \"text-align: left;\">S13031</td><td style = \"text-align: right;\">20150529</td><td style = \"text-align: left;\">D-20100325-C</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccccccccc}\n",
       "\t& customer\\_id & customer\\_name & gender\\_cd & birth\\_day & age & postal\\_cd & address & application\\_store\\_cd & application\\_date & status\\_cd\\\\\n",
       "\t\\hline\n",
       "\t& String15 & String31 & Int64 & Date & Int64 & String15 & String & String7 & Int64 & String15\\\\\n",
       "\t\\hline\n",
       "\t1 & CS021313000114 & 大野 あや子 & 1 & 1981-04-29 & 37 & 259-1113 & 神奈川県伊勢原市粟窪********** & S14021 & 20150905 & 0-00000000-0 \\\\\n",
       "\t2 & CS037613000071 & 六角 雅彦 & 9 & 1952-04-01 & 66 & 136-0076 & 東京都江東区南砂********** & S13037 & 20150414 & 0-00000000-0 \\\\\n",
       "\t3 & CS031415000172 & 宇多田 貴美子 & 1 & 1976-10-04 & 42 & 151-0053 & 東京都渋谷区代々木********** & S13031 & 20150529 & D-20100325-C \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×10 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 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 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 │ CS021313000114  大野 あや子            1  1981-04-29     37  259-1113   神奈川県伊勢原市粟窪**********  S14021                        20150905  0-00000000-0\n",
       "   2 │ CS037613000071  六角 雅彦              9  1952-04-01     66  136-0076   東京都江東区南砂**********      S13037                        20150414  0-00000000-0\n",
       "   3 │ CS031415000172  宇多田 貴美子          1  1976-10-04     42  151-0053   東京都渋谷区代々木**********    S13031                        20150529  D-20100325-C"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×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;\">gender</th><th style = \"text-align: left;\">gender_cd</th><th style = \"text-align: left;\">customer_id</th><th style = \"text-align: left;\">customer_name</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 = \"Union{Missing, 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><th title = \"String31\" style = \"text-align: left;\">String31</th><th title = \"Dates.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;\">女性</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">CS021313000114</td><td style = \"text-align: left;\">大野 あや子</td><td style = \"text-align: left;\">1981-04-29</td><td style = \"text-align: right;\">37</td><td style = \"text-align: left;\">259-1113</td><td style = \"text-align: left;\">神奈川県伊勢原市粟窪**********</td><td style = \"text-align: left;\">S14021</td><td style = \"text-align: right;\">20150905</td><td style = \"text-align: left;\">0-00000000-0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">不明</td><td style = \"text-align: right;\">9</td><td style = \"text-align: left;\">CS037613000071</td><td style = \"text-align: left;\">六角 雅彦</td><td style = \"text-align: left;\">1952-04-01</td><td style = \"text-align: right;\">66</td><td style = \"text-align: left;\">136-0076</td><td style = \"text-align: left;\">東京都江東区南砂**********</td><td style = \"text-align: left;\">S13037</td><td style = \"text-align: right;\">20150414</td><td style = \"text-align: left;\">0-00000000-0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">女性</td><td style = \"text-align: right;\">1</td><td style = \"text-align: left;\">CS031415000172</td><td style = \"text-align: left;\">宇多田 貴美子</td><td style = \"text-align: left;\">1976-10-04</td><td style = \"text-align: right;\">42</td><td style = \"text-align: left;\">151-0053</td><td style = \"text-align: left;\">東京都渋谷区代々木**********</td><td style = \"text-align: left;\">S13031</td><td style = \"text-align: right;\">20150529</td><td style = \"text-align: left;\">D-20100325-C</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccccccccccc}\n",
       "\t& gender & gender\\_cd & customer\\_id & customer\\_name & birth\\_day & age & postal\\_cd & address & application\\_store\\_cd & application\\_date & status\\_cd\\\\\n",
       "\t\\hline\n",
       "\t& String7? & Int64 & String15 & String31 & Date & Int64 & String15 & String & String7 & Int64 & String15\\\\\n",
       "\t\\hline\n",
       "\t1 & 女性 & 1 & CS021313000114 & 大野 あや子 & 1981-04-29 & 37 & 259-1113 & 神奈川県伊勢原市粟窪********** & S14021 & 20150905 & 0-00000000-0 \\\\\n",
       "\t2 & 不明 & 9 & CS037613000071 & 六角 雅彦 & 1952-04-01 & 66 & 136-0076 & 東京都江東区南砂********** & S13037 & 20150414 & 0-00000000-0 \\\\\n",
       "\t3 & 女性 & 1 & CS031415000172 & 宇多田 貴美子 & 1976-10-04 & 42 & 151-0053 & 東京都渋谷区代々木********** & S13031 & 20150529 & D-20100325-C \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×11 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m gender   \u001b[0m\u001b[1m gender_cd \u001b[0m\u001b[1m customer_id    \u001b[0m\u001b[1m customer_name \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 String7? \u001b[0m\u001b[90m Int64     \u001b[0m\u001b[90m String15       \u001b[0m\u001b[90m String31      \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 │ 女性              1  CS021313000114  大野 あや子    1981-04-29     37  259-1113   神奈川県伊勢原市粟窪**********  S14021                        20150905  0-00000000-0\n",
       "   2 │ 不明              9  CS037613000071  六角 雅彦      1952-04-01     66  136-0076   東京都江東区南砂**********      S13037                        20150414  0-00000000-0\n",
       "   3 │ 女性              1  CS031415000172  宇多田 貴美子  1976-10-04     42  151-0053   東京都渋谷区代々木**********    S13031                        20150529  D-20100325-C"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-092 顧客の性別を第3正規形へ正規化\n",
    "genders = unique(df_customers[:, [:gender, :gender_cd]])\n",
    "new_customers = select(df_customers, Not(:gender))\n",
    "\n",
    "display(genders)\n",
    "first(new_customers, 3) |> display\n",
    "first(rightjoin(genders, new_customers, on = :gender_cd), 3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "48c0b275",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>10×9 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;\">category_major_cd</th><th style = \"text-align: left;\">category_medium_cd</th><th style = \"text-align: left;\">category_small_cd</th><th style = \"text-align: left;\">unit_price</th><th style = \"text-align: left;\">unit_cost</th><th style = \"text-align: left;\">category_major_name</th><th style = \"text-align: left;\">category_medium_name</th><th style = \"text-align: left;\">category_small_name</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 = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"String31\" style = \"text-align: left;\">String31</th><th title = \"String\" style = \"text-align: left;\">String</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: left;\">P040101001</td><td style = \"text-align: right;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">198</td><td style = \"text-align: right;\">149</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">218</td><td style = \"text-align: right;\">164</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">230</td><td style = \"text-align: right;\">173</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">248</td><td style = \"text-align: right;\">186</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">268</td><td style = \"text-align: right;\">201</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">298</td><td style = \"text-align: right;\">224</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">338</td><td style = \"text-align: right;\">254</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">420</td><td style = \"text-align: right;\">315</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">498</td><td style = \"text-align: right;\">374</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">580</td><td style = \"text-align: right;\">435</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccccccccc}\n",
       "\t& product\\_cd & category\\_major\\_cd & category\\_medium\\_cd & category\\_small\\_cd & unit\\_price & unit\\_cost & category\\_major\\_name & category\\_medium\\_name & category\\_small\\_name\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64 & Int64 & Int64 & Int64? & Int64? & String31 & String & String\\\\\n",
       "\t\\hline\n",
       "\t1 & P040101001 & 4 & 401 & 40101 & 198 & 149 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\t2 & P040101002 & 4 & 401 & 40101 & 218 & 164 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\t3 & P040101003 & 4 & 401 & 40101 & 230 & 173 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\t4 & P040101004 & 4 & 401 & 40101 & 248 & 186 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\t5 & P040101005 & 4 & 401 & 40101 & 268 & 201 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\t6 & P040101006 & 4 & 401 & 40101 & 298 & 224 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\t7 & P040101007 & 4 & 401 & 40101 & 338 & 254 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\t8 & P040101008 & 4 & 401 & 40101 & 420 & 315 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\t9 & P040101009 & 4 & 401 & 40101 & 498 & 374 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\t10 & P040101010 & 4 & 401 & 40101 & 580 & 435 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m10×9 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m product_cd \u001b[0m\u001b[1m category_major_cd \u001b[0m\u001b[1m category_medium_cd \u001b[0m\u001b[1m category_small_cd \u001b[0m\u001b[1m unit_price \u001b[0m\u001b[1m unit_cost \u001b[0m\u001b[1m category_major_name \u001b[0m\u001b[1m category_medium_name \u001b[0m\u001b[1m category_small_name \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 Int64?     \u001b[0m\u001b[90m Int64?    \u001b[0m\u001b[90m String31            \u001b[0m\u001b[90m String               \u001b[0m\u001b[90m String              \u001b[0m\n",
       "─────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────\n",
       "   1 │ P040101001                  4                 401              40101         198        149  惣菜                 御飯類                弁当類\n",
       "   2 │ P040101002                  4                 401              40101         218        164  惣菜                 御飯類                弁当類\n",
       "   3 │ P040101003                  4                 401              40101         230        173  惣菜                 御飯類                弁当類\n",
       "   4 │ P040101004                  4                 401              40101         248        186  惣菜                 御飯類                弁当類\n",
       "   5 │ P040101005                  4                 401              40101         268        201  惣菜                 御飯類                弁当類\n",
       "   6 │ P040101006                  4                 401              40101         298        224  惣菜                 御飯類                弁当類\n",
       "   7 │ P040101007                  4                 401              40101         338        254  惣菜                 御飯類                弁当類\n",
       "   8 │ P040101008                  4                 401              40101         420        315  惣菜                 御飯類                弁当類\n",
       "   9 │ P040101009                  4                 401              40101         498        374  惣菜                 御飯類                弁当類\n",
       "  10 │ P040101010                  4                 401              40101         580        435  惣菜                 御飯類                弁当類"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-093 商品データにカテゴリ名列を追加: 非正規化\n",
    "new_products = @chain df_products begin\n",
    "   innerjoin(df_categories, on= :category_small_cd => :category_small_cd,\n",
    "        makeunique=true)\n",
    "    select([names(df_products); names(df_categories)] |> unique)\n",
    "end\n",
    "\n",
    "first(new_products, 10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "83106227",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"result/P-094.csv\""
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-094 CSV出力\n",
    "mkpath(\"result\")\n",
    "CSV.write(\"result/P-094.csv\", new_products)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "ce82f8a0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "StringEncoder{UTF-8, cp932}(IOStream(<file result/P-095.csv>))"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-095 CSV出力(cp932)\n",
    "using StringEncodings\n",
    "open(\"result/P-095.csv\", enc\"cp932\", \"w\") do io\n",
    "    CSV.write(io, new_products)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "595b40b6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"result/P-096.csv\""
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-096 CSV出力(ヘッダー行なし)\n",
    "CSV.write(\"result/P-096.csv\", new_products; header=false)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "793d1c40",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×9 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;\">category_major_cd</th><th style = \"text-align: left;\">category_medium_cd</th><th style = \"text-align: left;\">category_small_cd</th><th style = \"text-align: left;\">unit_price</th><th style = \"text-align: left;\">unit_cost</th><th style = \"text-align: left;\">category_major_name</th><th style = \"text-align: left;\">category_medium_name</th><th style = \"text-align: left;\">category_small_name</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 = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"String31\" style = \"text-align: left;\">String31</th><th title = \"String\" style = \"text-align: left;\">String</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: left;\">P040101001</td><td style = \"text-align: right;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">198</td><td style = \"text-align: right;\">149</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">218</td><td style = \"text-align: right;\">164</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">230</td><td style = \"text-align: right;\">173</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccccccccc}\n",
       "\t& product\\_cd & category\\_major\\_cd & category\\_medium\\_cd & category\\_small\\_cd & unit\\_price & unit\\_cost & category\\_major\\_name & category\\_medium\\_name & category\\_small\\_name\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64 & Int64 & Int64 & Int64? & Int64? & String31 & String & String\\\\\n",
       "\t\\hline\n",
       "\t1 & P040101001 & 4 & 401 & 40101 & 198 & 149 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\t2 & P040101002 & 4 & 401 & 40101 & 218 & 164 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\t3 & P040101003 & 4 & 401 & 40101 & 230 & 173 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×9 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m product_cd \u001b[0m\u001b[1m category_major_cd \u001b[0m\u001b[1m category_medium_cd \u001b[0m\u001b[1m category_small_cd \u001b[0m\u001b[1m unit_price \u001b[0m\u001b[1m unit_cost \u001b[0m\u001b[1m category_major_name \u001b[0m\u001b[1m category_medium_name \u001b[0m\u001b[1m category_small_name \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 Int64?     \u001b[0m\u001b[90m Int64?    \u001b[0m\u001b[90m String31            \u001b[0m\u001b[90m String               \u001b[0m\u001b[90m String              \u001b[0m\n",
       "─────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────\n",
       "   1 │ P040101001                  4                 401              40101         198        149  惣菜                 御飯類                弁当類\n",
       "   2 │ P040101002                  4                 401              40101         218        164  惣菜                 御飯類                弁当類\n",
       "   3 │ P040101003                  4                 401              40101         230        173  惣菜                 御飯類                弁当類"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-097 CSV読み込み\n",
    "df = CSV.read(\"result/P-094.csv\", DataFrame)\n",
    "first(df, 3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "fbc3201c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×9 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;\">Column1</th><th style = \"text-align: left;\">Column2</th><th style = \"text-align: left;\">Column3</th><th style = \"text-align: left;\">Column4</th><th style = \"text-align: left;\">Column5</th><th style = \"text-align: left;\">Column6</th><th style = \"text-align: left;\">Column7</th><th style = \"text-align: left;\">Column8</th><th style = \"text-align: left;\">Column9</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 = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"String31\" style = \"text-align: left;\">String31</th><th title = \"String\" style = \"text-align: left;\">String</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: left;\">P040101001</td><td style = \"text-align: right;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">198</td><td style = \"text-align: right;\">149</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">218</td><td style = \"text-align: right;\">164</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">230</td><td style = \"text-align: right;\">173</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccccccccc}\n",
       "\t& Column1 & Column2 & Column3 & Column4 & Column5 & Column6 & Column7 & Column8 & Column9\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64 & Int64 & Int64 & Int64? & Int64? & String31 & String & String\\\\\n",
       "\t\\hline\n",
       "\t1 & P040101001 & 4 & 401 & 40101 & 198 & 149 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\t2 & P040101002 & 4 & 401 & 40101 & 218 & 164 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\t3 & P040101003 & 4 & 401 & 40101 & 230 & 173 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×9 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m Column1    \u001b[0m\u001b[1m Column2 \u001b[0m\u001b[1m Column3 \u001b[0m\u001b[1m Column4 \u001b[0m\u001b[1m Column5 \u001b[0m\u001b[1m Column6 \u001b[0m\u001b[1m Column7  \u001b[0m\u001b[1m Column8 \u001b[0m\u001b[1m Column9 \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 Int64?  \u001b[0m\u001b[90m Int64?  \u001b[0m\u001b[90m String31 \u001b[0m\u001b[90m String  \u001b[0m\u001b[90m String  \u001b[0m\n",
       "─────┼─────────────────────────────────────────────────────────────────────────────────────\n",
       "   1 │ P040101001        4      401    40101      198      149  惣菜      御飯類   弁当類\n",
       "   2 │ P040101002        4      401    40101      218      164  惣菜      御飯類   弁当類\n",
       "   3 │ P040101003        4      401    40101      230      173  惣菜      御飯類   弁当類"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-098 CSV読み込み(ヘッダー行なし)\n",
    "df = CSV.read(\"result/P-096.csv\", DataFrame, header=false)\n",
    "first(df, 3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "dad0ba61",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"result/P-099.tsv\""
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-099 TSV出力\n",
    "CSV.write(\"result/P-099.tsv\", new_products; delim=\"\\t\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "94d54022",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×9 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;\">category_major_cd</th><th style = \"text-align: left;\">category_medium_cd</th><th style = \"text-align: left;\">category_small_cd</th><th style = \"text-align: left;\">unit_price</th><th style = \"text-align: left;\">unit_cost</th><th style = \"text-align: left;\">category_major_name</th><th style = \"text-align: left;\">category_medium_name</th><th style = \"text-align: left;\">category_small_name</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 = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"Union{Missing, Int64}\" style = \"text-align: left;\">Int64?</th><th title = \"String31\" style = \"text-align: left;\">String31</th><th title = \"String\" style = \"text-align: left;\">String</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: left;\">P040101001</td><td style = \"text-align: right;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">198</td><td style = \"text-align: right;\">149</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">218</td><td style = \"text-align: right;\">164</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</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;\">4</td><td style = \"text-align: right;\">401</td><td style = \"text-align: right;\">40101</td><td style = \"text-align: right;\">230</td><td style = \"text-align: right;\">173</td><td style = \"text-align: left;\">惣菜</td><td style = \"text-align: left;\">御飯類</td><td style = \"text-align: left;\">弁当類</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccccccccc}\n",
       "\t& product\\_cd & category\\_major\\_cd & category\\_medium\\_cd & category\\_small\\_cd & unit\\_price & unit\\_cost & category\\_major\\_name & category\\_medium\\_name & category\\_small\\_name\\\\\n",
       "\t\\hline\n",
       "\t& String15 & Int64 & Int64 & Int64 & Int64? & Int64? & String31 & String & String\\\\\n",
       "\t\\hline\n",
       "\t1 & P040101001 & 4 & 401 & 40101 & 198 & 149 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\t2 & P040101002 & 4 & 401 & 40101 & 218 & 164 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\t3 & P040101003 & 4 & 401 & 40101 & 230 & 173 & 惣菜 & 御飯類 & 弁当類 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×9 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m product_cd \u001b[0m\u001b[1m category_major_cd \u001b[0m\u001b[1m category_medium_cd \u001b[0m\u001b[1m category_small_cd \u001b[0m\u001b[1m unit_price \u001b[0m\u001b[1m unit_cost \u001b[0m\u001b[1m category_major_name \u001b[0m\u001b[1m category_medium_name \u001b[0m\u001b[1m category_small_name \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 Int64?     \u001b[0m\u001b[90m Int64?    \u001b[0m\u001b[90m String31            \u001b[0m\u001b[90m String               \u001b[0m\u001b[90m String              \u001b[0m\n",
       "─────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────\n",
       "   1 │ P040101001                  4                 401              40101         198        149  惣菜                 御飯類                弁当類\n",
       "   2 │ P040101002                  4                 401              40101         218        164  惣菜                 御飯類                弁当類\n",
       "   3 │ P040101003                  4                 401              40101         230        173  惣菜                 御飯類                弁当類"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# P-100 TSV読み込み\n",
    "df = CSV.read(\"result/P-099.tsv\", DataFrame; delim=\"\\t\")\n",
    "first(df, 3)"
   ]
  }
 ],
 "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
}