خواندن ۳۵ دقیقه·۳ سال پیش

Titanic Competition in SQL

هیچ چیز قدرتمندتر از یادگیری چیزی جدید یا رساندن یک مهارت به سطوح بالاتر با استفاده از روش های ساده نیست. در این مقاله، من از SQL (Postgres) برای انجام تجزیه و تحلیل اکتشافی خود و ایجاد یک مجموعه آموزشی (training set) ویژگی تبدیل شده برای مدل یادگیری ماشین خود استفاده خواهم کرد.

اگرچه پایتون یا R زبان واقعی فعلی برای علم داده و یادگیری ماشین است، اما داشتن یک پایه قوی در SQL، گاهی اوقات به شما کمک می‌کند داده‌های خود را سریع کاوش کنید. بیشتر داده هایی که با آنها کار می کنیم در یک پایگاه داده رابطه ای زندگی می کنند. داشتن توانایی نوشتن کوئری های SQL به صورت روان به شما این امکان را می دهد که به راحتی با داده ها روبه رو شوید و به سرعت تجزیه و تحلیل خود را شروع کنید.

هدف

اگرچه پایتون رویکرد ترجیحی من خواهد بود، اما می‌خواستم ببینم آیا می‌توانم تمام تحلیل‌های اکتشافی و مهندسی ویژگی‌های خود را در SQL انجام دهم؟! هدف من این است که نشان دهم SQL چقدر می تواند برای گردش کار فرد قدرتمند و مکمل باشد. اگرچه یکی از محدودیت‌های آشکار SQL، توانایی مصورسازی داده‌ها است، اما همچنان می‌توانید با نوشتن کوئری ها و خروجی نتایج، دستاوردهای بزرگی به دست آورید. بیا شروع کنیم!

اکتشاف

تحقیقات من در مورد غرق شدن کشتی تایتانیک نشان داد که زنان و کودکان در انتقال مسافران به قایق های نجات نسبت به دیگران اولویت دارند. این مفهوم نقش مهمی در نحوه گروه بندی و تجزیه و تحلیل مجموعه داده های Kaggle خواهد داشت.

علاوه بر این، بحث هایی در مورد اینکه آیا مجموعه داده های train و test را جدا نگه دارید یا آنها را برای مهندسی ویژگی و انتخاب ترکیب کنید، وجود دارد. در بیشتر موارد، اولی برای جلوگیری از نشت داده ها (data leakage) معنی دار خواهد بود.

روش معمول این است که مجموعه داده آزمایشی را کنار بگذاریم، آن را فراموش کنیم و فقط از مجموعه داده آموزشی برای impute و مهندسی ویژگی ها (engineer features) استفاده کنیم. برای این تمرین، تصمیم گرفتم train و test را ترکیب کنم تا داده های بیشتری برای پیش بینی میزان زنده ماندن داشته باشم. این یک جهان ثابت است (fixed universe) و ما انتظار نداریم داده های جدیدی به pipeline پیش بینی وارد شود.

برای شروع، فایل های train.csv و test.csv را از Kaggle دانلود کردم و فایل ها را به دو جدولی که در پایگاه داده Postgres ایجاد کرده بودم، وارد کردم. بعد، من این دو جدول را با هم ترکیب کردم تا اولین جدول کاری خود را ایجاد کنم (titanic_train_test_raw).

Titanic-MachineLearningfromDisaster|Kaggle

برای درج رکوردها در جداول خود، psql را در ترمینال خود با تایپ کردن "psql postgres" باز کردم. این ابزار خط فرمان PostgreSQL است. من دو دستور زیر را برای قرار دادن فایل‌های CSV در جداول خود اجرا کردم.

\copy titanic_train_raw from ‘file/path/train.csv’ delimiter ‘,’ csv HEADER;
\copy titanic_test_raw from ‘file/path/test.csv’ delimiter ‘,’ csv HEADER;

برای درک بهتر داده ها، Survival_rate را برای هر ویژگی موجود در مجموعه train محاسبه کردم. من کل خروجی را در اینجا به اشتراک نمی گذارم زیرا آنها را قسمت به قسمت در طول مقاله به اشتراک خواهم گذاشت.

کد :

with titanic_survival_by_feature_high_level as
(
  select
    feature,
    feature_value,
    survival_rate,
    survived_passengers,
    total_passengers
  from
  (
    select
      'pclass' as feature, 
      cast(pclass as varchar) as feature_value,
      round(avg(survived),4) as survival_rate,
      sum(survived) as survived_passengers, count(*) as total_passengers
    from titanic_train_raw
    group by 1,2
      union
    select
      'name', 
      split_part(split_part(name, ', ', 2), '.', 1), 
      round(avg(survived),4) as survival_rate,
      sum(survived) as num_survived, count(*) as total_passengers
    from titanic_train_raw
    group by 1,2
      union
    select
      'sex', 
      sex, 
     round(avg(survived),4) as survival_rate,
      sum(survived) as num_survived, count(*) as total_passengers
    from titanic_train_raw
    group by 1,2
      union
    select
      'age', 
      cast(age_bucket as varchar) as age_bucket, 
      round(avg(survived),4) as survival_rate,
      sum(survived) as num_survived, count(*) as total_passengers
    from (select ntile(6) over (order by age) as age_bucket, tr.* from titanic_train_raw tr) ag
    group by 1,2
      union
    select
      'sibsp', 
      cast(sibsp as varchar) as sibsp, 
      round(avg(survived),4) as survival_rate,
      sum(survived) as num_survived, count(*) as total_passengers
    from titanic_train_raw
    group by 1,2
      union
    select
      'parch', 
      cast(parch as varchar) as parch, 
      round(avg(survived),4) as survival_rate,
      sum(survived) as num_survived, count(*) as total_passengers
    from titanic_train_raw
    group by 1,2
      union
    select
      'fare', 
      cast(fare_bucket as varchar) as fare_bucket, 
      round(avg(survived),4) as survival_rate,
      sum(survived) as num_survived, count(*) as total_passengers
    from (select ntile(6) over (order by fare) as fare_bucket, tr.* from titanic_train_raw tr) fr
    group by 1,2
      union
    select
      'cabin', 
      substring(cabin,1,1) as cabin, 
      round(avg(survived),4) as survival_rate,
      sum(survived) as num_survived, count(*) as total_passengers
    from titanic_train_raw
    group by 1,2
      union
    select
      'embarked', 
      coalesce(embarked,'S') as embarked, 
      round(avg(survived),4) as survival_rate,
      sum(survived) as num_survived, count(*) as total_passengers
    from titanic_train_raw
    group by 1,2
  ) t1
)
select *
from titanic_survival_by_feature_high_level
order by 1,2,3 desc;

