86 lines
2.4 KiB
Plaintext
86 lines
2.4 KiB
Plaintext
{
|
|
"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
|
|
}
|