Excel и Python. Создание сводной таблицы при помощи Python

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

Для начала давайте представим, что мы являемся аналитиками в фирме по продаже компьютеров, программного обеспечения к ним, а также оказываем услуги по техническому сопровождению. Нам поставлена задача проанализировать участие компании в различных аукционах. Таблица с исходными данными представлена ниже:

Аукцион	Контрагент	Контакт	Менеджер	Продукт	Количество	Цена	Статус
424845	Ильин и Ко	Сергей Ильин	Илья Сергеев	Компьютер	4	45 200	на рассмотрении
312058	Ильин и Ко	Сергей Ильин	Илья Сергеев	Софт	2	37 600	на рассмотрении
918390	Ильин и Ко	Сергей Ильин	Илья Сергеев	Тех. сопровождение	2	21 200	в ожидании
997345	Ильин и Ко	Сергей Ильин	Илья Сергеев	Компьютер	5	39 100	отменен
496901	Шахты плюс	Данил Сидоров	Илья Сергеев	Компьютер	3	13 600	выигран
800437	Шахты плюс	Данил Сидоров	Илья Сергеев	Компьютер	1	24 400	в ожидании
967756	Шахты плюс	Данил Сидоров	Илья Сергеев	Софт	1	6 700	на рассмотрении
871434	Альма	Женя Сидин	Илья Сергеев	Тех. сопровождение	2	7 000	в ожидании
131102	Альма	Женя Сидин	Илья Сергеев	Компьютер	4	42 000	отменен
191777	Микрошкин	Сергей Минин	Павел Попов	Компьютер	3	28 900	выигран
225531	Микрошкин	Сергей Минин	Павел Попов	Компьютер	5	15 000	на рассмотрении
159172	Микрошкин	Сергей Минин	Павел Попов	Тех. сопровождение	2	2 300	в ожидании
346287	Микрошкин	Сергей Минин	Павел Попов	Софт	4	46 900	на рассмотрении
170247	Кружка и ложка	Виктор Юдин	Павел Попов	Тех. сопровождение	1	14 800	выигран
769790	Кружка и ложка	Виктор Юдин	Павел Попов	Компьютер	1	47 500	выигран
106612	Кружка и ложка	Виктор Юдин	Павел Попов	Компьютер	5	36 400	отменен
151606	Кружка и ложка	Виктор Юдин	Павел Попов	Монитор	4	9 300	на рассмотрении

Сохраните таблицу в Excel файл, вставив начиная с ячейки А1, а также назовите лист "База". Сохраните файл с названием "Отчет по аукционам.xlsx".

Итак сначала давайте прочитаем данные из Excel файла, создадим Pandas Dataframe и передадим туда данные:

import pandas as pd
import numpy as np

data_pd=pd.read_excel('Отчет по аукционам.xlsx',sheet_names='База')

Отлично, теперь давайте столбец "Статус" переведем в тип "category" для того, что бы в дальнейшем данные у нас были структурированы:

data_pd['Статус'] = data_pd['Статус'].astype('category')
data_pd['Статус'].cat.set_categories(['выигран','в ожидании','на рассмотрении','отменен'],inplace=True)

Выводить данные мы можем как в консоль Python, так и в Excel файл. Выберем второй вариант. Эта строка должна быть последней в коде, весь остальной код, который мы будем рассматривать ниже, должен располагаться перед этой строкой. Сохраните скрипт в туже папку, что и файл Отчет по аукционам.xlsx:

data_pt.to_excel('result.xlsx')

Сводная таблица в Python

Cоздать сводную таблицу в Python при помощи пакета Pandas очень просто. К примеру давайте создадим сводную таблицу по столбцу Контрагент:

data_pt = pd.pivot_table(data_pd,index=['Контрагент'])

Откройте файл result.xlsx, который скрипт создал в той же папке, где он располагается. Результат должен быть следующего вида:

Мы можем создать сводную таблицу по нескольким индексируемым столбцам:

data_pt = pd.pivot_table(data_pd,index=['Контрагент', 'Контакт', 'Менеджер'])

