分享2个Python处理Excel的脚本

2021-09-07 02:12:27

一、写在前面

来源于两个读者的学习/工作需求，很巧，这两个读者提的需求都是关于批量合并sheet（检索需要的信息）。

本文所用数据样式，经读者同意，可以公开，文件中的数据为Excel中的Rand函数生成，确保大家在学习、使用代码过程中不会遇到障碍，数据和代码获取方式见文末。

二、基本知识概要

pandas创建一个DataFrame对象

pd.DataFrame()

pandas datafrmae索引

按列名索引：dataframe[列名]
按列值索引：dataframe[dataframe[列名]==列值]

pandas 读取、存储excel文件，存储csv文件

read_excel、to_excel、to_csv

pandas datafrmae根据缩影取指定行数据

dataframe.loc[list]

pandas datafrmae修改列名

dataframe.rename(columns={‘column_name_old’:‘column_name_new’})

pandas datafrmae将数据插入到指定列

dataframe.insert(loc=列序号,column=列名,value=列值)

pandas datafrmae根据列名删除指定列

dataframe.drop([列名],axis=1)

pandas 连接多个datafrmae

pd.concat([df_1, df_2])

三、开始动手动脑

3.1 第一个读者需求

首先我们先看第一个读者的需求：原始数据有18个Excel文件，每个Excel文件里有34个sheet（34个省的相关数据），需要取出每个sheet中指定的几行数据，然后全部合并起来，存储到一个新的文件，命名为2000_2017年各省份碳排放数据。

经过沟通，我确定了最终输出文件的样式，以下数据都是用Excel中的随机函数生成：

完成这个需求，如果是手动操作我们需要完成以下几个步骤：

0、新建一个Excel

1、打开第一个Excel
2、复制出每个sheet中需要的几行数据
3、将复制出来的数据粘贴到新建的Excel中
4、重复1-3，直到取出所有Excel中的数据
5、保存新建的Excel

如果只是1-2个文件，动手还可以接受，但是要是有几十个，几百个，如果靠动手就头大了。
现在我们看看以上手动操作换成代码操作需要那些步骤：

0、新建一个数据存储对象（我们用pandas中的Dataframe）

1、读取目标Excel文件
2、遍历取出每个sheet中需要的几行数据，存储到新建的Dataframe中
3、for循环遍历，读取所有目标Excel数据，并存储到新建的Dataframe中
4、将新建的Dataframe数据保存为一个Excel文件

了解了这些后，我们就开始愉快的代码之旅吧：

0、新建一个数据存储对象（我们用pandas中的Dataframe）

df_concat = pd.DataFrame()
复制代码

1、读取目标Excel文件
文件一共有18个文件，文件名也是有规则的。

file_path = 'data/2000年-2017年碳排放清单/2000年30个省份排放清单.xlsx'
data = pd.read_excel(file_path, sheet_name=None)
复制代码

2、遍历取出每个sheet中需要的几行数据，存储到新建的Dataframe中
为了代码的可读性，这里写了一个函数get_sheet_data来取出单个sheet中需要的数据，然后for循环遍历所有的sheet。

'''
取出单个sheet中需要的数据
'''
def get_sheet_data(data, sheet_name, year):
    # 取需要的几行数据
    df_concat = data[sheet_name].loc[[2,3,48,49]]
    # 给 Unnamed: 0 列进行重命名
    df_concat = df_concat.rename(columns={'Unnamed: 0':'类别'})
    # 插入两列数据 省份	年份
    df_concat.insert(loc=0,column='省份',value=sheet_name)
    df_concat.insert(loc=1,column='年份',value=i)
    # 将Total这列移动到第四列
    df_temp = df_concat['Total']
    df_concat = df_concat.drop(['Total'],axis=1)  # 先删除该列
    df_concat.insert(loc=3,column='Total',value=df_temp)  # 然后插入到第四列位置
    return df_concat

for sheet_name in list(data.keys()):
        if sheet_name == 'Sum':
            continue
        df_temp = get_sheet_data(data, sheet_name, year)
        df_concat = pd.concat([df_concat, df_temp])
复制代码

3、for循环遍历，读取所有目标Excel数据，并存储到新建的Dataframe中
在上一步，已经读取出了单个Excel中的所有sheet，现在再利用for循环遍历读取所有Excel中的数据。

'''
取出单个Excel中需要的数据
'''
def get_excel_data(data, year):
    df_concat = pd.DataFrame()
    for sheet_name in list(data.keys()):
        if sheet_name == 'Sum':
            continue
        df_temp = get_sheet_data(data, sheet_name, year)
        df_concat = pd.concat([df_concat, df_temp])
    return df_concat

# 生成一个列表，存储时间
date_year = [str(i) for i in range(2000, 2018)]
for i in date_year:
    file_path = 'data/2000年-2017年碳排放清单/%s年30个省份排放清单.xlsx'%i
    data = pd.read_excel(file_path, sheet_name=None)
    df_temp = get_excel_data(data, i)
    df_concat = pd.concat([df_concat, df_temp])
复制代码

4、将新建的Dataframe数据保存为一个Excel文件
这里直接调用pandas内置的to_excel函数，第一个参数为文件存储目录，第二个参数为sheet_name，第三个参数是编码格式，这里指定为utf-8。

print("开始存储数据")
df_concat.to_excel("data/2000_2017年省份碳排放数据.xlsx", "2000_2017", index=None, encoding="utf-8")
print("数据保存成功")
复制代码

完整代码如下：

import pandas as pd
import time

