# %% 提取數據 從sqlite
import pandas as pd
from sqlalchemy import create_engine
'''創建sqlite3連接'''
eg = create_engine('sqlite:///I:/jianguoyun/Udoc/WorkDb/dataset2022.db')  #創建數據庫連接
# 讀取數據庫文件
q_store = """
select *  from pj_store20220525
"""
# 讀取表
cols= ['貨號','品類','節慶','庫位','合格','換包裝','錯碼','維修','總數量' ]
df = pd.read_sql(q_store,eg)[cols]

# df.duplicated 計算重復項
cond = df.duplicated('貨號') # 查看貨號相同的項 df.duplicated返回的是布爾值
def_line = df[cond]
def_line

#有多少個重復行
dep_data = df.duplicated('貨號').sum() 
dep_data

# 用df.drop_duplicates提取夏季品類
df_category = df.query('節慶=="夏季"').drop_duplicates('品類',keep='last',inplace=False) 
df_category['品類'].sort_values()