Рассказываю о том, как собирать в Google Sheets удобные аналитические отчеты с помощью формулы =QUERY()
.
Рассказываю о том, как собирать в Google Sheets удобные аналитические отчеты с помощью формулы =QUERY()
.
В начале февраля я подписался на челедндж «Content Hero Global» и обязался каждый день публиковать контент на английском языке. Начал с того. что зарегистрировался в Твиттере, но быстро понял, что мне интереснее писать более объемные заметки, например, разборы книг. В этой серии я адаптирую сайт для работы с несколькими языками и деплою англоязычную версию на новый домен.
gettext
. Не забудьте установить саму утилиту.local
и manage.py makemessages
.manage.py combilemessages
.gettext
и добавить команду, компилирующую переводы.На этом все! Успехов,
Куличевский
P.S. Посмотрите, какой сайт кайфовый получился: https://alexchevsky.com
На неделе я добавил несколько новых фичей на сайт. Теперь с любой страницы сайта можно вернуться на главную, кликнув по логотипу в шапке, в блоге появился рубрикатор категорий, а на в заметки теперь можно расшарить с помощью социальных кнопок.
Доска в Trello: https://smysl.io/trello
Код на Github: https://github.com/alexchevsky/
Представим, что у нас есть список заказов и выгрузка из рекламной системы. Нужно составить отчет, считающий количество заказов, выручку, стоимость заказа и окупаемость маркетинга. В видео рассказываю, как можно решить задачу и какие подводные камни могут встретиться на пути.
Чтобы объединить данные только по одному критерию, подойдет формула VLOOKUP
. Если параметров несколько, лучше использовать формулы агрегации с условиями: SUMIFS
, COUNTIFS
и другие.
Бывает, что некоторые значения колонки, по которой мы объединяем данные, отсутствуют в одной из таблиц. Это может привести к потере данных. Чтобы решить проблему, лучше вывести все нужные значения этой колонки в отдельную таблицу и оттуда уже все объединять.
Отчет, который получился на видео. Скопируйте его себе в аккаунт и используйте на здоровье.Когда я, наконец, задеплоил сайт в продакшн, я сел писать пост о том, чем я научился в процессе. Чтобы пост был более наглядным, я подготовил иллюстрации. И тогда понял, что не знаю, как эти иллюстрации загрузить к статье.
ImageField()
.settings.py
название папки, в которую их надо будет класть. Например, '/media/'
.
urls.py
.
Об этих пунктах я рассказываю в видео. Помимо них есть еще два, которые я в видео упустил.
ImageField()
нужна библиотека Pillow. Ее можно установить, выполнив pip install Pillow
.
/media/
надо прописать в конфиге Nginx. Мы ранее делали такую же операцию, когда настраивали папку /static/
.Как обычно, последнюю версию кода сайта можно посмотреть и скопировать из Гитхаба.
Я захотел научиться делать сайты с помощью Django и решил снять весь процесс на видео и опубликовать на Youtube, а код выложить в открытый доступ Github. В итоге снял 10 двухчасовых видео, а весь код выложил в открытый доступ.
Выводы: делать сайт с нуля сложно, но весело. Делать прямые эфиры сложно и не очень весело. Записанные и
Чтобы следить за следующими сериями, подписывайтесь на рассылку.
В прошлом году мне в руки попалась книга Остина Клеона «Show Your Work». Это короткая, емкая и вдохновляющая книга о том, зачем и как вести блог.
«Представьте, что будущему работодателю не придется читать ваше резюме потому что он уже читает ваш блог. Представьте, что вы студент и получаете первую работу благодаря своему учебному проекту, который разместили в интернете. Представьте, что вас увольняют, но у вас уже есть контакты людей, осведомленных о вашей работе и готовых помочь отыскать новую. Представьте, что вы превращаете дополнительную работу или хобби в профессию, потому что ваше окружение вас поддерживает».
Я несколько раз пытался сформировать привычку делиться контентом. Например, завел канал в Телеграме, сделал сайт, принял участие в челендже Content Hero. Бывает, что находит вдохновение и контент дается легко. А бывает, что другие дела засасывают и блог отправляется на дальнюю полку.
Например, в блоге я написал две статьи о Pandas. Я уже не помню, когда это было. Кажется в 2017 году. В своём канале я сначала писал про аналитику, но потом идеи кончились. Подписался на Content Hero. Там все участники обязуются в течение 30 дней публиковать хотя бы по одному посту. Написал пару неплохих заметок про философию, но потом челендж закончился, и посты вместе с ним.
В общем, сформировать привычку не удалось.
Параллельно в моей голове давно крутится желание научиться делать цифровые продукты. Я начал карьеру продавцом, потом научился управлять баннерами и стал маркетологом. Постепенно научился считать метрики и строить когорты — я стал аналитиком. Но все это время я завидовал разработчикам.
Ну как завидовал. В принципе, именно работать программистом часто, вроде бы, довольно скучная работа. Но мне всегда казалось, что уметь прям взять и своими руками реализовать идею в продукте — очень крутая суперсила.
Я постепенно научился писать код и анализировать данные. В Osome вообще получилось собрать всю инфраструктуру работы с данными, на которой компания прожила первые 3 года. Но все это было не то. Как будто не хватало
Недавно я понял, что не хватает как раз умения просто брать и создавать, например, сайты.
В Show Your Work есть одна мощная идея:
Лучший способ начать делиться своей работой — подумать о том, чему вы хотите научиться и взять на себя обязательство делать это на глазах у других.
В декабре 2021, незадолго до начала новогодних праздников я подумал: «А почему бы мне не научиться делать сайты в прямом эфире?».
Еще полгода назад мне на глаза попалась другая книга, Test Driven Development with Django, в которой по шагам показывают, как написать сайт на Python. Я придумал, что могу идти по книге, делать задания, снимать процесс на видео и выкладывать на Youtube. Только, чтобы было интереснее, вместо примеров из книги, я решил сразу делать свой продукт. Например, переделать свой старый сайт.
С одной стороны, идея захватывающая, но с другой — страшная до ужаса.
Я люблю нравиться. Поэтому мне некомфортно от мысли, что я вот так прям публично заявлю о том, что я
Но в книге и на эту мысль нашелся ответ:
Самое глупое творчество остается творчеством. Представьте линейку, измеряющую качество той или иной работы. Разница между посредственной и хорошей работой может быть огромна, но плохая работа хотя бы есть на этой линейке. Настоящая пропасть лежит между бездействием и действием, каким бы оно не было. Лучше сделать хоть
Ну не понравятся
В конце декабря сделал анонс в
Первый эфир вышел 2 января, как только закончилась активная часть новогодних праздников. Потом я старался держать темп и выпускать один эфир раз в два дня.
В итоге получилось 4 эфира по 2 часа каждый. Получилось написать основную логику работы блога. В конце праздников сайт умел создавать, хранить и отображать статьи, но внешне выглядел пока так себе.
Так выглядел сайт, когда я «доделал блог». Сайт умел сохранять статьи в базу данных и показывать их на экране. Но оформления сначала никакого не было.
Стало очевидно, что за праздники сайт доделать не получится, придется продолжать параллельно с моей работой. Но в будни делать и смотреть прямые эфиры сложно, ведь вернулась основная работа. Я, конечно рано встаю, и могу выходить в эфир в 8 утра, но, кажется, смотреть вживую это никто не будет. Поэтому решил отказаться от лайвов и вместо этого записывать прогресс на видео и продолжать выкладывать. Так получились 5 и 6, 8 и 9 эпизоды.
В итоге получилось 10 эпизодов: 6 лайвов и 4 в записи. Cуммарное время эфиров около 20 часов (там
За это время у меня получилось пройти путь от пустого экрана до полноценного блога, написанного на Django, полностью покрытого функциональными и
Конечно, пока сайт не то, чтобы технологическое чудо. Такого же (или даже лучшего) результата можно было добиться просто использовав
Работая с готовыми платформами, привыкаешь к процессу «
А тут, чтобы одну страничку сделать, надо базу поднять, вьюху написать, шаблон добавить, маршрутизацию настроить. И даже после всего этого страничка начнет работать на локальном компьютере. Чтобы она появилась в интернете, надо
Но, знаете что, по мне так именно в этих деталях все веселье! Я теперь знаю, как именно сайт работает, он мне стал как будто родным. И, самое главное, теперь я знаю, что нужно делать, чтобы его развивать.
У меня есть некоторый опыт работы с камерой. Я веду курсы, читаю лекции. В сентябре я купил себе домой нормальную камеру, свет и микрофон и снял пару
Я подумал, что, раз я говорить на камеру умею, то лайвы тоже смогу. Ну, в принципе, смог, но на деле все оказалось гораздо сложнее.
Мой сетап: Macbook Pro, камера Sony a6400 с телесуфлером Pixaero, микрофон Shure MV7, Elgato Stream Deck
Мой сетап состоит из ноутбука, камеры и микрофона. Когда в ходе лайва я показываю свой экран, я вижу этот же экран, но не вижу себя и финальную композицию, которую видят зрители. То есть, если с композицией
А эти «не так», естественно, постоянно возникали.
Например, при съемках второго эпизода, я пару раз забывал переключить экран. Объясняю: «а вот тут, смотрите, мы вводим такую команду и получаем
В третьем эпизоде еще хуже. У меня, видимо, съехала камера и в течение почти всего стрима на экране помещалась только половина моего лица. Я это увидел только после эфира. Если это была бы запись, я пошел бы все переделывать, но лайв уже в интернете и его не вернешь.
Хорошо хоть, код видно
Ну и, конечно, то, что относится потом ко всем лайвам: больно смотреть видео со всеми этими паузами, мычанием и словарными паразитами. Наверное, надо будет нанять монтажера и вырезать лишнее. Но на будущее, все же, проще сначала снимать видео, монтировать и потом выкладывать. Мороки меньше, а результат получается гораздо лучше.
Когда планировал проект, я боялся опозориться. Оказалось, что я боялся не совсем безосновательно. Как только я начал публиковать видео в
За время эфиров от меня отписались 100 с лишним человек
Когда я смотрел на график, я, конечно же сразу рационализировал: «Это нормально! Я давно ничего не писал, за это время ко мне случайно подписались
Рационализация — это, конечно, хорошо, но смотреть на растущий график гораздо кайфовее. В Телеграме в итоге отписалось чуть больше 100 человек то есть около 5% аудитории. Обидно!
Но, с другой стороны, на
На самом деле, я предполагал, что такой контент, наверняка, будет бесить 10%, оставит равнодушным 70–80% и очень хорошо зайдет другим 10%. Кажется, так и случилось. Я вижу по отзывам и комментариям к видео, что некоторым людям они очень даже полезны. Это самое кайфовое!
Этот пост — это как бы финал «первого сезона», в котором я прошел путь от полной пустоты до работающего в интернете блога.
Я вошел во вкус и планирую продолжать сериал.
Это большой проект, надо будет научиться делать закрытые разделы, регистрацию и логин пользователей, интегрировать платежную систему, настроить управление подписками.
Но спамить людей в Телеграме, наверное больше не буду, надо разнообразить контент. А для тех, кому интересно следить за прогрессом, лучше сделаю тематическую рассылку.
Кажется, Остин Клеон был прав про то, что публичная учеба — лучший способ начать делиться своей работой. Это точно помогает мне и, кажется, не только мне.
Привет. Я задумывал эту заметку для студентов курса Digital Rockstar, на котором мы учим маркетологов автоматизировать свою работу с помощью программирования, но решил поделиться шпаргалкой по Pandas со всеми. Я ожидаю, что читатель умеет писать код на Python хотя бы на минимальном уровне, знает, что такое списки, словари, циклы и функции.
Pandas — это библиотека для работы с данными на Python. Она упрощает жизнь аналитикам: где раньше использовалось 10 строк кода теперь хватит одной.
Например, чтобы прочитать данные из csv, в стандартном Python надо сначала решить, как хранить данные, затем открыть файл, прочитать его построчно, отделить значения друг от друга и очистить данные от специальных символов.
> with open('file.csv') as f:
... content = f.readlines()
... content = [x.split(',').replace('\n','') for x in content]
В Pandas всё проще. Во-первых, не нужно думать, как будут храниться данные — они лежат в датафрейме. Во-вторых, достаточно написать одну команду:
> data = pd.read_csv('file.csv')
Pandas добавляет в Python новые структуры данных — серии и датафреймы. Расскажу, что это такое.
Серии — одномерные массивы данных. Они очень похожи на списки, но отличаются по поведению — например, операции применяются к списку целиком, а в сериях — поэлементно.
То есть, если список умножить на 2, получите тот же список, повторенный 2 раза.
> vector = [1, 2, 3]
> vector * 2
[1, 2, 3, 1, 2, 3]
А если умножить серию, ее длина не изменится, а вот элементы удвоятся.
> import pandas as pd
> series = pd.Series([1, 2, 3])
> series * 2
0 2
1 4
2 6
dtype: int64
Обратите внимание на первый столбик вывода. Это индекс, в котором хранятся адреса каждого элемента серии. Каждый элемент потом можно получать, обратившись по нужному адресу.
> series = pd.Series(['foo', 'bar'])
> series[0]
'foo'
Еще одно отличие серий от списков — в качестве индексов можно использовать произвольные значения, это делает данные нагляднее. Представим, что мы анализируем помесячные продажи. Используем в качестве индексов названия месяцев, значениями будет выручка:
> months = ['jan', 'feb', 'mar', 'apr']
> sales = [100, 200, 300, 400]
> data = pd.Series(data=sales, index=months)
> data
jan 100
feb 200
mar 300
apr 400
dtype: int64
Теперь можем получать значения каждого месяца:
> data['feb']
200
Так как серии — одномерный массив данных, в них удобно хранить измерения по одному. На практике удобнее группировать данные вместе. Например, если мы анализируем помесячные продажи, полезно видеть не только выручку, но и количество проданных товаров, количество новых клиентов и средний чек. Для этого отлично подходят датафреймы.
Датафреймы — это таблицы. У их есть строки, колонки и ячейки.
Технически, колонки датафреймов — это серии. Поскольку в колонках обычно описывают одни и те же объекты, то все колонки делят один и тот же индекс:
> months = ['jan', 'feb', 'mar', 'apr']
> sales = {
... 'revenue': [100, 200, 300, 400],
... 'items_sold': [23, 43, 55, 65],
... 'new_clients': [10, 20, 30, 40]
...}
> sales_df = pd.DataFrame(data=sales, index=months)
> sales_df
revenue items_sold new_clients
jan 100 23 10
feb 200 43 20
mar 300 55 30
apr 400 65 40
Объясню, как создавать датафреймы и загружать в них данные.
Бывает, что мы не знаем, что собой представляют данные, и не можем задать структуру заранее. Тогда удобно создать пустой датафрейм и позже наполнить его данными.
> df = pd.DataFrame()
А иногда данные уже есть, но хранятся в переменной из стандартного Python, например, в словаре. Чтобы получить датафрейм, эту переменную передаем в ту же команду:
> df = pd.DataFrame(data=sales, index=months))
Случается, что в некоторых записях не хватает данных. Например, посмотрите на список goods_sold
— в нём продажи, разбитые по товарным категориям. За первый месяц мы продали машины, компьютеры и программное обеспечение. Во втором машин нет, зато появились велосипеды, а в третьем снова появились машины, но велосипеды исчезли:
> goods_sold = [
... {'computers': 10, 'cars': 1, 'soft': 3},
... {'computers': 4, 'soft': 5, 'bicycles': 1},
... {'computers': 6, 'cars': 2, 'soft': 3}
... ]
Если загрузить данные в датафрейм, Pandas создаст колонки для всех товарных категорий и, где это возможно, заполнит их данными:
> pd.DataFrame(goods_sold)
bicycles cars computers soft
0 NaN 1.0 10 3
1 1.0 NaN 4 5
2 NaN 2.0 6 3
Обратите внимание, продажи велосипедов в первом и третьем месяце равны NaN
— расшифровывается как Not a Number. Так Pandas помечает отсутствующие значения.
Теперь разберем, как загружать данные из файлов. Чаще всего данные хранятся в экселевских таблицах или csv-, tsv- файлах.
Экселевские таблицы читаются с помощью команды pd.read_excel()
. Параметрами нужно передать адрес файла на компьютере и название листа, который нужно прочитать. Команда работает как с xls, так и с xlsx:
> pd.read_excel('file.xlsx', sheet_name='Sheet1')
Файлы формата csv и tsv — это текстовые файлы, в которых данные отделены друг от друга запятыми или табуляцией:
# CSV
month,customers,sales
feb,10,200
# TSV
month\tcustomers\tsales
feb\t10\t200
Оба читаются с помощью команды .read_csv()
, символ табуляции передается параметром sep
(от англ. separator — разделитель):
> pd.read_csv('file.csv')
> pd.read_csv('file.tsv', sep='\t')
При загрузке можно назначить столбец, который будет индексом. Представьте, что мы загружаем таблицу с заказами. У каждого заказа есть свой уникальный номер, Если назначим этот номер индексом, сможем выгружать данные командой df[order_id]
. Иначе придется писать фильтр df[df[‘id’] == order_id ]
.
О том, как получать данные из датафреймов, я расскажу в одном из следующих разделов. Чтобы назначить колонку индексом, добавим в команду read_csv()
параметр index_col
, равный названию нужной колонки:
> pd.read_csv('file.csv', index_col='id')
После загрузки данных в датафрейм, хорошо бы их исследовать — особенно, если они вам незнакомы.
Представим, что мы анализируем продажи американского интернет-магазина. У нас есть данные о заказах и клиентах. Загрузим файл с продажами интернет-магазина в переменную orders
. Раз загружаем заказы, укажем, что колонка id
пойдет в индекс:
> orders = pd.read_csv('orders.csv', index_col='id')
Расскажу о четырех атрибутах, которые есть у любого датафрейма: .shape
, .columns
, .index
и .dtypes
.
.shape
показывает, сколько в датафрейме строк и колонок. Он возвращает пару значений (n_rows, n_columns)
. Сначала идут строки, потом колонки.
> orders.shape
(5009, 5)
В датафрейме 5009 строк и 5 колонок.
Окей, масштаб оценили. Теперь посмотрим, какая информация содержится в каждой колонке. С помощью .columns
узнаем названия колонок:
> orders.columns
Index(['order_date', 'ship_mode', 'customer_id', 'sales'], dtype='object')
Теперь видим, что в таблице есть дата заказа, метод доставки, номер клиента и выручка.
С помощью .dtypes
узнаем типы данных, находящихся в каждой колонке и поймем, надо ли их обрабатывать. Бывает, что числа загружаются в виде текста. Если мы попробуем сложить две текстовых значения '1' + '1'
, то получим не число 2, а строку '11'
:
> orders.dtypes
order_date object
ship_mode object
customer_id object
sales float64
dtype: object
Тип object
— это текст, float64
— это дробное число типа 3,14.
C помощью атрибута .index
посмотрим, как называются строки:
> orders.index
Int64Index([100006, 100090, 100293, 100328, 100363, 100391, 100678, 100706,
100762, 100860,
...
167570, 167920, 168116, 168613, 168690, 168802, 169320, 169488,
169502, 169551],
dtype='int64', name='id', length=5009)
Ожидаемо, в индексе датафрейма номера заказов: 100762, 100860 и так далее.
В колонке sales
хранится стоимость каждого проданного товара. Чтобы узнать разброс значений, среднюю стоимость и медиану, используем метод .describe()
:
> orders.describe()
sales
count 5009.0
mean 458.6
std 954.7
min 0.6
25% 37.6
50% 152.0
75% 512.1
max 23661.2
Наконец, чтобы посмотреть на несколько примеров записей датафрейма, используем команды .head()
и .sample()
. Первая возвращает 6 записей из начала датафрейма. Вторая — 6 случайных записей:
> orders.head()
order_date ship_mode customer_id sales
id
100006 2014-09-07 Standard DK-13375 377.970
100090 2014-07-08 Standard EB-13705 699.192
100293 2014-03-14 Standard NF-18475 91.056
100328 2014-01-28 Standard JC-15340 3.928
100363 2014-04-08 Standard JM-15655 21.376
Получив первое представление о датафреймах, теперь обсудим, как доставать из него данные.
Данные из датафреймов можно получать по-разному: указав номера колонок и строк, использовав условные операторы или язык запросов. Расскажу подробнее о каждом способе.
Продолжаем анализировать продажи интернет-магазина, которые загрузили в предыдущем разделе. Допустим, я хочу вывести столбец sales
. Для этого название столбца нужно заключить в квадратные скобки и поставить после них названия датафрейма: orders['sales']
:
> orders['sales']
id
100006 377.970
100090 699.192
100293 91.056
100328 3.928
100363 21.376
100391 14.620
100678 697.074
100706 129.440
...
Обратите внимание, результат команды — новый датафрейм с таким же индексом.
Если нужно вывести несколько столбцов, в квадратные скобки нужно вставить список с их названиями: orders[['customer_id', 'sales']]
. Будьте внимательны: квадратные скобки стали двойными. Первые — от датафрейма, вторые — от списка:
> orders[['customer_id', 'sales']]
customer_id sales
id
100006 DK-13375 377.970
100090 EB-13705 699.192
100293 NF-18475 91.056
100328 JC-15340 3.928
100363 JM-15655 21.376
100391 BW-11065 14.620
100363 KM-16720 697.074
100706 LE-16810 129.440
...
Перейдем к строкам. Их можно фильтровать по индексу и по порядку. Например, мы хотим вывести только заказы 100363, 100391 и 100706, для этого есть команда .loc[]
:
> show_these_orders = ['100363', '100363', '100706']
> orders.loc[show_these_orders]
order_date ship_mode customer_id sales
id
100363 2014-04-08 Standard JM-15655 21.376
100363 2014-04-08 Standard JM-15655 21.376
100706 2014-12-16 Second LE-16810 129.440
А в другой раз бывает нужно достать просто заказы с 1 по 3 по порядку, вне зависимости от их номеров в таблицемы. Тогда используют команду .iloc[]
:
> show_these_orders = [1, 2, 3]
> orders.iloc[show_these_orders]
order_date ship_mode customer_id sales
id
100090 2014-04-08 Standard JM-15655 21.376
100293 2014-04-08 Standard JM-15655 21.376
100328 2014-12-16 Second LE-16810 129.440
Можно фильтровать датафреймы по колонкам и столбцам одновременно:
> columns = ['customer_id', 'sales']
> rows = ['100363', '100363', '100706']
> orders.loc[rows][columns]
customer_id sales
id
100363 JM-15655 21.376
100363 JM-15655 21.376
100706 LE-16810 129.440
...
Часто вы не знаете заранее номеров заказов, которые вам нужны. Например, если задача — получить заказы, стоимостью более 1000 рублей. Эту задачу удобно решать с помощью условных операторов.
Задача: нужно узнать, откуда приходят самые большие заказы. Начнем с того, что достанем все покупки стоимостью более 1000 долларов:
> filter_large = orders['sales'] > 1000
> orders.loc[filter_slarge]
order_date ship_mode customer_id sales
id
101931 2014-10-28 First TS-21370 1252.602
102673 2014-11-01 Standard KH-16630 1044.440
102988 2014-04-05 Second GM-14695 4251.920
103100 2014-12-20 First AB-10105 1107.660
103310 2014-05-10 Standard GM-14680 1769.784
...
Помните, в начале статьи я упоминал, что в сериях все операции применяются по-элементно? Так вот, операция orders['sales'] > 1000
идет по каждому элементу серии и, если условие выполняется, возвращает True
. Если не выполняется — False
. Получившуюся серию мы сохраняем в переменную filter_large
.
Вторая команда фильтрует строки датафрейма с помощью серии. Если элемент filter_large
равен True
, заказ отобразится, если False
— нет. Результат — датафрейм с заказами, стоимостью более 1000 долларов.
Интересно, сколько дорогих заказов было доставлено первым классом? Добавим в фильтр ещё одно условие:
> filter_large = df['sales'] > 1000
> filter_first_class = orders['ship_mode'] == 'First'
> orders.loc[filter_large & filter_first_class]
order_date ship_mode customer_id sales
id
101931 2014-10-28 First TS-21370 1252.602
103100 2014-12-20 First AB-10105 1107.660
106726 2014-12-06 First RS-19765 1261.330
112158 2014-12-02 First DP-13165 1050.600
116666 2014-05-08 First KT-16480 1799.970
...
Логика не изменилась. В переменную filter_large
сохранили серию, удовлетворяющую условию orders['sales'] > 1000
. В filter_first_class
— серию, удовлетворяющую orders['ship_mode'] == 'First'
.
Затем объединили обе серии с помощью логического ‘И’: filter_first_class & filter_first_class
. Получили новую серию той же длины, в элементах которой True
только у заказов, стоимостью больше 1000, доставленных первым классом. Таких условий может быть сколько угодно.
Еще один способ решить предыдущую задачу — использовать язык запросов. Все условия пишем одной строкой 'sales > 1000 & ship_mode == 'First'
и передаем ее в метод .query()
. Запрос получается компактнее.
> orders.query('sales > 1000 & ship_mode == First')
order_date ship_mode customer_id sales
id
101931 2014-10-28 First TS-21370 1252.602
103100 2014-12-20 First AB-10105 1107.660
106726 2014-12-06 First RS-19765 1261.330
112158 2014-12-02 First DP-13165 1050.600
116666 2014-05-08 First KT-16480 1799.970
...
Отдельный кайф: значения для фильтров можно сохранить в переменной, а в запросе сослаться на нее с помощью символа @: sales > @sales_filter
.
> sales_filter = 1000
> ship_mode_filter = 'First'
> orders.query('sales > @sales_filter & ship_mode > @ship_mode_filter')
order_date ship_mode customer_id sales
id
101931 2014-10-28 First TS-21370 1252.602
103100 2014-12-20 First AB-10105 1107.660
106726 2014-12-06 First RS-19765 1261.330
112158 2014-12-02 First DP-13165 1050.600
116666 2014-05-08 First KT-16480 1799.970
...
Разобравшись, как получать куски данных из датафрейма, перейдем к тому, как считать агрегированные метрики: количество заказов, суммарную выручку, средний чек, конверсию.
Задача: посчитаем, сколько денег магазин заработал с помощью каждого класса доставки. Начнем с простого — просуммируем выручку со всех заказов. Для этого используем метод .sum()
:
> orders['sales'].sum()
2297200.8603000003
Добавим класс доставки. Перед суммированием сгруппируем данные с помощью метода .groupby()
:
> orders.groupby('ship_mode')['sales'].sum()
ship_mode
First 3.514284e+05
Same Day 1.283631e+05
Second 4.591936e+05
Standard 1.358216e+06
3.514284e+05
— научный формат вывода чисел. Означает 3.51 * 105. Нам такая точность не нужна, поэтому можем сказать Pandas, чтобы округлял значения до сотых:
> pd.options.display.float_format = '{:,.1f}'.format
> orders.groupby('ship_mode')['sales'].sum()
ship_mode
First 351,428.4
Same Day 128,363.1
Second 459,193.6
Standard 1,358,215.7
Другое дело. Теперь видим сумму выручки по каждому классу доставки. По суммарной выручке неясно, становится лучше или хуже. Добавим разбивку по датам заказа:
> orders.groupby(['ship_mode', 'order_date'])['sales'].sum()
ship_mode order_date
First 2014-01-06 12.8
2014-01-11 9.9
2014-01-14 62.0
2014-01-15 149.9
2014-01-19 378.6
2014-01-26 152.6
...
Видно, что выручка прыгает ото дня ко дню: иногда 10 долларов, а иногда 378. Интересно, это меняется количество заказов или средний чек? Добавим к выборке количество заказов. Для этого вместо .sum()
используем метод .agg()
, в который передадим список с названиями нужных функций.
> orders.groupby(['ship_mode', 'order_date'])['sales'].agg(['sum', 'count'])
sum count
ship_mode order_date
First 2014-01-06 12.8 1
2014-01-11 9.9 1
2014-01-14 62.0 1
2014-01-15 149.9 1
2014-01-19 378.6 1
2014-01-26 152.6 1
...
Ого, получается, что это так прыгает средний чек. Интересно, а какой был самый удачный день? Чтобы узнать, отсортируем получившийся датафрейм: выведем 10 самых денежных дней по выручке:
> orders.groupby(['ship_mode', 'order_date'])['sales'].agg(['sum']).sort_values(by='sum', ascending=False).head(10)
sum
ship_mode order_date
Standard 2014-03-18 26,908.4
2016-10-02 18,398.2
First 2017-03-23 14,299.1
Standard 2014-09-08 14,060.4
First 2017-10-22 13,716.5
Standard 2016-12-17 12,185.1
2017-11-17 12,112.5
2015-09-17 11,467.6
2016-05-23 10,561.0
2014-09-23 10,478.6
Команда разрослась, и её теперь неудобно читать. Чтобы упростить, можно разбить её на несколько строк. В конце каждой строки ставим обратный слеш \
:
> orders \
... .groupby(['ship_mode', 'order_date'])['sales'] \
... .agg(['sum']) \
... .sort_values(by='sum', ascending=False) \
... .head(10)
sum
ship_mode order_date
Standard 2014-03-18 26,908.4
2016-10-02 18,398.2
First 2017-03-23 14,299.1
Standard 2014-09-08 14,060.4
First 2017-10-22 13,716.5
Standard 2016-12-17 12,185.1
2017-11-17 12,112.5
2015-09-17 11,467.6
2016-05-23 10,561.0
2014-09-23 10,478.6
В самый удачный день — 18 марта 2014 года — магазин заработал 27 тысяч долларов с помощью стандартного класса доставки. Интересно, откуда были клиенты, сделавшие эти заказы? Чтобы узнать, надо объединить данные о заказах с данными о клиентах.
До сих пор мы смотрели только на таблицу с заказами. Но ведь у нас есть еще данные о клиентах интернет-магазина. Загрузим их в переменную customers
и посмотрим, что они собой представляют:
> customers = pd.read_csv('customers.csv', index='id')
> customers.head()
name segment state city
id
CG-12520 Claire Gute Consumer Kentucky Henderson
DV-13045 Darrin Van Huff Corporate California Los Angeles
SO-20335 Sean O'Donnell Consumer Florida Fort Lauderdale
BH-11710 Brosina Hoffman Consumer California Los Angeles
AA-10480 Andrew Allen Consumer North Carolina Concord
Мы знаем тип клиента, место его проживания, его имя и имя контактного лица. У каждого клиента есть уникальный номер id
. Этот же номер лежит в колонке customer_id
таблицы orders
. Значит мы можем найти, какие заказы сделал каждый клиент. Например, посмотрим, заказы пользователя CG-12520
:
> cust_filter = 'CG-12520'
> orders.query('customer_id == @cust_filter')
order_date ship_mode customer_id sales
id
CA-2016-152156 2016-11-08 Second CG-12520 993.90
CA-2017-164098 2017-01-26 First CG-12520 18.16
US-2015-123918 2015-10-15 Same Day CG-12520 136.72
Вернемся к задаче из предыдущего раздела: узнать, что за клиенты, которые сделали 18 марта заказы со стандартной доставкой. Для этого объединим таблицы с клиентами и заказами. Датафреймы объединяют с помощью методов .concat()
, .merge()
и .join()
. Все они делают одно и то же, но отличаются синтаксисом — на практике достаточно уметь пользоваться одним из них.
Покажу на примере .merge()
:
> new_df = pd.merge(orders, customers, how='inner', left_on='customer_id', right_index=True)
> new_df.columns
Index(['order_date', 'ship_mode', 'customer_id', 'sales', 'name', 'segment',
'state', 'city'],
dtype='object')
В .merge()
я сначала указал названия датафреймов, которые хочу объединить. Затем уточнил, как именно их объединить и какие колонки использовать в качестве ключа.
Ключ — это колонка, связывающая оба датафрейма. В нашем случае — номер клиента. В таблице с заказами он в колонке customer_id
, а таблице с клиентами — в индексе. Поэтому в команде мы пишем: left_on='customer_id', right_index=True
.
Закрепим полученный материал, решив задачу. Найдем 5 городов, принесших самую большую выручку в 2016 году.
Для начала отфильтруем заказы из 2016 года:
> orders_2016 = orders.query("order_date >= '2016-01-01' & order_date <= '2016-12-31'")
> orders_2016.head()
order_date ship_mode customer_id sales
id
100041 2016-11-20 Standard BF-10975 328.5
100083 2016-11-24 Standard CD-11980 24.8
100153 2016-12-13 Standard KH-16630 63.9
100244 2016-09-20 Standard GM-14695 475.7
100300 2016-06-24 Second MJ-17740 4,823.1
Город — это атрибут пользователей, а не заказов. Добавим информацию о пользователях:
> with_customers_2016 = pd.merge(customers, orders_2016, how='inner', left_index=True, right_on='customer_id')
Cруппируем получившийся датафрейм по городам и посчитаем выручку:
> grouped_2016 = with_customers_2016.groupby('city')['sales'].sum()
> grouped_2016.head()
city
Akron 1,763.0
Albuquerque 692.9
Amarillo 197.2
Arlington 5,672.1
Arlington Heights 14.1
Name: sales, dtype: float64
Отсортируем по убыванию продаж и оставим топ-5:
> top5 = grouped_2016.sort_values(ascending=False).head(5)
> print(top5)
city
New York City 53,094.1
Philadelphia 39,895.5
Seattle 33,955.5
Los Angeles 33,611.1
San Francisco 27,990.0
Name: sales, dtype: float64
Готово!
Попробуйте сами:
Возьмите данные о заказах и покупателях и посчитайте:
Через некоторое время выложу ответы в Телеграме. Подписывайтесь, чтобы не пропустить ответы и новые статьи.
До скорого!
Кстати, большое спасибо Александру Марфицину за то, что помог отредактировать статью.
Привет! Продолжем изучать Pandas. В прошлый раз я рассказал о возможностях библиотеки, а сегодня покажу, как с её помощью делать когортный анализ.
Прежде всего, вспомним, что такое когорты и как их анализировать.
Когорта — это группа людей, которая совершила нужное действие в определенный промежуток времени.
Когортный анализ — это наблюдение за когортами. Выбираем одну или несколько метрик, измеряем их и делаем выводы.
Например, социологи могут отслеживать, сколько людей, родившихся в 1980 году, получили высшее образование. Когорта здесь — те, кто родились в 1980 году. Метрика — доля людей с высшим образованием.
Еще пример: маркетологи хотят узнать, сколько заказов и выручки принесли пользователи, совершившие свой первый заказ год назад. Теперь когорта — это прошлогодние покупатели, а метрики — количество заказов и выручка.
Получается, когортный анализ состоит из трех шагов: определения нужного действия и временного промежутка когорты и выбора метрик для отслеживания:
Действие | Время | Метрика |
---|---|---|
Родились | В 1980 году | % людей с высшим образованием |
Впервые |
Год назад | Количество заказов и выручка |
Установили приложение | Неделю назад | % пользователей, открывших приложение еще раз |
Когорты можно сравнивать между собой. Например, маркетологи измеряют, сколько заказов обычно делают пользователи в течение месяца после первой покупки и смотрят динамику:
Месяц | Клиенты | Покупок в 1й месяц | Покупок на клиента |
---|---|---|---|
Январь 2018 | 134 | 161 | 1.20 |
Февраль 2018 | 164 | 194 | 1.18 |
Март 2018 | 193 | 200 | 1.03 |
Общее количество клиентов и покупок выросло — приятно. Но, сравнив когорты, видим, что в среднем клиенты стали покупать реже — тревожный знак.
Ок, теперь мы готовы к тому, чтобы научиться делать когортный анализ с помощью Pandas. Для наглядности решим задачу.
Допустим, мы работаем в
Каждая строка таблицы orders
— это покупка. Мы знаем, когда она произошла, кто её сделал и сколько денег она принесла в магазин. Дата заказа лежит в поле order_date
, номер покупателя — в customer_id
, а выручка — в sales
.
Часто бывает, что даты загружаются в виде текста. Преобразим колонку order_date
из текста в дату:
Данные перед нами, теперь можно с ними работать. Начнем с простого: выясним, сколько всего в магазине было покупок и выручки.
Чтобы посчитать общую выручку, просуммируем колонку sales
:
Количество заказов можно посчитать с помощью этой же колонки, но вместо суммы используем метод count()
:
Теперь посчитаем обе метрики для каждого пользователя. Сгруппируем датафрейм по полю customer_id
:
Видим, например, что пользователь AA-10315 сделал 5 заказов и принес $5563 выручки.
Идём дальше. Чтобы построить когорты, нам нужно сгруппировать клиентов по дате их первой покупки. В данных нет такого поля, значит, нужно его посчитать.
Чтобы вычислить дату первой покупки каждого пользователя, сгруппируем данные по customer_id
и найдем минимальное значение поля order_date
. Результат сохраним в переменную first_orders
:
Видим, что пользовать AA-10315 впервые что-то купил 31 марта 2014 года, а пользователь AA-10375 — 21 апреля того же года.
Зная даты первых покупок, можем строить когорты.
Итак, когортами будем считать людей, сделавших первую покупку в тот или иной день. Метрики для отслеживания — количество заказов и выручка.
План такой: сначала добавим дату первой покупки пользователей в таблицу с заказами, затем сгруппируем по датам первой покупки и заказа и, наконец, посчитаем выручку и количество заказов каждой когорты.
Приступим. Добавим дату первой покупки с помощью метода merge()
и сохраним получившийся датафрейм в переменную orders_merged
:
В строках получившегося датафрейма всё еще покупки, но теперь в таблице появилась новая колонка: дата первой покупки пользователя.
Агрегируем по дате первой покупки и посчитаем нужные показатели:
Видим, что клиенты от 3 января 2014 года, всего сделали 9 заказов на $1050.6. Посмотрим, когда были эти заказы. Для этого добавим к группировке колонку order_date
:
Ага, первый заказ этой когорты был 3 января на $16. В следующий раз клиент вернулся почти год спустя и купил что-то ещё, в этот раз на $153. Следующая покупка была уже в апреле 2015 и так далее.
Когорты готовы, теперь решим задачу.
Напомню, что мы хотим посчитать, сколько в среднем заказов и выручки приносят клиенты в течение года, после первой покупки.
Мы знаем, сколько магазин заработал с каждой когорты за всё время. Уточним метрику: посчитаем показатели за первый год жизни когорты.
Сначала узнаем, сколько дней прошло между первой покупкой и последующим заказом, и удалим те, которые случились позже 365 дней. Чтобы посчитать количество дней между заказами, вычтем из колонки order_date
столбец first_order
:
Вуаля. Видим, что, например, заказ 131884
случился 455 дней спустя первой покупки. 455 days
— это тип данных под названием «Timedelta», его специально придумали, чтобы показывать временные промежутки.
Чтобы удалить поздние заказы, добавим условие <= '365 days'
:
Сохраним результат в переменную year_1_filter
, отфильтруем ненужные заказы из когортного отчета и сохраним результат в переменную year_1_orders
:
В датафрейме остались только заказы, сделанные когортами в первый год после первой покупки. Теперь сгруппируем заказы по дате первой покупки и посчитаем нужные метрики. Результат сохраним в переменную cohorts
:
Последний шаг: посчитаем, сколько в среднем заказов и приносят клиенты в течение первого года. Для этого сначала просуммируем показатели каждой когорты, а затем усредним значения методом mean()
:
Готово! В среднем за первый год когорты делают по 4 заказа и приносят по $1949 долларов.
Есть много способов улучшить решение, например сгруппировать дневные когорты в недельные или месячные, визуализировать отчет в таблице или на графике. Наконец, интересно разбить когорты по каким-то признакам, например, отделить частных покупателей от компаний — наверняка их показатели существенно отличаются.
Обо всем этом в следующих сериях. Подписывайтесь на канал, чтобы не пропустить.
Адиос!