{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "initial_id",
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import re\n",
    "\n",
    "# 读取Excel文件\n",
    "path = r'C:\\Users\\Administrator.DESKTOP-7IC2USJ\\Desktop\\新建文件夹\\已使用套餐卡.xlsx'\n",
    "df = pd.read_excel(path)\n",
    "\n",
    "# 初始化结果列表\n",
    "result = []\n",
    "\n",
    "# 遍历每一行数据\n",
    "for index, row in df.iterrows():\n",
    "    # 获取套餐未使用项目列的值\n",
    "    projects = row['套餐未使用项目']\n",
    "\n",
    "    # 如果该列为空或无内容，直接跳过\n",
    "    if pd.isna(projects):\n",
    "        continue\n",
    "\n",
    "    # 使用换行符分割项目\n",
    "    project_list = projects.split('\\n')\n",
    "\n",
    "    # 计算拆分次数\n",
    "    split_count = len(project_list)\n",
    "\n",
    "    # 处理每一项\n",
    "    for project in project_list:\n",
    "        # 创建新行数据\n",
    "        new_row = row.copy()\n",
    "        new_row['套餐未使用项目'] = project.strip()  # 更新套餐未使用项目\n",
    "\n",
    "        # 如果存在套餐余额列，则计算余额\n",
    "        if '套餐余额' in df.columns and not pd.isna(row['套餐余额']):\n",
    "            new_row['余额'] = row['套餐余额'] / split_count\n",
    "        else:\n",
    "            new_row['余额'] = None  # 如果没有套餐余额列，则余额设为None\n",
    "\n",
    "        # 添加到结果列表\n",
    "        result.append(new_row)\n",
    "\n",
    "# 将结果转换为DataFrame\n",
    "result_df = pd.DataFrame(result)\n",
    "\n",
    "# 打印结果\n",
    "print(result_df)\n",
    "\n",
    "# 保存到新的Excel文件\n",
    "output_path = fr'C:\\Users\\Administrator.DESKTOP-7IC2USJ\\Desktop\\新建文件夹\\拆分_已使用套餐卡.xlsx'\n",
    "result_df.to_excel(output_path, index=False)"
   ]
  }
 ],
 "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
}