Load packages.

library(knitr)
library(stringr)
library(tidyr)
library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

Import text file, identify delimiters, skip first 4 rows.

chess<-read.table("tournamentinfo.txt", sep="|", fill=TRUE, stringsAsFactors = FALSE, skip=4)
#str(chess) #testing
#head(chess) #testing

Combine observations across two rows.

namesrow <- chess[seq(1,nrow(chess),by=3),]
#head(namesrow) #testing
staterow <- chess[seq(2,nrow(chess),by=3),]
#head(staterow) #testing
chess <- cbind(namesrow, staterow)
#head(chess) #testing

Discard unneeded columns.

chess <- subset(chess[, c(1:10,12,13)])
#head(chess) #testing

Use regular expressions to extract player ID and pre-game rating.

chess <- extract(chess, V2.1, c('PID', 'PreRating'), '(.[0-9]{8}) / R: (.[0-9]{3,4})')
#head(chess) #testing

Use regular expressions to split each round into round number and opponent number.

chess <- extract (chess, V4, c('Game1', 'Opp1'), '^([A-Z]{1})* +(.[0-9]{0,2})')
chess <- extract (chess, V5, c('Game2', 'Opp2'), '^([A-Z]{1})* +(.[0-9]{0,2})')
chess <- extract (chess, V6, c('Game3', 'Opp3'), '^([A-Z]{1})* +(.[0-9]{0,2})')
chess <- extract (chess, V7, c('Game4', 'Opp4'), '^([A-Z]{1})* +(.[0-9]{0,2})')
chess <- extract (chess, V8, c('Game5', 'Opp5'), '^([A-Z]{1})* +(.[0-9]{0,2})')
chess <- extract (chess, V9, c('Game6', 'Opp6'), '^([A-Z]{1})* +(.[0-9]{0,2})')
chess <- extract (chess, V10, c('Game7', 'Opp7'), '^([A-Z]{1})* +(.[0-9]{0,2})')

View result and structure.

knitr::kable(head(chess))
V1 V2 V3 Game1 Opp1 Game2 Opp2 Game3 Opp3 Game4 Opp4 Game5 Opp5 Game6 Opp6 Game7 Opp7 V1.1 PID PreRating
1 1 GARY HUA 6.0 W 39 W 21 W 18 W 14 W 7 D 12 D 4 ON 15445895 1794
4 2 DAKSHESH DARURI 6.0 W 63 W 58 L 4 W 17 W 16 W 20 W 7 MI 14598900 1553
7 3 ADITYA BAJAJ 6.0 L 8 W 61 W 25 W 21 W 11 W 13 W 12 MI 14959604 1384
10 4 PATRICK H SCHILLING 5.5 W 23 D 28 W 2 W 26 D 5 W 19 D 1 MI 12616049 1716
13 5 HANSHI ZUO 5.5 W 45 W 37 D 12 D 13 D 4 W 14 W 17 MI 14601533 1655
16 6 HANSEN SONG 5.0 W 34 D 29 L 11 W 35 D 10 W 27 W 21 OH 15055204 1686 
str(chess)
## 'data.frame':    64 obs. of  20 variables:
##  $ V1       : chr  "    1 " "    2 " "    3 " "    4 " ...
##  $ V2       : chr  " GARY HUA                        " " DAKSHESH DARURI                 " " ADITYA BAJAJ                    " " PATRICK H SCHILLING             " ...
##  $ V3       : chr  "6.0  " "6.0  " "6.0  " "5.5  " ...
##  $ Game1    : chr  "W" "W" "L" "W" ...
##  $ Opp1     : chr  "39" "63" "8" "23" ...
##  $ Game2    : chr  "W" "W" "W" "D" ...
##  $ Opp2     : chr  "21" "58" "61" "28" ...
##  $ Game3    : chr  "W" "L" "W" "W" ...
##  $ Opp3     : chr  "18" "4" "25" "2" ...
##  $ Game4    : chr  "W" "W" "W" "W" ...
##  $ Opp4     : chr  "14" "17" "21" "26" ...
##  $ Game5    : chr  "W" "W" "W" "D" ...
##  $ Opp5     : chr  "7" "16" "11" "5" ...
##  $ Game6    : chr  "D" "W" "W" "W" ...
##  $ Opp6     : chr  "12" "20" "13" "19" ...
##  $ Game7    : chr  "D" "W" "W" "D" ...
##  $ Opp7     : chr  "4" "7" "12" "1" ...
##  $ V1.1     : chr  "   ON " "   MI " "   MI " "   MI " ...
##  $ PID      : chr  " 15445895" " 14598900" " 14959604" " 12616049" ...
##  $ PreRating: chr  "1794" "1553" "1384" "1716" ...

