data_cleaning.qmd

---
title: "Data cleaning in R"
toc: true
toc-depth: 2
number-sections: false
highlight-style: github
format:
  html:
    self-contained: true
    code-fold: true
    code-summary: "Show the code"
    code-tools: true
    theme: united 
knitr: 
 opts_knit: 
   warning: false
   message: false
editor: visual
---

Data and system challenges

Public health data encounter various constraints due to the heterogeneity in data sources, collection methods, and volume. These constraints, often referred to as data quality dimensions, include attributes such as:

Completeness: Captured but not reported. Timeliness: Late reporting. Availability: Captured and reported but not accessible for use. Incomplete/Poor Recording: Some important variables or attributes not captured. Consistency: Always tells a similar fact/story. Aggregated: Masks important information relevant for decision-making. Big Data: An ambiguous dimension. Name Mismatching: Misspelled facility or area names.

Arguments suggest that having minimal useful data in real time, well-utilized, is better than having lots of data at a low speed and poorly utilized. The choice is yours! As data grows, advanced skills and tools become necessary.

Reading and exploring data

In R, you can import various types of data. Common file types include .csv, .dta, and .xlsx. To import CSV files into RStudio, use the base function read.csv(). Assign the imported dataset to an object (e.g., “routine_data”) using the <- operator.

For this training, we’re using a dataset called “routine_data,” extracted from DHIS2. It contains variables related to malaria tests and confirmed cases, stratified by age and sex, reported by districts.

For data management, consider using the tidyverse package (install it using install.packages("tidyverse")). The tidyverse includes various packages (like dplyr, ggplot2, tidyr) that enhance data manipulation and visualization.

Happy data exploration! 📊🔍👩‍💻

Install and load packages if required

```{r}
if (!requireNamespace("tidyverse", quietly = TRUE)) {
  install.packages("tidyverse", repos = "http://cran.us.r-project.org") 
}
if (!requireNamespace("janitor", quietly = TRUE)) {
  install.packages("janitor", repos = "http://cran.us.r-project.org") 
}
if (!requireNamespace("hablar", quietly = TRUE)) {
  install.packages("hablar", repos = "http://cran.us.r-project.org") 
}
if (!requireNamespace("haven", quietly = TRUE)) {
  install.packages("haven", repos = "http://cran.us.r-project.org") 
}


# Load libraries
library(tidyverse)   # Data management
library(janitor)     # Clean names
library(hablar)      # Convert data type
library(haven)       # To read and save '.dta' files
library(lubridate)   # For date. In the new tidyverse package, lubridate is included as one tidyverse of package lists.
```
Set working directory

```{r}
setwd("C:/Users/user/Documents/Data_management_in_R/Data/")  # Set your desired directory here
```

Data can be stored in different file types such as csv, .xlsx, .dta and .sav. Remember, we need to know the file formats of our data to install any necessary packages (e.g., readxl, haven) using install.packages("package_name").

Let’s break down the information about reading different file types into R:

CSV files: The most common file format is CSV (Comma-Separated Values). To import CSV files into R, you can use the read.csv() function from the base R package.

Example using read.csv()

```{r, base_R, eval=FALSE}
routine_data <- read.csv("Number of slides or RDT positive for malaria disaggregated by age & sex_woredas.csv") %>%
  clean_names()  # Clean column names
```

Example using read_csv():

```{r,option_2, eval==FALSE}
routine_data <- read.csv("Number of slides or RDT positive for malaria disaggregated by age & sex_woredas.csv") %>%
  clean_names()  # Clean column names
```

Whilst read.csv() is the most common way to read data into R, there is the alternative read_csv() function (note the underscore). This is part of the tidyverse group of packages and is often a better option for reading in CSV files. This function is quicker, it reads the data in as a tibble instead of a data frame and allows for non standard variable names among other benefits.

There are various options we can use when importing data, such as whether to include headers, what character to use for decimal points, what to import as missing values. To explore these options you can look at the help pages e.g. ?read_csv.

