Как составить агрегированную таблицы

Составить агрегированный баланс организации

Агрегирование баланса — это объединение однородных по экономическому содержанию статей баланса. При этом статьи актива баланса перегруппировываются по степени их ликвидности. Под ликвидностью активов понимают их способность превращения в денежную форму. Так, наиболее ликвидными являются денежные средства и ценные бумаги, а наименее ликвидными — основные средства. Текущие пассивы перегруппировываются по сроку погашения.

Таблица 1

Схема расчета укрупненных статей баланса

Для определения кредитоспособности заемщика проводится оценка его финансового состояния. Цель анализа — получение объективной оценки его финансового положения, способности заемщика своевременно и в полном объеме погасить задолженность по ссуде. Для оценки кредитоспособности заемщика с точки зрения банка предстоит рассчитать для нее коэффициенты ликвидности и финансовой устойчивости.

Банки оценивают финансовое состояние заемщиков по коэффициентам ликвидности (абсолютной, текущей и покрытия), финансового риска (соотношению собственных и заемных средств), а также рентабельности.

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

Формула 1. Расчет коэффициента абсолютной ликвидности

Промежуточный коэффициент покрытия или критической (быстрой) ликвидности говорит о способности компании погасить свои текущие обязательства в критической ситуации только за счет дебиторской задолженности, денежных средств и ликвидных финансовых вложений. Предполагается, что ликвидационная стоимость запасов нулевая. Коэффициент определяется по формуле 2.

Формула 2. Расчет коэффициента критической ликвидности

Общий коэффициент покрытия или текущей ликвидности свидетельствует о достаточности оборотного капитала для погашения краткосрочных обязательств компании. Вычисляется по формуле 3.

Формула 3. Расчет коэффициента текущей ликвидности

Коэффициент соотношения собственных и заемных средств или финансового риска показывает, сколько собственных средств компании приходится на рубль заемных. Для его расчета применяется формула 4.

Формула 4. Расчет коэффициента соотношения собственных и заемных средств

Кроме прочего, для анализа кредитоспособности рассчитываются два показателя рентабельности – продаж и деятельности в целом.

Формула 5. Расчет рентабельности продаж

Формула 6. Расчет рентабельности деятельности

Агрегированный баланс – общее понятие и предназначение

Агрегированный баланс – это обобщенная (укрупненная) форма бухгалтерского баланса, благодаря использованию которой процедура проведения финансового анализа упрощается. вы узнаете, как правильно составить отчет о финансовых результатах фирмы.

Следует отметить, что даже стандартный бухгалтерский баланс в определенной степени является агрегированным из-за того, что некоторые приведенные в нем данные имеют схожий экономический смысл и объединяются в статьи. Об этом говорится в Приказе Минфина № 66н от 2 июля 2010 года.

Агрегированный баланс обладает рядом особенностей, а именно:

• Максимально упрощен, поэтому не вызывает сложностей при изучении и позволяет быстро выделить основные показатели, оказывающие влияние на экономическое положение организации;
• Содержит данные, предназначенные для расчета коэффициента финансовой устойчивости, индекса деловой активности, ликвидности;
• Формируется различными способами, ведь единого алгоритма укрупнения не существует;
• Имеет структуру стандартного баланса – преобразуются только статьи;
• Уровень достоверности проведенного анализа зависит от степени агрегирования информации: чем сильнее укрупнены данные, тем ниже качество оценки.

Как уже говорилось выше, агрегированный бухгалтерский баланс требуется при проведении анализа. На его основе производится оценка двух важных показателей, один из которых – ликвидность, второй – платежеспособность предприятия. Они взаимосвязаны друг с другом.

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

Структуру и основные правила составления баланса можно посмотреть в этом видео:

Степени агрегирования

Общее количество степеней агрегирования бухгалтерского баланса назвать невозможно. Это связано с тем, что отчетный документ может быть составлен несколькими способами и предназначаться для проведения различного по глубине анализа.

Наиболее известны 2 степени агрегирования баланса – высшая и предшествующая ей. В первом случае данные максимально укрупнены и входят либо в раздел «Активы», либо в раздел «Пассивы», а единственным числовым показателем остается валюта.

Во втором случае упомянутые выше статьи детализированы. Так раздел «Активы» может включать в себя пункты «Внеоборотные активы» и «Оборотные активы», а в раздел «Пассивы» – подразделы «Капитал и резервы», «Долгосрочные обязательства», «Краткосрочные обязательства».

