用Pandas完成Excel中常見的任務（2）

艾凌風發表於2015-01-29

介紹

讀者對於本系列第一篇文章的迴應，讓我感到很興奮。感謝大家正面的反饋。我想把本系列繼續下去，重點介紹其他的一些你經常使用Excel完成的任務，並且展示給你如何在pandas 中使用相同的功能。

在第一篇文章中，我著重介紹了Excel中常見的數學計算工作，以及在pandas如何完成這些工作。在本文中，我們將著重介紹一些常見的選擇和篩選任務，並且介紹如何在pandas中完成同樣的事情。

設定

如果您想要繼續下去，您可以下載本excel檔案。

匯入pandas和numpy模組。

1 2	import pandas as pd import numpy as np

匯入我們樣本公司銷售年銷售額的Excel檔案。

1	df = pd.read_excel("sample-salesv3.xlsx")

快速瀏覽一下資料型別，以確保所以事情都能如預期一樣執行。

df.dtypes

account number int64

name object

sku object

quantity int64

unit price float64

ext price float64

date object

dtype: object

你會注意到，我們的date列，顯示的是一個通用物件。我們準備把它轉換為日期物件，來簡化將來會用到的一些選擇操作。

1 2	df['date'] = pd.to_datetime(df['date']) df.head()

	account number	name	sku	quantity	unit price	ext price	date
0	740150	Barton LLC	B1-20000	39	86.69	3380.91	2014-01-01 07:21:51
1	714466	Trantow-Barrows	S2-77896	-1	63.16	-63.16	2014-01-01 10:00:47
2	218895	Kulas Inc	B1-69924	23	90.70	2086.10	2014-01-01 13:24:58
3	307599	Kassulke, Ondricka and Metz	S1-65481	41	21.05	863.05	2014-01-01 15:05:22
4	412290	Jerde-Hilpert	S2-34077	6	83.21	499.26	2014-01-01 23:26:55

df.dtypes

account number int64

name object

sku object

quantity int64

unit price float64

ext price float64

date datetime64[ns]

dtype: object

現在，data變成了一個datetime型別的物件，這對於將來的操作是很有用的。

篩選資料

我認為在Excel中最方便的功能是篩選。我想幾乎每一次有人拿到一個任意大小的Excel檔案，當他們想要篩選資料的時候，都會使用這個功能。

如圖，對本資料集使用該功能：

同Excel中的篩選功能一樣，你可以使用pandas來篩選和選擇某個特定資料的子集。

比方說，如果我們僅僅想檢視一個特定的賬號，我們可以簡單是在Excel中完成，或是使用pandas完成操作。

下面是Excel的篩選解決方案：

在pandas中執行相關操作比Excel中更加直觀。注意，我將會使用head 函式來顯示前面幾個結果。這僅僅是為了讓本文保持簡短。

1	df[df["account number"]==307599].head()

你還可以以數值為基準來進行篩選。我就不再舉任何Excel的例子了。我相信你能明白。

1	df[df["quantity"] > 22].head()

	account number	name	sku	quantity	unit price	ext price	date
0	740150	Barton LLC	B1-20000	39	86.69	3380.91	2014-01-01 07:21:51
2	218895	Kulas Inc	B1-69924	23	90.70	2086.10	2014-01-01 13:24:58
3	307599	Kassulke, Ondricka and Metz	S1-65481	41	21.05	863.05	2014-01-01 15:05:22
14	737550	Fritsch, Russel and Anderson	B1-53102	23	71.56	1645.88	2014-01-04 08:57:48
15	239344	Stokes LLC	S1-06532	34	71.51	2431.34	2014-01-04 11:34:58

如果我們想要更多複雜的篩選，我們可以可以使用map 來以多重標準進行篩選。在這個例子中，從B1中查詢以“sku”中起始的專案。

1	df[df["sku"].map(lambda x: x.startswith('B1'))].head()

	account number	name	sku	quantity	unit price	ext price	date
