数据科学 100 道练习 01~10

数据科学 100 道练习 01~10#

前言#

请先从 100knocks-preprocess 下载示例数据。

首先执行下面的单元格。
该单元格导入所需的库，并从数据库(PostgreSQL)读取数据。
pandas 等预计会用到的库已在下面的单元格中导入。
需要其他库时请自行安装，也可以用 !pip install 库名 安装。
处理可以分成多次完成。
姓名、地址等均为虚构数据，并非真实存在。

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-001#

从收据明细数据(df_receipt)中显示全部字段的前 10 条，目视确认数据包含哪些内容。

df_receipt.head(5)

shape: (5, 9)

sales_ymd	sales_epoch	store_cd	receipt_no	receipt_sub_no	customer_id	product_cd	quantity	amount
i64	i64	str	i64	i64	str	str	i64	i64
20181103	1541203200	"S14006"	112	1	"CS006214000001"	"P070305012"	1	158
20181118	1542499200	"S13008"	1132	2	"CS008415000097"	"P070701017"	1	81
20170712	1499817600	"S14028"	1102	1	"CS028414000014"	"P060101005"	1	170
20190205	1549324800	"S14042"	1132	1	"ZZ000000000000"	"P050301001"	1	25
20180821	1534809600	"S14025"	1102	2	"CS025415000050"	"P060102007"	1	90

P-002#

从收据明细数据(df_receipt)中，按销售年月日(sales_ymd)、顾客 ID(customer_id)、商品代码(product_cd)、销售金额(amount)的顺序指定列，显示 10 条。

(
df_receipt
.select('sales_ymd', 'customer_id', 'product_cd', 'amount')
.head(5)
)

shape: (5, 4)

sales_ymd	customer_id	product_cd	amount
i64	str	str	i64
20181103	"CS006214000001"	"P070305012"	158
20181118	"CS008415000097"	"P070701017"	81
20170712	"CS028414000014"	"P060101005"	170
20190205	"ZZ000000000000"	"P050301001"	25
20180821	"CS025415000050"	"P060102007"	90

P-003#

从收据明细数据(df_receipt)中，按销售年月日(sales_ymd)、顾客 ID(customer_id)、商品代码(product_cd)、销售金额(amount)的顺序指定列，显示 10 条。其中要把 sales_ymd 这一项改名为 sales_date 后再抽取。

(
df_receipt
.select(
    pl.col('sales_ymd').alias('sales_date'), 
    'customer_id', 
    'product_cd', 
    'amount')
.head(5)
)

shape: (5, 4)

sales_date	customer_id	product_cd	amount
i64	str	str	i64
20181103	"CS006214000001"	"P070305012"	158
20181118	"CS008415000097"	"P070701017"	81
20170712	"CS028414000014"	"P060101005"	170
20190205	"ZZ000000000000"	"P050301001"	25
20180821	"CS025415000050"	"P060102007"	90

(
df_receipt
.select('sales_ymd', 'customer_id', 'product_cd', 'amount')
.rename({'sales_ymd':'sales_date'})
.head(5)
)

shape: (5, 4)

sales_date	customer_id	product_cd	amount
i64	str	str	i64
20181103	"CS006214000001"	"P070305012"	158
20181118	"CS008415000097"	"P070701017"	81
20170712	"CS028414000014"	"P060101005"	170
20190205	"ZZ000000000000"	"P050301001"	25
20180821	"CS025415000050"	"P060102007"	90

P-004#

从收据明细数据(df_receipt)中，按销售日(sales_ymd)、顾客 ID(customer_id)、商品代码(product_cd)、销售金额(amount)的顺序指定列，抽取满足下列条件的数据。

顾客 ID(customer_id)为 "CS018205000001"

(
df_receipt
.filter(pl.col.customer_id == 'CS018205000001')
.select('sales_ymd', 'customer_id', 'product_cd', 'amount')
.head(5)
)

shape: (5, 4)

