chat_history_analysis/qa_info.py

import pandas as pd

import warnings
warnings.filterwarnings('ignore')

# 创建筛选条件
def analyze_data(excel_path):

    df = pd.read_excel(excel_path)

    result = {}

    # 1. 呼出且与AI对话的数量
    total_calls = len(df[(df['对话方向'] == '呼出')])
    ai_calls = len(df[(df['对话方向'] == '呼出') & (df['是否在和AI对话'] == '是')])
    
    # 2. 呼出且非AI对话的数量
    non_ai_calls = len(df[(df['对话方向'] == '呼出') & (df['是否在和AI对话'] == '否')])
    
    # 3. 呼出且AI对话的满意度分类
    ai_calls_df = df[(df['对话方向'] == '呼出') & (df['是否在和AI对话'] == '是')]
    satisfied = len(ai_calls_df[ai_calls_df['分类是否正确'] == '满意'])
    unsatisfied = len(ai_calls_df[ai_calls_df['分类是否正确'] == '不满意'])
    not_rated = len(ai_calls_df[ai_calls_df['分类是否正确'] == '未评价'])

    # 4. 比率计算
    satisfaction_rate = f"{((satisfied + not_rated) / ai_calls * 100):.2f}%"
    not_rated_rate = f"{(not_rated / ai_calls * 100):.2f}%"
    auto_call_rate = f"{(ai_calls / total_calls * 100):.2f}%"

    # 5. 人工计算

    result["总呼出"] = total_calls
    result["人呼出"] = non_ai_calls
    result["AI呼出"] = ai_calls
    result["满意数"] = satisfied
    result["不满意"] = unsatisfied
    result["未评价"] = not_rated
    result["未评价率"] = not_rated_rate
    result["满意率"] = satisfaction_rate
    result["AI回复率"] = auto_call_rate

    # print(result)


    # 筛选条件：呼出 + AI对话 + 未评价
    filtered_df = df[
        (df['对话方向'] == '呼出') & 
        (df['是否在和AI对话'] == '是') & 
        (df['分类是否正确'] == '未评价')
    ]

    # 按客服用户名分组统计
    user_counts = filtered_df['客服用户名'].value_counts()
    
    unrate_info = {}
    for user, count in user_counts.items():
        unrate_info[user.split(':')[-1]] = count    
    result["未评价统计"] = unrate_info

    return result

# 使用示例
if __name__ == "__main__":

    excel_path = "./data/聊天历史统计表_04_02.xlsx"
    result = analyze_data(excel_path)

    print(result)