#Load libraries
invisible(library(dplyr))
invisible(library(stringr))

Load the file into a data frame

#Load file into data frame
df1 <- read.table("tournamentinfo.txt", sep = "|",fill = TRUE) 

#Remove spacer rows
df1 <- df1[!grepl("--------", df1[,1]),] 

#Put state and score values in same rows
df1$state <- lead(df1$V1,1)
df1$V11 <- lead(df1$V2,1)

#Remove unused rows
df1 <- subset(df1,!str_detect(df1$V1,"[A-Z][A-Z]"))
df1 <- df1[-c(1, 2), ]

#Remove unused columns
rownames(df1) <- NULL
df1$V1 <- NULL

#Remove whitespace in names and state
df1$V2 <- str_squish(df1$V2)
df1$state <- str_squish(df1$state)

#Calculate scores
df1$V11 <- as.numeric(str_extract(df1$V11,"(?<=R:\\s{0,5})\\d+"))

#Extract opponent code
df1[,3:9] = lapply(df1[,3:9], function(i) as.numeric(str_extract(i,"(?<=[A-Z]\\s{0,5})\\d+")))

#Covert scores to numbers
df1$V3 <- as.numeric(as.character(df1$V3))

#Load opponent scores on separate columns
df1[,12:18] = lapply(df1[,3:9], function(i) df1$V11[i])

#Add means 
df1$mean <- round(rowMeans(df1[,12:18],na.rm = TRUE), digits = 0)

Extract the required variables

# Create data frame with the variables of interest
myvars <- c("V2", "state","V3","V11", "mean")
tresults <- df1[myvars]
names(tresults) <- c("name","state","tot_points","pre-rating","avg_opp_pre-rating")

head(tresults)
##                  name state tot_points pre-rating avg_opp_pre-rating
## 1            GARY HUA    ON        6.0       1794               1605
## 2     DAKSHESH DARURI    MI        6.0       1553               1469
## 3        ADITYA BAJAJ    MI        6.0       1384               1564
## 4 PATRICK H SCHILLING    MI        5.5       1716               1574
## 5          HANSHI ZUO    MI        5.5       1655               1501
## 6         HANSEN SONG    OH        5.0       1686               1519

Create the CSV file

#Create a CSV file with the results
write.csv(tresults, file = "tresults.csv")