Технологии Blogger.

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

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

Для начала давайте представим, что мы являемся аналитиками в фирме по продаже компьютеров, программного обеспечения к ним, а также оказываем услуги по техническому сопровождению. Нам поставлена задача проанализировать участие компании в различных аукционах. Таблица с исходными данными представлена ниже:
Аукцион Контрагент Контакт Менеджер Продукт Количество Цена Статус
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, который скрипт создал в той же папке, где он располагается. Результат должен быть следующего вида:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На сегодня все. Надеюсь эта статья была вам полезна, спасибо за то что прочитали. Остались вопросы - задавайте в комментариях под статьей.
Опубликовать в Google Plus