Введение
Данная работа описывает мою попытку создать модель машинного обучения для предсказания выживших пассажиров "Титаника".
В этой статье я буду использовать SQL для исследовательского анализа и создания новых признаков, а так же Python для тестирования моделей машинного обучения.
Писать код я буду в блокноте Kaggle. Данные я загружу в облачный сервис Google BigQuery, который поддерживает множество СУБД. Обращаться к данным, в облачной БД, я буду через SQL-запросы.
Цель
Не смотря на то, что Python был бы предпочтительным инструментом для исследовательского анализа, я хотел посмотреть, смогу ли я провести весь исследовательский анализ с помощью SQL. Моя цель - показать, насколько сильно может помочь SQL для рабочего процесса. Хотя SQL имеет ограничения с визуализацией данных, всё равно, можно получить больше выгоды, быстро написав запросы и выведя результаты.
Получение данных
Kaggle предоставляет данные в виде двух файлов в формате csv:
train.csv (содержит 10 признаков для пассажиров, включая целевой - выживание)
test.csv (содержит 10 признаков)
Загрузим данные в облачную БД GBQ и отправим запрос, чтобы получить их.
from google.cloud import bigquery
# Добавляем ID проекта из Google Cloud Platform
PROJECT_ID = 'kaggle-titanic-sql'
client = bigquery.Client(project=PROJECT_ID, location="US")
dataset = client.create_dataset('741852', exists_ok=True)
# Создаём ссылку на таблицу
table = client.get_table("kaggle-titanic-sql.741852.titanic_train")
# Просматриваем первые пять строк из набора данных
client.list_rows(table, max_results=5, start_index=1).to_dataframe()ВАЖНО: Используйте данный код, чтобы выполнить SQL-запрос к GBQ, и получить данные в виде датафрейма.
query = """ SQL - запрос """
query_job = client.query(query);
query_df = query_job.to_dataframe();
query_dfАнализ данных
Исследовательский анализ является одной из самых наиболее важных работ при построении модели. Он помогает выявить очевидные ошибки, а также лучше понять закономерности в данных, обнаружить выбросы или аномалии и, возможно, найти интересные взаимосвязи между переменными, проверить гипотезы. Часто во время этого процесса можно увидеть скрытые зависимости, благодаря использованию которых и получаются наиболее точные модели.
Основная задача — найти зависимости в данных, влияющие на вероятность выжить при крушении "Титаника", и на основе этих зависимостей построить новые признаки, улучшающие точность модели.
На данном этапе, исследовательский анализ мы будем проводить только с обучающей выборкой (т.к. для тестовой выборки нет целевого признака - "Survived"). В дальнейшем, при обработке пропущенных значений и составлении новых признаков включим тестовую выборку.
1. Пропущенные значения
Для начала посмотрим на процент отсутствующих значений для каждого из признаков обучающей выборки, чтобы иметь представление о потенциальных признаках, которые нужно оценить, а которые даже отбросить из-за большого количества пропущенных данных, которые мы не сможем восстановить в силу нарушения дисперсии исходной выборки.
Таким образом имеется 19,9%, 77,1%, 0,2% пропущенных значений для признаков "Age", "Cabin" и "Embarked" соответственно. И если с признаком "Age" можно провести обоснованное замещение пропущенных значений, в связи с небольшой их долей (это будет сделано позднее), то для признака "Cabin" маловероятно получится сделать то же самое, т.к. пропущенных значений даже больше чем заполненных.
select
round(100 * sum(case when survived is null then 1 else 0 end) / max(f.total_passeng), 1) as Survivedved,
round(100 * sum(case when pclass is null then 1 else 0 end) / max(f.total_passeng), 1) as Pclass,
round(100 * sum(case when name is null then 1 else 0 end) / max(f.total_passeng), 1) as Name,
round(100 * sum(case when sex is null then 1 else 0 end) / max(f.total_passeng), 1) as Sex,
round(100 * sum(case when age is null then 1 else 0 end) / max(f.total_passeng), 1) as Age,
round(100 * sum(case when sibsp is null then 1 else 0 end) / max(f.total_passeng), 1) as SibSp,
round(100 * sum(case when parch is null then 1 else 0 end) / max(f.total_passeng), 1) as Parch,
round(100 * sum(case when ticket is null then 1 else 0 end) / max(f.total_passeng), 1) as Ticket,
round(100 * sum(case when fare is null then 1 else 0 end) / max(f.total_passeng), 1) as Fare,
round(100 * sum(case when cabin is null then 1 else 0 end) / max(f.total_passeng), 1) as Cabin,
round(100 * sum(case when embarked is null then 1 else 0 end) / max(f.total_passeng), 1) as Embarked
from
(
select
count(*) over() as total_passeng,
t.*
from `kaggle-titanic-sql.741852.titanic_train` t
) fДля поиска пропущенных значений используется оператор CASE. Оператор CASE перебирает условия и возвращает значение, когда выполняется одно из условий, которому он соответствует (аналог оператора if-elif-else в Python).
В нашем случае условия будут такими: если в "признаке" значение является пропуском - null, тогда ставится флаг (1), иначе (0). Далее считается сумма пропущенных значений с флагом (1) и сумма пропущенных значений делится на суммарное количество пассажиров обучающей выборки.
2. Общая выживаемость
В обучающем наборе имеется 891 пассажир, из них выживших 342 человека, что составляет 38,4% от общего количества. Кроме того, это дает нам понятие о том, что целевой класс - "Survived" не сильно разбалансирован - 62% / 38%.
select
case when f.survived = 1 then 'Survived' else 'Not Survived' end as Survival_status,
count(*) as Survival_rate,
round(100 * count(*) / max(f.total_passeng), 1) as Percent,
max(f.total_passeng) as Total_passengers
from
(
select
count(*) over() as total_passeng,
t.*
from `kaggle-titanic-sql.741852.titanic_train` t
) f
group by f.survived3. Выживание по полу и возрасту
Сведения из википедии: на борту "Титаника" было 1317 пассажиров и 908 членов экипажа, в общей сумме 2225 человек. "Титаник" был британским пароходом. Согласно законам Британии на корабле должно было быть число шлюпок, соответствующее водоизмещению судна, но не пассажировместимости. Титаник соответствовал этим требованиям и имел 20 шлюпок, которые были рассчитаны на погрузку 1178 человек.
Гипотеза: Капитан понимал, что мест на всех в шлюпках не хватит, и был отдан приказ - в первую очередь сажать на шлюпки женщин и детей.
Проверим нашу гипотезу о том, что у женщин и детей было преобладание на шанс выживания по сравнению с мужчинами.
Отделение женщин от мужчин среди их общего количества показывает:
Женщин было почти в 2 раза меньше чем мужчин на судне: 64.76% мужчин и 35.24% женщин.
Относительная выживаемость внутри половых групп составляет 74.2% и 18.89% для женщин и мужчин соответственно.
Уже на данном этапе можно сказать, что одним из основных признаков для модели будет пол пассажира - "Sex".
select
f.sex,
sum(f.survived) as count_survived,
max(f.gender_cnt) as total_passeng,
round(100 * max(f.gender_cnt) / max(f.total_passeng), 2) as sex_rate,
round(100 *sum(f.survived) / max(gender_cnt), 2) as percent_survived,
from
(
select
passengerid,
count(*) over(partition by sex) as gender_cnt,
count(*) over() as total_passeng,
sex,
survived
from `kaggle-titanic-sql.741852.titanic_train` t
) f
group by f.sexДиапазон возраста слишком велик, поэтому разделим его на 7 групп (дети младше 10, от 10 до 20 лет, и т.д.), и сгруппируем в зависимости от пола, чтобы получить более обобщенную оценку выживания.
Разделив возрастной диапазон на 7 групп, можно увидеть вторую часть подтверждения нашей гипотезы: Выживаемость женщин в каждой из возрастных групп имело довольно большой процент - больше 60%, в то время как среди мужчин такой результат приходился только на группу детей младше 10 лет - 59%, начиная с возрастной группы мужчин от 10 до 20 лет выживаемость уже составляла 12% и оставалась в этом диапазоне для оставшихся групп.
select
sex,
case
when age < 10 then '10 < age'
when age >=10 and age < 20 then '10<= age < 20'
when age >= 20 and age < 30 then '20<= age < 30'
when age >=30 and age < 40 then '30<= age < 40'
when age >=40 and age < 55 then '40<= age < 55'
when age >=55 then '55 <= age'
else 'None_age'
end as age_grouping,
sum(survived) as count_survived,
count(sex) as total_count,
round(sum(survived)/count(survived), 4) as percent_survived
from `kaggle-titanic-sql.741852.titanic_train`
group by age_grouping, sex
order by sex, age_grouping4. Выживание на основе титула из оригинального имени
Признак с именами пассажиров - "Name" сам по себе не может рассказать нам что-то о выживаемости, но его можно использовать для создания новых признаков, которые будут иметь относительно более высокую предсказательную силу. Например, выделим из признака "Name" оригинальные титулы.
Посмотрим на количество оригинальных титулов в именах. Всего в данном датасете имеется 17 оригинальных титулов, большинство из них слишком разряжены, чтобы делать оценочные суждения. Например, в таких титулах как: Sir, Major наблюдается единичное количество вхождений, по сравнению с такими титулами, как Miss и Mr.
select
split(split(f.name, ',' )[offset(1)], '.')[offset(0)] as title_name,
count(f.total_passeng) as total_passeng,
sum(f.survived) as total_survived,
round(100 * sum(f.survived) / count(f.total_passeng), 1) as percent_survived
from
(
select
name,
count(*) over() as total_passeng,
survived,
from `kaggle-titanic-sql.741852.titanic_train` t
) f
group by title_nameЧтобы сделать каждую группу актуальной и получить более общую оценку, объединим 17 титулов в обобщенные группы: Mr, Mrs, Miss и Master. Титул Master в 19 веке применялось по отношению к детям мужского пола, Miss применялось по отношению к незамужним женщинам, но в 19 веке незамужними были, в подавляющем большинстве, только молодые девушки и девочки, Mrs применялось по отношению к замужним женщинам, Mr - мужчина, неважно, замужний или нет.
В группу Mr обобщим всех мужчин, кроме детей "Master". В группу Miss будут входить все незамужние девушки и женщины, а в группу Mrs объединим всех замужних девушек и женщин, так же девушек и женщин в титулах которых предполагается, что они были замужем.
Выживаемость обобщенных групп на основе оригинального титула, так же подтверждает нашу гипотезу: Все женщины имели высокий процент выживаемости, в то время как среди мужчин высокий результат приходился только на детей.
Я внесу и закодирую данный признак - title_grouping_name при построении модели.
select
case
when split(split(name, ', ')[offset(1)], '.')[offset(0)]in('Dr','Rev','Col','Major','Jonkheer','Sir','Don','Capt')
and sex = 'male' then 'Mr'
when split(split(name, ', ')[offset(1)], '.')[offset(0)] in ('Mlle', 'Ms')
and sex = 'female' then 'Miss'
when split(split(name, ', ')[offset(1)], '.')[offset(0)] in ('Dr','the Countess','Mme','Lady','Dona')
and sex = 'female' then 'Mrs'
else split(split(name, ', ')[offset(1)], '.')[offset(0)]
end as title_grouping_name,
count(*) as total_passeng,
sum(survived) as total_survived,
round(100 * sum(survived) / count(*), 1) as survival_rate
from `kaggle-titanic-sql.741852.titanic_train`
group by title_grouping_name
order by percent_survived desc5. Выживание на основе уровня каюты и места посадки
Сведения из википедии: "Титаник" столкнулся с айсбергом в ночь с 14 на 15 апреля 1912 года. 14 апреля в 23:39 вперёд смотрящий доложил на капитанский мостик об айсберге прямо по курсу. Меньше чем через минуту произошло столкновение.
Гипотеза: Столкновение произошло в 23:40 вечера, в 23:40 подавляющее большинство пассажиров и корабельной команды находились в своих каютах - уровень каюты влиял на шанс выживания, т.к. пассажиры нижних палуб, позднее узнали о столкновении и, соответственно, имели меньше времени добраться до верхней палубы.
Каюты «Титаника» разделялись на три класса: первый класс был ближе всего расположен к палубе "Титаника", третий класс был ближе всего расположен ко дну судна, второй класс - между первым и третьим.
Сгруппировав данные по уровням кают - "Pclass", можно увидеть тенденцию снижения выживаемости, при снижении уровня кают: 63% для кают первого класса, 47% для кают второго класса и 24% для кают третьего класса, так же подавляющее большинство пассажиров находились именно в каютах третьего класса. Здесь мы находим подтверждение нашей гипотезы о влиянии уровня каюты на шанс выживания. Кроме того, вторым нашим главным признаком на выживание, при построении модели, будет расположение каюты - "Pclass".
select
pclass,
round(sum(survived)/count(survived), 4) as percent_survived,
count(*) as total_passeng
from `kaggle-titanic-sql.741852.titanic_train`
group by pclass
order by pclassИзучив подробнее выживание на основе класса каюты - "Pclass", можно увидеть интересную картину: у женщин первого и второго класса каюты, выживаемость составляет больше 90%, в то время как у женщин третьего класса выживаемость всего 50%. У мужчин, которые были в первом классе кают наибольший процент выживаемости, среди всех трех классов кают, 36,89% в противовес 15,74% для кают второго класса и 13,54% для кают третьего класса.
Мы уже подтвердили нашу гипотезу о том, что класс каюты влиял на шанс выживания, но вдобавок к этому, выживаемость снижалась, cо снижением уровня кают, вне зависимости от пола пассажира.
select
pclass,
count(*) as count_pass_group,
count(case when sex='female' then 1 else null end) as female_count,
count(case when sex='male' then 1 else null end) as male_count,
round(100*sum(case when sex='female' and survived=1 then 1 else 0 end)/sum(case when sex='female' then 1 else 0 end), 2) as female_survived_rate,
round(100*sum(case when sex='male' and survived=1 then 1 else 0 end)/sum(case when sex='male' then 1 else 0 end), 2) as male_survived_rate,
from `kaggle-titanic-sql.741852.titanic_train`
group by pclass
order by pclassПервоначально Титаник остановился в Саутгемптоне (S) - Англия, а затем переместился в Шербур (C) - Франция. Наконец, остановка в Квинстауне (Q) - Ирландия, перед отплытием в Нью-Йорк.
Из минусов: сам по себе признак "Embarked" мало что может рассказать про историю выживания.
Из плюсов: признак "Embarked" рассказывает нам, что подавляющее большинство пассажиров сделали посадку с порта в Саутгемптоне (S) - Англии, подавляющее меньшинство с порта в Квинстауне (Q) - Ирландии. (Но вряд ли мы сможем это использовать).
Так же имеется 2 пассажира, о которых неизвестно в каком из портов они сели на "Титаник". Вбив их имена в адресную строку "Google" я нашел энциклопедию "Титаника" (https://www.encyclopedia-titanica.org/), в которой говорится что оба этих пассажира сели на судно в Саутгемптоне (S) - Англии (позже заполним эти пропуски).
select
embarked,
count(*) as count_passengers,
round(100 * sum(survived)/count(survived), 2) as survived_rate
from `kaggle-titanic-sql.741852.titanic_train`
group by embarked6. Выживание по тарифам и средней стоимости проезда на пассажира
Чтобы быстро получить представление о соотношении цены и выживания, используем оконную функцию "ntile", чтобы равномерно распределить пассажиров по 6 корзинам и создать статистику для более внимательного изучения. Это быстрый и грязный метод группирования, поскольку одни и те же суммы тарифов могут попадать в разные ячейки, как показано с помощью "fare_min" и "fare_max".
На первый взгляд, "survival_rate" будет расти по мере удорожания билетов. В то же время "fare_mean" довольно резко переходит с 5-го на 6. Самая высокая цена "fare_max" составляет 512,3292 фунта стерлингов, а самая низкая "fare_min"- 0 фунта. В каждой из групп наблюдается, что выживаемость женщин намного выше, чем выживаемость мужчин..
select
fare_grouping,
count(*) as passenger_cnt,
count(case when sex = 'female' then passengerid else null end) as female_passenger_cnt,
count(case when sex = 'male' then passengerid else null end) as male_passenger_cnt,
min(fare) as fare_min,
max(fare) as fare_max,
round(avg(fare),2) as fare_mean,
round(avg(survived),4) as survival_rate,
round(avg(case when sex = 'female' then survived else null end),4) as female_survival_rate,
round(avg(case when sex = 'male' then survived else null end),4) as male_survival_rate,
from
(
select
passengerid,
pclass,
sex,
fare,
survived,
ntile(6) over (order by fare) as fare_grouping
from `kaggle-titanic-sql.741852.titanic_train`
) f
group by 1
order by 1;Сгруппировав данные по номерам билета - "Ticket", можно увидеть, что стоимость билета представляет собой не тариф на пассажира, а тариф на всех пассажиров входящих в данный билет.
Например, в билете "19950" имеется 4 пассажира, все они из одной семьи "Fortune" и располагались в одной и той же каюте. Так же имеются билеты, в которых представлены пассажиры с разных семей и располагались в разных каютах (здесь я не буду это показывать).
select
ticket,
count(*) as count_num
from `kaggle-titanic-sql.741852.titanic_train`
group by ticket
having count_num >= 3
order by count_num desc;
select
ticket,
pclass,
name,
sex,
survived,
age,
cabin,
embarked
from `kaggle-titanic-sql.741852.titanic_train`
where ticket = '19950'Посмотрим какова средняя стоимость проездного на одного пассажира для каждого из класса кают. Для этого разделим общую стоимость билета на количество пассажиров данного билета и сгруппируем средние значения по классам кают - "Pclass".
В среднем, стоимость проезда в первом классе на пассажира составляла 42 фунта стерлингов, во втором классе - 14 фунта стерлингов, а в третьем классе - 8. Так же, минимальная цена за проезд в каждом из классе составляла 0 фунтов (я не знаю с чем это может быть связано, и информации по этому поводу я не нашел). Максимальная цена за путешествие на одного пассажира 222, 33, 23 фунта для первого, второго и третьего класса соответственно.
Добавление признака "avg_per_passenger" может быть избыточно для модели, так как он будет сильно коррелировать с признаком "Pclass", возможно в дальнейшем мы захотим отказаться от одного из них.
select
f.pclass,
round(avg(f.fare_per_passeng), 4) as avg_per_passeng,
min(f.fare_per_passeng) as min_per_passeng,
max(f.fare_per_passeng) as max_per_passeng
from
(
select
pclass,
ticket,
count(*) as count_passeng,
round(max(fare)/count(passengerid)) as fare_per_passeng
from `kaggle-titanic-sql.741852.titanic_train`
group by pclass, ticket
)f
group by pclass
order by pclassСоставление новых признаков и заполнение пропущенных значений
Проведя исследовательский анализ, мы лучше поняли, как выживаемость пассажиров зависит от различных признаков. Соберем датасет для обучения модели, где обобщим наши выводы.
1. Объединение обучающей и тестовой выборки
Для начала объединим обучающую и тестовую выборку, чтобы не писать выкладки для каждого датасета отдельно. Так как в обучающем наборе нет целевого признака - "Survived", создадим его, заполнив значениями "null", и для каждого из наборов добавим новый признак "dataset", который будет являться флагом, чтобы мы смоги разделить наши выборки после преобразований.
drop table if exists `kaggle-titanic-sql.741852.titanic_train_test`;
create table if not exists `kaggle-titanic-sql.741852.titanic_train_test` as
(
select
'train' as dataset,
t1.*
from `kaggle-titanic-sql.741852.titanic_train` t1
union all
select
'test' as dataset,
t2.passengerid, null as passengerid, t2.pclass,
t2.name, t2.sex, t2.age, t2.sibsp,
t2.parch, t2.ticket, t2.fare,
t2.cabin, t2.embarked
from `kaggle-titanic-sql.741852.titanic_test` t2
)Помимо пропусков в признаках "Embarked", "Cabin", "Age", которые имелись в обучающей выборки, в тестовой выборке еще имеется 0,1% пропусков в признаке "Fare" (учтем это при заполнении). В признаке "Survived" появилось 31,9%, поскольку тренировочные данные не имеют данного признака.
2. one_family / mix_group / alone
Я буду предполагать, что пассажиры имеющие один и тот же билет - путешествующих вместе. Так же я предполагаю, что если в билете указан единственный пассажир - он путешествует один, не принимая во внимание тот факт, что другие члены семьи могут путешествовать по другим билетам.
Эти предположения требуют компромисса, но будем считать, что так оно и есть. Цель этих предположений - обобщить население корабля.
Если в данном билете имеется одна фамилия и несколько пассажиров - считаем это одной семьей. Если пассажир был одним из членов семьи данного билета, этот флаг помечается как 1 - истина и 0 - если ложь, признак "one_family".
Если в данном билете имеется несколько фамилий и несколько пассажиров, это помечается как смешанная группа "mix_family". Всего имеется 67 билетов с двумя и более фамилиями.
Если в данном билете имеется одна фамилия и один пассажир, это помечается как пассажир, путешествующий в одиночку "alone". Подавляющее большинство билетов приходилось именно на пассажиров, путешествующих в одиночку - 713 билета.
select
case when f.surname_count=1 and f.count_passeng=1 then 1 else 0 end as alone,
case when f.surname_count=1 and f.count_passeng>1 then 1 else 0 end as one_family,
case when f.surname_count>1 and f.count_passeng>1 then 1 else 0 end as mix_family,
count(*) as count_ticket
from
(
select
ticket,
count(distinct (split(name, ',')[offset(0)])) as surname_count,
count(*) as count_passeng
from `kaggle-titanic-sql.741852.titanic_train_test`
group by ticket
) f
group by alone, one_family, mix_familyСгруппировав данные по признакам "one_family", "mix_family", "alone" и полу - "Sex", можно увидеть, что пассажиры мужского пола, путешествующие в одиночку, имели наименьшие шансы на выживание. У пассажиров мужчин, путешествующих вместе со своими семьями был самый высокий уровень выживаемости, скорее всего высокий уровень выживаемости связан с наличием детей-мальчиков в этих семьях, которые повышают статистику выживаемости. У женщин, путешествующих со своими семьями был наименьший уровень выживания, среди женщин других групп, возможно это связано с тем, что большинство семей в которых были девушки, располагались в каютах третьего класса (как мы выяснили, именно расположение женщин в каютах третьего класса сильно портят статистику выживаемости у женщин).
select
f1.sex,
f2.one_family,
f2.mix_group,
f2.alone,
round(avg(f1.survived),4) as survival_rate
from `kaggle-titanic-sql.741852.titanic_train` f1
join
(
select
ticket,
case when num_surnames = 1 and num_passengers > 1 then 1 else 0 end as one_family,
case when num_surnames > 1 and num_passengers > 1 then 1 else 0 end as mix_group,
case when num_surnames = 1 and num_passengers = 1 then 1 else 0 end as alone
from
(
select
ticket,
count(passengerid) as num_passengers,
count(distinct split(name, ', ')[offset(0)]) as num_surnames
from `kaggle-titanic-sql.741852.titanic_train_test`
where dataset='train'
group by 1
) t1
) f2
on f1.ticket = f2.ticket
group by 1,2,3,4
order by 1,5 desc;3. family_size
Вторая возможность обобщить население корабля - сгруппировать пассажиров по количеству человек в семьях.
Чтобы определить размер семьи каждого пассажира, логичнее было бы определять их как ["SibSp" + "ParCh" и текущего пассажира (+1)] ("SibSP" - количество братьев и сестер / супругов на борту "Титаника", "ParCh" - количество родителей / детей на борту "Титаника") .Однако вместо того, чтобы определять это на уровне пассажира, мы уже договорились, что предполагаем определять семьи, как путешествующих по одному билету.
В результате сумма "SibSp", "ParCh" и текущего пассажира (+1), которая представляет размер семьи, сопоставляется с номером и фамилией билета. Напишем SQL запрос, в котором, если бы у вас был тот же номер билета и фамилия, тогда размер семьи ("SibSp" + "ParCh" + 1) был сопоставлен с пассажиром.
Посмотрим на среднюю выживаемость по размеру семьи - "family_size" в зависимости от уровня кают - "Pclass". Мы видим, что всё слишком хаотично, не прослеживается какой-либо закономерности, и какого-то определенного вывода, на основе размера семьи - "family_size", сделать не получается. Данный признак, скорее всего, будет оказывать только негативное влияние на качество нашей модели для прогнозирования выживаемости. Тем не менее, мы его добавим и посмотрим, какой результат модели будет лучше: с признаком "family_size" или без него.
select
f.pclass,
f.family_size,
round(100 * sum(f.survived_rate_by_familysize)/count(f.survived_rate_by_familysize), 2) as survived_rate_by_familysize,
count(f.ticket) as unique_ticket
from
(
select
ticket,
pclass,
split(name, ',')[offset(0)] as family_name,
count(sibsp+parch+1) as family_size,
round(sum(survived)/count(survived), 2) as survived_rate_by_familysize
from `kaggle-titanic-sql.741852.titanic_train`
group by 1,2,3
order by ticket
) f
group by 1, 2
order by 1, 2 desc4. Создание обобщенного датасета
Пришло время составить обобщенный датасет на основе всех вышеперечисленных выводов. На этом этапе добавим новые признаки и закодируем существующие для лучшего использования модели.
Список изменений:
Кодирование признака пола - "Sex": female=1, male=0.
Удаление признака "Cabin".
Кодирование признака "Embarked": S=0, C=1, Q=2.
Добавление признаков: "one_family", "mix_family" и "alone".
Добавление признака "avg_fare_per_passenger" путем деления тарифа на количество пассажиров, найденных для каждого билета.
Добавление признака "title_name", с последующей кодировкой: Mr=0, Mrs=1, Miss=2, Master=3.
Добавление признака "family_size" на основе номера билета и фамилии.
drop table if exists `kaggle-titanic-sql.741852.titanic_train_test_v1`;
create table if not exists `kaggle-titanic-sql.741852.titanic_train_test_v1` as
(
select
f1.passengerid,
f1.dataset,
f1.survived,
f1.pclass,
f1.age,
case when f1.sex="female" then 1 else 0 end as sex,
f1.fare,
f1.ticket,
f1.sibsp,
f1.parch,
case
when f1.embarked is not null and f1.embarked = "S" then 0
when f1.embarked is not null and f1.embarked = "C" then 1
when f1.embarked is not null and f1.embarked = "Q" then 2
end as embarked,
case
when split(split(name, ', ')[offset(1)], '.')[offset(0)] in
('Mr','Dr','Rev','Col','Major','Jonkheer','Sir','Don','Capt')
and sex = 'male' then 0
when split(split(name, ', ')[offset(1)], '.')[offset(0)] in
('Mlle', 'Ms', 'Miss')
and sex = 'female' then 2
when split(split(name, ', ')[offset(1)], '.')[offset(0)] in
('Dr', 'the Countess','Mme','Lady','Dona', 'Mrs')
and sex = 'female' then 1
when split(split(name, ', ')[offset(1)], '.')[offset(0)] = 'Master'
and sex = 'male' then 3
else 3
end as title_name,
f2.alone,
f2.one_family,
f2.mix_family,
f3.family_size
f4.avg_fare_per_passenger
from `kaggle-titanic-sql.741852.titanic_train_test` f1
join
(
select
ticket,
case when t1.surname_count=1 and t1.count_passeng=1 then 1 else 0 end as alone,
case when t1.surname_count=1 and t1.count_passeng>1 then 1 else 0 end as one_family,
case when t1.surname_count>1 and t1.count_passeng>1 then 1 else 0 end as mix_family,
from
(
select
ticket,
count(distinct (split(name, ',')[offset(0)])) as surname_count,
count(*) as count_passeng
from `kaggle-titanic-sql.741852.titanic_train_test`
group by ticket
) t1
) f2
on f1.ticket=f2.ticket
join
(
select
ticket,
count(sibsp+parch+1) as family_size,
from `kaggle-titanic-sql.741852.titanic_train_test`
group by 1
) f3
on f1.ticket=f3.ticket
join
(
select
pclass,
round(avg(fare_per_passenger)) as avg_fare_per_passenger
from
(
select
pclass,
ticket,
round(max(fare) / count(passengerid), 2) as fare_per_passenger
from `kaggle-titanic-sql.741852.titanic_train_test`
group by 1, 2)
group by 1
) f4
on f4.pclass=f1.pclass
)Далее перейдем к заполнению пропущенных значений. При использовании SQL лучше создавать несколько таблиц таким образом, потому что это помогает поддерживать порядок и делает SQL более читабельным.
Список изменений:
Заполнение пропусков признака "Embarked". Вспомним, что оба пассажира, для которых имеется пропуск в данном признаке, сели на судно в Саутгемптоне (S) - Англии.
Заполнение пропусков признака "Fare". Во время исследовательского анализа было выяснено, что средний тариф на пассажира был привязан к классу кают - "Pclass". Заполним недостающие значения средней ценой в зависимости от класса каюты
Заполнение признака "avg_fare_per_passenger" недостающими значениями.
Заполнение пропусков признака "Age". Сгруппируем данные по "Pclass", "Sex" и "title_name", чтобы рассчитать средний возраст внутри каждой группы. Заполнение произведем средними значениями, а не медианой, потому что оба набора чисел похожи, и округлим в большую сторону до целых чисел.
Добавление признака "age_grouping". После того как заполнены пропуски признака "Age", можно заменить его признаком "age_grouping", т.к. он дает более обобщенную оценку выживания, c последующим кодированием: "10<age"=0, "10<=age<20"=1, "20<=age<30"=2, "30<=age<40"=3, "40<=age<50"=4, "55<=age"=5,
drop table if exists `kaggle-titanic-sql.741852.titanic_train_test_v2`;
create table if not exists `kaggle-titanic-sql.741852.titanic_train_test_v2` as
(
select
f1.passengerid,
f1.dataset,
f1.survived,
f1.pclass,
f1.sex,
f1.sibsp,
f1.parch,
case when f1.embarked is null then 0 else f1.embarked end as embarked,
f1.title_name,
f1.alone,
f1.one_family,
f1.mix_family,
f1.family_size,
coalesce(f1.fare, f3.fare_mean) as fare,
f2.age,
case
when f2.age < 10 then 0
when f2.age >=10 and f2.age < 20 then 1
when f2.age >= 20 and f2.age < 30 then 2
when f2.age >=30 and f2.age < 40 then 3
when f2.age >=40 and f2.age < 55 then 4
when f2.age >=55 then 5
end as age_grouping,
case
when f1.avg_fare_per_passenger is null and f1.pclass = 1 then 32
when f1.avg_fare_per_passenger is null and f1.pclass = 2 then 12
when f1.avg_fare_per_passenger is null and f1.pclass = 3 then 8
else f1.avg_fare_per_passenger
end as avg_fare_per_passenger
from `kaggle-titanic-sql.741852.titanic_train_test_v1` f1
join
(
select
passengerid,
coalesce(f1.age, f2.avg_age) as age
from `kaggle-titanic-sql.741852.titanic_train_test_v1` f1
join
(
select
sex,
pclass,
title_name,
round(avg(age), 0) as avg_age
from `kaggle-titanic-sql.741852.titanic_train_test_v1` t1
group by sex, pclass, title_name
) f2
on f1.sex=f2.sex
and f1.pclass=f2.pclass
and f1.title_name=f2.title_name
) f2
on f1.passengerid=f2.passengerid
join
(
select
pclass,
round(avg(fare_per_passenger),2) as fare_mean
from
(
select
pclass,
ticket,
round(max(fare) / count(passengerid), 4) as fare_per_passenger
from `kaggle-titanic-sql.741852.titanic_train_test_v1`
group by 1,2
) t1
group by 1
) f3
on f1.pclass = f3.pclass
)Разобьем датасет "kaggle-titanic-sql.741852.titanic_train_test_v2" на обучающую и тестовую выборки:
Обучающий набор данных: "kaggle-titanic-sql.741852.titanic_train_v0"
Тестовый набор данных: "kaggle-titanic-sql.741852.titanic_test_v0"
drop table if exists `kaggle-titanic-sql.741852.titanic_train_v0`;
create table if not exists `kaggle-titanic-sql.741852.titanic_train_v0` as
(
select
f1.survived,
f1.pclass,
f1.sex,
f1.sibsp,
f1.parch,
f1.embarked,
f1.age,
f1.fare,
f1.title_name,
f1.alone,
f1.one_family,
f1.mix_family,
f1.family_size,
f1.age_grouping,
f1.avg_fare_per_passenger
from `kaggle-titanic-sql.741852.titanic_train_test_v2` f1
where dataset = 'train'
);
drop table if exists `kaggle-titanic-sql.741852.titanic_test_v0`;
create table if not exists `kaggle-titanic-sql.741852.titanic_test_v0` as
(
select
f1.pclass,
f1.sex,
f1.sibsp,
f1.parch,
f1.embarked,
f1.title_name,
f1.alone,
f1.one_family,
f1.mix_family,
f1.family_size,
f1.age_grouping,
f1.avg_fare_per_passenger
from `kaggle-titanic-sql.741852.titanic_train_test_v2` f1
where dataset = 'test'
)Обучение моделей
Собрав датасеты, где мы обобщили наши выводы, пришло время для обучения моделей и их оценочных результатов, где мы отберем наиболее важные признаки, настроим гиперпараметры для каждой из моделей и отправим результаты моделей на Kaggle.
1. Подготовка train и test datasets
Используя Pandas, импортируем файлы CSV как фреймы данных. При импорте данных в pandas с BigQuery необходимо установить пакет "pandas-gbq" (pip install pandas-gbq). Признак "passengerid" будем использовать в качестве индексации dataframe.
Обучение моделей начнем с 14 признаков, как показано ниже (X_train.info()), за исключением целевого признака - "survived".
### SQL-запрос на обучающую выборку
query_train = """
select
*
from `kaggle-titanic-sql.741852.titanic_train_v0`
"""
### SQL-запрос на тестовую выборку
query_test = """
select
*
from `kaggle-titanic-sql.741852.titanic_test_v0`
"""from google.cloud import bigquery
import pandas as pd
### Датафреймы обучающей и тестовой выборок
train_data = pd.read_gbq(query_train, project_id='kaggle-titanic-sql', index_col="passengerid")
test_data = pd.read_gbq(query_test, project_id='kaggle-titanic-sql', index_col="passengerid")
### Подготовка данных, отделим целевой признак
y_train = train_data['survived'].copy()
X_train = train_data.drop(["survived"], axis=1).copy()
X_test = test_data.copy()2. Матрица коэффициентов корреляции
В машинном обучении, если две или более переменных тесно связаны между собой (высоко коррелированы), производительность некоторых алгоритмов может ухудшиться за счет снижения производительности обобщения данных. И лучше всего избавляться от сильно коррелированных признаков.
Создадим матрицу попарной корреляции, используя функцию corr(), встроенную в Pandas и посмотрим на тепловую карту корреляции с помощью Seaborn. Функция corr() вычисляет коэффициенты корреляции Пирсона в качестве метода по умолчанию.
Используем в качестве порога корреляции Пирсона абсолютные значения в диапазоне от 0,8 до 1,0 (этот порог корреляции произволен). Как мы и предполагали, пара признаков "pclass" и "avg_fare_per_passenger" сильно коррелированы, так же как "age" и "age_grouping".
import seaborn as sns
import matplotlib.pyplot as plt
import numpy as np
### Матрица корреляций
plt.figure(figsize=[10,8])
sns.heatmap(X_train.corr(), annot=True, mask=np.triu(X_train.corr()))
def corr_treshold(data, treshold):
"""Функция для вывода датафрейма коррелирующих признаков
с задаваемым порогом корреляции - treshold"""
corr_data = data.corr().abs().unstack()
corr_data = corr_data[corr_data >= treshold]
corr_data = corr_data[corr_data < 1]
corr_df = pd.DataFrame(corr_data).reset_index()
corr_df.columns = ['feature1', 'feature2', 'corr']
corr_df = corr_df.drop_duplicates('corr')
return corr_dfВыявив пары с высокой степенью корреляции, логичнее было бы избавиться сразу от одной из пар, но мы оставим эту задачу для наших моделей машинного обучения, которые сами решат, какие признаки для них наиболее важны (сделаем это позже).
3. Обучение моделей. Baseline
Будем использовать 5 моделей машинного обучения из пакета "sklearn": метод ближайших соседей, метод опорных векторов, решающее дерево, случайный лес и логистическую регрессию. Метрикой качества модели, на Kaggle, нам предлагают выбрать "accuracy" - долю верных ответов. Оценивать качество модели будем на 10-ти кратной кросс-валидации. Так же, перед обучением масштабируем наши признаки с использованием класса "StandarScaler", т.к. линейные алгоритмы машинного обучения устроены таким образом, что признаки с меньшей амплитудой оказываются "оштрафованы" по сравнению с признаками с большей амплитудой, и оказывают меньшее влияние на процесс построения модели.
Сильно выделяющихся моделей на фоне других не виднеется, но решающее дерево показало наихудший результат - 63%, в то время как остальные модели показали оценку не меньше 70%. Так же, у решающего дерево, среди других моделей, самое высокое стандартное отклонение на кросс-валидации, а высокое стандартное отклонение указывает на то, что модель не может хорошо обобщаться с новыми данными.
from sklearn.linear_model import LinearRegression
from sklearn.neighbors import KNeighborsClassifier
from sklearn.tree import DecisionTreeClassifier
from sklearn.ensemble import RandomForestClassifier
from sklearn.linear_model import LogisticRegression
from sklearn.svm import SVC
from sklearn.model_selection import GridSearchCV
from sklearn.model_selection import cross_val_score
from sklearn.preprocessing import StandardScaler
### Создадим экземпляр класса StandardScaler
sc = StandardScaler()
### Обучим StandardScaler на обучающей выборке и приведем их к pandas DataFrame
### Трансормируем данные обучающей выборки и приведем их к pandas DataFrame
X_train = pd.DataFrame(sc.fit_transform(X_train),\
columns=train_data.drop(["survived"], axis=1).columns,\
index=train_data.index)
X_test = pd.DataFrame(sc.transform(X_test), columns=test_data.columns,\
index=test_data.index)
### Словарь используемых моделей. Гиперпараметры моделей стоят по умолчанию
models_dict = {
"KNN": KNeighborsClassifier(),
"tree": DecisionTreeClassifier(random_state=42),
"RF": RandomForestClassifier(random_state=42),
"Lg":LogisticRegression(solver='liblinear', random_state=42),
"SVC": SVC(random_state=42)
}
def to_df(data):
"""Функция для преобразования результатов моделей в датафрейм"""
column = ['mean_score', 'std', 'best_score']
score_df = pd.DataFrame.from_dict(data, columns=column, orient='index')
return score_df
def model_scores(model_dict, X_train, y_train):
"""Функция для обучения исходных данных на кросс-валидации"""
### Словарь для записи результатов моделей
model_scores = {}
### Обучение моделей на кросс-валидации и запись результатов в словарь
for name_model, model in models_dict.items():
score_list = []
score = cross_val_score(model, X_train, y_train, cv=10, scoring='accuracy')
### Среднее значение оценки на кросс-валидации
score_list.append(score.mean())
### Стандартное отклонение на кросс-валидации
score_list.append(score.std())
### Лучший результат на кросс-валидации
score_list.append(score.max())
model_scores[name_model+'_v0'] = score_list
score_df_v0 = to_df(model_scores)
return score_df_v04. Отбор признаков (Features Selection)
Попробуем улучшить качество наших моделей, путём отбора наиболее важных признаков для каждой из модели.
Для отбора признаков будем использовать один из "wrapper methods", в пакете "MLextend".
Особенности "wrapper methods":
Используют модели машинного обучения для отбора признаков.
Формируют подмножества признаков.
Для каждого подмножества признаков строится отдельная модель машинного обучения.
Для конкретной модели машинного обучения (на которой производится оценка) чаще всего генерируют оптимальный набор признаков. (Но этот набор не обобщается на все модели.)
Очень затратны с точки зрения вычислительных ресурсов.
Мы будем использовать прямой алгоритм - "Sequential Future Selector" (с параметром конструктора forward=True).
Суть прямых алгоритмов "wrapper methods":
На первом шаге для каждого признака
принадлежащего множеству признаков
строятся модели машинного обучения (на основе одного признака). Оценивается качество всех моделей на основе заданной метрики, выбирается лучшая модель, содержащая один признак 
На втором шаге к признаку
по очереди добавляются оставшиеся признаки 
принадлежащие множеству признаков 
, и строятся модели машинного обучения (на основе двух признаков). Оценивается качество всех моделей на основе заданной метрики, выбирается лучшая модель из двух признаков.Процедура итеративно повторяется для 3, 4, ... признаков.
Критерии остановки могут быть различными:
1. Улучшение метрики качества меньше заданной величины
.2. Выбран
лучших признаков
У "MLextend" очень хорошая и понятная документация. Для обучения алгоритма "Sequential Future Selector", будем использовать open source код, который предоставлен в библиотеке "MLextend".
Обучим модели машинного обучения на алгоритме "Sequential Future Selector" и посмотрим на оценку моделей и лучшую комбинацию признаков для каждой из модели.
Каждая из моделей отобрала для себя различные наборы признаков. Можно отметить, что как мы и предполагали, решающими признаками для моделей стали пол - "sex" и класс каюты - "pclass" пассажира.
Обучение заняло 2 минуты и 7 секунд. После отбора наиболее важных признаков, модели получили прирост к оценке. Так же стали меньше стандартные отклонения на кросс-валидации, что говорит о том, что наши модели стали устойчивее к новым данным. Стоит отметить решающее дерево, теперь оно выделяется на фоне остальных в "другом свете", получив наибольший прирост к средней оценке и стандартному отклонению. Возможно это связано с переобучением модели, т.к. решающие деревья склонны к переобучению, но это мы не будем проверять и говорить об этом, т.к. целью данной работы не является разбор алгоритмов машинного обучения.
def future_selector(models_dict, X_train, y_train):
"""Функция для отбора наилучшей комбинации признаков для моделей"""
### Словарь для записи лучших комбинаций признаков
best_features = {}
### Словарь для записи результатов моделей
model_scores = {}
### Обучение моделей на алгоритме SFS и запись результатов в словари
for name_model, model in models_dict.items():
score_list = []
### Экземляр алгоритма SFS
sfs = SFS(model,
k_features=(3, 14),
forward=True,
floating=False,
scoring='accuracy',
cv=5)
sfs = sfs.fit(X_train, y_train)
### Среднее значение оценки на кросс-валидации
score_list.append(sfs.subsets_[len(sfs.k_feature_names_)]['cv_scores'].mean())
### Стандартное отклонение на кросс-валидации
score_list.append(sfs.subsets_[len(sfs.k_feature_names_)]['cv_scores'].std())
### Лучший результат на кросс-валидации
score_list.append(sfs.subsets_[len(sfs.k_feature_names_)]['cv_scores'].max())
model_scores[name_model+'_v1'] = score_list
best_features[name_model] = sfs.k_feature_names_
print('model {}, score (accuracy: {}):'.format(name_model, sfs.k_score_))
print('selected features: {}\n'.format(sfs.k_feature_names_))
score_df_v1 = to_df(model_scores)
### Вернуть датафрейм результатов оценок моделей и словарь лучших комбинаций признаков
return score_df_v1, best_features5. GridSearchCV
Последним нашим шагом в оптимизации моделей будет подбор гиперпараметров.
GridSearchCV – это очень мощный инструмент для автоматического подбирания параметров для моделей машинного обучения. GridSearchCV находит наилучшие параметры, путем обычного перебора: он создает модель для каждой возможной комбинации параметров и показывает лучшую комбинацию параметров, вызовом метода best_params_
Обучим модели машинного обучения, в диапазоне выбранных параметров и выберем лучшую комбинацию параметров для итогового результата.
Обучение заняло 1 минуту. В целом, в данном примере, особых улучшений не наблюдается. В любом случае настройка правильного набора гиперпараметров и охват широкого диапазона настроек может улучшить предсказания модели. Настройка гиперпараметров для моделей машинного обучения является важной частью рабочего процесса машинного обучения.
X_train_dict = {}
X_test_dict = {}
### Оставляем только те признаки, которые выбрала каждая модель
### И записываем данные в словарь для каждой модели
for name_model, model in models_dict.items():
X_train_dict[name_model] = X_train[list(best_features[name_model])]
X_test_dict[name_model] = X_test[list(best_features[name_model])]
### Словарь для перебора гиперпараметров
params_dict = {
"KNN": {"n_neighbors": np.arange(2,10),\
"weights": ["uniform", "distance"]},
"tree": {"max_depth": np.arange(2,8), "criterion": ["gini", "entropy"],\
"min_samples_split": np.arange(2,4),\
"min_samples_leaf": np.arange(1,4)},
"RF": {"n_estimators": np.arange(100,200,50), "max_depth": np.arange(2,6),\
"min_samples_split": np.arange(2,7),
"min_samples_leaf": np.arange(2,5)},
"Lg": {"max_iter": [100,200], "C": [0.01,0.05,0.1,0.2,0.5,1,1.5,2]},
"SVC": {"C": [0.001,0.005,0.01,0.05,0.1,0.4,0.8,1],\
"kernel": ["linear", "poly", "rbf"]}
}
def gridsearchcv(models_dict, params_dict, X_train_dict, y_train):
"""Функция для подбора гиперпараметров"""
### Словарь для записи лучших параметров моделей
best_params_dict = {}
### Словарь для записи результатов моделей
model_scores = {}
### Обучение моделей на GridSearchCV и запись результатов в словари
for name_model, model in models_dict.items():
score_list = []
print(name_model+": beginning")
### Из словаря данных, выбираем только те, которые принадлежат данной модели
X_train = X_train_dict[name_model]
### Из словаря параметров, выбираем только те, которые принадлежат данной модели
params = params_dict[name_model]
gscv = GridSearchCV(model, params, cv=5, n_jobs=-1, scoring='accuracy')
gscv.fit(X_train,y_train)
### Среднее значение оценки на кросс-валидации
score_list.append(gscv.cv_results_['mean_test_score'].mean())
### Стандартное отклонение на кросс-валидации
score_list.append(gscv.cv_results_['std_test_score'].mean())
### Лучший результат на кросс-валидации
score_list.append(gscv.best_score_)
model_scores[name_model+"_v2"] = score_list
best_params = gscv.best_params_
best_params_dict[name_model] = best_params
score_df_v2 = to_df(model_scores)
### Вернуть датафрейм результатов оценок моделей
### И словарь лучших комбинаций параметров
return score_df_v2, best_params_dict6. Результаты на Kaggle
В качестве последнего шага обучим наши модели со всеми оптимизациями, которые были проведены ранее. Создадим файлы с результатами предсказаний моделей и отправим результаты на Kaggle для получения оценочных результатов.
Лучшей моделью в итоге оказалось решающее дерево, которое заняло 2061 место среди 14553 в таблице лидеров, c результатом 78,468%.
### Словарь моделей, с установленными гиперпараметрами
models_dict = {
"KNN": KNeighborsClassifier(n_neighbors=6, weights="uniform"),
"tree": DecisionTreeClassifier(criterion="gini", max_depth=7,\
min_samples_leaf=2, min_samples_split=2,\
random_state=42),
"RF": RandomForestClassifier(n_estimators=10, max_depth=5,\
min_samples_leaf=2, min_samples_split=5,\
random_state=42),
"Lg":LogisticRegression(C=1.0, max_iter=100, solver="liblinear",\
random_state=42),
"SVC": SVC(C=0.005, kernel="linear", random_state=42)
}
### Обучение моделей и создание файлов с предиктами для отправки на Kaggle
for name_model, model in models_dict.items():
model.fit(X_train_dict[name_model], y_train)
predicted = model.predict(X_test_dict[name_model])
output = pd.DataFrame({'PassengerId': X_test.index, 'Survived': predicted})
output.to_csv('kaggle_predict_{}.csv'.format(name_model), index=False)Заключение
В данной статье был проведен исследовательский анализ и разработка новых признаков, используя исключительно SQL. Были использованы: GROUP BY, оконные функции, агрегатные функции, подзапросы, предложение HAVING и другие методы SQL. Для тестирования различных моделей машинного обучения, понимания важности признаков и настройки выбранной модели с помощью SequentialFutureSelector и GridSearchCV, был использован Python. Наконец, я отправил свои прогнозы в Kaggle и посмотрел оценочные результаты.
