Типичные задачи Excel, продемонстрированные в pandas (часть 2)

Введение¶

В первой статье я сосредоточился на распространенных математических задачах, выполняемых в Excel, и их аналогах в pandas. В этой статье я сосредоточусь на некоторых типичных задачах выбора и фильтрации и покажу, как сделать то же самое в pandas.

Оригинал статьи Криса по ссылке.

Подготовка к настройке¶

Импортируйте модули pandas и numpy:

Загрузите данные в формате Excel, представляющие годовой объем продаж компании:

In [2]:

df = pd.read_excel("https://github.com/dm-fedorov/pandas_basic/blob/master/%D0%B1%D1%8B%D1%81%D1%82%D1%80%D0%BE%D0%B5%20%D0%B2%D0%B2%D0%B5%D0%B4%D0%B5%D0%BD%D0%B8%D0%B5%20%D0%B2%20pandas/data/sample-salesv3.xlsx?raw=True")
df.head()

Out[2]:

	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

Взгляните на типы данных, чтобы убедиться, что все прошло должным образом:

In [3]:

df.dtypes

Out[3]:

account number      int64
name               object
sku                object
quantity            int64
unit price        float64
ext price         float64
date               object
dtype: object

Видим, что столбец date отображается как object, т.е. как строка. Преобразуем его в datetime, чтобы упростить себе задачу в дальнейшем:

In [4]:

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

Out[4]:

	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

In [5]:

df.dtypes

Out[5]:

account number             int64
name                      object
sku                       object
quantity                   int64
unit price               float64
ext price                float64
date              datetime64[ns]
dtype: object

Фильтрация данных¶

Думаю, что одна из самых удобных функций Excel - это фильтр. Полагаю, что каждый раз, когда кто-то получает Excel файл любого размера и хочет отфильтровать данные, он пользуется функцией filter.

Вот изображение ее использования для представленного набора данных:

Подобно функции фильтрации в Excel, вы можете использовать pandas для фильтрации и выбора определенных подмножеств данных.

Например, если мы хотим просто увидеть конкретный номер учетной записи, то можем легко сделать это с помощью Excel или pandas.

Вот решение для фильтрации в Excel:

В pandas это сделать относительно просто.

Обратите внимание, что я использую функцию head для показа верхних результатов. Это сделано исключительно для того, чтобы статья выглядела короче:

In [6]:

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

Out[6]:

	account number	name	sku	quantity	unit price	ext price	date
3	307599	Kassulke, Ondricka and Metz	S1-65481	41	21.05	863.05	2014-01-01 15:05:22
13	307599	Kassulke, Ondricka and Metz	S2-10342	17	12.44	211.48	2014-01-04 07:53:01
34	307599	Kassulke, Ondricka and Metz	S2-78676	35	33.04	1156.40	2014-01-10 05:26:31
58	307599	Kassulke, Ondricka and Metz	B1-20000	22	37.87	833.14	2014-01-15 16:22:22
70	307599	Kassulke, Ondricka and Metz	S2-10342	44	96.79	4258.76	2014-01-18 06:32:31

Вы также можете выполнить фильтрацию на основе числовых значений. Я не собираюсь больше приводить примеры в Excel. Уверен, что вы уловили идею.

In [7]:

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

Out[7]:

	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:

In [8]:

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

Out[8]:

	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

С помощью & легко связать два или более операторов в цепочку:

In [9]:

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

Out[9]:

	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. Она позволяет определить список значений, которые мы хотим найти.

Далее мы ищем все записи, которые включают два номера счетов:

In [10]:

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

Out[10]:

	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, показанному выше:

In [11]:

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

Out[11]:

	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, вы можете выполнять сложную фильтрацию по датам. Прежде чем делать что-либо с датами, я рекомендую отсортировать их по столбцу даты, чтобы убедиться, что результаты возвращают то, что вы ожидаете:

In [12]:

df = df.sort_values(by=['date'])
df.head()

Out[12]:

	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, показанный ранее, работает с датами:

In [13]:

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

Out[13]:

	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 - это то, что он понимает даты, что позволяет нам выполнять частичную фильтрацию.

Если хотим найти данные, начиная с определенного месяца:

In [14]:

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

Out[14]:

	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

Конечно, можно объединить критерии фильтрации:

In [15]:

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

Out[15]:

	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 понимает столбцы с датами, то вы можете выразить значение даты в разных форматах:

In [16]:

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

Out[16]:

	account number	name	sku	quantity	unit price	ext price	date
1141	307599	Kassulke, Ondricka and Metz	B1-50809	25	56.63	1415.75	2014-10-01 10:56:32
1142	737550	Fritsch, Russel and Anderson	S2-82423	38	45.17	1716.46	2014-10-01 16:17:24
1143	737550	Fritsch, Russel and Anderson	S1-47412	6	68.68	412.08	2014-10-01 22:28:49
1144	146832	Kiehn-Spinka	S2-11481	13	18.80	244.40	2014-10-02 00:31:01
1145	424914	White-Trantow	B1-53102	9	94.47	850.23	2014-10-02 02:48:26

In [17]:

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

Out[17]:

	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:

In [18]:

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

Out[18]:

	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

Выполним срез (slic), чтобы получить диапазон:

In [19]:

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

Out[19]:

	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

Еще раз, мы можем использовать различные представления даты, чтобы устранить любую двусмысленность в соглашениях об именах дат:

In [20]:

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

Out[20]:

	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

In [21]:

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

Out[21]:

	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

In [22]:

df2.loc["2014"].head()

Out[22]:

	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

In [23]:

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

Out[23]:

	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. В этом случае мы знаем, что артикул всегда представлен одинаково, поэтому B1 отображается только перед артикулом:

In [24]:

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

Out[24]:

	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

Мы можем объединить запросы и использовать sort_values для управления порядком данных:

In [25]:

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

Out[25]:

	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 для столбца, чтобы получить список:

In [26]:

df["name"].unique()

Out[26]:

array(['Barton LLC', 'Trantow-Barrows', 'Kulas Inc',
       'Kassulke, Ondricka and Metz', 'Jerde-Hilpert', 'Koepp Ltd',
       'Fritsch, Russel and Anderson', 'Kiehn-Spinka', 'Keeling LLC',
       'Frami, Hills and Schmidt', 'Stokes LLC', 'Kuhn-Gusikowski',
       'Herman LLC', 'White-Trantow', 'Sanford and Sons', 'Pollich LLC',
       'Will LLC', 'Cronin, Oberbrunner and Spencer',
       'Halvorson, Crona and Champlin', 'Purdy-Kunde'], dtype=object)

Если бы мы хотели включить account number (номер учетной записи), то могли бы использовать drop_duplicates:

In [27]:

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

Out[27]:

	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

Очевидно, что мы собираем больше данных, чем нам нужно, и получаем некоторую бесполезную информацию, поэтому выберите только первый и второй столбцы с помощью iloc:

In [28]:

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

Out[28]:

	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) ретвитнул мой пост и сказал: «Хорошие советы избавляют нас от #excel зависимости». Я думаю, что это точный способ описать, как часто используется Excel сегодня. Множество людей сразу тянутся к Excel, не осознавая, насколько это может быть ограничивающим. Я надеюсь, что эта серия статей поможет людям понять, что существуют альтернатива и python + pandas - чрезвычайно мощная комбинация.

Подписка на онлайн-обучение