7 Replies Latest reply on Aug 30, 2012 5:55 PM by PeterW45

    Calculating tennis player ratings from game results

    PeterW45

      Title

      Calculating tennis player ratings from game results

      Post

      It should be clear that I am not too astute regarding understanding Filemaker Pro, nor am I diligent in developing solutions (its been over a year). 

      I had posted elements of my database creation Help with tennis database set up and calculations and Script to use a find result as source for new search. I am able to track who is playing with whom, the players, and sort the ratio of number of times they have played to the number of times they have brought balls. Not pretty, but so far, so good. 

      Now, I would like to calculate an Elo type of rating for each player. A little more detail. We play doubles and change partners after every set. For the Elo rating, I plan to calculate the expentency of winning for each team by calculating a team average and do the calculations from that. 

      I have collected game results in a table with each player's results.

      I did not know how to handle this data, so I exported a fraction of this data and imported it into Excel. In Excel, I changed the data so each set was repeated for each match (set_1, set_2, and set_3) and the scores for each set. I then manually calculated the Expected result (1/(1+10^(opponent rating-home rating)/400). Initially, each teams rating is equal, so each team has a 1/2 or 50% change of winning. From that result, the winners receive a new rating (old rating = 32*(1-expected). This means I averaged each teams rating, and subtracted rating_1 from rating_2. 

      Excel let me verify the rating system and suggested how I might alter the calculations. Accomplishing this same calculation in Filemaker is a completely different task. Can the calculation be carried out with the table I collect the data from or should it be transferred to a more efficient table? (I changed the table structure for the calculations in Excel.) In any case, I have no idea about how to change the table structure or to get Filemaker to do the calculations.

      I didn't know how to get Excel to do it either and so I just manually selected which cells to use in the calculations. I rather suspect that I will not understand the jargon used to do the calculations. In one of my earlier posts, what I discovered was the jargon describing how to use the result from one seach to use in another search. I found how someone had succeeded in doing this and modified their script. (It is a lot easier to modify a working script than write one on my own.) What I am hoping is someone might understand the basics of how the data must be manipulated to suggest where I may find a solution or how to search for a solution. Players may be widgets and scores may be costs and sets may be orders or ??? Suggestions?

      tennis_DB.png

        • 1. Re: Calculating tennis player ratings from game results
          PeterW45

          Let me add to and be more specific about this problem. I realize the match result table is best handled with a single table and different set numbers (thanks Phil). So I want to convert the set scores from the match table (above) to a set table (below). 

          Looking at some examples leads me to, how do I accomplish the following? From the Match table, I should be able to lookup who played from a MatchID. (I found an example in which address data was looked up from a customer ID.) In this instance, I want to lookup and create four records for each MatchID. How do I do that?

          Secondly, I want to look up and create an additional set of records for each SetID. However, the SetID is obtained from a different field in the Match table. I need to reread the Match table to get the same MatchID, hence PlayerIDs, and create four more records with a different SetID

          I hope, that despite all of the tennis discussions, someone can suggest how to answer the underlined questions (or how I might repost the underlined as separate questions)? 

          • 2. Re: Calculating tennis player ratings from game results
            philmodjunk

            Can you upload a screen shot of Manage | Database | Relationships to show the most recent design for your database? I'm not sure from the links that you shared what final design you ended up with.

            I'm looking at the screen shot in your last post and am not sure that I understand what you have

            The first row shows exactly the same data in the first three fields. Does this mean that you have one record for each doubles partner? What does the 1 in the ::Won column represent? the ID of a doubles team record or, an individual player ID or .... ?

            • 3. Re: Calculating tennis player ratings from game results
              PeterW45

              Thanks Phil for taking a look at my problem. The SetID in the second screenshot is the set number, Set_1 corresponds with the first set score, etc. Won is games won, so F and D won the first set 6-1 over W and G. In the second set, W and D won 6-3 over F and G.

              The two screenshots do show the same data. It is easier to enter the scores in the first table, but I think calculatons may be easier to perform in the scond. 

              Below is a screenshot of the relationships. If it looks confusing, it is because I really don't know what I am doing. I just try stuff and try to figure out how to make things work. 

              • 4. Re: Calculating tennis player ratings from game results
                philmodjunk

                I would guess that the layout I was questioning earlier is based on Sets_PlaerID_Matches?

                and ::Sets refers to the Sets field in Sets_SetID?

                And ::Won refers to the Won field in Sets_Score?

                If so, the last one seems problematic.

                • 5. Re: Calculating tennis player ratings from game results
                  PeterW45

                  Thanks Phil. I can disconnect that relationship, but that only removes the games won from the table. 

                  Let's look at this one step at a time. If I enter a MatchID number that has data in the Matches table, it does look up the first PlayerID (and enters their score from Set_1). If I create another record and enter the same MatchID, it adds the same data again. How do I get the other PlayerIDs or how do I write a script that will enter four records (or as many records as there is data) for each MatchID? 

                  I can foresee an alternative that if the Set table contained the SetID, MatchID, and PlayerID, I could enter the scores directly in this table. If that were the case, I could abandon entering scores into the Matches table. This could easily be predicated by having a button that enters the SetIDs for four records that I then enter. For example, "Two" would create four records with SetID =2 for the current MatchID (or something like that). 

                  • 6. Re: Calculating tennis player ratings from game results
                    philmodjunk

                    YOu never answered my questions. I need to know the context here. And does your new post also refer to sets_PlayerID_Matches?

                    • 7. Re: Calculating tennis player ratings from game results
                      PeterW45

                      I would guess that the layout I was questioning earlier is based on Sets_PlaerID_Matches?

                      Yes

                      and ::Sets refers to the Sets field in Sets_SetID?

                      Yes

                      And ::Won refers to the Won field in Sets_Score?

                      Yes

                      If so, the last one seems problematic.

                      I don't know. I disabled it and it simply dropped that field from the table.  

                      And does your new post also refer to sets_PlayerID_Matches?

                      Yes