数据科学 100 道练习题 91~100#
import polars as pl
from helper.polars import load_100knocks_data
pl.Config.set_fmt_str_lengths(100)
df_customer, df_category, df_product, df_receipt, df_store, df_geocode = load_100knocks_data()
P-091#
对顾客数据(df_customer)的各顾客，以使有销售实绩的顾客数与无销售实绩的顾客数成为 1:1 的比例做欠采样抽取。
df_tmp = (
df_customer
.join(
df_receipt
.group_by(pl.col.customer_id)
.agg(sum_amount=pl.col.amount.sum()),
on='customer_id',
how='left'
)
)
flag = pl.col.sum_amount.is_null().cast(pl.UInt8)
df_result = df_tmp.filter(
pl.int_range(pl.len()).shuffle().over(flag) < pl.len().over(flag).min()
)
(
df_result
.group_by(flag)
.agg(count=pl.col.customer_id.count())
)
shape: (2, 2)
| sum_amount | count |
|---|---|
| u8 | u32 |
| 0 | 8306 |
| 1 | 8306 |
P-092#
将顾客数据(df_customer)中与性别相关的部分规范化为第三范式。
df_gender_std = df_customer.select('gender_cd', 'gender').unique()
df_gender_std
shape: (3, 2)
| gender_cd | gender |
|---|---|
| str | str |
| "0" | "男性" |
| "9" | "不明" |
| "1" | "女性" |
df_customer_std = df_customer.select(
'customer_id',
'customer_name',
'gender_cd',
'birth_day',
'age',
'postal_cd',
'application_store_cd',
'application_date',
'status_cd'
)
df_customer_std.head()
shape: (5, 9)
| customer_id | customer_name | gender_cd | birth_day | age | postal_cd | application_store_cd | application_date | status_cd |
|---|---|---|---|---|---|---|---|---|
| str | str | str | str | i64 | str | str | i64 | str |
| "CS021313000114" | "大野 あや子" | "1" | "1981-04-29" | 37 | "259-1113" | "S14021" | 20150905 | "0-00000000-0" |
| "CS037613000071" | "六角 雅彦" | "9" | "1952-04-01" | 66 | "136-0076" | "S13037" | 20150414 | "0-00000000-0" |
| "CS031415000172" | "宇多田 貴美子" | "1" | "1976-10-04" | 42 | "151-0053" | "S13031" | 20150529 | "D-20100325-C" |
| "CS028811000001" | "堀井 かおり" | "1" | "1933-03-27" | 86 | "245-0016" | "S14028" | 20160115 | "0-00000000-0" |
| "CS001215000145" | "田崎 美紀" | "1" | "1995-03-29" | 24 | "144-0055" | "S13001" | 20170605 | "6-20090929-2" |
P-093#
商品数据(df_product)只保存各类别的代码值，不保存类别名称。将其与类别数据(df_category)结合做反规范化，创建包含类别名称的新商品数据。
df_product_full = (
df_product.join(
df_category,
on='category_small_cd',
how='inner'
)
.select(
'product_cd',
'category_major_cd',
'category_major_name',
'category_medium_cd',
'category_medium_name',
'category_small_cd',
'category_small_name',
'unit_price',
'unit_cost'
)
)
df_product_full.head()
shape: (5, 9)
| product_cd | category_major_cd | category_major_name | category_medium_cd | category_medium_name | category_small_cd | category_small_name | unit_price | unit_cost |
|---|---|---|---|---|---|---|---|---|
| str | str | str | str | str | str | str | i64 | i64 |
| "P040101001" | "04" | "惣菜" | "0401" | "御飯類" | "040101" | "弁当類" | 198 | 149 |
| "P040101002" | "04" | "惣菜" | "0401" | "御飯類" | "040101" | "弁当類" | 218 | 164 |
| "P040101003" | "04" | "惣菜" | "0401" | "御飯類" | "040101" | "弁当類" | 230 | 173 |
| "P040101004" | "04" | "惣菜" | "0401" | "御飯類" | "040101" | "弁当類" | 248 | 186 |
| "P040101005" | "04" | "惣菜" | "0401" | "御飯類" | "040101" | "弁当類" | 268 | 201 |
P-094#
将 093 创建的带类别名称的商品数据按以下规格输出为文件。
文件格式
有无表头
字符编码
CSV(逗号分隔)
有
UTF-8
文件输出路径如下
输出路径
./data
df_product_full.write_csv('./data/product_full.csv')
P-095#
将 093 创建的带类别名称的商品数据按以下规格输出为文件。
文件格式
有无表头
字符编码
CSV(逗号分隔)
有
CP932
文件输出路径如下。
输出路径
./data
import io
buf = io.BytesIO()
df_product_full.write_csv(buf)
with open('./data/product_full_cp932.csv', 'wb') as f:
f.write(buf.getvalue().decode('utf-8').encode('cp932'))
P-096#
将 093 创建的带类别名称的商品数据按以下规格输出为文件。
文件格式
有无表头
字符编码
CSV(逗号分隔)
无
UTF-8
文件输出路径如下。
输出路径
./data
df_product_full.write_csv('./data/product_full_nohead.csv', include_header=False)
P-097#
读取 094 创建的以下格式文件，显示 3 条数据，确认已正确读入。
文件格式
有无表头
字符编码
CSV(逗号分隔)
有
UTF-8
pl.read_csv('./data/product_full.csv').head()
shape: (5, 9)
| product_cd | category_major_cd | category_major_name | category_medium_cd | category_medium_name | category_small_cd | category_small_name | unit_price | unit_cost |
|---|---|---|---|---|---|---|---|---|
| str | i64 | str | i64 | str | i64 | str | i64 | i64 |
| "P040101001" | 4 | "惣菜" | 401 | "御飯類" | 40101 | "弁当類" | 198 | 149 |
| "P040101002" | 4 | "惣菜" | 401 | "御飯類" | 40101 | "弁当類" | 218 | 164 |
| "P040101003" | 4 | "惣菜" | 401 | "御飯類" | 40101 | "弁当類" | 230 | 173 |
| "P040101004" | 4 | "惣菜" | 401 | "御飯類" | 40101 | "弁当類" | 248 | 186 |
| "P040101005" | 4 | "惣菜" | 401 | "御飯類" | 40101 | "弁当類" | 268 | 201 |
P-098#
读取 096 创建的以下格式文件，显示 3 条数据，确认已正确读入。
文件格式
有无表头
字符编码
CSV(逗号分隔)
无
UTF-8
df_product_full.columns
['product_cd',
'category_major_cd',
'category_major_name',
'category_medium_cd',
'category_medium_name',
'category_small_cd',
'category_small_name',
'unit_price',
'unit_cost']
cols = ['product_cd',
'category_major_cd',
'category_major_name',
'category_medium_cd',
'category_medium_name',
'category_small_cd',
'category_small_name',
'unit_price',
'unit_cost']
pl.read_csv('./data/product_full_nohead.csv', has_header=False, new_columns=cols).head()
shape: (5, 9)
| product_cd | category_major_cd | category_major_name | category_medium_cd | category_medium_name | category_small_cd | category_small_name | unit_price | unit_cost |
|---|---|---|---|---|---|---|---|---|
| str | i64 | str | i64 | str | i64 | str | i64 | i64 |
| "P040101001" | 4 | "惣菜" | 401 | "御飯類" | 40101 | "弁当類" | 198 | 149 |
| "P040101002" | 4 | "惣菜" | 401 | "御飯類" | 40101 | "弁当類" | 218 | 164 |
| "P040101003" | 4 | "惣菜" | 401 | "御飯類" | 40101 | "弁当類" | 230 | 173 |
| "P040101004" | 4 | "惣菜" | 401 | "御飯類" | 40101 | "弁当類" | 248 | 186 |
| "P040101005" | 4 | "惣菜" | 401 | "御飯類" | 40101 | "弁当類" | 268 | 201 |
P-099#
将 093 创建的带类别名称的商品数据按以下规格输出为文件。
文件格式
有无表头
字符编码
TSV(制表符分隔)
有
UTF-8
文件输出路径如下
输出路径
./data
df_product_full.write_csv('./data/product_full.tsv', separator='\t')
P-100#
读取 099 创建的以下格式文件，显示 3 条数据，确认已正确读入。
文件格式
有无表头
字符编码
TSV(制表符分隔)
有
UTF-8
pl.read_csv('./data/product_full.tsv', separator='\t').head()
shape: (5, 9)
| product_cd | category_major_cd | category_major_name | category_medium_cd | category_medium_name | category_small_cd | category_small_name | unit_price | unit_cost |
|---|---|---|---|---|---|---|---|---|
| str | i64 | str | i64 | str | i64 | str | i64 | i64 |
| "P040101001" | 4 | "惣菜" | 401 | "御飯類" | 40101 | "弁当類" | 198 | 149 |
| "P040101002" | 4 | "惣菜" | 401 | "御飯類" | 40101 | "弁当類" | 218 | 164 |
| "P040101003" | 4 | "惣菜" | 401 | "御飯類" | 40101 | "弁当類" | 230 | 173 |
| "P040101004" | 4 | "惣菜" | 401 | "御飯類" | 40101 | "弁当類" | 248 | 186 |
| "P040101005" | 4 | "惣菜" | 401 | "御飯類" | 40101 | "弁当類" | 268 | 201 |