Change required character columns to numeric, view structure.

chess[, c(1,3,5,7,9,11,13,15,17,19,20)] <- sapply(chess[, c(1,3,5,7,9,11,13,15,17,19,20)], as.numeric)
str(chess)
## 'data.frame':    64 obs. of  20 variables:
##  $ V1       : num  1 2 3 4 5 6 7 8 9 10 ...
##  $ V2       : chr  " GARY HUA                        " " DAKSHESH DARURI                 " " ADITYA BAJAJ                    " " PATRICK H SCHILLING             " ...
##  $ V3       : num  6 6 6 5.5 5.5 5 5 5 5 5 ...
##  $ Game1    : chr  "W" "W" "L" "W" ...
##  $ Opp1     : num  39 63 8 23 45 34 57 3 25 16 ...
##  $ Game2    : chr  "W" "W" "W" "D" ...
##  $ Opp2     : num  21 58 61 28 37 29 46 32 18 19 ...
##  $ Game3    : chr  "W" "L" "W" "W" ...
##  $ Opp3     : num  18 4 25 2 12 11 13 14 59 55 ...
##  $ Game4    : chr  "W" "W" "W" "W" ...
##  $ Opp4     : num  14 17 21 26 13 35 11 9 8 31 ...
##  $ Game5    : chr  "W" "W" "W" "D" ...
##  $ Opp5     : num  7 16 11 5 4 10 1 47 26 6 ...
##  $ Game6    : chr  "D" "W" "W" "W" ...
##  $ Opp6     : num  12 20 13 19 14 27 9 28 7 25 ...
##  $ Game7    : chr  "D" "W" "W" "D" ...
##  $ Opp7     : num  4 7 12 1 17 21 2 19 20 18 ...
##  $ V1.1     : chr  "   ON " "   MI " "   MI " "   MI " ...
##  $ PID      : num  15445895 14598900 14959604 12616049 14601533 ...
##  $ PreRating: num  1794 1553 1384 1716 1655 ...

Create lookup table of all players and their pre-game rating, view structure.

tblookup <- subset(chess[, c(1,20)])
str(tblookup)
## 'data.frame':    64 obs. of  2 variables:
##  $ V1       : num  1 2 3 4 5 6 7 8 9 10 ...
##  $ PreRating: num  1794 1553 1384 1716 1655 ...

Perform lookup for each opponent by their opponent number.

