如何实现店铺名字段拆分并均分价格？Pandas Excel数据处理需求

需求 #

现有Excel数据中，部分行的Shop Name字段包含斜杠分隔的多个店铺名（如MarketA/MarketB），对应同一价格。需要将这类行拆分为单行单店铺的格式，同时将原价格按店铺数量平均分配到拆分后的每一行。

现有代码 #

import numpy as np
import pandas as pd
import os
import xlsxwriter

original_data = pd.read_excel('purchase_record.xlsx')
original_data.head()
df = pd.DataFrame(original_data)
filter_data = df.set_index(['item', 'Region', 'City Code', 'Area', 'City Name',
                        'price']).apply(lambda x: x.str.split('/').explode()).reset_index()

原始数据样例 #

Shop Name      item    Region  City Code   Area  City Name  price
MarketA/MarketB   Apple     A       10201     East      B        100
    MarketC       Grape     B       20010     West      C         90
MarketD/MarketE   Orange    C       30132     North     D         84

预期处理效果 #

1. MarketA Apple A 10201 East B 50
2. MarketB Apple A 10201 East B 50
3. MarketC Grape B 20010 West C 90
4. MarketD Orange C 30132 North D 42
5. MarketE Orange C 30132 North D 42

修改后的代码及说明 #

import numpy as np
import pandas as pd
import os
import xlsxwriter

# 读取原始数据
original_data = pd.read_excel('purchase_record.xlsx')
df = pd.DataFrame(original_data)

# 1. 计算每行的店铺数量
df['shop_count'] = df['Shop Name'].str.split('/').str.len()

# 2. 拆分Shop Name字段为多行
df_exploded = df.assign(Shop_Name=df['Shop Name'].str.split('/')).explode('Shop_Name')

# 3. 计算平均价格：原价格除以店铺数量
df_exploded['price'] = df_exploded['price'] / df_exploded['shop_count']

# 4. 整理列顺序并删除临时列
result = df_exploded[['Shop_Name', 'item', 'Region', 'City Code', 'Area', 'City Name', 'price']].rename(columns={'Shop_Name': 'Shop Name'})

# 查看处理结果
print(result)

# 可选：保存到新Excel文件
result.to_excel('processed_purchase_record.xlsx', index=False, engine='xlsxwriter')

代码说明 #

• 计算店铺数量：通过str.split('/').str.len()获取每行拆分后的店铺数量，用于后续价格分配。
• 拆分字段：使用assign+explode拆分Shop Name，避免设置索引后可能出现的列处理问题。
• 分配价格：直接用原价格除以店铺数量，得到每个店铺的对应价格。
• 整理输出：调整列顺序并删除临时的shop_count列，确保输出格式符合预期。

内容的提问来源于stack exchange，提问作者Vui Chee Chang