Football on the plus, part 2: practical

Despite some skepticism, about the first part of the material , as promised, I publish the results, and how they were received.
Mainly, manipulations were performed with data stored in Excell, the tables of which are generated according to 3NF, therefore in some places of the code, data from cells is used instead of indexes. So, according to the algorithm, it is necessary to get the club coefficient and the climate characteristic of the city in which this club accepts opponents = team: {climate, rating} - this is the main goal. Go.

Stage 1:

The authorized person or System randomly selects the number N within the number of representatives of the participating teams - from 1 to 16, ordered according to the alphabet. The selected participant is invited to calculate the random team weight rating.

TeamSequence = new List<int>(); for (int i = 0; i < teams.Length; i++) { int team_ambassor = generator.Next(1000, 1000000)%teams.Length; while (TeamSequence.Contains(team_ambassor)) team_ambassor = generator.Next(1000, 1000000)%teams.Length; TeamSequence.Add(team_ambassor); } foreach (int i in TeamSequence) textBox1.Text += "  : " + teams[i] + "\r\n"; 

For the representative, data on the participants of the competition has already been formed, which of course he does not see, as well as the name of the team for which the calculation is made:

Stage 2

Since I did not have the opportunity to invite any of the experts, I made the assignment of estimates by parameters random, but in order to bring it closer to reality, I carried out this procedure 8 times exactly in terms of the number of estimated indicators.

  double[,] rate = new double[16,8]; int team=new int(); DataTable table = new DataTable(); List<int> Marklist = new List<int>(); string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"", "C:\\team.xlsx"); using (OleDbConnection dbConnection = new OleDbConnection(strConn)) { using (OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT * FROM [3$]", dbConnection)) dbAdapter.Fill(table); for (int j = 0; j < 8; j++) { team = 0; foreach (DataRow dr in table.Rows) { for (int i = 9; i < 17; i++) { int mark = 1 + generator.Next(10000, 10000000)%8; while (Marklist.Contains(mark)) mark = 1 + generator.Next(10000, 10000000)%8; Marklist.Add(mark); rate[team, i - 9] += Convert.ToInt32(dr.ItemArray[i])*mark; } team++; Marklist.Clear(); } } dbConnection.Close(); } 

Storing data in xlsx files is quite convenient, since Some trivial arithmetic can be performed right inside the data tables.
After completing a random assessment of indicators, objective characteristics of the participants were obtained.

The results were quite logical.
Let me remind you that the optimization is performed by the minimum criterion, i.e. less = better.

Stage 3

It remains to the coefficient of the club, add a climatic characteristic of the city. These characteristics were obtained for each month, in which the tournament is held: July-December, March-May. The data is quite cumbersome and I don’t see any reason to bring them.
We calculate the final characteristics:

  double[,] rate = new double[12,9]; int team=new int(); DataTable table = new DataTable(); List<int> Marklist = new List<int>(); string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"", "C:\\team.xlsx"); using (OleDbConnection dbConnection = new OleDbConnection(strConn)) { //   , 12 , 9  using (OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT * FROM [5$]", dbConnection)) dbAdapter.Fill(table); team = 0; foreach (DataRow dr in table.Rows) { { rate[Convert.ToInt32(dr.ItemArray[0]) - 1, team%9] = Convert.ToDouble(dr.ItemArray[6]); team++; } } dbConnection.Close(); } table = new DataTable(); double[,] team_rate = new double[16,9]; team = 0; using (OleDbConnection dbConnection = new OleDbConnection(strConn)) { //    = 16 . using (OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT * FROM [6$]", dbConnection)) dbAdapter.Fill(table); foreach (DataRow dr in table.Rows) { for (int i = 0; i < 9; i++) { team_rate[team%16, i] = rate[Convert.ToInt32(dr.ItemArray[0]) - 1, i]* Convert.ToDouble(dr.ItemArray[1]); } team++; } dbConnection.Close(); } StreamWriter sw = new StreamWriter("teams_koeff.txt"); for (int i = 0; i <16; i++) { sw.WriteLine(Convert.ToString(teams[i])); for (int j = 0; j < 9; j++) { sw.Write(Convert.ToString(month[j]+"\t")); sw.Write(team_rate[i, j]+"\t"); } sw.WriteLine(); } sw.Close(); 

Is done. A bit of conversion will result in the following table:

As can be seen, the Kuban and Krasnodar ratings are different, although they are from the same city, the Moscow clubs have a similar situation. Based on this information, you can optimize the procedure for the draw. Of course, various force majeure, European Cup games, national teams, police recommendations, etc., will need to be taken into account manually, but the overall grid framework will already be formed.
For example, compare the published calendar and our table:

If you do not take into account 2 Moscow pairs, then about 50% of the "quality". However, in some pairs, the coefficient differs by an order !!! Someone may say that, in principle, “everything is fine”, but definitely it would be possible to form a calendar so that (1 table) Terek played with Tomyu, and Krasnodar with Ruby. Whatever one may say, it is cool to run and run in Siberia in November, let alone look at the podium.
Thank! As always, I welcome your comments.