Task 1
Давай начнем с самого простого. В этом упражнении тебе необходимо получить отфильтрованные данные из таблицы в базе данных. Почему важно фильтровать данные именно в запросе, а не после этого в библиотеке Pandas? Потому что таблицы могут иметь огромный размер. Если ты попыташься получить всю таблицу, то не сможешь ее "переварить" – у тебя может не хватить оперативной памяти. Всегда помни об этом.

Первый способ фильтрации — выбрать только те столбцы, которые тебе действительно нужны. Второй способ — выбрать только те строки, которые тебе действительно нужны.

Подробное описание задания:

Помести базу данных в подкаталог data в корневом каталоге, используемом в рамках этого проекта.
Создай соединение с базой данных с помощью библиотеки sqlite3.
Получи схему таблицы pageviews с помощью pd.io.sql.read_sql и запроса PRAGMA table_info(pageviews);.
Получи только первые 10 строк таблицы pageviews, чтобы проверить, как она выглядит.
Получи подтаблицу с помощью только одного запроса, в котором:
используются только uid и datetime;
используются только данные пользователей (user_*), и не используются данные администраторов;
сортировка выполняется по uid в порядке возрастания;
столбец индекса — это datetime;
datetime преобразован в DatetimeIndex;
имя датафрейма — pageviews.
Закрой соединение с базой данных. import sqlite3
import pandas as pd

# Помести базу данных в подкаталог data в корневом каталоге, используемом в рамках этого проекта.

# Создай соединение с базой данных с помощью библиотеки sqlite3
conn = sqlite3.connect('/content/drive/MyDrive/School21/day10/checking-logs.sqlite')

# Получи схему таблицы pageviews с помощью pd.io.sql.read_sql и запроса PRAGMA table_info(pageviews);
pageviews_schema = pd.io.sql.read_sql("PRAGMA table_info(pageviews);", conn)

# Получи только первые 10 строк таблицы pageviews, чтобы проверить, как она выглядит.
pageviews_data = pd.read_sql_query("SELECT * FROM pageviews LIMIT 10;", conn)

# Получи подтаблицу с помощью только одного запроса
query_pageviews = """
SELECT datetime, uid
FROM pageviews
WHERE uid LIKE 'user_%'
AND uid NOT LIKE '%admin%'
ORDER BY uid ASC
"""

pageviews = pd.read_sql_query(query_pageviews, conn, index_col='datetime', parse_dates=True)

# Вывод информации о таблице pageviews
print(pageviews.info())

# Закрой соединение с базой данных
conn.close() <class 'pandas.core.frame.DataFrame'>
Index: 987 entries, 2020-04-26 21:53:59.624136 to 2020-05-21 16:36:40.915488
Data columns (total 1 columns):
 #   Column  Non-Null Count  Dtype 
---  ------  --------------  ----- 
 0   uid     987 non-null    object
dtypes: object(1)
memory usage: 15.4+ KB
None Task 2
А теперь давай усложним задачу.Ты знаешь, что такое подзапрос? Это как запрос, но только внутри запроса. Чем он может быть полезен? Например, с его помощью можно сделать определенную агрегацию для выбранных данных. Однако следует помнить, что в первую очередь выполняются вложенные запросы, и только после этого — основной запрос.

Что тебе нужно сделать:

Создай соединение с базой данных с помощью библиотеки sqlite3.
Получи схему таблицы checker.
Получи только первые 10 строк таблицы checker, чтобы проверить, как она выглядит.
Подсчитай, сколько строк удовлетворяют следующим условиям, используя только один запрос с любым количеством подзапросов.
Подсчитай количество строк из таблицы pageviews, но только с пользователями из таблицы checker, у которых:
status = 'ready' (мы не хотим анализировать логи со статусом checking);
numTrials = 1 (мы хотим проанализировать только первые коммиты, поскольку только они информируют о том, когда студент начал работать над лабораторным заданием);
названия лабораторных заданий должны быть только из следующего списка: laba04, laba04s, laba05, laba06, laba06s, project1. Только они были активными во время проведения эксперимента.
Сохрани их в датафрейме checkers в столбце cnt. Датафрейм будет представлять собой только одну ячейку.
Закрой соединение. import sqlite3
import pandas as pd

# Создайте соединение с базой данных с помощью библиотеки sqlite3
conn = sqlite3.connect('/content/drive/MyDrive/School21/day10/checking-logs.sqlite')

# Получите схему таблицы checker
checker_schema = pd.read_sql_query("PRAGMA table_info(checker);", conn)

# Получите только первые 10 строк таблицы checker
checker_data = pd.read_sql_query("SELECT * FROM checker LIMIT 10;", conn)