sales_ymd	customer_id	product_cd	amount
i64	str	str	i64
20180911	"CS018205000001"	"P071401012"	2200
20180414	"CS018205000001"	"P060104007"	600
20170614	"CS018205000001"	"P050206001"	990
20170614	"CS018205000001"	"P060702015"	108
20190216	"CS018205000001"	"P071005024"	102

P-005#

从收据明细数据(df_receipt)中，按销售日(sales_ymd)、顾客 ID(customer_id)、商品代码(product_cd)、销售金额(amount)的顺序指定列，抽取满足下列全部条件的数据。

顾客 ID(customer_id)为 "CS018205000001"
销售金额(amount)在 1,000 以上

(
df_receipt
.filter(
    (pl.col.customer_id == 'CS018205000001')
    & (pl.col.amount >= 1000)
)
.select('sales_ymd', 'customer_id', 'product_cd', 'amount')
)

shape: (3, 4)

sales_ymd	customer_id	product_cd	amount
i64	str	str	i64
20180911	"CS018205000001"	"P071401012"	2200
20190226	"CS018205000001"	"P071401020"	2200
20180911	"CS018205000001"	"P071401005"	1100

(
df_receipt
.filter(
    pl.col.customer_id
        .eq('CS018205000001')
        .and_(pl.col.amount.ge(1000))
)
.select('sales_ymd', 'customer_id', 'product_cd', 'amount')
)

shape: (3, 4)

sales_ymd	customer_id	product_cd	amount
i64	str	str	i64
20180911	"CS018205000001"	"P071401012"	2200
20190226	"CS018205000001"	"P071401020"	2200
20180911	"CS018205000001"	"P071401005"	1100

P-006#

从收据明细数据(df_receipt)中，按销售日(sales_ymd)、顾客 ID(customer_id)、商品代码(product_cd)、销售数量(quantity)、销售金额(amount)的顺序指定列，抽取满足下列全部条件的数据。

顾客 ID(customer_id)为 "CS018205000001"
销售金额(amount)在 1,000 以上，或销售数量(quantity)在 5 以上

(
df_receipt
.filter(
    pl.col.customer_id
        .eq('CS018205000001')
        .and_(
            pl.col.amount
                .ge(1000)
                .or_(pl.col.quantity >= 5)
        )
)
.select('sales_ymd', 'customer_id', 'product_cd', 'amount', 'quantity')
)

shape: (5, 5)

sales_ymd	customer_id	product_cd	amount	quantity
i64	str	str	i64	i64
20180911	"CS018205000001"	"P071401012"	2200	1
20180414	"CS018205000001"	"P060104007"	600	6
20170614	"CS018205000001"	"P050206001"	990	5
20190226	"CS018205000001"	"P071401020"	2200	1
20180911	"CS018205000001"	"P071401005"	1100	1

P-007#

从收据明细数据(df_receipt)中，按销售日(sales_ymd)、顾客 ID(customer_id)、商品代码(product_cd)、销售金额(amount)的顺序指定列，抽取满足下列全部条件的数据。

顾客 ID(customer_id)为 "CS018205000001"
销售金额(amount)在 1,000 以上、2,000 以下

(
df_receipt
.filter(
    (pl.col.customer_id == 'CS018205000001')
    & pl.col.amount.is_between(1000, 2000)
)
.select('sales_ymd', 'customer_id', 'product_cd', 'amount')
)

shape: (1, 4)

sales_ymd	customer_id	product_cd	amount
i64	str	str	i64
20180911	"CS018205000001"	"P071401005"	1100

P-008#

从收据明细数据(df_receipt)中，按销售日(sales_ymd)、顾客 ID(customer_id)、商品代码(product_cd)、销售金额(amount)的顺序指定列，抽取满足下列全部条件的数据。

顾客 ID(customer_id)为 "CS018205000001"
商品代码(product_cd)不是 "P071401019"

(
df_receipt
.filter(
    (pl.col.customer_id == 'CS018205000001')
    & (pl.col.product_cd != 'P071401019')
)
.select('sales_ymd', 'customer_id', 'product_cd', 'amount')
)