Excel Files .xlsx : To read Excel files into R, use the read_excel() function from the readxl package. 

Example:

```{r, read_excel_file, eval=FALSE}
# Examples, don't run
library(readxl) 
my_excel_data <- read_excel("data",sheet = "sheet1", col_names = TRUE, na = "NA")
```

Stata Files .dta: To read the Stata files, use the read_dta() function from the haven package (part of the tidyverse). 

Example:

```{r, stata_file, eval=FALSE}
library(haven)
my_stata_data <- read_dta("path/stata_file.dta")
```

Other Data Formats: Other formats like SPSS, Matlab, and binary files can also be read using specific functions.

For SPSS files, use read_sav() from the haven package.

For Matlab files, use readMat() from the R.matlab package.

For binary files, explore relevant functions based on your specific needs

Import and Exploring Data

About the data:

The data I used for this demonstration is a sample of routine malaria data from DHIS2.

```{r, import_csv_data, eval=FALSE}
routine_data <- read_csv("C:/Users/user/Documents/Data_management_in_R/Data/Number of slides or RDT positive for malaria disagregated by age & sex_ woredas.csv") |> 
clean_names() #  make the column names readable
 ```

Explore the data

To get an overview of the data, we have several options:

 View the whole data: You can view the entire dataset by clicking on it in the global environment window. Alternatively, you can use the command View(routine_data) to open a window displaying the data.

```{r, view, eval=FALSE}
 View(routine_data)
```
View the Top Five Rows: To quickly inspect the first few rows of the dataset, you can use the head() function to see the top rows and tail() function to see the last rows . This function displays the first n rows of the data.

```{r, view_rows_of_the_dataframe, eval=FALSE}
head(routine_data, 5) # top five
tail(routine_data, 5) # bottom five
```
Understand the structure of the data: To understand the structure of the data, we can use either the str() or glimpse() function.

str() provides a concise summary of the structure of the dataset, including the data types and the first few values of each variable.

glimpse() is part of the tidyverse ecosystem and offers a similar summary but with a focus on tibbles, providing additional information such as variable types and the first few values of each variable, displayed in a more compact format.

```{r, str, eval=FALSE}
str(routine_data) # Use str() for a concise summary
```
```{r, row_x_col, eval=FALSE}
glimpse(routine_data) # Use glimpse() for a tidyverse-friendly summary
```
Check the Class: You can check the class of the dataset, which could be either tbl or data.frame.

```{r}
class(routine_data)
```
Select Specific Rows or Columns: If you want to select specific rows or columns, you can use square brackets.

```{r view_the second_col}
head(routine_data[,2], 5) #View the second column
```
```{r first_row}
routine_data[1,] # view the first row
```
Check Column Names: Checking column names is essential for identifying any missing or misspelled variables. Ensure that variable names are clean, short, and readable.

```{r}
colnames(routine_data) # using colnames

names(routine_data) # using names

variable.names(routine_data) # Assuming variable.names() is a custom function to retrieve variable names
```
Task 1: - Import the routine_data.csv file using the read_csv() function, setting -9999 values to NA, call the object "routine_data" - Explore the data using functions such as str(), head() and colname ()

Basic data cleaning in Tidyverse

In this section I introduce you to the tidyverse packages, demonstrating how they streamline data exploration, manipulation, and analysis.

Introduction to Tidyverse: Tidyverse comprises a suite of R packages tailored for data science, aiming to simplify data cleaning and analysis tasks. Leveraging various packages and functions, coupled with the %>% or |> pipe operator, Tidyverse promotes clear, reproducible code.

Cleaning Column Names: Initially, we address long column names containing extraneous information such as months and years. To make these names readable, our approach involves removing the prefix number_of_slides_or_rdt_positive_for_.

Identifying Character Strings: Before proceeding, we verify that the column names are character strings using the class() function.

Pattern Matching and Replacement: Utilizing the gsub() function from the base R package grep, we implement pattern matching and replacement. The syntax for gsub() is straightforward:

