# 推薦環(huán)境：Anaconda套件（已集成Pandas/OpenPyXL）
# 或通過pip安裝
pip install pandas openpyxl xlrd

import pandas as pd
 
# 基礎(chǔ)讀取
df = pd.read_excel('sales_data.xlsx', sheet_name='Sheet1')
 
# 進(jìn)階參數(shù)示例
df = pd.read_excel(
    'large_file.xlsx',
    nrows=10000,          # 僅讀取前1萬行
    usecols='C:F',        # 讀取C到F列
    dtype={'訂單號(hào)': str}  # 指定列數(shù)據(jù)類型
)

# 檢測重復(fù)項(xiàng)
duplicates = df[df.duplicated(subset=['訂單號(hào)', '產(chǎn)品ID'])]
 
# 智能去重（保留最新記錄）
df.sort_values('下單時(shí)間', inplace=True)
df.drop_duplicates(subset=['訂單號(hào)'], keep='last', inplace=True)

# 字符串轉(zhuǎn)日期（處理Excel日期格式混亂）
df['下單日期'] = pd.to_datetime(
    df['下單日期'],
    format='%Y/%m/%d',  # 明確指定格式
    errors='coerce'     # 無效解析轉(zhuǎn)為NaT
)
 
# 數(shù)值規(guī)范化（處理科學(xué)計(jì)數(shù)法）
df['產(chǎn)品ID'] = df['產(chǎn)品ID'].astype('str').str.zfill(10)

pivot = df.pivot_table(
    index='銷售區(qū)域',
    columns='產(chǎn)品類別',
    values='訂單金額',
    aggfunc={
        '訂單金額': 'sum',
        '訂單號(hào)': 'count'
    },
    fill_value=0
)
 
# 計(jì)算客單價(jià)
pivot['客單價(jià)'] = pivot['訂單金額'] / pivot['訂單號(hào)']

# 數(shù)值異常檢測示例
z_scores = (df['訂單金額'] - df['訂單金額'].mean()) / df['訂單金額'].std()
outliers = df[z_scores.abs() > 3]
 
# 分類異常檢測（需安裝`pandas-profiling`）
# pip install pandas-profiling
import pandas_profiling
profile = pandas_profiling.ProfileReport(df)
profile.to_file("report.html")

orders = pd.read_excel('orders.xlsx')
customers = pd.read_excel('customers.xlsx')
 
# 左連接（保留所有訂單）
merged = pd.merge(
    orders,
    customers[['客戶ID', '客戶等級(jí)', '所屬區(qū)域']],
    on='客戶ID',
    how='left'
)

# 分塊讀取處理（適用于500MB+文件）
chunk_size = 50000
chunks = []
for chunk in pd.read_excel('huge_data.xlsx', chunksize=chunk_size):
    # 每個(gè)chunk進(jìn)行清洗操作
    chunk = clean_data(chunk)
    chunks.append(chunk)
df = pd.concat(chunks)

# 轉(zhuǎn)換數(shù)據(jù)類型節(jié)省內(nèi)存
df['訂單號(hào)'] = df['訂單號(hào)'].astype('category')  # 類別型
df['訂單金額'] = df['訂單金額'].astype('float32') # 浮點(diǎn)數(shù)降精度
 
# 刪除中間變量
del chunk
import gc
gc.collect()  # 強(qiáng)制垃圾回收

# 生成分析摘要
report = f"""
=== 銷售數(shù)據(jù)概覽 ===
總訂單數(shù): {len(df):,}
總銷售額: {df['訂單金額'].sum():,.2f}
平均客單價(jià): {df['訂單金額'].mean():,.2f}
"""
 
with open('report.txt', 'w') as f:
    f.write(report)
 
# 導(dǎo)出處理后數(shù)據(jù)
df.to_excel('cleaned_data.xlsx', index=False)

import matplotlib.pyplot as plt
 
# 銷售趨勢分析
monthly_sales = df.resample('M', on='下單日期')['訂單金額'].sum()
 
plt.figure(figsize=(12,6))
monthly_sales.plot(kind='bar', color='skyblue')
plt.title('月度銷售趨勢')
plt.xlabel('月份')
plt.ylabel('銷售額（萬元）')
plt.savefig('sales_trend.png', dpi=300, bbox_inches='tight')

bank_df = pd.read_excel('bank_statement.xlsx')
internal_df = pd.read_excel('internal_records.xlsx')
 
merged = pd.merge(
    bank_df,
    internal_df,
    left_on=['交易時(shí)間', '金額'],
    right_on=['記賬時(shí)間', '發(fā)生額'],
    how='outer',
    indicator=True
)
 
unmatched = merged[merged['_merge'] != 'both']

inventory = pd.read_excel('inventory.xlsx')
 
# 安全庫存計(jì)算（考慮采購周期）
inventory['安全庫存'] = inventory['日均銷量'] * 7
inventory['庫存狀態(tài)'] = np.where(
    inventory['當(dāng)前庫存'] < inventory['安全庫存'],
    '需補(bǔ)貨',
    '正常'
)
 
alert = inventory[inventory['庫存狀態(tài)'] == '需補(bǔ)貨']