خروجی :

1. مقادیر از دست رفته یا Missing values

بیایید نگاهی دقیق‌تر به مقادیر از دست رفته بیندازیم و ببینیم چه feature هایی بخاطر پراکندگی نیاز به تخمین یا حتی کاهش دارند. محاسبات missing value ها با داده های ترکیبی train و test انجام می شود. از آنجایی که داده های test ویژگی هدف Survived را ندارند، ستون ترکیبی Survived، مقدار 31.93 درصد از مقادیر آن را از دست داده است. همچنین ویژگی های Age، Cabin، Fare و Embarked به ترتیب 20.09، 77.46، 0.08 و 0.15 درصد گم شده اند.

کد:

select
  round(1.0 * sum(case when f1.passengerid is null then 1 else 0 end) / max(f1.total_passengers), 4) as passengerid,
  round(1.0 * sum(case when f1.survived is null then 1 else 0 end) / max(f1.total_passengers), 4) as survived,
  round(1.0 * sum(case when f1.pclass is null then 1 else 0 end) / max(f1.total_passengers), 4) as pclass,
  round(1.0 * sum(case when f1.name is null then 1 else 0 end) / max(f1.total_passengers), 4) as name,
  round(1.0 * sum(case when f1.sex is null then 1 else 0 end) / max(f1.total_passengers), 4) as sex,
  round(1.0 * sum(case when f1.age is null then 1 else 0 end) / max(f1.total_passengers), 4) as age,
  round(1.0 * sum(case when f1.sibsp is null then 1 else 0 end) / max(f1.total_passengers), 4) as sibsp,
  round(1.0 * sum(case when f1.parch is null then 1 else 0 end) / max(f1.total_passengers), 4) as parch,
  round(1.0 * sum(case when f1.ticket is null then 1 else 0 end) / max(f1.total_passengers), 4) as ticket,
  round(1.0 * sum(case when f1.fare is null then 1 else 0 end) / max(f1.total_passengers), 4) as fare,
  round(1.0 * sum(case when f1.cabin is null then 1 else 0 end) / max(f1.total_passengers), 4) as cabin,
  round(1.0 * sum(case when f1.embarked is null then 1 else 0 end) / max(f1.total_passengers), 4) as embarked
from
(
	select
	  count(*) over () as total_passengers,
	  t1.*
	from titanic_train_test_raw t1
) f1;

2. میزان نجات یافتگان به صورت کلی

از training data، من درصد زنده مانده ها در مقابل درصد فوت شده ها را محاسبه کردم. از 891 مسافر در training data، تنها 38.38 درصد از آنها زنده مانده اند. همچنین، این تجزیه و تحلیل به من اجازه داد تا ببینم که کلاس هدف (target class) خیلی نامتوازن نباشد- تقریباً یک 60٪ به 40٪ است.

کد:

select
  case when f1.survived = 1 then 'Survived' else 'Not Survived' end as survival_status,
  round(1.0 * count(*) / max(f1.total_passengers), 4) as survival_rate,
  count(*) as num_of_passengers,
  max(f1.total_passengers) as total_passengers
from
(
	select
	  count(*) over () as total_passengers,
	  t1.*
	from titanic_train_raw t1
) f1
group by 1;

3. میزان زنده ماندن بر اساس جنسیت و سن

مسافران زن در مقایسه با مسافران مرد بیشترین احتمال زنده ماندن را داشتند. تنها بر اساس جنسیت، training data به ما می گوید که میزان زنده ماندن برای زنان تقریباً 74.2٪ و برای مردان 18.89٪ است.

با استفاده از SQL خالص، 10 عدد bin یا (age_cohort) برای گروه بندی سنین مختلف ایجاد کردم. من به هیچ گونه تقسیم بندی دقیقی از گروه های سنی در نظر نخواهم داشت، بنابراین تعداد سطل ها یا bin ها دلخواه است. در حالی که نرخ زنده ماندن زنان در تمام گروه‌های سنی بالاتر از مردان بوده است، جنسیت مذکر زیر 9 سال سن، نرخ زنده ماندنشان تقریباً 60٪ می باشد. براساس نتایجی که قبلا بدست آمد، تنها بر اساس جنسیت، میزان زنده ماندن مردان کمتر از 20 درصد بود، اما با برش داده‌ها براساس گروه‌های سنی می‌بینیم که یک زیر گروه از مردان نرخ زنده ماندن نسبتاً بالاتری داشتند. (کودکان)

کد:

-- survival based on sex
select
  t1.sex,
  round(1.0 * sum(survived) / max(gender_cnt), 4) as survival_rate,
  sum(survived) as survived_passengers,
  max(gender_cnt) as total_passengers
from
(
	select
	  passengerid,
	  count(*) over (partition by sex) as gender_cnt,
	  sex,
	  survived
	from titanic_train_test_raw
	where cohort = 'train'
) t1
where t1.survived = 1
group by 1;



-- survival based on sex and age cohorts (10 bins)
select
  t1.sex,
  t1.age_cohort,
  min(age) as age_min,
  max(age) as age_max,
  count(*) as num_survived,
  max(age_sex_cnt) as num_age_cohort,
  round(1.0 * count(*) / max(age_sex_cnt), 4) as survival_rate
from
(
	select
	  passengerid,
	  age,
	  count(*) over (partition by sex, floor(1.0 * age / 10)) as age_sex_cnt,
	  sex,
	  floor(1.0 * age / 10) as age_cohort,
	  survived
	from titanic_train_test_raw
	where cohort = 'train'
) t1
where t1.survived = 1
group by 1,2
order by 1,2;

4. میزان زنده ماندن بر اساس مبلغ کرایه (fare) و میانگین آن به ازای هر مسافر

برای اینکه سریعاً ارتباط کرایه با میزان زنده ماندن را محاسبه کنم، از تابع پنجره ای ntile استفاده کردم تا مسافران را به طور مساوی در 6 بین (bin) قرار دهم و آماری را برای بررسی دقیق تر ایجاد کنم.

در یک نگاه، به نظر می رسد survival_rate با گران شدن کرایه ها افزایش می یابد. در همان زمان، fare_mean و fare_stddev به طور چشمگیری از بین (bin) 5 به 6 پرش می کنند. بالاترین قیمت fare_max مقدار 512.3292 دلار است، در حالی که کمترین fare_min مقدار 0 دلار است. مقداری نویز در ستون کرایه وجود دارد. آنچه مستمر وجود دارد این است که زنده ماندن زنان بسیار بیشتر از مردان است. بر اساس ویژگی fare_grouping، میزان زنده ماندن زنان 2 تا 9 برابر بیشتر از مردان است (survival_ratio).