0	740150	Barton LLC	B1-20000	39	86.69	3380.91	2014-01-01 07:21:51
2	218895	Kulas Inc	B1-69924	23	90.70	2086.10	2014-01-01 13:24:58
6	218895	Kulas Inc	B1-65551	2	31.10	62.20	2014-01-02 10:57:23
14	737550	Fritsch, Russel and Anderson	B1-53102	23	71.56	1645.88	2014-01-04 08:57:48
17	239344	Stokes LLC	B1-50809	14	16.23	227.22	2014-01-04 22:14:32

把兩個或更多的語句連線起來很簡單，用&就可以。

1	df[df["sku"].map(lambda x: x.startswith('B1')) & (df["quantity"] > 22)].head()

	account number	name	sku	quantity	unit price	ext price	date
0	740150	Barton LLC	B1-20000	39	86.69	3380.91	2014-01-01 07:21:51
2	218895	Kulas Inc	B1-69924	23	90.70	2086.10	2014-01-01 13:24:58
14	737550	Fritsch, Russel and Anderson	B1-53102	23	71.56	1645.88	2014-01-04 08:57:48
26	737550	Fritsch, Russel and Anderson	B1-53636	42	42.06	1766.52	2014-01-08 00:02:11
31	714466	Trantow-Barrows	B1-33087	32	19.56	625.92	2014-01-09 10:16:32

pandas支援的另外一個很有用的函式是isin。它使得我們可以定義一個列表，裡面包含我們所希望查詢的值

在這個例子中，我們查詢包含兩個特定account number值的全部專案。

1	df[df["account number"].isin([714466,218895])].head()

	account number	name	sku	quantity	unit price	ext price	date
1	714466	Trantow-Barrows	S2-77896	-1	63.16	-63.16	2014-01-01 10:00:47
2	218895	Kulas Inc	B1-69924	23	90.70	2086.10	2014-01-01 13:24:58
5	714466	Trantow-Barrows	S2-77896	17	87.63	1489.71	2014-01-02 10:07:15
6	218895	Kulas Inc	B1-65551	2	31.10	62.20	2014-01-02 10:57:23
8	714466	Trantow-Barrows	S1-50961	22	84.09	1849.98	2014-01-03 11:29:02

pandas支援的另外一個函式叫做query，它使得我們可以有效的再資料集中選擇資料。使用它需要安裝numexpr ，所以請確保你在進行下面步驟前已經進行了安裝。

如果你想要通過名字來得到一個消費者列表，你可以使用query來完成，和前面展示的python語法類似。

1	df.query('name == ["Kulas Inc","Barton LLC"]').head()

	account number	name	sku	quantity	unit price	ext price	date
0	740150	Barton LLC	B1-20000	39	86.69	3380.91	2014-01-01 07:21:51
2	218895	Kulas Inc	B1-69924	23	90.70	2086.10	2014-01-01 13:24:58
6	218895	Kulas Inc	B1-65551	2	31.10	62.20	2014-01-02 10:57:23
33	218895	Kulas Inc	S1-06532	3	22.36	67.08	2014-01-09 23:58:27
36	218895	Kulas Inc	S2-34077	16	73.04	1168.64	2014-01-10 12:07:30

這裡只是做個簡單的示例，query函式能做到的還不止這些。我在此展示這些函式的用法，以便當你有需要的時候，會意識到可以用它。

處理日期

使用pandas，你可以對日期進行更加複雜的篩選。在我們處理日期前，我建議你把日期欄進行一個排序，以便返回的結果如你所願。

1 2	df = df.sort('date') df.head()

	account number	name	sku	quantity	unit price	ext price	date
0	740150	Barton LLC	B1-20000	39	86.69	3380.91	2014-01-01 07:21:51
1	714466	Trantow-Barrows	S2-77896	-1	63.16	-63.16	2014-01-01 10:00:47
2	218895	Kulas Inc	B1-69924	23	90.70	2086.10	2014-01-01 13:24:58
3	307599	Kassulke, Ondricka and Metz	S1-65481	41	21.05	863.05	2014-01-01 15:05:22
4	412290	Jerde-Hilpert	S2-34077	6	83.21	499.26	2014-01-01 23:26:55

在操作日期前，為您展示python的篩選語法。

1	df[df['date'] >='20140905'].head()

	account number	name	sku	quantity	unit price	ext price	date
