How to prepare categorical predictor values that are comma separated but in the same table column? I have collected a information on a bunch of movies. I'm especially interested in the actors, writers and directors and their relationship to the box office and rating of the movie. I'm still preprocessing the data. I'm using XLMiner Platform for Excel to do small scale analysis of the data.
My outcome variable is movie rating or box office and I have chosen to build a predictive model.
My question is, some of the fields in the actor, director and writer columns have multiple names in them. Transforming them to dummies isn't realistic (nor does XLMiner support that amount of dummies). 
Would it be better to:


*

*Take the first actor/director/writer and remove the rest.

*Add extra fields such as Actor1, Actor2, Actorx, Director1, 2 and so on.

*Obtain a list of "popular" actors, writers and director and transform the predictors to a binary "is/is not popular actor"

*Some other method?


Any advice would be appreciated! English is not my native language, and I'm new to the analysis domain, so I couldn't figure out how to search for methods to solve problems like these.
 A: You haven't said how you are going to parse these comma delimited fields into separate mentions for each name. I've assumed you have a method for doing that. 
Given that, there is no single "right" answer to how to process this information for analysis as there are trade-offs with each. The chances are that any of the approaches you cite could be used. Here are some thoughts about them:
1) First mention: this would assume some sort of implicit ranking to the names insofar as the first name should be most "important" to the film with the last name being the least important. Using this approach would, in effect, reify this ranking, if it exists -- a ranking that could be verified with subsequent analysis
2) Adding extra fields: This might work as you could limit the count of fields for each role to a manageable number. My question is what would the resulting fields mean since "Actor 1" would be different for each film? The highly likely presence of many singletons in the categories would be a challenge to calibration. To the extent that there is considerable overlap across films, it would be a viable approach, e.g., insofar as "Tom Hanks" is mentioned across multiple properties
3) Is/is not popular: This would work but how is "popularity" to be measured? In my view, this approach would be a blunt instrument that loses the specificity available from retaining the actual names
Another suggestion might be to borrow a page from computational linguistics and create a TF-IDF measure for each specific name mentioned (the "TF") and treat each film as a "document" (the "IDF"). TF-IDF calculations are explained here, among many other sources:
https://en.wikipedia.org/wiki/Tf%E2%80%93idf
The obvious limitation with this approach is XLMiner, as you've noted. If you are able to step outside of the XLMiner framework, you would obviously have a great deal more latitude in your analysis. R or Python would be additional options. I don't see why you wouldn't be able to do this (since they are freeware) but I'm not doing the analysis.
So, if you're stuck with XLMiner then the most viable approach I can think of is to agree that "adding extra fields" is the way to go.