متوجه شدم که کرایه نشان دهنده هزینه کل بلیط است و نه کرایه هر مسافر. به عنوان مثال، بلیط PC 17755، چهار مسافر وجود دارد. Cardeza یک خانواده ثروتمند است که با دو کارمند خود، Miss Ward و Mr. Lesurer، در first class سفر می کنند.

برای اینکه بفهمم هزینه کرایه چقدر است، مبلغ کرایه را بر تعداد کل مسافران هر بلیط تقسیم کردم و میانگین ها را بر اساس Pclass گروه بندی کردم. هنگام بررسی ویژگی بلیط، خانواده ها، گروه های مختلط و افرادی را دیدم که به بلیط های فردی متصل شده بودند. در نتیجه، به جای استفاده از [SibSp (siblings & spouses) + ParCh (parents & children) + 1 (PassengerId)]، که معمولاً توسط سایر افراد Kaggle برای محاسبه ویژگی Family_size استفاده می‌شود، با استفاده از تعداد مسافران در هر بلیط، انحراف معیار کمتری ایجاد کنم و به من اطمینان داد که میانگین کرایه نسبتا دقیق تری برای هر مسافر فراهم می کند.

به طور متوسط، کرایه first class برای هر مسافر حدود 32 دلار، کرایه second class برای هر مسافر حدود 12 دلار و third class حدود 8 دلار است. این میانگین مقدار کمی از نویز موجود در ویژگی اصلی کرایه را حذف کرد. fare_mean به نظر می رسد که ارتباط زیادی با Pclass دارد. بنابراین، داشتن هر دو ویژگی، Pclass و fare_per_passenger، ممکن است اضافی باشد. بعداً، ممکن است بخواهیم یکی از این موارد را در طول ساخت مدل حذف کنیم.

کد 1:

-- distribution of fares and survival based on arbitray fare groupings
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(stddev(fare),2) as fare_stddev,
  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,
  round(round(avg(case when sex = 'female' then survived else null end),4) / round(avg(case when sex = 'male' then survived else null end),4), 2) as survival_ratio
from
(
	select
	  passengerid,
	  pclass,
	  sex,
	  fare,
	  survived,
	  ntile(6) over (order by fare) as fare_grouping
	from titanic_train_test_raw
	where cohort = 'train'
) t1
group by 1
order by 1;

کد 2:

-- this query shows that passenger_cnt per ticket does not always equal family_size_cnt from sibsp+parch+1
select
  ticket,
  max(fare) as fare,
  count(passengerid) as passenger_cnt,
  max(sibsp + parch + 1) as family_size_cnt
from titanic_train_test_raw
group by 1
having count(passengerid) != max(sibsp + parch + 1);

کد 3:

-- calculate fare_per_passenger and average fare_per_passenger per class
-- pclass is already grouping the fares into groups. Most likely redundant to have both features.
select
  pclass,
  round(avg(fare_per_passenger),2) as fare_mean,
  round(stddev(fare_per_passenger),2) as fare_stddev,
  percentile_disc(0.5) within group (order by fare_per_passenger) as fare_median_disc,
  min(fare_per_passenger) as fare_min,
  max(fare_per_passenger) as fare_max
from
(
	select
	  pclass,
	  ticket,
	  max(fare) as fare_total,
	  count(passengerid) as ticket_passenger_cnt, -- used as denominator b/c more reliable when count(passengerid) != max(sibsp + parch + 1)
	  max(sibsp + parch + 1) as family_size_cnt,
	  round(max(fare) / count(passengerid), 4) as fare_per_passenger
 	from titanic_train_test_raw
	group by 1,2
) t1
group by 1
order by 1;

5. میزان زنده ماندن بر اساس Title از ویژگی نام اصلی

17 عنوان منحصر به فرد وجود دارد و برخی از آنها نرخ زنده ماندن بالایی دارند. در training data فقط دو مسافر با عنوان Major و یک مسافر با Sir وجود دارد. برای اینکه هر گروه بندی برای مدل سازی مرتبط و تاثیرگذار باشد، عناوین نادر را به چهار عناون اضافه کردم - Mr, Mrs, Miss, and Master - و بنابراین یک ویژگی title_grouping ایجاد کردم. به عنوان مثال، Mlle، که مخفف Mademoiselle (دختر خانم) است، به Miss.

عنوان Master جالب است زیرا نرخ زنده ماندن نسبتاً بالایی دارد و تعداد کمی از آنها در training data وجود دارد. در طی این دوره، به پسران تا رسیدن به سن بلوغ این لقب داده می شد. اکنون ما راهی برای شناسایی پسران با استفاده از title_grouping داریم در حالی که همه دختران و زنان در Mrs یا Miss جمع شده اند.

من میزان زنده ماندن را برای هر title_grouping محاسبه کردم و اعداد بسیار گویای هستند. با جدا کردن پسران از گروه مردان، میزان زنده ماندن مردان کاهش می یابد و زنده ماندن پسران نزدیک به 60٪ است. من این ویژگی را در طول کار آماده سازی training data نهایی در مقادیر عددی رمزگذاری خواهم کرد.

-- Boys are with Master title and girls have Miss titles which are grouped into Miss title grouping.
select
  case when split_part(split_part(name,'.',1),', ',2) in ('Dr','Rev','Col','Major','Jonkheer','Sir','Don','Capt') and sex = 'male' then 'Mr'
       when split_part(split_part(name,'.',1),', ',2) in ('Mlle') then 'Miss'
       when split_part(split_part(name,'.',1),', ',2) in ('Dr','Ms','the Countess','Mme','Lady','Dona') and sex = 'female' then 'Mrs'
       else split_part(split_part(name,'.',1),', ',2)
  end as title_grouping,
  round(avg(survived),4) as survival_rate,
  count(*)
from titanic_train_test_raw
where cohort = 'train'
group by 1
order by 2 desc;

6. میزان زنده ماندن بر اساس زن یا فرزند بودن

با مقاله Chris Deotte’s در مورد ویژگی‌ای که او ایجاد کرد به نام «woman-child-groups» بر اساس گروه‌بندی مسافران با نام خانوادگی (surnames) و القاب (titles)، برخوردم. با در نظر گرفتن این موضوع، یک ویژگی باینری ایجاد کردم که هر مسافر می تواند woman یا boy باشد. is_woman_child = 1 یعنی همه زنان و کودکان را میگیرد در حالی که is_woman_child = 0 یعنی همه مردان بالغ را می گیرد.

