python语言处理：读取拆分excel中sheet的某列，保存原结构于新sheet中

问题描述：

​ 在工作中，接收到Excel文件数据如下图所示，某一列（下图C列）是按照特定字符串联的长字符，但是需求让我们把这列拆分，且在保留同一行其他列数据不变的情况下。

​ 这里提供python、sql脚本两种处理方式。处理后的需求数据如下图所示。

处理方式1：

​ 这里利用python，设计两个函数。其中，read_excel_sheet()读取函数，负责读取拆分数据；save_table_to_sheet()写入函数，负责保存拆分数据。具体脚本如下：

# -*- coding: utf-8 -*-
import datetime
import xlrd
from xlutils import copy
import numpy as np

def read_excel_sheet(read_file_name):
    # 01 读取excel数据
    try:
        # 打开excel表格
        data_excel = xlrd.open_workbook(r'{}'.format(read_file_name))
        table = data_excel.sheets()[0]      # 打开第一个sheet1
        # 返回某行中所有单元格的数据组成的列表
        row_data = table.row_values(0, start_colx=0, end_colx=None)
        n_rows = table.nrows  # 获取该sheet中的有效行数
        n_cols = table.ncols  # 获取该sheet中的有效列数

        # 定义存储列表
        data_list = []
        # 需要拆分的列为第3列
        for i in range(1, n_rows):
            # 定义未拆分前的 每行存储列表
            row_list = []

            cell0 = table.cell_value(rowx=i, colx=0)
            cell1 = table.cell_value(rowx=i, colx=1)
            cell2 = table.cell_value(rowx=i, colx=2)
            cell3 = table.cell_value(rowx=i, colx=3)

            column_3_list = cell2.split(',')    # 拆分第三列
            num_col_3 = len(column_3_list)      # 所拆分第三列的长度

            for j in range(num_col_3):
                split_row = [cell0,cell1,column_3_list[j],cell3]
                row_list.append(split_row)  # 列的拆分行加入row_list

            data_list.extend(row_list)  # 汇总行每一行扩展入data_list

        print(data_list)
        now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        print('{}>>> Excel数据已经读取，列数据已经拆分！'.format(now))
        return data_list
    except Exception as e:
        print(e)

def save_table_to_sheet(table_list,file_name):
    # 02 写入excel数据
    try:
        # book = xlwt.Workbook(encoding='utf-8', style_compression=0)  # 打开已有excel表格类型文件
        old_book = xlrd.open_workbook(r'{}'.format(read_file_name))
        new_book = copy.copy(old_book)      # 复制文本并保留格式
        sheet1 = new_book.get_sheet(1)      # 打开表单

        table = old_book.sheets()[0]  # 打开第一个sheet1
        # 返回某行中所有单元格的数据组成的列表——标题行
        row_title = table.row_values(0, start_colx=0, end_colx=None)
        table_list.insert(0,row_title)  # 首行插入标题行

        # 列名称设置
        # col_name = table_list[0]
        table_list_num = np.array(table_list)  # np_var_sheet2
        rows = table_list_num.shape[0]  # 输出行
        cols = table_list_num.shape[1]  # 输出列
        # 数据写入
        for i in range(0, rows):
            for j in range(0, cols):
                sheet1.write(i, j, table_list[i][j])
        now = datetime.datetime.now().strftime('%Y%m%d %H:%M:%S')
        new_book.save(file_name)
        print('{}>>> 数据已经写入！'.format(now))
    except Exception as e:
        print(e)

if __name__=='__main__':
    read_file_name = r'D:\test_data\拆分列数据.xls'
    data_list = read_excel_sheet(read_file_name)
    save_table_to_sheet(data_list,read_file_name)

处理方式2：

​ 这里利用mysql，进行数据列拆分，为了方便（作者本人）以本地pyspark模拟mysql环境，进行sql脚本的测试。主要思想是：

• 首先，借助pandas读取Excel文件，并转化为rdd
• 然后，创建两个sql视图：tmp_table主表、help_topic辅助表
• 最后，进行sql执行，再转为pandas并保存为Excel

具体脚本如下：

# -*- coding: utf-8 -*-
import datetime
from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StringType, IntegerType
import pandas as pd
from pyspark.sql import functions as F


if __name__ == '__main__':
    # 0. 构建执行环境入口对象SparkSession
    spark = SparkSession.builder.\
        appName("test").\
        master("local[*]").\
        config("spark.sql.shuffle.partitions", 2).\
        getOrCreate()
    sc = spark.sparkContext

    # 1. 构建rdd
    # 利用pandas读取excel文件，构建一个rdd
    pd_df = pd.read_excel(r'D:\test_data\拆分列数据.xls', sheet_name='Sheet1')
    # 转化为spark格式
    df = spark.createDataFrame(pd_df)

    #  创建另一个rdd，模拟mysql数据库中的mysql.help_topic表的id
    data_list = []
    for i in range(1000):
        data_list.append(i)
    pd_2_df = pd.DataFrame(data_list)
    df_id = spark.createDataFrame(pd_2_df, schema=['help_topic_id'])

    #  创建sql视图1
    df.createOrReplaceTempView('tmp_table')

    # 2. 创建视图
    #  创建sql视图2
    df_id.createOrReplaceTempView('help_topic')

    # 3. sql脚本执行
    result = spark.sql("""
        SELECT xh, table_name,
            SUBSTRING_INDEX(SUBSTRING_INDEX(tb.column_names,',',b.help_topic_id + 1),',',- 1) AS column_names
        FROM
            (SELECT * FROM tmp_table) tb
            JOIN help_topic b ON b.help_topic_id <
             (LENGTH(tb.column_names) - LENGTH(
            REPLACE(tb.column_names, ',', '')) + 1)
        ;
    """).show(truncate=False)


    result_rdd = spark.sql("""
            SELECT xh, table_name,
                SUBSTRING_INDEX(SUBSTRING_INDEX(tb.column_names,',',b.help_topic_id + 1),',',- 1) AS column_names
            FROM
                (SELECT * FROM tmp_table) tb
                JOIN help_topic b ON b.help_topic_id <
                 (LENGTH(tb.column_names) - LENGTH(
                REPLACE(tb.column_names, ',', '')) + 1)
            ;
        """)

    # 4. 数据保存
    #  pandas.DataFrame 转换成 spark.DataFrame
    pandas_df = result_rdd.toPandas()

    #  利用pandas进行保存至Excel中
    pandas_df.to_excel(r'D:\test_data\拆分列数据.xls',sheet_name='Sheet3',
                      index=False)

    now = datetime.datetime.now().strftime('%Y%m%d %H:%M:%S')
    print('{}>>>> 数据已经保存'.format(now))

小贴士：

在真实的mysql环境下，脚本为：

SELECT 
	xh, table_name,
    SUBSTRING_INDEX(SUBSTRING_INDEX(tb.column_names,',',b.help_topic_id + 1),',',- 1) AS column_names
FROM
    (SELECT * FROM tmp_table) tb
     JOIN mysql.help_topic b ON b.help_topic_id <
     (LENGTH(tb.column_names) - LENGTH(
     REPLACE(tb.column_names, ',', '')) + 1)
        ;