Составление и оформление баланса

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

Чтобы сформировать агрегированный баланс, необходимо:

1. Располагать оборотно-сальдовой ведомостью и стандартным бухгалтерским отчетом;
2. Проанализировать содержание документов и выделить в них подразделы, которые можно объединить;
3. Используя Microsoft Excel или аналогичную программу, составить отчет в виде таблицы, включающей в себя столбцы (их число зависит от степени агрегирования) с уже обобщенной информацией.

Это важно! Объединение разделов стандартного баланса должно иметь экономическое обоснование. В частности активы группируются по степени ликвидности, а пассивы – по срокам погашения.

Баланс для анализа финансовой устойчивости.

Пример статей и кодов строк

Готовый агрегированный баланс может выглядеть следующим образом:

Каким бывает анализ агрегированного баланса

Агрегированная форма баланса используется для проведения двух видов анализа. Первый из них – вертикальный, а второй – горизонтальный. Краткие описания обоих приведены ниже.

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

При проведении горизонтального анализа данные из агрегированного баланса сравниваются с аналогичными значениями, полученными ранее, например, месяц или год назад.

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

Представленные виды анализа дополняют друг друга. Их единовременное проведение позволяет сформировать общее представление об экономическом положении организации.

Пример агрегированного баланса.

Статьи баланса

Количество статей в описываемой форме бухгалтерского баланса зависит от степени агрегирования. Если последняя является высшей, то документ имеет такую структуру, в которой есть всего 2 раздела: «Активы» и «Пассивы». Если баланс более развернут, то статей в нем гораздо больше.

вы узнаете, как правильно рассчитать и определить чистые активы в балансе.

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

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

Пример составления агрегированного баланса можно посмотреть тут:

Агрегирование баланса

Источником информации при проведении финансового анализа служит бухгалтерский баланс. Бухгалтерский баланс – это отчетный документ, представляющий собой детализированный перечень в стоимостной оценке имущества предприятия (активы) и источников их возникновения (пассивы).

Для удобства чтения данных и проведения анализа производят преобразование стандартной формы бухгалтерского баланса в укрупненную (агрегированную) форму.

Основным отличием агрегированного баланса от стандартного является перегруппировка статей бухгалтерского баланса, объединяющая статьи бухгалтерского баланса с одинаковым экономическим содержанием.

Форма агрегированного баланса более удобна для чтения и проведения анализа, она позволяет выделить ключевые элементы, характеризующие состояние компании. Кроме того, подобная форма представления информации близка (методологически и терминологически) к используемым в мировой практике формам балансовых отчетов. Корректное агрегирование статей бухгалтерского баланса является основой для проведения качественного финансового анализа.

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

Агрегированный баланс предприятия, пример составления

При составлении агрегированного баланса сохраняется структура исходного баланса – выделение постоянных и текущих активов, собственного и заемного капитала, сохраняется равенство общих величин (сальдо) актива и пассива баланса. Однако внутри разделов осуществляется ряд преобразований.

Стоит отметить важную особенность агрегирования в финансовом анализе. Уровень укрупнения данных определяет уровень пригодности их для анализа. Чем больше данные агрегированы, тем менее качественный анализ можно провести. Следует подчеркнуть, что единого алгоритма агрегирования баланса для всех известных в практике форматов не существует. Проводя такое агрегирование, необходимо руководствоваться, прежде всего, здравым смыслом и логикой последующего анализа, в основе которой лежит подразделение активов и пассивов на долгосрочные и краткосрочные.

Тестовая база к разделу «Анализ финансовой отчетности»

(правильный вариант ответа выделен жирным шрифтом)

1. Понятие «ликвидность активов» означает:

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

2. Период, в течение которого имущество предприятия полностью изнашивается и подлежит ликвидации

3. Способность превращения активов в денежную форму в короткий срок и без существенного снижения их стоимости

4. Период ликвидации имущества предприятия при банкротстве

2. Агрегирование (уплотнение) баланса осуществляется:

1. Вычитанием средних арифметических сумм

2. Объединением в группы однородных статей

3. Исключением регулирующих статей

3. Ликвидность баланса отражает:

1. Состояние имущества и обязательств, при котором предприятие подлежит ликвидации