کد :

-- is_woman_child flag >> this grabs adult women, girls and boys where boys had hire chance of survival than adult men
select
  case when title_grouping in ('Mrs','Miss','Master') then 1 else 0 end as is_woman_child,
  round(avg(survived),4) as survival_rate
from
(
	select
	  case when split_part(split_part(name,'.',1),', ',2) in 
'Dr','Rev','Col','Major','Jonkheer','Sir','Don','Capt') and sex = 'male' then 'Mr'
               when split_part(split_part(name,'.',1),', ',2) in ('Mlle') then 'Miss'
  	       when split_part(split_part(name,'.',1),', ',2) in ('Dr','Ms','the Countess','Mme','Lady','Dona') and sex = 'female' then 'Mrs'
  	       else split_part(split_part(name,'.',1),', ',2)
          end as title_grouping,
	  t1.*
	from titanic_train_test_raw t1
	where cohort = 'train'
) f1
group by 1;

7. نرخ زنده ماندن بر اساس سطح کابین

برخی از بررسی هایی که برای این کار انجام دادم نشان داد که اکثریت مسافران third class در سطوح کابین F و G قرار داشتند. با این کار، منطقی بود که سطوح کابین را با دقت بیشتری بررسی کنیم، اگرچه 77٪ از مقادیر وجود نداشت. من فرض کردم که سطوح پایین کابین، شانس زنده ماندن مسافر را کاهش می دهد.

پس از انجام برخی تجزیه و تحلیل ها، زمانی که مسافران بخشی از کلاس پایین تر و سطوح کابین پایین تر هستند، میزان زنده ماندن کاهش می یابد.

همانطور که در زیر نشان داده شده است، ستون embarked به تنهایی کاملاً میزان زنده ماندن را برایمان تعریف نمی کند، اما من معتقدم که می توان از آن برای ایجاد ویژگی های جدیدی استفاده کرد که قدرت پیش بینی نسبتاً بالاتری دارند.

به عنوان یادداشت جانبی، دو مسافر با مقادیر Embarked گم شده وجود دارد، اما من این مسافران را جستجو کردم و هر دو در ساوت همپتون (S) انگلستان سوار کشتی شدند.

من فرض کردم که ارتباطی بین مکان سوار شدن (embarked) و عرشه یا deck (cabin_level) کابین قرار دارد. به عبارت دیگر، هر مکان سوار شدن برای یک مجموعه خاص از کابین ها بود و نوعی تشکیلات وجود داشت که ابتدا کابین ها بر اساس مکان سوار شدن پر می شدند. بنابراین، اگر یک بلیط third-class داشتید که با F شروع می شد، نشان می داد در آن عرشه کابین مسافری اقامت دارد. برای مثال به احتمال زیاد از ساوت همپتون، انگلستان، سوار کشتی شده اند.

کد :

-- cabin level and pclass survival
select
  pclass,
  cabin_level,
  count(*) as num_passengers,
  round(avg(survived),4) as survival_rate
from titanic_train_test_raw_v2
where cohort = 'train'
group by 1,2
order by 1,2;

-- embarked doesn't seem to be as strong as a feature to predict survival
select
  coalesce(embarked,'S') as embarked,
  round(avg(survived),4) as survival_rate
from titanic_train_raw
group by 1;

تبدیل ویژگی و مهندسی (Feature Transformation and Engineering)

اکنون که ویژگی‌های آموزشی اصلی و بینش‌های بهتر در مورد زنده ماندن مسافران را درک می‌کنم، زمان آن رسیده است که یافته‌های خود را ادغام کنیم و شروع به جمع‌آوری مجموعه داده‌های آموزشی بهینه‌شده برای فیت کردن مدل کنیم.

1.ساخت ویژگی های is_one_family / is_mix_group / is_alone

من فرض می‌کنم که همان شماره بلیت، مجموعه ای از مسافرانی را که با هم سفر می‌کنند تعریف می‌کند. علاوه بر این، فرض می‌کنم که اگر فردی تنها مسافری باشد که به بلیط متصل شده است، این مسافر یک مسافر تنها است. بنابراین، مسافران را بر اساس شماره بلیط و نام خانوادگی استخراج شده از ویژگی اصلی Name گروه بندی کردم.

یک trade-off با این فرض وجود دارد، اما فرض می کنم که این بیشتر سناریوهای گروه را در بر می گیرد. هدف اصلی از تقسیم بندی گروه ها به این روش، تعمیم جمعیت کشتی است. (ship’s population)

اگر بلیطی دارای تنها یک نام خانوادگی با چند مسافر باشد، یک خانواده در نظر گرفته می شود که 1 درست است و 0 نادرست است (is_one_family). این در درجه اول به خانواده هایی مرتبط است که با هم سفر می کنند. بنابراین، اگر مسافر بخشی از بلیط یک خانواده بود، این فلگ عنوان 1 علامت گذاری می شود.
اگر بلیطی دارای چندین نام خانوادگی و چندین مسافر باشد، به عنوان یک گروه مختلط (is_mix_group) تعریف می شود. به عنوان مثال شماره بلیط 1601 دارای 7 نام خانوادگی مختلف است. در مجموع 67 بلیط با دو یا چند نام خانوادگی پیوست شده است. من فقط 12 بلیط برتر را در اینجا نشان می دهم.

اگر بلیطی دارای تنها یک نام خانوادگی و یک مسافر باشد، بیانگر مسافری است که به تنهایی سفر می کند (is_alone). با نگاهی دقیق تر به جمعیت male یا male population، مسافران male ای که به تنهایی سفر می کنند کمترین میزان زنده ماندن (15.57 درصد) را در مقایسه با مردان بالغ با خانواده (17.46 درصد) و پسران با خانواده (57.5 درصد) داشتند.

در یک نگاه کلی، مسافران male بالغ بدون خانواده یا بخشی از یک گروه مختلط کمترین شانس زنده ماندن را داشتند. ما الگوهای جالبی را در این خروجی می بینیم. مسافران male که با خانواده خود سفر می کردند، بالاترین میزان زنده ماندن را داشتند. به احتمال زیاد این مسافران male پسرها بودند. من این را در زیر تأیید می کنم.

وقتی پسرها را از male population جدا کردم، بسیار واضح‌تر شد. پسران با سرعت بیشتری نسبت به مردان بالغ زنده ماندند.

