<jupyter_start><jupyter_text>データサイエンス100本ノック（構造化データ加工編） - Python  はじめに- 初めに以下のセルを実行してください- 必要なライブラリのインポートとデータベース（PostgreSQL）からのデータ読み込みを行います- pandas等、利用が想定されるライブラリは以下セルでインポートしています- その他利用したいライブラリがあれば適宜インストールしてください（"!pip install ライブラリ名"でインストールも可能）- 処理は複数回に分けても構いません- 名前、住所等はダミーデータであり、実在するものではありません<jupyter_code>%load_ext sql
import os
import pandas as pd
import numpy as np
from datetime import datetime, date
from dateutil.relativedelta import relativedelta
import math
import psycopg2
from sqlalchemy import create_engine
from sklearn import preprocessing
from sklearn.model_selection import train_test_split
from imblearn.under_sampling import RandomUnderSampler

pgconfig = {
    'host': 'db',
    'port': os.environ['PG_PORT'],
    'database': os.environ['PG_DATABASE'],
    'user': os.environ['PG_USER'],
    'password': <PASSWORD>['<PASSWORD>'],
}
dsl = 'postgresql://{user}:{password}@{host}:{port}/{database}'.format(**pgconfig)

# pd.read_sql用のコネクタ
conn = psycopg2.connect(**pgconfig)
# pd.to_sql用のcreate engine
engine = create_engine(dsl)
# MagicコマンドでSQLを書くための設定
%sql $dsl

df_customer = pd.read_sql(sql='select * from customer', con=conn)
df_category = pd.read_sql(sql='select * from category', con=conn)
df_product = pd.read_sql(sql='select * from product', con=conn)
df_receipt = pd.read_sql(sql='select * from receipt', con=conn)
df_store = pd.read_sql(sql='select * from store', con=conn)
df_geocode = pd.read_sql(sql='select * from geocode', con=conn)<jupyter_output>The sql extension is already loaded. To reload it, use:
  %reload_ext sql<jupyter_text>dss-postgres 演習問題 ---> P-001: レシート明細のデータフレーム（df_receipt）から全項目の先頭10件を表示し、どのようなデータを保有しているか目視で確認せよ。<jupyter_code>df_receipt.head()<jupyter_output><empty_output><jupyter_text>---> P-001: レシート明細のデータフレーム（df_receipt）から全項目の先頭10件を表示し、どのようなデータを保有しているか目視で確認せよ。 ---> P-002: レシート明細のデータフレーム（df_receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、10件表示させよ。<jupyter_code># for I in range(10):
