library('dplyr')
library('glue')
library('plyr')
library('stringr')Reading in our table using read.delim as it is
pipe-delimited. We’ll skip the comment lines (i.e., “—…”) and we’ll also
need to manipulate this dataframe so each player has one associated row
with all their information.
github_url <- "https://raw.githubusercontent.com/andrewbowen19/cunyDATA607/main/projects/project1/tournamentinfo.txt"
df <- read.delim(github_url, sep="|", comment.char="-")
df## Pair Player.Name Total Round Round.1 Round.2
## 1 Num USCF ID / Rtg (Pre NA
## 2 1 GARY HUA 6.0 W 39 W 21 W 18
## 3 ON 15445895 / R: 1794 NA
## 4 2 DAKSHESH DARURI 6.0 W 63 W 58 L 4
## 5 MI 14598900 / R: 1553 NA
## 6 3 ADITYA BAJAJ 6.0 L 8 W 61 W 25
## 7 MI 14959604 / R: 1384 NA
## 8 4 PATRICK H SCHILLING 5.5 W 23 D 28 W 2
## 9 MI 12616049 / R: 1716 NA
## 10 5 HANSHI ZUO 5.5 W 45 W 37 D 12
## 11 MI 14601533 / R: 1655 NA
## 12 6 HANSEN SONG 5.0 W 34 D 29 L 11
## 13 OH 15055204 / R: 1686 NA
## 14 7 GARY DEE SWATHELL 5.0 W 57 W 46 W 13
## 15 MI 11146376 / R: 1649 NA
## 16 8 EZEKIEL HOUGHTON 5.0 W 3 W 32 L 14
## 17 MI 15142253 / R: 1641P17 NA
## 18 9 STEFANO LEE 5.0 W 25 L 18 W 59
## 19 ON 14954524 / R: 1411 NA
## 20 10 ANVIT RAO 5.0 D 16 L 19 W 55
## 21 MI 14150362 / R: 1365 NA
## 22 11 CAMERON WILLIAM MC LEMAN 4.5 D 38 W 56 W 6
## 23 MI 12581589 / R: 1712 NA
## 24 12 KENNETH J TACK 4.5 W 42 W 33 D 5
## 25 MI 12681257 / R: 1663 NA
## 26 13 TORRANCE HENRY JR 4.5 W 36 W 27 L 7
## 27 MI 15082995 / R: 1666 NA
## 28 14 BRADLEY SHAW 4.5 W 54 W 44 W 8
## 29 MI 10131499 / R: 1610 NA
## 30 15 ZACHARY JAMES HOUGHTON 4.5 D 19 L 16 W 30
## 31 MI 15619130 / R: 1220P13 NA
## 32 16 MIKE NIKITIN 4.0 D 10 W 15 H
## 33 MI 10295068 / R: 1604 NA
## 34 17 RONALD GRZEGORCZYK 4.0 W 48 W 41 L 26
## 35 MI 10297702 / R: 1629 NA
## 36 18 DAVID SUNDEEN 4.0 W 47 W 9 L 1
## 37 MI 11342094 / R: 1600 NA
## 38 19 DIPANKAR ROY 4.0 D 15 W 10 W 52
## 39 MI 14862333 / R: 1564 NA
## 40 20 JASON ZHENG 4.0 L 40 W 49 W 23
## 41 MI 14529060 / R: 1595 NA
## 42 21 DINH DANG BUI 4.0 W 43 L 1 W 47
## 43 ON 15495066 / R: 1563P22 NA
## 44 22 EUGENE L MCCLURE 4.0 W 64 D 52 L 28
## 45 MI 12405534 / R: 1555 NA
## 46 23 ALAN BUI 4.0 L 4 W 43 L 20
## 47 ON 15030142 / R: 1363 NA
## 48 24 MICHAEL R ALDRICH 4.0 L 28 L 47 W 43
## 49 MI 13469010 / R: 1229 NA
## 50 25 LOREN SCHWIEBERT 3.5 L 9 W 53 L 3
## 51 MI 12486656 / R: 1745 NA
## 52 26 MAX ZHU 3.5 W 49 W 40 W 17
## 53 ON 15131520 / R: 1579 NA
## 54 27 GAURAV GIDWANI 3.5 W 51 L 13 W 46
## 55 MI 14476567 / R: 1552 NA
## 56 28 SOFIA ADINA STANESCU NA
## 57 MI 14882954 / R: 1507 NA
## 58 29 CHIEDOZIE OKORIE 3.5 W 50 D 6 L 38
## 59 MI 15323285 / R: 1602P6 NA
## 60 30 GEORGE AVERY JONES 3.5 L 52 D 64 L 15
## 61 ON 12577178 / R: 1522 NA
## 62 31 RISHI SHETTY 3.5 L 58 D 55 W 64
## 63 MI 15131618 / R: 1494 NA
## 64 32 JOSHUA PHILIP MATHEWS 3.5 W 61 L 8 W 44
## 65 ON 14073750 / R: 1441 NA
## 66 33 JADE GE 3.5 W 60 L 12 W 50
## 67 MI 14691842 / R: 1449 NA
## 68 34 MICHAEL JEFFERY THOMAS 3.5 L 6 W 60 L 37
## 69 MI 15051807 / R: 1399 NA
## 70 35 JOSHUA DAVID LEE 3.5 L 46 L 38 W 56
## 71 MI 14601397 / R: 1438 NA
## 72 36 SIDDHARTH JHA 3.5 L 13 W 57 W 51
## 73 MI 14773163 / R: 1355 NA
## 74 37 AMIYATOSH PWNANANDAM 3.5 B L 5 W 34
## 75 MI 15489571 / R: 980P12 NA
## 76 38 BRIAN LIU 3.0 D 11 W 35 W 29
## 77 MI 15108523 / R: 1423 NA
## 78 39 JOEL R HENDON 3.0 L 1 W 54 W 40
## 79 MI 12923035 / R: 1436P23 NA
## 80 40 FOREST ZHANG 3.0 W 20 L 26 L 39
## 81 MI 14892710 / R: 1348 NA
## 82 41 KYLE WILLIAM MURPHY 3.0 W 59 L 17 W 58
## 83 MI 15761443 / R: 1403P5 NA
## 84 42 JARED GE 3.0 L 12 L 50 L 57
## 85 MI 14462326 / R: 1332 NA
## 86 43 ROBERT GLEN VASEY 3.0 L 21 L 23 L 24
## 87 MI 14101068 / R: 1283 NA
## 88 44 JUSTIN D SCHILLING 3.0 B L 14 L 32
## 89 MI 15323504 / R: 1199 NA
## 90 45 DEREK YAN 3.0 L 5 L 51 D 60
## 91 MI 15372807 / R: 1242 NA
## 92 46 JACOB ALEXANDER LAVALLEY 3.0 W 35 L 7 L 27
## 93 MI 15490981 / R: 377P3 NA
## 94 47 ERIC WRIGHT 2.5 L 18 W 24 L 21
## 95 MI 12533115 / R: 1362 NA
## 96 48 DANIEL KHAIN 2.5 L 17 W 63 H
## 97 MI 14369165 / R: 1382 NA
## 98 49 MICHAEL J MARTIN 2.5 L 26 L 20 D 63
## 99 MI 12531685 / R: 1291P12 NA
## 100 50 SHIVAM JHA 2.5 L 29 W 42 L 33
## 101 MI 14773178 / R: 1056 NA
## 102 51 TEJAS AYYAGARI 2.5 L 27 W 45 L 36
## 103 MI 15205474 / R: 1011 NA
## 104 52 ETHAN GUO 2.5 W 30 D 22 L 19
## 105 MI 14918803 / R: 935 NA
## 106 53 JOSE C YBARRA 2.0 H L 25 H
## 107 MI 12578849 / R: 1393 NA
## 108 54 LARRY HODGE 2.0 L 14 L 39 L 61
## 109 MI 12836773 / R: 1270 NA
## 110 55 ALEX KONG 2.0 L 62 D 31 L 10
## 111 MI 15412571 / R: 1186 NA
## 112 56 MARISA RICCI 2.0 H L 11 L 35
## 113 MI 14679887 / R: 1153 NA
## 114 57 MICHAEL LU 2.0 L 7 L 36 W 42
## 115 MI 15113330 / R: 1092 NA
## 116 58 VIRAJ MOHILE 2.0 W 31 L 2 L 41
## 117 MI 14700365 / R: 917 NA
## 118 59 SEAN M MC CORMICK 2.0 L 41 B L 9
## 119 MI 12841036 / R: 853 NA
## 120 60 JULIA SHEN 1.5 L 33 L 34 D 45
## 121 MI 14579262 / R: 967 NA
## 122 61 JEZZEL FARKAS 1.5 L 32 L 3 W 54
## 123 ON 15771592 / R: 955P11 NA
## 124 62 ASHWIN BALAJI 1.0 W 55 U U
## 125 MI 15219542 / R: 1530 NA
## 126 63 THOMAS JOSEPH HOSMER 1.0 L 2 L 48 D 49
## 127 MI 15057092 / R: 1175 NA
## 128 64 BEN LI 1.0 L 22 D 30 L 31
## 129 MI 15006561 / R: 1163 NA
## Round.3 Round.4 Round.5 Round.6 X
## 1 NA
## 2 W 14 W 7 D 12 D 4 NA
## 3 NA
## 4 W 17 W 16 W 20 W 7 NA
## 5 NA
## 6 W 21 W 11 W 13 W 12 NA
## 7 NA
## 8 W 26 D 5 W 19 D 1 NA
## 9 NA
## 10 D 13 D 4 W 14 W 17 NA
## 11 NA
## 12 W 35 D 10 W 27 W 21 NA
## 13 NA
## 14 W 11 L 1 W 9 L 2 NA
## 15 NA
## 16 L 9 W 47 W 28 W 19 NA
## 17 NA
## 18 W 8 W 26 L 7 W 20 NA
## 19 NA
## 20 W 31 D 6 W 25 W 18 NA
## 21 NA
## 22 L 7 L 3 W 34 W 26 NA
## 23 NA
## 24 W 38 H D 1 L 3 NA
## 25 NA
## 26 D 5 W 33 L 3 W 32 NA
## 27 NA
## 28 L 1 D 27 L 5 W 31 NA
## 29 NA
## 30 L 22 W 54 W 33 W 38 NA
## 31 NA
## 32 W 39 L 2 W 36 U NA
## 33 NA
## 34 L 2 W 23 W 22 L 5 NA
## 35 NA
## 36 W 32 L 19 W 38 L 10 NA
## 37 NA
## 38 D 28 W 18 L 4 L 8 NA
## 39 NA
## 40 W 41 W 28 L 2 L 9 NA
## 41 NA
## 42 L 3 W 40 W 39 L 6 NA
## 43 NA
## 44 W 15 H L 17 W 40 NA
## 45 NA
## 46 W 58 L 17 W 37 W 46 NA
## 47 NA
## 48 L 25 W 60 W 44 W 39 NA
## 49 NA
## 50 W 24 D 34 L 10 W 47 NA
## 51 NA
## 52 L 4 L 9 D 32 L 11 NA
## 53 NA
## 54 W 37 D 14 L 6 U NA
## 55 NA
## 56 NA
## 57 NA
## 58 L 34 W 52 W 48 U NA
## 59 NA
## 60 W 55 L 31 W 61 W 50 NA
## 61 NA
## 62 L 10 W 30 W 50 L 14 NA
## 63 NA
## 64 L 18 W 51 D 26 L 13 NA
## 65 NA
## 66 D 36 L 13 L 15 W 51 NA
## 67 NA
## 68 W 29 D 25 L 11 W 52 NA
## 69 NA
## 70 L 6 W 57 D 52 W 48 NA
## 71 NA
## 72 D 33 H L 16 D 28 NA
## 73 NA
## 74 L 27 H L 23 W 61 NA
## 75 NA
## 76 L 12 H L 18 L 15 NA
## 77 NA
## 78 L 16 W 44 L 21 L 24 NA
## 79 NA
## 80 W 59 L 21 W 56 L 22 NA
## 81 NA
## 82 L 20 X U U NA
## 83 NA
## 84 D 60 D 61 W 64 W 56 NA
## 85 NA
## 86 W 63 W 59 L 46 W 55 NA
## 87 NA
## 88 W 53 L 39 L 24 W 59 NA
## 89 NA
## 90 L 56 W 63 D 55 W 58 NA
## 91 NA
## 92 L 50 W 64 W 43 L 23 NA
## 93 NA
## 94 W 61 L 8 D 51 L 25 NA
## 95 NA
## 96 D 52 H L 29 L 35 NA
## 97 NA
## 98 D 64 W 58 H U NA
## 99 NA
## 100 W 46 H L 31 L 30 NA
## 101 NA
## 102 W 57 L 32 D 47 L 33 NA
## 103 NA
## 104 D 48 L 29 D 35 L 34 NA
## 105 NA
## 106 L 44 U W 57 U NA
## 107 NA
## 108 B L 15 L 59 W 64 NA
## 109 NA
## 110 L 30 B D 45 L 43 NA
## 111 NA
## 112 W 45 H L 40 L 42 NA
## 113 NA
## 114 L 51 L 35 L 53 B NA
## 115 NA
## 116 L 23 L 49 B L 45 NA
## 117 NA
## 118 L 40 L 43 W 54 L 44 NA
## 119 NA
## 120 D 42 L 24 H U NA
## 121 NA
## 122 L 47 D 42 L 30 L 37 NA
## 123 NA
## 124 U U U U NA
## 125 NA
## 126 L 43 L 45 H U NA
## 127 NA
## 128 D 49 L 46 L 42 L 54 NA
## 129 NAdat <- transform(df, state = c(Pair[-1], NA))
dat## Pair Player.Name Total Round Round.1 Round.2
## 1 Num USCF ID / Rtg (Pre NA
## 2 1 GARY HUA 6.0 W 39 W 21 W 18
## 3 ON 15445895 / R: 1794 NA
## 4 2 DAKSHESH DARURI 6.0 W 63 W 58 L 4
## 5 MI 14598900 / R: 1553 NA
## 6 3 ADITYA BAJAJ 6.0 L 8 W 61 W 25
## 7 MI 14959604 / R: 1384 NA
## 8 4 PATRICK H SCHILLING 5.5 W 23 D 28 W 2
## 9 MI 12616049 / R: 1716 NA
## 10 5 HANSHI ZUO 5.5 W 45 W 37 D 12
## 11 MI 14601533 / R: 1655 NA
## 12 6 HANSEN SONG 5.0 W 34 D 29 L 11
## 13 OH 15055204 / R: 1686 NA
## 14 7 GARY DEE SWATHELL 5.0 W 57 W 46 W 13
## 15 MI 11146376 / R: 1649 NA
## 16 8 EZEKIEL HOUGHTON 5.0 W 3 W 32 L 14
## 17 MI 15142253 / R: 1641P17 NA
## 18 9 STEFANO LEE 5.0 W 25 L 18 W 59
## 19 ON 14954524 / R: 1411 NA
## 20 10 ANVIT RAO 5.0 D 16 L 19 W 55
## 21 MI 14150362 / R: 1365 NA
## 22 11 CAMERON WILLIAM MC LEMAN 4.5 D 38 W 56 W 6
## 23 MI 12581589 / R: 1712 NA
## 24 12 KENNETH J TACK 4.5 W 42 W 33 D 5
## 25 MI 12681257 / R: 1663 NA
## 26 13 TORRANCE HENRY JR 4.5 W 36 W 27 L 7
## 27 MI 15082995 / R: 1666 NA
## 28 14 BRADLEY SHAW 4.5 W 54 W 44 W 8
## 29 MI 10131499 / R: 1610 NA
## 30 15 ZACHARY JAMES HOUGHTON 4.5 D 19 L 16 W 30
## 31 MI 15619130 / R: 1220P13 NA
## 32 16 MIKE NIKITIN 4.0 D 10 W 15 H
## 33 MI 10295068 / R: 1604 NA
## 34 17 RONALD GRZEGORCZYK 4.0 W 48 W 41 L 26
## 35 MI 10297702 / R: 1629 NA
## 36 18 DAVID SUNDEEN 4.0 W 47 W 9 L 1
## 37 MI 11342094 / R: 1600 NA
## 38 19 DIPANKAR ROY 4.0 D 15 W 10 W 52
## 39 MI 14862333 / R: 1564 NA
## 40 20 JASON ZHENG 4.0 L 40 W 49 W 23
## 41 MI 14529060 / R: 1595 NA
## 42 21 DINH DANG BUI 4.0 W 43 L 1 W 47
## 43 ON 15495066 / R: 1563P22 NA
## 44 22 EUGENE L MCCLURE 4.0 W 64 D 52 L 28
## 45 MI 12405534 / R: 1555 NA
## 46 23 ALAN BUI 4.0 L 4 W 43 L 20
## 47 ON 15030142 / R: 1363 NA
## 48 24 MICHAEL R ALDRICH 4.0 L 28 L 47 W 43
## 49 MI 13469010 / R: 1229 NA
## 50 25 LOREN SCHWIEBERT 3.5 L 9 W 53 L 3
## 51 MI 12486656 / R: 1745 NA
## 52 26 MAX ZHU 3.5 W 49 W 40 W 17
## 53 ON 15131520 / R: 1579 NA
## 54 27 GAURAV GIDWANI 3.5 W 51 L 13 W 46
## 55 MI 14476567 / R: 1552 NA
## 56 28 SOFIA ADINA STANESCU NA
## 57 MI 14882954 / R: 1507 NA
## 58 29 CHIEDOZIE OKORIE 3.5 W 50 D 6 L 38
## 59 MI 15323285 / R: 1602P6 NA
## 60 30 GEORGE AVERY JONES 3.5 L 52 D 64 L 15
## 61 ON 12577178 / R: 1522 NA
## 62 31 RISHI SHETTY 3.5 L 58 D 55 W 64
## 63 MI 15131618 / R: 1494 NA
## 64 32 JOSHUA PHILIP MATHEWS 3.5 W 61 L 8 W 44
## 65 ON 14073750 / R: 1441 NA
## 66 33 JADE GE 3.5 W 60 L 12 W 50
## 67 MI 14691842 / R: 1449 NA
## 68 34 MICHAEL JEFFERY THOMAS 3.5 L 6 W 60 L 37
## 69 MI 15051807 / R: 1399 NA
## 70 35 JOSHUA DAVID LEE 3.5 L 46 L 38 W 56
## 71 MI 14601397 / R: 1438 NA
## 72 36 SIDDHARTH JHA 3.5 L 13 W 57 W 51
## 73 MI 14773163 / R: 1355 NA
## 74 37 AMIYATOSH PWNANANDAM 3.5 B L 5 W 34
## 75 MI 15489571 / R: 980P12 NA
## 76 38 BRIAN LIU 3.0 D 11 W 35 W 29
## 77 MI 15108523 / R: 1423 NA
## 78 39 JOEL R HENDON 3.0 L 1 W 54 W 40
## 79 MI 12923035 / R: 1436P23 NA
## 80 40 FOREST ZHANG 3.0 W 20 L 26 L 39
## 81 MI 14892710 / R: 1348 NA
## 82 41 KYLE WILLIAM MURPHY 3.0 W 59 L 17 W 58
## 83 MI 15761443 / R: 1403P5 NA
## 84 42 JARED GE 3.0 L 12 L 50 L 57
## 85 MI 14462326 / R: 1332 NA
## 86 43 ROBERT GLEN VASEY 3.0 L 21 L 23 L 24
## 87 MI 14101068 / R: 1283 NA
## 88 44 JUSTIN D SCHILLING 3.0 B L 14 L 32
## 89 MI 15323504 / R: 1199 NA
## 90 45 DEREK YAN 3.0 L 5 L 51 D 60
## 91 MI 15372807 / R: 1242 NA
## 92 46 JACOB ALEXANDER LAVALLEY 3.0 W 35 L 7 L 27
## 93 MI 15490981 / R: 377P3 NA
## 94 47 ERIC WRIGHT 2.5 L 18 W 24 L 21
## 95 MI 12533115 / R: 1362 NA
## 96 48 DANIEL KHAIN 2.5 L 17 W 63 H
## 97 MI 14369165 / R: 1382 NA
## 98 49 MICHAEL J MARTIN 2.5 L 26 L 20 D 63
## 99 MI 12531685 / R: 1291P12 NA
## 100 50 SHIVAM JHA 2.5 L 29 W 42 L 33
## 101 MI 14773178 / R: 1056 NA
## 102 51 TEJAS AYYAGARI 2.5 L 27 W 45 L 36
## 103 MI 15205474 / R: 1011 NA
## 104 52 ETHAN GUO 2.5 W 30 D 22 L 19
## 105 MI 14918803 / R: 935 NA
## 106 53 JOSE C YBARRA 2.0 H L 25 H
## 107 MI 12578849 / R: 1393 NA
## 108 54 LARRY HODGE 2.0 L 14 L 39 L 61
## 109 MI 12836773 / R: 1270 NA
## 110 55 ALEX KONG 2.0 L 62 D 31 L 10
## 111 MI 15412571 / R: 1186 NA
## 112 56 MARISA RICCI 2.0 H L 11 L 35
## 113 MI 14679887 / R: 1153 NA
## 114 57 MICHAEL LU 2.0 L 7 L 36 W 42
## 115 MI 15113330 / R: 1092 NA
## 116 58 VIRAJ MOHILE 2.0 W 31 L 2 L 41
## 117 MI 14700365 / R: 917 NA
## 118 59 SEAN M MC CORMICK 2.0 L 41 B L 9
## 119 MI 12841036 / R: 853 NA
## 120 60 JULIA SHEN 1.5 L 33 L 34 D 45
## 121 MI 14579262 / R: 967 NA
## 122 61 JEZZEL FARKAS 1.5 L 32 L 3 W 54
## 123 ON 15771592 / R: 955P11 NA
## 124 62 ASHWIN BALAJI 1.0 W 55 U U
## 125 MI 15219542 / R: 1530 NA
## 126 63 THOMAS JOSEPH HOSMER 1.0 L 2 L 48 D 49
## 127 MI 15057092 / R: 1175 NA
## 128 64 BEN LI 1.0 L 22 D 30 L 31
## 129 MI 15006561 / R: 1163 NA
## Round.3 Round.4 Round.5 Round.6 X state
## 1 NA 1
## 2 W 14 W 7 D 12 D 4 NA ON
## 3 NA 2
## 4 W 17 W 16 W 20 W 7 NA MI
## 5 NA 3
## 6 W 21 W 11 W 13 W 12 NA MI
## 7 NA 4
## 8 W 26 D 5 W 19 D 1 NA MI
## 9 NA 5
## 10 D 13 D 4 W 14 W 17 NA MI
## 11 NA 6
## 12 W 35 D 10 W 27 W 21 NA OH
## 13 NA 7
## 14 W 11 L 1 W 9 L 2 NA MI
## 15 NA 8
## 16 L 9 W 47 W 28 W 19 NA MI
## 17 NA 9
## 18 W 8 W 26 L 7 W 20 NA ON
## 19 NA 10
## 20 W 31 D 6 W 25 W 18 NA MI
## 21 NA 11
## 22 L 7 L 3 W 34 W 26 NA MI
## 23 NA 12
## 24 W 38 H D 1 L 3 NA MI
## 25 NA 13
## 26 D 5 W 33 L 3 W 32 NA MI
## 27 NA 14
## 28 L 1 D 27 L 5 W 31 NA MI
## 29 NA 15
## 30 L 22 W 54 W 33 W 38 NA MI
## 31 NA 16
## 32 W 39 L 2 W 36 U NA MI
## 33 NA 17
## 34 L 2 W 23 W 22 L 5 NA MI
## 35 NA 18
## 36 W 32 L 19 W 38 L 10 NA MI
## 37 NA 19
## 38 D 28 W 18 L 4 L 8 NA MI
## 39 NA 20
## 40 W 41 W 28 L 2 L 9 NA MI
## 41 NA 21
## 42 L 3 W 40 W 39 L 6 NA ON
## 43 NA 22
## 44 W 15 H L 17 W 40 NA MI
## 45 NA 23
## 46 W 58 L 17 W 37 W 46 NA ON
## 47 NA 24
## 48 L 25 W 60 W 44 W 39 NA MI
## 49 NA 25
## 50 W 24 D 34 L 10 W 47 NA MI
## 51 NA 26
## 52 L 4 L 9 D 32 L 11 NA ON
## 53 NA 27
## 54 W 37 D 14 L 6 U NA MI
## 55 NA 28
## 56 NA MI
## 57 NA 29
## 58 L 34 W 52 W 48 U NA MI
## 59 NA 30
## 60 W 55 L 31 W 61 W 50 NA ON
## 61 NA 31
## 62 L 10 W 30 W 50 L 14 NA MI
## 63 NA 32
## 64 L 18 W 51 D 26 L 13 NA ON
## 65 NA 33
## 66 D 36 L 13 L 15 W 51 NA MI
## 67 NA 34
## 68 W 29 D 25 L 11 W 52 NA MI
## 69 NA 35
## 70 L 6 W 57 D 52 W 48 NA MI
## 71 NA 36
## 72 D 33 H L 16 D 28 NA MI
## 73 NA 37
## 74 L 27 H L 23 W 61 NA MI
## 75 NA 38
## 76 L 12 H L 18 L 15 NA MI
## 77 NA 39
## 78 L 16 W 44 L 21 L 24 NA MI
## 79 NA 40
## 80 W 59 L 21 W 56 L 22 NA MI
## 81 NA 41
## 82 L 20 X U U NA MI
## 83 NA 42
## 84 D 60 D 61 W 64 W 56 NA MI
## 85 NA 43
## 86 W 63 W 59 L 46 W 55 NA MI
## 87 NA 44
## 88 W 53 L 39 L 24 W 59 NA MI
## 89 NA 45
## 90 L 56 W 63 D 55 W 58 NA MI
## 91 NA 46
## 92 L 50 W 64 W 43 L 23 NA MI
## 93 NA 47
## 94 W 61 L 8 D 51 L 25 NA MI
## 95 NA 48
## 96 D 52 H L 29 L 35 NA MI
## 97 NA 49
## 98 D 64 W 58 H U NA MI
## 99 NA 50
## 100 W 46 H L 31 L 30 NA MI
## 101 NA 51
## 102 W 57 L 32 D 47 L 33 NA MI
## 103 NA 52
## 104 D 48 L 29 D 35 L 34 NA MI
## 105 NA 53
## 106 L 44 U W 57 U NA MI
## 107 NA 54
## 108 B L 15 L 59 W 64 NA MI
## 109 NA 55
## 110 L 30 B D 45 L 43 NA MI
## 111 NA 56
## 112 W 45 H L 40 L 42 NA MI
## 113 NA 57
## 114 L 51 L 35 L 53 B NA MI
## 115 NA 58
## 116 L 23 L 49 B L 45 NA MI
## 117 NA 59
## 118 L 40 L 43 W 54 L 44 NA MI
## 119 NA 60
## 120 D 42 L 24 H U NA MI
## 121 NA 61
## 122 L 47 D 42 L 30 L 37 NA ON
## 123 NA 62
## 124 U U U U NA MI
## 125 NA 63
## 126 L 43 L 45 H U NA MI
## 127 NA 64
## 128 D 49 L 46 L 42 L 54 NA MI
## 129 NA <NA>Shifting our rows so we have all of a player’s data on one line/row
df <- df %>%
mutate(state = c(Pair[-1], NA)) %>%
mutate(rating = c(Player.Name[-1], NA))
head(df, 10)## Pair Player.Name Total Round Round.1 Round.2 Round.3
## 1 Num USCF ID / Rtg (Pre NA
## 2 1 GARY HUA 6.0 W 39 W 21 W 18 W 14
## 3 ON 15445895 / R: 1794 NA
## 4 2 DAKSHESH DARURI 6.0 W 63 W 58 L 4 W 17
## 5 MI 14598900 / R: 1553 NA
## 6 3 ADITYA BAJAJ 6.0 L 8 W 61 W 25 W 21
## 7 MI 14959604 / R: 1384 NA
## 8 4 PATRICK H SCHILLING 5.5 W 23 D 28 W 2 W 26
## 9 MI 12616049 / R: 1716 NA
## 10 5 HANSHI ZUO 5.5 W 45 W 37 D 12 D 13
## Round.4 Round.5 Round.6 X state rating
## 1 NA 1 GARY HUA
## 2 W 7 D 12 D 4 NA ON 15445895 / R: 1794
## 3 NA 2 DAKSHESH DARURI
## 4 W 16 W 20 W 7 NA MI 14598900 / R: 1553
## 5 NA 3 ADITYA BAJAJ
## 6 W 11 W 13 W 12 NA MI 14959604 / R: 1384
## 7 NA 4 PATRICK H SCHILLING
## 8 D 5 W 19 D 1 NA MI 12616049 / R: 1716
## 9 NA 5 HANSHI ZUO
## 10 D 4 W 14 W 17 NA MI 14601533 / R: 1655Only want the odd rows now that they’re consolidated. Should be able to drop exces rows
odd_rows <- seq(nrow(df)) %% 2
df <- df[odd_rows == 0,]
head(df, 10)## Pair Player.Name Total Round Round.1 Round.2 Round.3
## 2 1 GARY HUA 6.0 W 39 W 21 W 18 W 14
## 4 2 DAKSHESH DARURI 6.0 W 63 W 58 L 4 W 17
## 6 3 ADITYA BAJAJ 6.0 L 8 W 61 W 25 W 21
## 8 4 PATRICK H SCHILLING 5.5 W 23 D 28 W 2 W 26
## 10 5 HANSHI ZUO 5.5 W 45 W 37 D 12 D 13
## 12 6 HANSEN SONG 5.0 W 34 D 29 L 11 W 35
## 14 7 GARY DEE SWATHELL 5.0 W 57 W 46 W 13 W 11
## 16 8 EZEKIEL HOUGHTON 5.0 W 3 W 32 L 14 L 9
## 18 9 STEFANO LEE 5.0 W 25 L 18 W 59 W 8
## 20 10 ANVIT RAO 5.0 D 16 L 19 W 55 W 31
## Round.4 Round.5 Round.6 X state rating
## 2 W 7 D 12 D 4 NA ON 15445895 / R: 1794
## 4 W 16 W 20 W 7 NA MI 14598900 / R: 1553
## 6 W 11 W 13 W 12 NA MI 14959604 / R: 1384
## 8 D 5 W 19 D 1 NA MI 12616049 / R: 1716
## 10 D 4 W 14 W 17 NA MI 14601533 / R: 1655
## 12 D 10 W 27 W 21 NA OH 15055204 / R: 1686
## 14 L 1 W 9 L 2 NA MI 11146376 / R: 1649
## 16 W 47 W 28 W 19 NA MI 15142253 / R: 1641P17
## 18 W 26 L 7 W 20 NA ON 14954524 / R: 1411
## 20 D 6 W 25 W 18 NA MI 14150362 / R: 1365Extracting the player’s rating from the rating
column.
df<- df %>%
mutate(rating = str_extract(df$rating, "R:\\s(....)"))
# Getting rid of 'R: ' prefix
df <- df %>%
mutate(rating = gsub("R: ", "", df$rating))
# Dropping unnecessary 'X' column
df <- select(df, -X, -Round)
head(df)## Pair Player.Name Total Round.1 Round.2 Round.3
## 2 1 GARY HUA 6.0 W 21 W 18 W 14
## 4 2 DAKSHESH DARURI 6.0 W 58 L 4 W 17
## 6 3 ADITYA BAJAJ 6.0 W 61 W 25 W 21
## 8 4 PATRICK H SCHILLING 5.5 D 28 W 2 W 26
## 10 5 HANSHI ZUO 5.5 W 37 D 12 D 13
## 12 6 HANSEN SONG 5.0 D 29 L 11 W 35
## Round.4 Round.5 Round.6 state rating
## 2 W 7 D 12 D 4 ON 1794
## 4 W 16 W 20 W 7 MI 1553
## 6 W 11 W 13 W 12 MI 1384
## 8 D 5 W 19 D 1 MI 1716
## 10 D 4 W 14 W 17 MI 1655
## 12 D 10 W 27 W 21 OH 1686Extracting the player opponent numbers – need to remove the numbers
are we don’t necessarily care about wins, losses, etc. Also coercing the
rating column to an Int type so we can average the
opponent’s ratings for each player.
for (i in 1:6){
df <- df %>%
dplyr::mutate(across(glue('Round.{i}'), str_replace, "(W|L|B|D|H|U)\\s", '')) # Extract opponent index
#mutate(glue('Round.{i}'), gsub( "(W|L|B|D|H|U)\\s", ""))
}
# Coerce ratings to int type
df <- df %>%
dplyr::mutate(rating = as.integer(rating))
head(df, 10)## Pair Player.Name Total Round.1 Round.2 Round.3
## 2 1 GARY HUA 6.0 21 18 14
## 4 2 DAKSHESH DARURI 6.0 58 4 17
## 6 3 ADITYA BAJAJ 6.0 61 25 21
## 8 4 PATRICK H SCHILLING 5.5 28 2 26
## 10 5 HANSHI ZUO 5.5 37 12 13
## 12 6 HANSEN SONG 5.0 29 11 35
## 14 7 GARY DEE SWATHELL 5.0 46 13 11
## 16 8 EZEKIEL HOUGHTON 5.0 32 14 9
## 18 9 STEFANO LEE 5.0 18 59 8
## 20 10 ANVIT RAO 5.0 19 55 31
## Round.4 Round.5 Round.6 state rating
## 2 7 12 4 ON 1794
## 4 16 20 7 MI 1553
## 6 11 13 12 MI 1384
## 8 5 19 1 MI 1716
## 10 4 14 17 MI 1655
## 12 10 27 21 OH 1686
## 14 1 9 2 MI 1649
## 16 47 28 19 MI 1641
## 18 26 7 20 ON 1411
## 20 6 25 18 MI 1365Looking up opponents’ ratings for each player. Could likely vectorize this functionality to apply on a column basis.
# Iterate over each player, need to grab each opponent's rankings
for (i in 1:nrow(df)) {
# Need to calculate these metrics for each player
rating_sum <- 0
n_games_by_player <- 0
avg_rating <- 0
# Then need to average against # of games player (which won't be 6 for each player)
for (j in 1:6) {
if (is.na(df[i, j])) {
print("No game played.")
}
else {
opponent_num <- as.integer(df[i, glue("Round.{j}")])
print(glue("Opponent num {opponent_num}"))
n_games_by_player <- n_games_by_player + 1
rating_sum <- rating_sum + df[opponent_num, 'rating']
}
}
# Calculating average rating of opponents based on # of games played.
avg_rating <- rating_sum / n_games_by_player
print(glue("Avg rating: {avg_rating}"))
df$opp_rating[i] <- avg_rating
}## Opponent num 21
## Opponent num 18
## Opponent num 14
## Opponent num 7
## Opponent num 12
## Opponent num 4
## Avg rating: 1633.5
## Opponent num 58
## Opponent num 4
## Opponent num 17
## Opponent num 16
## Opponent num 20
## Opponent num 7
## Avg rating: 1518.33333333333
## Opponent num 61
## Opponent num 25
## Opponent num 21
## Opponent num 11
## Opponent num 13
## Opponent num 12
## Avg rating: 1550.66666666667
## Opponent num 28
## Opponent num 2
## Opponent num 26
## Opponent num 5
## Opponent num 19
## Opponent num 1
## Avg rating: 1608.66666666667
## Opponent num 37
## Opponent num 12
## Opponent num 13
## Opponent num 4
## Opponent num 14
## Opponent num 17
## Avg rating: 1544
## Opponent num 29
## Opponent num 11
## Opponent num 35
## Opponent num 10
## Opponent num 27
## Opponent num 21
## Avg rating: 1538.66666666667
## Opponent num 46
## Opponent num 13
## Opponent num 11
## Opponent num 1
## Opponent num 9
## Opponent num 2
## Avg rating: 1418.83333333333
## Opponent num 32
## Opponent num 14
## Opponent num 9
## Opponent num 47
## Opponent num 28
## Opponent num 19
## Avg rating: 1482.5
## Opponent num 18
## Opponent num 59
## Opponent num 8
## Opponent num 26
## Opponent num 7
## Opponent num 20
## Avg rating: 1486.16666666667
## Opponent num 19
## Opponent num 55
## Opponent num 31
## Opponent num 6
## Opponent num 25
## Opponent num 18
## Avg rating: 1545.83333333333
## Opponent num 56
## Opponent num 6
## Opponent num 7
## Opponent num 3
## Opponent num 34
## Opponent num 26
## Avg rating: 1475
## Opponent num 33
## Opponent num 5
## Opponent num 38
## Opponent num NA
## Opponent num 1
## Opponent num 3
## Avg rating: NA
## Opponent num 27
## Opponent num 7
## Opponent num 5
## Opponent num 33
## Opponent num 3
## Opponent num 32
## Avg rating: 1521.66666666667
## Opponent num 44
## Opponent num 8
## Opponent num 1
## Opponent num 27
## Opponent num 5
## Opponent num 31
## Avg rating: 1555.83333333333
## Opponent num 16
## Opponent num 30
## Opponent num 22
## Opponent num 54
## Opponent num 33
## Opponent num 38
## Avg rating: 1470.5
## Opponent num 15
## Opponent num NA
## Opponent num 39
## Opponent num 2
## Opponent num 36
## Opponent num NA
## Avg rating: NA
## Opponent num 41
## Opponent num 26
## Opponent num 2
## Opponent num 23
## Opponent num 22
## Opponent num 5
## Avg rating: 1518
## Opponent num 9
## Opponent num 1
## Opponent num 32
## Opponent num 19
## Opponent num 38
## Opponent num 10
## Avg rating: 1499.66666666667
## Opponent num 10
## Opponent num 52
## Opponent num 28
## Opponent num 18
## Opponent num 4
## Opponent num 8
## Avg rating: 1460.66666666667
## Opponent num 49
## Opponent num 23
## Opponent num 41
## Opponent num 28
## Opponent num 2
## Opponent num 9
## Avg rating: 1421.33333333333
## Opponent num 1
## Opponent num 47
## Opponent num 3
## Opponent num 40
## Opponent num 39
## Opponent num 6
## Avg rating: 1501.66666666667
## Opponent num 52
## Opponent num 28
## Opponent num 15
## Opponent num NA
## Opponent num 17
## Opponent num 40
## Avg rating: NA
## Opponent num 43
## Opponent num 20
## Opponent num 58
## Opponent num 17
## Opponent num 37
## Opponent num 46
## Avg rating: 1130.16666666667
## Opponent num 47
## Opponent num 43
## Opponent num 25
## Opponent num 60
## Opponent num 44
## Opponent num 39
## Avg rating: 1332
## Opponent num 53
## Opponent num 3
## Opponent num 24
## Opponent num 34
## Opponent num 10
## Opponent num 47
## Avg rating: 1355.33333333333
## Opponent num 40
## Opponent num 17
## Opponent num 4
## Opponent num 9
## Opponent num 32
## Opponent num 11
## Avg rating: 1542.83333333333
## Opponent num 13
## Opponent num 46
## Opponent num 37
## Opponent num 14
## Opponent num 6
## Opponent num NA
## Avg rating: NA
## Opponent num NA
## Opponent num NA
## [1] "No game played."
## Opponent num NA
## Opponent num NA
## Opponent num NA
## Avg rating: NA
## Opponent num 6
## Opponent num 38
## Opponent num 34
## Opponent num 52
## Opponent num 48
## Opponent num NA
## Avg rating: NA
## Opponent num 64
## Opponent num 15
## Opponent num 55
## Opponent num 31
## Opponent num 61
## Opponent num 50
## Avg rating: 1179
## Opponent num 55
## Opponent num 64
## Opponent num 10
## Opponent num 30
## Opponent num 50
## Opponent num 14
## Avg rating: 1317
## Opponent num 8
## Opponent num 44
## Opponent num 18
## Opponent num 51
## Opponent num 26
## Opponent num 13
## Avg rating: 1449.33333333333
## Opponent num 12
## Opponent num 50
## Opponent num 36
## Opponent num 13
## Opponent num 15
## Opponent num 51
## Avg rating: 1328.5
## Opponent num 60
## Opponent num 37
## Opponent num 29
## Opponent num 25
## Opponent num 11
## Opponent num 52
## Avg rating: 1323.5
## Opponent num 38
## Opponent num 56
## Opponent num 6
## Opponent num 57
## Opponent num 52
## Opponent num 48
## Avg rating: 1278.5
## Opponent num 57
## Opponent num 51
## Opponent num 33
## Opponent num NA
## Opponent num 16
## Opponent num 28
## Avg rating: NA
## Opponent num 5
## Opponent num 34
## Opponent num 27
## Opponent num NA
## Opponent num 23
## Opponent num 61
## Avg rating: NA
## Opponent num 35
## Opponent num 29
## Opponent num 12
## Opponent num NA
## Opponent num 18
## Opponent num 15
## Avg rating: NA
## Opponent num 54
## Opponent num 40
## Opponent num 16
## Opponent num 44
## Opponent num 21
## Opponent num 24
## Avg rating: 1368.83333333333
## Opponent num 26
## Opponent num 39
## Opponent num 59
## Opponent num 21
## Opponent num 56
## Opponent num 22
## Avg rating: 1356.5
## Opponent num 17
## Opponent num 58
## Opponent num 20## Warning: NAs introduced by coercion## Opponent num NA
## Opponent num NA
## Opponent num NA
## Avg rating: NA
## Opponent num 50
## Opponent num 57
## Opponent num 60
## Opponent num 61
## Opponent num 64
## Opponent num 56
## Avg rating: 1064.33333333333
## Opponent num 23
## Opponent num 24
## Opponent num 63
## Opponent num 59
## Opponent num 46
## Opponent num 55
## Avg rating: 1030.5
## Opponent num 14
## Opponent num 32
## Opponent num 53
## Opponent num 39
## Opponent num 24
## Opponent num 59
## Avg rating: 1327
## Opponent num 51
## Opponent num 60
## Opponent num 56
## Opponent num 63
## Opponent num 55
## Opponent num 58
## Avg rating: 1068.16666666667
## Opponent num 7
## Opponent num 27
## Opponent num 50
## Opponent num 64
## Opponent num 43
## Opponent num 23
## Avg rating: 1344.33333333333
## Opponent num 24
## Opponent num 21
## Opponent num 61
## Opponent num 8
## Opponent num 51
## Opponent num 25
## Avg rating: 1357.33333333333
## Opponent num 63
## Opponent num NA
## Opponent num 52
## Opponent num NA
## Opponent num 29
## Opponent num 35
## Avg rating: NA
## Opponent num 20
## Opponent num 63
## Opponent num 64
## Opponent num 58
## Opponent num NA
## Opponent num NA
## Avg rating: NA
## Opponent num 42
## Opponent num 33
## Opponent num 46
## Opponent num NA
## Opponent num 31
## Opponent num 30
## Avg rating: NA
## Opponent num 45
## Opponent num 36
## Opponent num 57
## Opponent num 32
## Opponent num 47
## Opponent num 33
## Avg rating: 1323.5
## Opponent num 22
## Opponent num 19
## Opponent num 48
## Opponent num 29
## Opponent num 35
## Opponent num 34
## Avg rating: 1490
## Opponent num 25
## Opponent num NA
## Opponent num 44
## Opponent num NA
## Opponent num 57
## Opponent num NA
## Avg rating: NA
## Opponent num 39
## Opponent num 61
## Opponent num NA
## Opponent num 15
## Opponent num 59
## Opponent num 64
## Avg rating: NA
## Opponent num 31
## Opponent num 10
## Opponent num 30
## Opponent num NA
## Opponent num 45
## Opponent num 43
## Avg rating: NA
## Opponent num 11
## Opponent num 35
## Opponent num 45
## Opponent num NA
## Opponent num 40
## Opponent num 42
## Avg rating: NA
## Opponent num 36
## Opponent num 42
## Opponent num 51
## Opponent num 35
## Opponent num 53
## Opponent num NA
## Avg rating: NA
## Opponent num 2
## Opponent num 41
## Opponent num 23
## Opponent num 49
## Opponent num NA
## Opponent num 45
## Avg rating: NA
## Opponent num NA
## Opponent num 9
## Opponent num 40
## Opponent num 43
## Opponent num 54
## Opponent num 44
## Avg rating: NA
## Opponent num 34
## Opponent num 45
## Opponent num 42
## Opponent num 24
## Opponent num NA
## Opponent num NA
## Avg rating: NA
## Opponent num 3
## Opponent num 54
## Opponent num 47
## Opponent num 42
## Opponent num 30
## Opponent num 37
## Avg rating: 1308.33333333333
## Opponent num NA
## Opponent num NA
## Opponent num NA
## Opponent num NA
## Opponent num NA
## Opponent num NA
## Avg rating: NA
## Opponent num 48
## Opponent num 49
## Opponent num 43
## Opponent num 45
## Opponent num NA
## Opponent num NA
## Avg rating: NA
## Opponent num 30
## Opponent num 31
## Opponent num 49
## Opponent num 46
## Opponent num 42
## Opponent num 54
## Avg rating: 1214.33333333333head(df, 10)## Pair Player.Name Total Round.1 Round.2 Round.3
## 2 1 GARY HUA 6.0 21 18 14
## 4 2 DAKSHESH DARURI 6.0 58 4 17
## 6 3 ADITYA BAJAJ 6.0 61 25 21
## 8 4 PATRICK H SCHILLING 5.5 28 2 26
## 10 5 HANSHI ZUO 5.5 37 12 13
## 12 6 HANSEN SONG 5.0 29 11 35
## 14 7 GARY DEE SWATHELL 5.0 46 13 11
## 16 8 EZEKIEL HOUGHTON 5.0 32 14 9
## 18 9 STEFANO LEE 5.0 18 59 8
## 20 10 ANVIT RAO 5.0 19 55 31
## Round.4 Round.5 Round.6 state rating opp_rating
## 2 7 12 4 ON 1794 1633.500
## 4 16 20 7 MI 1553 1518.333
## 6 11 13 12 MI 1384 1550.667
## 8 5 19 1 MI 1716 1608.667
## 10 4 14 17 MI 1655 1544.000
## 12 10 27 21 OH 1686 1538.667
## 14 1 9 2 MI 1649 1418.833
## 16 47 28 19 MI 1641 1482.500
## 18 26 7 20 ON 1411 1486.167
## 20 6 25 18 MI 1365 1545.833Only selecting columns we weant and renaming them.
df <- df %>%
select(Pair, Player.Name, state, Total, rating, opp_rating) %>%
plyr::rename(c("Pair"="no",
"Player.Name" = "name",
"Total" = "total",
"rating" = "pre_rating",
"opp_rating" = "avg_opp_rating"))
head(df)## no name state total pre_rating
## 2 1 GARY HUA ON 6.0 1794
## 4 2 DAKSHESH DARURI MI 6.0 1553
## 6 3 ADITYA BAJAJ MI 6.0 1384
## 8 4 PATRICK H SCHILLING MI 5.5 1716
## 10 5 HANSHI ZUO MI 5.5 1655
## 12 6 HANSEN SONG OH 5.0 1686
## avg_opp_rating
## 2 1633.500
## 4 1518.333
## 6 1550.667
## 8 1608.667
## 10 1544.000
## 12 1538.667Write to a csv as ouput
write.csv(df, "elo-ratings-cleaned.csv")