import pandas as pd
import numpy as np

# Series 示例
s = pd.Series([85, 92, 78, 96],
              index=['張三', '李四', '王五', '趙六'],
              name='數學成績')
print("Series:")
print(s)

# DataFrame 示例
df = pd.DataFrame({
    '姓名': ['張三', '李四', '王五', '趙六'],
    '數學': [85, 92, 78, 96],
    '英語': [88, 85, 92, 79],
    '語文': [76, 80, 85, 88]
})
print("\nDataFrame:")
print(df)

import pandas as pd

# 首先，我們創(chuàng)建一個 CSV 文件用于演示
data = {
    '訂單ID': range(1, 11),
    '商品': ['手機', '電腦', '平板', '耳機', '鍵盤',
            '鼠標', '顯示器', '音箱', '充電器', '數據線'],
    '價格': [2999, 5999, 1999, 199, 399,
            99, 1599, 499, 79, 29],
    '數量': [2, 1, 3, 5, 2,
            4, 1, 2, 10, 15],
    '日期': pd.date_range('2024-01-01', periods=10)
}
df = pd.DataFrame(data)
df.to_csv('sample_orders.csv', index=False, encoding='utf-8-sig')
print("示例 CSV 文件已創(chuàng)建")

# 讀取 CSV 文件
df_read = pd.read_csv('sample_orders.csv')
print("\n讀取的 DataFrame:")
print(df_read)
print(f"\n數據形狀: {df_read.shape}")
print(f"數據類型:\n{df_read.dtypes}")

# 演示各種參數的用法
# 假設我們有一個稍微復雜一點的 CSV 文件
complex_csv = """ID,Name,Score,Grade,Comment
001,張三,85.5,A,
002,李四,92.0,B,優(yōu)秀
003,王五,,C,需要努力
004,趙六,78.5,A,進步很大
005,,88.0,B,繼續(xù)加油"""

# 先寫入文件
with open('complex_data.csv', 'w', encoding='utf-8') as f:
    f.write(complex_csv)

# 1. 基本讀取
print("1. 基本讀取:")
print(pd.read_csv('complex_data.csv'))

# 2. 指定列名
print("\n2. 指定列名:")
print(pd.read_csv('complex_data.csv',
                  names=['編號', '姓名', '分數', '等級', '評語'],
                  header=0))

# 3. 只讀取指定列
print("\n3. 只讀姓名列和分數列:")
print(pd.read_csv('complex_data.csv', usecols=['Name', 'Score']))

# 4. 處理缺失值
print("\n4. 默認缺失值處理:")
df = pd.read_csv('complex_data.csv')
print(df.isna().sum())  # 統計每列缺失值數量

# 5. 指定缺失值標記
print("\n5. 自定義缺失值標記:")
df = pd.read_csv('complex_data.csv', na_values=['', ' '])
print(df.isna().sum())

# 6. 解析日期列
df_with_date = pd.read_csv('sample_orders.csv', parse_dates=['日期'])
print("\n6. 日期列解析:")
print(df_with_date['日期'].dtype)  # 應該是 datetime64

# 7. 指定數據類型
print("\n7. 指定數據類型:")
df_typed = pd.read_csv('sample_orders.csv',
                       dtype={'訂單ID': str, '商品': 'category'})
print(df_typed.dtypes)

# 8. 跳過行
print("\n8. 跳過前2行:")
print(pd.read_csv('complex_data.csv', skiprows=2))

# 9. 讀取指定行數（用于預覽大文件）
print("\n9. 只讀前3行:")
print(pd.read_csv('sample_orders.csv', nrows=3))

# 10. 設置索引列
print("\n10. 設置索引列為訂單ID:")
df_idx = pd.read_csv('sample_orders.csv', index_col='訂單ID')
print(df_idx)

# TSV（制表符分隔）
tsv_data = "ID\tName\tScore\n1\t張三\t85\n2\t李四\t92"
with open('data.tsv', 'w', encoding='utf-8') as f:
    f.write(tsv_data)

print("TSV 文件讀取:")
df_tsv = pd.read_csv('data.tsv', sep='\t')  # 或直接用 pd.read_table()
print(df_tsv)

