Search code examples
excelcategorical-datadummy-variable

Categorical column in to a dummy array in Excel

I'm trying to translate a machine-learning model into excel, so that data analysts could play with it interactively.

I'd like to transform a categorical variable into dummy representation:

WeekDay
Monday
Thursday

to

WeekDay
{1,0,0,0,0,0,0}
{0,0,0,1,0,0,0}

Using excel arrays.

I tried this:

={INT(A1="Monday"),INT(A1="Tuesday"),INT(A1="Wednesday"), ...}

However, for some reason, excel doesn't accept forumlas in array expressions.

This approach does work, but it is problematic - since it does not allow combinig multiple arrays into one

=IF(A1="Monday", {1,0,0,0,0,0,0}, IF(A1="Tuesday", {0,1,0,0,0,0,0}, ....))

Also, it's super ugly

Any ideas ?

Solution

  • To get your array you can use INDEX like this:

    INDEX(IF(TEXT(ROW($2:$8),"dddd")=A1,1,0),0)

    This returns a vertical array.

    to return a horizontal array use:

    INDEX(IF(TEXT(COLUMN($B:$H),"dddd")=A2,1,0),0)

    I have spilled the results of the array in the photo below:

    enter image description here

    If one has the Dynamic Array Formula SEQUENCE the ROW and COLUMN can be replaced with:

    SEQUENCE(7,,2)

    and 

    SEQUENCE(,7,2)

    Respectively