# This R environment comes with all of CRAN preinstalled, as well as many other helpful packages
# The environment is defined by the kaggle/rstats docker image: https://github.com/kaggle/docker-rstats
# For example, here's several helpful packages to load in 

library(readr) # CSV file I/O, e.g. the read_csv function
library(dplyr)
library(tidyr)
library(data.table)
library(ggplot2)
library(stringr)
library(reshape)
library(png)
library(gridExtra)


# Input data files are available in the "../input/" directory.
# For example, running this (by clicking run "../input/cliente_tabla.csv"or pressing Shift+Enter) will list the files in the input directory
client_table <- read.csv2("../input/cliente_tabla.csv", sep = ",")
product_table <-  read.csv2("../input/producto_tabla.csv", sep = ",")
town_table <- read.csv2("../input/town_state.csv", sep = ",")
test_data <- fread("../input/test.csv",sep = ",",stringsAsFactors=FALSE)
train_data <- fread("../input/train.csv",sep = ",",stringsAsFactors=FALSE)


## Just to check the data is from which week to which week
train_data %>%
  distinct(Semana);

### to check if there are NA's
any(is.na(test_data))

##summarise By week
Set1 <- train_data %>%
  group_by(Semana) %>%
  summarise(Sales_unit = sum(Venta_uni_hoy),Net_Sales_in_pesos = sum(Venta_hoy-Dev_proxima))
 
##Show Net Sales per Week
  ggplot(Set1, aes(Semana,Net_Sales_in_pesos)) +
  geom_line(stat="identity",size = 1.5,color="blue") +
  scale_x_continuous(breaks=1:10) +
  labs(title ="Net Sales in Pesos/Week", x = "Week", y = "Net Sales") +
  theme_bw()

##Show Net Sales through Canals
Set2 <- train_data %>%
        group_by(Semana,Canal_ID) %>%
        summarise(Net_Sales_in_pesos =sum(Venta_hoy-Dev_proxima)) %>%
        arrange(desc(Net_Sales_in_pesos))

#Unstacked Bars
Set2 %>%
    ##filter((n) <= 250000) %>%
    ggplot() + 
    aes(x = as.factor(Semana),y = Net_Sales_in_pesos, fill = as.factor(Canal_ID)) +
    labs(title ="Net Sales in Pesos/Canal", x = "Week", y = "Net Sales(in Pesos)") +
    geom_bar(stat="identity",position = 'dodge') +
    scale_fill_discrete(name="Canal ID")

# Stacked Bar
Set2 %>%
  ggplot() + 
  aes(as.factor(Semana), Net_Sales_in_pesos, fill = as.factor(Canal_ID)) +
  labs(title ="Net Sales in Pesos/Canal", x = "Week", y = "Net Sales(in Pesos)") +
  geom_bar(stat="identity") +
  scale_fill_discrete(name="Canal ID")


Set3 <- train_data %>%
        group_by(Semana) %>%
        mutate(Difference = Venta_hoy-Dev_proxima) %>%
        summarise_each(funs(sum), Venta_uni_hoy , 
                       Venta_hoy,Dev_uni_proxima, 
                       Dev_proxima,Demanda_uni_equil,
                       Difference)
      
      
#Group by Product and Week 
Set4 <- train_data %>%
        group_by(Semana,Producto_ID) %>%
        mutate(Net_Pesos = Venta_hoy-Dev_proxima) %>%
        summarise_each(funs(sum), Venta_uni_hoy , 
                       Venta_hoy,Dev_uni_proxima, 
                       Dev_proxima,Demanda_uni_equil,
                       Net_Pesos) %>%
      inner_join(product_table, by="Producto_ID") %>%
      ## This gives a rank Week and Productwise
      mutate(prrank = rank(-Venta_hoy)) %>%
      ## Allows to filter top n of each week , remove this and you will find all observations
      filter(prrank <= 5 ) %>%
      arrange(Semana,desc(Venta_hoy))

##List of Top 5 Products in the weeks
Top_Products <- data.frame(matrix((Set4$NombreProducto),nrow=5,ncol=7))
names(Top_Products) <- t(matrix(unique(Set4$Semana),))

print.data.frame(Top_Products)
png("Top_Products.png", height=200, width=1800)
grid.table(Top_Products)
dev.off()

##img <- readPNG('test.png')
##grid::grid.raster(img)


#Group by Product and Week to find Top 100 
Set5 <- train_data %>%
        group_by(Semana,Producto_ID) %>%
        mutate(Net_Pesos = Venta_hoy-Dev_proxima) %>%
        summarise_each(funs(sum), Venta_uni_hoy , 
                       Venta_hoy,Dev_uni_proxima, 
                       Dev_proxima,Demanda_uni_equil,
                       Net_Pesos) %>%
        inner_join(product_table, by="Producto_ID") %>%
        ## This gives a rank Week and Productwise
        mutate(prrank = rank(-Venta_hoy)) %>%
        ## Allows to filter top n of each week , remove this and you will find all observations
        filter(prrank <= 100 ) %>%
        arrange(Semana,desc(Venta_hoy))
      
      #Facet Grid
      ggplot(Set5, aes(NombreProducto, Net_Pesos/(1000000) , colour=Net_Pesos))  +
      labs(title ="Top 100 products/Week ",x="Products", y = "Net Pesos") +
      geom_point(position="jitter",aes(size=Net_Pesos) ) +
      facet_grid(. ~ Semana, labeller = label_both) 
      
##Show Net Sales through top 5 Agencies
Set6 <- train_data %>%
        group_by(Semana,Agencia_ID) %>%
        summarise(Net_Sales_in_pesos =sum(Venta_hoy-Dev_proxima), sum(Dev_proxima), vh = sum(Venta_hoy)) %>% 
        inner_join(town_table, by="Agencia_ID") %>%
        mutate(ag_rank = rank(-vh)) %>%
        filter(ag_rank <= 5 ) %>%
        arrange(Semana,desc(vh))
      
##Show Net Sales through Top 20 Agencies
Set7 <- train_data %>%
        group_by(Semana,Agencia_ID) %>%
        summarise(Net_Sales_in_pesos =sum(Venta_hoy-Dev_proxima), sum(Dev_proxima), vh = sum(Venta_hoy)) %>% 
        inner_join(town_table, by="Agencia_ID") %>%
        mutate(ag_rank = rank(-vh)) %>%
        filter(ag_rank <= 20 ) %>%
        arrange(Semana,desc(vh))
      
      #Top 5 Agencies
      ggplot(Set6 , aes(Semana, Net_Sales_in_pesos)) +
      geom_line(stat="identity",size = 1.5,color="blue") +
      scale_x_continuous(breaks=1:10) +
      facet_grid( . ~ Agencia_ID, labeller = label_both, scales = "free") 
    
      ##display.brewer.all()
      
      #Top 5 Agencies
      ggplot(data=Set6, aes(x=Semana, y=Net_Sales_in_pesos, group = Agencia_ID, colour = as.factor(Agencia_ID))) +
      geom_line() +
      labs(title ="Top 5 Agencies", x = "Week", y = "Net Sales(in Pesos)") +
      scale_x_continuous(breaks=1:10) +
      geom_point(size=4, shape=21, fill="white")
      
      #Top 20 Agencies
      ggplot(data=Set7, aes(x=Semana, y=Net_Sales_in_pesos, group = Agencia_ID, colour = as.factor(Agencia_ID))) +
      geom_line() +
      labs(title ="Top 20 Agencies", x = "Week", y = "Net Sales(in Pesos)") +
      scale_x_continuous(breaks=1:10) +
      geom_point( size=4, shape=21, fill="white")