# 其他分隔符示例
pipe_data = "ID|Name|Score\n1|張三|85\n2|李四|92"
with open('data_pipe.txt', 'w', encoding='utf-8') as f:
    f.write(pipe_data)

print("\n管道符分隔讀取:")
df_pipe = pd.read_csv('data_pipe.txt', sep='|')
print(df_pipe)

import pandas as pd

# 創(chuàng)建示例 Excel 文件
df1 = pd.DataFrame({
    '姓名': ['張三', '李四', '王五'],
    '部門': ['技術部', '市場部', '財務部'],
    '工資': [15000, 12000, 13000]
})
df2 = pd.DataFrame({
    '姓名': ['趙六', '錢七'],
    '部門': ['技術部', '人事部'],
    '工資': [16000, 11000]
})

# 寫入 Excel（需要 openpyxl 庫）
with pd.ExcelWriter('sample_data.xlsx', engine='openpyxl') as writer:
    df1.to_excel(writer, sheet_name='員工信息', index=False)
    df2.to_excel(writer, sheet_name='新員工', index=False)

print("Excel 文件已創(chuàng)建")

# 讀取 Excel
# 注意：需要安裝 openpyxl: pip install openpyxl
print("\n讀取第一個工作表:")
df_excel = pd.read_excel('sample_data.xlsx', sheet_name='員工信息')
print(df_excel)

# 查看所有工作表名
print("\n所有工作表:")
xls = pd.ExcelFile('sample_data.xlsx')
print(xls.sheet_names)

# 讀取所有工作表
print("\n讀取所有工作表:")
all_sheets = pd.read_excel('sample_data.xlsx', sheet_name=None)
for name, df in all_sheets.items():
    print(f"\n--- {name} ---")
    print(df)

# 1. 跳過表頭行
# 有些 Excel 文件前兩行是標題和說明
with pd.ExcelWriter('demo_skip.xlsx', engine='openpyxl') as writer:
    pd.DataFrame({'A': [1, 2, 3]}).to_excel(writer, sheet_name='data')

# 2. 指定列的數據類型
df = pd.read_excel('sample_data.xlsx', sheet_name='員工信息',
                   dtype={'工資': float})

# 3. 讀取指定范圍（需要引擎支持）
# df = pd.read_excel('file.xlsx', usecols='A:C', nrows=100)

# 4. 處理 Excel 中的空行
df = pd.read_excel('sample_data.xlsx', sheet_name='員工信息',
                   skip_blank_lines=True)

# 方法1：使用 chardet 庫自動檢測
# pip install chardet
import chardet

with open('sample_orders.csv', 'rb') as f:
    raw_data = f.read(10000)  # 讀取前10000字節(jié)
    result = chardet.detect(raw_data)
    print(f"檢測到的編碼: {result['encoding']}")
    print(f"置信度: {result['confidence']:.2%}")

# 方法2：用 pandas 的 errors 參數處理
# ignore：忽略無法解碼的字符
# replace：用 ? 替換無法解碼的字符
# surrogateescape：特殊處理（Python 3.1+）

# 方法3：嘗試多種編碼
def read_with_encoding(filepath, encodings=['utf-8', 'gbk', 'gb18030', 'latin-1']):
    for enc in encodings:
        try:
            df = pd.read_csv(filepath, encoding=enc)
            print(f"成功使用 {enc} 編碼讀取")
            return df
        except UnicodeDecodeError:
            continue
    raise ValueError("無法用任何編碼讀取文件")

df = read_with_encoding('sample_orders.csv')

# Windows 系統導出的 CSV 通常使用 GBK 編碼
# 解決方案1：指定編碼
df = pd.read_csv('data.csv', encoding='gbk')

# 解決方案2：使用 utf-8-sig（處理帶 BOM 的 UTF-8）
# BOM (Byte Order Mark) 是 UTF-8 文件開頭的特殊標記
df = pd.read_csv('data.csv', encoding='utf-8-sig')

# 解決方案3：用 Notepad++ 或 VS Code 轉換文件編碼
# 打開文件 -> 轉換為 UTF-8 -> 保存

