5_data_cleanup.Rmd

---
title: "R Notebook"
output: html_notebook
---

# Introduction

This notebook is designed to clean and merge CSV files containing player ranking data. It includes steps to ensure data integrity by fixing ranking inconsistencies and appending year information based on filenames.

# Load Libraries

Start by loading necessary libraries for data manipulation.


```{r}
library(dplyr)
library(readr)
library(tidyr)
```

# Define Functions for Data Cleaning

## Fix Ranking Data

Create a function to correct any issues with the 'Rank' column in the dataset, such as filling missing values and converting them to integers.


```{r}
fix_ranking <- function(df) {
  df <- df %>% 
    mutate(Rank = as.character(Rank)) %>% 
    fill(Rank, .direction = "down") %>%
    mutate(Rank = as.integer(Rank))
  return(df)
}
```

## Read and Fix CSV Files

Define a function to read CSV files, apply the ranking fix, and extract the year from the filename to add as a new column.

```{r}
read_and_fix_csv_with_year <- function(file_path) {
  df <- read_csv(file_path, show_col_types = FALSE)
  fixed_df <- fix_ranking(df)
  year <- as.integer(gsub(".*_(\\d{4})\\.csv$", "\\1", basename(file_path)))
  fixed_df$Year <- year
  return(fixed_df)
}
```

# Merge CSV Files

Combine multiple CSV files from a directory into a single dataset with corrected rankings and appended year information.

## Execute Merging Process

Run the function to merge all CSV files in the specified directory into a single cleaned and consolidated dataset.

```{r}
merge_csv_files_with_year <- function(dir_path, output_file) {
  file_paths <- list.files(path = dir_path, pattern = "*.csv", full.names = TRUE)
  merged_df <- bind_rows(lapply(file_paths, read_and_fix_csv_with_year))
  write_csv(merged_df, output_file)
}
```

```{r}
merge_csv_files_with_year("top_100_each_year", "dataset/rank.csv")
```


Now data is clean.

Not all data is alphanumeric and not all rows are actual players (duplicate header rows).