I’d like to perform one-hot encoding on the industryCode column and preserve all other columns in the following table.
| date | windCode | industryCode |
|---|---|---|
| 2022.08.01 | 000001.SZ | 1101 |
| 2022.08.02 | 000002.SZ | 1102 |
| 2022.08.03 | 000003.SZ | 1103 |
After the conversion, the table should look like:
| date | windCode | 1101 | 1102 | 1103 |
|---|---|---|---|---|
| 2022.08.01 | 000001.SZ | 1 | 0 | 0 |
| 2022.08.02 | 000002.SZ | 0 | 1 | 0 |
| 2022.08.03 | 000003.SZ | 0 | 0 | 1 |
Based on your requests, there are two ways to solve this.
Solution #1:
Use built-in function oneHot(obj, encodingColumns)
t = table(2022.08.01 2022.08.02 2022.08.03 as date
,`000001.SZ`000002.SZ`000003.SZ as windCode
,`1101`1102`1103 as industryCode)
oneHot(t, ['industryCode'])
Worth mentioning, with the method above, you can one hot encode multiple columns all at once.
oneHot(t, ['industryCode','windCode'])
Solution #2:
Use SQL query pivot by
res = select iif(isNull(industryCode), 0, 1) from t pivot by date, windCode, industryCode;
nullFill!(res, 0);
