Let's say we have a dataset with these columns:
A | B | C | D | E | F | G
I want to predict [E,F,G] based on [A,B] with following rule: top 5 entries order by (SUM(D)/SUM(C)) desc
Pseudo-SQL query representation for above rule for a A-B combination:
SELECT
E, F, G, (SUM(D)/SUM(C)) AS ctr
FROM dataset
WHERE A=... AND B=...
GROUP BY E, F, G
ORDER BY ctr DESC
LIMIT 5
I managed to partially solve problem using Decision Tree Classifier by pre-calculating results through a new dataset that becomes:
A | B | E | F | G
But DecisionTreeClassifier in Python can only predict ONE random E-F-G combination out of 5 possible variants in new dataset for a A-B pair:
left = df.drop(columns=["E", "F", "G"])
right = df.drop(columns=["A","B"])
model = DecisionTreeClassifier(random_state=1)
model.fit(left, right)
# no point splitting into train & test sets because model has pre-calculated results
predictions = model.predict([ [3,1]])
print(predictions)
What should be the right path to get the 5 variants for A-B pair I save in dataset (without LabelEncoder)?
Also periodically we add new entries to dataset and the more this "database" grows the more feature engineering will become more costly.
UPDATE
Issue solved by feature engineering and ranking:
A | B || C | D || E | F | G
Was converted to:
A | B | RANK || E | F | G
So DecisionTreeClassifier is able to get 5 suggestions expected:
trainedModel.predict([ [9,21,1], [9,21,2], [9,21,3], [9,21,4], [9,21,5] ]))
Steps to solve similar cases:
- (using pandas) create pandas dataframe from csv and groupby A-B-E-F-G then sum C,D
- (programmatically) loop through resulting dataframe, apply D/C sums, sort and generate ranking in dictionary applying limit 5 condition, making other cases empty
- (using pandas) import dictionary into a new dataframe, train and test model