Data Wrangling.Rmd

---
title: "Data Loading and wrangling"
output:
  pdf_document: default
urlcolor: blue
---

```{r}
library(rvest)
library(tidyverse)
library(readxl)
library(janitor)
library(stringr)
library(lubridate)
library(glue)
library(here)
```


Data scraping wikipedia

```{r}
link <- read_html("https://en.wikipedia.org/wiki/List_of_dams_and_reservoirs_in_Maharashtra")
daminfo <- html_node(link, 'table') %>%
  html_table(fill = TRUE)
```


loading 2015 data

```{r}
dam2015 <- read_excel("Data/2015.xlsx") %>%
  clean_names() %>%
  pivot_longer(cols = c(-reservoir_name, -district, -basin),
               names_to = "month") %>%
  mutate(day = 1) %>%
  separate(month, into = c("month", "year"), sep = "_") %>%
  mutate(month = case_when(
    month == "jan" ~ 1,
    month == "feb" ~ 2,
    month == "mar" ~ 3,
    month == "apr" ~ 4,
    month == "may" ~ 5,
    month == "jun" ~ 6,
    month == "jul" ~ 7,
    month == "aug" ~ 8,
    month == "sep" ~ 9,
    month == "oct" ~ 10,
    month == "nov" ~ 11,
    month == "dec" ~ 12),
    date = make_date(year, month, day),
    storage_bcm = as.numeric(value)) %>%
  select(reservoir_name, district, basin, date, value, month, day, year)
  

daminfo <- daminfo %>%
  clean_names() %>%
  mutate(name_of_dam = case_when(
    name_of_dam == "Koyna" ~ "Koyana/Shivaji Sagar",
    name_of_dam == "Hatnur" ~ "Upper TapiHatnur Reservoir",
    name_of_dam == "Isapur" ~ "Isapur Reservoir",
    name_of_dam == "Upper Vaitarana" ~ "Upper Vaitarana Reservoir",
    name_of_dam == "Khadakwasla" ~ "Khadakwasla Reservoir",
    name_of_dam == "Yeldari" ~ "Yeldari Reservoir",
    name_of_dam == "Mula" ~ "Mula Reservoir",
    name_of_dam == "Jayakwadi" ~ "JayakwadiNath Sagar",
    name_of_dam == "Girna" ~ "Girna Reservoir",
    name_of_dam == "Mulshi" ~ "Mulshi Dam",
    name_of_dam == "Kanher" ~ "Kanher Dam",
    name_of_dam == "Ujani" ~ "BhimaUjjani Reservoir",
    TRUE ~ name_of_dam
  ) )

dam2015fin <- left_join(dam2015, daminfo, by = c ("reservoir_name" = "name_of_dam")) %>%
  mutate(gross_storage_capacity_103m3 = as.numeric(gross_storage_capacity_103m3),
         effective_storage_capacity_103m3 = as.numeric(effective_storage_capacity_103m3),
         value = as.numeric(value)) %>%
  select(-na)


#make factors for graph
dam2015fin2 <- dam2015fin %>%
  drop_na() %>%
  mutate(purpose = str_replace(purpose, "  ", " & "),
         purpose = fct_relevel(purpose, c("Hydroelectricity",
                                          "Irrigation",
                                          "Irrigation & Hydroelectricity",
                                          "Irrigation & Water supply")))
```


2015 wide format

```{r}
wide2015 <- dam2015fin %>%
  select(-month, -day, -year) %>%
  pivot_wider(names_from = date, values_from = value)
```