1042	163416	Purdy-Kunde	B1-38851	41	98.69	4046.29	2014-09-05 01:52:32
1043	714466	Trantow-Barrows	S1-30248	1	37.16	37.16	2014-09-05 06:17:19
1044	729833	Koepp Ltd	S1-65481	48	16.04	769.92	2014-09-05 08:54:41
1045	729833	Koepp Ltd	S2-11481	6	26.50	159.00	2014-09-05 16:33:15
1046	737550	Fritsch, Russel and Anderson	B1-33364	4	76.44	305.76	2014-09-06 08:59:08

pandas的一個特別棒的特性是它能夠理解日期，所以它允許我們進行部分篩選。如果我只想要檢視最近幾個月的日期資料，我可以這樣做。

1	df[df['date'] >='2014-03'].head()

	account number	name	sku	quantity	unit price	ext price	date
242	163416	Purdy-Kunde	S1-30248	19	65.03	1235.57	2014-03-01 16:07:40
243	527099	Sanford and Sons	S2-82423	3	76.21	228.63	2014-03-01 17:18:01
244	527099	Sanford and Sons	B1-50809	8	70.78	566.24	2014-03-01 18:53:09
245	737550	Fritsch, Russel and Anderson	B1-50809	20	50.11	1002.20	2014-03-01 23:47:17
246	688981	Keeling LLC	B1-86481	-1	97.16	-97.16	2014-03-02 01:46:44

當然，你可以把篩選標準連結起來。

1	df[(df['date'] >='20140701') & (df['date'] <= '20140715')].head()

	account number	name	sku	quantity	unit price	ext price	date
778	737550	Fritsch, Russel and Anderson	S1-65481	35	70.51	2467.85	2014-07-01 00:21:58
779	218895	Kulas Inc	S1-30248	9	16.56	149.04	2014-07-01 00:52:38
780	163416	Purdy-Kunde	S2-82423	44	68.27	3003.88	2014-07-01 08:15:52
781	672390	Kuhn-Gusikowski	B1-04202	48	99.39	4770.72	2014-07-01 11:12:13
782	642753	Pollich LLC	S2-23246	1	51.29	51.29	2014-07-02 04:02:39

由於pandas可以理解日期列，所以可以將日期值設為不同的格式，都會得到正確的結果。

	account number	name	sku	quantity	unit price	ext price	date
1168	307599	Kassulke, Ondricka and Metz	S2-23246	6	88.90	533.40	2014-10-08 06:19:50
1169	424914	White-Trantow	S2-10342	25	58.54	1463.50	2014-10-08 07:31:40
1170	163416	Purdy-Kunde	S1-27722	22	34.41	757.02	2014-10-08 09:01:18
1171	163416	Purdy-Kunde	B1-33087	7	79.29	555.03	2014-10-08 15:39:13
1172	672390	Kuhn-Gusikowski	B1-38851	30	94.64	2839.20	2014-10-09 00:22:33

1	df[df['date'] >= '10-10-2014'].head()

	account number	name	sku	quantity	unit price	ext price	date
1174	257198	Cronin, Oberbrunner and Spencer	S2-34077	13	12.24	159.12	2014-10-10 02:59:06
1175	740150	Barton LLC	S1-65481	28	53.00	1484.00	2014-10-10 15:08:53
1176	146832	Kiehn-Spinka	S1-27722	15	64.39	965.85	2014-10-10 18:24:01
1177	257198	Cronin, Oberbrunner and Spencer	S2-16558	3	35.34	106.02	2014-10-11 01:48:13
1178	737550	Fritsch, Russel and Anderson	B1-53636	10	56.95	569.50	2014-10-11 10:25:53

當操作時間序列資料時，如果你把資料進行轉化，以日期作為索引，我們可以做一些變相的篩選。

使用set_index 來設定新的索引。

1 2	df2 = df.set_index(['date']) df2.head()

	account number	name	sku	quantity	unit price	ext price
date
2014-01-01 07:21:51	740150	Barton LLC	B1-20000	39	86.69	3380.91
2014-01-01 10:00:47	714466	Trantow-Barrows	S2-77896	-1	63.16	-63.16
2014-01-01 13:24:58	218895	Kulas Inc	B1-69924	23	90.70	2086.10
2014-01-01 15:05:22	307599	Kassulke, Ondricka and Metz	S1-65481	41	21.05	863.05
2014-01-01 23:26:55	412290	Jerde-Hilpert	S2-34077	6	83.21	499.26

