# -*- coding: utf-8 -*-
"""第 8 章《Pandas 数据处理与分析》示例代码 · 模块 6：数据查询

配套页面：https://www.qixiangsun.com.cn/python-bigdata-2026-lesson8
运行环境：Python 3.14+ / pandas 3.0.5（pandas 2.x 亦可运行，个别输出格式略有差异）
说明：本模块：行列查询 · 比较条件 · & | ~ 多条件 · between / isin · nlargest · 查询 + 排序。
      页面中每段代码都独立可运行，本文件按页面顺序排列；
      页面上标注“故意报错”的示例未收录（运行会中断），请到页面查看。
用法：python 示例代码_模块6_数据查询.py
"""
import pandas as pd

# ===== [k6-row-slice] 行切片 df[2:5] 取第 2—4 行、df[:3] 取前 3 行（例 8-15） =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print('df[2:5] —— 从第 2 行开始取 3 行（不含 5）：')
print(df[2:5][['订单编号', '商品名称', '金额']])
print('df[:3] —— 前 3 行：')
print(df[:3][['订单编号', '商品名称', '金额']])


# ===== [k6-col-select] 选一列是 Series，选多列是 DataFrame；先取行再取列（例 8-16、8-17） =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print(df[['商品名称', '数量']].head(3))
print('先取前 3 行、再取两列（例 8-17）：')
print(df[:3][['商品名称', '数量']])
print('df["商品名称"] 的类型：', type(df['商品名称']).__name__)
print('df[["商品名称", "数量"]] 的类型：', type(df[['商品名称', '数量']]).__name__)


# ===== [k6-loc-iloc] loc 看名字、iloc 看座号：同一个格子两种查法 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print('loc[3, 商品名称]：', df.loc[3, '商品名称'])
print('loc 取多行多列：')
print(df.loc[[2, 5], ['商品名称', '金额']])
print('iloc[2, 3]：', df.iloc[2, 3])
print('iloc[0:2, 0:3]：')
print(df.iloc[0:2, 0:3])


# ===== [k6-compare] 数值比较、字符串比较、筛选后再 tail（例 8-18、8-19） =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print('金额 >= 4000 的订单：')
print(df[df['金额'] >= 4000][['订单编号', '商品名称', '金额']])
print('数量 >= 25 的订单数：', int((df['数量'] >= 25).sum()))
print('商品名称 != 围巾 的订单数：', int((df['商品名称'] != '围巾').sum()))
print('运动服订单的最后 2 条（例 8-18 先筛后 tail）：')
print(df[df['商品名称'] == '运动服'].tail(2)[['订单编号', '商品名称', '金额']])


# ===== [k6-date] 日期条件查询：等于某一天、大于某一天，字符串自动转日期（例 8-23 的日期条件） =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print('2 月 28 日的订单：')
print(df[df['订单日期'] == '2021-02-28'][['订单编号', '订单日期', '商品名称']])
print('2 月 20 日之后的订单：')
print(df[df['订单日期'] > '2021-02-20'][['订单编号', '订单日期', '金额']])


# ===== [k6-and] 两个条件用 & 连起来：单价 > 150 并且数量 >= 10 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

mask = (df['单价'] > 150) & (df['数量'] >= 10)
print('单价 > 150 且数量 >= 10：')
print(df[mask][['订单编号', '商品名称', '单价', '数量']])
print('命中行数：', int(mask.sum()))
print('换成“卫衣且数量 >= 25”（例 8-22）：',
      int(((df['数量'] >= 25) & (df['商品名称'] == '卫衣')).sum()), '行')
print('—— 完整 310 行数据里能查到，这份 12 行精简数据里恰好没有')


# ===== [k6-or] 两个条件用 | 连起来：金额 > 4500 或者日期是 2 月 28 日（例 8-23） =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