2. Степень покрытия обязательств предприятия его активами, срок превращения которых в денежную форму соответствует сроку погашения обязательств

3. Период утраты платежеспособности предприятия

4. В агрегированном балансе-нетто статьи актива баланса могут быть сгруппированы по:

1.Принадлежности капитала

2. Степени ликвидности

3. Продолжительности использования капитала

5. Статьи пассива агрегированного баланса могут быть сгруппированы по:

>1.Степени ликвидности

2.Срочности оплаты (степени востребования)

3.Принадлежности капитала

6. К внеоборотным активам относятся:

1. Расчеты по дивидендам

2. Основные средства

3. Краткосрочные финансовые вложения

7. К оборотным активам относятся:

1. Запасы

2. Долгосрочные финансовые вложения

3. Нематериальные активы

8. Эффективность использования оборотных активов характеризуется:

1. Рентабельностью оборотных активов

2. Структурой оборотных средств

3. Структурой капитала

9. Коэффициент текущей ликвидности характеризует:

1. Способность организации отвечать по своим долгосрочным обязательствам

2. Достаточность средств для погашения обязательств в случае его ликвидации

3. Способность отвечать по своим краткосрочным обязательствам в полном объеме и в надлежащие сроки, исходя из имеющихся оборотных активов

10. Чистые оборотные активы определяются так:

1. Активы — Обязательства

2. Собственный капитал — Обязательства

11. В величину стоимости чистых активов не включаются статьи:

1. Нематериальные активы

2. Прочие внеоборотные активы

3. Собственные акции, выкупленные у акционеров

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

1. Расчеты по дивидендам

2. Прочие краткосрочные пассивы

3. Доходы будущих периодов

13. При расчете чистых активов в состав активов не включается статья:

1. Дебиторская задолженность, срок погашения которой превышает 12 месяцев

2. Прочие оборотные активы

3. Задолженность участников (учредителей) по взносам в уставный капитал

14. В величину стоимости чистых активов не включаются статьи:

1. Нематериальные активы

2. Прочие внеоборотные активы

3. Собственные акции, выкупленные у акционеров

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

1. Расчеты по дивидендам

2. Прочие краткосрочные пассивы

3. Доходы от будущих периодов

16. При расчете чистых активов в состав активов не включается статья:

1. Дебиторская задолженность, срок погашения которой превышается 12 месяцев

2. Прочие оборотные активы

3. Задолженность участников (учредителей) по взносам в уставный капитал

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

1. Расчеты по дивидендам

2. Прочие краткосрочные пассивы

3. Резерв по сомнительным долгам

18. При расчете чистых активов в состав активов не включается статья:

1. Дебиторская задолженность, срок погашения которой превышается 12 месяцев

2. Расходы будущих периодов

3. Задолженность участников (учредителей) по взносам в уставный капитал

19. В числитель коэффициента текущей ликвидности не включается:

1. Долгосрочная дебиторская задолженность

2. Прочие оборотные активы

3. Расходы в незавершенном производстве

20. В состав краткосрочных обязательств при расчете коэффициента абсолютной ликвидности не включаются:

1. Задолженность по выплате дивидендов

2. Доходы будущих периодов

3. Прочие краткосрочные пассивы

21. В числе коэффициента абсолютной ликвидности отражается:

1. Денежные средства

2. Денежные средства и их эквиваленты

3. Денежные средства и дебиторская задолженность покупателей с устойчивым финансовым положением

22. В величину срочных обязательств, принимаемых в расчет чистых оборотных активов, не включается статья:

1. Расчеты по дивидендам

2. Прочие краткосрочные пассивы

3. Резервы предстоящих расходов (или добавочный капитал)

23. К ликвидным активам 2 класса можно отнести:

1. Краткосрочная дебиторская задолженность (менее 12 месяцев)

2. Ценные бумаги, не имеющие рыночной котировки

3. Просроченная дебиторская задолженность

4. Готовая продукция, на которую заключены договора с покупателями

24. К ликвидным активам 3 класса можно отнести:

1. Товары, предназначенные для оптовой продажи на условиях предоплаты

2. Ценные бумаги, не имеющие рыночной котировки

3. Просроченная дебиторская задолженность

4. Непросроченная дебиторская задолженность

5. Готовая продукция

25. К ликвидным активам 2 класса можно отнести:

1. Краткосрочная дебиторская задолженность

2. Ценные бумаги со сроком реализации 3 банковских дня

3. Денежные средства на расчетном счете, блокированные в связи с выставлением аккредитива

4. Денежные средства в кассе

5. Готовая продукция, предназначенная к отгрузке в счет поступившей от покупателя предоплаты – если в вопросе будет 3 класса

26. К ликвидным активам 1 класса можно отнести:

1. Товары, предназначенные для оптовой продажи на условиях предоплаты

2. Ценные бумаги со сроком реализации 3 банковских дня и Денежные средства в кассе

3. Денежные средства на расчетном счете, блокированные в связи с выставлением аккредитива

4. Готовая продукция, предназначенная к отгрузке в счет поступившей от покупателя предоплаты

27. К ликвидным активам 1 класса нельзя отнести:

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

2. Ценные бумаги со сроком реализации 3 банковских дня и Денежные средства в кассе

28. Оборачиваемость готовой продукции рассчитывается исходя из:

1. Полная себестоимость произведенной продукции

2. Полная себестоимость реализованной продукции

3. Плановая производственная себестоимость продукции

4. Выручки от продаж

29. К ликвидным активам 3 класса можно отнести:

1. Запасы, дебиторы, готовая продукция

2. Запасы, НДС по приобретенным ценностям, авансы полученные

3. Материалы, незавершенное производство, готовая продукция, НДС по приобретенным ценностям, товары отгруженные

4. Запасы, НДС по приобретенным ценностям, дебиторская задолженность сроком погашения свыше 12 месяцев, авансы выданные

Каждую минуту кто-то в мире совершает покупку или просто заходит на сайт интернет-магазина. Сегодня даже самые маленькие фирмы собирают большое количество данных о покупках, клиентах и партнерах. Данные такого объема бывает трудно помещать на один график и анализировать визуально, поэтому аналитикам нередко приходится прибегать к сжатию данных — агрегации.

В этом уроке вы узнаете, что такое агрегация и как работать с агрегированными данными. Мы изучим пять основных агрегаций и научимся построить их в Google Sheets.

Что такое агрегация

Агрегация данных — это способ преобразования набора данных в одно результирующее значение. Это значение описывает исходный набор данных с точки зрения того, какая именно агрегация была выбрана.

На схеме агрегацию данных можно изобразить так:

К основным агрегациям можно отнести:

• Количество данных
• Сумма значений
• Среднее значение
• Минимальное значение
• Максимальное значение

Агрегированные данные — это набор нескольких значений, описывающих исходные данные. Агрегированные данные позволяют охарактеризовать данные вне зависимости от их объема в наборе. Чем больше агрегаций, тем точнее.

Далее на примере аналитической задачи мы разберем основные функции агрегации и узнаем, как их реализовывать в Google Sheets.

Основные агрегации

Представим, что мы аналитики в интернет-магазине «Все для дома». Нам нужно предоставить руководителю отчет о покупках за прошедший месяц. Известно, что ему нужны ответы на следующие вопросы:

• Как часто клиенты покупали у нас?
• Сколько мы заработали денег на покупках?
• Какой средний чек покупки?
• Какая была максимальная покупка?
• Какая была минимальная покупка?

Для ответа на эти вопросы нам предоставили таблицу с суммами покупок за последний месяц. Задача — собрать агрегированные данные по информации о покупках.

Для удобства будем собирать эти данные в такую таблицу:

Количество данных: как часто клиенты покупали у нас?

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

Нам нужно посчитать количество строк в таблице. В Google Sheets это можно сделать с помощью функции COUNT:

• Шаг 1. В нужной ячейке вводим =COUNT
• Шаг 2. Открываем скобки и указываем диапазон значений, для которых нужно посчитать агрегацию
• Шаг 3. Нажимаем Enter

Количество данных для этого набора — 181. Много это или мало? Мы не можем ответить, пока не сравним это количество покупок с той же метрикой за прошлые месяцы.

Сумма значений: cколько мы заработали денег на покупках?

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

Посчитаем сумму всех значений столбца «Сумма покупки» в нашей таблице. В Google Sheets это можно сделать с помощью функции SUM.

• Шаг 1. В нужной ячейке вводим =SUM
• Шаг 2. Открываем скобки и указываем диапазон значений, для которых нужно посчитать агрегацию
• Шаг 3. Нажимаем Enter

