分享2個(gè)Python處理Excel的腳本

來(lái)源于兩個(gè)讀者的學(xué)習(xí)/工作需求，很巧，這兩個(gè)讀者提的需求都是關(guān)于批量合并sheet（檢索需要的信息）。

本文所用數(shù)據(jù)樣式，經(jīng)讀者同意，可以公開，文件中的數(shù)據(jù)為Excel中的Rand函數(shù)生成，確保大家在學(xué)習(xí)、使用代碼過程中不會(huì)遇到障礙，數(shù)據(jù)和代碼獲取方式見文末。

來(lái)自讀者的認(rèn)可，嘿嘿

基本知識(shí)概要

• pandas創(chuàng)建一個(gè)DataFrame對(duì)象
  pd.DataFrame()
• pandas datafrmae索引
  按列名索引：dataframe[列名]
  按列值索引：dataframe[dataframe[列名]==列值]
• pandas 讀取、存儲(chǔ)excel文件，存儲(chǔ)csv文件
  read_excel、to_excel、to_csv
• pandas datafrmae根據(jù)縮影取指定行數(shù)據(jù)
  dataframe.loc[list]
• pandas datafrmae修改列名
  dataframe.rename(columns={'column_name_old':'column_name_new'})
• pandas datafrmae將數(shù)據(jù)插入到指定列
  dataframe.insert(loc=列序號(hào),column=列名,value=列值)
• pandas datafrmae根據(jù)列名刪除指定列
  dataframe.drop([列名],axis=1)
• pandas 連接多個(gè)datafrmae
  pd.concat([df_1, df_2])

開始動(dòng)手動(dòng)腦

第一個(gè)讀者需求

首先我們先看第一個(gè)讀者的需求：原始數(shù)據(jù)有18個(gè)Excel文件，每個(gè)Excel文件里有34個(gè)sheet（34個(gè)省的相關(guān)數(shù)據(jù)），需要取出每個(gè)sheet中指定的幾行數(shù)據(jù)，然后全部合并起來(lái)，存儲(chǔ)到一個(gè)新的文件，命名為2000_2017年各省份碳排放數(shù)據(jù)。

經(jīng)過溝通，我確定了最終輸出文件的樣式，以下數(shù)據(jù)都是用Excel中的隨機(jī)函數(shù)生成：

完成這個(gè)需求，如果是手動(dòng)操作我們需要完成以下幾個(gè)步驟：

0、新建一個(gè)Excel
1、打開第一個(gè)Excel
2、復(fù)制出每個(gè)sheet中需要的幾行數(shù)據(jù)
3、將復(fù)制出來(lái)的數(shù)據(jù)粘貼到新建的Excel中
4、重復(fù)1-3，直到取出所有Excel中的數(shù)據(jù)
5、保存新建的Excel

如果只是1-2個(gè)文件，動(dòng)手還可以接受，但是要是有幾十個(gè)，幾百個(gè)，如果靠動(dòng)手就頭大了。
現(xiàn)在我們看看以上手動(dòng)操作換成代碼操作需要那些步驟：

0、新建一個(gè)數(shù)據(jù)存儲(chǔ)對(duì)象（我們用pandas中的Dataframe）
1、讀取目標(biāo)Excel文件
2、遍歷取出每個(gè)sheet中需要的幾行數(shù)據(jù)，存儲(chǔ)到新建的Dataframe中
3、for循環(huán)遍歷，讀取所有目標(biāo)Excel數(shù)據(jù)，并存儲(chǔ)到新建的Dataframe中
4、將新建的Dataframe數(shù)據(jù)保存為一個(gè)Excel文件

了解了這些后，我們就開始愉快的代碼之旅吧：

0、新建一個(gè)數(shù)據(jù)存儲(chǔ)對(duì)象（我們用pandas中的Dataframe）

df_concat = pd.DataFrame()