mask = (df['金额'] > 4500) | (df['订单日期'] == '2021-02-28')
print('金额 > 4500 或者日期是 2 月 28 日：')
print(df[mask][['订单编号', '订单日期', '金额']])
print('命中行数：', int(mask.sum()))
print('分别看两个条件各命中几行：',
      int((df['金额'] > 4500).sum()), '和', int((df['订单日期'] == '2021-02-28').sum()))


# ===== [k6-not] ~ 取反：把“等于围巾”翻成“不等于围巾”，也等价于 != =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

mask = ~(df['商品名称'] == '围巾')
print('不是围巾的订单：', int(mask.sum()), '行')
print(df[mask][['订单编号', '商品名称', '金额']].head(3))
print('和 != 等价吗？', mask.equals(df['商品名称'] != '围巾'))
print('~ 也能翻区间：金额不在 1000—5000 之间的行数：',
      int((~df['金额'].between(1000, 5000)).sum()))


# ===== [k6-between] between(25, 30) 两端都含；想不含两端用 inclusive='neither'（例 8-20） =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print('数量在 25—30（含两端）：')
print(df[df['数量'].between(25, 30)][['订单编号', '商品名称', '数量', '金额']])
left = (df['数量'] >= 25) & (df['数量'] <= 30)
print('与 >= 25 且 <= 30 等价吗？', df['数量'].between(25, 30).equals(left))
print('不含两端（25 和 30 都不算）：',
      int(df['数量'].between(25, 30, inclusive='neither').sum()), '行')


# ===== [k6-isin] isin(['围巾', '运动服']) 一次匹配多个值（例 8-21） =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print('围巾和运动服的订单：')
print(df[df['商品名称'].isin(['围巾', '运动服'])][['订单编号', '商品名称', '金额']])
print('命中行数：', int(df['商品名称'].isin(['围巾', '运动服']).sum()))
print('只要围巾：', int(df['商品名称'].isin(['围巾']).sum()), '行')
print('等价写法（两个等于用 | 连）：', int(((df['商品名称'] == '围巾') | (df['商品名称'] == '运动服')).sum()), '行')


# ===== [k6-nlargest] nlargest(3, '金额') 取金额最大的 3 条（例 8-24 是 10 条） =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print('金额最大的 3 条：')
print(df.nlargest(3, '金额')[['订单编号', '商品名称', '金额']])
print('和 sort_values 再 head(3) 结果一样吗？',
      df.nlargest(3, '金额')['订单编号'].tolist() ==
      df.sort_values('金额', ascending=False)['订单编号'].head(3).tolist())


# ===== [k6-sort-multi] 多列排序：金额降序、金额相同再按订单日期升序（例 8-26） =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

sorted_df = df.sort_values(by=['金额', '订单日期'], ascending=[False, True])
print('金额降序、金额相同按日期升序，前 5 条：')
print(sorted_df[['订单编号', '订单日期', '金额']].head(5))
print('空值排最后（na_position 默认 last）：',
      df.sort_values('数量')['数量'].tail(2).tolist())


# ===== [k6-filter-sort] 先筛后排：金额 >= 2000 的记录按金额降序（例 8-27） =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

result = df[df['金额'] >= 2000].sort_values(by='金额', ascending=False)
print('金额 >= 2000 的订单，按金额降序：')
print(result[['订单编号', '订单日期', '商品名称', '金额']])
print('行数：', result.shape[0], ' 金额合计：', round(result['金额'].sum(), 2))


# ===== [k6-cow] 链式赋值不改原表（pandas 3 写时复制），loc 一步赋值才生效 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

df[df['金额'] >= 4000]['标记'] = '大单'
print('链式赋值后，原表有“标记”列吗？', '标记' in df.columns)
df.loc[df['金额'] >= 4000, '标记'] = '大单'
print('用 loc 一次定位，才真的写进原表：')
print(df[df['金额'] >= 4000][['订单编号', '金额', '标记']])
print('其余行标记为 NaN：', df['标记'].isnull().sum(), '行')


# 注：页面上这些示例是“故意报错”的，未收录到本文件：k6-parens-error