combo<- merge(chess, tblookup, by.x="Opp1",by.y="V1", sort=FALSE)
combo<- merge(combo, tblookup, by.x="Opp2",by.y="V1", sort=FALSE)
combo<- merge(combo, tblookup, by.x="Opp3",by.y="V1", sort=FALSE)
## Warning in merge.data.frame(combo, tblookup, by.x = "Opp3", by.y = "V1", :
## column names 'PreRating.x', 'PreRating.y' are duplicated in the result
combo<- merge(combo, tblookup, by.x="Opp4",by.y="V1", sort=FALSE)
## Warning in merge.data.frame(combo, tblookup, by.x = "Opp4", by.y = "V1", :
## column names 'PreRating.x', 'PreRating.y' are duplicated in the result
combo<- merge(combo, tblookup, by.x="Opp5",by.y="V1", sort=FALSE)
## Warning in merge.data.frame(combo, tblookup, by.x = "Opp5", by.y = "V1", :
## column names 'PreRating.x', 'PreRating.y', 'PreRating.x', 'PreRating.y' are
## duplicated in the result
combo<- merge(combo, tblookup, by.x="Opp6",by.y="V1", sort=FALSE)
## Warning in merge.data.frame(combo, tblookup, by.x = "Opp6", by.y = "V1", :
## column names 'PreRating.x', 'PreRating.y', 'PreRating.x', 'PreRating.y' are
## duplicated in the result
combo<- merge(combo, tblookup, by.x="Opp7",by.y="V1", sort=FALSE)
## Warning in merge.data.frame(combo, tblookup, by.x = "Opp7", by.y = "V1", :
## column names 'PreRating.x', 'PreRating.y', 'PreRating.x', 'PreRating.y',
## 'PreRating.x', 'PreRating.y' are duplicated in the result

Discard unneeded columns, rename columns, view result.

combo <- subset(combo[, c(8:10,18:27)])
colnames(combo) <- c("Order","Player","Points","State","Player ID","PreRating","Opp1PR","Opp2PR","Opp3PR","Opp4PR","Opp5PR","Opp6PR","Opp7PR")
knitr::kable(combo)
Order Player Points State Player ID PreRating Opp1PR Opp2PR Opp3PR Opp4PR Opp5PR Opp6PR Opp7PR
1 GARY HUA 6.0 ON 15445895 1794 1436 1563 1600 1610 1649 1663 1716
2 DAKSHESH DARURI 6.0 MI 14598900 1553 1175 917 1716 1629 1604 1595 1649
3 ADITYA BAJAJ 6.0 MI 14959604 1384 1641 955 1745 1563 1712 1666 1663
4 PATRICK H SCHILLING 5.5 MI 12616049 1716 1363 1507 1553 1579 1655 1564 1794
5 HANSHI ZUO 5.5 MI 14601533 1655 1242 980 1663 1666 1716 1610 1629
6 HANSEN SONG 5.0 OH 15055204 1686 1399 1602 1712 1438 1365 1552 1563
7 GARY DEE SWATHELL 5.0 MI 11146376 1649 1092 377 1666 1712 1794 1411 1553
8 EZEKIEL HOUGHTON 5.0 MI 15142253 1641 1384 1441 1610 1411 1362 1507 1564
9 STEFANO LEE 5.0 ON 14954524 1411 1745 1600 853 1641 1579 1649 1595
10 ANVIT RAO 5.0 MI 14150362 1365 1604 1564 1186 1494 1686 1745 1600
11 CAMERON WILLIAM MC LEMAN 4.5 MI 12581589 1712 1423 1153 1686 1649 1384 1399 1579
13 TORRANCE HENRY JR 4.5 MI 15082995 1666 1355 1552 1649 1655 1449 1384 1441
14 BRADLEY SHAW 4.5 MI 10131499 1610 1270 1199 1641 1794 1552 1655 1494
15 ZACHARY JAMES HOUGHTON 4.5 MI 15619130 1220 1564 1604 1522 1555 1270 1449 1423
17 RONALD GRZEGORCZYK 4.0 MI 10297702 1629 1382 1403 1579 1553 1363 1555 1655
18 DAVID SUNDEEN 4.0 MI 11342094 1600 1362 1411 1794 1441 1564 1423 1365
19 DIPANKAR ROY 4.0 MI 14862333 1564 1220 1365 935 1507 1600 1716 1641
20 JASON ZHENG 4.0 MI 14529060 1595 1348 1291 1363 1403 1507 1553 1411
21 DINH DANG BUI 4.0 ON 15495066 1563 1283 1794 1362 1384 1348 1436 1686
23 ALAN BUI 4.0 ON 15030142 1363 1716 1283 1595 917 1629 980 377
24 MICHAEL R ALDRICH 4.0 MI 13469010 1229 1507 1362 1283 1745 967 1199 1436
25 LOREN SCHWIEBERT 3.5 MI 12486656 1745 1411 1393 1384 1229 1399 1365 1362
26 MAX ZHU 3.5 ON 15131520 1579 1291 1348 1629 1716 1411 1441 1712
28 SOFIA ADINA STANESCU-BELLU 3.5 MI 14882954 1507 1229 1716 1555 1564 1595 1641 1355
30 GEORGE AVERY JONES 3.5 ON 12577178 1522 935 1163 1220 1186 1494 955 1056
31 RISHI SHETTY 3.5 MI 15131618 1494 917 1186 1163 1365 1522 1056 1610
32 JOSHUA PHILIP MATHEWS 3.5 ON 14073750 1441 955 1641 1199 1600 1011 1579 1666
33 JADE GE 3.5 MI 14691842 1449 967 1663 1056 1355 1666 1220 1011
34 MICHAEL JEFFERY THOMAS 3.5 MI 15051807 1399 1686 967 980 1602 1745 1712 935
35 JOSHUA DAVID LEE 3.5 MI 14601397 1438 377 1423 1153 1686 1092 935 1382
39 JOEL R HENDON 3.0 MI 12923035 1436 1794 1270 1348 1604 1199 1563 1229
40 FOREST ZHANG 3.0 MI 14892710 1348 1595 1579 1436 853 1563 1153 1555
42 JARED GE 3.0 MI 14462326 1332 1663 1056 1092 967 955 1163 1153
43 ROBERT GLEN VASEY 3.0 MI 14101068 1283 1563 1363 1229 1175 853 377 1186
45 DEREK YAN 3.0 MI 15372807 1242 1655 1011 967 1153 1175 1186 917
46 JACOB ALEXANDER LAVALLEY 3.0 MI 15490981 377 1438 1649 1552 1056 1163 1283 1363
47 ERIC WRIGHT 2.5 MI 12533115 1362 1600 1229 1563 955 1641 1011 1745
51 TEJAS AYYAGARI 2.5 MI 15205474 1011 1552 1242 1355 1092 1441 1362 1449
52 ETHAN GUO 2.5 MI 14918803 935 1522 1555 1564 1382 1602 1438 1399
61 JEZZEL FARKAS 1.5 ON 15771592 955 1441 1384 1270 1362 1332 1522 980
64 BEN LI 1.0 MI 15006561 1163 1555 1522 1494 1291 377 1332 1270