import pandas as pd
import numpy as np

# 先創(chuàng)建一個大文件用于演示（模擬 100 萬行數據）
np.random.seed(42)
n_rows = 1_000_000
large_df = pd.DataFrame({
    'id': range(n_rows),
    'value1': np.random.normal(100, 20, n_rows),
    'value2': np.random.normal(50, 10, n_rows),
    'category': np.random.choice(['A', 'B', 'C', 'D'], n_rows),
    'date': pd.date_range('2020-01-01', periods=n_rows, freq='min')
})
large_df.to_csv('large_data.csv', index=False)
print(f"已創(chuàng)建包含 {n_rows} 行數據的文件")

# 每次讀取 10 萬行
chunk_size = 100_000
total_rows = 0
total_value1 = 0
total_value2 = 0

for chunk in pd.read_csv('large_data.csv', chunksize=chunk_size):
    total_rows += len(chunk)
    total_value1 += chunk['value1'].sum()
    total_value2 += chunk['value2'].sum()
    print(f"已處理 {total_rows} 行...")

print(f"\n總行數: {total_rows}")
print(f"value1 總和: {total_value1:,.2f}")
print(f"value2 總和: {total_value2:,.2f}")

# 計算大文件的整體統計信息
chunk_size = 100_000
stats = {
    'count': 0,
    'sum_v1': 0, 'sum_v2': 0,
    'sum_sq_v1': 0, 'sum_sq_v2': 0,
    'min_v1': float('inf'), 'max_v1': float('-inf'),
    'min_v2': float('inf'), 'max_v2': float('-inf')
}

for chunk in pd.read_csv('large_data.csv',
                         usecols=['value1', 'value2'],  # 只讀需要的列
                         chunksize=chunk_size):

    stats['count'] += len(chunk)
    stats['sum_v1'] += chunk['value1'].sum()
    stats['sum_v2'] += chunk['value2'].sum()
    stats['sum_sq_v1'] += (chunk['value1'] ** 2).sum()
    stats['sum_sq_v2'] += (chunk['value2'] ** 2).sum()
    stats['min_v1'] = min(stats['min_v1'], chunk['value1'].min())
    stats['max_v1'] = max(stats['max_v1'], chunk['value1'].max())
    stats['min_v2'] = min(stats['min_v2'], chunk['value2'].min())
    stats['max_v2'] = max(stats['max_v2'], chunk['value2'].max())

# 計算均值和標準差
mean_v1 = stats['sum_v1'] / stats['count']
mean_v2 = stats['sum_v2'] / stats['count']
std_v1 = (stats['sum_sq_v1'] / stats['count'] - mean_v1 ** 2) ** 0.5
std_v2 = (stats['sum_sq_v2'] / stats['count'] - mean_v2 ** 2) ** 0.5

print("大文件統計信息:")
print(f"  value1: 均值={mean_v1:.2f}, 標準差={std_v1:.2f}, "
      f"范圍=[{stats['min_v1']:.2f}, {stats['max_v1']:.2f}]")
print(f"  value2: 均值={mean_v2:.2f}, 標準差={std_v2:.2f}, "
      f"范圍=[{stats['min_v2']:.2f}, {stats['max_v2']:.2f}]")

# 使用 iterator 參數，手動控制迭代
reader = pd.read_csv('large_data.csv', chunksize=50000, iterator=True)

# 可以跳過某些 chunk
for i, chunk in enumerate(reader):
    if i == 0:
        print(f"第 {i} 塊，形狀: {chunk.shape}")
        # 保存第一塊作為參考
        sample = chunk.head()
    # 只處理 category 為 A 的數據
    filtered = chunk[chunk['category'] == 'A']
    if len(filtered) > 0:
        pass  # 處理過濾后的數據

# 優(yōu)化版分塊讀取
optimized_chunks = pd.read_csv(
    'large_data.csv',
    chunksize=100_000,
    usecols=['id', 'value1', 'category'],  # 只讀3列
    dtype={'id': 'int32', 'value1': 'float32', 'category': 'category'}  # 節(jié)省內存
)

