I want to represent a string column as a binary 1 or 0 by pivoting the string column and making its values as header using SQL (Snowflake). It would python equivalent of pd.get_dummies where the function transform categorical column in to one-hot encoded columns. How do I do that?
Sample Data:
| id | value |
|---|---|
| A | ‘G802’ |
| A | ‘R620’ |
| A | ” |
| B | ‘J209’ |
| B | ‘B009’ |
| C | ” |
| C | ‘R509’ |
Expected Output:
| id | G802 | R620 | J209 | B009 | R509 |
|---|---|---|---|---|---|
| A | 1 | 1 | 0 | 0 | 0 |
| B | 0 | 0 | 1 | 1 | 0 |
| C | 0 | 0 | 0 | 0 | 1 |
I need the query to be dynamic as I have more and random values in the value column. The order of the column can be anything. The '' value in value can or cannot be part of the pivot table. I have tried a few options with the pivot function but no luck. I very much appreciate your help. Thank you!
1
It is possible to achieve it with SQL and dynamic PIVOT:
SELECT *
FROM (SELECT id, VALUE, IFF(value='',0, 1) AS VALUE2 FROM t)
PIVOT (MAX(VALUE2) FOR VALUE IN (SELECT VALUE FROM t WHERE VALUE != '')
DEFAULT ON NULL (0));
For test data:
CREATE OR REPLACE TABLE t(id TEXT, VALUE TEXT) AS
SELECT 'A', 'G802' UNION ALL
SELECT 'A', 'R620' UNION ALL
SELECT 'A', '' UNION ALL
SELECT 'B', 'J209'UNION ALL
SELECT 'B', 'B009'UNION ALL
SELECT 'C', '' UNION ALL
SELECT 'C', 'R509';
Output:
How does it work:
IFF(value='',0, 1) AS VALUE2helper column to categorize data 0 or 1PIVOT MAX(VALUE2)– result of pivot based on helper columnVALUE IN (SELECT VALUE FROM t WHERE VALUE != '')– dynamic pivot, skipping''- if we don’t bother empty column
''then simplyVALUE IN (ANY)
Using VALUE IN (ANY) and EXCLUDE:
SELECT * EXCLUDE "''"
FROM (SELECT id, VALUE, IFF(value='',0, 1) AS VALUE2 FROM t)
PIVOT (MAX(VALUE2) FOR VALUE IN (ANY) DEFAULT ON NULL (0));
3
You’re not going to find a way to make SQL dynamic like that.
What I personally do, is have my own templating library that renders the SQL for me, and I use Jinja to write my templates.
rasgoql does the same thing, and here’s the jinja for one-hot-encoding:
SELECT *,
{% for val in distinct_col_vals %}
{%- if val is not none %}
CASE WHEN {{ column }} = {{ "'" ~ val ~ "'"}} THEN 1 ELSE 0 END as {{ column ~ '_' ~ val }}{{ ', ' if not loop.last else '' }}
{%- else %}
CASE WHEN {{ column }} IS NULL THEN 1 ELSE 0 END as {{ column }}_IS_NULL{{ ', ' if not loop.last else '' }}
{%- endif -%}
{% endfor %}
FROM {{ source_table }}
Obviously you need to pass in the distinct vals as an argument, which requires you either do that in python or you build yourself a run_query() function that does this automatically, which I do.
With little effort you could have a template that automatically retrieves the values:
{%- set run_query_error_message -%}
This transform depends on dynamic values to work, but no Data Warehouse connection is available.
Instead, please use the `list_of_vals` argument to provide these values explicitly
{%- endset -%}
{%- if list_of_vals is not defined -%}
{%- set results = run_query("SELECT DISTINCT " + column + " FROM " + source_table) -%}
{%- if results is none -%}
{{ raise_exception(run_query_error_message) }}
{%- endif -%}
{%- set distinct_col_vals = results[column].to_list() -%}
{%- else -%}
{%- set distinct_col_vals = list_of_vals -%}
{%- endif -%}
SELECT *,
{% for val in distinct_col_vals %}
{%- if val is not none %}
CASE WHEN {{ column }} = {{ "'" ~ val ~ "'"}} THEN 1 ELSE 0 END as {{ column ~ '_' ~ val }}{{ ', ' if not loop.last else '' }}
{%- else %}
CASE WHEN {{ column }} IS NULL THEN 1 ELSE 0 END as {{ column }}_IS_NULL{{ ', ' if not loop.last else '' }}
{%- endif -%}
{% endfor %}
FROM {{ source_table }}
You may think, this is way overkill. And yes, it is. But I write dynamic SQL a lot and to me it’s worth it.
But the short answer to your question is – No, SQL requires you type out the columns you won’t get the engine itself to be dynamic. So either deal with it, learn tricks to write the SQL for you dynamically using Excel or python, or go overkill and build a templating library like rasgoql.
3