Dealing with mixed categorical data: e.g. Heritage Health Prize data Looking at the Heritage Health Prize, the data is structured as follows:

Each of the Data Sets will be comprised of tables as follows:

a. Members Table, which will include:
    i. MemberID (a unique member ID)
    ii. AgeAtFirstClaim (member's age when first claim was made in the Data Set period)
    iii. Sex 
b. Claims Table, which will include:
    i. MemberID
    ii. ProviderID (the ID of the doctor or specialist providing the service)
    iii. Vendor (the company that issues the bill)
    iv. PCP (member's primary care physician)
    v. Year (the year of the claim, Y1, Y2, Y3)
    vi. Specialty
    vii. PlaceSvc (place where the member was treated)
    viii. PayDelay (the delay between the claim and the day the claim was paid for)
    ix. LengthOfStay
    x. DSFS (days since first service that year)
    xi. PrimaryConditionGroup (a generalization of the primary diagnosis codes)
    xii. CharlsonIndex (a generalization of the diagnosis codes in the form of a categorized comorbidity score)
    xiii. ProcedureGroup (a generalization of the CPT code or treatment code)
    xiv. SupLOS (a flag that indicates if LengthOfStay is null because it has been suppressed) 
c. Labs Table, which will contain certain details of lab tests provided to members.
d. RX Table, which will contain certain details of prescriptions filled by members.
e. DaysInHospital Tables - Y2 and Y3, which will contain the number of days of hospitalization for each eligible member during Y2 and Y3 and will include:
    i. MemberID;
    ii. ClaimsTruncated (a flag for members who have had claims suppressed. If the flag is 1 for member xxx in DaysInHospital_Y2, some claims for member xxx will have been suppressed in Y1).
    iii. DaysInHospital (the number of days in hospital Y2 or Y3, as applicable). 
These two Tables are intended for use by Entrants to train and validate their algorithms. DaysInHospital Tables are based on the Claims Table with admissions in Y2 or Y3, as applicable. As a privacy measure, any member who spent more than two weeks in hospital is grouped; they are treated as though they spent 15 days in hospital.
f. Target - is "DaysInHospital_Y4" but doesn't include DaysInHospital. DaysInHospital data for Y4 are to be filled in by Entrants to produce entries. Seem SampleEntry.csv as an example. 

So the goal is to predict the values in table f.
Clearly the first thing to do is to do a JOIN across the tables using member IDs. 
Some of these appear to be standard discrete variables (age, sex etc) but others are much more tricky to deal with. For example, the labs index and RX index point to other tables. Also, there are many categorical types. Some of the categorical types (such as PrimaryConditionGroup) have many possible values. Normally for categorical data, these can be made into features using a binary flag for each category (simply using the index will fail as there is no ordering). However when there are many thousands of these this seems like an inefficient approach. Are there any better ways to deal with the mixture of numerical and categorical data such as this?
 A: Here's the approach I used: Any categorical variables with fewer than about 50 levels were turned into binary variables.  For categorial variables with greater than 50 levels, I also split them into binary variables, but only kept the 50 most "predictive" levels, using a simple filter.
This is a form of feature selection, so it isn't optimal, and should be included within any cross-validation you do to validate your algorithm.
Note that the team that won milestone 1 (currently in 2nd place) posted their SQL code to create the "analysis dataset" by merging and aggregating all of those tables.  It's a great place to start.
A: I haven't looked at this specific data set, but from a project I was involved in a long time ago, we had a questionnaire response that was the specific drugs a patient took. Given the number of possible drugs, turning each into a category was not feasible. I broke out several discrete features from this varying length set: number of prescriptions, presence of certain prescriptions and the ratio of those prescriptions to all the prescriptions taken. In my project, if someone took a narcotic and three non-narcotics, they would have f_total = 4, f_narcotics = 1, f_ratio = 0.3. I used the Physician's Desk Reference and a list of the 100 most prescribed drugs to do an estimate of these values (since I didn't have access to a database of all prescription drugs). I'm not sure if those features are relevant for the Heritage Health competition, but I presume that determining the relevant features is the added-value they are looking for.