for chunk in optimized_chunks:
    # 處理每個塊
    pass

import pandas as pd
import sqlite3

# 創(chuàng)建 SQLite 數據庫
conn = sqlite3.connect('sample_database.db')
cursor = conn.cursor()

# 創(chuàng)建表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS employees (
        id INTEGER PRIMARY KEY,
        name TEXT,
        department TEXT,
        salary REAL,
        hire_date TEXT
    )
''')

# 插入數據
employees = [
    (1, '張三', '技術部', 15000, '2020-03-15'),
    (2, '李四', '市場部', 12000, '2021-06-01'),
    (3, '王五', '財務部', 13000, '2019-11-20'),
    (4, '趙六', '技術部', 18000, '2018-08-10'),
    (5, '錢七', '人事部', 11000, '2022-01-05'),
]
cursor.executemany('INSERT OR REPLACE INTO employees VALUES (?, ?, ?, ?, ?)',
                   employees)
conn.commit()

# 方式1：直接讀取整個表
print("1. 讀取整個表:")
df_sqlite = pd.read_sql('SELECT * FROM employees', conn)
print(df_sqlite)

# 方式2：帶條件的查詢
print("\n2. 查詢技術部員工:")
df_tech = pd.read_sql(
    "SELECT name, salary FROM employees WHERE department = '技術部'",
    conn
)
print(df_tech)

# 方式3：使用 read_sql_query（等價于 read_sql）
print("\n3. 平均工資查詢:")
df_avg = pd.read_sql_query(
    "SELECT department, AVG(salary) as avg_salary, COUNT(*) as count "
    "FROM employees GROUP BY department",
    conn
)
print(df_avg)

# 方式4：使用 read_sql_table（讀取整張表，SQLite 不支持）
# df = pd.read_sql_table('employees', conn)  # 需要 SQLAlchemy

# 方式5：DataFrame 直接寫入數據庫
df_new = pd.DataFrame({
    'id': [6, 7],
    'name': ['孫八', '周九'],
    'department': ['技術部', '市場部'],
    'salary': [16000, 13500],
    'hire_date': ['2023-05-10', '2023-08-20']
})
df_new.to_sql('employees', conn, if_exists='append', index=False)
print("\n4. 新增數據后:")
print(pd.read_sql('SELECT * FROM employees', conn))

# 關閉連接
conn.close()

# 注意：以下代碼需要 MySQL 服務器運行
# 安裝: pip install pymysql

# 連接 MySQL
# conn_mysql = create_engine('mysql+pymysql://username:password@localhost:3306/database_name')

# 讀取數據
# df = pd.read_sql('SELECT * FROM table_name', conn_mysql)

# 示例代碼（注釋狀態(tài)，實際運行需要 MySQL）
"""
from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:password@localhost:3306/mydb')

# 讀取數據
df = pd.read_sql('SELECT * FROM users', engine)

# 寫入數據
df.to_sql('users_backup', engine, if_exists='replace', index=False)

# 復雜查詢
df = pd.read_sql('''
    SELECT d.department_name,
           AVG(e.salary) as avg_salary,
           COUNT(e.id) as emp_count
    FROM employees e
    JOIN departments d ON e.dept_id = d.id
    GROUP BY d.department_name
    ORDER BY avg_salary DESC
