Наука о данных

И. В. Щуров, НИУ ВШЭ

Авторы задач в подборке: Ф. Тюрин, И. Щуров.

На странице курса находятся дополнительные материалы.

Домашнее задание №11

Для получения полного балла нужно решить все задачи (набрать 13 баллов).

In [ ]:
import sqlite3

Мы будем работать с данными о приложениях из Google Play Store. У нас будут два датасета: apps с техническими данными о приложениях, и reviews с отзывами пользователей. Описание можно посмотреть по ссылке. Для начала загрузим базу на диск (это надо будет сделать только один раз).

In [ ]:
import requests
import os
from hashlib import md5

class MyError(Exception):
    pass

def get_file_md5(filename):
    ### FROM: https://stackoverflow.com/a/3431838/3025981
    hash_md5 = md5()
    with open(filename, "rb") as f:
        for chunk in iter(lambda: f.read(1024), b""):
            hash_md5.update(chunk)
    return hash_md5.hexdigest()
    ### END FROM
        
try:
    if get_file_md5("apps.sqlite") != "5eddd7a74c85226de623f209a5c5d4e3":
        raise MyError
except (MyError, FileNotFoundError): 
    try:
        r = requests.get("https://github.com/ischurov/pythonhse/raw/master/apps.sqlite")
        with open("apps.sqlite", "wb") as f:
            f.write(r.content)
        assert get_file_md5("apps.sqlite") == "5eddd7a74c85226de623f209a5c5d4e3", \
           "База данных повреждена — обратитесь к преподавателю"
    except requests.ConnectionError:
        print("Что-то со связью — проверьте, что интернет работает, и запустите ячейку заново")

Чтобы заработали тесты, нам понадобится также такая функция. (Если вы получите сообщение об ошибке о том, что myhash не определено — запустите эту ячейку ещё раз.)

In [ ]:
from hashlib import md5

def myhash(x):
    return int(md5(str(x).encode('utf-8')).hexdigest()[:6], 16)

Теперь подключимся к базе

In [ ]:
conn = sqlite3.connect("apps.sqlite")
c = conn.cursor()

Начнём работать с таблицей apps. Вот такие столбцы в ней есть:

In [ ]:
c.execute("""
PRAGMA table_info(apps);
""").fetchall()

Задача 0 (1 балл)

С помощью подходящего SQL-запроса запишите в список genres все жанры, встречающиеся в базе, упорядоченные по алфавиту. Каждый жанр должен упоминаться только один раз. Запрещено использовать set и sort.

После выполнения ячейки с решением genres должен выглядеть примерно так:

['Action',
 'Action;Action & Adventure',
 'Adventure',
 'Adventure;Action & Adventure',
 'Adventure;Brain Games',
 'Adventure;Education',
 'Arcade',
 ...]

Подсказка. Чтобы получить все различные значения, нужно использовать команду DISTINCT после SELECT. Ещё вам нужен ORDER BY. Помните о том, что c.execute(...).fetchall() возвращает список кортежей.

In [ ]:
"# YOUR CODE HERE"
In [ ]:
assert myhash(genres) == 10234285
assert len(genres) == 120

Задача 1 (1 балл)

Напишите функцию get_good_free_apps(rating), которая принимает на вход число с плавающей точкой rating, выбирает колонки App и Category из датасета apps и возвращает только те приложения и их категории, которые можно скачать бесплатно и рейтинг которых не меньше rating. Функция должна возвращать список кортежей типа такого:

[('Hojiboy Tojiboyev Life Hacks', 'COMICS'),
 ('American Girls Mobile Numbers', 'DATING'),
 ('Awake Dating', 'DATING'),
 ...]

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

In [ ]:
"# YOUR CODE HERE"
In [ ]:
assert sorted(get_good_free_apps(5))[:5] == [('420 BZ Budeze Delivery', 'MEDICAL'),
 ('420 BZ Budeze Delivery', 'MEDICAL'),
 ('A-Y Collection', 'SHOPPING'),
 ('AI Today : Artificial Intelligence News & AI 101', 'NEWS_AND_MAGAZINES'),
 ('AJ Cam', 'PHOTOGRAPHY')]

assert (sum(myhash(x) for x in get_good_free_apps(4.9))) == 2675354175
assert (sum(myhash(x) for x in get_good_free_apps(5))) == 2003112710
assert set(get_good_free_apps(4.7)).issubset(set(get_good_free_apps(4.6)))

Задача 2 (2 балла)