#     print("{}.{}.{}.{}".format(df_receipt.sales_ymd[I],df_receipt.customer_id[I],df_receipt.product_cd[I],df_receipt.amount[I]))
df_receipt[['sales_ymd','customer_id','product_cd','amount']].head(10)<jupyter_output><empty_output><jupyter_text>---> P-003: レシート明細のデータフレーム（df_receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、10件表示させよ。ただし、sales_ymdはsales_dateに項目名を変更しながら抽出すること。<jupyter_code>df_receipt[['sales_ymd','customer_id','product_cd','amount']].rename(columns={'sales_ymd':'sales_date'}).head(10)<jupyter_output><empty_output><jupyter_text>---> P-004: レシート明細のデータフレーム（df_receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ。> - 顧客ID（customer_id）が"CS018205000001"<jupyter_code>df_receipt[df_receipt['customer_id'] == 'CS018205000001'][['sales_ymd','customer_id','product_cd','amount']]<jupyter_output><empty_output><jupyter_text>---> P-005: レシート明細のデータフレーム（df_receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ。> - 顧客ID（customer_id）が"CS018205000001"> - 売上金額（amount）が1,000以上<jupyter_code>df_receipt[(df_receipt['amount'] >= 1000) & (df_receipt['customer_id'] == 'CS018205000001')][['sales_ymd','customer_id','product_cd','amount']]<jupyter_output><empty_output><jupyter_text>---> P-006: レシート明細データフレーム「df_receipt」から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上数量（quantity）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ。> - 顧客ID（customer_id）が"CS018205000001"> - 売上金額（amount）が1,000以上または売上数量（quantity）が5以上<jupyter_code>df_receipt[((df_receipt['amount'] >= 1000) | (df_receipt['quantity'] >= 5) )& (df_receipt['customer_id'] == 'CS018205000001')][['sales_ymd','customer_id','product_cd','quantity','amount']]<jupyter_output><empty_output><jupyter_text>---> P-007: レシート明細のデータフレーム（df_receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ。> - 顧客ID（customer_id）が"CS018205000001"> - 売上金額（amount）が1,000以上2,000以下<jupyter_code>df_receipt[((df_receipt['amount'] >= 1000) & (df_receipt['amount'] <= 2000) )& (df_receipt['customer_id'] == 'CS018205000001')][['sales_ymd','customer_id','product_cd','quantity','amount']]<jupyter_output><empty_output><jupyter_text>---> P-008: レシート明細のデータフレーム（df_receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ。> - 顧客ID（customer_id）が"CS018205000001"> - 商品コード（product_cd）が"P071401019"以外<jupyter_code>df_receipt[ (df_receipt['product_cd'] != 'P071401019') & (df_receipt['customer_id'] == 'CS018205000001')][['sales_ymd','customer_id','product_cd','quantity','amount']]<jupyter_output><empty_output><jupyter_text>---> P-009: 以下の処理において、出力結果を変えずにORをANDに書き換えよ。`df_store.query('not(prefecture_cd == "13" | floor_area > 900)')`<jupyter_code>df_store[(df_store['prefecture_cd'] != "13") & (df_store['floor_area'] <= 900)]<jupyter_output><empty_output><jupyter_text>---> P-010: 店舗データフレーム（df_store）から、店舗コード（store_cd）が"S14"で始まるものだけ全項目抽出し、10件だけ表示せよ。<jupyter_code>df_store[df_store['store_cd'].str.startswith('S14')].head(10)<jupyter_output><empty_output><jupyter_text>---> P-011: 顧客データフレーム（df_customer）から顧客ID（customer_id）の末尾が1のものだけ全項目抽出し、10件だけ表示せよ。<jupyter_code>df_customer[df_customer['customer_id'].str.endswith('1')].head(10)<jupyter_output><empty_output><jupyter_text>---> P-012: 店舗データフレーム（df_store）から横浜市の店舗だけ全項目表示せよ。<jupyter_code>df_store[df_store['address'].str.contains('横浜市')]<jupyter_output><empty_output><jupyter_text>---> P-013: 顧客データフレーム（df_customer）から、ステータスコード（status_cd）の先頭がアルファベットのA〜Fで始まるデータを全項目抽出し、10件だけ表示せよ。<jupyter_code>df_customer[df_customer['status_cd'].str.match('^[A-F]')].head(10)<jupyter_output><empty_output><jupyter_text>---> P-014: 顧客データフレーム（df_customer）から、ステータスコード（status_cd）の末尾が数字の1〜9で終わるデータを全項目抽出し、10件だけ表示せよ。<jupyter_code>df_customer[df_customer['status_cd'].str.match('.*[1-9]$')].head(10)<jupyter_output><empty_output><jupyter_text>---> P-015: 顧客データフレーム（df_customer）から、ステータスコード（status_cd）の先頭がアルファベットのA〜Fで始まり、末尾が数字の1〜9で終わるデータを全項目抽出し、10件だけ表示せよ。<jupyter_code>df_customer[df_customer['status_cd'].str.match('^[A-F].*[1-9]$')].head(10)<jupyter_output><empty_output><jupyter_text>---> P-016: 店舗データフレーム（df_store）から、電話番号（tel_no）が3桁-3桁-4桁のデータを全項目表示せよ。<jupyter_code>df_store[df_store['tel_no'].str.match('[0-9]{3}-[0-9]{3}-[0-9]{4}')]<jupyter_output><empty_output><jupyter_text>---> P-17: 顧客データフレーム（df_customer）を生年月日（birth_day）で高齢順にソートし、先頭10件を全項目表示せよ。<jupyter_code>df_customer.sort_values(by=['birth_day']).head(10)<jupyter_output><empty_output><jupyter_text>---> P-18: 顧客データフレーム（df_customer）を生年月日（birth_day）で若い順にソートし、先頭10件を全項目表示せよ。<jupyter_code>df_customer.sort_values(by=['birth_day'],  ascending=False).head(10)<jupyter_output><empty_output><jupyter_text>---> P-19: レシート明細データフレーム（df_receipt）に対し、1件あたりの売上金額（amount）が高い順にランクを付与し、先頭10件を抽出せよ。項目は顧客ID（customer_id）、売上金額（amount）、付与したランクを表示させること。なお、売上金額（amount）が等しい場合は同一順位を付与するものとする。<jupyter_code>rank = df_receipt['amount'].rank(method='min',ascending=False)
df_tmp=pd.concat([df_receipt[['customer_id','amount']],rank],axis=1)
df_tmp.columns=['customer_id','amount','rank']
df_tmp.sort_values('rank').head(10)<jupyter_output><empty_output><jupyter_text>---> P-020: レシート明細データフレーム（df_receipt）に対し、1件あたりの売上金額（amount）が高い順にランクを付与し、先頭10件を抽出せよ。項目は顧客ID（customer_id）、売上金額（amount）、付与したランクを表示させること。なお、売上金額（amount）が等しい場合でも別順位を付与すること。<jupyter_code>rank = df_receipt['amount'].rank(method='first',ascending=False)
df_tmp=pd.concat([df_receipt[['customer_id','amount']],rank],axis=1)
df_tmp.columns=['customer_id','amount','rank']
df_tmp.sort_values('rank').head(10)<jupyter_output><empty_output><jupyter_text>---> P-021: レシート明細データフレーム（df_receipt）に対し、件数をカウントせよ。<jupyter_code>len(df_receipt)<jupyter_output><empty_output><jupyter_text>---> P-022: レシート明細データフレーム（df_receipt）の顧客ID（customer_id）に対し、ユニーク件数をカウントせよ。<jupyter_code>len(df_receipt['customer_id'].unique())<jupyter_output><empty_output><jupyter_text>---> P-023: レシート明細データフレーム（df_receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）と売上数量（quantity）を合計せよ。<jupyter_code>df_receipt.groupby('store_cd').agg({'amount':'sum','quantity':'sum'})<jupyter_output><empty_output><jupyter_text>---> P-024: レシート明細データフレーム（df_receipt）に対し、顧客ID（customer_id）ごとに最も新しい売上日（sales_ymd）を求め、10件表示せよ。<jupyter_code># df_receipt['Date']=pd.to_datetime(df_receipt['sales_ymd'])
# df_receipt.sort_values('Date').max
df_receipt.groupby('customer_id').agg({'sales_ymd':max}).head(10)<jupyter_output><empty_output><jupyter_text>---> P-025: レシート明細データフレーム（df_receipt）に対し、顧客ID（customer_id）ごとに最も古い売上日（sales_ymd）を求め、10件表示せよ。<jupyter_code>df_receipt.groupby('customer_id').agg({'sales_ymd':min}).head(10)<jupyter_output><empty_output><jupyter_text>---> P-026: レシート明細データフレーム（df_receipt）に対し、顧客ID（customer_id）ごとに最も新しい売上日（sales_ymd）と古い売上日を求め、両者が異なるデータを10件表示せよ。<jupyter_code>df_tmp=df_receipt.groupby('customer_id').agg({'sales_ymd':[min,max]})
df_tmp[df_tmp[max] != df_tmp[min]].head(10)<jupyter_output><empty_output><jupyter_text>---> P-027: レシート明細データフレーム（df_receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の平均を計算し、降順でTOP5を表示せよ。<jupyter_code>df_receipt.groupby('store_cd').agg({'amount':'mean'}).sort_values('amount',ascending=False).head(5)<jupyter_output><empty_output><jupyter_text>---> P-028: レシート明細データフレーム（df_receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の中央値を計算し、降順でTOP5を表示せよ。<jupyter_code>df_receipt.groupby('store_cd').agg({'amount':'median'}).sort_values('amount', ascending=False).head(5)<jupyter_output><empty_output><jupyter_text>---> P-029: レシート明細データフレーム（df_receipt）に対し、店舗コード（store_cd）ごとに商品コード（product_cd）の最頻値を求めよ。<jupyter_code>df_receipt.groupby('store_cd').product_cd.apply(lambda x: x.mode()).reset_index()<jupyter_output><empty_output><jupyter_text>---> P-030: レシート明細データフレーム（df_receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の標本分散を計算し、降順でTOP5を表示せよ。<jupyter_code>df_receipt.groupby('store_cd').amount.var(ddof=0).reset_index().sort_values('amount',ascending=False).head(5)<jupyter_output><empty_output><jupyter_text>---> P-031: レシート明細データフレーム（df_receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の標本標準偏差を計算し、降順でTOP5を表示せよ。<jupyter_code>df_receipt.groupby('store_cd').amount.std(ddof=0).reset_index().sort_values('amount',ascending=False).head(5)<jupyter_output><empty_output><jupyter_text>---> P-032: レシート明細データフレーム（df_receipt）の売上金額（amount）について、25％刻みでパーセンタイル値を求めよ。<jupyter_code>df_receipt['amount'].quantile([0,0.25,0.5,0.75,1.0])<jupyter_output><empty_output><jupyter_text>---> P-033: レシート明細データフレーム（df_receipt）に対し、店舗コード（store_cd）ごとに売上金額（amount）の平均を計算し、330以上のものを抽出せよ。<jupyter_code>df_receipt.groupby('store_cd').agg({'amount':'mean'}).reset_index().query('amount >= 330')<jupyter_output><empty_output><jupyter_text>---> P-034: レシート明細データフレーム（df_receipt）に対し、顧客ID（customer_id）ごとに売上金額（amount）を合計して全顧客の平均を求めよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。<jupyter_code>df_receipt[~df_receipt['customer_id'].str.startswith('Z')].groupby('customer_id').amount.sum().mean()<jupyter_output><empty_output><jupyter_text>---> P-035: レシート明細データフレーム（df_receipt）に対し、顧客ID（customer_id）ごとに売上金額（amount）を合計して全顧客の平均を求め、平均以上に買い物をしている顧客を抽出せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。なお、データは10件だけ表示させれば良い。<jupyter_code>mean=df_receipt[~df_receipt['customer_id'].str.startswith('Z')].groupby('customer_id').amount.sum().mean()
tmp=df_receipt[~df_receipt['customer_id'].str.startswith('Z')].groupby('customer_id').amount.sum().reset_index()
tmp[tmp['amount'] >= mean].head(10)<jupyter_output><empty_output><jupyter_text>---> P-036: レシート明細データフレーム（df_receipt）と店舗データフレーム（df_store）を内部結合し、レシート明細データフレームの全項目と店舗データフレームの店舗名（store_name）を10件表示させよ。<jupyter_code>pd.merge(df_receipt,df_store[['store_cd','store_name']],how='inner',on='store_cd').head(10)<jupyter_output><empty_output><jupyter_text>---> P-037: 商品データフレーム（df_product）とカテゴリデータフレーム（df_category）を内部結合し、商品データフレームの全項目とカテゴリデータフレームの小区分名（category_small_name）を10件表示させよ。<jupyter_code>pd.merge(df_product,df_category[['category_small_name','category_small_cd']],how='inner',on='category_small_cd').head(10)<jupyter_output><empty_output><jupyter_text>---> P-038: 顧客データフレーム（df_customer）とレシート明細データフレーム（df_receipt）から、各顧客ごとの売上金額合計を求めよ。ただし、買い物の実績がない顧客については売上金額を0として表示させること。また、顧客は性別コード（gender_cd）が女性（1）であるものを対象とし、非会員（顧客IDが'Z'から始まるもの）は除外すること。なお、結果は10件だけ表示させれば良い。<jupyter_code>tmp_df_receipt=df_receipt.groupby('customer_id').amount.sum().reset_index()
tmp_df_customer=df_customer[(df_customer['gender_cd'] == "1") & (~df_customer['customer_id'].str.startswith('Z'))]
pd.merge( tmp_df_customer['customer_id'],tmp_df_receipt, how='left', on='customer_id').fillna(0).head(10)<jupyter_output><empty_output><jupyter_text>---> P-039: レシート明細データフレーム（df_receipt）から売上日数の多い顧客の上位20件と、売上金額合計の多い顧客の上位20件を抽出し、完全外部結合せよ。ただし、非会員（顧客IDが'Z'から始まるもの）は除外すること。<jupyter_code>df_sum=df_receipt.groupby('customer_id').amount.sum().reset_index()
df_sum=df_sum[~df_sum['customer_id'].str.startswith("Z")]
df_sum=df_sum.sort_values('amount',ascending=False).head(20)

