Привет, Хабр! Меня зовут Панчин Денис, это мой первый пост и я хочу Вам рассказать о работе с сводными таблицами в Pandas. Сводные таблицы хорошо известны всем аналитикам по Excel. Это прекрасный инструмент, который помогает быстро получить различную информацию по массиву данных. Рассмотрим реализацию и тонкости сводных таблиц в Pandas.
Для эксперимента возьмём датасет "Крупные города России: объединенные данные по основным социально-экономическим показателям за 1985-2019 гг.".
Блокнот можно открыть здесь.
Использовать будем только столбцы 'region' (субъект РФ), 'municipality' (муниципальное образование), 'year' (год), 'birth' (число родившихся на 1000 человек населения), 'wage' (cреднемесячная номинальная начисленная заработная плата, руб.). Сразу оговорюсь, что Москва и Санкт-Петербург являются отдельными субъектами Российской Федерации и в этом датафрейме отсутствуют.
import pandas as pd
import numpy as np
df = pd.read_csv('Krupnie_goroda-RF_1985-2019_187_09.12.21/data.csv', sep=';')
df = df[['region', 'municipality', 'year', 'birth', 'wage']]
df.sample(7)Минимальную статистику можно получить использовав метод describe(include = 'all'). Мы видим что у нас 4109 строки, по 81 региону и 202 городам. Средняя рождаемость на 1000 человек 11,39, минимальная — 3,4, максимальная - 36,1.
df.describe(include = 'all')Агрегирование
Если объяснять простыми словами, то агрегирование — это процесс приведения некого большого массива значений к некому сжатому по определенному параметру массиву значений. Например, среднее, медиана, количество, сумма.
df[['birth', 'wage']].agg(['mean', 'median', 'min', 'max'])Groupby
Но средняя температура по больнице нам не интересна, мы хотим знать победителей в лицо. Допустим нам нужно посмотреть средние значения с группировкой по городам и субъектам РФ. Для этого закономерно используем метод groupby(['region', 'municipality']).agg('mean').
df_groupby = df.groupby(['region', 'municipality']).agg('mean')
df_groupby.head(7)Обратите внимание, что колонки region и municipality стали индексами.
На этом мы не успокаиваемся и пытаемся выжать больше стат.данных: среднее, медиану, минимум, максимум.
agg_func_math = {
'birth': ['mean', 'median', 'min', 'max'],
'wage': ['mean', 'median', 'min', 'max']
}
df.groupby(['region', 'municipality']).agg(agg_func_math).head(7)Посмотрим топ городов по зарплатам.
df.groupby(['region', 'municipality']).agg('mean').sort_values(by='wage', ascending=False).head(7)А что если посмотреть данные в разрезе по годам...?
Pivot table
Ответим на этот вопрос чуть позже, а пока создадим сводную таблицу аналогичную той, что мы уже делали ранее.
df_pivot_table = df.pivot_table(index=['region', 'municipality'])
df_pivot_table.head(7)Уже на этом этапе видно, что сводная таблица достаточно умная и сама агрегировала данные и посчитала средние значения.
Но если мы захотим расширить таблицу новыми значениями — медианой, то увидим, что конечный результат по структуре будет отличаться от того, что мы делали при использовании метода groupby.
df_pivot_table = df.pivot_table(index=['region', 'municipality'],
values=['birth', 'wage'],
aggfunc=[np.mean, np.median])
df_pivot_table.head(7)Так мы плавно подошли к тем преимуществам, которые делает сводные таблицы швейцарским ножом для аналитиков. Если используя groupby мы «укрупняли» строки по городам, то сейчас мы можем «развернуть» столбец, например, 'year' (год) и посмотреть данные в разрезе по годам.
df_pivot_table = df.pivot_table(index=['region', 'municipality'],
values='birth',
columns='year')
df_pivot_table.head(7)И, конечно, данные в сводной таблице можно фильтровать. Создадим сводную таблицу и оставим в ней данные по городам, в которых рождаемость на 1000 человек превышает 12, зарплата выше 40.000 и отсортируем всё по убыванию рождаемости.
df2 = df.pivot_table(index=['region', 'municipality'])
df2.query("`birth`>12 and `wage`>40000").sort_values(by='birth', ascending=False)Pivot
Всё было замечательно, но в Pandas кроме pivot_table есть ещё просто pivot. Посмотрим что это за зверь и чем они отличаются.
Создадим pivot: рождаемость в разрезе по регионам и годам.
df_pivot = df.pivot(index='region',
values='birth',
columns='year')
df_pivot.head(7)Мы получили ошибку «Index contains duplicate entries, cannot reshape«. Что-то не так с индексами, попробуем создать pivot с индексами по городам, а не регионам.
df_pivot = df.pivot(index='municipality',
values='birth',
columns='year')
df_pivot.head(7)Всё получилось. Как мы уже заметили всё дело в индексах. На самом деле всё в повторяющихся индексах. Как мы видели ранее в датафрейме есть позиции с несколькими городами в рамках одного субъекта, так получаются дублированные данные индексов с которыми не умеет работать pivot.
Вывод
Groupby, pivot и pivot_table удобные и привычные инструменты для работы с данными. Groupby позволяет кодом в одну строку получить агрегированные и сортированные данные, а pivot и pivot_table работать в более глубоком разрезе. Pivot_table предпочтителен, т.к. не ограничивает вас в уникальности значений в столбце индекса. И, конечно, все эти данные можно фильтровать под ваши запросы.
Комментарии (10)
Trabant_Vishnya
29.01.2023 23:04+1Если вопрос в работе с условно большими данными (~1-3млн строк), то тут сценарий Power Pivot как-то лучше реализуется.
Ananiev_Genrih
30.01.2023 07:51+4Согласен. Уже 15 лет прошло а с этими пандасом все носятся как с писанной торбой. Даже сам разработчик pandas Вес Маккини писал Apache Arrow and the "10 Things I Hate About pandas"
Есть куча инструментов позволяющих гораздо большие объемы данных крутить значительно быстрее и комфортнее чем pandas на том же железе. Приходилось крутить сводными табличками по 60-80 миллионов записей в Excel'вском Power Pivot (лимит в 1 миллион строчек Excel тут не преграда) +комфортная low code etl предоработка в том же Excel'вском Power Query- на таких объёмах разница на том же железе была не в пользу pandas . А есть еще и колоночные СУБД типа DuckDB- зверь похожий на Clickhouse с API под Python и R. (На DuckDB приходилось анализировать на локальной машине 150 csv файлов общим весом более 10Gb предварительно перегнав их в в parquet, и настолько приятно что даже помыслов не было поюзать в таком кейсе пандас). А есть еще (py)arrow, которую продвигает Маккини как замена pandas. И помимо субд в том же python такая библиотека как polars легко обойдет pandas на любых операциях.
Ниже по ссылке бенчмарки производительности основных инструментов которые периодически проводит H2O ,так же интересно как обходит пандас R'вский пакет data.table (которым тоже часто пользуюсь), ну и Julia удивила.
И бонус: если у вас в качестве источника csv или паркет - можно приятно крутить данные на чистых сводных таблицах (drag & drop) с DuckDB в качестве движка под капотом - TED
economist75
30.01.2023 09:22Вы назвали классные продукты, но связка Pandas + JupyterLab пока что незаменима для итеративной ELT-хирургии данных, где SQL рулит только на первом этапе. А дальше - векторизация Numpy и "свои" предметно data-ориентированные UDF (введеные как новые методы df/series) - делают работу аналитика быстрее, чем шлифовка select... и возведение трехэтажных with cte... А после того как данные вычищены - да, можно делать сводные в DuckDB+TAD или других связках.
Однопоточный Pandas на объемах до 10 млн. строк, когда все помещается в RAM, заметно (в 2-4 раза) быстрее чем Excel/PowerPivot. Точнее так: он дает результат сразу (разница в миллисекунды - не ощущается).
Polars пока что не умеет в продвинутое индексирование, не все умеет трансформации и решейпинг. Parquet - отличный формат для хранения пережатых "сотен CSV", но опять же, на вменяемых размерах он не особо быстрее Pandas. Бенч датасатанисты боязливо посматривают, но он отражает не всё. Пока что у медлительного бамбукового мишки в рукаве мультиииндексы, всеядность форматов и метод .plot() с разными бэкендами для графиков.
Ananiev_Genrih
30.01.2023 09:51+1В 2023 году данных для анализа даже у рядового аналитика стало существенно больше чем в 2008 когда pandas создавался, и все что смогли в pandas за это время - натянуть его на dask (со своими особенностями и новыми требованиями к железу).
метод .plot() с разными бэкендами для графиков.
Я не то чтобы придираюсь, но и это мне видится никаким не плюсом - не понятно зачем в него понапичкано всяких читалок к БД, корреляций и каких-то там методов .plot. Это как автор данный статьи его обозвал "швейцарский нож" в котором понапихано все, но каждый элемент хуже чем его аналог в отдельности.
Хочешь полноценный инструмент доступа к БД - есть sqlAlchemy, хочешь нормальный перечень стат.методов - есть scipy, хочешь полноценной визулизации - есть seaborn & plotly, хочешь быстрой обработки табличных данных - уже писал выше по пакетам.
А делать все, но на "на минималках", мне кажется уже не тот век для полноценной работы с данными.
PS про индексы стало интересно, в чем их "+" в пандасе? Особенно учитывая что разработчики arrow, polars и data.table считают их "злом" и давно отказались от них, и даже автор статьи на простом кейсе предупреждает новоиспеченных аналитиков:
Что-то не так с индексами, попробуем создать pivot с индексами по городам, а не регионам
economist75
30.01.2023 14:13Аналитики почти ничего не пишут в БД, им не нужен sqlalchemy (и вообще любой ORM). Вполне достаточно pd.readsql() и чистого SQL. Они рисуют до 1 тыс. графиков день, поэтому они используют df.loc[].query().plot(), а не plotly с его тысячей параметров.
Короче говоря, разница в подходах аналитиков и девопсов - в разной терпимости и важности промежуточных результатов, она существенная. У аналитиков часто (и строго) спрашивают как они считали, у девопсов - почти никогда.
Мульти-индексы (МИ) Pandas помогают автоматически решейпить таблицу, вынеся уникальности по строкам - в разные столбцы. Или когда требуется объединить (обогатить) данные однострочником df1,join(df2), безо всяких with cte. Это также здорово ускоряет получение результата "строгой формы", когда МИ используются для заголовков секций строк/столбцов. Вот почему в 98% проектов с Polars и Arrow мы неизменно найдем остаточные следы Pandas. Т.к. она написана поверх Numpy - со скоростью проблем нет, а большая часть бенчей лишь отражает однопоточность "бамбуковых" вычислений. Соглашусь что в bigdata Pandas не пойдет.
Метод pivot() в статье упомянут
всуелишь из-за созвучности с другим методом. Он служит для другого - для "штучной нарезки" строк/столбцов одной таблицы и укладки их в новую. Так как он категорически исключает какое-либо агрегирование данных - отсюда и требование уникальности индекса: двух строк возвращаться при запросе одной строки не должно ни при каких обстоятельствах. .Ananiev_Genrih
31.01.2023 12:46+1а не plotly с его тысячей параметров.
На практике все 1000 параметров мало кто использует ни в plotly ни в LightGBM ни где либо еще (всегда есть топ Х "фичей" инструмента по макс.востребованности, остальное - частные случаи). К примеру ничто не мешает запустить linechart с 2-мя аргументами XY так же как и в plot(), НО тут же под рукой все остальное чтобы добавить и красоты и фасетов по +1 атрибуту и тултипов если надо.
Вот почему в 98% проектов с Polars и Arrow мы неизменно найдем остаточные следы Pandas
Под капотом там мало общего с pandas , как раз из-за такой разобщенности механизмов и виден прирост производительности в сравнении с pandas (с его numpy и Blockmanager). Собственно сам Вес Маккини и пишет о том что из-за такой огромной разницы он не видел способов изменить "легаси"-код pandas настолько кардинально, поэтому родился arrow (и впоследствии polars, так же использующий arrow - но не pandas).
Мне кажется народная популярность pandas сейчас не только в Ваших аргументах, а больше из серии "сначала ты работаешь на свою популярность а потом популярность работает на тебя". То есть сначала для обработки табличных данных не было в python альтернатив, пришел pandas и занял свою нишу, создал "вау"-эффект в свое время, сообщество начало впиливать в него куски какой то статистики и визуализации (ибо на то время что-то нормальное только зарождалось параллельно), потом он достиг пика популярности и на вершине славы остановился с точки зрения тех.развития.
Теперь, все курсы аналитиков /сайнтистов содержат pandas (назовите хоть один без него), студенты сдают проект на нем, приходят джунами указав его в резюме, дорастают до мидла на нем, как случаются неизбежные затыки - одна часть идет на поклон к вечно загруженным дата-инженерам перекладывать РАЗОВУЮ задачу в Airflow/Spark/... , другая часть пытается натянуть pandas на dask, но все равно использовать pandas.
В этом - с одной стороны есть здравое зерно (мы все ленивы, кому нафиг охота учить новый синтаксис когда задачи горят?), с другой стороны репутация pandas - мыльный пузырь, который закрывает кругозор от инструментов, давно переросших pandas. (так кажется лично мне)
Trabant_Vishnya
01.02.2023 00:3360-80 млн крутились в итоге через Direct Query\моделька на SSAS или что-то другое вообще было? Просто интересны такие сценарии, ранее в ритейле только с такими объемами сталкивался, там это в итоге приводило к определенным ограничениям.
Ananiev_Genrih
01.02.2023 10:09не хочется отвечать здесь ибо оффтоп, но если кратко: крутить в сводной 60-80млн без особых тормозов на 8гб оперативки достаточно для локальной книги Excel с Power Pivot, главный секрет - никаких плоских портянок, только схема "звезда" с большими (план)фактовыми таблицами в центре из одних чиселок (ID+фактовые числа максимально стараться в Integer8/32/64 + расчетные меры на DAX) и текстовые справочники вокруг (Dimensions). В пределе терпимо крутит даже 100млн локальной книги на "звезде" . Табулярные модели у нас на SSAS серверах (перешли на них с OLAP кубов) >2 млрд.записей ритейл данных (уже агрегированных) в моменте обслуживают до 150 пользователей с клиентскими подключениями сводных табличек в Excel + юзеры из PowerBI с DirecrQuery коннектами к той же "модельке". А вообще таких решений у нас много (под каждый департамент)
Denis-ulcity Автор
30.01.2023 13:34Со всем полностью согласен, но по классике начинают изучать DS с pandas, поэтому решил, что для начинающих эта статья будет полезной.
sergey-ryzhikov
Статья бы заметно выиграла от добавления в нее примеров получившихся таблиц.