```{r}
cruncher<- function(data){
  read_excel(data, col_types = c("text", 
    "text", "text", "text", "text", 
    "text", "text", "text", "text", 
    "text", "text", "text", "text", 
    "text", "text")) %>%
  clean_names() %>%
  pivot_longer(cols = c(-reservoir_name, -district, -basin),
               names_to = "month") %>%
  mutate(day = 1) %>%
  separate(month, into = c("month", "year"), sep = "_") %>%
  mutate(month = case_when(
    month == "jan" ~ 1,
    month == "feb" ~ 2,
    month == "mar" ~ 3,
    month == "apr" ~ 4,
    month == "may" ~ 5,
    month == "jun" ~ 6,
    month == "jul" ~ 7,
    month == "aug" ~ 8,
    month == "sep" ~ 9,
    month == "oct" ~ 10,
    month == "nov" ~ 11,
    month == "dec" ~ 12),
    date = make_date(year, month, day),
    storage_bcm = as.numeric(value)) %>%
  select(reservoir_name, district, basin, date, storage_bcm, month, day, year)
}

year <- 2015:2020 
dataglue <- glue("Data/{year}.xlsx")
dataname <- glue("dam{year}")


dam2015 <- cruncher("Data/2015.xlsx")
dam2016 <- cruncher("Data/2016.xlsx")
dam2017 <- cruncher("Data/2017.xlsx")
dam2018 <- cruncher("Data/2018.xlsx")
dam2019 <- cruncher("Data/2019.xlsx")
dam2020 <- cruncher("Data/2020.xlsx")


# for(i in 1:length(dataglue)){
#   dataname[i] <- cruncher(dataglue[i])
# }

bigdata <- bind_rows(dam2015, dam2016, dam2017, dam2018, dam2019, dam2020)

biggerdata <- inner_join(bigdata, daminfo, by = c ("reservoir_name" = "name_of_dam")) %>%
  mutate(gross_storage_capacity_109m3 = as.numeric(gross_storage_capacity_103m3)/1000000,
         effective_storage_capacity_109m3 = as.numeric(effective_storage_capacity_103m3)/1000000) %>%
  mutate(purpose = str_replace(purpose, "  ", " & "))

```


```{r}
dam_locations <- read_excel("Data/dam_locations.xlsx", 
    col_names = FALSE) %>%
  rename(dam = ...1,
         lat = ...2,
         long = ...3)

biggerdata <- left_join(biggerdata, dam_locations,
                        by = c("reservoir_name" = "dam"))

sum(is.na(biggerdata$lat))
sum(is.na(biggerdata$long))

biggerdata <- biggerdata %>%
  mutate(district = str_to_title(district),
         drought = case_when(
           long > 75 & long < 77 ~ "High drought risk",
           TRUE ~ "Low drought risk"
         )) %>%
  unite(col = distlabel, c(district, drought), sep = " - ", remove = FALSE)

dam_spat <- biggerdata %>%
  select(-c(date, month, day, year,  x)) %>%
  distinct(reservoir_name, .keep_all = TRUE) %>%
  mutate(gross_storage_capacity_103m3 = as.numeric(gross_storage_capacity_103m3),
         effective_storage_capacity_103m3 = as.numeric(effective_storage_capacity_103m3),
         district = str_to_title(district))

write_csv(biggerdata, here("damdata.csv"))
write_csv(dam_spat, here("damspats.csv"))
```


playing around with the plot

```{r}

biggerdata %>%
#  filter(purpose == "Irrigation & Hydroelectricity") %>%
  filter(year %in% c(2015, 2016)) %>%
  mutate(drought = fct_relevel(drought, c("Low drought risk", "High drought risk"))) %>%
  ggplot(aes(x = date, y = storage_bcm, color = purpose,
             group = reservoir_name, shape = drought)) +
      geom_point(alpha = 0.6, aes(size = effective_storage_capacity_109m3))+
      geom_line(color = "black", alpha = 0.5)+
      theme_minimal() +
      labs(y = bquote("Water storage"~(10^9~m^3)), color = "Use",
           shape = "Drought risk",
           size = bquote("Effective storage capacity"~(10^9~m^3))) +
      theme(axis.title.x = element_blank()) +
      guides(color = guide_legend(order = 1),
             size = guide_legend(order = 2)) +
      scale_color_manual(breaks = c("Hydroelectricity",
                                    "Irrigation",
                                    "Irrigation & Hydroelectricity",
                                    "Irrigation & Water supply"),
                         values = c("#66C2A5", "#FC8D62", "#8DA0CB", "#E78AC3"))
```