О чем речь?
В этой статье я бы хотела рассказать о применении python-библиотеки Pandasql.
Многие люди, сталкивающиеся с задачами анализа данных, уже, скорее всего, знакомы с библиотекой Pandas. Pandas позволяет быстро и удобно работать с табличными данными: фильтровать, группировать, делать join над данными; строить сводные таблицы и даже рисовать графики (для простых визуализации достаточно функции plot(), а если хочется чего-то позаковыристее, то поможет библиотека matplotlib). На Хабре не раз рассказывали о применении этой библиотеки для работы с данными: раз, два, три.
Но по моему опыту далеко не все знают о библиотеке Pandasql, которая позволяет работать с Pandas DataFrames как с таблицами и обращаться к ним, используя язык SQL. В некоторых задачах проще выразить желаемое с помощью декларативного языка SQL, поэтому я считаю, что людям, работающим с данными, полезно знать о наличии такой функциональности. Если говорить о реальных задачах, то я использовала эту библиотеку для решения задачи join'a таблиц по нечетким условиям (необходимо было объединить записи о событиях из разных систем по примерно совпадающему времени, разрыв порядка 5 секунд).
Рассмотрим использование этой библиотеки на конкретных примерах.
Данные для анализа
Для иллюстрации я взяла данные о вовлеченности студентов специализации "Data Analyst Nanodegree" на Udacity. Эти данные опубликованы в курсе Intro to Data Analysis (могу порекомендовать этот курс всем, кто хочет познакомиться с использованием библиотек Pandas и Numpy для анализа данных, хотя там совсем не рассмотрена бибилиотека Pandasql).
В примерах я буду использовать 2 таблицы (подробнее о данных можно почитать тут):
- enrollments: данные о записи на специализацию "Data Analyst Nanodegree" некоторого случайного подмножества студентов;
- account key: ID студента;
- join date: день, когда студент записался на специализацию;
- status: статус студента на момент сбора данных: "current", если студент активен, и "canceled", если он покинул курс;
- cancel date: день, когда студент покинул курс, None для активных студентов;
- is udacity: принимает значение True для тестовых аккаунтов в Udacity, для живых пользователей — False;
- daily engagements: данные об активности студентов из таблицы enrollments для каждого дня, когда они были записаны на специализацию;
- acct: ID студента;
- utc date: дата активности;
- total minutes visited: суммарное число минут, которое студент провел на курсах специализации "Data Analyst Nanodegree".
Примеры
Теперь мы можем перейти к рассмотрению примеров. Мне кажется, наиболее наглядно будет показать, как решить каждую из задач с помощью стандартной функциональности Pandas и Pandasql.
Для начала нужно сделаем все необходимы импорты и загрузим данные из csv файлов в DataFrames. Полный код примеров и исходные данные можно найти в репозитории.
import pandas as pd
import pandasql as ps
from datetime import datetime
import seaborn
daily_engagements = pd.read_csv('./data/daily_engagement.csv')
enrollments = pd.read_csv('./data/enrollments.csv')Импорт библиотеки seaborn использован только для того, чтобы сделать графики красивее, никакой специальной функциональности библиотеки использовано не будет.
Простой запрос
Задача: найти топ-10 максимальных активностей студента в конкретный день.
В данном примере рассмотрено, как использовать фильтрацию, сортировку и получение N первых объектов. Для выполнения SQL запроса используется функция
sqldf модуля Pandasql, также в эту функцию необходимо передать словарь локальных имен locals() (подробнее про использование функций locals() и globals() в Pandasql можно почитать на Stackoverflow).# pandas code
top10_engagements_pandas = daily_engagements[['acct', 'total_minutes_visited', 'utc_date']]
.sort('total_minutes_visited', ascending = False)[:10]
# pandasql code
simple_query = '''
SELECT
acct,
total_minutes_visited,
utc_date
FROM daily_engagements
ORDER BY total_minutes_visited desc
LIMIT 10
'''
top10_engagements_pandas = ps.sqldf(simple_query, locals())Вывод: Самый усердный студент просидел за учебой более 17 часов в один день.
Использование агрегатных функций
Задача: интересно, есть ли недельная сезонность в активности студентов (если судить по себе, то обычно не хватает времени на online курсы по будням, но можно уделять этому больше времени в выходные).
Для начала добавим в исходный DataFrame колонку "weekday", преобразовав дату ко дню недели.
daily_engagements['weekday'] = map(lambda x: datetime.strptime(x, '%Y-%m-%d').strftime('%A'),
daily_engagements.utc_date)
daily_engagements.head()
# pandas code
weekday_engagement_pandas = pd.DataFrame(daily_engagements.groupby('weekday').total_minutes_visited.mean())
# pandasql code
aggr_query = '''
SELECT
avg(total_minutes_visited) as total_minutes_visited,
weekday
FROM daily_engagements
GROUP BY weekday
'''
weekday_engagement_pandasql = ps.sqldf(aggr_query, locals()).set_index('weekday')
week_order = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
weekday_engagement_pandasql.loc[week_order].plot(kind = 'bar', rot = 45,
title = 'Total time spent on Udacity by weekday')Вывод: Любопытно, но в среднем больше всего времени студенты проводят за курсами во вторник, а меньше всего в суботту. В среднем студенты больше времени тратят на MOOC в будние дни, нежели в выходные. Еще одно доказательство того, что я нерепрезентативна.
JOIN таблиц
Задача: рассмотрим студентов, которые не прошли специализацию (со статусом canceled) и тех, которые успешно учатся/учились и сравним для них среднюю активность в день за первую неделю, после того как они записались на специализацию. Есть гипотеза, что те, кто остались и успешно учатся, тратили на обучение больше времени.
Для ответа на этот вопрос нам понадобятся данные из обеих таблиц enrollments и daily engagements, так что будем использовать join по ID студента.
Также в этой задаче есть несколько подводных камней, которые нужно учесть:
- не все пользователи это живые люди, есть также тестовые аккаунты Udacity, их нужно отфильтровать
is_udacity = 0; - студент может записываться на специализацию несколько раз, в том числе и с интервалом менее одной недели, так что нужно проверять что дата активности utc date находится между join date и cancel date (только для студентов со статусом canceled).
# pandas code
join_df = pd.merge(daily_engagements,
enrollments[enrollments.is_udacity == 0],
how = 'inner',
right_on ='account_key',
left_on = 'acct')
join_df = join_df[['account_key', 'status', 'total_minutes_visited', 'utc_date', 'join_date', 'cancel_date']]
join_df['days_since_joining'] = map(lambda x: x.days,
pd.to_datetime(join_df.utc_date) - pd.to_datetime(join_df.join_date))
join_df['before_cancel'] = (pd.to_datetime(join_df.utc_date) <= pd.to_datetime(join_df.cancel_date))
join_df = join_df[join_df.before_cancel | (join_df.status == 'current')]
join_df = join_df[(join_df.days_since_joining < 7) & (join_df.days_since_joining >= 0)]
avg_account_total_minutes = pd.DataFrame(join_df.groupby(['account_key', 'status'], as_index = False)
.total_minutes_visited.mean())
avg_engagement_pandas = pd.DataFrame(avg_account_total_minutes.groupby('status').total_minutes_visited.mean())
avg_engagement_pandas.columns = []
# pandasql code
join_query = '''
SELECT
avg(avg_acct_total_minutes) as avg_total_minutes,
status
FROM
(SELECT
avg(total_minutes_visited) as avg_acct_total_minutes,
status,
account_key
FROM
(SELECT
e.account_key,
e.status,
de.total_minutes_visited,
(cast(strftime('%s',de.utc_date) as interger) - cast(strftime('%s',e.join_date) as interger))/(24*60*60)
as days_since_joining,
(cast(strftime('%s',e.cancel_date) as interger) - cast(strftime('%s', de.utc_date) as interger))/(24*60*60)
as days_before_cancel
FROM enrollments as e JOIN daily_engagements as de ON (e.account_key = de.acct)
WHERE (is_udacity = 0) AND (days_since_joining < 7) AND (days_since_joining >= 0)
AND ((days_before_cancel >= 0) OR (status = 'current'))
)
GROUP BY status, account_key)
GROUP BY status
'''
avg_engagement_pandasql = ps.sqldf(join_query, locals()).set_index('status')Стоит отметить, что в SQL запросе были использованы функции
cast и strftime, чтобы привести даты из строк в timestamp (количество секунд с начала эпохи), а затем посчитать разницу между этими датами в днях.Вывод: В среднем студенты, не забросившие специализацию, в первую неделю проводили на Udacity на 53% больше времени чем те, кто решил прекратить обучение.
Резюмируя
В этой статье мы рассмотрели примеры применения библиотеки Pandasql для анализа данных и сравнили ее с использованием функциональности Pandas. Мы применяли фильтрацию, сортировку, агрегатные функции и join'ы для работы с DataFrames в Pandasql.
Pandas — очень удобная библиотека, позволяющая быстро и легко преобразовывать данные, но мне кажется, что в некоторых задачах проще выразить свою мысль с помощью декларативного языка и тогда Pandasql приходит на помощь. Кроме того, Pandasql может быть полезен тем, кто только начинает знакомство с Pandas, но уже имеет хорошие знания SQL.
Полный код примеров и исходные данные также приведены в репозитории на github.
Для заинтересовавшихся есть также хороший tutorial по Pandasql на The Yhat Blog.