کد :

-- some tickets have multiple non-family individuals which were employed by wealthy employer e.g. ticket number PC 17757 while some tickets had a whole family attached.
select
  ticket,
  count(distinct trim(split_part(name,',',1))) as num_surnames,
  max(trim(split_part(name, ',', 1))) as surname_1,
  min(trim(split_part(name, ',', 1))) as surname_2,
  max(pclass) as pclass
from titanic_train_test_raw
group by 1
order by 2 desc;

-- group families by surname and ticket with assumption all families traveled on a single ticket and measure survival
-- male passengers with a family or with a mix group slightly higher probability of survival while boys with family has the highest chance of survival
-- adult male passengers without family or part of a mix group has the lowest chance of survival
select
  sex,
  case when trim(split_part(split_part(name,'.',1),', ',2)) = 'Master' then 'Boy' else 'Adult' end as bucket,
  case when (sibsp+parch+1) = 1 then 1 else 0 end as is_alone,
  round(avg(survived), 4)
from titanic_train_test_raw
where cohort = 'train'
and sex = 'male'
group by 1,2,3;

-- is_mix_group flag >> flag passengers who are traveling with group of other passengers on the same ticket
-- is_one_family flag >> flag passengers traveling with families
-- is_alone flag >> flag passenegers traveling alone
select
  case when num_surnames = 1 and num_passengers > 1 then 1 else 0 end as is_one_family,
  case when num_surnames > 1 and num_passengers > 1 then 1 else 0 end as is_mix_group,
  case when num_surnames = 1 and num_passengers = 1 then 1 else 0 end as is_alone,
  count(*) as num_tickets
from
(
	select
	  ticket,
	  count(passengerid) as num_passengers,
	  count(distinct trim(split_part(name,',',1))) as num_surnames
	from titanic_train_test_raw
	group by 1
) t1
group by 1,2,3;

-- We do see some interesting patterns in this output. Particularly, male + is_one_family = 1, which has the highest survival rate. This is most likely due to the boys in this cohort.
select
  f1.sex,
  f2.is_one_family,
  f2.is_mix_group,
  f2.is_alone,
  round(avg(f1.survived),4) as survival_rate
from titanic_train_raw f1
left join
(
	select
	  ticket,
	  case when num_surnames = 1 and num_passengers > 1 then 1 else 0 end as is_one_family,
	  case when num_surnames > 1 and num_passengers > 1 then 1 else 0 end as is_mix_group,
	  case when num_surnames = 1 and num_passengers = 1 then 1 else 0 end as is_alone
	from
	(
		select
		  ticket,
		  count(passengerid) as num_passengers, -- looking for actual number of passengers per ticket versus family_size because family size can also reflect family members on other tickets
		  count(distinct trim(split_part(name,',',1))) as num_surnames
		from titanic_train_test_raw
		group by 1
		-- Because ticket numbers are tied to class, it will be better to great group flags using passengers attached to individual tickets.
	) t1
) f2
  on f1.ticket = f2.ticket
group by 1,2,3,4
order by 1,5 desc;

-- male passengers with family or with a mix group slightly higher probability of survival while boys with family has the highest chance of survival
-- adult male passengers without family or part of a mix group has the lowest chance of survival
select
  f1.sex,
  case when trim(split_part(split_part(f1.name,'.',1),', ',2)) = 'Master' then 'Boy' else 'Adult' end as bucket,
  f2.is_one_family,
  f2.is_mix_group,
  f2.is_alone,
  round(avg(f1.survived),4) as survival_rate
from titanic_train_raw f1
left join
(
	select
	  ticket,
	  case when num_surnames = 1 and num_passengers > 1 then 1 else 0 end as is_one_family,
	  case when num_surnames > 1 and num_passengers > 1 then 1 else 0 end as is_mix_group,
	  case when num_surnames = 1 and num_passengers = 1 then 1 else 0 end as is_alone
	from
	(
		select
		  ticket,
		  count(passengerid) as num_passengers, -- looking for actual number of passengers per ticket versus family_size because family size can also reflect family members on other tickets
		  count(distinct trim(split_part(name,',',1))) as num_surnames
		from titanic_train_test_raw
		group by 1
		-- Because ticket numbers are tied to class, it will be better to great group flags using passengers attached to individual tickets.
	) t1
) f2
  on f1.ticket = f2.ticket
where f1.sex = 'male'
group by 1,2,3,4,5
order by 1,6 desc;

-- ticket+surname count does not match sum of sibsp+parch+1
select
  ticket,
  split_part(name,',',1) as surname,
  count(*) cnt,
  max(sibsp+parch+1) as sum_,
  max(cohort) as cohort
from titanic_train_test_raw
group by 1,2
having count(*) != max(sibsp+parch+1);
-- e.g. A/4 48871 - fourth member of the group missing, Joseph Davies.
	-- https://www.encyclopedia-titanica.org/titanic-victim/alfred-davies.html

select
  ticket,
  split_part(name,',',1) as surname,
  count(*) cnt,
  max(sibsp+parch+1) as sum_,
  max(cohort) as cohort
from titanic_train_test_raw
group by 1,2
having count(*) < max(sibsp+parch+1);

select
  ticket,
  split_part(name,',',1) as surname,
  count(*) cnt,
  max(sibsp+parch+1) as sum_,
  max(cohort) as cohort
from titanic_train_test_raw
group by 1,2
having count(*) > max(sibsp+parch+1);

2. بررسی family_size

برای تعیین اندازه خانواده، منطقی است که SibSp، ParCh، و مسافر فعلی (+1) را اضافه کنید. با این حال، به جای تعریف این موضوع در سطح مسافر، تعیین حداکثر اندازه خانواده بر اساس شماره بلیط و نام خانوادگی منطقی تر بود.

من به این فرض پایبند هستم که خانواده ها با هم و در این مورد با یک بلیط سفر کرده اند. در نتیجه، مجموع SibSp، ParCh، و مسافر فعلی، که نشان دهنده اندازه خانواده است، با شماره بلیط و نام خانوادگی مطابقت داده می شود. من یک mapping SQL subquery ایجاد کردم که در آن اگر شماره بلیط و نام خانوادگی یکسانی وجود دارد، اندازه خانواده (SibSp + ParCh + 1) برای مسافر map می شود.

