CODEWITHSUNDEEP
excelcategorical-datadummy-variable
Uri Goren
Uri Goren

Reputation: 13700

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 ?

Upvotes: 0

Views: 488

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

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

Upvotes: 1

Ryland Moyar
Ryland Moyar

Reputation: 25

I am not sure what the end goal is, but I would suggest instead of an array (again, assuming you are not utilizing VBA, and even if you are, this is very possible with Case functions):

Use the formula =Weekday(Cell,different return type for which day should = 1)

If you were to use the actual date (e.g. 5/1/2020), display it through custom formatting with only the day of the week (typically data analysis will already have the full date),

Cell Value A20 = "5/1/2020", format display Long Date - "Friday, May 1, 2020"

cell referencing with formula '=WEEKDAY(A20,1)' = 6

cell referencing with formula '=WEEKDAY(A20,2)' = 5 (is this similar to {0,0,0,0,1,0,0} enough for you to use?)

if using VBA, you could define a range with logic to turn this into {0,0,0,0,1,0,0}

Hope this helps.

Upvotes: 0

Related Questions