如何用Pandas按session_id分组统计、计算均值及生成价格区间与性别维度结果

1. 数据加载与预处理 #

import pandas as pd

# 导入给定的电商会话数据
data = [
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "red"}, "duration": 25, "attribute-occasion": "Casual", "attribute-ideal for": "Women", "attribute-heel height": "0 inch", "product": "V4STYLES Loafers", "price": 1049},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "blue"}, "duration": 25, "attribute-occasion": "Casual", "attribute-ideal for": "Women", "attribute-heel height": "0 inch", "product": "V4STYLES Loafers", "price": 1049},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "blue"}, "duration": 25, "attribute-occasion": "Casual", "attribute-ideal for": "Women", "attribute-heel height": "0 inch", "product": "V4STYLES Loafers", "price": 1049},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "blue"}, "duration": 25, "attribute-occasion": "Casual", "attribute-ideal for": "Women", "attribute-heel height": "0 inch", "product": "V4STYLES Loafers", "price": 1049},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "blue"}, "duration": 25, "attribute-occasion": "Casual, Ethnic, Formal, Party", "attribute-ideal for": "Women", "attribute-weight": "200 g (per single Shoe) - Weight of the product may vary depending on size.", "product": "dilli bazaaar Bellies, Corporate Casuals, Casuals", "price": 349},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "blue"}, "duration": 25, "attribute-ideal for": "Women's", "attribute-occasion": "Casual, Party", "attribute-pattern": "Self Design", "product": "F&S Casual, Party Short Sleeve Self Design Women's Top", "price": 421},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "blue"}, "duration": 25, "attribute-ideal for": "Women's", "attribute-occasion": "Casual, Party", "attribute-pattern": "Self Design", "product": "F&S Casual, Party Short Sleeve Self Design Women's Top", "price": 421},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "green"}, "duration": 25, "attribute-base material": "Brass", "attribute-brand": "Vendee Fashion", "attribute-model number": "VD8636", "product": "Vendee Fashion Brass Jewel Set", "price": 829},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "green"}, "duration": 25, "attribute-base material": "Brass", "attribute-brand": "Vendee Fashion", "attribute-model number": "VD8636", "product": "Vendee Fashion Brass Jewel Set", "price": 829},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "green"}, "duration": 25, "attribute-base material": "Brass", "attribute-brand": "Vendee Fashion", "attribute-model number": "VD8636", "product": "Vendee Fashion Brass Jewel Set", "price": 829},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "green"}, "duration": 25, "attribute-base material": "Brass", "attribute-brand": "Vendee Fashion", "attribute-model number": "VD8636", "product": "Vendee Fashion Brass Jewel Set", "price": 829},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "green"}, "duration": 25, "attribute-occasion": "Casual", "attribute-ideal for": "Women", "attribute-tip shape": "Round", "product": "Willy Winkies Black Boots", "price": 999},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "green"}, "duration": 25, "attribute-occasion": "Casual", "attribute-ideal for": "Women", "attribute-tip shape": "Round", "product": "Willy Winkies Black Boots", "price": 999},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "green"}, "duration": 25, "attribute-occasion": "Casual", "attribute-ideal for": "Women", "attribute-tip shape": "Round", "product": "Willy Winkies Black Boots", "price": 999},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "green"}, "duration": 25, "attribute-ideal for": "Women", "attribute-occasion": "Casual", "attribute-weight": "500.00000000 g (per single Shoe) - Weight of the product may vary depending on size.", "product": "CatBird Boots", "price": 999},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "green"}, "duration": 25, "attribute-ideal for": "Women", "attribute-occasion": "Casual", "attribute-weight": "500.00000000 g (per single Shoe) - Weight of the product may vary depending on size.", "product": "CatBird Boots", "price": 999},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "green"}, "duration": 25, "attribute-ideal for": "Women", "attribute-occasion": "Casual", "attribute-weight": "500.00000000 g (per single Shoe) - Weight of the product may vary depending on size.", "product": "CatBird Boots", "price": 999},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "green"}, "duration": 15, "attribute-pattern": "Printed", "attribute-occasion": "Party", "attribute-ideal for": "Men's", "product": "Van Heusen Men's Printed Party Shirt", "price": 1469},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "green"}, "duration": 15, "attribute-pattern": "Printed", "attribute-occasion": "Party", "attribute-ideal for": "Men's", "product": "Van Heusen Men's Printed Party Shirt", "price": 1469},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "green"}, "duration": 15, "attribute-pattern": "Printed", "attribute-occasion": "Party", "attribute-ideal for": "Men's", "product": "Van Heusen Men's Printed Party Shirt", "price": 1469},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "green"}, "duration": 15, "attribute-pattern": "Printed", "attribute-occasion": "Party", "attribute-ideal for": "Men's", "product": "Van Heusen Men's Printed Party Shirt", "price": 1469},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "green"}, "duration": 15, "attribute-pattern": "Printed", "attribute-occasion": "Party", "attribute-ideal for": "Men's", "product": "Van Heusen Men's Printed Party Shirt", "price": 1469},
    {"session_id": "fc4867a5-cff2-4210-9ddf-9e5db35187ec", "filters": {"color": "green"}, "duration": 15, "attribute-pattern": "Printed", "attribute-occasion": "Party", "attribute-ideal for": "Men's", "product": "Van Heusen Men's Printed Party Shirt", "price": 1469}
]