پس از جمع‌آوری منطق، به میانگین میزان زنده ماندن بر اساس اندازه خانواده و Pclass نگاه کردم. خانواده های نسبتا پرجمعیت میزان بقا کمتری داشتند. به راحتی می توان اینطور استنباط کرد که اگر عضوی از یک خانواده بزرگ بودید، مرده اید! همچنین اگر تنها نیز بودید میمردید! از آنجایی که تنها تعداد انگشت شماری بلیط با چهار مسافر یا بیشتر وجود دارد، تعمیم زنده ماندن گروه های بزرگ دشوار است. با این حال، بر اساس داده‌های موجود، ارزش دیدن این را دارد که آیا اندازه خانواده در پیش‌بینی زنده ماندن اهمیت نسبی دارد یا خیر.

کد:

-- average survival rate by family size
-- larger the family size survival was very low. Basically, if you were part of a big family, you died.
select
  pclass,
  family_size,
  round(avg(survival_rate), 4) as avg_survival_rate_by_familysize,
  count(*) as unique_ticket_cnt
from
(
    select
      ticket,
      pclass,
      trim(split_part(name,',',1)) as family_name,
      max(sibsp+parch+1) as family_size,
      round(avg(survived),4) as survival_rate
    from titanic_train_test_raw
    group by 1,2,3
) t1
group by 1,2
order by 1,2 desc;

3. اولین جدول ویژگی های تلفیقی را بسازید: titanic_train_test_raw_v2

من اکنون اولین جدول تلفیقی خود را بر اساس تمام یافته هایم در بالا تجمیع می کنم. در این مرحله، ویژگی های جدید را ترکیب کرده ام و ویژگی های موجود را برای مصرف بهتر مدل تغییر می دهم.

رمزگذاری (Encoding) ویژگی جنسیت : female = 1 and male = 0
نام خانوادگی (نام خانوادگی) را از ویژگی Name استخراج کنید.
افزودن ویژگی title_grouping که قبلا به اشتراک گذاشتم.
افزودن ویژگی is_woman_child
استخراج سطوح کابین (عرشه) یا cabin levels (deck) از ویژگی کابین
رمزگذاری یا (Encoding) ویژگی Embarked به صورت S = 0، C = 1، و Q = 2
افزودن ویژگی Family_size بر اساس شماره بلیط و نام خانوادگی (surname)
افزودن ویژگی‌های is_one_family، is_mix_group و is_alone
افزودن ویژگی fare_per_passenger با تقسیم کرایه بر تعداد PassengerId یافت شده در هر بلیط

کد :

drop table if exists titanic_train_test_raw_v2;

create table titanic_train_test_raw_v2 as
(
	select
	  t1.passengerid,
	  t1.cohort,
	  t1.survived,
	  t1.pclass,
	  case when t1.sex = 'female' then 1 else 0 end as sex,
	  t1.age,
	  t4.fare_per_passenger,
	  t1.fare,
	  t1.ticket,
	  trim(split_part(t1.name,',',1)) as family_name,
	  case when split_part(split_part(t1.name,'.',1),', ',2) in ('Dr','Rev','Col','Major','Jonkheer','Sir','Don','Capt') and t1.sex = 'male' then 'Mr'
	       when split_part(split_part(t1.name,'.',1),', ',2) = 'Dr' and t1.sex = 'female' then 'Mrs'
	       when split_part(split_part(t1.name,'.',1),', ',2) in ('Mlle') then 'Miss'
	       when split_part(split_part(t1.name,'.',1),', ',2) in ('Ms','the Countess','Mme','Lady','Dona') then 'Mrs'
	       else split_part(split_part(t1.name,'.',1),', ',2)
	  end as title_grouping,
	  case when split_part(split_part(t1.name,'.',1),', ',2) in ('Dr','Rev','Col','Major','Jonkheer','Sir','Don','Capt') and t1.sex = 'male' then 0
	       when split_part(split_part(t1.name,'.',1),', ',2) = 'Dr' and t1.sex = 'female' then 1
	       when split_part(split_part(t1.name,'.',1),', ',2) in ('Mlle','Ms','Miss','Mrs','Miss','the Countess','Mme','Lady','Dona') then 1
	       when split_part(split_part(t1.name,'.',1),', ',2) = 'Master' then 1
	       when split_part(split_part(t1.name,'.',1),', ',2) = 'Mr' then 0
	  end as is_woman_child,
	  t1.sibsp,
	  t1.parch,
	  t1.cabin,
	  substring(trim(t1.cabin),1,1) as cabin_level,
	  t2.family_size,
	  t3.is_one_family,
	  t3.is_mix_group,
	  t3.is_alone,
	  case when coalesce(embarked,'S') = 'S' then 0
	       when coalesce(embarked,'S') = 'C' then 1
	       when coalesce(embarked,'S') = 'Q' then 2
	  end as embarked
	from titanic_train_test_raw t1
	     left join
	     (
		select
		  ticket,
		  trim(split_part(name,',',1)) as family_name,
		  max(sibsp + parch + 1) as family_size
		from titanic_train_test_raw -- logic assumes a family traveled on a single ticket
		group by 1,2
	     ) t2
	       on t1.ticket = t2.ticket
	       and trim(split_part(t1.name,',',1)) = t2.family_name
	     left join
	     (
		 select
		   ticket,
		   case when num_surnames = 1 and num_passengers > 1 then 1 else 0 end as is_one_family,
		   case when num_surnames > 1 and num_passengers > 1 then 1 else 0 end as is_mix_group,
		   case when num_surnames = 1 and num_passengers = 1 then 1 else 0 end as is_alone
		 from
		 (
		    select
		      ticket,
		      count(passengerid) as num_passengers,
		      count(distinct trim(split_part(name,',',1))) as num_surnames
		    from titanic_train_test_raw
		    group by 1
		 ) tic
	     ) t3
	        on t1.ticket = t3.ticket
	     left join
	     (
		 select
		   ticket,
		   round(max(fare) / count(passengerid), 2) as fare_per_passenger
		 from titanic_train_test_raw
		 group by 1
	     ) t4
	       on t4.ticket = t1.ticket
);

4. دومین جدول ویژگی های تلفیقی را بسازید: titanic_train_test_raw_v3

با استفاده از برخی از ویژگی‌هایی که قبلا ایجاد شده‌اند، یک جدول تلفیقی دوم (v3) ایجاد کردم تا ویژگی‌های بیشتری را اضافه کنم و تغییرات بیشتری ایجاد کنم. بهتر است هنگام استفاده از SQL چندین جدول به این روش بسازید زیرا به سازماندهی کمک می کند و با داشتن تکه های کوچکتر SQL را خواناتر می کند.

مقادیر از دست رفته سن یا Age missing values:

در ابتدا می بایست مقادیر سن از دست رفته را پر کنید. من داده ها را بر اساس Pclass، Sex، و title_grouping گروه بندی کردم تا میانگین سنی هر گروه را محاسبه کنم. من از میانگین به جای میانه استفاده کردم زیرا هر دو مجموعه اعداد مشابه هستند. همچنین انحراف معیار میانگین را برای بررسی تغییرپذیری یا variability محاسبه کردم.

ویژگی Age_bucket:

علاوه بر آن، یک ویژگی به نام age_bucket ایجاد کردم. تصمیم گرفتم که متغیر Age پیوسته را بگیرم و آنها را در 7 سطل یا bin قرار دهم. از آنجایی که ویژگی Age حاوی میانگین های تخمینی است، شاید بهتر باشد که این ویژگی را به یک categorical feature تبدیل کنید تا نویز در ویژگی پیوسته به حداقل برسد.

ویژگی Fare_bucket & fare missing values:

من میانگین کرایه به ازای هر مسافر به ازای هر کلاس را در طول تحلیل خود محاسبه کردم، و مشخص بود که قیمت‌ها به کلاس مرتبط هستند. همچنین fare missing values را با fare_per_passenger که به Pclass هر مسافر مرتبط بود پر کردم. در همان زمان، 5 سطل یا bin را برای ایجاد یک categorical feature ایجاد کردم.

ویژگی Title_grouping: چهار برچسب text string در این ویژگی به مقادیر عددی (numeric values) تبدیل شدند. (Master = 0, Mrs = 1, Miss = 2, and Mr = 3)

ویژگی Cabin_level & cabin level missing values :

رویکرد در نظر گرفته شده برای پر کردن مقادیر از دست رفته cabin_level این است که همه مسافران را بر اساس Pclass، Embarked و cabin_level که Cabin مقدار null نیستند، شمارش کنم. در مرحله بعد، تعداد مسافران متصل به Pclass، Embarked و cabin_level را به ترتیب نزولی رتبه‌بندی کردم و برای هر ترکیب از Pclass و Embarked، مورد cabin_level را با بیشترین تعداد مسافران متصل به آن انتخاب کردم.

کد:

-- cabin level estimating logic in SQL to fill in missing cabin level values per passenger
select *
from
(
  select
    pclass,
    embarked,
    cabin_level,
    row_number() over (partition by pclass, embarked order by num_passengers desc) as cabin_rnk
  from
  (
     select
       pclass,
       embarked,
       cabin_level,
       count(*) as num_passengers
     from titanic_train_test_raw_v2
     where cabin_level is not null
     group by 1,2,3
  ) cl
) t3
where t3.cabin_rnk = 1;

If Pclass = 1 and Embarked = 0, then cabin_level = C.

If Pclass = 1 and Embarked = 1, then cabin_level = C.

If Pclass = 1 and Embarked = 2, then cabin_level = C.

If Pclass = 2 and Embarked = 0, then cabin_level = F.

If Pclass = 3 and Embarked = 0, then cabin_level = G.

And so on!

کد:

drop table if exists titanic_train_test_raw_v3;

create table titanic_train_test_raw_v3 as

select
  t1.passengerid,
  t1.cohort,
  t1.survived,
  t1.pclass,
  t1.sex,
  coalesce(t1.age, t2.age_mean) as age,
  case when floor(1.0 * coalesce(t1.age, t2.age_mean) / 10) >= 7 then 7 else floor(1.0 * coalesce(t1.age, t2.age_mean) / 10) end as age_bucket,
  coalesce(t1.fare, t4.fare_mean) as fare,
  coalesce(t1.fare_per_passenger, t4.fare_mean) as fare_per_passenger,
  case when floor(coalesce(t1.fare_per_passenger, t4.fare_mean) / 11) > 4 then 5 else floor(coalesce(t1.fare_per_passenger, t4.fare_mean) / 11) end as fare_bucket,
  t1.ticket,
  t1.family_name,
  case when t1.title_grouping = 'Master' then 0
       when t1.title_grouping = 'Mrs' then 1
      when t1.title_grouping = 'Miss' then 2
       when t1.title_grouping = 'Mr' then 3
  end as title_grouping,
  t1.is_woman_child,
  t1.sibsp,
  t1.parch,
  t1.cabin,
  case when coalesce(t1.cabin_level, t3.cabin_level) = 'A' then 0
       when coalesce(t1.cabin_level, t3.cabin_level) = 'B' then 1
       when coalesce(t1.cabin_level, t3.cabin_level) = 'C' then 2
       when coalesce(t1.cabin_level, t3.cabin_level) = 'D' then 3
       when coalesce(t1.cabin_level, t3.cabin_level) = 'E' then 4
       when coalesce(t1.cabin_level, t3.cabin_level) = 'F' then 5
       when coalesce(t1.cabin_level, t3.cabin_level) = 'G' then 6
       when coalesce(t1.cabin_level, t3.cabin_level) = 'T' then 7
  end as cabin_level,
  t1.family_size,
  t1.is_one_family,
  t1.is_mix_group,
  t1.is_alone,
  t1.embarked
from titanic_train_test_raw_v2 t1
     left join
    (
        select
          pclass,
          sex,
         title_grouping,
         round(avg(age),2) as age_mean,
          round(stddev(age),2) as age_stddev,
	  count(passengerid) as num_passengers
        from titanic_train_test_raw_v2
	group by 1,2,3
     ) t2
       on t1.pclass = t2.pclass
       and t1.sex = t2.sex
       and t1.title_grouping = t2.title_grouping
     left join
     (
        select
          pclass,
          embarked,
          cabin_level,
          row_number() over (partition by pclass, embarked order by num_passengers desc) as cabin_rnk
        from
        (
           select
             pclass,
             embarked,
             cabin_level,
             count(*) as num_passengers
           from titanic_train_test_raw_v2
           where cabin_level is not null
           group by 1,2,3
        ) cl
      ) t3
        on t1.pclass = t3.pclass
        and t1.embarked = t3.embarked
        and t3.cabin_rnk = 1
      left 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 titanic_train_test_raw
            group by 1,2
         ) t1
         group by 1
       ) t4
         on t1.pclass = t4.pclass
);

5. ایجاد منطق «women-child-group»

در ادامه سعی کردم ویژگی Chris Deotte را با تغییراتی در SQL دوباره ایجاد کنم. در مقاله بعدی خود، این ویژگی را از نظر تأثیر و اهمیت آزمایش خواهم کرد.