```{r}
gsub(pattern, replacement, x)
```
Here,

pattern: The target pattern, in our case, number_of_slides_or_rdt_positive_for_. replacement: The desired replacement, typically an empty string " ". x: The string to be searched, representing the portion of the column name following the pattern.

```{r}
colnames(routine_data) <- gsub("number_of_slides_or_rdt_positive_for_", "", colnames(routine_data))
```
With the prefix number_of_slides_or_rdt_positive_for_ successfully removed, let's confirm the change.

```{r}
colnames(routine_data)
```
Standardize District Names

The column organisationunitname contains district names, but they exhibit inconsistencies. For instance, some districts like Addis Abeba have the suffix Subcity, while others have WorHo. Additionally, the lengths of these suffixes vary. We'll standardize these suffixes using the str_replace() function.

```{r}
## Replace "SubCity" with "WorHO"
routine_data$organisationunitname <- str_replace(routine_data$organisationunitname, "SubCity", "WorHO")
```
To remove the WorHo suffix, we utilize str_sub() from the stringr package, adjusting the number of characters with nchar().

```{r}
routine_data$organisationunitname <- str_sub(routine_data$organisationunitname, 1, nchar(routine_data$organisationunitname)-5)
```
Clean zone information

Cleaning the zone column involves replacing occurrences of "Sub City" and "WorHO" with "ZHD" and removing redundant substrings like "Town" and "town".

```{r}
# Replace "Sub City" and "WorHO" with "ZHD"
routine_data$zone <- str_replace(routine_data$zone, c("Sub City", "WorHO"), "ZHD") 

# Remove "Town" and "town"
routine_data$zone <- gsub("Town|town", "", as.character(routine_data$zone)) 

# Trim excess characters
routine_data$zone <- str_sub(routine_data$zone , 1, nchar(routine_data$zone)-3)
```
Clean Region column The region column needs trimming to remove the string "Regional Health Bureau".

```{r}
routine_data$region <- str_sub(routine_data$region , 1, nchar(routine_data$region)-23)
```
Now we have a clean region, zone, organisationunitname along with the confirmed cases stratified by sex and age. After these transformations, let's rename the organisationunitname column to "district" for clarity using the rename() function in tidyverse package.

```{r}
routine_data <- rename(routine_data,
                           "district" = "organisationunitname")
```
Remove White Spaces

Following the cleanup, we notice trailing white spaces in the district, zone, and region columns. We eliminate these using trimws().

```{r view_white_spaces}
data.frame(levels(as.factor(routine_data$district)))[1:5,]
```
Let's remove the white space for all three columns

```{r Trim_white_spaces}
routine_data$district <- trimws(routine_data$district, which = c("both"))
routine_data$region <- trimws(routine_data$region, which = c("both"))
routine_data$zone <- trimws(routine_data$zone, which = c("both"))
```
##data.frame(levels(as.factor(routine_data$district)))[1:5,]

## Subsetting Data by Age Group

As each column name denotes age and sex, we'll subset the data by age groups. Below are examples for under-five, 5-14 years, and 15 years and above.

```{r under_five age group}
u5 <- select(routine_data, region:district, starts_with("malaria_5_years_"))

##Age groups 5-14 years .

{r age5-14_years}
  select(routine_data,
         region:district,contains("_5_14_years")) ->age5_14

Age group 15 and above

##{r age_15_and_over}
select(routine_data, region:district,malaria_male_15_years_hamle_2013:malaria_female_15_years_sene_2014) ->age_ov15
```


#### Organize the data

