在Python中使用Pandas.DataFrame對Excel操作筆記一

comeonwyj 2018-12-02

展開全文

近期和朋友小A聊天的時候，總是聽小A說工作越來越煩，有大量的Excel表格需要分析整理 ~~~，需要總結(jié)各種數(shù)據(jù)，做統(tǒng)計匯總之類，@#￥%%~~。像我等懶人，是不能容忍做大量重復性工作的。以懶人的觀點來看，凡重復性的，必定有其規(guī)律啊，像太陽東升西落，像季節(jié)四季更替。只要有規(guī)律可循，就可以用工具來自動分析。能讓機器做的事情，人就可以解放一下了嘛，就有更多的時間享受工作和生活了，吼吼吼。

出于對小A的同情，我決定寫一個工具，來自動解析這“大量”的Excel表格（詳談后才知道，其實小A說的大量，也就幾個上千行的表格而已）。這里記錄下一些Pandas對Excel的操作過程，供自己和碼友們交流，共同提高、不斷改進。

環(huán)境：Python3.6 + Pandas（0.22）

有一個1000行，28列的表格，包含了公司每個項目的各種信息（項目ID，名稱，開始時間，狀態(tài)，結(jié)束時間，報價，稅率……）。這么多列，看了確實頭暈??@_@??。這里我做了一個簡單的表格，來說明一下Pandas是如何讀取、篩選Excel的。

import pandas as pd
excelFile = r'TEMP.xlsx'
df = pd.DataFrame(pd.read_excel(excelFile))
print(df)

讀取信息到DataFrame里面就這么簡單，只需要提供一個excel的名稱就好了，當然默認的Sheet名稱是Sheet1。我們可以指定讀取Sheet的名稱的。且看 read_excel 的定義。

def read_excel(io, sheet_name=0, header=0, skiprows=None, skip_footer=0,
               index_col=None, names=None, usecols=None, parse_dates=False,
               date_parser=None, na_values=None, thousands=None,
               convert_float=True, converters=None, dtype=None,
               true_values=None, false_values=None, engine=None,
               squeeze=False, **kwds):

這里不做過多說明，詳細參數(shù)說明官方網(wǎng)站 http://pandas./pandas-docs/version/0.22/api.html。

