Преобразование таблицы в горячее кодирование значения одного столбца

У меня есть таблица с двумя столбцами:

+---------+--------+
| keyword | color  |
+---------+--------+
| foo     | red    |
| bar     | yellow |
| fobar   | red    |
| baz     | blue   |
| bazbaz  | green  |
+---------+--------+

Мне нужно сделать какое-то однократное кодирование и преобразовать таблицу в PostgreSQL, чтобы:

+---------+-----+--------+-------+------+
| keyword | red | yellow | green | blue |
+---------+-----+--------+-------+------+
| foo     |   1 |      0 |     0 |    0 |
| bar     |   0 |      1 |     0 |    0 |
| fobar   |   1 |      0 |     0 |    0 |
| baz     |   0 |      0 |     0 |    1 |
| bazbaz  |   0 |      0 |     1 |    0 |
+---------+-----+--------+-------+------+

Можно ли обойтись только SQL? Любые советы о том, как начать?


postgresql sql crosstab
person Ernest    schedule 10.08.2017    source источник
comment
Что означает one-hot encoding?   -  person Siyual    schedule 10.08.2017
comment
Ваш список цветов известен заранее?   -  person PM 77-1    schedule 10.08.2017
comment
@ PM77-1 да, только эти четыре.   -  person Ernest    schedule 10.08.2017
comment
@Siyual - это название преобразования, то есть преобразования категориальных данных в числовые, где каждая категория находится в отдельном столбце. квора. ком/   -  person Ernest    schedule 10.08.2017
comment
@Ernest Так это bit или count?   -  person Siyual    schedule 10.08.2017
comment
Затем используйте CASE WHEN для каждого цвета. Если пары keyword и color не уникальны, необходимо добавить агрегацию.   -  person PM 77-1    schedule 10.08.2017
comment
@ PM77-1 спасибо, пары уникальны, и ваше решение с Ото - это то, что мне нужно.   -  person Ernest    schedule 10.08.2017
comment
@Siyual a bit, 1 — правда, 0 — ложь.   -  person Ernest    schedule 10.08.2017

Ответы (3)


arrow_upward
22
arrow_downward

Если я правильно понимаю, вам нужна условная агрегация:

select keyword,
count(case when color = 'red' then 1 end) as red,
count(case when color = 'yellow' then 1 end) as yellow
-- another colors here
from t
group by keyword
person Oto Shavadze    schedule 10.08.2017
comment
Не уверен, почему за него проголосовали, он точно выполняет свою работу. Теперь это кажется таким очевидным, спасибо. - person Ernest; 10.08.2017
comment
Это соответствует критериям исходного вопроса, но я ищу способ динамического горячего кодирования более 100 столбцов для специалиста по данным. В основном необходимо поворачивать потенциальные значения каждого столбца и делать это динамически. Надеюсь, кто-то уже написал динамический SQL! - person njkroes; 06.09.2018
comment
@njkroes, ты когда-нибудь находил ответ на этот вопрос? Я сталкиваюсь с аналогичной ситуацией, когда список столбцов, которые я хочу закодировать, может измениться. - person njfrazie; 22.02.2019
comment
@njfrazie В итоге я просто использовал несколько операторов IIF, чтобы сделать это, как описано здесь. В ситуации, когда вы просматриваете большой набор данных, вы можете взять все представленные ответы и динамически сгенерировать что-то, но на самом деле нет никакого способа сделать что-то для одной записи на лету, если вы уже не знаете, чего ожидать. В этом случае вы просто запишете все ожидаемые результаты, как предлагает этот ответ. - person njkroes; 04.03.2019

arrow_upward
1
arrow_downward

Чтобы использовать этот код в таблице с большим количеством столбцов, используйте Python для генерации запросов:

1) Создайте список с уникальными переменными, которые вы хотите использовать в качестве имен столбцов, и импортируйте их в Python, скажем, как: list.

for item in list:
 print('count(case when item=' +str(item)+ 'then 1 end) as is_'+str(item)+',')

2) Скопируйте вывод (минус последняя запятая в последней строке)

3) Затем:

select keyword,

OUTPUT FROM PYTHON

from t
group by keyword
person Emil    schedule 09.05.2019

arrow_upward
1
arrow_downward

Еще один способ достичь цели в вашем тестовом примере, используя расширение tablefunc и COALESCE(), чтобы заполнить все пустые поля:

postgres=# create table t(keyword varchar,color varchar);
CREATE TABLE
postgres=# insert into t values ('foo','red'),('bar','yellow'),('fobar','red'),('baz','blue'),('bazbaz','green');
INSERT 0 5
postgres=# SELECT keyword, COALESCE(red,0) red, 
 COALESCE(blue,0) blue, COALESCE(green,0) green, 
 COALESCE(yellow,0) yellow 
 FROM crosstab(                         
  $$select keyword, color, COALESCE('1',0) as onehot from test01
    group by 1, 2 order by 1, 2$$,
  $$select distinct color from test01 order by 1$$)
 AS result(keyword varchar, blue int, green int, red int, yellow int);
 keyword | red | blue | green | yellow 
---------+-----+------+-------+--------
 bar     |   0 |    0 |     0 |      1
 baz     |   0 |    1 |     0 |      0
 bazbaz  |   0 |    0 |     1 |      0
 fobar   |   1 |    0 |     0 |      0
 foo     |   1 |    0 |     0 |      0
(5 rows)

postgres=#

А если просто получить результат под psql:

postgres=# select keyword, color, COALESCE('1',0) as onehot from t
  --group by 1, 2 order by 1, 2
  \crosstabview keyword color
 keyword | red | yellow | blue | green 
---------+-----+--------+------+-------
 foo     |   1 |        |      |      
 bar     |     |      1 |      |      
 fobar   |   1 |        |      |      
 baz     |     |        |    1 |      
 bazbaz  |     |        |      |     1
(5 rows)

postgres=#
person C.C. Hsu    schedule 13.09.2019