По умолчанию сводная таблица выводится по всем числовым полям, однако это не всегда удобно, а иногда и лишено смысла, поэтому можно выводить сводные данные только по отдельным столбцам. Для примера выведем только столбец "Стоимость", для этого добавим параметр values=['Стоимость']:

data_pt = pd.pivot_table(data_pd,index=['Менеджер', 'Контакт'], values=['Стоимость'])

Столбец стоимость по умолчанию выводит среднее значение, однако нам скорее интересна сумма продаж. Добавляем параметр aggfunc=np.sum:

data_pt = pd.pivot_table(data_pd,index=['Менеджер', 'Контакт'], values=['Стоимость'], aggfunc=np.sum)

С помощью aggfunc можно выводить несколько значений, к примеру средную стоимость и количество продаж:

data_pt = pd.pivot_table(data_pd,index=['Менеджер', 'Контакт'], values=['Стоимость'], aggfunc=[np.mean,len])

Также как в Excel, в Pandas индексируемые параметры можно выводить не только в строки, но и в столбцы, для этого служит параметр columns. Например выведем в столбцы наименование продуктов:

data_pt = pd.pivot_table(data_pd,index=['Менеджер', 'Контакт'], values=['Стоимость'], columns=['Продукт'], aggfunc=np.sum)

Наверное вы обратили внимание, что в ячейках, где нет данных пусто, хотя нам привычнее, что бы в таких полях указывалось бы значение 0. Добавим параметр fill_value=0:

data_pt = pd.pivot_table(data_pd,index=['Менеджер', 'Контакт'], values=['Стоимость'], columns=['Продукт'], aggfunc=np.sum ,fill_value=0)

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

data_pt = pd.pivot_table(data_pd,index=['Менеджер', 'Контакт'], values=['Стоимость', 'Количество'], columns=['Продукт'], aggfunc=np.sum ,fill_value=0)

Как и в Excel, мы можем перемещать индексируемые поля между столбцами и строками. К примеру перенесем "Продукт" из столбцов в строки:

data_pt = pd.pivot_table(data_pd,index=['Менеджер', 'Контакт', 'Продукт'], values=['Стоимость', 'Количество'], aggfunc=np.sum ,fill_value=0)

Если нужно добавить итоговую строчку в таблицу, то за это отвечает параметр margins=True:

data_pt = pd.pivot_table(data_pd,index=['Менеджер', 'Контакт', 'Продукт'], values=['Стоимость', 'Количество'], aggfunc=[np.sum,np.mean],fill_value=0,margins=True)

Как мы помним исходной таблице у нас есть столбец "Статус", которому мы присвоили тип категория. Давайте проанализируем работу наших менеджеров этому параметру. Обратите внимание на то, что статусы выводятся именно в том порядке, что мы определили выше:

data_pt = pd.pivot_table(data_pd,index=['Менеджер', 'Статус'], values=['Стоимость'], aggfunc=[np.sum],fill_value=0,margins=True)

Еще одной очень удобной функцией в сводных таблицах Pandas является то, что для каждого типа значений можно выбирать, какую функцию к ним применить. К примеру для "Количество" мы хотим отражать количество продаж, а для "Стоимость" - сумму продаж:

data_pt = pd.pivot_table(data_pd,index=['Менеджер', 'Статус'], values=['Количество', 'Стоимость'], columns=['Продукт'], aggfunc={'Количество':len, 'Стоимость':np.sum},fill_value=0)

Также для отдельного значения мы можем использовать несколько агригирующих функций:

data_pt = pd.pivot_table(data_pd,index=['Менеджер', 'Статус'], values=['Количество', 'Стоимость'], columns=['Продукт'], aggfunc={'Количество':len, 'Стоимость':[np.sum,np.mean]},fill_value=0)

Также мы можем фильтровать данные, выводя только те записи, которые нам интересны. К примеру выведем продажи только менеджера "Илья Сергеев":

data_pt = data_pt.query('Менеджер == ["Илья Сергеев"]')

Или к примеру мы можем вывести только те продажи, у которых статус "выигран" или "в ожидании":

data_pt = data_pt.query('Статус == ["выигран", "в ожидании"]')