如何针对DataFrame按日期匹配变量并计算比率？

Hey there! Let's work through this problem with your df1 DataFrame. First, let's recap your data structure to make sure we're on the same page:

First, let's clean up the duplicate rows since they don't add new information (same month, product, and price):

import pandas as pd

# Drop duplicate rows (keep one unique entry per month + product_key)
df_clean = df1.drop_duplicates(subset=['month', 'product_key'])

场景1：计算单个产品跨月份的价格比率 #

If you want to calculate how each product's price changes from one month to the next (e.g., September price vs August price), here's how to do it:

# Convert month column to datetime for easier time-series handling
df_clean['month'] = pd.to_datetime(df_clean['month'], format='%Y%m')

# Sort the data by product and month to ensure correct order
df_clean = df_clean.sort_values(by=['product_key', 'month'])

# Calculate price ratio vs previous month (current month price / previous month price)
df_clean['price_ratio_prev_month'] = df_clean.groupby('product_key')['price'].pct_change() + 1

• pct_change() gives the percentage difference between current and previous value; adding 1 converts it to a ratio (e.g., 1.0 means no change, 1.1 means 10% increase).

场景2：计算月度聚合指标的比率 #

If you want to look at aggregated metrics (like total monthly sales) and compare them across months, first we'll aggregate the data, then compute the ratios:

# Calculate total sales per month per product (assuming duplicate rows are sales instances)
df_agg = df1.groupby(['month', 'product_key']).agg(
    total_sales=('price', lambda x: x.count() * x.iloc[0]),  # Count * unit price
    unit_price=('price', 'first')
).reset_index()

# Convert month to datetime
df_agg['month'] = pd.to_datetime(df_agg['month'], format='%Y%m')

# Calculate total monthly sales and their ratios vs previous month
monthly_totals = df_agg.groupby('month')['total_sales'].sum().reset_index()
monthly_totals['sales_ratio_prev_month'] = monthly_totals['total_sales'].pct_change() + 1

场景3：宽格式匹配方便跨月对比 #

If you prefer to have all months' prices for a product in a single row (easier to compute specific month-to-month ratios), use pivot:

# Reshape data to wide format (products as rows, months as columns)
df_wide = df_clean.pivot(index='product_key', columns='month', values='price')

# Calculate specific month-to-month ratios
df_wide['ratio_sep_vs_aug'] = df_wide['2014-09-01'] / df_wide['2014-08-01']
df_wide['ratio_oct_vs_sep'] = df_wide['2014-10-01'] / df_wide['2014-09-01']

All these approaches let you match variables by date and compute the ratios you need—adjust based on exactly which ratio you're targeting!

内容的提问来源于stack exchange，提问作者Jay J