'''
取出单个sheet中需要的数据
'''
def get_sheet_data(data, sheet_name, year):
    # 取需要的几行数据
    df_concat = data[sheet_name].loc[[2,3,48,49]]
    # 给 Unnamed: 0 列进行重命名
    df_concat = df_concat.rename(columns={'Unnamed: 0':'类别'})
    # 插入两列数据 省份	年份
    df_concat.insert(loc=0,column='省份',value=sheet_name)
    df_concat.insert(loc=1,column='年份',value=i)
    # 将Total这列移动到第四列
    df_temp = df_concat['Total']
    df_concat = df_concat.drop(['Total'],axis=1)  # 先删除该列
    df_concat.insert(loc=3,column='Total',value=df_temp)  # 然后插入到第四列位置
    return df_concat

'''
取出单个Excel中需要的数据
'''
def get_excel_data(data, year):
    df_concat = pd.DataFrame()
    for sheet_name in list(data.keys()):
        if sheet_name == 'Sum':
            continue
        df_temp = get_sheet_data(data, sheet_name, year)
        df_concat = pd.concat([df_concat, df_temp])
    return df_concat

# 0、新建一个数据存储对象（我们用pandas中的Dataframe）
df_concat = pd.DataFrame()

# 生成一个列表，存储时间
date_year = [str(i) for i in range(2000, 2018)]

# 1、遍历取出每个Excel中的每个sheet中需要的几行数据，存储到新建的Dataframe中
for i in date_year:
    file_path = 'data/2000年-2017年碳排放清单/%s年30个省份排放清单.xlsx'%i
    data = pd.read_excel(file_path, sheet_name=None)
    df_temp = get_excel_data(data, i)
    df_concat = pd.concat([df_concat, df_temp])

# 2、写入数据
print("开始存储数据")
df_concat.to_excel("data/2000_2017年省份碳排放数据.xlsx", "2000_2017", index=None, encoding="utf-8")
print("数据保存成功")
复制代码

3.2 第二个读者需求

我们来看第二个读者的需求：原数据只有一个文件，里面有8个sheet，需要将每个sheet中的几列取出来，然后根据日期存储为一个一个的csv文件。

完成这个需求，如果是手动操作我们需要完成以下几个步骤：

0、打开Excel文件

1、复制出每个sheet中需要的几行数据
2、根据日期进行排序
3、按日期将不同的数据存入不同csv文件

看似很简单，但实际却是复杂的，比如要手动创建保存365个csv文件，文件名字还不一样，想着就头大！

现在我们看看以上手动操作换成代码操作需要那些步骤：

0、新建一个数据存储对象（我们用pandas中的Dataframe）

1、读取目标Excel文件
2、遍历取出每个sheet中需要的几行数据，存储到新建的Dataframe中
3、根据日期进行分组，将不同日期数据存储到对应的文件

了解了这些后，我们就开始愉快的代码之旅吧： 0、新建一个数据存储对象（我们用pandas中的Dataframe）

df_concat = pd.DataFrame()
复制代码

1、读取目标Excel文件

file_path = 'data/meteo_china_tmin_2018.xlsx'
data = pd.read_excel(file_path, sheet_name=None)
复制代码

2、遍历取出每个sheet中需要的几行数据，存储到新建的Dataframe中

for sheet_name in list(data.keys()):
    if sheet_name == 'meteo_china_tmin_2018':
        continue
    df_temp = data[sheet_name][['ymd', 'lat', 'lon', 'tmin']]
    df_concat = pd.concat([df_concat, df_temp])
复制代码

3、根据日期进行分组，将不同日期数据存储到对应的文件
这里根据日期进行检索对应的数据，并调用to_csv函数存储数据，第一个参数为存储的目录，第二个参数columns为存储的数据列，第三个参数header=None表示存储的时候不需要表头，第四个参数index=False表示去除索引。

'''
按时间进行分组，并保存为csv文件
文件格式：hetao-ymd_tmin
'''
# 获取所有日期
ymd_set = set(df_concat['ymd'])
# 循环操作所有数据
for ymd in ymd_set:
    ymd_data = df_concat[df_concat['ymd']==ymd]
    ymd_data.to_csv('./data/hetao/hetao-%d_tmin.csv'%ymd, columns=['lat', 'lon', 'tmin'], header=None, index=False)
复制代码

完整代码：

import pandas as pd

'''
读取、取出需要的数据并合并
'''
file_path = './data/meteo_china_tmin_2018.xlsx'
data = pd.read_excel(file_path, sheet_name=None)
df_concat = pd.DataFrame()

for sheet_name in list(data.keys()):
    if sheet_name == 'meteo_china_tmin_2018':
        continue
    df_temp = data[sheet_name][['ymd', 'lat', 'lon', 'tmin']]
    df_concat = pd.concat([df_concat, df_temp])

'''
按时间进行分组，并保存为csv文件
文件格式：hetao-ymd_tmin
'''
# 获取所有日期
ymd_set = set(df_concat['ymd'])
# 循环操作所有数据
for ymd in ymd_set:
    ymd_data = df_concat[df_concat['ymd']==ymd]
    # 指定存储的列，并且去掉表头
    ymd_data.to_csv('./data/hetao/hetao-%d_tmin.csv'%ymd, columns=['lat', 'lon', 'tmin'], header=None, index=False)
复制代码

四、随便说说

大家如果有什么类似需求，可以说下你的需求，按功能点分1 2 3 最好，然后附上示例数据，欢迎大家进行学习交流。

码农公寓

一、写在前面

二、基本知识概要

三、开始动手动脑

3.1 第一个读者需求

3.2 第二个读者需求

四、随便说说

相关文章