For this project, we first had to clean our data so that it was in a format that was easy to work with. Some of the issues with the original data were that the college names were not standardized, so the same college appeared under multiple slightly different variations of the same name. For example, the dataset contained AUGSBURG, Augsburg and Augsburg College all referring to the same school. So, we used R and regular expressions in order to make sure that the data was standardized in that way. (See the R-script here)
Then, we used this data to create three datasets, one for each of the maps. The first dataset we created consisted of every team Carleton has played, the number of times we have played that school, and the first and last year we played them. Next, we created a second dataset which consisted of every team that Carleton has played that was an away game, and the number of times Carleton has played an away game there. Finally, we created a third dataset which just consisted of the games Carleton played in the 2023 season, and the score and location (home or away) of those games.
For each of our datasets we used Geocode by AwesomeTables, which is a Google Sheets extension that generates latitude and longitude coordinates for places in your google sheet. However, we had some issues with that such as Beloit returning the coordinates for the city, rather than for the college. So, in situations like that we checked the locations manually using Google Maps to ensure that we had the right coordinates for each school.
Next, we began to create the maps. We first created a map of all of the away games Carleton has played, with the points color coded by the number of times Carleton played each school. We also created a time-series map for all of the opponents Carleton has ever had. This map moves through time in 5-year intervals, populating points onto the map for every new school that Carleton played within that 5 year period. This allows someone viewing the map to see how the number of different schools Carleton has played has changed throughout the years, as well as the geographic location of those schools and how more recently Carleton has started playing schools from further away when in the past it was more localized to the Midwest. Finally, we constructed a story map for the 2023 season. This map allows the viewer to experience the season by moving from stadium to stadium and being able to see the season game by game with the score as well as a link to the game recap and a photo from the game. This way we were able to use some of the data to create an interactive experience for the user to “relive” the 2023 Carleton football season.