| 1234567891011121314151617181920212223242526272829303132333435363738 |
- import pandas as pd
- import ast
- # 读取Excel文件
- input_file = '视频分析报告1.xlsx'
- df = pd.read_excel(input_file)
- # 第8列（索引7）为"钩子提取"
- def extract_fields(hook_col):
- times, queries, hooks = [], [], []
- try:
- # 兼容字符串形式的列表
- items = ast.literal_eval(hook_col) if isinstance(hook_col, str) else []
- for item in items:
- if isinstance(item, dict):
- times.append(item.get('需求钩子出现时间', ''))
- queries.append(item.get('需求详细query', ''))
- hooks.append(item.get('需求钩子话术', ''))
- except Exception:
- pass
- return '\n'.join(times), '\n'.join(queries), '\n'.join(hooks)
- # 新增三列
- new_times, new_queries, new_hooks = [], [], []
- for val in df.iloc[:, 8]:
- t, q, h = extract_fields(val)
- new_times.append(t)
- new_queries.append(q)
- new_hooks.append(h)
- df.insert(11, 'time', new_times)
- df.insert(12, 'query', new_queries)
- df.insert(13, 'hook', new_hooks)
- # 保存为新文件
- output_file = '视频分析报告1_拆分钩子.xlsx'
- df.to_excel(output_file, index=False)
- print(f'已保存到 {output_file}')
|
