{ "cells": [ { "metadata": {}, "cell_type": "markdown", "source": "宜搭导出文件字段对应", "id": "ccd76344cc98d6e6" }, { "cell_type": "code", "id": "initial_id", "metadata": { "collapsed": true, "ExecuteTime": { "end_time": "2026-03-07T02:04:58.912156600Z", "start_time": "2026-03-07T02:04:55.474219900Z" } }, "source": [ "import pandas as pd\n", "import numpy as np\n", "from datetime import datetime\n", "\n", "\n", "# 读取旧数据,记得排除下有订单的\n", "old_df = pd.read_excel(fr\"C:\\Users\\hp_z66\\OneDrive\\Desktop\\续约服务数据合并报告_20260307.xlsx\",sheet_name = \"今天前30及后120\") # 或 read_csv\n", "\n", "# 初始化新 DataFrame（列按新表头）\n", "new_columns = [\n", " \"关联数据\", \"公司名称\", \"门店名称\", \"门店编码\", \"加盟商\", \"过期日\", \"Saas版本\",\n", " \"上次购买价格\", \"联系人\", \"联系手机号\", \"运营顾问\", \"订单商品名称\",\n", " \"120天是否跟进\", \"120天处理人\", \"120天跟进时间\",\n", " \"60天是否跟进\", \"60天处理人\", \"60天跟进时间\",\n", " \"30天是否跟进\", \"30天处理人\", \"30天跟进时间\",\n", " \"是否联系上\", \"现阶段问题\", \"未联系原因\", \"联系情况及问题说明\", \"回访方式\",\n", " \"潜在商机\", \"商机归属\", \"商机详情\", \"续约意愿\", \"不续约原因\",\n", " \"产品问题\", \"服务问题\", \"门店问题\", \"价格问题\", \"不续约具体情况说明\",\n", " \"周期性增购\", \"连锁门店待办同步处理\", \"选择需要同步的门店名称\",\n", " \"订单编码\", \"订单支付日期\", \"本次-实付金额（元）\", \"业务类型（续约、升级）\",\n", " \"120天自动流转时间\", \"60天自动流转时间\", \"30天自动流转时间\", \"0天自动流转时间\",\n", " \"当前所处节点\", \"流程状态\", \"经营模式\", \"程序关停\", \"区域客服\", \"公司等级\",\n", " \"运营专家\", \"流水号\", \"是否已同步宜搭\", \"连锁店数据同步辅助：门店列表\",\n", " \"公司id\", \"提交人\", \"提交时间\", \"更新时间\", \"流程状态\", \"当前节点\", \"当前负责人\",\n", " \"商品名称\", \"购买数量\", \"购买金额\", \"应续约日\", \"是否愿意续约\", \"不续约原因\", \"续约后订单编码\"\n", "]\n", "\n", "new_df = pd.DataFrame(columns=new_columns)\n", "\n", "# 字段映射\n", "mapping = {\n", " \"门店名称\": \"门店名称\",\n", " \"门店编码\": \"门店编码\",\n", " \"加盟商\": \"加盟商\",\n", " \"过期日\": \"过期日\",\n", " \"Saas版本\": \"Saas版本\",\n", " \"上次购买价格\": \"上次购买价格\",\n", " \"联系人\": \"联系人\",\n", " \"联系手机号\": \"联系手机号\",\n", " \"专属运营顾问\": \"运营顾问\",\n", " \"订单编码\": \"订单编码\",\n", " \"订单支付日期\": \"订单支付日期\",\n", " \"客户等级\": \"公司等级\",\n", " \"联系情况及问题说明\": \"联系情况及问题说明\",\n", " \"产品原因\": \"产品问题\",\n", " \"服务问题\": \"服务问题\",\n", " \"门店原因\": \"门店问题\",\n", " \"价格原因\": \"价格问题\",\n", " \"不续约具体情况说明\": \"不续约具体情况说明\",\n", " \"公司名称\": \"公司名称\",\n", " \"经营模式\": \"经营模式\",\n", " \"区域客服\": \"区域客服\",\n", " \"提交人\": \"提交人\",\n", " \"创建时间\": \"提交时间\",\n", " \"最近修改时间\": \"更新时间\",\n", " \"应续约日\": \"应续约日\",\n", " \"区域经理\": \"运营专家\", # 假设区域经理作为运营专家\n", " \"120天处理人\":\"120天是否跟进\",\n", " \"60天处理人\":\"60天是否跟进\",\n", " \"30天处理人\":\"30天是否跟进\",\n", " \"到期前120天联系情况_提交人\":\"120天处理人\",\n", " \"到期前60天联系情况_提交人\":\"60天处理人\",\n", " \"到期前30天联系情况_提交人\":\"30天处理人\",\n", " \"到期前120天联系情况_日期\":\"120天跟进时间\",\n", " \"到期前60天联系情况_日期\":\"60天跟进时间\",\n", " \"到期前30天联系情况_日期\":\"30天跟进时间\",\n", "}\n", "\n", "# 处理每一行\n", "def map_row(row):\n", " new_row = {col: None for col in new_columns}\n", "\n", " # 基础映射\n", " for old_col, new_col in mapping.items():\n", " if old_col in row.index:\n", " new_row[new_col] = row[old_col]\n", "\n", " # 自动流转时间\n", " new_row['120天自动流转时间'] = new_row['过期日'] - pd.Timedelta(days=60)\n", " new_row['60天自动流转时间'] = new_row['过期日'] - pd.Timedelta(days=30)\n", " new_row['30天自动流转时间'] = new_row['过期日'] - pd.Timedelta(days=0)\n", " new_row['0天自动流转时间'] = new_row['过期日'] + pd.Timedelta(days=90)\n", "\n", "\n", " # 是否联系上：旧表有“是否联系上客户”\n", " contacted = row.get(\"是否联系上客户\", None)\n", " if pd.notna(contacted):\n", " new_row[\"是否联系上\"] = contacted\n", " else:\n", " # 或根据联系情况推断\n", " new_row[\"是否联系上\"] = \"是\" if pd.notna(row.get(\"联系情况及问题说明\")) else \"否\"\n", "\n", " # 续约意愿\n", " willing = row.get(\"门店是否愿意续约\", None)\n", " new_row[\"是否愿意续约\"] = willing\n", " new_row[\"续约意愿\"] = \"愿意\" if willing == \"是\" else (\"不愿意\" if willing == \"否\" else willing)\n", "\n", " # 不续约原因（合并分类）\n", " reasons = []\n", " if row.get(\"产品原因\") == \"是\": reasons.append(\"产品问题\")\n", " if row.get(\"服务问题\") == \"是\": reasons.append(\"服务问题\")\n", " if row.get(\"门店原因\") == \"是\": reasons.append(\"门店问题\")\n", " if row.get(\"价格原因\") == \"是\": reasons.append(\"价格问题\")\n", " new_row[\"不续约原因\"] = \"; \".join(reasons) if reasons else None\n", "\n", " # 流程状态：根据实例状态或审批结果\n", " status = row.get(\"实例状态\", \"\")\n", " if \"完成\" in str(status) or \"通过\" in str(status):\n", " new_row[\"流程状态\"] = \"已完成\"\n", " elif \"终止\" in str(status) or \"拒绝\" in str(status):\n", " new_row[\"流程状态\"] = \"已终止\"\n", " else:\n", " new_row[\"流程状态\"] = \"进行中\"\n", "\n", " new_row[\"当前所处节点\"] = row.get(\"当前审批节点\", None)\n", " new_row[\"当前节点\"] = new_row[\"当前所处节点\"]\n", "\n", " # 默认值填充\n", " new_row[\"业务类型（续约、升级）\"] = \"续约\"\n", " new_row[\"回访方式\"] = \"电话\"\n", " new_row[\"程序关停\"] = False\n", " new_row[\"是否已同步宜搭\"] = False\n", " new_row[\"商品名称\"] = \"SaaS服务\"\n", " new_row[\"购买数量\"] = 1\n", " new_row[\"购买金额\"] = row.get(\"上次购买价格\", None)\n", "\n", " return pd.Series(new_row)\n", "\n", "# 应用映射\n", "new_df = old_df.apply(map_row, axis=1)\n", "\n", "# 保存\n", "new_df.to_excel(\"new_table_migrated.xlsx\", index=False)\n", "print(\"迁移完成！\")" ], "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "迁移完成！\n" ] } ], "execution_count": 4 } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 2 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython2", "version": "2.7.6" } }, "nbformat": 4, "nbformat_minor": 5 }