你可以通過切分資料來獲取一段區間。

1	df2["20140101":"20140201"].head()

	account number	name	sku	quantity	unit price	ext price
date
2014-01-01 07:21:51	740150	Barton LLC	B1-20000	39	86.69	3380.91
2014-01-01 10:00:47	714466	Trantow-Barrows	S2-77896	-1	63.16	-63.16
2014-01-01 13:24:58	218895	Kulas Inc	B1-69924	23	90.70	2086.10
2014-01-01 15:05:22	307599	Kassulke, Ondricka and Metz	S1-65481	41	21.05	863.05
2014-01-01 23:26:55	412290	Jerde-Hilpert	S2-34077	6	83.21	499.26

再一次的，我們可以使用不同的日期表示方法來避免模稜兩可的日期命名慣例。

1	df2["2014-Jan-1":"2014-Feb-1"].head()

	account number	name	sku	quantity	unit price	ext price
date
2014-01-01 07:21:51	740150	Barton LLC	B1-20000	39	86.69	3380.91
2014-01-01 10:00:47	714466	Trantow-Barrows	S2-77896	-1	63.16	-63.16
2014-01-01 13:24:58	218895	Kulas Inc	B1-69924	23	90.70	2086.10
2014-01-01 15:05:22	307599	Kassulke, Ondricka and Metz	S1-65481	41	21.05	863.05
2014-01-01 23:26:55	412290	Jerde-Hilpert	S2-34077	6	83.21	499.26

1	df2["2014-Jan-1":"2014-Feb-1"].tail()

	account number	name	sku	quantity	unit price	ext price
date
2014-01-31 22:51:18	383080	Will LLC	B1-05914	43	80.17	3447.31
2014-02-01 09:04:59	383080	Will LLC	B1-20000	7	33.69	235.83
2014-02-01 11:51:46	412290	Jerde-Hilpert	S1-27722	11	21.12	232.32
2014-02-01 17:24:32	412290	Jerde-Hilpert	B1-86481	3	35.99	107.97
2014-02-01 19:56:48	412290	Jerde-Hilpert	B1-20000	23	78.90	1814.70

1	df2["2014"].head()

	account number	name	sku	quantity	unit price	ext price
date
2014-01-01 07:21:51	740150	Barton LLC	B1-20000	39	86.69	3380.91
2014-01-01 10:00:47	714466	Trantow-Barrows	S2-77896	-1	63.16	-63.16
2014-01-01 13:24:58	218895	Kulas Inc	B1-69924	23	90.70	2086.10
2014-01-01 15:05:22	307599	Kassulke, Ondricka and Metz	S1-65481	41	21.05	863.05
2014-01-01 23:26:55	412290	Jerde-Hilpert	S2-34077	6	83.21	499.26

1	df2["2014-Dec"].head()

	account number	name	sku	quantity	unit price	ext price
date
2014-12-01 20:15:34	714466	Trantow-Barrows	S1-82801	3	77.97	233.91
2014-12-02 20:00:04	146832	Kiehn-Spinka	S2-23246	37	57.81	2138.97
2014-12-03 04:43:53	218895	Kulas Inc	S2-77896	30	77.44	2323.20
2014-12-03 06:05:43	141962	Herman LLC	B1-53102	20	26.12	522.40
2014-12-03 14:17:34	642753	Pollich LLC	B1-53636	19	71.21	1352.99

正如你所見到的那樣，在進行基於日期的排序或者篩選時，可以有很多選擇。

額外的字串方法

Pandas同樣已經支援了向量字串方法。

如果我們想識別出sku欄中包含某一特定值的全部值。我們可以使用str.contains。在這個例子中，我們已知sku總是以一種相同的方式表示，所以B1僅會出現在sku的前面。你需要理解你的資料來保證你能夠得到你想要的結果。

1	df[df['sku'].str.contains('B1')].head()

	account number	name	sku	quantity	unit price	ext price	date