Выручка интернет-магазина за этот месяц составила 848 855,7. На этом этапе мы выяснили, что в прошлом месяце 181 покупка принесла 848 855,7 рубля.

Среднее значение: какой средний чек покупки?

Средний чек покупки — это то же самое, что и среднее значение суммы всех покупок.

Посчитаем сумму всех значений и поделим ее на количество значений. В Google Sheets это можно сделать с помощью функции AVERAGE:

• Шаг 1. В нужной ячейке вводим =AVERAGE
• Шаг 2. Открываем скобки и указываем диапазон значений, для которых нужно посчитать агрегацию
• Шаг 3. Нажимаем Enter

Таким образом, средний чек за месяц составил 4 689,81 рубля. Другими словами, именно столько тратят покупатели в нашем интернет-магазине в среднем за одну покупку.

Минимальное значение: какая была минимальная покупка?

Чтобы рассчитать минимальную покупку, нужно найти минимальное значение всех покупок.

Можно отсортировать набор данных по возрастанию и взять самое первое значение. В Google Sheets это можно сделать с помощью функции MIN:

• Шаг 1. В нужной ячейке вводим =MIN
• Шаг 2. Открываем скобки и указываем диапазон значений, для которых нужно посчитать агрегацию
• Шаг 3. Нажимаем Enter

Минимальная сумма покупки месяца составила 1,25. Теперь мы знаем, что за месяц не было ни одной покупки ниже этой суммы. Возможно, за такую цену был приобретен пакет, что может указывать на самый дешевый товар в магазине.

Максимальное значение: какая была максимальная покупка?

Чтобы рассчитать самую дорогую покупку, нам нужно найти максимальное значение среди всех покупок.

Отсортируем набор данных по убыванию и возьмем первое значение. В Google Sheets это можно сделать с помощью функции MAX:

• Шаг 1. В нужной ячейке вводим =MAX
• Шаг 2. Открываем скобки и указываем диапазон значений, для которых нужно посчитать агрегацию
• Шаг 3. Нажимаем Enter

Самая дорогая покупка в прошлом месяце обошлась в 46 935 рублей. Это значение сильно выше среднего чека — значит оно могло сильно сместить расчет среднего чека. Мы можем сделать вывод, что в прогнозах не стоит полагаться на получившееся среднее значение.

В итоге у нас получилась вот такая таблица агрегированных данных:

Краткий отчет может выглядеть следующим образом:

«За прошлый месяц была совершена 181 покупка, что принесло фирме выручку в размере 848 855 рублей. Минимальное и максимальное значения покупок сильно далеки друг от друга, поэтому разброс между суммами покупок большой. Это означает, что средний чек может быть сильно смещен. Вероятно, расчеты среднего чека не показывают, какую сумму среднестатистический покупатель действительно тратит в магазине».

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

Выводы

В этом уроке мы узнали, что такое агрегация и для чего нужно агрегировать данные. Теперь вы умеете реализовать агрегацию в Google Sheets.

Напомним ключевые выводы урока:

Введение¶

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

В pandas функцию groupby можно комбинировать с одной или несколькими функциями агрегирования, чтобы быстро и легко обобщать данные. Эта концепция обманчиво проста и большинство новых пользователей pandas поймут ее. Однако они удивятся тому, насколько полезными могут стать функции агрегирования для проведения сложного анализа данных.

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

Оригниал статьи Криса тут.

Агрегирование¶

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

Наиболее распространенные функции агрегирования — это простое среднее (simple average) или суммирование (summation) значений.

Далее представлен пример расчета суммарной и средней стоимости билетов для набора данных «Титаник», загруженного из пакета seaborn.

15 апреля 1912 года самый большой пассажирский лайнер в истории во время своего первого рейса столкнулся с айсбергом. Когда Титаник затонул, погибли 1502 из 2224 пассажиров и членов экипажа. Эта сенсационная трагедия потрясла международное сообщество и привела к улучшению правил безопасности для судов. Одна из причин, по которой кораблекрушение привело к гибели людей, заключалась в том, что не хватало спасательных шлюпок для пассажиров и экипажа. Несмотря на то, что в выживании после затопления была определенная доля удачи, некоторые группы людей имели больше шансов выжить, чем другие.

In [1]:

