rm(list=ls())
setwd("C:/Dropbox/Research/_DeLoach/Ag_Data_News/")

#############################################################
## Code to create plots in Ag Data News article            ##
##  Title:                    ##
##  Link:  https://asmith.ucdavis.edu/news/    ##
#############################################################

# If a package is installed, it will be loaded. If any are not, the missing package(s) will be installed 
# from CRAN and then loaded.
# pacman::p_load(tidyverse,RColorBrewer,data.table,dplyr,purrr,lubridate,tmap,tigris,pals,httr,tidyr,stringr,furrr,readxl)
pacman::p_load(purrr, tidyverse, RColorBrewer, data.table, rnassqs, blscrapeR, readxl, httr,tigris,tmap,pals,ggpubr)


##Graph of USA Tobacco and Sweet Potato Production
GET("https://files.asmith.ucdavis.edu/FAOSTAT_us_sp_tb.xls", write_disk(tf1 <- tempfile(fileext = ".xls")))
sp_tb <- read_excel(tf1)

G_sp_tb<-sp_tb %>%
  mutate(value=as.numeric(sp_tb$Value)/1000000) %>%
  ggplot(aes(Year, value)) + 
  scale_x_discrete(breaks = seq(1960, 2020, by = 10)) +
  geom_line(aes(colour = Item, group = Item)) +
  labs(y = "Million of Tons",caption="Source: FAOSTAT \n https://agdatanews.substack.com") +
  theme_minimal() +
  ggtitle(paste0("USA Tobacco and Sweet Potato Production"))+
  theme(legend.position = "right", 
        legend.title = element_blank(),
        plot.title = element_text(hjust = 0.5,size=16),
        text = element_text(size=14))

G_sp_tb
ggsave("sw po & tb_us_production.png",bg="white")

##Graph of NC Tobacco and Sweet Potato Production
GET("https://files.asmith.ucdavis.edu/USDA_NC_sp_tb.xlsx", write_disk(tf2 <- tempfile(fileext = ".xlsx")))
sp_tb_nc <- read_excel(tf2)

G_sp_tb_nc<-sp_tb_nc %>%
  mutate(value=as.numeric(sp_tb_nc$Value)) %>%
  mutate(year=as.numeric(sp_tb_nc$Year)) %>%
  mutate(value=case_when(Commodity=="SWEET POTATOES"~value*100,
                         Commodity=="TOBACCO"~value))%>% #raw data has different scale of units
  mutate(value=value/1000000000)%>%
  ggplot(aes(year, value)) + 
  geom_line(aes(colour = Commodity, group = Commodity)) +
  labs(y = "Billion of Pounds",caption="Source: Ag Census; USDA NASS \n https://agdatanews.substack.com") +
  theme_minimal()+
  ggtitle(paste0("NC Tobacco and Sweet Potato Production"))+
  theme(legend.position = "right", 
        legend.title = element_blank(),
        plot.title = element_text(hjust = 0.5,size=16),
        text = element_text(size=14))

G_sp_tb_nc
ggsave("sw po & tb_nc_production.png",bg="white")



##Graph of World Sweet Potato Production
wr_sp<-read_csv("https://files.asmith.ucdavis.edu/FAOSTAT_wr_sp.csv")

countries <- c("China","Nigeria","Tanzania","Ethiopia","Indonesia","USA","Others")
country_order <- mutate(as.data.frame(countries),order=row_number()) %>%
  rename(name=countries) %>%
  mutate(order=ifelse(name=="China",1,order)) %>%
  mutate(order=ifelse(name=="Nigeria",2,order)) %>%
  mutate(order=ifelse(name=="Tanzania",3,order)) %>%
  mutate(order=ifelse(name=="Ethiopia",4,order)) %>%
  mutate(order=ifelse(name=="Indonesia",5,order)) %>%
  mutate(order=ifelse(name=="USA",6,order)) %>%
  mutate(order=ifelse(name=="Others",7,order))

G_wr_sp <- wr_sp %>%
  filter(Area %in% c("World","China, mainland","United States of America", "United Republic of Tanzania","Nigeria","Ethiopia","Indonesia")) %>%
  filter(Element=="Production") %>%
  select(Year,Area,Value) %>%
  mutate(Value=replace_na(Value,0)) %>%
  pivot_wider(names_from = Area, values_from=Value, values_fill = 0) %>%
  rename(USA=`United States of America`) %>%
  rename(China=`China, mainland`) %>%
  rename(Tanzania=`United Republic of Tanzania`) %>%
  mutate(Other=World-China-USA-Nigeria-Ethiopia-Indonesia-Tanzania) %>%
  select(-World) %>%
  pivot_longer(-Year) %>%
  left_join(country_order)%>%
  mutate(value=value/1000000) %>%
  ggplot(aes(x=Year,y=value,fill=reorder(name,order))) +
  geom_area()+
  labs(fill="Country", x = "Year", y = "Millions of Tons",caption="Source: FAOSTAT \n https://agdatanews.substack.com") +
  theme_minimal()+
  ggtitle(paste0("World Sweet Potato Production"))+
  theme(legend.position = "right", 
        legend.title = element_blank(),
        plot.title = element_text(hjust = 0.5,size=16),
        text = element_text(size=14))+
  scale_fill_brewer(palette="Set3")   

