Pandas库读取excel文件进行查询实例

import pandas as pd
from openpyxl import load_workbook

'''
一、读取表
1.xloopup功能
2.index+match功能(可以用其他功能代替)

二、输出表
1.按照表模板输出
2.添加数据append
'''

kw_root = pd.read_excel('库存.xlsx',sheet_name='门店周转',dtype={'V':str,'门店编码':str,'库存数量':str},usecols='A,B,C,E,F') #读取excel源文件
kw_GD = kw_root[kw_root['分区'] == '广东']    #只输出分区为广东的门店数据
kw_GD_clean = kw_GD[['门店编码','SKU','V','库存数量']]  #只输出[V（商品编码）、门店编码、SKU、库存数量]列
'''
MDM门店编码匹配问题：
1.缺失值{}
2.门店id应该对应[电商店MDM]or[普通门店MDM]?
目前解决办法：
1.drop缺失值,后续进行人工匹配
2.取普通门店MDM进行对应
'''
kw_reference1 = pd.read_excel('门店编码及SKU对照表.xlsx',dtype={'门店编码':str,'MDM编码':str},sheet_name='MDM') #读取门店MDM-门店id 对照表
kw_GD_rMDM = pd.merge(kw_GD_clean, kw_reference1[['门店编码', 'MDM编码','门店名称']], on='门店编码', how='inner')  #根据kw_GD_clean的门店id列，对照kw_reference1的MDM编号，并在kw_GD_clean中增加MDM编号列
kw_reference2 = pd.read_excel('门店编码及SKU对照表.xlsx',sheet_name='SKU') #读取 门店SKU-isku 对照表
kw_GD_rMDM = pd.merge(kw_GD_rMDM, kw_reference2[['iSKU', 'SKU','目标库存天数','系列']], on='SKU', how='inner')  #根据kw_GD_rMDMn的门店SKU列，对照kw_reference2的ISKU编号，并在kw_GD_rMDM中增加MDM编号列
P3M = pd.read_excel('P3M.xlsx',sheet_name='DB',usecols=['customer_code','TSR','xcity_name'])    #读取P3M表
kw_GD_rMDM.rename(columns={'MDM编码': 'customer_code'}, inplace=True) #修改kw_GD_rMDM中的MDM编码列名为customer_code
kw_GD_rMDM_TSR = pd.merge(kw_GD_rMDM, P3M[['customer_code','TSR','xcity_name']], on='customer_code', how='inner')  #根据kw_GD_rMDM的MDM编号，对照P3M的MDM编号，并在kw_GD_rMDM_TSR中增加MDM编号列
kw_GD_rMDM_TSR.insert(loc=8, column='客户', value='渠道名') #追加渠道名
kw_GD_rMDM_TSR = kw_GD_rMDM_TSR.drop_duplicates()   #检查是否有重复项
kw_GD_rMDM_TSR.rename(columns={'customer_code': 'MDM编码'}, inplace=True) #撤销kw_GD_rMDM_TSR的列名修改
kw_GD_rMDM_TSR.rename(columns={
    'MDM编码': 'MDM门店编码',
    '门店编码': '门店外部系统id',
    'V': 'Account Item SKU',
    'SKU': 'sku',
    '库存数量': 'W1库存'
}, inplace=True)    #修改dataframe中的列名，以对照excel中的列名，1.MDM编码-MDM门店编码 2.门店编码-门店外部系统id 3.V-Account Item SKU 4.SKU-sku 5.库存数量-W1库存

