Pandas宽格式时间序列：识别首个有效季度、最高销量季度及对应销量

核心思路 #

先提取所有季度销量列，针对每行数据筛选有效销量（非0）的季度，再提取首个、第二个有效季度的ID和销量，同时找出销量最高的季度ID。

完整实现代码 #

import pandas as pd

data = {'Book Title': ['A Court of Thorns and Roses', 'Where the Crawdads Sing', 'Bad Blood', 'Atomic Habits'],
    'Metric': ['Book Sales','Book Sales','Book Sales','Book Sales'],
   'Q1 2022': [100000,0,0,0],
   'Q2 2022': [50000,75000,0,35000],
   'Q3 2022': [25000,150000,20000,45000],
   'Q4 2022': [25000,20000,10000,65000]}

df1 = pd.DataFrame(data)

# 1. 提取所有季度销量列
quarter_cols = [col for col in df1.columns if col.startswith('Q')]

# 2. 新增「首个有效季度ID」和对应销量
# 找到每行第一个非0销量的季度列名
df1['首个有效季度ID'] = df1[quarter_cols].ne(0).idxmax(axis=1)
# 根据季度ID提取对应销量
df1['首个有效季度销量'] = df1.lookup(df1.index, df1['首个有效季度ID'])

# 3. 新增「第二个有效季度ID」和对应销量
# 先获取每行所有有效（非0）的季度列表
valid_quarters = df1[quarter_cols].ne(0).apply(lambda x: x[x].index.tolist(), axis=1)
# 提取第二个有效季度，无则设为NaN
df1['第二个有效季度ID'] = valid_quarters.str[1]
# 提取对应销量，无则设为NaN
df1['第二个有效季度销量'] = df1.apply(
    lambda row: row[row['第二个有效季度ID']] if pd.notna(row['第二个有效季度ID']) else pd.NA,
    axis=1
)

# 4. 新增「最高销量季度ID」
# 找到每行销量最大值对应的季度列名
df1['最高销量季度ID'] = df1[quarter_cols].idxmax(axis=1)

关键步骤说明 #

• 提取季度列：通过startswith('Q')筛选季度相关列，避免硬编码列名，适配后续季度扩展。
• 首个有效季度：用ne(0)将非0销量转为布尔值，idxmax(axis=1)返回每行第一个有效季度的列名，再通过lookup提取对应销量值。
• 第二个有效季度：先收集每行所有有效季度的列名列表，取索引为1的元素作为第二个有效季度；若有效季度不足2个，字段值设为NaN，对应销量同理处理。
• 最高销量季度：直接用idxmax(axis=1)返回每行销量最大值对应的季度列名。

最终结果示例 #

处理后的数据新增4个目标字段，以《Where the Crawdads Sing》为例：

• 首个有效季度ID：Q2 2022，销量75000
• 第二个有效季度ID：Q3 2022，销量150000
• 最高销量季度ID：Q3 2022

内容的提问来源于stack exchange，提问作者Tyler Moore