print(df) 讀取到的信息如下，是不是很簡單(向Pandas開發(fā)團隊致敬）：

   Project ID    Project Name Start Time      Status    Offer Tax Rate    PM
0      #10001 Microsoft_XXXX 2018-01-01   Completed 1000000     0.060   Bob
1      #10002      Adobe_XXXX 2018-01-02 Processing   105500     0.065   TOM
2      #10003     VMVare_XXXX 2018-01-03 Processing   280000     0.070 Kate
3      #10004      Intel_XXXX 2018-01-04 Processing   520000     0.060 Jone
4      #10005         HP_XXXX 2018-01-05     Pending   600000     0.060   Bob
5      #10006     Lenovo_XXXX 2018-01-06   Completed   980000     0.080 Jone
6      #10007       DELL_XXXX 2018-01-07 Processing   620000     0.060 Kate
7      #10008        ALI_XXXX 2018-01-08 Processing   100000     0.060   Bob
8      #10009      Apple_XXXX 2018-01-09     Pending    80000     0.090   Ken
9      #10010     Google_XXXX 2018-01-10   Completed   610000     0.060   Ken
10     #10011     Amazon_XXXX 2018-01-11     Pending    92000     0.125   Ken

需求一：
我們只想要 Project Name、Status、Offer、Tax Rate、PM   這幾列的信息：

import pandas as pd
excelFile = r'TEMP.xlsx'
df = pd.DataFrame(pd.read_excel(excelFile))
df1= df[['Project Name', 'Status', 'Offer', 'Tax Rate', 'PM']]
print(df1)

需求二：
我們只想要 統(tǒng)計 Bob 的項目

import pandas as pd
excelFile = r'TEMP.xlsx'
df = pd.DataFrame(pd.read_excel(excelFile))
df1 = df[['Project Name', 'Status', 'Offer', 'Tax Rate', 'PM']]
df2 = df1.loc[df1['PM'] == 'Bob']
print(df2)

我們只想要統(tǒng)計 Bob 的，Status為Completed的項目

import pandas as pd
excelFile = r'TEMP.xlsx'
df = pd.DataFrame(pd.read_excel(excelFile))
df1 = df[['Project Name', 'Status', 'Offer', 'Tax Rate', 'PM']]
df2 = df1.loc[df1['PM'] == 'Bob'].loc[df1['Status'] == 'Completed']
print(df2)

需求三：
來統(tǒng)計PM各自的項目信息。

分析：首先我們要知道都有哪些PM，這在表的PM列里面有。

import pandas as pd
excelFile = r'TEMP.xlsx'
df = pd.DataFrame(pd.read_excel(excelFile))
df1 = df[['Project Name', 'Status', 'Offer', 'Tax Rate', 'PM']]
df2 = df1.loc[df1['PM'] == 'Bob'].loc[df1['Status'] == 'Completed']
#獲取PM列的值
pmList = df1[['PM']].values.T.tolist()[:][0]
print(pmList)
#排除重復值
pmList = list(set(pmList))
print(pmList)
for pm in pmList:
    dfByPM = df1.loc[df1['PM'] == pm]
    print('\r\n')
    print(dfByPM)

結(jié)果如下：

pandas 是不是很強大，我們只需要很少的代碼，就可以讀取和查詢excel的幾乎所有內(nèi)容。

~~~~~2018/05/29 更新 ~~~~~~~

需求四：

來統(tǒng)計每位PM所有Offer列的總和。

import pandas as pd
excelFile = r'TEMP.xlsx'
df = pd.DataFrame(pd.read_excel(excelFile))
# 獲取PM列的值
pmList = df[['PM']].values.T.tolist()[:][0]
print(pmList)
# 排除重復值
pmList = list(set(pmList))
print(pmList)
sum_list = [['PM', 'Offer']]
for pm in pmList:
    temp = []
    dfByPM = df.loc[df['PM'] == pm]
    temp.append(pm)
    for col in dfByPM.columns:
        if col == 'Offer':
            sumValue = dfByPM[col].sum() #計數(shù)指定列的和
            temp.append(sumValue)
    sum_list.append(temp)
print(sum_list)

運行結(jié)果如下：

[['PM', 'Offer'], ['Ken', 782000], ['Kate', 900000], ['Bob', 1700000], ['TOM', 105500], ['Jone', 1500000]]

需求五：

如何把這些信息寫入到Excel里面呢？且往下看……

pandas.DataFrame.to_excel

這是一個特別惹人愛的函數(shù)，由dataframe對象直接調(diào)用，然后指定文件名、表名等各種參數(shù)。函數(shù)定義如下：

DataFrame.to_excel(excel_writer, sheet_name='Sheet1', na_rep='', float_format=None, columns=None, header=True, index=True, index_label=None, startrow=0, startcol=0, engine=None, merge_cells=True, encoding=None, inf_rep='inf', verbose=True, freeze_panes=None)

更詳細的說明可以看這里：

http://pandas./pandas-docs/stable/generated/pandas.DataFrame.to_excel.html#pandas.DataFrame.to_excel

思路：我們先把一個二維列表轉(zhuǎn)換成dataframe對象，然后再調(diào)用這個pandas.DataFrame.to_excel函數(shù)

summaryDataFrame = pd.DataFrame(sum_list)
summaryDataFrame.to_excel(filePath, encoding='utf-8', index=False, header=False)filePath, encoding='utf-8', index=False, header=False)

運行后就會在filePath下面發(fā)現(xiàn)新生成的文件。

對excel的查詢及再存儲就簡單記錄到這里，后續(xù)說說修改Excel的樣式及在現(xiàn)存excel里面添加內(nèi)容