91 lines
3.1 KiB
Python
91 lines
3.1 KiB
Python
import pandas as pd
|
|
import re
|
|
import json # Add this line at the top of your script
|
|
from AgentProxy import AgentProxy
|
|
|
|
|
|
# Read the Excel file
|
|
df = pd.read_excel('analysis_result_top200.xlsx')
|
|
statistic = {}
|
|
|
|
# Iterate through the "example" column
|
|
for index, row in df.iterrows():
|
|
text = row['销售动作分析']
|
|
# Extract text enclosed by '**', by using regular expression
|
|
print(index)
|
|
stage = row['Sales stage']
|
|
if stage not in statistic:
|
|
statistic[stage] = {}
|
|
statistic[stage]['total'] = 0
|
|
statistic[stage]['total'] += 1
|
|
|
|
matches = re.findall(r'\*\*(.*?)\*\*', text)
|
|
for match in matches:
|
|
print(f"{match}") # Print as markdown title # iterate all the matches
|
|
if match not in statistic[stage]:
|
|
statistic[stage][match] = 0
|
|
statistic[stage][match] += 1
|
|
if len(matches) > 0:
|
|
df.at[index, '销售动作'] = ','.join(matches)
|
|
else:
|
|
df.at[index, '销售动作'] = ''
|
|
|
|
df.to_excel('analysis_result_top200.xlsx', index=False)
|
|
|
|
print(statistic)
|
|
|
|
action_dict = {}
|
|
# Calculate the percentage distribution of each action within each stage
|
|
for stage, actions in statistic.items():
|
|
total = actions.pop('total')
|
|
print(f"Stage: {stage}")
|
|
for action, count in actions.items():
|
|
percentage = (count / total) * 100
|
|
print(f" {action}: {percentage:.2f}%")
|
|
action_dict[action] = action
|
|
|
|
print(len(action_dict.keys()))
|
|
prompt_abstract_actions = f"以下是销售动作的文字描述，{','.join(action_dict.keys())} 请根据这些结果，总结出抽象的销售动作，以便于将上述销售动作归纳到抽象的销售动作中"
|
|
api_key = '25bda2c39c0f8ca0'
|
|
api_secret = 'e0008b9b9727cb8ceea5a132dbe62495'
|
|
assistant_id = "66bb09a84673b57506fe7bbd"
|
|
agent = AgentProxy(assistant_id, api_key, api_secret)
|
|
# res = agent.send_message(prompt_abstract_actions)
|
|
# print(res)
|
|
|
|
|
|
|
|
abstract_actions = [
|
|
"客户接触与需求识别",
|
|
"产品演示与方案提供",
|
|
"商务谈判与合同准备",
|
|
"项目支持与优化",
|
|
"市场分析与策略调整",
|
|
"内部协调与支持",
|
|
"合同审查与订单处理",
|
|
"客户关系维护",
|
|
"后续跟进与机会挖掘"
|
|
]
|
|
|
|
full_mapping = []
|
|
for i in range(0, len(action_dict.keys()), 20):
|
|
group = list(action_dict.keys())[i:i+20]
|
|
prompt_abstract_actions = f"以下是销售动作的文字描述，{','.join(group)}; 以下是抽象销售动作, {','.join(abstract_actions)}; 请根据上述信息，将销售动作归纳到抽象的销售动作中并给出映射关系， 用json数组返回,销售动作字段为action, 抽象销售动作字段为abstract_action"
|
|
res = agent.send_message(prompt_abstract_actions)
|
|
|
|
try:
|
|
json_match = re.search(r'```json\s*(.*?)\s*```', res, re.DOTALL)
|
|
|
|
if json_match:
|
|
json_data = json_match.group(1)
|
|
mapping = json.loads(json_data)
|
|
else:
|
|
mapping = []
|
|
except Exception as e:
|
|
print(f"Error parsing response: {e}")
|
|
mapping = []
|
|
print(res)
|
|
print(mapping)
|
|
full_mapping.extend(mapping)
|
|
print(full_mapping)
|