39. Pandas处理Excel复杂多列到多行转换.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas处理Excel - 复杂多列到多行转换"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 用户需求图片\n",
    "<img src=\"./course_datas/c39_explode_to_manyrows/用户需求图片.png\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 分析：\n",
    "1. 一行变多行，可以用explode实现；\n",
    "2. 要使用explode，需要先将多列变成一列；\n",
    "3. 注意有的列为空，需要做空值过滤；"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. 读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = \"./course_datas/c39_explode_to_manyrows/读者提供的数据-输入.xlsx\"\n",
    "df = pd.read_excel(file_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>P/N</th>\n",
       "      <th>Description</th>\n",
       "      <th>Supplier</th>\n",
       "      <th>Supplier PN</th>\n",
       "      <th>Supplier.1</th>\n",
       "      <th>Supplier PN.1</th>\n",
       "      <th>Supplier.2</th>\n",
       "      <th>Supplier PN.2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>302-462-326</td>\n",
       "      <td>CAP CER 0402 100pF 5% 50V</td>\n",
       "      <td>MURATA</td>\n",
       "      <td>GRM1555C1H101JA01D</td>\n",
       "      <td>YAGEO</td>\n",
       "      <td>CC0402JRNPO9BN101</td>\n",
       "      <td>GRM1555C1H101JA01J</td>\n",
       "      <td>Murata Electronics North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>302-462-012</td>\n",
       "      <td>CAP CER 0402 6.8pF 0.25pF 50V</td>\n",
       "      <td>AVX Corporation</td>\n",
       "      <td>04025A6R8CAT2A</td>\n",
       "      <td>KEMET</td>\n",
       "      <td>C0402C689C5GACTU</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>302-462-009</td>\n",
       "      <td>CAP CER 0402 3.9pF 0.25pF 50V</td>\n",
       "      <td>AVX Corporation</td>\n",
       "      <td>04025A3R9CAT2A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           P/N                    Description         Supplier  \\\n",
       "0  302-462-326      CAP CER 0402 100pF 5% 50V           MURATA   \n",
       "1  302-462-012  CAP CER 0402 6.8pF 0.25pF 50V  AVX Corporation   \n",
       "2  302-462-009  CAP CER 0402 3.9pF 0.25pF 50V  AVX Corporation   \n",
       "\n",
       "          Supplier PN Supplier.1      Supplier PN.1          Supplier.2  \\\n",
       "0  GRM1555C1H101JA01D      YAGEO  CC0402JRNPO9BN101  GRM1555C1H101JA01J   \n",
       "1      04025A6R8CAT2A      KEMET   C0402C689C5GACTU                 NaN   \n",
       "2      04025A3R9CAT2A        NaN                NaN                 NaN   \n",
       "\n",
       "                      Supplier PN.2  \n",
       "0  Murata Electronics North America  \n",
       "1                               NaN  \n",
       "2                               NaN  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. 把多列合并到一列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Supplier',\n",
       " 'Supplier PN',\n",
       " 'Supplier.1',\n",
       " 'Supplier PN.1',\n",
       " 'Supplier.2',\n",
       " 'Supplier PN.2']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 提取待合并的所有列名，一会可以把它们drop掉\n",
    "merge_names = list(df.loc[:, \"Supplier\":].columns.values)\n",
    "merge_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>P/N</th>\n",
       "      <th>Description</th>\n",
       "      <th>Supplier</th>\n",
       "      <th>Supplier PN</th>\n",
       "      <th>Supplier.1</th>\n",
       "      <th>Supplier PN.1</th>\n",
       "      <th>Supplier.2</th>\n",
       "      <th>Supplier PN.2</th>\n",
       "      <th>merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>302-462-326</td>\n",
       "      <td>CAP CER 0402 100pF 5% 50V</td>\n",
       "      <td>MURATA</td>\n",
       "      <td>GRM1555C1H101JA01D</td>\n",
       "      <td>YAGEO</td>\n",
       "      <td>CC0402JRNPO9BN101</td>\n",
       "      <td>GRM1555C1H101JA01J</td>\n",
       "      <td>Murata Electronics North America</td>\n",
       "      <td>MURATA|GRM1555C1H101JA01D#YAGEO|CC0402JRNPO9BN...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>302-462-012</td>\n",
       "      <td>CAP CER 0402 6.8pF 0.25pF 50V</td>\n",
       "      <td>AVX Corporation</td>\n",
       "      <td>04025A6R8CAT2A</td>\n",
       "      <td>KEMET</td>\n",
       "      <td>C0402C689C5GACTU</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AVX Corporation|04025A6R8CAT2A#KEMET|C0402C689...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>302-462-009</td>\n",
       "      <td>CAP CER 0402 3.9pF 0.25pF 50V</td>\n",
       "      <td>AVX Corporation</td>\n",
       "      <td>04025A3R9CAT2A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AVX Corporation|04025A3R9CAT2A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           P/N                    Description         Supplier  \\\n",
       "0  302-462-326      CAP CER 0402 100pF 5% 50V           MURATA   \n",
       "1  302-462-012  CAP CER 0402 6.8pF 0.25pF 50V  AVX Corporation   \n",
       "2  302-462-009  CAP CER 0402 3.9pF 0.25pF 50V  AVX Corporation   \n",
       "\n",
       "          Supplier PN Supplier.1      Supplier PN.1          Supplier.2  \\\n",
       "0  GRM1555C1H101JA01D      YAGEO  CC0402JRNPO9BN101  GRM1555C1H101JA01J   \n",
       "1      04025A6R8CAT2A      KEMET   C0402C689C5GACTU                 NaN   \n",
       "2      04025A3R9CAT2A        NaN                NaN                 NaN   \n",
       "\n",
       "                      Supplier PN.2  \\\n",
       "0  Murata Electronics North America   \n",
       "1                               NaN   \n",
       "2                               NaN   \n",
       "\n",
       "                                               merge  \n",
       "0  MURATA|GRM1555C1H101JA01D#YAGEO|CC0402JRNPO9BN...  \n",
       "1  AVX Corporation|04025A6R8CAT2A#KEMET|C0402C689...  \n",
       "2                     AVX Corporation|04025A3R9CAT2A  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def merge_cols(x):\n",
    "    \"\"\"\n",
    "    x是一个行Series，把它们按分隔符合并\n",
    "    \"\"\"\n",
    "    # 删除为空的列\n",
    "    x = x[x.notna()]\n",
    "    # 使用x.values用于合并\n",
    "    y = x.values\n",
    "    # 合并后的列表，每个元素是\"Supplier\" + \"Supplier PN\"对\n",
    "    result = []\n",
    "    # range的步长为2，目的是每两列做合并\n",
    "    for idx in range(0, len(y), 2):\n",
    "        # 使用竖线作为\"Supplier\" + \"Supplier PN\"之间的分隔符\n",
    "        result.append(f\"{y[idx]}|{y[idx+1]}\")\n",
    "    # 将所有两两对，用#分割，返回一个大字符串\n",
    "    return \"#\".join(result)\n",
    "\n",
    "# 添加新列，把待合并的所有列变成一个大字符串\n",
    "df[\"merge\"] = df.loc[:, \"Supplier\":].apply(merge_cols, axis=1)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>P/N</th>\n",
       "      <th>Description</th>\n",
       "      <th>merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>302-462-326</td>\n",
       "      <td>CAP CER 0402 100pF 5% 50V</td>\n",
       "      <td>MURATA|GRM1555C1H101JA01D#YAGEO|CC0402JRNPO9BN...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>302-462-012</td>\n",
       "      <td>CAP CER 0402 6.8pF 0.25pF 50V</td>\n",
       "      <td>AVX Corporation|04025A6R8CAT2A#KEMET|C0402C689...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>302-462-009</td>\n",
       "      <td>CAP CER 0402 3.9pF 0.25pF 50V</td>\n",
       "      <td>AVX Corporation|04025A3R9CAT2A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           P/N                    Description  \\\n",
       "0  302-462-326      CAP CER 0402 100pF 5% 50V   \n",
       "1  302-462-012  CAP CER 0402 6.8pF 0.25pF 50V   \n",
       "2  302-462-009  CAP CER 0402 3.9pF 0.25pF 50V   \n",
       "\n",
       "                                               merge  \n",
       "0  MURATA|GRM1555C1H101JA01D#YAGEO|CC0402JRNPO9BN...  \n",
       "1  AVX Corporation|04025A6R8CAT2A#KEMET|C0402C689...  \n",
       "2                     AVX Corporation|04025A3R9CAT2A  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 把不用的列删除掉\n",
    "df.drop(merge_names, axis=1, inplace=True)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. 使用explode把一列变多行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>P/N</th>\n",
       "      <th>Description</th>\n",
       "      <th>merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>302-462-326</td>\n",
       "      <td>CAP CER 0402 100pF 5% 50V</td>\n",
       "      <td>[MURATA|GRM1555C1H101JA01D, YAGEO|CC0402JRNPO9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>302-462-012</td>\n",
       "      <td>CAP CER 0402 6.8pF 0.25pF 50V</td>\n",
       "      <td>[AVX Corporation|04025A6R8CAT2A, KEMET|C0402C6...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>302-462-009</td>\n",
       "      <td>CAP CER 0402 3.9pF 0.25pF 50V</td>\n",
       "      <td>[AVX Corporation|04025A3R9CAT2A]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           P/N                    Description  \\\n",
       "0  302-462-326      CAP CER 0402 100pF 5% 50V   \n",
       "1  302-462-012  CAP CER 0402 6.8pF 0.25pF 50V   \n",
       "2  302-462-009  CAP CER 0402 3.9pF 0.25pF 50V   \n",
       "\n",
       "                                               merge  \n",
       "0  [MURATA|GRM1555C1H101JA01D, YAGEO|CC0402JRNPO9...  \n",
       "1  [AVX Corporation|04025A6R8CAT2A, KEMET|C0402C6...  \n",
       "2                   [AVX Corporation|04025A3R9CAT2A]  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 先将merge列变成list的形式\n",
    "df[\"merge\"] = df[\"merge\"].str.split(\"#\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>P/N</th>\n",
       "      <th>Description</th>\n",
       "      <th>merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>302-462-326</td>\n",
       "      <td>CAP CER 0402 100pF 5% 50V</td>\n",
       "      <td>MURATA|GRM1555C1H101JA01D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>302-462-326</td>\n",
       "      <td>CAP CER 0402 100pF 5% 50V</td>\n",
       "      <td>YAGEO|CC0402JRNPO9BN101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>302-462-326</td>\n",
       "      <td>CAP CER 0402 100pF 5% 50V</td>\n",
       "      <td>GRM1555C1H101JA01J|Murata Electronics North Am...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>302-462-012</td>\n",
       "      <td>CAP CER 0402 6.8pF 0.25pF 50V</td>\n",
       "      <td>AVX Corporation|04025A6R8CAT2A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>302-462-012</td>\n",
       "      <td>CAP CER 0402 6.8pF 0.25pF 50V</td>\n",
       "      <td>KEMET|C0402C689C5GACTU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>302-462-009</td>\n",
       "      <td>CAP CER 0402 3.9pF 0.25pF 50V</td>\n",
       "      <td>AVX Corporation|04025A3R9CAT2A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           P/N                    Description  \\\n",
       "0  302-462-326      CAP CER 0402 100pF 5% 50V   \n",
       "0  302-462-326      CAP CER 0402 100pF 5% 50V   \n",
       "0  302-462-326      CAP CER 0402 100pF 5% 50V   \n",
       "1  302-462-012  CAP CER 0402 6.8pF 0.25pF 50V   \n",
       "1  302-462-012  CAP CER 0402 6.8pF 0.25pF 50V   \n",
       "2  302-462-009  CAP CER 0402 3.9pF 0.25pF 50V   \n",
       "\n",
       "                                               merge  \n",
       "0                          MURATA|GRM1555C1H101JA01D  \n",
       "0                            YAGEO|CC0402JRNPO9BN101  \n",
       "0  GRM1555C1H101JA01J|Murata Electronics North Am...  \n",
       "1                     AVX Corporation|04025A6R8CAT2A  \n",
       "1                             KEMET|C0402C689C5GACTU  \n",
       "2                     AVX Corporation|04025A3R9CAT2A  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 执行explode变成多行\n",
    "df_explode = df.explode(\"merge\")\n",
    "df_explode"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. 将一列还原成结果的多列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>P/N</th>\n",
       "      <th>Description</th>\n",
       "      <th>merge</th>\n",
       "      <th>Supplier</th>\n",
       "      <th>Supplier PN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>302-462-326</td>\n",
       "      <td>CAP CER 0402 100pF 5% 50V</td>\n",
       "      <td>MURATA|GRM1555C1H101JA01D</td>\n",
       "      <td>MURATA</td>\n",
       "      <td>GRM1555C1H101JA01D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>302-462-326</td>\n",
       "      <td>CAP CER 0402 100pF 5% 50V</td>\n",
       "      <td>YAGEO|CC0402JRNPO9BN101</td>\n",
       "      <td>YAGEO</td>\n",
       "      <td>CC0402JRNPO9BN101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>302-462-326</td>\n",
       "      <td>CAP CER 0402 100pF 5% 50V</td>\n",
       "      <td>GRM1555C1H101JA01J|Murata Electronics North Am...</td>\n",
       "      <td>GRM1555C1H101JA01J</td>\n",
       "      <td>Murata Electronics North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>302-462-012</td>\n",
       "      <td>CAP CER 0402 6.8pF 0.25pF 50V</td>\n",
       "      <td>AVX Corporation|04025A6R8CAT2A</td>\n",
       "      <td>AVX Corporation</td>\n",
       "      <td>04025A6R8CAT2A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>302-462-012</td>\n",
       "      <td>CAP CER 0402 6.8pF 0.25pF 50V</td>\n",
       "      <td>KEMET|C0402C689C5GACTU</td>\n",
       "      <td>KEMET</td>\n",
       "      <td>C0402C689C5GACTU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>302-462-009</td>\n",
       "      <td>CAP CER 0402 3.9pF 0.25pF 50V</td>\n",
       "      <td>AVX Corporation|04025A3R9CAT2A</td>\n",
       "      <td>AVX Corporation</td>\n",
       "      <td>04025A3R9CAT2A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           P/N                    Description  \\\n",
       "0  302-462-326      CAP CER 0402 100pF 5% 50V   \n",
       "0  302-462-326      CAP CER 0402 100pF 5% 50V   \n",
       "0  302-462-326      CAP CER 0402 100pF 5% 50V   \n",
       "1  302-462-012  CAP CER 0402 6.8pF 0.25pF 50V   \n",
       "1  302-462-012  CAP CER 0402 6.8pF 0.25pF 50V   \n",
       "2  302-462-009  CAP CER 0402 3.9pF 0.25pF 50V   \n",
       "\n",
       "                                               merge            Supplier  \\\n",
       "0                          MURATA|GRM1555C1H101JA01D              MURATA   \n",
       "0                            YAGEO|CC0402JRNPO9BN101               YAGEO   \n",
       "0  GRM1555C1H101JA01J|Murata Electronics North Am...  GRM1555C1H101JA01J   \n",
       "1                     AVX Corporation|04025A6R8CAT2A     AVX Corporation   \n",
       "1                             KEMET|C0402C689C5GACTU               KEMET   \n",
       "2                     AVX Corporation|04025A3R9CAT2A     AVX Corporation   \n",
       "\n",
       "                        Supplier PN  \n",
       "0                GRM1555C1H101JA01D  \n",
       "0                 CC0402JRNPO9BN101  \n",
       "0  Murata Electronics North America  \n",
       "1                    04025A6R8CAT2A  \n",
       "1                  C0402C689C5GACTU  \n",
       "2                    04025A3R9CAT2A  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 分别从merge中提取两列\n",
    "df_explode[\"Supplier\"]=df_explode[\"merge\"].str.split(\"|\").str[0]\n",
    "df_explode[\"Supplier PN\"]=df_explode[\"merge\"].str.split(\"|\").str[1]\n",
    "df_explode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>P/N</th>\n",
       "      <th>Description</th>\n",
       "      <th>Supplier</th>\n",
       "      <th>Supplier PN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>302-462-326</td>\n",
       "      <td>CAP CER 0402 100pF 5% 50V</td>\n",
       "      <td>MURATA</td>\n",
       "      <td>GRM1555C1H101JA01D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>302-462-326</td>\n",
       "      <td>CAP CER 0402 100pF 5% 50V</td>\n",
       "      <td>YAGEO</td>\n",
       "      <td>CC0402JRNPO9BN101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>302-462-326</td>\n",
       "      <td>CAP CER 0402 100pF 5% 50V</td>\n",
       "      <td>GRM1555C1H101JA01J</td>\n",
       "      <td>Murata Electronics North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>302-462-012</td>\n",
       "      <td>CAP CER 0402 6.8pF 0.25pF 50V</td>\n",
       "      <td>AVX Corporation</td>\n",
       "      <td>04025A6R8CAT2A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>302-462-012</td>\n",
       "      <td>CAP CER 0402 6.8pF 0.25pF 50V</td>\n",
       "      <td>KEMET</td>\n",
       "      <td>C0402C689C5GACTU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>302-462-009</td>\n",
       "      <td>CAP CER 0402 3.9pF 0.25pF 50V</td>\n",
       "      <td>AVX Corporation</td>\n",
       "      <td>04025A3R9CAT2A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           P/N                    Description            Supplier  \\\n",
       "0  302-462-326      CAP CER 0402 100pF 5% 50V              MURATA   \n",
       "0  302-462-326      CAP CER 0402 100pF 5% 50V               YAGEO   \n",
       "0  302-462-326      CAP CER 0402 100pF 5% 50V  GRM1555C1H101JA01J   \n",
       "1  302-462-012  CAP CER 0402 6.8pF 0.25pF 50V     AVX Corporation   \n",
       "1  302-462-012  CAP CER 0402 6.8pF 0.25pF 50V               KEMET   \n",
       "2  302-462-009  CAP CER 0402 3.9pF 0.25pF 50V     AVX Corporation   \n",
       "\n",
       "                        Supplier PN  \n",
       "0                GRM1555C1H101JA01D  \n",
       "0                 CC0402JRNPO9BN101  \n",
       "0  Murata Electronics North America  \n",
       "1                    04025A6R8CAT2A  \n",
       "1                  C0402C689C5GACTU  \n",
       "2                    04025A3R9CAT2A  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 把merge列删除掉，得到最终数据\n",
    "df_explode.drop(\"merge\", axis=1, inplace=True)\n",
    "df_explode"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. 输出到结果Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_explode.to_excel(\"./course_datas/c39_explode_to_manyrows/读者提供的数据-输出.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}