0	740150	Barton LLC	B1-20000	39	86.69	3380.91	2014-01-01 07:21:51
2	218895	Kulas Inc	B1-69924	23	90.70	2086.10	2014-01-01 13:24:58
6	218895	Kulas Inc	B1-65551	2	31.10	62.20	2014-01-02 10:57:23
14	737550	Fritsch, Russel and Anderson	B1-53102	23	71.56	1645.88	2014-01-04 08:57:48
17	239344	Stokes LLC	B1-50809	14	16.23	227.22	2014-01-04 22:14:32

我們可以把查詢連線起來並且使用排序來控制資料的順序。

1	df[(df['sku'].str.contains('B1-531')) & (df['quantity']>40)].sort(columns=['quantity','name'],ascending=[0,1])

	account number	name	sku	quantity	unit price	ext price	date
684	642753	Pollich LLC	B1-53102	46	26.07	1199.22	2014-06-08 19:33:33
792	688981	Keeling LLC	B1-53102	45	41.19	1853.55	2014-07-04 21:42:22
176	383080	Will LLC	B1-53102	45	89.22	4014.90	2014-02-11 04:14:09
1213	604255	Halvorson, Crona and Champlin	B1-53102	41	55.05	2257.05	2014-10-18 19:27:01
1215	307599	Kassulke, Ondricka and Metz	B1-53102	41	93.70	3841.70	2014-10-18 23:25:10
1128	714466	Trantow-Barrows	B1-53102	41	55.68	2282.88	2014-09-27 10:42:48
1001	424914	White-Trantow	B1-53102	41	81.25	3331.25	2014-08-26 11:44:30

彩蛋任務

在Excel中，我發現我自己經常會嘗試從一個冗長的列表中，得到一個包含不重複項的小列表。在Excel中這件事情需要分幾步來完成，但是在Pandas中卻非常簡單。有一種方式是使用Excel中提供的高階篩選工具來完成。

在pandas中，我們對某列使用這個unique函式來獲取這個列表。

1	df["name"].unique()

array([u'Barton LLC', u'Trantow-Barrows', u'Kulas Inc',

u'Kassulke, Ondricka and Metz', u'Jerde-Hilpert', u'Koepp Ltd',

u'Fritsch, Russel and Anderson', u'Kiehn-Spinka', u'Keeling LLC',

u'Frami, Hills and Schmidt', u'Stokes LLC', u'Kuhn-Gusikowski',

u'Herman LLC', u'White-Trantow', u'Sanford and Sons',

u'Pollich LLC', u'Will LLC', u'Cronin, Oberbrunner and Spencer',

u'Halvorson, Crona and Champlin', u'Purdy-Kunde'], dtype=object)

If we wanted to include the account number, we could use drop_duplicates .

如果我們想要包含賬戶號，我們可以使用drop_duplicates。

1	df.drop_duplicates(subset=["account number","name"]).head()

	account number	name	sku	quantity	unit price	ext price	date
0	740150	Barton LLC	B1-20000	39	86.69	3380.91	2014-01-01 07:21:51
1	714466	Trantow-Barrows	S2-77896	-1	63.16	-63.16	2014-01-01 10:00:47
2	218895	Kulas Inc	B1-69924	23	90.70	2086.10	2014-01-01 13:24:58
3	307599	Kassulke, Ondricka and Metz	S1-65481	41	21.05	863.05	2014-01-01 15:05:22
4	412290	Jerde-Hilpert	S2-34077	6	83.21	499.26	2014-01-01 23:26:55

很顯然我們放入了的資料超過了我們的需要，得到了一些無用的資訊，因此，使用ix 來僅僅選擇第一第二列。

1	df.drop_duplicates(subset=["account number","name"]).ix[:,[0,1]]

	account number	name
0	740150	Barton LLC
1	714466	Trantow-Barrows
2	218895	Kulas Inc
3	307599	Kassulke, Ondricka and Metz
4	412290	Jerde-Hilpert
7	729833	Koepp Ltd
9	737550	Fritsch, Russel and Anderson
10	146832	Kiehn-Spinka
11	688981	Keeling LLC
12	786968	Frami, Hills and Schmidt
15	239344	Stokes LLC
16	672390	Kuhn-Gusikowski
18	141962	Herman LLC
20	424914	White-Trantow
21	527099	Sanford and Sons
30	642753	Pollich LLC
37	383080	Will LLC
51	257198	Cronin, Oberbrunner and Spencer
67	604255	Halvorson, Crona and Champlin
106	163416	Purdy-Kunde