1、讀取目標(biāo)Excel文件
文件一共有18個(gè)文件，文件名也是有規(guī)則的。

file_path = 'data/2000年-2017年碳排放清單/2000年30個(gè)省份排放清單.xlsx'
data = pd.read_excel(file_path, sheet_name=None)

2、遍歷取出每個(gè)sheet中需要的幾行數(shù)據(jù)，存儲(chǔ)到新建的Dataframe中
為了代碼的可讀性，這里寫了一個(gè)函數(shù)get_sheet_data來(lái)取出單個(gè)sheet中需要的數(shù)據(jù)，然后for循環(huán)遍歷所有的sheet。

'''
取出單個(gè)sheet中需要的數(shù)據(jù)
'''
def get_sheet_data(data, sheet_name, year):
    # 取需要的幾行數(shù)據(jù)
    df_concat = data[sheet_name].loc[[2,3,48,49]]
    # 給 Unnamed: 0 列進(jìn)行重命名
    df_concat = df_concat.rename(columns={'Unnamed: 0':'類別'})
    # 插入兩列數(shù)據(jù) 省份 年份
    df_concat.insert(loc=0,column='省份',value=sheet_name)
    df_concat.insert(loc=1,column='年份',value=i)
    # 將Total這列移動(dòng)到第四列
    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循環(huán)遍歷，讀取所有目標(biāo)Excel數(shù)據(jù)，并存儲(chǔ)到新建的Dataframe中
在上一步，已經(jīng)讀取出了單個(gè)Excel中的所有sheet，現(xiàn)在再利用for循環(huán)遍歷讀取所有Excel中的數(shù)據(jù)。

'''
取出單個(gè)Excel中需要的數(shù)據(jù)
'''
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

# 生成一個(gè)列表，存儲(chǔ)時(shí)間
date_year = [str(i) for i in range(2000, 2018)]
for i in date_year:
    file_path = 'data/2000年-2017年碳排放清單/%s年30個(gè)省份排放清單.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數(shù)據(jù)保存為一個(gè)Excel文件
這里直接調(diào)用pandas內(nèi)置的to_excel函數(shù)，第一個(gè)參數(shù)為文件存儲(chǔ)目錄，第二個(gè)參數(shù)為sheet_name，第三個(gè)參數(shù)是編碼格式，這里指定為utf-8。

print("開始存儲(chǔ)數(shù)據(jù)")
df_concat.to_excel("data/2000_2017年省份碳排放數(shù)據(jù).xlsx", "2000_2017", index=None, encoding="utf-8")
print("數(shù)據(jù)保存成功")

完整代碼如下：

import pandas as pd
import time

'''
取出單個(gè)sheet中需要的數(shù)據(jù)
'''
def get_sheet_data(data, sheet_name, year):
    # 取需要的幾行數(shù)據(jù)
    df_concat = data[sheet_name].loc[[2,3,48,49]]
    # 給 Unnamed: 0 列進(jìn)行重命名
    df_concat = df_concat.rename(columns={'Unnamed: 0':'類別'})
    # 插入兩列數(shù)據(jù) 省份 年份
    df_concat.insert(loc=0,column='省份',value=sheet_name)
    df_concat.insert(loc=1,column='年份',value=i)
    # 將Total這列移動(dòng)到第四列
    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

'''
取出單個(gè)Excel中需要的數(shù)據(jù)
'''
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、新建一個(gè)數(shù)據(jù)存儲(chǔ)對(duì)象（我們用pandas中的Dataframe）
df_concat = pd.DataFrame()

# 生成一個(gè)列表，存儲(chǔ)時(shí)間
date_year = [str(i) for i in range(2000, 2018)]

# 1、遍歷取出每個(gè)Excel中的每個(gè)sheet中需要的幾行數(shù)據(jù)，存儲(chǔ)到新建的Dataframe中
for i in date_year:
    file_path = 'data/2000年-2017年碳排放清單/%s年30個(gè)省份排放清單.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、寫入數(shù)據(jù)
