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. What is the solution you would recommend?

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:

1. (using pandas) create pandas dataframe from csv and groupby A-B-E-F-G then sum C,D
2. (programmatically) loop through resulting dataframe, apply D/C sums, sort and generate ranking in dictionary applying limit 5 condition, making other cases empty
3. (using pandas) import dictionary into a new dataframe, train and test model

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

The more entries are saved to dataset, the more accurate prediction will become. Is this a machine learning issue after all? I managed to solve problem using Decision Tree Classifier by precalculating results through a new dataset that becomes:

A | B | E | F | G

But this is no longer true machine learning... Are statistics based predictions possible? If so, what are the algorithms I should use for above use case

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. What is the solution you would recommend?