1
$\begingroup$

How can I do encoding for a category when data has repeated rows? Can I do target encoding? Or Can I utilize another encoding?

I want to figure out how to include a categorical variable in a model to predict a numerical variable Y. Because I am working with some legislative data, my challenge is my category code is over 4000 unique values, those values that cannot be easily grouped(*), and they can have repeated. In fact, anecdotally I know that both that code is a strong predictor of Y and repeats in a code per row will have a stronger impact on Y.

My attempt at a solution: In fact, I tried to do this myself thinking that maybe something as simple as mean target times the count of each code (Table B * Table C). But, I am uncertain if this is a valid type of encoding.

A = sample legislative data to predict ethics variable Y, the values have been anonymized

+-----+-----------+------------+
| row | Category  | Y variable |
+-----+-----------+------------+
|   2 | 15-11-2   |        370 |
|   2 | 15-11-106 |        370 |
|   2 | 15-5-44   |        370 |
|   2 | 15-11-41  |        370 |
|   3 | 15-7-23   |        363 |
|   3 | 15-7-23   |        363 |
|   3 | 15-11-37  |        363 |
|   3 | 15-11-37  |        363 |
|   3 | 15-5-21   |        363 |
|   6 | 15-11-106 |        287 |
|   6 | 15-5-21   |        287 |
|   7 | 16-5-70   |        931 |
|   7 | 15-5-23   |        931 |
|   7 | 15-5-41   |        931 |
|   9 | 15-11-7   |        336 |
|   9 | 15-2-30   |        336 |
|   9 | 40-11-76  |        336 |
|   9 | 15-2-2    |        336 |
|   9 | 15-11-131 |        336 |
|   9 | 15-11-131 |        336 |
|  10 | 15-2-30   |        350 |
|  10 | 15-11-131 |        350 |
+-----+-----------+------------+

B = The Mean Values for the unique rows

+-----------+-----------------------+
| Category  | Average of Y variable |
+-----------+-----------------------+
| 15-11-106 | 334.714               |
| 15-11-2   | 298.994               |
| 15-11-37  | 251.931               |
| 15-11-41  | 399.210               |
| 15-11-7   | 288.021               |
| 15-2-2    | 234.533               |
| 15-2-30   | 303.127               |
| 15-5-21   | 317.999               |
| 15-5-23   | 252.109               |
| 15-5-41   | 300.882               |
| 15-5-44   | 499.405               |
| 15-5-70   | 277.306               |
| 15-7-23   | 244.976               |
| 40-11-76  | 261.938               |
| 15-11-131 | 319.799               |
+-----------+-----------------------+

Table C The counts of each category

+-----+-----------+-------------------+
| row | Category  | Count of Category |
+-----+-----------+-------------------+
|   2 | 15-11-106 |                 1 |
|   2 | 15-11-2   |                 1 |
|   2 | 15-11-41  |                 1 |
|   2 | 15-5-44   |                 1 |
|   3 | 15-11-37  |                 2 |
|   3 | 15-5-21   |                 1 |
|   4 | 15-7-23   |                 2 |
|   4 | 16-8-18   |                 1 |
|   5 | 16-7-1    |                 1 |
|   6 | 16-8-2    |                 1 |
|   6 | 15-11-106 |                 1 |
|   7 | 15-5-21   |                 1 |
|   7 | 15-5-23   |                 1 |
|   8 | 15-5-41   |                 1 |
|   8 | 16-5-70   |                 1 |
|   8 | 16-11-37  |                 1 |
|   8 | 16-7-21   |                 1 |
|   9 | 15-11-131 |                 2 |
|   9 | 15-11-7   |                 1 |
|   9 | 15-2-2    |                 1 |
|   9 | 15-2-30   |                 1 |
|   9 | 40-11-76  |                 1 |
|  10 | 15-11-131 |                 1 |
|  10 | 15-2-30   |                 1 |
|  10 | 40-11-76  |                 1 |
|     |           |                   |
+-----+-----------+-------------------+

Footnote: (*) What I mean by "the category code cannot be easily grouped" is that 15-11-2 and 15-11-1 cannot be group under 15-11 or 15. I have also tried clustering.

$\endgroup$

1 Answer 1

0
$\begingroup$

Yes, you can do target encoding. watch this video from @2:18 to @3:50. Hope this helps.

Update: Table B is enough to encode, you have mean values of y among each category. If a responce has like this [15-11-106 ,15-11-2], then you can replace those values with them [334.714, 298.994], now you can apply any statistic to get final encoding value, whether it could be mean/mode/median etc... of those values.

The mean target times the count of each code (Table B * Table C) is nothing but the sum of the target variable for each unique category(instead of mean of target variable you are going to choose sum of target variable). I don't think it's a good strategy.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.