# df_receipt[df_receipt['customer_id']=='CS040214000008'].sort_values('amount')
df_cnt=df_receipt[~df_receipt['customer_id'].str.startswith("Z")]
df_cnt=df_cnt.groupby('customer_id').sales_ymd.count().reset_index()
df_cnt=df_cnt.sort_values('sales_ymd',ascending=False).head(20)

pd.merge(df_sum,df_cnt,how='outer',on='customer_id')<jupyter_output><empty_output><jupyter_text>---> P-040: 全ての店舗と全ての商品を組み合わせると何件のデータとなるか調査したい。店舗（df_store）と商品（df_product）を直積した件数を計算せよ。<jupyter_code>df_product['key']=0
df_store['key']=0
len(df_store)*len(df_product)
# len(pd.merge(df_store,df_product, how='outer', on='key'))<jupyter_output><empty_output><jupyter_text>---> P-041: レシート明細データフレーム（df_receipt）の売上金額（amount）を日付（sales_ymd）ごとに集計し、前日からの売上金額増減を計算せよ。なお、計算結果は10件表示すればよい。<jupyter_code>base = df_receipt.groupby('sales_ymd').amount.sum().reset_index()
shift=base.shift().copy()
res = pd.concat([base,shift],axis=1)
res.columns = ['sales_ymd','amount','lag_sales_ymd','lag_amount']
# res
res['diff'] = res['amount'] - res['lag_amount']
res<jupyter_output><empty_output><jupyter_text>---> P-042: レシート明細データフレーム（df_receipt）の売上金額（amount）を日付（sales_ymd）ごとに集計し、各日付のデータに対し、１日前、２日前、３日前のデータを結合せよ。結果は10件表示すればよい。<jupyter_code>base=df_receipt.groupby('sales_ymd').amount.sum().reset_index()
one_day=base.shift()
two_day=one_day.shift()
three_day=two_day.shift()
res=pd.concat([base,one_day,two_day,three_day],axis=1).head(10)
res.dropna()<jupyter_output><empty_output><jupyter_text>---> P-043： レシート明細データフレーム（df_receipt）と顧客データフレーム（df_customer）を結合し、性別（gender）と年代（ageから計算）ごとに売上金額（amount）を合計した売上サマリデータフレーム（df_sales_summary）を作成せよ。性別は0が男性、1が女性、9が不明を表すものとする。>> ただし、項目構成は年代、女性の売上金額、男性の売上金額、性別不明の売上金額の4項目とすること（縦に年代、横に性別のクロス集計）。また、年代は10歳ごとの階級とすること。<jupyter_code>df_tmp = pd.merge(df_receipt, df_customer, how='inner',on='customer_id')
df_tmp['era']=df_tmp['age'].apply(lambda x: math.floor(x/10)*10)
summary = pd.pivot_table(df_tmp, index='era', columns='gender_cd', values='amount', aggfunc='sum').reset_index()
summary.columns=['era','male','female','unknown']
summary
# df_tmp.groupby('gender').amount.sum()
# df_tmp.groupby('age').amount.sum()<jupyter_output><empty_output><jupyter_text>---> P-044： 前設問で作成した売上サマリデータフレーム（df_sales_summary）は性別の売上を横持ちさせたものであった。このデータフレームから性別を縦持ちさせ、年代、性別コード、売上金額の3項目に変換せよ。ただし、性別コードは男性を'00'、女性を'01'、不明を'99'とする。<jupyter_code>summary.set_index('era').stack().reset_index().replace({'male':'00','female':'01','unknown':'99'}).rename(columns={'level_1':'gender',0:'amount'})<jupyter_output><empty_output><jupyter_text>---> P-045: 顧客データフレーム（df_customer）の生年月日（birth_day）は日付型（Date）でデータを保有している。これをYYYYMMDD形式の文字列に変換し、顧客ID（customer_id）とともに抽出せよ。データは10件を抽出すれば良い。<jupyter_code>pd.concat([df_customer['customer_id'],pd.to_datetime(df_customer['birth_day']).dt.strftime('%Y%m%d')]).head(10)<jupyter_output><empty_output><jupyter_text>---> P-046: 顧客データフレーム（df_customer）の申し込み日（application_date）はYYYYMMDD形式の文字列型でデータを保有している。これを日付型（dateやdatetime）に変換し、顧客ID（customer_id）とともに抽出せよ。データは10件を抽出すれば良い。<jupyter_code>date=pd.to_datetime(df_customer['application_date'])
pd.concat([df_customer['customer_id'],date],axis=1).head(10)<jupyter_output><empty_output><jupyter_text>---> P-047: レシート明細データフレーム（df_receipt）の売上日（sales_ymd）はYYYYMMDD形式の数値型でデータを保有している。これを日付型（dateやdatetime）に変換し、レシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。データは10件を抽出すれば良い。<jupyter_code>pd.concat([pd.to_datetime(df_receipt['sales_ymd']).dt.strftime('%Y-%m-%d'),df_receipt[['receipt_no','receipt_sub_no']]],axis=1).head(10)<jupyter_output><empty_output><jupyter_text>---> P-048: レシート明細データフレーム（df_receipt）の売上エポック秒（sales_epoch）は数値型のUNIX秒でデータを保有している。これを日付型（dateやdatetime）に変換し、レシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。データは10件を抽出すれば良い。<jupyter_code>pd.concat([pd.to_datetime(df_receipt['sales_epoch'],unit='s'),df_receipt[['receipt_no','receipt_sub_no']]],axis=1).head(10)<jupyter_output><empty_output><jupyter_text>---> P-049: レシート明細データフレーム（df_receipt）の売上エポック秒（sales_epoch）を日付型（timestamp型）に変換し、"年"だけ取り出してレシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。データは10件を抽出すれば良い。<jupyter_code>pd.concat([pd.to_datetime(df_receipt['sales_epoch'],unit='s').dt.strftime('%Y'),df_receipt[['receipt_no','receipt_sub_no']]],axis=1).head(10)<jupyter_output><empty_output><jupyter_text>---> P-050: レシート明細データフレーム（df_receipt）の売上エポック秒（sales_epoch）を日付型（timestamp型）に変換し、"月"だけ取り出してレシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。なお、"月"は0埋め2桁で取り出すこと。データは10件を抽出すれば良い。<jupyter_code>pd.concat([pd.to_datetime(df_receipt['sales_epoch'],unit='s').dt.strftime('%m'),df_receipt[['receipt_no','receipt_sub_no']]],axis=1).head(10)<jupyter_output><empty_output><jupyter_text>---> P-051: レシート明細データフレーム（df_receipt）の売上エポック秒（sales_epoch）を日付型（timestamp型）に変換し、"日"だけ取り出してレシート番号(receipt_no)、レシートサブ番号（receipt_sub_no）とともに抽出せよ。なお、"日"は0埋め2桁で取り出すこと。データは10件を抽出すれば良い。<jupyter_code>pd.concat([pd.to_datetime(df_receipt['sales_epoch'],unit='s').dt.strftime('%d'),df_receipt[['receipt_no','receipt_sub_no']]],axis=1).head(10)<jupyter_output><empty_output><jupyter_text>---> P-052: レシート明細データフレーム（df_receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計の上、売上金額合計に対して2000円以下を0、2000円超を1に2値化し、顧客ID、売上金額合計とともに10件表示せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。<jupyter_code>customers=df_receipt[~df_receipt['customer_id'].str.startswith('Z')]
res=customers.groupby('customer_id').amount.sum().reset_index()
pd.concat([res['customer_id'],res['amount'],res['amount'].apply(lambda x: 0 if x <= 2000 else 1)],axis=1).head(10)<jupyter_output><empty_output><jupyter_text>---> P-053: 顧客データフレーム（df_customer）の郵便番号（postal_cd）に対し、東京（先頭3桁が100〜209のもの）を1、それ以外のものを0に２値化せよ。さらにレシート明細データフレーム（df_receipt）と結合し、全期間において買い物実績のある顧客数を、作成した2値ごとにカウントせよ。<jupyter_code>tmp_customer=pd.concat([df_customer['postal_cd'].apply(lambda x: 1 if 100 <= int(x[0:3]) <= 209 else 0),df_customer['customer_id']],axis=1)
res=pd.merge(tmp_customer,df_receipt,how='inner',on='customer_id')
tmp=res[res['amount'] >= 0].groupby(['customer_id','postal_cd']).amount.sum().reset_index()
tmp['postal_cd'].value_counts().reset_index()<jupyter_output><empty_output><jupyter_text>---> P-054: 顧客データデータフレーム（df_customer）の住所（address）は、埼玉県、千葉県、東京都、神奈川県のいずれかとなっている。都道府県毎にコード値を作成し、顧客ID、住所とともに抽出せよ。値は埼玉県を11、千葉県を12、東京都を13、神奈川県を14とすること。結果は10件表示させれば良い。<jupyter_code># df_customer['address']
pd.concat([df_customer[['customer_id','address']],df_customer['address'].str[0:3].replace({'埼玉県':'11','千葉県':'12','東京都':'13','神奈川':'14'})],axis=1).head(10)<jupyter_output><empty_output><jupyter_text>---> P-055: レシート明細データフレーム（df_receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、その合計金額の四分位点を求めよ。その上で、顧客ごとの売上金額合計に対して以下の基準でカテゴリ値を作成し、顧客ID、売上金額と合計ともに表示せよ。カテゴリ値は上から順に1〜4とする。結果は10件表示させれば良い。>> - 最小値以上第一四分位未満> - 第一四分位以上第二四分位未満> - 第二四分位以上第三四分位未満> - 第三四分位以上<jupyter_code>res=df_receipt.groupby('customer_id').amount.sum().reset_index()
amount=res.describe()['amount']
min=amount['min']
pct25=amount['25%']
pct50=amount['50%']
pct75=amount['75%']
def category(x):
    if min <= x < pct25:
        return 1
    elif pct25 <= x < pct50:
        return 2
    elif pct50 <= x < pct75:
        return 3
    elif pct75 <= x:
        return 4
