在Pandas DataFrame中按州计算Market Share列值

原始数据 #

import pandas as pd

data = {
    'State': ['FL', 'FL', 'FL', 'FL', 'FL', 'FL', 'FL', 'FL Total', 
              'GA', 'GA', 'GA', 'GA', 'GA', 'GA', 'GA Total', 
              'LA', 'LA', 'LA', 'LA', 'LA Total'],
    'ProductName2': ['Advil', 'Advil', 'Advil Total', 'Mucinex', 'Mucinex Total', 'Solosec', 'Solosec Total', '',
                     'Advil', 'Advil', 'Advil Total', 'Mucinex', 'Mucinex', 'Mucinex Total', '',
                     'Advil', 'Advil', 'Advil', 'Advil Total', ''],
    'Units': ['1', '2', '3', '3', '3', '4', '4', '10',
              '5', '6', '11', '7', '8', '15', '26',
              '9', '4', '2', '15', '15'],
    'Scripts': ['5', '7', '12', '6', '6', '4', '4', '22',
                '2', '9', '11', '10', '2', '12', '23',
                '6', '7', '12', '25', '25'],
    'Total Amount': ['1', '54', '55', '321', '321', '45', '45', '421',
                     '89', '48', '137', '23', '56', '79', '216',
                     '9', '26', '32', '67', '67'],
    'Market Share': ['', '', '', '', '', '', '', '',
                     '', '', '', '', '', '', '',
                     '', '', '', '', '']
}

result_df = pd.DataFrame(data)

需求 #

计算Market Share列值：当前行的Scripts除以对应州的总Scripts（每个州所有行的Market Share总和应为100%）。州总Scripts对应State列带Total的行的Scripts值（如FL州总Scripts为22）。

尝试的代码 #

rows = result_df.values.tolist()
state_sums = []
states = []

for row in rows:
    if 'Total' in row[0]:
        state_sums.append(row[3])

for state in result_df['State'].unique():
    if 'Total' not in state:
        states.append(state)

for row in rows:
    if row[0] in states:
        index = states.index(row[0])
        row[5] = (float(row[3]) / float(state_sums[index]))

print(result_df)

问题 #

单州计算可行，但多州场景下无法准确关联每行与对应州的总Scripts，需要更可靠的关联方式。

解决方案 #

利用Pandas的映射功能，先构建州名到总Scripts的字典，再批量计算Market Share，无需手动循环行：

方法1：简洁向量化实现 #

# 提取州总行数据，构建州名到总Scripts的映射
state_totals = result_df[result_df['State'].str.contains('Total')].copy()
state_totals['State'] = state_totals['State'].str.replace(' Total', '')
state_total_map = state_totals.set_index('State')['Scripts'].astype(float).to_dict()

# 计算非州总行的Market Share
non_total_mask = ~result_df['State'].str.contains('Total')
result_df.loc[non_total_mask, 'Market Share'] = (
    result_df.loc[non_total_mask, 'Scripts'].astype(float) 
    / result_df.loc[non_total_mask, 'State'].map(state_total_map)
)

# 州总行的Market Share设为1.0（总和为100%）
result_df.loc[~non_total_mask, 'Market Share'] = 1.0

# 保留三位小数
result_df['Market Share'] = result_df['Market Share'].round(3)

print(result_df)

方法2：自定义函数实现（适合复杂逻辑） #

# 构建州总Scripts映射字典
state_total_map = {}
for _, row in result_df.iterrows():
    if 'Total' in row['State']:
        state_name = row['State'].replace(' Total', '')
        state_total_map[state_name] = float(row['Scripts'])

# 定义计算函数
def get_market_share(row):
    if 'Total' in row['State']:
        return 1.0
    total = state_total_map.get(row['State'])
    if not total or not row['Scripts']:
        return None
    return round(float(row['Scripts']) / total, 3)

# 批量计算
result_df['Market Share'] = result_df.apply(get_market_share, axis=1)

print(result_df)

两种方法都能自动匹配每行对应的州总Scripts，支持任意数量的州，计算结果符合预期：

• FL州每行Market Share为对应Scripts/22，总和为1.0
• GA州对应Scripts/23，总和为1.0
• LA州对应Scripts/25，总和为1.0

内容的提问来源于stack exchange，提问作者Hunter Garrison