用Python的pandas框架操作Excel文件中的數(shù)據(jù)教程
引言
本文的目的，是向您展示如何使用pandas 來執(zhí)行一些常見的Excel任務(wù)。有些例子比較瑣碎，但我覺得展示這些簡單的東西與那些你可以在其他地方找到的復(fù)雜功能同等重要。作為額外的福利，我將會(huì)進(jìn)行一些模糊字符串匹配，以此來展示一些小花樣，以及展示pandas是如何利用完整的Python模塊系統(tǒng)去做一些在Python中是簡單，但在Excel中卻很復(fù)雜的事情的。
有道理吧？讓我們開始吧。
為某行添加求和項(xiàng)
我要介紹的第一項(xiàng)任務(wù)是把某幾列相加然后添加一個(gè)總和欄。
首先我們將excel 數(shù)據(jù) 導(dǎo)入到pandas數(shù)據(jù)框架中。
import pandas as pd
import numpy as np
df = pd.read_excel("excel-comp-data.xlsx")
df.head()
我們想要添加一個(gè)總和欄來顯示Jan、Feb和Mar三個(gè)月的銷售總額。
在Excel和pandas中這都是簡單直接的。對(duì)于Excel，我在J列中添加了公式sum(G2:I2)。在Excel中看上去是這樣的：
下面，我們是這樣在pandas中操作的：
df["total"] = df["Jan"] + df["Feb"] + df["Mar"] df.head()
接下來，讓我們對(duì)各列計(jì)算一些匯總信息以及其他值。如下Excel表所示，我們要做這些工作：
如你所見，我們?cè)诒硎驹路莸牧械牡?7行添加了SUM(G2:G16)，來取得每月的總和。
進(jìn)行在pandas中進(jìn)行列級(jí)別的分析很簡單。下面是一些例子：
df["Jan"].sum(), df["Jan"].mean(),df["Jan"].min(),df["Jan"].max() (1462000, 97466.666666666672, 10000, 162000)
現(xiàn)在我們要把每月的總和相加得到它們的和。這里pandas和Excel有點(diǎn)不同。在Excel的單元格里把每個(gè)月的總和相加很簡單。由于pandas需要維護(hù)整個(gè)DataFrame的完整性，所以需要一些額外的步驟。
首先，建立所有列的總和欄
sum_row=df[["Jan","Feb","Mar","total"]].sum() sum_row Jan 1462000 Feb 1507000 Mar 717000 total 3686000 dtype: int64
這很符合直覺，不過如果你希望將總和值顯示為表格中的單獨(dú)一行，你還需要做一些微調(diào)。
我們需要把數(shù)據(jù)進(jìn)行變換，把這一系列數(shù)字轉(zhuǎn)換為DataFrame，這樣才能更加容易的把它合并進(jìn)已經(jīng)存在的數(shù)據(jù)中。T 函數(shù)可以讓我們把按行排列的數(shù)據(jù)變換為按列排列。
df_sum=pd.DataFrame(data=sum_row).T df_sum
在計(jì)算總和之前我們要做的最后一件事情是添加丟失的列。我們使用reindex來幫助我們完成。技巧是添加全部的列然后讓pandas去添加所有缺失的數(shù)據(jù)。
df_sum=df_sum.reindex(columns=df.columns) df_sum
現(xiàn)在我們已經(jīng)有了一個(gè)格式良好的DataFrame，我們可以使用append來把它加入到已有的內(nèi)容中。
df_final=df.append(df_sum,ignore_index=True) df_final.tail()
額外的數(shù)據(jù)變換
另外一個(gè)例子，讓我們嘗試給數(shù)據(jù)集添加狀態(tài)的縮寫。
對(duì)于Excel，最簡單的方式是添加一個(gè)新的列，對(duì)州名使用vlookup函數(shù)并填充縮寫欄。
我進(jìn)行了這樣的操作，下面是其結(jié)果的截圖：
你可以注意到，在進(jìn)行了vlookup后，有一些數(shù)值并沒有被正確的取得。這是因?yàn)槲覀兤村e(cuò)了一些州的名字。在Excel中處理這一問題是一個(gè)巨大的挑戰(zhàn)（對(duì)于大型數(shù)據(jù)集而言）
幸運(yùn)的是，使用pandas我們可以利用強(qiáng)大的python生態(tài)系統(tǒng)。考慮如何解決這類麻煩的數(shù)據(jù)問題，我考慮進(jìn)行一些模糊文本匹配來決定正確的值。
幸運(yùn)的是其他人已經(jīng)做了很多這方面的工作。fuzzy wuzzy庫包含一些非常有用的函數(shù)來解決這類問題。首先要確保你安裝了他。
我們需要的另外一段代碼是州名與其縮寫的映射表。而不是親自去輸入它們，谷歌一下你就能找到這段代碼code。
首先導(dǎo)入合適的fuzzywuzzy函數(shù)并且定義我們的州名映射表。
from fuzzywuzzy import fuzz
from fuzzywuzzy import process
state_to_code = {"VERMONT": "VT", "GEORGIA": "GA", "IOWA": "IA", "Armed Forces Pacific": "AP", "GUAM": "GU",
"KANSAS": "KS", "FLORIDA": "FL", "AMERICAN SAMOA": "AS", "NORTH CAROLINA": "NC", "HAWAII": "HI",
"NEW YORK": "NY", "CALIFORNIA": "CA", "ALABAMA": "AL", "IDAHO": "ID", "FEDERATED STATES OF MICRONESIA": "FM",
"Armed Forces Americas": "AA", "DELAWARE": "DE", "ALASKA": "AK", "ILLINOIS": "IL",
"Armed Forces Africa": "AE", "SOUTH DAKOTA": "SD", "CONNECTICUT": "CT", "MONTANA": "MT", "MASSACHUSETTS": "MA",
"PUERTO RICO": "PR", "Armed Forces Canada": "AE", "NEW HAMPSHIRE": "NH", "MARYLAND": "MD", "NEW MEXICO": "NM",
"MISSISSIPPI": "MS", "TENNESSEE": "TN", "PALAU": "PW", "COLORADO": "CO", "Armed Forces Middle East": "AE",
"NEW JERSEY": "NJ", "UTAH": "UT", "MICHIGAN": "MI", "WEST VIRGINIA": "WV", "WASHINGTON": "WA",
"MINNESOTA": "MN", "OREGON": "OR", "VIRGINIA": "VA", "VIRGIN ISLANDS": "VI", "MARSHALL ISLANDS": "MH",
"WYOMING": "WY", "OHIO": "OH", "SOUTH CAROLINA": "SC", "INDIANA": "IN", "NEVADA": "NV", "LOUISIANA": "LA",
"NORTHERN MARIANA ISLANDS": "MP", "NEBRASKA": "NE", "ARIZONA": "AZ", "WISCONSIN": "WI", "NORTH DAKOTA": "ND",
"Armed Forces Europe": "AE", "PENNSYLVANIA": "PA", "OKLAHOMA": "OK", "KENTUCKY": "KY", "RHODE ISLAND": "RI",
"DISTRICT OF COLUMBIA": "DC", "ARKANSAS": "AR", "MISSOURI": "MO", "TEXAS": "TX", "MAINE": "ME"}
這里有些介紹模糊文本匹配函數(shù)如何工作的例子。
process.extractOne("Minnesotta",choices=state_to_code.keys())
('MINNESOTA', 95)
process.extractOne("AlaBAMMazzz",choices=state_to_code.keys(),score_cutoff=80)
現(xiàn)在我知道它是如何工作的了，我們創(chuàng)建自己的函數(shù)來接受州名這一列的數(shù)據(jù)然后把他轉(zhuǎn)換為一個(gè)有效的縮寫。這里我們使用score_cutoff的值為80。你可以做一些調(diào)整，看看哪個(gè)值對(duì)你的數(shù)據(jù)來說比較好。你會(huì)注意到，返回值要么是一個(gè)有效的縮寫，要么是一個(gè)np.nan 所以域中會(huì)有一些有效的值。
def convert_state(row):
abbrev = process.extractOne(row["state"],choices=state_to_code.keys(),score_cutoff=80)
if abbrev:
return state_to_code[abbrev[0]]
return np.nan
把這列添加到我們想要填充的單元格，然后用NaN填充它
df_final.insert(6, "abbrev", np.nan) df_final.head()
我們使用apply 來把縮寫添加到合適的列中。
df_final['abbrev'] = df_final.apply(convert_state, axis=1) df_final.tail()
我覺的這很酷。我們已經(jīng)開發(fā)出了一個(gè)非常簡單的流程來智能的清理數(shù)據(jù)。顯然，當(dāng)你只有15行左右數(shù)據(jù)的時(shí)候這沒什么了不起的。但是如果是15000行呢？在Excel中你就必須進(jìn)行一些人工清理了。
分類匯總
在本文的最后一節(jié)中，讓我們按州來做一些分類匯總（subtotal）。
在Excel中，我們會(huì)用subtotal 工具來完成。
輸出如下：
在pandas中創(chuàng)建分類匯總，是使用groupby 來完成的。
df_sub=df_final[["abbrev","Jan","Feb","Mar","total"]].groupby('abbrev').sum()
df_sub
然后，我們想要通過對(duì)data frame中所有的值使用 applymap 來把數(shù)據(jù)單位格式化為貨幣。
def money(x):
return "${:,.0f}".format(x)
formatted_df = df_sub.applymap(money)
formatted_df
格式化看上去進(jìn)行的很順利，現(xiàn)在我們可以像之前那樣獲取總和了。
sum_row=df_sub[["Jan","Feb","Mar","total"]].sum() sum_row
Jan 1462000 Feb 1507000 Mar 717000 total 3686000 dtype: int64
把值變換為列然后進(jìn)行格式化。
df_sub_sum=pd.DataFrame(data=sum_row).T df_sub_sum=df_sub_sum.applymap(money) df_sub_sum
最后，把總和添加到DataFrame中。
final_table = formatted_df.append(df_sub_sum) final_table
你可以注意到總和行的索引號(hào)是‘0'。我們想要使用rename 來重命名它。
final_table = final_table.rename(index={0:"Total"})
final_table
結(jié)論
到目前為止，大部分人都已經(jīng)知道使用pandas可以對(duì)數(shù)據(jù)做很多復(fù)雜的操作——就如同Excel一樣。因?yàn)槲乙恢痹趯W(xué)習(xí)pandas，但我發(fā)現(xiàn)我還是會(huì)嘗試記憶我是如何在Excel中完成這些操作的而不是在pandas中。我意識(shí)到把它倆作對(duì)比似乎不是很公平——它們是完全不同的工具。但是，我希望能接觸到哪些了解Excel并且想要學(xué)習(xí)一些可以滿足分析他們數(shù)據(jù)需求的其他替代工具的那些人。我希望這些例子可以幫助到其他人，讓他們有信心認(rèn)為他們可以使用pandas來替換他們零碎復(fù)雜的Excel，進(jìn)行數(shù)據(jù)操作。
相關(guān)文章
Python中最快的循環(huán)姿勢(shì)實(shí)例詳解
python給我們提供了多個(gè)循環(huán)方法,比如while循環(huán)、for循環(huán)等,下面這篇文章主要給大家介紹了關(guān)于Python中最快的循環(huán)姿勢(shì),需要的朋友可以參考下2021-11-11
這篇文章主要介紹了pytorch SENet實(shí)現(xiàn)案例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2020-06-06
這篇文章主要介紹了python建造者模式案例運(yùn)行原理解析,文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-06-06
利用Python實(shí)現(xiàn)Windows定時(shí)關(guān)機(jī)功能
是最初的幾個(gè)爬蟲，讓我認(rèn)識(shí)了Python這個(gè)新朋友，雖然才剛認(rèn)識(shí)了幾天，但感覺有種莫名的默契感。下面通過這篇文章給大家介紹Python實(shí)現(xiàn)Windows定時(shí)關(guān)機(jī)功能,需要的朋友可以參考下2017-03-03
python中dtypes和type()函數(shù)的區(qū)別示例詳解
type()是python內(nèi)置的函數(shù),type()返回?cái)?shù)據(jù)結(jié)構(gòu)類型(list、dict、numpy.ndarray 等),dtype返回?cái)?shù)據(jù)元素的數(shù)據(jù)類型(int、float等),這篇文章主要給大家介紹了關(guān)于python中dtypes和type()函數(shù)區(qū)別的相關(guān)資料,需要的朋友可以參考下2024-03-03
一篇文章弄懂Python中的可迭代對(duì)象、迭代器和生成器
這篇文章主要給大家介紹了關(guān)于Python中可迭代對(duì)象、迭代器和生成器的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用Python具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-08-08
PyQt5實(shí)現(xiàn)仿QQ貼邊隱藏功能的實(shí)例代碼
這篇文章主要介紹了PyQt5實(shí)現(xiàn)仿QQ貼邊隱藏功能,本文通過實(shí)例代碼給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2020-05-05
Python3使用PyQt5制作簡單的畫板/手寫板實(shí)例
下面小編就為大家?guī)硪黄狿ython3使用PyQt5制作簡單的畫板/手寫板實(shí)例。小編覺得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧2017-10-10
python圖的深度優(yōu)先和廣度優(yōu)先算法實(shí)例分析
這篇文章主要介紹了python圖的深度優(yōu)先和廣度優(yōu)先算法,結(jié)合實(shí)例形式分析了圖的深度優(yōu)先算法與廣度優(yōu)先算法相關(guān)概念、原理、實(shí)現(xiàn)技巧與操作注意事項(xiàng),需要的朋友可以參考下2019-10-10
pandas數(shù)據(jù)處理之繪圖的實(shí)現(xiàn)
這篇文章主要介紹了pandas數(shù)據(jù)處理之繪圖的實(shí)現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-06-06