In Part 1 of this blog post I show how you can scrape tables of sports data from websites and store that data in a data frame for data analysis (have I said ‘data’ enough times yet?).
Whenever you are automating data collection in this sort of way, you always want to get a “health check” on your new table to make sure nothing went awry. There are countless things that can go wrong from missing data to web pages timing out or blocking you from collecting data — and it is critical we understand whether any of this has happened before moving on to an analysis phase. You might call this a “data cleaning” phase to get you into position to analyze.
With the head function you can quickly get a glimpse of what your variable names look like, along with a few observations. Let’s take a look:
head(fb_main)
Date NU rank Opponent Site Outcome Score <U+00A0>
1 Sept. 17, 1960 #4 Texas Away Win 14-13 Details
2 Sept. 24, 1960 #12 Minnesota Home Loss 26-14 Details
3 Oct. 1, 1960 Iowa State Home Loss 10-7 Details
4 Oct. 8, 1960 Kansas State Home Win 17-7 Details
5 Oct. 15, 1960 Army Home Win 14-9 Details
6 Oct. 22, 1960 Colorado Away Loss 19-6 Details
Immediately a few concerns jump out at me. First, there is a column where all of the values contain the word “Details.” On the original site I drew this information from, this column linked out to details for each game. I do not need this column for any analysis, so I will remove it. There are numerous ways to do this. Since I know it is my seventh column, I’ll just do it this way. If you’re unsure about how to tackle this (football pun), then you may want to save the table as a different name and keep the original.
fb_main <- fb_main[,-7]
head(fb_main)
Date NU rank Opponent Site Outcome Score
1 Sept. 17, 1960 #4 Texas Away Win 14-13
2 Sept. 24, 1960 #12 Minnesota Home Loss 26-14
3 Oct. 1, 1960 Iowa State Home Loss 10-7
4 Oct. 8, 1960 Kansas State Home Win 17-7
5 Oct. 15, 1960 Army Home Win 14-9
6 Oct. 22, 1960 Colorado Away Loss 19-6
Much better. But it sure would be nice if the score was split into two columns in case I wanted to sum or average any of the scores during analysis. One variable can easily be split into two variables with a convenient function called separate, which is part of the tidyr package. It would look like this:
fb_main <- separate(fb_main, Score, into = c("Win Score", "Lose Score"), sep = "-")
But not so fast my friend — this really isn’t very helpful at all. We want the Husker scores in one column and the opponent score in another column, not a mix. This creates complexity to our code, but we can still accomplish it in a few easy steps. You will need to use dplyr, so make sure that is activated in your library.
library(tidyr) fb_main$NUScore <- as.numeric(ifelse(fb_main$Outcome=="Win", fb_main$`Winner Score`, fb_main$`Loser Score`)) fb_main$OppScore <- as.numeric(ifelse(fb_main$Outcome=="Win", fb_main$`Loser Score`, fb_main$`Winner Score`)) fb_main <- fb_main[, -c(6:7)]
The first two lines above create the new columns we want using an ifelse function. And I saved myself some time by making the columns numeric. The ifelse statement has three arguments: condition, value of new row if condition is true, value of new row if condition is false. The last line is simply deleting the Winner Score and Loser Score variables, which we no longer need. Now look at the data:
head(fb_main3)
Date NU rank Opponent Site Outcome NUScore OppScore
1 Sept. 17, 1960 #4 Texas Away Win 14 13
2 Sept. 24, 1960 #12 Minnesota Home Loss 14 26
3 Oct. 1, 1960 Iowa State Home Loss 7 10
4 Oct. 8, 1960 Kansas State Home Win 17 7
5 Oct. 15, 1960 Army Home Win 14 9
6 Oct. 22, 1960 Colorado Away Loss 6 19
Some other observations I have made about this data is that the Opponent variable contains both the opponent name and their ranking (this could create difficulties down the road), some values are missing, and the rankings contain a hash fragment (#). These are all worth tidying up before analysis, but I’ll stop there since the above should provide enough direction to complete those tasks.
One final consideration to make once you have collected all of this data is where to store it. There are many options and each of them have different methods in R. But here are a few to consider so that you do not need to re-gather the data each time you need it for analysis:
- Save as a data frame in R
- Use write.csv to save it on your computer as a .csv file (a similar function exists for Excel)
- Send to a local or cloud-based database
- Upload to data.world
Here is the final data, uploaded to data.world: https://data.world/jeffgswanson/husker-football-game-results
Enjoy.