Add column for average opponent pre-game rating, view result.

combo <- mutate(combo, OppMean = round(rowMeans(combo[,7:13]),0))
knitr::kable(combo)
Order Player Points State Player ID PreRating Opp1PR Opp2PR Opp3PR Opp4PR Opp5PR Opp6PR Opp7PR OppMean
1 GARY HUA 6.0 ON 15445895 1794 1436 1563 1600 1610 1649 1663 1716 1605
2 DAKSHESH DARURI 6.0 MI 14598900 1553 1175 917 1716 1629 1604 1595 1649 1469
3 ADITYA BAJAJ 6.0 MI 14959604 1384 1641 955 1745 1563 1712 1666 1663 1564
4 PATRICK H SCHILLING 5.5 MI 12616049 1716 1363 1507 1553 1579 1655 1564 1794 1574
5 HANSHI ZUO 5.5 MI 14601533 1655 1242 980 1663 1666 1716 1610 1629 1501
6 HANSEN SONG 5.0 OH 15055204 1686 1399 1602 1712 1438 1365 1552 1563 1519
7 GARY DEE SWATHELL 5.0 MI 11146376 1649 1092 377 1666 1712 1794 1411 1553 1372
8 EZEKIEL HOUGHTON 5.0 MI 15142253 1641 1384 1441 1610 1411 1362 1507 1564 1468
9 STEFANO LEE 5.0 ON 14954524 1411 1745 1600 853 1641 1579 1649 1595 1523
10 ANVIT RAO 5.0 MI 14150362 1365 1604 1564 1186 1494 1686 1745 1600 1554
11 CAMERON WILLIAM MC LEMAN 4.5 MI 12581589 1712 1423 1153 1686 1649 1384 1399 1579 1468
13 TORRANCE HENRY JR 4.5 MI 15082995 1666 1355 1552 1649 1655 1449 1384 1441 1498
14 BRADLEY SHAW 4.5 MI 10131499 1610 1270 1199 1641 1794 1552 1655 1494 1515
15 ZACHARY JAMES HOUGHTON 4.5 MI 15619130 1220 1564 1604 1522 1555 1270 1449 1423 1484
17 RONALD GRZEGORCZYK 4.0 MI 10297702 1629 1382 1403 1579 1553 1363 1555 1655 1499
18 DAVID SUNDEEN 4.0 MI 11342094 1600 1362 1411 1794 1441 1564 1423 1365 1480
19 DIPANKAR ROY 4.0 MI 14862333 1564 1220 1365 935 1507 1600 1716 1641 1426
20 JASON ZHENG 4.0 MI 14529060 1595 1348 1291 1363 1403 1507 1553 1411 1411
21 DINH DANG BUI 4.0 ON 15495066 1563 1283 1794 1362 1384 1348 1436 1686 1470
23 ALAN BUI 4.0 ON 15030142 1363 1716 1283 1595 917 1629 980 377 1214
24 MICHAEL R ALDRICH 4.0 MI 13469010 1229 1507 1362 1283 1745 967 1199 1436 1357
25 LOREN SCHWIEBERT 3.5 MI 12486656 1745 1411 1393 1384 1229 1399 1365 1362 1363
26 MAX ZHU 3.5 ON 15131520 1579 1291 1348 1629 1716 1411 1441 1712 1507
28 SOFIA ADINA STANESCU-BELLU 3.5 MI 14882954 1507 1229 1716 1555 1564 1595 1641 1355 1522
30 GEORGE AVERY JONES 3.5 ON 12577178 1522 935 1163 1220 1186 1494 955 1056 1144
31 RISHI SHETTY 3.5 MI 15131618 1494 917 1186 1163 1365 1522 1056 1610 1260
32 JOSHUA PHILIP MATHEWS 3.5 ON 14073750 1441 955 1641 1199 1600 1011 1579 1666 1379
33 JADE GE 3.5 MI 14691842 1449 967 1663 1056 1355 1666 1220 1011 1277
34 MICHAEL JEFFERY THOMAS 3.5 MI 15051807 1399 1686 967 980 1602 1745 1712 935 1375
35 JOSHUA DAVID LEE 3.5 MI 14601397 1438 377 1423 1153 1686 1092 935 1382 1150
39 JOEL R HENDON 3.0 MI 12923035 1436 1794 1270 1348 1604 1199 1563 1229 1430
40 FOREST ZHANG 3.0 MI 14892710 1348 1595 1579 1436 853 1563 1153 1555 1391
42 JARED GE 3.0 MI 14462326 1332 1663 1056 1092 967 955 1163 1153 1150
43 ROBERT GLEN VASEY 3.0 MI 14101068 1283 1563 1363 1229 1175 853 377 1186 1107
45 DEREK YAN 3.0 MI 15372807 1242 1655 1011 967 1153 1175 1186 917 1152
46 JACOB ALEXANDER LAVALLEY 3.0 MI 15490981 377 1438 1649 1552 1056 1163 1283 1363 1358
47 ERIC WRIGHT 2.5 MI 12533115 1362 1600 1229 1563 955 1641 1011 1745 1392
51 TEJAS AYYAGARI 2.5 MI 15205474 1011 1552 1242 1355 1092 1441 1362 1449 1356
52 ETHAN GUO 2.5 MI 14918803 935 1522 1555 1564 1382 1602 1438 1399 1495
61 JEZZEL FARKAS 1.5 ON 15771592 955 1441 1384 1270 1362 1332 1522 980 1327
64 BEN LI 1.0 MI 15006561 1163 1555 1522 1494 1291 377 1332 1270 1263