shape: (12, 4)

sales_ymd	customer_id	product_cd	amount
i64	str	str	i64
20180911	"CS018205000001"	"P071401012"	2200
20180414	"CS018205000001"	"P060104007"	600
20170614	"CS018205000001"	"P050206001"	990
20170614	"CS018205000001"	"P060702015"	108
20190216	"CS018205000001"	"P071005024"	102
…	…	…	…
20190924	"CS018205000001"	"P060805001"	495
20190226	"CS018205000001"	"P071401020"	2200
20180911	"CS018205000001"	"P071401005"	1100
20190216	"CS018205000001"	"P040101002"	218
20190924	"CS018205000001"	"P091503001"	280

P-009#

下面的处理中，请在不改变输出结果的前提下把 OR 改写成 AND。

df_store.query('not(prefecture_cd == "13" | floor_area > 900)')

(
df_store
.filter(
    (pl.col.prefecture_cd != '13')
    & (pl.col.floor_area <= 900)
)
)

shape: (3, 10)

store_cd	store_name	prefecture_cd	prefecture	address	address_kana	tel_no	longitude	latitude	floor_area
str	str	str	str	str	str	str	f64	f64	f64
"S14046"	"北山田店"	"14"	"神奈川県"	"神奈川県横浜市都筑区北山田一丁目"	"カナガワケンヨコハマシツヅキクキタヤマタイッチョウメ"	"045-123-4049"	139.5916	35.56189	831.0
"S14011"	"日吉本町店"	"14"	"神奈川県"	"神奈川県横浜市港北区日吉本町四丁目"	"カナガワケンヨコハマシコウホククヒヨシホンチョウヨンチョウメ"	"045-123-4033"	139.6316	35.54655	890.0
"S12013"	"習志野店"	"12"	"千葉県"	"千葉県習志野市芝園一丁目"	"チバケンナラシノシシバゾノイッチョウメ"	"047-123-4002"	140.022	35.66122	808.0

P-010#

从店铺数据(df_store)中，只抽取店铺代码(store_cd)以 "S14" 开头的记录的全部字段，显示 10 条。

(
df_store
.filter(pl.col.store_cd.str.starts_with('S14'))
.head(5)
)

shape: (5, 10)

store_cd	store_name	prefecture_cd	prefecture	address	address_kana	tel_no	longitude	latitude	floor_area
str	str	str	str	str	str	str	f64	f64	f64
"S14010"	"菊名店"	"14"	"神奈川県"	"神奈川県横浜市港北区菊名一丁目"	"カナガワケンヨコハマシコウホククキクナイッチョウメ"	"045-123-4032"	139.6326	35.50049	1732.0
"S14033"	"阿久和店"	"14"	"神奈川県"	"神奈川県横浜市瀬谷区阿久和西一丁目"	"カナガワケンヨコハマシセヤクアクワニシイッチョウメ"	"045-123-4043"	139.4961	35.45918	1495.0
"S14036"	"相模原中央店"	"14"	"神奈川県"	"神奈川県相模原市中央二丁目"	"カナガワケンサガミハラシチュウオウニチョウメ"	"042-123-4045"	139.3716	35.57327	1679.0
"S14040"	"長津田店"	"14"	"神奈川県"	"神奈川県横浜市緑区長津田みなみ台五丁目"	"カナガワケンヨコハマシミドリクナガツタミナミダイゴチョウメ"	"045-123-4046"	139.4994	35.52398	1548.0
"S14050"	"阿久和西店"	"14"	"神奈川県"	"神奈川県横浜市瀬谷区阿久和西一丁目"	"カナガワケンヨコハマシセヤクアクワニシイッチョウメ"	"045-123-4053"	139.4961	35.45918	1830.0

数据科学 100 道练习 01~10

目录

数据科学 100 道练习 01~10#

前言#

P-001#

P-002#

P-003#

P-004#

P-005#

P-006#

P-007#

P-008#

P-009#

P-010#