我認為這個記住這個單獨的命令比記憶Excel的各步操作更容易。

如果你想要檢視我的筆記請隨意下載。

結論

在我發表了我的第一篇文章之後，Dave Proffer在Twitter上轉發了我的文章並評論到“打破你#excel沉迷的一些好技巧”。我覺得這句話非常準確，它描述了在我們的生活中使用Excel是有多麼的頻繁。大多數的人只管伸手去用卻從來沒有意識到它的侷限性。我希望這個系列的文章可以幫助大家認識到我們還有其他的替代工具，Python+Pandas是一個極其強大的組合。

打賞支援我翻譯更多好文章，謝謝！
打賞譯者

打賞支援我翻譯更多好文章，謝謝！

任選一種支付方式

用Pandas完成Excel中常見的任務（2）

目前待完成的任務們
2024-04-13
用Python完成Excel的常用操作
2018-09-08
PythonExcel
UAVIA打造遠端操控軍用無人機，完成不可能完成的任務
2018-03-06
無人機
Go Web 應用中常見的反模式
2021-08-14
GoWeb模式
手把手教你用一個簡單的2DCNN完成MNIST數字識別任務！
2018-05-24
CNN
Django 如何使用 Celery 完成非同步任務或定時任務
2023-04-25
Django非同步
任務系統 / 擊殺指定數量怪物後完成任務
2024-09-13
盤點web應用程式中常見的漏洞!
2024-01-10
Web
python excel pandas openpyxl
2024-08-20
PythonExcel
常見的定時任務方案
2024-07-16
Golang 入門 : 等待 goroutine 完成任務
2019-05-24
Golang
Flowable啟動流程例項和查詢任務以及完成任務
2024-03-11
每日任務2
2019-05-03
（新手)使用pandas操作EXCEL
2019-01-08
Excel
Oracle中常見的Hint(一)
2018-11-23
Oracle
Tensorflow2 自定義資料集圖片完成圖片分類任務
2020-06-21
excel中常用函式（二）
2019-02-25
Excel函式
Linux常見的軟體包管理任務
2018-06-30
Linux
flowable 查詢、完成、作廢、刪除任務
2021-11-09
用雲壓力測試工具，如何完成一次測試任務
2020-08-14
如何用python pandas操作excel?
2021-09-11
PythonExcel
Android可見APP的不可見任務棧（TaskRecord）銷燬分析
2019-01-08
AndroidAPP
【Python自動化Excel】pandas處理Excel的“分分合合”
2022-02-22
PythonExcel
PHP DIY 系列------應用篇：2. 延時任務
2020-02-25
PHP
如何高效完成領導安排的複雜工作任務？羅列待辦任務清單很有效
2024-03-05
【Python自動化Excel】pandas處理Excel資料的基本流程
2022-01-09
PythonExcel
人性化-完成需要人類感官的任務的機器人
2020-08-31
機器人
CSS居中常見方法
2019-01-14
CSS
任務2 使用Canva的繪圖工具
2024-09-23
繪圖
Flink Yarn的2種任務提交方式
2021-10-24
Yarn
使用quartz.net 完成一個winform版的任務提醒工具
2021-03-14
quartzORM
2Do for Mac(任務管理GTD工具)2.8.3啟用版
2023-09-20
Mac
CTF中常見的四種python逆向
2024-04-18
Python
逆向中常見的加密演算法
2024-03-29
加密演算法
windows 中常見的一些埠
2018-04-18
Windows
CTF中常見的加解密（經典）
2018-03-26
解密
mvc中常見的屬性驗證
2021-12-03
MVC
Redis 中常見的叢集部署方案
2022-02-20
Redis
Linux中常見的網路埠號
2022-09-26
Linux

用Pandas完成Excel中常見的任務（2）

設定

篩選資料

處理日期

額外的字串方法

彩蛋任務

結論

打賞支援我翻譯更多好文章，謝謝！

相關文章