# Вывод схемы таблицы checker и первых 10 строк
print("Схема таблицы checker:")
print(checker_schema)
print()
print("Первые 10 строк таблицы checker:")
print(checker_data)
print()

# Подсчитайте количество строк, удовлетворяющих указанным условиям
query = """
SELECT COUNT(*) as cnt
FROM pageviews
WHERE uid IN (
    SELECT uid
    FROM checker
    WHERE status = 'ready'
    AND numTrials = 1
    AND labname IN ('laba04', 'laba04s', 'laba05', 'laba06', 'laba06s', 'project1')
)
"""

checkers = pd.read_sql_query(query, conn)

# Вывод результата
print("Количество строк, удовлетворяющих условиям:")
print(checkers)

# Закройте соединение с базой данных
conn.close() Схема таблицы checker:
   cid       name       type  notnull dflt_value  pk
0    0      index    INTEGER        0       None   0
1    1     status       TEXT        0       None   0
2    2    success    INTEGER        0       None   0
3    3  timestamp  TIMESTAMP        0       None   0
4    4  numTrials    INTEGER        0       None   0
5    5    labname       TEXT        0       None   0
6    6        uid       TEXT        0       None   0
7    7   datetime  TIMESTAMP        0       None   0

Первые 10 строк таблицы checker:
   index    status  success                   timestamp  numTrials   labname  \
0      0  checking        0  2020-04-16 21:12:50.740474          5      None   
1      1     ready        0  2020-04-16 21:12:54.708365          5  code_rvw   
2      2  checking        0  2020-04-16 21:46:47.769088          7      None   
3      3     ready        0  2020-04-16 21:46:48.121217          7     lab02   
4      4  checking        0  2020-04-16 21:53:01.862637          6  code_rvw   
5      5     ready        0  2020-04-16 21:53:05.373389          6  code_rvw   
6      6  checking        0  2020-04-17 05:18:51.965864          1      None   
7      7     ready        0  2020-04-17 05:19:02.744528          1  project1   
8      8  checking        0  2020-04-17 05:22:35.249331          2  project1   
9      9     ready        1  2020-04-17 05:22:45.549397          2  project1   

       uid datetime  
0  admin_1     None  
1  admin_1     None  
2  admin_1     None  
3  admin_1     None  
4  admin_1     None  
5  admin_1     None  
6     None     None  
7   user_4     None  
8   user_4     None  
9   user_4     None  

Количество строк, удовлетворяющих условиям:
   cnt
0  985 Task 3
В этом упражнении ты создашь так называемую витрину данных. Она представляет собой таблицу, которую можно использовать для аналитических целей. Обычно она создается путем объединения нескольких отдельных таблиц. В этом упражнении мы будем собирать различные данные о наших пользователях: когда они сделали свои первые коммиты, когда они впервые посетили ленту новостей и т. д. Это поможет позднее выполнить анализ данных.

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

Создай соединение с базой данных с помощью библиотеки sqlite3.
Создай новую таблицу datamart в базе данных, объединив таблицы pageviews и checker с помощью только одного запроса.
Таблица должна содержать следующие столбцы: uid, labname, first_commit_ts, first_view_ts.
first_commit_ts — это просто новое имя для столбца timestamp из таблицы checker; он показывает первый коммит конкретного лабораторного задания конкретного пользователя.
first_view_ts — первое посещение пользователем из таблицы pageviews, метка времени посещения пользователем ленты новостей.
По-прежнему нужно использовать фильтр status = 'ready'.
По-прежнему нужно использовать фильтр numTrials = 1.
Имена лабораторных заданий по-прежнему должны быть из следующего списка: laba04, laba04s, laba05, laba06, laba06s, project1.
Таблица должна содержать только пользователей (uid с user_*), а не администраторов.
first_commit_ts и first_view_ts должны быть распарсены как datetime64[ns].
Используя методы библиотеки Pandas, создай два датафрейма: test и control.
test должен включать пользователей, у которых имеются значения в first_view_ts.
control должен включать пользователей, у которых отсутствуют значения в first_view_ts.
Замени пропущенные значения в control средним значением first_view_ts пользователей из test (оно пригодится нам для анализа в будущем).
Сохрани обе таблицы в базе данных (вы будете использовать их в следующих упражнениях).
Закрой соединение. import sqlite3
import pandas as pd

# Create a connection to the database
conn = sqlite3.connect('/content/drive/MyDrive/School21/day10/checking-logs.sqlite')

# Define SQL query
query = '''
CREATE TEMPORARY TABLE datamart AS
SELECT c.uid, c.labname, c.timestamp AS first_commit_ts, MIN(p.datetime) AS first_view_ts
FROM checker AS c
LEFT JOIN pageviews AS p ON c.uid = p.uid
WHERE c.status = 'ready' AND c.numTrials = 1 AND c.labname IN ('laba04', 'laba04s', 'laba05', 'laba06', 'laba06s', 'project1') AND c.uid LIKE 'user_%'
GROUP BY c.uid, c.labname
'''

