Join Two Data Frames,
Team , Captain, Match_ID
A, Jack, 13
A, Jack, 14
A, Sam, 15
B, Nancy, 13
B, Rob, 14
B, Job, 15
Team_L , Team_R , Match_ID,Match_Date, Winning Team
A B 13 12011018 A
A B 15 13011018 B
Output DataFrame must have, Team_L , Team_R , Match_ID,Match_Date, Winning Team,
and the 6 additional fields, WinRationOfTeam_L , WinRationOfTeam_R, WinRationOfTeam_L_VS_R , WinRationOfCaptain_L, WinRationOfCaptain_R, WinRatio_Captain_L_VS_R
Let Say ,
WinRationOfTeam_L Team L Played 15 matches with any, and won 10 then Team A win Ration is 10/15
WinRationOfCaptain_L Team L with 'Jack ? ' as captain Played 4 matches with any, and won 1 then Team A win Ration is 1/4 = 0.25
WinRationOfTeam_L_VS_R Team A Played 5 matches with Team R, and won 3 then Team A win Ration is 3/4 = 0.75
WinRatio_Captain_L_ON_R Team A played 3 with Captain Jack played with Team B with Rob as Captain , and won 2 then WinRatio_Captain_L_ON_R =2/3 =0.66
To Give a perspective, Country A Football team, won 85 out of 100 matches, winRatio is 0.85 Dan as the captain for Team_A and played 20 matches, and won 10 out of 20. WinRtion of Captain is 10/20 which is 0.5.
Team A played with Team B 10 times, Team A won 6 , Now if Team A is in the output dataframe under column Team_L, then WinRationOfTeam_L_VS_R is 6/10 =0.6
Dan as the captain for Team_A and played 4 with Jim as captain of Team B, and won 3. Then WinRatio_Captain_L_VS_R is 3/4 = 0.75