import pandas as pd
import seaborn as sns

df = sns.load_dataset('titanic')

Каждая строка набора данных представляет одного человека. Столбцы описывают различные атрибуты, включая то, выжили ли они (survived), их возраст (age), класс пассажира (pclass), пол (sex) и стоимость проезда (fare).

Out[2]:

Эта простая концепция — необходимый строительный блок для более сложного анализа.

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

Что, если вы хотите выполнить анализ только подмножества столбцов?

Есть два других варианта агрегирования: использование словаря и именованное агрегирование (named aggregation).

Использование кортежей (именованное агрегирование):

Важно знать об этих параметрах и понимать, какой из них и когда использовать.

Я предпочитаю использовать словари для агрегирования.

Подход с кортежами ограничен возможностью применять только одно агрегирование за раз к определенному столбцу. Если мне нужно переименовать столбцы, я буду использовать функцию rename после завершения агрегации. В некоторых случаях подход со списком является более рациональным. Тем не менее, я повторю, что, на мой взгляд, словарный подход обеспечивает наиболее надежный способ для большинства ситуаций.

Groupby¶

Теперь, когда мы знаем, как использовать агрегацию, мы можем объединить это с groupby для резюмирования данных.

Основы математики¶

Наиболее распространенными встроенными функциями агрегирования являются базовые математические функции, включая сумму (sum), среднее значение (mean), медианное значение (median), минимум (minimum), максимум (maximum), стандартное отклонение (standard deviation), дисперсию (variance), среднее абсолютное отклонение (mean absolute deviation) и произведение (product).

Мы можем применить все эти функции к fare (стоимости проезда) при группировке по embark_town (городу посадки на корабль):

In [6]:

agg_func_math = {
    'fare': ['sum', 'mean', 'median', 'min', 'max', 'std', 'var', 'mad', 'prod']
}

Это все относительно простая математика.

Кстати, я не нашел подходящего варианта использования функции prod, которая вычисляет произведение всех значений в группе, и включил ее для полноты картины.

Еще один полезный трюк — использовать describe для одновременного выполнения нескольких встроенных агрегаторов:

In [8]:

agg_func_describe = {'fare': ['describe']}

Подсчет¶

После базовой математики подсчет (counting) является следующим наиболее распространенным агрегированием, которое я выполняю для сгруппированных данных.

Он несколько сложнее, чем простая математика. Вот три примера подсчета:

In [10]:

agg_func_count = {'embark_town': ['count', 'nunique', 'size']}

Главное отличие, о котором следует помнить, заключается в том, что count не включает значения NaN, тогда как size их включает. В зависимости от набора данных это различие может оказаться полезным.

Кроме того, функция nunique исключит значения NaN из уникальных счетчиков.

Продолжайте читать, чтобы увидеть пример того, как включить NaN в подсчет уникальных значений.

Первый и последний¶

В следующем примере мы можем выбрать самую высокую и самую низкую стоимость билета в зависимости от города, в котором совершили посадку пассажиры Титаника.

Следует помнить один важный момент: вы должны сначала отсортировать данные, если хотите, чтобы в качестве first (первого) и last (последнего) были выбраны максимальное и минимальное значения.

In [12]:

agg_func_selection = {'fare': ['first', 'last']}

В приведенном выше примере я бы рекомендовал использовать max и min, но для полноты картины включил first и last. В других приложениях (например, при анализе временных рядов) вы можете выбрать значения first и last для дальнейшего анализа.

Другой подход к выбору — использовать idxmax и idxmin для выбора значения индекса, соответствующего максимальному или минимальному значениям.

In [14]:

agg_func_max_min = {'fare': ['idxmax', 'idxmin']}

Можем проверить результаты:

Out[16]:

Вот еще один трюк, который можно использовать для просмотра строк с максимальной стоимостью проезда (fare):

Приведенный выше пример — одно из тех мест, где агрегирование на основе списка является полезным.

Другие библиотеки¶

Вы не ограничены функциями агрегирования в pandas. К примеру, можно использовать функции статистики из scipy или numpy.

Вот пример расчета моды (mode) и асимметрии (skew) данных для стоимости проезда.

In [18]:

from scipy.stats import skew, mode

In [19]:

agg_func_stats = {'fare': [skew, mode, pd.Series.mode]}