Discard unneeded columns, view result.

results <- subset(combo[,c(1:6,14)])
knitr::kable(results)
Order Player Points State Player ID PreRating OppMean
1 GARY HUA 6.0 ON 15445895 1794 1605
2 DAKSHESH DARURI 6.0 MI 14598900 1553 1469
3 ADITYA BAJAJ 6.0 MI 14959604 1384 1564
4 PATRICK H SCHILLING 5.5 MI 12616049 1716 1574
5 HANSHI ZUO 5.5 MI 14601533 1655 1501
6 HANSEN SONG 5.0 OH 15055204 1686 1519
7 GARY DEE SWATHELL 5.0 MI 11146376 1649 1372
8 EZEKIEL HOUGHTON 5.0 MI 15142253 1641 1468
9 STEFANO LEE 5.0 ON 14954524 1411 1523
10 ANVIT RAO 5.0 MI 14150362 1365 1554
11 CAMERON WILLIAM MC LEMAN 4.5 MI 12581589 1712 1468
13 TORRANCE HENRY JR 4.5 MI 15082995 1666 1498
14 BRADLEY SHAW 4.5 MI 10131499 1610 1515
15 ZACHARY JAMES HOUGHTON 4.5 MI 15619130 1220 1484
17 RONALD GRZEGORCZYK 4.0 MI 10297702 1629 1499
18 DAVID SUNDEEN 4.0 MI 11342094 1600 1480
19 DIPANKAR ROY 4.0 MI 14862333 1564 1426
20 JASON ZHENG 4.0 MI 14529060 1595 1411
21 DINH DANG BUI 4.0 ON 15495066 1563 1470
23 ALAN BUI 4.0 ON 15030142 1363 1214
24 MICHAEL R ALDRICH 4.0 MI 13469010 1229 1357
25 LOREN SCHWIEBERT 3.5 MI 12486656 1745 1363
26 MAX ZHU 3.5 ON 15131520 1579 1507
28 SOFIA ADINA STANESCU-BELLU 3.5 MI 14882954 1507 1522
30 GEORGE AVERY JONES 3.5 ON 12577178 1522 1144
31 RISHI SHETTY 3.5 MI 15131618 1494 1260
32 JOSHUA PHILIP MATHEWS 3.5 ON 14073750 1441 1379
33 JADE GE 3.5 MI 14691842 1449 1277
34 MICHAEL JEFFERY THOMAS 3.5 MI 15051807 1399 1375
35 JOSHUA DAVID LEE 3.5 MI 14601397 1438 1150
39 JOEL R HENDON 3.0 MI 12923035 1436 1430
40 FOREST ZHANG 3.0 MI 14892710 1348 1391
42 JARED GE 3.0 MI 14462326 1332 1150
43 ROBERT GLEN VASEY 3.0 MI 14101068 1283 1107
45 DEREK YAN 3.0 MI 15372807 1242 1152
46 JACOB ALEXANDER LAVALLEY 3.0 MI 15490981 377 1358
47 ERIC WRIGHT 2.5 MI 12533115 1362 1392
51 TEJAS AYYAGARI 2.5 MI 15205474 1011 1356
52 ETHAN GUO 2.5 MI 14918803 935 1495
61 JEZZEL FARKAS 1.5 ON 15771592 955 1327
64 BEN LI 1.0 MI 15006561 1163 1263

Export as CSV.

write.table(results, "chessresults.txt")

This can be done more easily.