ابتدا، عنوان هر مسافر به عنوان مرد، زن یا پسر (man, woman, or boy) برچسب گذاری می شود. به طور همزمان، همه عناوین male به عنوان "noGroup" برچسب گذاری می شوند زیرا اولویت زنده ماندن به زنان و کودکان داده می شود.

سپس، تعداد یا فرکانس نام خانوادگی برای هر مسافر را محاسبه کردم. این فرکانس به کل مجموعه داده نگاه می کند و نه در سطح بلیط. هر مسافری با تعداد نام خانوادگی یک یا کمتر، در صورت وجود، به عنوان "noGroup" برچسب گذاری می شود. بنابراین، زنان و کودکان در خانواده ها نسبت به زنانی که به تنهایی سفر می کنند، اولویت دارند.

در این مرحله، احتمال زنده ماندن با استفاده از ویژگی هدف Survived داده های آموزشی بر اساس ویژگی نام خانوادگی به روز شده محاسبه می شود. همچنین اکثر نام‌های خانوادگی به «noGroup» تغییر کرده‌اند، و بنابراین، زنده ماندن همه مسافران با استفاده از این ویژگی نام خانوادگی به‌روزرسانی شده تعمیم می‌یابد.

کد:

drop table if exists titanic_train_test_wcg_v0;

create table titanic_train_test_wcg_v0 as

	select
	  round(avg(t3.survived) over (partition by t3.surname), 4) as surname_survival,
	  t3.*
	from
	(
		select
		  case when count(*) over (partition by t2.surname) <= 1 then 'noGroup' else t2.surname end as surname,
		  count(*) over (partition by t2.surname) as surname_freq,
	  t2.title, t2.cohort,
		  t2.passengerid, t2.survived,
		  t2.pclass, t2.name, t2.sex,
		  t2.age, t2.sibsp,
		  t2.parch, t2.ticket,
		  t2.fare, t2.cabin, t2.embarked
		from
		(
			select
			  case when trim(split_part(split_part(t1.name,'.',1),', ',2)) in ('Capt','Don','Major','Col','Rev','Dr','Sir','Mr','Jonkheer')
			       then 'noGroup'
			       else trim(split_part(t1.name,',',1))
			  end as surname,
			  case when trim(split_part(split_part(t1.name,'.',1),', ',2)) in ('Capt','Don','Major','Col','Rev','Dr','Sir','Mr','Jonkheer') then 'man'
			       when trim(split_part(split_part(t1.name,'.',1),', ',2)) in ('Dona','the Countess','Mme','Mlle','Ms','Miss','Lady','Mrs') then 'woman'
			       when trim(split_part(split_part(t1.name,'.',1),', ',2)) = 'Master' then 'boy'
			  end as title,
			  t1.*
			from titanic_train_test_ra t1
		) t2
	) t3
);

در نهایت با استفاده از title و surname_survival، یک فلگ باینری جدید به نام title_surname_survival ایجاد کردم. من دو جدول برای ثبت هر کدام ایجاد کردم - titanic_train_ml_features_v0 و titanic_test_ml_features_v0.

مجموعه داده آموزش ML: titanic_train_ml_features_v0

مجموعه داده تست ML: titanic_test_ml_features_v0 (مورد یکسان با مجموعه داده آموزشی به جز ویژگی Survived وجود ندارد.)

کد :

drop table if exists titanic_train_ml_features_v0;

create table titanic_train_ml_features_v0 as
(
	select
	  t1.survived,
	  t1.passengerid,
          t2.title_surname_survival,
	  t1.pclass,
	  t1.sex,
	  t1.age,
	  t1.age_bucket,
	  t1.fare,
	  t1.fare_bucket,
	  t1.title_grouping,
	  t1.is_woman_child,
	  t1.family_size,
	  t1.is_one_family,
	  t1.is_mix_group,
	  t1.is_alone,
	  t1.cabin_level,
	  t1.embarked
	from titanic_train_test_raw_v3 t1
	     left join
	     (
	       select
                 passengerid,
                 max(case when title = 'woman' and surname_survival = 0 then 0
                          when title = 'woman' and surname_survival >= 0.75 then 1
                          when title = 'boy' and surname_survival >= 0.75 then 1
                          when title = 'woman' and surname_survival is null then 1
                          when title = 'boy' and surname_survival is null then 1
                          else 0
                 end) as title_surname_survival
	       from titanic_train_test_wcg_v0
	       group by 1
	     ) t2
	       on t1.passengerid = t2.passengerid
	where t1.cohort = 'train'
);

drop table if exists titanic_test_ml_features_v0;

create table titanic_test_ml_features_v0 as
(
	select
	  t1.passengerid,
          t2.title_surname_survival,
	  t1.pclass,
	  t1.sex,
	  t1.age,
	  t1.age_bucket,
	  t1.fare,
	  t1.fare_bucket,
	  t1.title_grouping,
	  t1.is_woman_child,
	  t1.family_size,
	  t1.is_one_family,
	  t1.is_mix_group,
	  t1.is_alone,
	  t1.cabin_level,
	  t1.embarked
	from titanic_train_test_raw_v3 t1
	     left join
	     (
               select
                 passengerid,
                 max(case when title = 'woman' and surname_survival = 0 then 0
                          when title = 'woman' and surname_survival >= 0.75 then 1
                          when title = 'boy' and surname_survival >= 0.75 then 1
                          when title = 'woman' and surname_survival is null then 1
                          when title = 'boy' and surname_survival is null then 1
                          else 0
                 end) as title_surname_survival
	       from titanic_train_test_wcg_v0
	       group by 1
	     ) t2
	       on t1.passengerid = t2.passengerid
	where t1.cohort = 'test'
);

خلاصه

اطلاعات بسیار زیادی در این مقاله ارائه شده است. من تجزیه و تحلیل اکتشافی و مهندسی ویژگی خود را با استفاده از SQL صرف انجام دادم. از GROUP BY، توابع پنجره، توابع تجمع، subqueries، WITH clause، HAVING clause و سایر تکنیک‌های SQL برای slice و dice داده‌ها استفاده کردم.

مرحله بعدی این است که مجموعه داده های آموزشی و آزمایشی نهایی را برای تجزیه و تحلیل با استفاده از پایتون پیاده سازی کنید. در مقاله بعدی خود، از Python برای آزمایش مدل‌های مختلف ML، درک اهمیت ویژگی و تنظیم مدل انتخابی با استفاده از RandomizedSearchCV و GridSearchCV خواهم پرداخت.

منبع

ابوالفضل وکیلی

instagram : @a_vakily7

شاید از این پست‌ها خوشتان بیاید