Reshaping or pivoting data is an important part of data cleaning and manipulation. Tidyverse has introduced the functions `pivot_wider()` and `pivot_longer()` to improve the ease of reshaping data in R. For details please read [Advanced manipulation of data frames section](https://malaria-atlas-project.gitlab.io/intro-to-spatial-analysis-for-infectious-diseases/02_datahandling.html#Advanced_manipulation_of_data_frames "Reshshaping data") in the MAP training module.

```{r to_long_format}
under_five <- u5 %>%
  pivot_longer(cols = malaria_5_years_male_hamle_2013:malaria_5_years_female_sene_2014,
          names_to = c("Age","Sex", "Month","Year"),
                         names_pattern = "malaria_(.*)_years_(.*)_(.*)_(.*)",
                         values_to = "conf") 
head(under_five)
```

```{r}

age5_14long <- age5_14 %>%
  pivot_longer(cols = malaria_male_5_14_years_hamle_2013:malaria_female_5_14_years_sene_2014,
          names_to = c("Sex","l_age", "u_age", "Month","Year"),
                         names_pattern = "malaria_(.*)_(.*)_(.*)_years_(.*)_(.*)",
                         values_to = "conf") |> 
  ## create a new column called 'Age' by combining the l_age and u_age. The 'mutate()' function will be covered below in the data manupulation section.  

          mutate(Age = paste(l_age, "-", u_age)) |>  
          select(-l_age,-u_age)

head(age5_14long)
```

```{r ov15}
age_ov15long <- age_ov15 |> 
  pivot_longer(cols = malaria_male_15_years_hamle_2013:malaria_female_15_years_sene_2014,
          names_to = c("Sex","Age", "Month","Year"),
                         names_pattern = "malaria_(.*)_(.*)_years_(.*)_(.*)",
                         values_to = "conf")
head(age_ov15long)
```

NOTE: When using tidyverse there are a range of helper functions to help you concisely refer to multiple variables based on their name. This makes it easier to select numerous variables and includes helper functions such as starts_with() and ends_with(). To explore these further look at ?select

The three data frames have an equal length and column names. To merge three data frames (datasets) horizontally, we can use the `merge()` function in the R language. To bind or combine rows in R, use the `rbind()` function. The `rbind()` stands for row binding. The `bind_rows()` is a function in *tidyverse* package that combines more than two [vectors](https://r-lang.com/r-vector-to-dataframe/), [matrices](https://r-lang.com/what-is-r-matrix-create-access-edit-and-delete-matrix-in-r/), and/or [data frames](https://r-lang.com/r-data-frame/) by rows. The data frames **must** have the same variables, but they do not have to be in the same order. Before binding the three dataframes, let's check the length and orders

```{r check_colnames_order_and_length}

#under 5
dim(under_five)
colnames(under_five)

#5-14
dim(age5_14long)
colnames(age5_14long)

#Over 15
dim(age_ov15long)
colnames(age_ov15long)

```

The three database has similar lengths but different column names order.

```{r bind_in_rows}
routine_data_new <- bind_rows(under_five, age5_14long,age_ov15long)
```

The newly created datasets contains locations (region, zone, district), age, sex, Year, Month and confirmed cases.

`summary()`and `summarise` will provide a summary of the numerical variable, whilst `table()` will tabulate categorical variables.

```{r summary}
summary(routine_data_new) #the whole dataset

summary(routine_data_new$conf)

table(routine_data$region) #one col

```

### Clean NA's

There are 29675 rows with NA value. We can exclude or replace 'NA'. All NA's return as FALSE

```{r echo=FALSE}
sum(is.na(routine_data_new)) #data frame
       
summary(!is.na(routine_data_new)) #each column

#apply(is.na(routine_data_new),2,which)# positions of missing value in each colum

#which(is.na(routine_data_new))#Identify locations of NA's
```

```{r replace_na, echo=FALSE}
routine_data_new$conf_nona[is.na(routine_data_new$conf)] == -9999 

mutate(routine_data_new,conf_nona = ifelse(is.na(conf), -9999, conf))
```

Create a column of confirmed cases for each sex and age group.

```{r summarise}
routine_data_new <-  routine_data_new |> 
group_by(region,zone,district,Month, Year) |>  
pivot_wider(names_from = c("Sex","Age"),# reshape from long to wide format
              names_sep = "_",
              values_from = c("conf")) |> 
  rowwise() |> #sum in rows
  #create a new column of confirmed cases for each sex and age group
  summarise(female = sum(female_5,`female_5 - 14`,female_15, na.rm = TRUE),
            male = sum(male_5,`male_5 - 14`,male_15, na.rm = TRUE),
            conf_5 = sum(female_5,male_5, na.rm = TRUE),
            conf_514 = sum(`female_5 - 14`,`male_5 - 14`, na.rm = TRUE),
            conf_15 = sum(female_15,male_15, na.rm = TRUE),
            cases = sum(conf_5,conf_514,conf_15))

```

::: callout-important
In the \`summarise()\` function, rows with \`NA\` values are assigned as zero. Please see the \`na.rm = TRUE\` function.
:::

The name and order of *`Month`* and `Year` variable are in Ethiopian calender. In Ethiopia the the first month of the year is `July`. For communication purpose (scientific publication), let's change the year and month variable into Gregorian calender format.

We can add new variables to the dataset or change existing variables using `mutate()`. Mutate allows us to assign new variables using the `=` sign. For example, here to change existing `Month` variable we could write:

```{r}
#Change the Month variable in the existing dataset
routine_data_new <- routine_data_new |> 
                      mutate(Month = ifelse(Month == "hamle", "July",
                                     ifelse(Month == "nehase", "August",
                                     ifelse(Month == "meskerem", "September",
                                     ifelse(Month == "tikemet", "October",
                                     ifelse(Month == "hidar", "November",
                                     ifelse(Month == "tahesas","December",
                                     ifelse(Month == "tir", "January",
                                     ifelse(Month == "yekatit", "February",
                                     ifelse(Month == "megabit", "March",
                                     ifelse(Month == "miazia", "April",
                                     ifelse(Month == "ginbot", "May",
                                     ifelse(Month == "sene", "June", NA)))))))))))))

levels(as.factor(routine_data_new$Month))
```

July is the first month of reporting physical year i.e., `Year 2013/July` is 2021.

```{r}
routine_data_new$Year[which((routine_data_new$Year=="2013") & (routine_data_new$Month =="July"))] <- "2013/14"
routine_data_new$Year[which((routine_data_new$Year=="2013") & (routine_data_new$Month =="August"))] <- "2013/14"

routine_data_new$Year[which(routine_data_new$Year=="2014")] <- "2022"
routine_data_new$Year[which(routine_data_new$Year=="2013/14")] <- "2022"
```

In our routine_data dataset we have a variable of month and year, from these we want to create one variable for date. We can do this using the `make_date()` function from `lubridate` package. This function expects inputs for the day, month and year. If the day or month is missing then this defaults to 1, and if the year is missing it defaults to 1970. We can use this function to create a date variable in our routine dataset, combining it with mutate.

```{r}
routine_data_new <- mutate(routine_data_new,
                       Date = make_date(year = as.numeric(Year), month =as.factor(Month)))
```

In Ethiopia, district boundaries are changed over time, reflecting new boundary designations for administration purposes across the study years.

District boundaries are changed over time, reflecting new boundary designations for administration purposes across the study years. For analysis purpose, we need to merege districts that underwent a boundary with their corresponding district before the change to create a stable unit. Collapse a set of districts into a single district, within a region and pass in a dataframe representing a region, a set of district names to target, and a name to use for replacing the given set of district names.

Data from the first 7 columns are kept static \-- only the first row is selected for each unique year-month combination. This loses the proper meaning of the given zone, unless the incoming name `(name_replace)` lies within the target zone. Consider replacing the zone name as well, if needed.

The remaining columns are summed (per column, not row) to reduce the data into the summary / combined row.

```{r echo=FALSE}
routine_data_new <-routine_data_new |> 
  select(region,zone,district,Month,Year,Date,                           female,male,conf_5,conf_514,conf_15,cases)
#' @param df_region Dataframe on which to operate, representing a single region
#' @param district_names Set of district names to target and collapse
#' @param name_replace Name to use for replacing the target district names
#' @param cols_static
#' @param cols_sum
#' @return Region dataframe with collapsed district rows
collapse_district <- function(
    df_region,
    district_names,
    name_replace,
    cols_static,
    cols_sum
  ) {

  # Subset the dataframe to target districts
  target_rows <- unname(unlist(Map(
    function(x) any(x == district_names),
    df_region$district
  )))

  df_district <- df_region[target_rows, ]

  # Get data split by year-month
  df_dist_ym <- Map(
    function(x) split(x, x$Month),        # Inner split -- month
    split(df_district, df_district$Year)  # Outer split -- year
  )

  # Collapse the data into new rows
  # - Outer iterates by year
  # - Inner iterates by month
  df_collapsed <- Map(
    function(x) Map(
      function(y) collapse_rows(
        y,
        name_replace = name_replace,
        cols_static  = cols_static,
        cols_sum     = cols_sum
      ),
      x
    ),
    df_dist_ym
  )

  # Bind together by row -- bind both inner and outer components
  do.call(rbind, do.call(rbind, df_collapsed))

}


#' Collapse the rows of a dataset into a single row, replacing district name
#'
#' The input dataframe `df` is intended to be one dataframe of one year-month
#' combination.  The dataframe of year-month combination should itself be
#' dataframe representing a single region.
#'
#' Only the first row is selected to be returned -- this should be checked!  It
#' is assumed that the zone does not matter.  All other data should be the same
#' (columns 1 to 5, except 2; zone is column 2).  Numeric data (columns 6 to
#' end; hardcoded) are summed.
#'
#' @param df Dataframe on which to operate
#' @param name_replace New district name, with which to replace existing
#' district name
#' @param cols_static
#' @param cols_sum
#' @return Collapsed dataframe with a copy of the first row, and with the
#' district name replaced
collapse_rows <- function(df, name_replace, cols_static, cols_sum) {

  # Get first row only -- remainder should be identical or can be dropped
  df_static <- df[1, cols_static]

  # Replace the district name
  df_static$district <- name_replace

  # Sum over the columns of the target columns to sum
  # TODO: Look into `Map` version of this
  df_summed <- apply(df[, cols_sum], MARGIN = 2, FUN = sum)

  # Bind the static data to the summed data by column
  # NOTE: The `as.data.frame(t(...))` call is because of the odd output format
  # of the `apply` call just above
  cbind(df_static, as.data.frame(t(df_summed)))

}


#' Batch-collapse row data within a region dataframe
#'
#' Input data are expected to be of the form:
#' ```
#' list(
#'   name_replace_1 = district_names_2,
#'   name_replace_2 = district_names_2,
#'   ...
#' )
#' ```
#'
#' @param df Dataframe in which to collapse rows
#' @param spec Specification data for which to collapse rows
#' @param cols_static
#' @param cols_sum
#' @return Dataframe with all specified row-collapse operations applied
#' @examples
#' df_reduced <- reduce_dataframe(
#'   df,
#'   spec = list(
#'     "Addis Ketema (DD)" = c("Goro", "Addis Ketema Operational")
#'   ),
#'   cols_static = seq(1, 5),
#'   cols_sum    = seq(6, ncol(df))
#' )
reduce_df_region <- function(df, spec, cols_static, cols_sum) {

  # Get a vector of districts to remove
  district_rm <- local({

    # Get all unique districts to remove from the overall dataframe
    district <- unique(do.call(c, spec))

    # Convert to a Boolean vector by comparing to the target districts
    unname(unlist(Map(function(x) !any(x == district), df$district)))

  })

  # Run `collapse_district` for all spec pairs, and immediately row-bind
  df_collapse <- local({

    # Run `collapse_district` for all spec pairs
    result <- Map(
      function(x) collapse_district(df, spec[[x]], x, cols_static, cols_sum),
      names(spec)
    )

    # Row-bind the collapsed blocks
    do.call(rbind, result)

  })

  # Remove the target rows from the input dataframe
  df_reduced <- df[district_rm, ]

  # Row-bind the reduced df and the row-bound, collapsed blocks
  output <- rbind(df_reduced, df_collapse)
  row.names(output) <- NULL

  output

}


#' Reduce a dataframe by collapsing districts within regions
#'
#' TODO: Document differing specification for this one
#'
#' @param df
#' @param spec
#' @param cols_static
#' @param cols_sum
#' @return
reduce_df <- function(df, spec, cols_static, cols_sum) {

  # Copy the input dataset to reduce to unmodified regions (for binding)
  df_out <- df[]

  df_out_idx <- unname(unlist(Map(
    function(x) !any(x == names(spec)),
    df_out$region
  )))

  df_out <- df_out[df_out_idx, ]

  # Split across all regions
  df_region <- split(df, df$region)

  # Get the target regions only
  df_target <- Reduce(
    function(a, x) {
      e <- getElement(df_region, x)
      if (is.null(e)) {
        # Warn if element not found
        cat(sprintf("\33[1;33mUnable to find target region:\33[m %s\n", x))
      } else {
        # Add target region data to accumulator by name
        a[[x]] <- e
      }
      a
    },
    names(spec),
    list()
  )

  # Compute the result
  result <- local({
    mid <- Map(
      function(x) reduce_df_region(
        df_target[[x]],
        spec[[x]],
        cols_static,
        cols_sum
      ),
      names(df_target)
    )
    do.call(rbind, mid)
  })

  output <- rbind(df_out, result)
  row.names(output) <- NULL

  output

}

 spec <- list(
      "Beneshangul Gumuz" = list(
      "Kamashi" = c("Kamash town",
                    "Kamashi"),
      "Mandura" = c("Mandura", "Gilgel Beles Town")
      ),
      "Oromiya" = list(
      "Kercha" = c("Kercha",
                    "Kercha Town"),
      "Holota Town" = c("Holota Town","Holota"),
      "Dawe kachen" = c("Dawe Serer",
                    "Dawe kachen"),
       "Ziway Dugda" = c("Batu Town",
                    "Ziway Dugda")
    ),
    "Sidama" = list(
     "Aleta Chuko" = c("Aleta Chuko Town",
                    "Aleta Chuko")
    ),
    "Amhara" = list(
           "Tach Armacho" = c("Tach Armacho",
                    "Central Armacho")
    ),
    "Tigray" = list(
      "Mekelle" = c(
        "Adi Haki", "Ayder",
        "Hadnet","Hawelti",
        "Qedamay Woyane",
        "Quiha","Semen")
      ),
      "Dire Dawa" = list(
      "Legehare Operational" = c("Dire Dawa Operational",
                      "Legehare Operational"),
      "Addis Ketema Operational" = c("Goro Operational", "Addis Ketema Operational")
                              
      ),
        "SNNP" = list(
        "Gerese Zuria" = c("Gerese Zuria","Gerese Town Administration"),
        "Geze Gofa" = c("Bulki Town Administration","Geze Gofa"),
        "Melekoza"= c("Melekoza", "Laha Town Administration"),
         "Uba Debretsehay"= c("Uba Debretsehay","Beto Town Administration") 
      )
    )
    
   
  cols_static <- seq(1, 6)
  cols_sum    <- seq(7, ncol(routine_data_new))

routine_new <- reduce_df(routine_data_new, spec, cols_static, cols_sum)
  
  ## Agalo Meti (renamed to Dambi), Dembi; Belo Jeganfoy (renamed to Mizyga); and Yaso (renamed to Zayi) 
  ## are duplicated in the data set and need to be removed
  ## Also Tula are under Hawasa city admisntration. Assuming the case data may report under the city administration,
  ## To avoid duplicate Tula also removed removed 
 
 routine_new_data <- routine_new |> 
  filter(!district%in%c("Ondulu",
                           "Tula","Asela Town",
                           "Arda Jila Me'e Boko",
                            "Karsasula"))

```

Let's save the clean data in a `.csv` format with the R built fucntion `write.csv()` followed by the file name.

```{r}
write.csv(routine_new_data, file = "task_data.csv", row.names = F)
```

##