# Execute the query and create datamart dataframe
conn.execute(query)
datamart = pd.read_sql_query('SELECT * FROM datamart', conn, parse_dates=['first_commit_ts', 'first_view_ts'])

# Create test and control dataframes
test = datamart[datamart['first_view_ts'].notnull()]
control = datamart[datamart['first_view_ts'].isnull()]

mean_view_ts = test['first_view_ts'].mean()
control['first_view_ts'].fillna(mean_view_ts, inplace=True)
test.to_sql('test_table', conn, if_exists='replace', index=False)
control.to_sql('control_table', conn, if_exists='replace', index=False)

# Close the connection
conn.close() <ipython-input-46-9f10716fa9e6>:26: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  control['first_view_ts'].fillna(mean_view_ts, inplace=True)  datamart.info() <class 'pandas.core.frame.DataFrame'>
RangeIndex: 140 entries, 0 to 139
Data columns (total 4 columns):
 #   Column           Non-Null Count  Dtype         
---  ------           --------------  -----         
 0   uid              140 non-null    object        
 1   labname          140 non-null    object        
 2   first_commit_ts  140 non-null    datetime64[ns]
 3   first_view_ts    59 non-null     datetime64[ns]
dtypes: datetime64[ns](2), object(2)
memory usage: 4.5+ KB test.info() <class 'pandas.core.frame.DataFrame'>
Int64Index: 59 entries, 0 to 114
Data columns (total 4 columns):
 #   Column           Non-Null Count  Dtype         
---  ------           --------------  -----         
 0   uid              59 non-null     object        
 1   labname          59 non-null     object        
 2   first_commit_ts  59 non-null     datetime64[ns]
 3   first_view_ts    59 non-null     datetime64[ns]
dtypes: datetime64[ns](2), object(2)
memory usage: 2.3+ KB control.info() <class 'pandas.core.frame.DataFrame'>
Int64Index: 81 entries, 12 to 139
Data columns (total 4 columns):
 #   Column           Non-Null Count  Dtype         
---  ------           --------------  -----         
 0   uid              81 non-null     object        
 1   labname          81 non-null     object        
 2   first_commit_ts  81 non-null     datetime64[ns]
 3   first_view_ts    81 non-null     datetime64[ns]
dtypes: datetime64[ns](2), object(2)
memory usage: 3.2+ KB Task 4
На предыдущих этапах ты занимался просто подготовкой данных. Ты не получил никакой аналитической информации на основе имеющихся данных. Пришло время приступить к анализу. Помни, что, согласно нашему предположению, пользователи приступили бы к работе над лабораторными заданиями раньше, если бы они просмотрели ленту новостей? Это означает, что ключевой метрикой для нас является период времени (delta) между датой начала работы пользователя над лабораторным заданием (первого коммита) и сроком сдачи лабораторного задания. Мы будем смотреть на то, меняется ли она в зависимости от просмотра страницы.

Что тебе нужно сделать в этом упражнении:

Создай соединение с базой данных с помощью библиотеки sqlite3.
Получи схему таблицы test.
Получи только первые 10 строк таблицы test, чтобы проверить, как она выглядит.
Найди среди всех пользователей минимальное значение этой самой дельты (периода времени от даты первого коммита пользователя до срока сдачи соответствующего лабораторного задания), используя только один запрос.
Для этого соедини данные своей таблицы с данными таблицы deadlines.
Период времени должен отображаться в часах.
Не учитывай лабораторное задание project1 с более длительным сроком сдачи, поскольку оно будет выделяться на фоне других.
Сохрани значение в датафрейме df_min с соответствующим uid.
Выполни те же самые операции для максимального значения дельты, используя только один запрос. Название итогового датафрейма — df_max.
Выполни те же самые операции для среднего значения дельты, используя только один запрос. На этот раз ваш итоговый датафрейм не должен включать столбец uid; имя датафрейма — df_avg.
Мы хотим проверить предположение о том, что у пользователей, посетивших ленту новостей всего несколько раз, период времени между датой первого коммита и сроком сдачи лабораторного задания меньше. Для этого тебе необходимо рассчитать коэффициент корреляции между количеством просмотров страниц и разницей между датами.
Используя только один запрос, создай таблицу со столбцами: uid, avg_diff, pageviews.
uid — это uid, существующие в таблице test.
avg_diff — среднее значение дельты (периода времени между датой первого коммита и сроком сдачи лабораторного задания) для каждого пользователя.
pageviews — количество посещений ленты новостей одним пользователем.
Не учитывай лабораторное задание project1.
Сохрани результаты в датафрейме views_diff.
Используй метод corr() библиотеки Pandas для вычисления коэффициента корреляции между количеством просмотров и значением дельты.
Закрой соединение. import sqlite3
import pandas as pd