# 转换为DataFrame
df = pd.json_normalize(data)

2. 核心统计分析 #

2.1 筛选条件与属性字段统计 #

颜色筛选次数统计

# 按会话ID和颜色分组统计
color_stats = df.groupby(['session_id', 'filters.color']).size().unstack(fill_value=0)
# 转换为"颜色: 次数"的字符串格式
color_summary = color_stats.apply(lambda x: ', '.join([f"{k}: {v}" for k, v in x.items() if v>0]), axis=1)

性别维度统计

gender_stats = df.groupby(['session_id', 'attribute-ideal for']).size().unstack(fill_value=0)
gender_summary = gender_stats.apply(lambda x: ', '.join([f"{k}: {v}" for k, v in x.items() if v>0]), axis=1)

2.2 价格均值与区间统计 #

# 计算会话级平均价格
price_mean = df.groupby('session_id')['price'].mean().round(2)

# 生成价格区间并统计
price_bins = pd.cut(df['price'], bins=[0, 500, 1000, 1500, float('inf')], labels=['0-500', '501-1000', '1001-1500', '1500+'])
price_interval_summary = df.groupby(['session_id', price_bins]).size().unstack(fill_value=0).apply(lambda x: ', '.join([f"{k}: {v}" for k, v in x.items() if v>0]), axis=1)

2.3 会话时长均值统计 #

duration_mean = df.groupby('session_id')['duration'].mean().round(2)

3. 最终结果汇总 #

# 合并所有统计结果
final_result = pd.DataFrame({
    '颜色筛选统计': color_summary,
    '平均会话时长': duration_mean,
    '平均商品价格': price_mean,
    '性别维度统计': gender_summary,
    '价格区间统计': price_interval_summary
})

# 输出格式化结果
print("会话ID,颜色筛选统计,平均会话时长,平均商品价格,性别维度统计,价格区间统计")
for idx, row in final_result.iterrows():
    print(f"{idx}, {row['颜色筛选统计']}, {row['平均会话时长']}, {row['平均商品价格']}, {row['性别维度统计']}, {row['价格区间统计']}")

输出结果 #

会话ID,颜色筛选统计,平均会话时长,平均商品价格,性别维度统计,价格区间统计
fc4867a5-cff2-4210-9ddf-9e5db35187ec, blue: 6, green: 16, red: 1, 23.33, 923.57, Men's: 6, Women: 14, Women's: 2, 0-500: 3, 501-1000: 13, 1001-1500: 6

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