Интересны результаты вычисления моды (mode). Функция mode из scipy.stats возвращает наиболее часто встречающееся значение, а также количество вхождений. Если вам просто нужно наиболее частое значение, то используйте pd.Series.mode.

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

Работа с текстом¶

При работе с текстом функции подсчета будут работать должным образом. Вы также можете использовать функцию mode из scipy для текстовых данных.

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

Следующая краткая сводка для class (класса каюты) и deck (палубы) показывает, как данный подход можно использовать:

In [21]:

agg_func_text = {'deck': ['nunique', mode, set]}

Пользовательские функции¶

Стандартные функции агрегирования pandas и функции из экосистемы Python удовлетворят многие ваши потребности в анализе данных. Однако вы, вероятно, захотите создать свои собственные пользовательские функции агрегирования. Есть четыре способа для создания собственных функций.

Чтобы проиллюстрировать различия, давайте вычислим 25-й процентиль данных (также называемый квантилью .25 или нижней квартилью), используя четыре подхода.

Во-первых, мы можем использовать функцию partial:

In [23]:

from functools import partial

In [24]:

q_25 = partial(pd.Series.quantile, q=0.25) # возвращает обортку над pd.Series.quantile()

In [25]:

q_25.__name__ = '25%' # пойдет в наименование будущего столбца

Затем мы определяем нашу собственную функцию (которая представляет собой небольшую обертку для quantile):

In [26]:

def percentile_25(x):
    return x.quantile(.25)

Далее определяем лямбда-функцию и даем ей имя:

In [27]:

lambda_25 = lambda x: x.quantile(.25)

In [28]:

lambda_25.__name__ = 'lambda_25%'

Затем задаем встроенную (inline) лямбду и формируем словарь:

In [29]:

agg_func = {
    'fare': [q_25, percentile_25, lambda_25, lambda x: x.quantile(.25)]
}

Как видите, результаты одинаковые, но названия столбцов немного отличаются. Это область предпочтений программистов, но я рекомендую ознакомиться с вариантами, поскольку вы встретите большинство из них в онлайн-решениях.

Я предпочитаю использовать собственные функции или встроенные (inline) лямбды.

Как и во многих других областях программирования — это элемент стиля и предпочтений, но я рекомендую вам выбрать один или два подхода и придерживаться их для единообразия.

Примеры пользовательских функций¶

Как показано выше, существует несколько подходов к разработке пользовательских функций агрегирования.

В большинстве случаев функции представляют собой легкие обертки (wrappers) для встроенных функций pandas. Они нужны, т.к. нет возможности передать аргументы в агрегаты (aggregations).

Следующие примеры должны пояснить этот момент.

Если вы хотите подсчитать количество нулевых значений, вы можете использовать эту функцию:

In [31]:

def count_nulls(s):
    return s.size - s.count()

Если вы хотите включить значения NaN в свои уникальные счетчики, вам необходимо указать параметр dropna=False у функции nunique.

In [32]:

def unique_nan(s):
    return s.nunique(dropna=False)

Вот результат применения всех функций:

In [33]:

agg_func_custom_count = {
    'embark_town': ['count', 'nunique', 'size', unique_nan, count_nulls, set]
}

Если вы хотите рассчитать 90-й процентиль, используйте quantile:

In [35]:

def percentile_90(x):
    return x.quantile(.9)

Если вы хотите вычислить усеченное среднее (trimmed mean) значение, из которого исключен самый низкий 10-й процент, используйте функцию trim_mean из scipy:

In [36]:

from scipy.stats import trim_mean

def trim_mean_10(x):
    return trim_mean(x, 0.1)

Если вы хотите получить наибольшее значение, независимо от порядка сортировки (см. ранее в этом Блокноте о first и last):

In [37]:

def largest(x):
    return x.nlargest(1)

Это эквивалентно max, но я приведу еще один пример с nlargest ниже, чтобы подчеркнуть разницу.

Ранее я уже писал о sparkline. Обратитесь к этой статье за инструкциями по установке.

Вот как включить их в агрегатную функцию для уникального представления данных:

In [38]:

#!pip3 install sparklines

In [39]:

from sparklines import sparklines

In [40]:

import numpy as np

def sparkline_str(x):
    bins = np.histogram(x)[0]
    sl = ''.join(sparklines(bins))
    return sl

In [41]:

agg_func_largest = {
    'fare': [percentile_90, trim_mean_10, largest, sparkline_str]
}

Функции nlargest и nsmallest могут быть полезны для резюмирования данных в различных сценариях.

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

In [43]:

def top_10_sum(x):
    return x.nlargest(10).sum()

In [44]:

def bottom_10_sum(x):
    return x.nsmallest(10).sum()

In [45]:

agg_func_top_bottom_sum = {
    'fare': [top_10_sum, bottom_10_sum]
}

Использование этого подхода может быть полезно для применения закона Парето к вашим собственным данным.

Пользовательские функции с несколькими столбцами¶

Если у вас есть сценарий, в котором небходимо запустить несколько агрегаций по столбцам, то вы можете использовать groupby в сочетании с apply, как описано в этом ответе на stack overflow.

Используя этот метод, вы получите доступ ко всем столбцам данных и сможете выбрать подходящий способ агрегирования для создания итогового DataFrame (включая наименование столбцов):

In [47]:

def summary(x):
    result = {
        'fare_sum': x['fare'].sum(),
        'fare_mean': x['fare'].mean(),
        'fare_range': x['fare'].max() - x['fare'].min()
    }
    return pd.Series(result).round(0)

Использование apply с groupby дает максимальную гибкость. Однако есть и обратная сторона. Функция apply работает медленно, поэтому этот подход следует использовать с осторожностью.

Работа с групповыми объектами¶

После группировки и агрегирования данных вы можете выполнять дополнительные вычисления для сгруппированных объектов.

В следующем примере определим, какой процент от общего количества проданных билетов можно отнести к каждой комбинации embark_town и class.

Мы используем метод assign() и лямбда-функцию для добавления столбца pct_total:

Следует отметить, что можно сделать проще с использованием кросс-таблицы pd.crosstab, как описано в статье:

Пока мы говорим о crosstab (кросс-таблицах), полезно иметь в виду, что функции агрегации также можно комбинировать со сводными таблицами (pivot tables).

Иногда необходимо выполнить множество группировок (multiple groupby), чтобы ответить на вопрос. Например, если мы хотим увидеть кумулятивную сумму стоимости билетов, мы можем сгруппировать и агрегировать по городу (town) и классу (class), затем сгруппировать полученный объект и вычислить кумулятивную сумму (cumulative sum):

Это может быть сложным для понимания. Вот краткое пояснение того, что мы делаем:

Пример с данными о продажах¶

В следующем примере резюмируем ежедневные данные о продажах и преобразуем их в совокупное ежедневное и ежеквартальное представление.

Обратитесь к статье о Grouper, если вы не знакомы с использованием метода pd.Grouper().

В этом примере мы хотим включить сумму ежедневных продаж, а также совокупную (cumulative) сумму за квартал:

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

Если вы хотите просто получить совокупный (cumulative) квартальный итог, вы можете связать несколько функций groupby.

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

В этом примере я включил именованный подход агрегации (named aggregation approach), чтобы переименовать переменную и уточнить, что теперь это ежедневные продажи. Затем я снова группирую и использую совокупную (cumulative) сумму, чтобы получить текущую сумму за квартал. Наконец, я переименовал столбец в квартальные продажи (quarterly sales).

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

Не расстраивайтесь!

Сглаживание иерархических индексов столбцов¶

По умолчанию pandas в сводном DataFrame создает иерархический индекс у столбца:

В какой-то момент в процессе анализа вы, вероятно, захотите «сгладить» (flatten) столбцы, чтобы получилась одна строка с именами.

Я обнаружил, что мне лучше всего подходит следующий подход.

Я использую параметр as_index=False при группировке, а затем создаю новое имя свернутого (collapsed) столбца.

Вот код:

Вот изображение, показывающее, как выглядит сплющенный кадр данных:

Я предпочитаю использовать _ в качестве разделителя, но вы можете использовать другие значения. Просто имейте в виду, что для последующего анализа будет проще, если в именах результирующих столбцов нет пробелов.

Промежуточные итоги¶

Если вы хотите добавить промежуточные итоги (subtotal), я рекомендую пакет sidetable.

Инструкция по работе с sidetable на русском языке тут.

Вот как вы можете суммировать fares по class, embark_town и sex с промежуточным итогом на каждом уровне, а также общим итогом внизу:

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

Резюме¶

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

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

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