G_wr_sp
ggsave("world_sp_production.png",bg="white")  


##Graph of World Sweet Potato Export Value
wr_sp_ex<-read_csv("https://files.asmith.ucdavis.edu/FAOSTAT_sp_ex.csv")

G_wr_sp_ex <- wr_sp_ex %>%
  filter(Area %in% c("World","China, mainland","United States of America", "United Republic of Tanzania","Nigeria","Ethiopia","Indonesia")) %>%
  filter(Element=="Export Value") %>%
  select(Year,Area,Value) %>%
  mutate(Value=replace_na(Value,0)) %>%
  pivot_wider(names_from = Area, values_from=Value, values_fill = 0) %>%
  rename(USA=`United States of America`) %>%
  rename(China=`China, mainland`) %>%
  rename(Tanzania=`United Republic of Tanzania`) %>%
  mutate(Other=World-China-USA-Nigeria-Ethiopia-Indonesia-Tanzania) %>%
  select(-World) %>%
  pivot_longer(-Year) %>%
  left_join(country_order)%>%
  mutate(value=value/1000) %>%
  ggplot(aes(x=Year,y=value,fill=reorder(name,order))) +
  geom_area()+
  labs(fill="Country", x = "Year", y = "Millions Dollars",caption="Source: FAOSTAT \n https://agdatanews.substack.com") +
  theme_minimal()+
  ggtitle(paste0("World Sweet Potato Export Value"))+
  theme(legend.position = "right", 
        legend.title = element_blank(),
        plot.title = element_text(hjust = 0.5,size=16),
        text = element_text(size=14))+
  scale_fill_brewer(palette="Set3")   

G_wr_sp_ex
ggsave("world_sp_export.png",bg="white")  

##Graph of US Sweet Potato Production By State
GET("https://files.asmith.ucdavis.edu/USDA_us_sp_state.xlsx", write_disk(tf3 <- tempfile(fileext = ".xlsx")))
sp_state<-read_xlsx(tf3)

sp_state<- sp_state%>%
  select(Year,State,Value) %>%
  transform(ID = as.numeric(factor(Year))) %>%
  reshape(
  direction = "wide",
  idvar     = "Year",     
  timevar   = "State",  
  v.names   = "Value"
  )
sp_state[is.na(sp_state)] = 0

G_sp_state<-sp_state %>%
  rename(CA='Value.CALIFORNIA', MI = 'Value.MISSISSIPPI', 
         Other = 'Value.OTHER STATES',NC='Value.NORTH CAROLINA', 
         AR='Value.ARKANSAS',FL='Value.FLORIDA',LA='Value.LOUISIANA',
         AL='Value.ALABAMA',NJ='Value.NEW JERSEY',TX='Value.TEXAS')%>%
  select(Year, CA:TX) %>%
  gather(state, value, CA:TX, factor_key=TRUE)%>%
  filter(!is.na(value))%>%
  mutate(value = as.numeric(gsub(",", "", value))) %>%
  ggplot(aes(x=Year,y=value*100/1000000000,fill=factor(state,levels=c("CA","MI","NC","AR","FL","LA","AL","NJ","TX","Other")))) +
  geom_bar(stat="identity",position="stack")+
  theme_minimal()+
  ggtitle("US Sweet Potato Production By State") +
  scale_fill_brewer(palette = "Set3") +
  labs(x="Year",y="Billion Pounds",fill="State",caption="Source: USDA NASS \n https://agdatanews.substack.com")+
  theme(plot.title = element_text(hjust = 0.5,size=16), text = element_text(size=14))

G_sp_state
ggsave("sp_states_production.png",bg="white")  



##Graph of NC sweet potato exports by destination
GET("https://files.asmith.ucdavis.edu/USA_Trade_Online_NC_sp_exp.xlsx", write_disk(tf4 <- tempfile(fileext = ".xlsx")))
nc_sp_exp<-read_excel(tf4)

G_nc_sp_exp<-nc_sp_exp %>%
  rename(Region='Country',Year='Time',Value='Total Value ($US)') %>% 
  ggplot(aes(x=Year,y=Value/1000000,fill=factor(Region))) +
  geom_bar(stat="identity",position="stack")+
  theme_minimal()+
  ggtitle("NC sweet potato exports by destination") +
  scale_fill_brewer(palette = "Set3") +
  labs(x="Year",y="Million Dollars",fill="Region")+
  theme(plot.title = element_text(hjust = 0.5,size=16), text = element_text(size=14))

G_nc_sp_exp
ggsave("nc_sp_exp.png",bg="white")