# Создаем соединение с БД
conn = sqlite3.connect('/content/drive/MyDrive/School21/day10/checking-logs.sqlite')

# Получаем схему таблицы test
cursor = conn.cursor()
cursor.execute("PRAGMA table_info(test);")
print(cursor.fetchall())

# Получаем первые 10 строк таблицы test
df_test = pd.read_sql_query("SELECT * FROM test LIMIT 10;", conn)
print(df_test)

# Находим минимальное значение дельты и сохраняем в df_min
min_query = """
SELECT uid, MIN((julianday(deadlines.deadlines) - julianday(test.first_commit_ts)) * 24) as min_diff
FROM test
JOIN deadlines ON test.labname = deadlines.labs
GROUP BY uid;
"""
df_min = pd.read_sql_query(min_query, conn)
print(df_min)

# Находим максимальное значение дельты и сохраняем в df_max
max_query = """
SELECT uid, MAX((julianday(deadlines.deadlines) - julianday(test.first_commit_ts)) * 24) as max_diff
FROM test
JOIN deadlines ON test.labname = deadlines.labs
GROUP BY uid;
"""
df_max = pd.read_sql_query(max_query, conn)
print(df_max)

# Находим среднее значение дельты и сохраняем в df_avg
avg_query = """
SELECT AVG((julianday(deadlines.deadlines) - julianday(test.first_commit_ts)) * 24) as avg_diff
FROM test
JOIN deadlines ON test.labname = deadlines.labs;
"""
df_avg = pd.read_sql_query(avg_query, conn)
print(df_avg)

# Создаем таблицу со столбцами: uid, avg_diff, pageviews
views_diff_query = """
SELECT test.uid,
       AVG((julianday(deadlines.deadlines) - julianday(test.first_commit_ts)) * 24) as avg_diff,
       COUNT(pageviews.datetime) as pageviews
FROM test
JOIN deadlines ON test.labname = deadlines.labs
JOIN pageviews ON test.uid = pageviews.uid
GROUP BY test.uid
HAVING avg_diff IS NOT NULL;
"""
views_diff = pd.read_sql_query(views_diff_query, conn)
print(views_diff)

# Вычисляем коэффициент корреляции между количеством просмотров и значением дельты
correlation_coefficient = views_diff['pageviews'].corr(views_diff['avg_diff'])
print(correlation_coefficient)

# Закрываем соединение
conn.close() [(0, 'uid', 'TEXT', 0, None, 0), (1, 'labname', 'TEXT', 0, None, 0), (2, 'first_commit_ts', 'TIMESTAMP', 0, None, 0), (3, 'first_view_ts', 'TIMESTAMP', 0, None, 0)]
       uid   labname             first_commit_ts               first_view_ts
0  user_17  project1  2020-04-18 07:56:45.408648  2020-04-18 10:56:55.833899
1  user_30    laba04  2020-04-18 13:36:53.971502  2020-04-17 22:46:26.785035
2  user_30   laba04s  2020-04-18 14:51:37.498399  2020-04-17 22:46:26.785035
3  user_14    laba04  2020-04-18 15:14:00.312338  2020-04-18 10:53:52.623447
4  user_14   laba04s  2020-04-18 22:30:30.247628  2020-04-18 10:53:52.623447
5  user_19    laba04  2020-04-20 19:05:01.297780  2020-04-21 20:30:38.034966
6  user_25    laba04  2020-04-20 19:16:50.673054  2020-05-09 23:54:54.260791
7  user_21    laba04  2020-04-21 17:48:00.487806  2020-04-22 22:40:36.824081
8  user_30  project1  2020-04-22 12:36:24.053518  2020-04-17 22:46:26.785035
9  user_21   laba04s  2020-04-22 20:09:21.857747  2020-04-22 22:40:36.824081
        uid min_diff
0    user_1     None
1   user_10     None
2   user_14     None
3   user_17     None
4   user_18     None
5   user_19     None
6   user_21     None
7   user_25     None
8   user_28     None
9    user_3     None
10  user_30     None
        uid max_diff
0    user_1     None
1   user_10     None
2   user_14     None
3   user_17     None
4   user_18     None
5   user_19     None
6   user_21     None
7   user_25     None
8   user_28     None
9    user_3     None
10  user_30     None
  avg_diff
0     None
Empty DataFrame
Columns: [uid, avg_diff, pageviews]
Index: []
nan