Сделайте запрос, который для каждой категории приложений из датасета apps посчитает средний рейтинг, минимальный рейтинг и максимальный рейтинг, после чего отсортирует категории по среднему рейтингу в порядке убывания и оставит только те категории, для которых минимальный рейтинг больше 1. Результат запроса (список, состоящий из четырёхэлементных кортежей: категория и три числа) поместите в переменную catstat. Все вычисления должны производиться средствами SQL: вам нужно написать только текст запроса.

Подсказка. В числе прочего, вам понадобится HAVING. Посмотрите в документации, чем он отличается от WHERE.

In [ ]:
catstat = c.execute(
"# YOUR CODE HERE"
).fetchall()
In [ ]:
assert myhash(catstat) == 6699152

Задача 3 (1 балл)

Одна из категорий выглядит очень странно — похоже на ошибку в базе. Удалите все записи с этой категорией. (Не записывайте результат в базу, то есть не делайте conn.commit(), чтобы задачи выше продолжили работать. Если вы сделали запрос, модифицирующий базу, но ещё не сделали conn.commit(), вы можете вернуть её в исходное состояние с помощью conn.rollback().)

Подсказка. Чтобы удалить строчки из таблицы нужно использовать команду DELETE.

In [ ]:
"# YOUR CODE HERE"
In [ ]:
assert c.execute("""
SELECT * FROM apps WHERE [index] = 10472
""").fetchall() == []

Задача 4 (2 балла)

Сделайте запрос, который находит 50 самых дорогих приложений, сортирует их по цене и помещает в список их название и цену. Результат должен быть сохранён в список expensive_apps.

Этот список должен выглядеть примерно так:

[("I'm Rich - Trump Edition", 400.0),
 ('most expensive app (H)', 399.99),
 ("💎 I'm rich", 399.99),
 ('I am rich', 399.99),
 ...]

Подсказка. Цена в базе записана в виде строк, начинающихся с символа $. Вам необходимо отрезать этот символ и переконверировать результат в вещественное число. Это можно и нужно сделать средствами SQL: вам понадобятся функции SUBSTR и CAST. Как обычно, нельзя пользоваться ничем, кроме SQL, то есть редактировать можно только текст запроса.

In [ ]:
expensive_apps = c.execute(
"# YOUR CODE HERE"
).fetchall()
In [ ]:
assert myhash(expensive_apps) in [13992335, 5391293]

Перейдем ко второму датасету reviews.

In [ ]:
c.execute("""
PRAGMA table_info(reviews);
""").fetchall()

Задача 5 (1 балл)

Удалите из датасета reviews все строчки, у которых Sentiment является пропущенным значением (NULL). (Не записывайте результат в базу, то есть не делайте conn.commit(). Если вы сделали запрос, модифицирующий базу, но ещё не сделали conn.commit(), вы можете вернуть её в исходное состояние с помощью conn.rollback())

Подсказка. Для проверки того, что что-то является NULL, нужно использовать сравнение IS, а не =.

In [ ]:
"# YOUR CODE HERE"
In [ ]:
assert c.execute("""
SELECT count(*) FROM reviews
""").fetchone()[0] == 37432

Задача 6 (2 балла)

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

[('GAME', 7270),
 ('FAMILY', 1559),
 ('SPORTS', 821),
 ...]

Как обычно, можно пользоваться только SQL.

In [ ]:
cat_neg_rev = c.execute(
"# YOUR CODE HERE"
).fetchall()
In [ ]:
assert myhash(cat_neg_rev) == 9929126

Задача 7 (3 балла)

Повторите предыдущее упражнение, но в этот раз вместо числа отрицательных отзывов выведите долю отрицательных отзывов в общем числе отзывов по всем приложениям из данной категории. (При вычислении доли следует игнорировать записи, в которых в Sentiment записан NULL, как если бы их не было.) Поместите результат (список двухэлементных кортежей — категория и доля (число с плавающей точкой)) в переменную cat_neg_share. Изменился ли порядок категорий? Как обычно, можно пользоваться только SQL.

Подсказка. Вам может пригодиться CASE.

In [ ]:
cat_neg_share = c.execute(
"# YOUR CODE HERE"
).fetchall()
In [ ]:
assert myhash([(a, round(b, 4)) for a, b in cat_neg_share]) == 2803714

Когда всё сделано можно закрыть соединение с базой и удалить файл.

In [ ]:
conn.close()
import os
try:
    os.unlink("apps.sqlite")
except FileNotFoundError:
    pass