print("開始存儲(chǔ)數(shù)據(jù)")
df_concat.to_excel("data/2000_2017年省份碳排放數(shù)據(jù).xlsx", "2000_2017", index=None, encoding="utf-8")
print("數(shù)據(jù)保存成功")

第二個(gè)讀者需求

我們來(lái)看第二個(gè)讀者的需求：原數(shù)據(jù)只有一個(gè)文件，里面有8個(gè)sheet，需要將每個(gè)sheet中的幾列取出來(lái)，然后根據(jù)日期存儲(chǔ)為一個(gè)一個(gè)的csv文件。

完成這個(gè)需求，如果是手動(dòng)操作我們需要完成以下幾個(gè)步驟：

0、打開Excel文件
1、復(fù)制出每個(gè)sheet中需要的幾行數(shù)據(jù)
2、根據(jù)日期進(jìn)行排序
3、按日期將不同的數(shù)據(jù)存入不同csv文件

看似很簡(jiǎn)單，但實(shí)際卻是復(fù)雜的，比如要手動(dòng)創(chuàng)建保存365個(gè)csv文件，文件名字還不一樣，想著就頭大！

現(xiàn)在我們看看以上手動(dòng)操作換成代碼操作需要那些步驟：

0、新建一個(gè)數(shù)據(jù)存儲(chǔ)對(duì)象（我們用pandas中的Dataframe）
1、讀取目標(biāo)Excel文件
2、遍歷取出每個(gè)sheet中需要的幾行數(shù)據(jù)，存儲(chǔ)到新建的Dataframe中
3、根據(jù)日期進(jìn)行分組，將不同日期數(shù)據(jù)存儲(chǔ)到對(duì)應(yīng)的文件

了解了這些后，我們就開始愉快的代碼之旅吧：0、新建一個(gè)數(shù)據(jù)存儲(chǔ)對(duì)象（我們用pandas中的Dataframe）

df_concat = pd.DataFrame()

1、讀取目標(biāo)Excel文件

file_path = 'data/meteo_china_tmin_2018.xlsx'
data = pd.read_excel(file_path, sheet_name=None)

2、遍歷取出每個(gè)sheet中需要的幾行數(shù)據(jù)，存儲(chǔ)到新建的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、根據(jù)日期進(jìn)行分組，將不同日期數(shù)據(jù)存儲(chǔ)到對(duì)應(yīng)的文件
這里根據(jù)日期進(jìn)行檢索對(duì)應(yīng)的數(shù)據(jù)，并調(diào)用to_csv函數(shù)存儲(chǔ)數(shù)據(jù)，第一個(gè)參數(shù)為存儲(chǔ)的目錄，第二個(gè)參數(shù)columns為存儲(chǔ)的數(shù)據(jù)列，第三個(gè)參數(shù)header=None表示存儲(chǔ)的時(shí)候不需要表頭，第四個(gè)參數(shù)index=False表示去除索引。

'''
按時(shí)間進(jìn)行分組，并保存為csv文件
文件格式：hetao-ymd_tmin
'''
# 獲取所有日期
ymd_set = set(df_concat['ymd'])
# 循環(huán)操作所有數(shù)據(jù)
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

'''
讀取、取出需要的數(shù)據(jù)并合并
'''
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])

'''
按時(shí)間進(jìn)行分組，并保存為csv文件
文件格式：hetao-ymd_tmin
'''
# 獲取所有日期
ymd_set = set(df_concat['ymd'])
# 循環(huán)操作所有數(shù)據(jù)
for ymd in ymd_set:
    ymd_data = df_concat[df_concat['ymd']==ymd]
    # 指定存儲(chǔ)的列，并且去掉表頭
    ymd_data.to_csv('./data/hetao/hetao-%d_tmin.csv'%ymd, columns=['lat', 'lon', 'tmin'], header=None, index=False)