res['amount_pct_group']=res['amount'].map(lambda x: category(x))
res.head(10)<jupyter_output><empty_output><jupyter_text>---> P-056: 顧客データフレーム（df_customer）の年齢（age）をもとに10歳刻みで年代を算出し、顧客ID（customer_id）、生年月日（birth_day）とともに抽出せよ。ただし、60歳以上は全て60歳代とすること。年代を表すカテゴリ名は任意とする。先頭10件を表示させればよい。<jupyter_code>df_customer['generation']=df_customer['age'].apply(lambda x: (x//10)*10 if x < 60 else 60)
df_customer[['customer_id','birth_day','generation']].head(10)<jupyter_output><empty_output><jupyter_text>---> P-057: 前問題の抽出結果と性別（gender）を組み合わせ、新たに性別×年代の組み合わせを表すカテゴリデータを作成せよ。組み合わせを表すカテゴリの値は任意とする。先頭10件を表示させればよい。<jupyter_code>df_customer['gender_generation']=df_customer['gender_cd']+df_customer['generation'].astype('str')
df_customer[['customer_id','birth_day','generation','gender_generation']].head(10)<jupyter_output><empty_output><jupyter_text>---> P-058: 顧客データフレーム（df_customer）の性別コード（gender_cd）をダミー変数化し、顧客ID（customer_id）とともに抽出せよ。結果は10件表示させれば良い。<jupyter_code>pd.get_dummies(df_customer[['customer_id','gender_cd']], columns=['gender_cd']).head(10)<jupyter_output><empty_output><jupyter_text>---> P-059: レシート明細データフレーム（df_receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、合計した売上金額を平均0、標準偏差1に標準化して顧客ID、売上金額合計とともに表示せよ。標準化に使用する標準偏差は、不偏標準偏差と標本標準偏差のどちらでも良いものとする。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。結果は10件表示させれば良い。<jupyter_code>df_sales_amount=df_receipt[~df_receipt['customer_id'].str.startswith('Z')].groupby('customer_id').amount.sum().reset_index()
df_sales_amount['amount_ss']=preprocessing.scale(df_sales_amount['amount'])
df_sales_amount.head(10)<jupyter_output><empty_output><jupyter_text>---> P-060: レシート明細データフレーム（df_receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、合計した売上金額を最小値0、最大値1に正規化して顧客ID、売上金額合計とともに表示せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。結果は10件表示させれば良い。<jupyter_code>df_sales_amount['amount_mm']=preprocessing.minmax_scale(df_sales_amount['amount'])
df_sales_amount.head(10)<jupyter_output><empty_output><jupyter_text>---> P-061: レシート明細データフレーム（df_receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、合計した売上金額を常用対数化（底=10）して顧客ID、売上金額合計とともに表示せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。結果は10件表示させれば良い。<jupyter_code>df_sales_amount['log10']=np.log10(df_sales_amount['amount']+1)
df_sales_amount<jupyter_output><empty_output><jupyter_text>---> P-062: レシート明細データフレーム（df_receipt）の売上金額（amount）を顧客ID（customer_id）ごとに合計し、合計した売上金額を自然対数化(底=e）して顧客ID、売上金額合計とともに表示せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。結果は10件表示させれば良い。<jupyter_code>df_sales_amount['loge']=np.log(df_sales_amount['amount']+1)
df_sales_amount.head(10)<jupyter_output><empty_output><jupyter_text>---> P-063: 商品データフレーム（df_product）の単価（unit_price）と原価（unit_cost）から、各商品の利益額を算出せよ。結果は10件表示させれば良い。<jupyter_code>df_product['profit']=df_product['unit_price']-df_product['unit_cost']
df_product.head(10)<jupyter_output><empty_output><jupyter_text>---> P-064: 商品データフレーム（df_product）の単価（unit_price）と原価（unit_cost）から、各商品の利益率の全体平均を算出せよ。ただし、単価と原価にはNULLが存在することに注意せよ。<jupyter_code>df_tmp=df_product.copy()
df_tmp['profit_rate'] = df_tmp['profit']/df_tmp['unit_price']
df_tmp['profit_rate'].mean()<jupyter_output><empty_output><jupyter_text>---> P-065: 商品データフレーム（df_product）の各商品について、利益率が30%となる新たな単価を求めよ。ただし、1円未満は切り捨てること。そして結果を10件表示させ、利益率がおよそ30％付近であることを確認せよ。ただし、単価（unit_price）と原価（unit_cost）にはNULLが存在することに注意せよ。<jupyter_code>df_tmp=df_product.copy()
df_tmp['new_price'] = np.floor(df_tmp['unit_cost']/0.7)
df_tmp['new_profit_rate'] =  (df_tmp['new_price'] - df_tmp['unit_cost']) / df_tmp['new_price']
df_tmp.head(10)<jupyter_output><empty_output><jupyter_text>---> P-066: 商品データフレーム（df_product）の各商品について、利益率が30%となる新たな単価を求めよ。今回は、1円未満を四捨五入すること（0.5については偶数方向の丸めで良い）。そして結果を10件表示させ、利益率がおよそ30％付近であることを確認せよ。ただし、単価（unit_price）と原価（unit_cost）にはNULLが存在することに注意せよ。<jupyter_code>df_tmp=df_product.copy()
df_tmp['new_price'] = np.round(df_tmp['unit_cost']/0.7)
df_tmp['new_profit_rate'] =  (df_tmp['new_price'] - df_tmp['unit_cost']) / df_tmp['new_price']
df_tmp.head(10)<jupyter_output><empty_output><jupyter_text>---> P-067: 商品データフレーム（df_product）の各商品について、利益率が30%となる新たな単価を求めよ。今回は、1円未満を切り上げること。そして結果を10件表示させ、利益率がおよそ30％付近であることを確認せよ。ただし、単価（unit_price）と原価（unit_cost）にはNULLが存在することに注意せよ。<jupyter_code>df_tmp=df_product.copy()
df_tmp['new_price'] = np.ceil(df_tmp['unit_cost']/0.7)
df_tmp['new_profit_rate'] =  (df_tmp['new_price'] - df_tmp['unit_cost']) / df_tmp['new_price']
df_tmp.head(10)<jupyter_output><empty_output><jupyter_text>---> P-068: 商品データフレーム（df_product）の各商品について、消費税率10%の税込み金額を求めよ。 1円未満の端数は切り捨てとし、結果は10件表示すれば良い。ただし、単価（unit_price）にはNULLが存在することに注意せよ。<jupyter_code>df_tmp = df_product.copy()
df_tmp['price_tax'] = np.floor(df_tmp['unit_price']*1.1)
df_tmp.head(10)<jupyter_output><empty_output><jupyter_text>---> P-069: レシート明細データフレーム（df_receipt）と商品データフレーム（df_product）を結合し、顧客毎に全商品の売上金額合計と、カテゴリ大区分（category_major_cd）が"07"（瓶詰缶詰）の売上金額合計を計算の上、両者の比率を求めよ。抽出対象はカテゴリ大区分"07"（瓶詰缶詰）の購入実績がある顧客のみとし、結果は10件表示させればよい。<jupyter_code>pd.merge(df_receipt,df_product,how='inner',on='product_cd')<jupyter_output><empty_output><jupyter_text>---> P-070: レシート明細データフレーム（df_receipt）の売上日（sales_ymd）に対し、顧客データフレーム（df_customer）の会員申込日（application_date）からの経過日数を計算し、顧客ID（customer_id）、売上日、会員申込日とともに表示せよ。結果は10件表示させれば良い（なお、sales_ymdは数値、application_dateは文字列でデータを保持している点に注意）。<jupyter_code>tmp_new=pd.merge(df_receipt[['customer_id','sales_ymd']],df_customer[['customer_id','application_date']],how='inner',on='customer_id')
tmp_new = tmp_new.drop_duplicates()
tmp_new['sales_ymd']=pd.to_datetime(tmp_new['sales_ymd'].astype('str'))
tmp_new['application_date']=pd.to_datetime(tmp_new['application_date'])
tmp_new['elapsed']= tmp_new['sales_ymd'] - tmp_new['application_date']
tmp_new<jupyter_output><empty_output><jupyter_text>---> P-071: レシート明細データフレーム（df_receipt）の売上日（sales_ymd）に対し、顧客データフレーム（df_customer）の会員申込日（application_date）からの経過月数を計算し、顧客ID（customer_id）、売上日、会員申込日とともに表示せよ。結果は10件表示させれば良い（なお、sales_ymdは数値、application_dateは文字列でデータを保持している点に注意）。1ヶ月未満は切り捨てること。<jupyter_code>tmp_new=pd.merge(df_receipt[['customer_id','sales_ymd']],df_customer[['customer_id','application_date']],how='inner',on='customer_id')
tmp_new = tmp_new.drop_duplicates()
tmp_new['sales_ymd']=pd.to_datetime(tmp_new['sales_ymd'].astype('str'))
tmp_new['application_date']=pd.to_datetime(tmp_new['application_date'])
tmp_new['elapsed_month']=tmp_new[['sales_ymd', 'application_date']].apply(lambda x: 
                                    relativedelta(x[0], x[1]).years * 12 + relativedelta(x[0], x[1]).months, axis=1)
tmp_new.sort_values('customer_id').head(10)<jupyter_output><empty_output><jupyter_text>---> P-072: レシート明細データフレーム（df_receipt）の売上日（sales_ymd）に対し、顧客データフレーム（df_customer）の会員申込日（application_date）からの経過年数を計算し、顧客ID（customer_id）、売上日、会員申込日とともに表示せよ。結果は10件表示させれば良い。（なお、sales_ymdは数値、application_dateは文字列でデータを保持している点に注意）。1年未満は切り捨てること。<jupyter_code>tmp_new=pd.merge(df_receipt[['customer_id','sales_ymd']],df_customer[['customer_id','application_date']],how='inner',on='customer_id')
tmp_new = tmp_new.drop_duplicates()
tmp_new['sales_ymd']=pd.to_datetime(tmp_new['sales_ymd'].astype('str'))
tmp_new['application_date']=pd.to_datetime(tmp_new['application_date'])
tmp_new['elapsed_month']=tmp_new[['sales_ymd', 'application_date']].apply(lambda x: 
                                    relativedelta(x[0], x[1]).years, axis=1)
tmp_new.sort_values('customer_id').head(10)<jupyter_output><empty_output><jupyter_text>---> P-073: レシート明細データフレーム（df_receipt）の売上日（sales_ymd）に対し、顧客データフレーム（df_customer）の会員申込日（application_date）からのエポック秒による経過時間を計算し、顧客ID（customer_id）、売上日、会員申込日とともに表示せよ。結果は10件表示させれば良い（なお、sales_ymdは数値、application_dateは文字列でデータを保持している点に注意）。なお、時間情報は保有していないため各日付は0時0分0秒を表すものとする。<jupyter_code>tmp_new=pd.merge(df_receipt[['customer_id','sales_ymd']],df_customer[['customer_id','application_date']],how='inner',on='customer_id')
tmp_new = tmp_new.drop_duplicates()
tmp_new['sales_ymd']=pd.to_datetime(tmp_new['sales_ymd'].astype('str'))
tmp_new['application_date']=pd.to_datetime(tmp_new['application_date'])
tmp_new['elapsed_date'] = (tmp_new['sales_ymd'].astype(np.int64) / 10**9) - (tmp_new['application_date'].astype(np.int64) / 10**9)

tmp_new.sort_values('customer_id').head(10)<jupyter_output><empty_output><jupyter_text>---> P-074: レシート明細データフレーム（df_receipt）の売上日（sales_ymd）に対し、当該週の月曜日からの経過日数を計算し、売上日、当該週の月曜日付とともに表示せよ。結果は10件表示させれば良い（なお、sales_ymdは数値でデータを保持している点に注意）。<jupyter_code>df_tmp = df_receipt[['customer_id', 'sales_ymd']]
df_tmp = df_tmp.drop_duplicates()
df_tmp['sales_ymd'] = pd.to_datetime(df_tmp['sales_ymd'].astype('str'))
hoge=df_tmp['sales_ymd'] - relativedelta(days=df_tmp['sales_ymd'].weekday())
hoge

# df_tmp['monday'] = df_tmp['sales_ymd'].apply(lambda x: x - relativedelta(days=x.weekday()))
# df_tmp['elapsed_weekday'] = df_tmp['sales_ymd'] - df_tmp['monday']
# df_tmp.head(10)<jupyter_output><empty_output><jupyter_text>---> P-075: 顧客データフレーム（df_customer）からランダムに1%のデータを抽出し、先頭から10件データを抽出せよ。<jupyter_code>df_customer.sample(frac=0.01).head(10)<jupyter_output><empty_output><jupyter_text>---> P-076: 顧客データフレーム（df_customer）から性別（gender_cd）の割合に基づきランダムに10%のデータを層化抽出データし、性別ごとに件数を集計せよ。<jupyter_code>_, df_tmp = train_test_split(df_customer, test_size=0.1, stratify=df_customer['gender_cd'])
df_tmp.groupby('gender_cd').agg({'customer_id' : 'count'}).reset_index()<jupyter_output><empty_output><jupyter_text>---> P-077: レシート明細データフレーム（df_receipt）の売上金額（amount）を顧客単位に合計し、合計した売上金額の外れ値を抽出せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。なお、ここでは外れ値を平均から3σ以上離れたものとする。結果は10件表示させれば良い。<jupyter_code>df_tmp=df_receipt[~df_receipt['customer_id'].str.startswith('Z')].groupby('customer_id').amount.sum().reset_index()
df_sales_amount['amount_ss'] = preprocessing.scale(df_tmp['amount'])
result=df_sales_amount[abs(df_sales_amount['amount_ss']) >= 3]
result[['customer_id','amount','amount_ss']]<jupyter_output><empty_output><jupyter_text>---> P-078: レシート明細データフレーム（df_receipt）の売上金額（amount）を顧客単位に合計し、合計した売上金額の外れ値を抽出せよ。ただし、顧客IDが"Z"から始まるのものは非会員を表すため、除外して計算すること。なお、ここでは外れ値を第一四分位と第三四分位の差であるIQRを用いて、「第一四分位数-1.5×IQR」よりも下回るもの、または「第三四分位数+1.5×IQR」を超えるものとする。結果は10件表示させれば良い。<jupyter_code>df_tmp=df_receipt[~df_receipt['customer_id'].str.startswith('Z')].groupby('customer_id').amount.sum().reset_index()
pct75=np.percentile(df_tmp['amount'], q=75)
pct25=np.percentile(df_tmp['amount'], q=25)
iqr=pct75-pct25
amount_low = pct25 - (iqr*1.5)
amount_hight = pct75 + (iqr*1.5)
df_tmp[(df_tmp['amount'] < amount_low) | (amount_hight < df_tmp['amount'])]<jupyter_output><empty_output><jupyter_text>---> P-079: 商品データフレーム（df_product）の各項目に対し、欠損数を確認せよ。<jupyter_code>df_product.isnull().sum()<jupyter_output><empty_output><jupyter_text>---> P-080: 商品データフレーム（df_product）のいずれかの項目に欠損が発生しているレコードを全て削除した新たなdf_product_1を作成せよ。なお、削除前後の件数を表示させ、前設問で確認した件数だけ減少していることも確認すること。<jupyter_code>tmp_df = df_product.copy()
print('before:',len(tmp_df))
tmp_df.dropna(inplace=True)
print('after:',len(tmp_df))<jupyter_output><empty_output><jupyter_text>---> P-081: 単価（unit_price）と原価（unit_cost）の欠損値について、それぞれの平均値で補完した新たなdf_product_2を作成せよ。なお、平均値について1円未満は四捨五入とし、0.5については偶数寄せでかまわない。補完実施後、各項目について欠損が生じていないことも確認すること。<jupyter_code>df_product_2=df_product.copy()
df_product_2['unit_price']=df_product_2['unit_price'].fillna(np.round(df_product['unit_price'].mean()))
df_product_2['unit_cost']=df_product_2['unit_cost'].fillna(np.round(df_product['unit_cost'].mean()))
df_product_2.isnull().sum()<jupyter_output><empty_output><jupyter_text>---> P-082: 単価（unit_price）と原価（unit_cost）の欠損値について、それぞれの中央値で補完した新たなdf_product_3を作成せよ。なお、中央値について1円未満は四捨五入とし、0.5については偶数寄せでかまわない。補完実施後、各項目について欠損が生じていないことも確認すること。<jupyter_code># df_product.isnull().sum()
df_product_3=df_product.fillna({'unit_price':np.round(df_product['unit_price'].median()),
                                'unit_cost':np.round(df_product['unit_cost'].median())})
df_product_3.isnull().sum()<jupyter_output><empty_output><jupyter_text>---> P-083: 単価（unit_price）と原価（unit_cost）の欠損値について、各商品の小区分（category_small_cd）ごとに算出した中央値で補完した新たなdf_product_4を作成せよ。なお、中央値について1円未満は四捨五入とし、0.5については偶数寄せでかまわない。補完実施後、各項目について欠損が生じていないことも確認すること。<jupyter_code>df_tmp = df_product.groupby('category_small_cd').agg({'unit_price':'median', 'unit_cost':'median'}).reset_index()
df_tmp.columns = ['category_small_cd', 'median_price', 'median_cost']

# df_product_4 = pd.merge(df_product, df_tmp, how='inner', on='category_small_cd')
df_product_5 = pd.merge(df_product, df_tmp, how='inner', on='category_small_cd')


df_product_5['unit_price'] = df_product_5['unit_price'].fillna(np.round(df_product_5['median_price']))
df_product_5['unit_cost'] = df_product_5['unit_cost'].fillna(np.round(df_product_5['median_cost']))
# df_product_4['unit_price'] = df_product_4[['unit_price', 'median_price']]. \
#                     apply(lambda x: np.round(x[1]) if np.isnan(x[0]) else x[0], axis=1)
# df_product_4['unit_cost'] = df_product_4[['unit_cost', 'median_cost']]. \
#                     apply(lambda x: np.round(x[1]) if np.isnan(x[0]) else x[0], axis=1)
# df_product_4.reset_index()
# print(df_product_4.iloc[159])
# print(df_product_5.iloc[159])
# ans=pd.DataFrame().reindex_like(df_product_4)
# ans['unit_price']=df_product_4['unit_price'] - df_product_5['unit_price']
# ans['unit_cost']=df_product_4['unit_cost'] - df_product_5['unit_cost']
# ans[['unit_price','unit_cost']][~(ans['unit_price'] == 0.0) |~(ans['unit_cost'] == 0.0) ]
df_product_5.isnull().sum()<jupyter_output><empty_output><jupyter_text>---> P-084: 顧客データフレーム（df_customer）の全顧客に対し、全期間の売上金額に占める2019年売上金額の割合を計算せよ。ただし、販売実績のない場合は0として扱うこと。そして計算した割合が0超のものを抽出せよ。 結果は10件表示させれば良い。また、作成したデータにNAやNANが存在しないことを確認せよ。<jupyter_code>tmp_df=df_receipt.query('20190101 <= sales_ymd <= 20191231')
tmp_df_1=pd.merge(df_customer['customer_id'], tmp_df[['customer_id','amount']],how='left',on='customer_id').groupby('customer_id').sum().reset_index().rename(columns={'amount':'amount_2019'})
tmp_df_2=pd.merge(df_customer['customer_id'], df_receipt[['customer_id','amount']],how='left',on='customer_id').groupby('customer_id').sum().reset_index()

df_tmp=pd.merge(tmp_df_1,tmp_df_2,how='inner',on='customer_id')
df_tmp['amount']=df_tmp['amount'].fillna(0)
df_tmp['amount_2019']=df_tmp['amount_2019'].fillna(0)
# df_tmp.isnull().any()
# df_tmp.isna().any()
df_tmp['amount_rate']=df_tmp['amount_2019']/df_tmp['amount']
df_tmp['amount_rate']=df_tmp['amount_rate'].fillna(0)
df_tmp[df_tmp['amount_rate'] > 0].head(10)<jupyter_output><empty_output><jupyter_text>---> P-085: 顧客データフレーム（df_customer）の全顧客に対し、郵便番号（postal_cd）を用いて経度緯度変換用データフレーム（df_geocode）を紐付け、新たなdf_customer_1を作成せよ。ただし、複数紐づく場合は経度（longitude）、緯度（latitude）それぞれ平均を算出すること。<jupyter_code>tmp_df_customer=pd.merge(df_customer[['customer_id','postal_cd']],df_geocode[['postal_cd','longitude','latitude']],how='inner',on='postal_cd')
df_customer_1=tmp_df_customer.groupby('customer_id').agg({'longitude':'mean','latitude':'mean'}).reset_index().rename(columns={'latitude':'m_latitude','longitude':'m_longitude'})
df_customer_1=pd.merge(df_customer,df_customer_1,how='inner',on='customer_id')
df_customer_1.head(3)<jupyter_output><empty_output><jupyter_text>---> P-086: 前設問で作成した緯度経度つき顧客データフレーム（df_customer_1）に対し、申込み店舗コード（application_store_cd）をキーに店舗データフレーム（df_store）と結合せよ。そして申込み店舗の緯度（latitude）・経度情報（longitude)と顧客の緯度・経度を用いて距離（km）を求め、顧客ID（customer_id）、顧客住所（address）、店舗住所（address）とともに表示せよ。計算式は簡易式で良いものとするが、その他精度の高い方式を利用したライブラリを利用してもかまわない。結果は10件表示すれば良い。 $$緯度（ラジアン）：\phi \\経度（ラジアン）：\lambda \\距離L = 6371 * arccos(sin \phi_1 * sin \phi_2+ cos \phi_1 * cos \phi_2 * cos(\lambda_1 − \lambda_2))$$ ---> P-087:  顧客データフレーム（df_customer）では、異なる店舗での申込みなどにより同一顧客が複数登録されている。名前（customer_name）と郵便番号（postal_cd）が同じ顧客は同一顧客とみなし、1顧客1レコードとなるように名寄せした名寄顧客データフレーム（df_customer_u）を作成せよ。ただし、同一顧客に対しては売上金額合計が最も高いものを残すものとし、売上金額合計が同一もしくは売上実績の無い顧客については顧客ID（customer_id）の番号が小さいものを残すこととする。<jupyter_code>tmp_receipt=df_receipt.groupby('customer_id').amount.sum().reset_index()
df_customer_u=pd.merge(df_customer,tmp_receipt,how='left',on='customer_id').sort_values(['amount','customer_id'],ascending=[False,True])
df_customer_u.drop_duplicates(subset=['customer_name','postal_cd'],keep='first',inplace=True)
#len(df_customer) - len(df_customer_u)
df_customer_u<jupyter_output><empty_output><jupyter_text>---> P-088: 前設問で作成したデータを元に、顧客データフレームに統合名寄IDを付与したデータフレーム（df_customer_n）を作成せよ。ただし、統合名寄IDは以下の仕様で付与するものとする。>> - 重複していない顧客：顧客ID（customer_id）を設定> - 重複している顧客：前設問で抽出したレコードの顧客IDを設定<jupyter_code>df_customer_n = pd.merge(df_customer, df_customer_u[['customer_name', 'postal_cd', 'customer_id']],
                        how='inner', on =['customer_name', 'postal_cd'])
df_customer_n.rename(columns={'customer_id_x':'customer_id', 'customer_id_y':'integration_id'}, inplace=True)

df_customer_n<jupyter_output><empty_output><jupyter_text>---> P-閑話: df_customer_1, df_customer_nは使わないので削除する。<jupyter_code>del df_customer_1
del df_customer_n<jupyter_output><empty_output><jupyter_text>---> P-089: 売上実績のある顧客に対し、予測モデル構築のため学習用データとテスト用データに分割したい。それぞれ8:2の割合でランダムにデータを分割せよ。<jupyter_code>df_tmp=pd.merge(df_customer, df_receipt['customer_id'], how='inner', on='customer_id')
df_train, df_test = train_test_split(df_tmp, test_size=0.2)
print('学習データ割合: ', len(df_train) / len(df_tmp))
print('テストデータ割合: ', len(df_test) / len(df_tmp))<jupyter_output>学習データ割合:  0.7999908650771901
テストデータ割合:  0.2000091349228099
{'fraction_non_alphanumeric': 0.17595331795358937, 'fraction_numerical': 0.031347536979237346, 'mean_word_length': 16.53736316039981, 'pattern_counts': {'":': 0, '<': 378, '<?xml version=': 0, '>': 481, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '90', 'dir': 'jupyter-structured-clean-dedup', 'id': '367508', 'license': 'MIT', 'n_tokens_mistral': 21653, 'n_tokens_neox': 20743, 'n_words': 743, 'path': 'docker/work/preprocess_knock_Python.ipynb', 'repo_name': 'jackfrostwillbeking/100knocks-preprocess', 'size': '36846'}