''', engine)
"""

import json

# 創(chuàng)建示例 JSON
json_data = [
    {"name": "張三", "age": 25, "city": "北京"},
    {"name": "李四", "age": 30, "city": "上海"},
    {"name": "王五", "age": 28, "city": "廣州"}
]
with open('sample.json', 'w', encoding='utf-8') as f:
    json.dump(json_data, f, ensure_ascii=False)

# 讀取 JSON
df_json = pd.read_json('sample.json')
print("JSON 讀取:")
print(df_json)

# 復雜 JSON（嵌套結構）
complex_json = '''
{
    "employees": {
        "張三": {"age": 25, "dept": "技術部"},
        "李四": {"age": 30, "dept": "市場部"}
    }
}
'''
with open('complex.json', 'w', encoding='utf-8') as f:
    f.write(complex_json)

# orient 參數處理不同 JSON 格式
df_complex = pd.read_json('complex.json', orient='index')
print("\n復雜 JSON 讀取:")
print(df_complex)

# 讀取網頁中的表格
# url = 'https://en.wikipedia.org/wiki/List_of_countries_by_GDP_(nominal)'
# tables = pd.read_html(url)
# print(f"頁面中找到 {len(tables)} 個表格")
# print(tables[0].head())  # 第一個表格

# 這里用本地 HTML 演示
html_table = """
<table>
    <tr><th>姓名</th><th>成績</th></tr>
    <tr><td>張三</td><td>85</td></tr>
    <tr><td>李四</td><td>92</td></tr>
    <tr><td>王五</td><td>78</td></tr>
</table>
"""
with open('sample.html', 'w', encoding='utf-8') as f:
    f.write(html_table)

df_html = pd.read_html('sample.html')[0]
print("HTML 表格讀取:")
print(df_html)

import pandas as pd
import numpy as np
import sqlite3

# 場景：整合來自不同數據源的信息

# 數據源1：CSV 文件（員工基本信息）
emp_csv = pd.DataFrame({
    'emp_id': [1, 2, 3, 4, 5],
    'name': ['張三', '李四', '王五', '趙六', '錢七'],
    'gender': ['男', '女', '男', '男', '女'],
    'birth_year': [1990, 1988, 1992, 1985, 1993]
})
emp_csv.to_csv('employees_basic.csv', index=False, encoding='utf-8-sig')

# 數據源2：Excel 文件（績效數據）
perf_excel = pd.DataFrame({
    'emp_id': [1, 2, 3, 4, 5],
    'q1_score': [85, 92, 78, 96, 88],
    'q2_score': [88, 90, 82, 94, 85],
    'q3_score': [90, 88, 85, 92, 90]
})
with pd.ExcelWriter('employees_perf.xlsx', engine='openpyxl') as writer:
    perf_excel.to_excel(writer, sheet_name='績效', index=False)

# 數據源3：SQLite 數據庫（薪資數據）
conn = sqlite3.connect('salary.db')
pd.DataFrame({
    'emp_id': [1, 2, 3, 4, 5],
    'base_salary': [15000, 12000, 13000, 18000, 11000],
    'bonus': [3000, 5000, 2000, 8000, 2500]
}).to_sql('salary', conn, if_exists='replace', index=False)

# 整合分析
print("=" * 50)
print("多源數據整合分析")
print("=" * 50)

# 讀取各數據源
basic = pd.read_csv('employees_basic.csv')
perf = pd.read_excel('employees_perf.xlsx', sheet_name='績效')
salary = pd.read_sql('SELECT * FROM salary', conn)

# 合并數據
merged = basic.merge(perf, on='emp_id').merge(salary, on='emp_id')
merged['avg_score'] = merged[['q1_score', 'q2_score', 'q3_score']].mean(axis=1)
merged['total_income'] = merged['base_salary'] + merged['bonus']

print("\n整合后的完整數據:")
print(merged.to_string())

print("\n各季度平均績效:")
print(f"  Q1: {perf['q1_score'].mean():.1f}")
print(f"  Q2: {perf['q2_score'].mean():.1f}")
print(f"  Q3: {perf['q3_score'].mean():.1f}")

print("\n平均薪資:")
print(f"  基本薪資均值: ￥{salary['base_salary'].mean():,.0f}")
print(f"  獎金均值: ￥{salary['bonus'].mean():,.0f}")
print(f"  總收入均值: ￥{merged['total_income'].mean():,.0f}")

conn.close()

import pandas as pd
import numpy as np

# 創(chuàng)建模擬日志文件（50 萬行）
np.random.seed(42)
n = 500_000
log_data = pd.DataFrame({
    'timestamp': pd.date_range('2024-01-01', periods=n, freq='s'),
    'user_id': np.random.randint(1, 10000, n),
    'action': np.random.choice(['click', 'view', 'purchase', 'logout'], n,
                               p=[0.5, 0.3, 0.15, 0.05]),
    'page': np.random.choice(['home', 'product', 'cart', 'checkout', 'help'], n),
    'duration_ms': np.random.exponential(2000, n).astype(int)
})
log_data.to_csv('web_logs.csv', index=False)
print(f"已創(chuàng)建 {n} 行日志數據")

# 分塊讀取并聚合
print("\n分塊處理日志數據:")

stats = {}
chunk_size = 50_000

for chunk in pd.read_csv('web_logs.csv',
                         usecols=['action', 'user_id', 'duration_ms'],
                         chunksize=chunk_size):

    # 按 action 分組統計
    action_counts = chunk['action'].value_counts()
    for action, count in action_counts.items():
        stats[action] = stats.get(action, 0) + count

    # 統計總用戶數
    stats['unique_users'] = len(chunk['user_id'].unique())

    print(f"處理 {chunk_size} 行...")

print("\n統計結果:")
for action, count in stats.items():
    print(f"  {action}: {count:,}" if action != 'unique_users'
          else f"  獨立用戶數: {count:,}")

import pandas as pd
import numpy as np

# 創(chuàng)建臟數據
dirty_csv = """id,name,price,quantity
1,手機,2999,2
2,電腦,5999,1
3,,1999,3
4,耳機,abc,5
5,鍵盤,399,2
1,手機,2999,2
6,鼠標,99,
"""
with open('dirty_data.csv', 'w', encoding='utf-8') as f:
    f.write(dirty_csv)

# 讀取并清洗
df = pd.read_csv('dirty_data.csv')

print("原始數據:")
print(df)

# 1. 刪除完全重復的行
df = df.drop_duplicates()

# 2. 刪除 name 為空的行
df = df.dropna(subset=['name'])

# 3. 處理 price 列的文本（轉換為數字，無效的變 NaN）
df['price'] = pd.to_numeric(df['price'], errors='coerce')

# 4. 填充 quantity 的缺失值為 1
df['quantity'] = df['quantity'].fillna(1).astype(int)

# 5. 刪除 price 仍然為 NaN 的行
df = df.dropna(subset=['price'])

print("\n清洗后數據:")
print(df)

import pandas as pd
import numpy as np
from collections import defaultdict

# 先創(chuàng)建大文件
np.random.seed(42)
n = 200_000
big = pd.DataFrame({
    'category': np.random.choice(['A', 'B', 'C', 'D', 'E'], n),
    'value': np.random.normal(50, 15, n)
})
big.to_csv('big_categories.csv', index=False)

# 分塊統計
cat_stats = defaultdict(lambda: {'sum': 0, 'count': 0})

for chunk in pd.read_csv('big_categories.csv', chunksize=50000):
    for cat, group in chunk.groupby('category'):
        cat_stats[cat]['sum'] += group['value'].sum()
        cat_stats[cat]['count'] += len(group)

print("分類統計:")
for cat, stats in sorted(cat_stats.items()):
    mean = stats['sum'] / stats['count']
    print(f"  {cat}: 均值={mean:.2f}, 數量={stats['count']}")

import pandas as pd
import sqlite3

conn = sqlite3.connect('shop.db')

# 創(chuàng)建客戶表
customers = pd.DataFrame({
    'customer_id': [1, 2, 3, 4, 5],
    'name': ['張三', '李四', '王五', '趙六', '錢七'],
    'city': ['北京', '上海', '廣州', '深圳', '杭州']
})
customers.to_sql('customers', conn, if_exists='replace', index=False)

# 創(chuàng)建訂單表
orders = pd.DataFrame({
    'order_id': range(1, 11),
    'customer_id': [1, 2, 1, 3, 2, 4, 1, 5, 3, 2],
    'amount': [500, 800, 300, 1200, 600, 400, 700, 900, 350, 550]
})
orders.to_sql('orders', conn, if_exists='replace', index=False)

# JOIN 查詢
result = pd.read_sql('''
    SELECT c.name, c.city,
           COUNT(o.order_id) as order_count,
           SUM(o.amount) as total_spent,
           AVG(o.amount) as avg_order
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id
    ORDER BY total_spent DESC
''', conn)

print("客戶消費分析:")
print(result.to_string())

conn.close()