批量拆分与合并excel表格.ipynb

{
 "metadata": {
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python37464bit996bc1b053e443969e72b61be9e433e9",
   "display_name": "Python 3.7.4 64-bit"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 使用Pandas批量拆分与合并excel表格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "os.chdir(r'H:\\Coding\\Python\\oper_excel')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "work_dir = r'.\\course_datas\\c15_excel_split_merge'\n",
    "# work_dir = '.\\\\course_datas\\\\c15_excel_split_merge'\n",
    "# work_dir = './course_datas/c15_excel_split_merge'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "splits_dir = f'{work_dir}\\\\split'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "'.\\\\course_datas\\\\c15_excel_split_merge\\\\split'"
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "source": [
    "splits_dir"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not os.path.exists(splits_dir):\n",
    "    os.mkdir(splits_dir)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "'H:\\\\Coding\\\\Python\\\\oper_excel'"
     },
     "metadata": {},
     "execution_count": 7
    }
   ],
   "source": [
    "pwd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_source = pd.read_excel(f\"{work_dir}\\\\crazyant_blog_articles_source.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "     id                 title                    tags\n0  2585  Tensorflow怎样接收变长列表特征  python,tensorflow,特征工程\n1  2583   Pandas实现数据的合并concat      pandas,python,数据分析\n2  2574  Pandas的Index索引有什么用途？      pandas,python,数据分析\n3  2564           机器学习常用数据集大全             python,机器学习\n4  2561          一个数据科学家的修炼路径                    数据分析",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>title</th>\n      <th>tags</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2585</td>\n      <td>Tensorflow怎样接收变长列表特征</td>\n      <td>python,tensorflow,特征工程</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2583</td>\n      <td>Pandas实现数据的合并concat</td>\n      <td>pandas,python,数据分析</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2574</td>\n      <td>Pandas的Index索引有什么用途？</td>\n      <td>pandas,python,数据分析</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2564</td>\n      <td>机器学习常用数据集大全</td>\n      <td>python,机器学习</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2561</td>\n      <td>一个数据科学家的修炼路径</td>\n      <td>数据分析</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 9
    }
   ],
   "source": [
    "df_source.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "RangeIndex(start=0, stop=258, step=1)"
     },
     "metadata": {},
     "execution_count": 10
    }
   ],
   "source": [
    "df_source.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 将一个Excel等份拆成多个Excel\n",
    "### 1. 使用df.iloc拆分一个大的dataframe\n",
    "### 2. 使用dataframe.to_excel保存每个小的excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Excel拆分给如下几个人\n",
    "user_names = ['lei', 'bei', 'ming', 'hong']\n",
    "total_row_count = df_source.index.size\n",
    "# total_row_count = df_source.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "split_size = total_row_count // len(user_names)\n",
    "if total_row_count % len(user_names) != 0:\n",
    "    split_size += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "65"
     },
     "metadata": {},
     "execution_count": 20
    }
   ],
   "source": [
    "split_size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_subs = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "for idx, user_name in enumerate(user_names):\n",
    "    # iloc的开始索引\n",
    "    begin = idx*split_size\n",
    "    # iloc的结束索引\n",
    "    end = begin + split_size\n",
    "    df_sub = df_source.iloc[begin:end]\n",
    "    df_subs.append((idx, user_name, df_sub))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "for idx, user_name, df_sub in df_subs:\n",
    "    file_name = f\"{splits_dir}/crazyant_blog_articles_{idx}_{user_name}.xlsx\"\n",
    "    df_sub.to_excel(file_name, index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 二、合并多个excel到一个excel文件\n",
    "1. 遍历文件夹，得到要合并的excel文件名列表\n",
    "2. 分别读取到dataframe，给每个df添加一列用于标识来源\n",
    "3. 使用pd.concat进行df批量合并\n",
    "4. 将合并后的dataframe输出到excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "['crazyant_blog_articles_0_lei.xlsx',\n 'crazyant_blog_articles_1_bei.xlsx',\n 'crazyant_blog_articles_2_ming.xlsx',\n 'crazyant_blog_articles_3_hong.xlsx']"
     },
     "metadata": {},
     "execution_count": 36
    }
   ],
   "source": [
    "import os\n",
    "excel_names = []\n",
    "for excel_name in os.listdir(splits_dir):\n",
    "    excel_names.append(excel_name)\n",
    "excel_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "file name is crazyant_blog_articles_0_lei.xlsx, user name is lei\nfile name is crazyant_blog_articles_1_bei.xlsx, user name is bei\nfile name is crazyant_blog_articles_2_ming.xlsx, user name is ming\nfile name is crazyant_blog_articles_3_hong.xlsx, user name is hong\n"
    }
   ],
   "source": [
    "df_list = []\n",
    "for excel_name in excel_names:\n",
    "    excel_path = f\"{splits_dir}/{excel_name}\"\n",
    "    df_split = pd.read_excel(excel_path)\n",
    "    username = excel_name.replace(\"crazyant_blog_articles_\", \"\").replace(\".xlsx\", \"\")[2:]\n",
    "    print(f'file name is {excel_name}, user name is {username}')\n",
    "    df_split['username'] = username\n",
    "    df_list.append(df_split)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged = pd.concat(df_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "(258, 4)"
     },
     "metadata": {},
     "execution_count": 43
    }
   ],
   "source": [
    "df_merged.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "     id                 title                    tags username\n0  2585  Tensorflow怎样接收变长列表特征  python,tensorflow,特征工程      lei\n1  2583   Pandas实现数据的合并concat      pandas,python,数据分析      lei\n2  2574  Pandas的Index索引有什么用途？      pandas,python,数据分析      lei\n3  2564           机器学习常用数据集大全             python,机器学习      lei\n4  2561          一个数据科学家的修炼路径                    数据分析      lei",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>title</th>\n      <th>tags</th>\n      <th>username</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2585</td>\n      <td>Tensorflow怎样接收变长列表特征</td>\n      <td>python,tensorflow,特征工程</td>\n      <td>lei</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2583</td>\n      <td>Pandas实现数据的合并concat</td>\n      <td>pandas,python,数据分析</td>\n      <td>lei</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2574</td>\n      <td>Pandas的Index索引有什么用途？</td>\n      <td>pandas,python,数据分析</td>\n      <td>lei</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2564</td>\n      <td>机器学习常用数据集大全</td>\n      <td>python,机器学习</td>\n      <td>lei</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2561</td>\n      <td>一个数据科学家的修炼路径</td>\n      <td>数据分析</td>\n      <td>lei</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 44
    }
   ],
   "source": [
    "df_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "ming    65\nbei     65\nlei     65\nhong    63\nName: username, dtype: int64"
     },
     "metadata": {},
     "execution_count": 45
    }
   ],
   "source": [
    "df_merged['username'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged.to_excel(f'{work_dir}/crazyant_blog_articles_merged.xlsx', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ]
}