ТИТАНИК

15 апреля 1912 года в северной атлантике столкнулся
с айсбергом и затонул пароход «Титаник».

На борту было 1309 человек, пассажиры и экипаж.
Спаслось 500 человек, погибло 809.

В этом цикле роликов мы исследуем вероятности выживаемости
пассажиров в зависимости от различных, хорошо задокументированных
факторов.

Первый слайд посвящен получению данных, переносу их в СУБД Postgres
и изучению их в psql - терминале Postgres.


Скачать архивный файл с помощью wget (30 Кб)
Команда выполняются в командной строке терминала Linux

wget an2k.ru/data/level2/201-titanic/titanic3.csv.zip

Распаковать архив

unzip titanic3.csv.zip

В файле titanic3.csv содержатся все необходимые данные.


Но, гораздо удобнее работать с данными в SQL базе данных, чем в Excel.

Перенесём данные в Postgres


Датасет titanic3.csv содержит следующие поля:

id       — идентификатор, который был присвоен в базе
pclass   — класс купленного билета (1-й, 2-й, или 3-й)
survived — выжил 1 или нет 0 пассажир
name     — имя пассажира
sex      — пол пассажира
age      — возраст пассажира
sibsp    — количество братьев или супругов у пассажира на борту
parch    — количество родителей или детей у пассажира на борту
ticket   — номер билета
fare     — сумма денег, которую заплатили за билет
cabin    — номер каюты, в которой находился пассажир
embarked — порт (город), где пассажир сел на титаник
boat     — номер лодки куда попал выживший
body     — номер лодки куда было поднято тело погибшего
home     — домашний адрес


Данные Go to SQL

Создать базу

create database titanic;

Перейти в созданную базу
\c titanic

Создать таблицу tit0

create table tit0 (
  pclass   int,
  survived int,
  name     varchar,
  sex      varchar,
  age      varchar,
  sibsp    int,
  parch    int,
  ticket   varchar,
  fare     varchar,
  cabin    varchar,
  embarked varchar,
  boat     varchar,
  body     varchar,
  home     varchar
);

Залить файл в таблицу tit0

COPY tit0 FROM '/home/an2k/py/lessons/Titanic/titanic3.csv'
  DELIMITER '	' CSV HEADER;


Создать таблицу tit1

create table tit1 (
  id serial,
  pclass   int,
  survived int,
  name     varchar,
  sex      varchar,
  age      varchar,
  sibsp    int,
  parch    int,
  ticket   varchar,
  fare     varchar,
  cabin    varchar,
  embarked varchar,
  boat     varchar,
  body     varchar,
  home     varchar
);

create UNIQUE index on tit1 (id);

Залить данные в таблицу tit1

insert into tit1 (
  pclass   ,
  survived ,
  name     ,
  sex      ,
  age      ,
  sibsp    ,
  parch    ,
  ticket   ,
  fare     ,
  cabin    ,
  embarked ,
  boat     ,
  body     ,
  home
)
select * from tit0;


Откомментоировать поля таблицы tit1

comment on column tit1.id        is 'Идентификатор, который был присвоен в базе';
comment on column tit1.pclass    is 'Класс купленного билета (1-й, 2-й, или 3-й)';
comment on column tit1.survived  is 'Выжил 1 или нет 0 пассажир';
comment on column tit1.name      is 'Имя пассажира';
comment on column tit1.sex       is 'Пол пассажира';
comment on column tit1.age       is 'Возраст пассажира';
comment on column tit1.sibsp     is 'Количество братьев или супругов у пассажира на борту';
comment on column tit1.parch     is 'Количество родителей или детей у пассажира на борту';
comment on column tit1.ticket    is 'Номер билета';
comment on column tit1.fare      is 'Сумма денег, которую заплатили за билет';
comment on column tit1.cabin     is 'Номер каюты, в которой находился пассажир';
comment on column tit1.embarked  is 'Порт (город), где пассажир сел на титаник';
comment on column tit1.boat      is 'Номер лодки куда попал выживший';
comment on column tit1.body      is 'Номер лодки куда было поднято тело погибшего';
comment on column tit1.home      is 'Домашний адрес';



Посмотреть структуру таблицы tit1

\d+ tit1


Посмотреть данные таблицы tit1

select * from tit1 limit 1;

select count(*) from tit1 ;
 count
-------
  1309

select count(*) from tit1 where survived = 1;
 count
-------
   500

select count(*) from tit1 where survived = 0;
 count
-------
   809

select survived, count(*) from tit1 group by survived;
 survived | count
----------+-------
        0 |   809 -- Погибли
        1 |   500 -- Выжили

select sex, count(*) from tit1 group by sex;
  sex   | count
--------+-------
 male   |   843  -- Мужчины
 female |   466  -- Женщины


select sex, survived, count(*) from tit1
  group by sex, survived order by sex, survived;

  sex   | survived | count
--------+----------+-------
 female |        0 |   127
 female |        1 |   339
 male   |        0 |   682
 male   |        1 |   161


Как посчитать проценты погибших (survived=0) и выживших (survived=1)?

  sex   | survived | count | percent
--------+----------+-------+---------
 female |        0 |   127 |   ?
 female |        1 |   339 |   ?
 male   |        0 |   682 |   ?
 male   |        1 |   161 |   ?


Вот так подсказывает ИИ phind.com:

https://www.phind.com/search/cmfmdi8ne00002v6qo02wgbkh

SELECT
    sex,
    survived,
    count,
    ROUND(
        100.0 * count /
        SUM(count) OVER (PARTITION BY sex),
        2
    ) as percent
FROM (
    SELECT
        sex,
        survived,
        count(*) as count
    FROM tit1
    GROUP BY sex, survived
) subquery
ORDER BY sex, survived;


  sex   | survived | count | percent
--------+----------+-------+---------
 female |        0 |   127 |   27.25
 female |        1 |   339 |   72.75
 male   |        0 |   682 |   80.90
 male   |        1 |   161 |   19.10