by_TSR = kw_GD_rMDM_TSR.groupby('TSR')   #将现有的表格按TSR分组
dfs = {category: group for category, group in by_TSR}   #按TSR姓名拆分dataframe
processed_dfs = []
for category, sub_df in dfs.items():
    try:
        TSR_Sheet = pd.read_excel('P3M.xlsx',sheet_name=category)   #按照分类名打开对应的TSR表
    except ValueError as e:
        sub_df.insert(loc=11, column='P3M POS', value='TSR_NOT_FOUND')
        processed_dfs.append(sub_df)
        continue
    if  TSR_Sheet is not None:
        sub_df.insert(loc=11, column='P3M POS', value='UNCHECK')
        TSR_Sheet_MDM = TSR_Sheet.loc[1]  # 读取MDM所在的行
        TSR_Sheet_MDM =TSR_Sheet_MDM.reset_index()  #修改inedx为数字
        TSR_Sheet_MDM = TSR_Sheet_MDM.drop(columns = 'index')   #删除index列
        TSR_Sheet_SKU = pd.read_excel('P3M.xlsx', sheet_name=category, usecols='B')  # 读取SKU所在的列
        for index,row in sub_df.iterrows():  #对每一行数据进行对比，MDM=customer_code且SKU=ISKU，读取对应的df.loc[行序数,列序数]，读取后更新数据列中的P3M POS列
            c = TSR_Sheet_MDM[TSR_Sheet_MDM.values == row['MDM门店编码']].index#查找MDM的列序数
            r = TSR_Sheet_SKU[TSR_Sheet_SKU.values == row['iSKU']].index#查找SKU的行序数
            if len(r) > 0 and len(c) > 0:
                r_index = r[0]  # 获取第一个匹配的行索引
                c_index = c[0]  # 获取第一个匹配的列索引
                # 检查索引是否在范围内
                if 0 <= r_index < len(TSR_Sheet) and 0 <= c_index < len(TSR_Sheet.columns):
                    P3M_POS = TSR_Sheet.iloc[r_index, c_index]  # 读取P3M POS数据
                    print(f"P3M_POS value at row {r_index}, column {c_index}: {P3M_POS}")
                    # 更新数据列中的P3M POS列
                    sub_df.at[index, 'P3M POS'] = P3M_POS
                else:
                    print(f"Index out of bounds for row {r_index}, column {c_index}")
                    sub_df.at[index, 'P3M POS'] = 'OUT_OF_INDEX'
            else:
                if len(r) == 0:
                    print(f"No valid row index found for iSKU {row['iSKU']}")
                    sub_df.at[index, 'P3M POS'] = 'OUT_OF_ROW'
                if len(c) == 0:
                    print(f"No valid column index found for customer_code {row['customer_code']}")
                    sub_df.at[index, 'P3M POS'] = 'OUT_OF_COL'
    processed_dfs.append(sub_df)

kw_GD_rMDM_TSR = pd.concat(processed_dfs,ignore_index=True)
columns_to_write = ['MDM门店编码','门店外部系统id','Account Item SKU', 'iSKU', 'W1库存','门店名称','TSR','目标库存天数','客户','xcity_name','P3M POS','系列'] #指定要写入的列名
df_to_write = kw_GD_rMDM_TSR[columns_to_write]  #指定写入列
inventory_management = load_workbook('库存周转计算表.xlsx')    #读取库存周转计算表模板
sheet = inventory_management['Sheet1']  #选择表sheet1
startrow = sheet.max_row + 1 if sheet.max_row > 0 else 1    #计算数据的起始行为已有数据的下一行
for row in df_to_write.itertuples(index=False, name=None):
    sheet[f"C{startrow}"] = row[0]  # MDM门店编码
    sheet[f"D{startrow}"] = row[1]  # 门店外部系统id
    sheet[f"E{startrow}"] = row[5]  #门店名称
    sheet[f"J{startrow}"] = row[2]  # Account Item SKU
    sheet[f"I{startrow}"] = row[3]  # sku
    sheet[f"P{startrow}"] = row[4]  # W1库存
    sheet[f"G{startrow}"] = row[6]  # TSR
    sheet[f"L{startrow}"] = row[7]  #目标库存天数
    sheet[f"A{startrow}"] = row[8]  #客户
    sheet[f"B{startrow}"] = row[9]  #城市
    sheet[f"N{startrow}"] = row[10]  #P3M POS
    sheet[f"K{startrow}"] = row[11]  #系列
    startrow += 1 #写入数据
inventory_management.save('库存周转计算表.xlsx')
print("数据追加完成")