This is a drilldown plot of trade export:
* 1st Level : Top 10 commodity India exported with respect to total value.(2010-2018)
* 2nd Level : For each 10 commodity their export values ploted per year
This is a drilldown plot of trade export: * 1st Level : Top 10 commodity India imported with respect to total value.(2010-2018) * 2nd Level : For each 10 commodity their import values ploted per year
This is a drilldown plot of trade export: * 1st Level : Top 10 countries India exported to with respect to total value.(2010-2018) * 2nd Level : For each 10 countries their export values ploted per year
This is a drilldown plot of trade import: * 1st Level : Top 10 countries India imported from with respect to total value.(2010-2018) * 2nd Level : For each 10 countries their import values ploted per year
This is a drilldown plot of trade by time:
* 1st Level : Import export vs in billion usd(2010-2018)
* 2nd Level : For that year Import or export division by country
This is a drilldown plot of trade by time:
* 1st Level : Import export vs in billion usd(2010-2018)
* 2nd Level : For that year Import or export division by commodity
---
title: "India - Trade Data Dashboard"
# author: "Anik Mallick"
# date: "Sep 15, 2019"
output:
flexdashboard::flex_dashboard:
orientation: columns
source_code: embed
vertical_layout: scroll
theme: yeti
---
``` {js}
// Inverse color of navigation bar.
$('.navbar-inverse').removeClass('navbar-inverse').addClass('navbar-default');
```
```{r setup, include=FALSE}
# libraries
library(dplyr)
library(tidyr)
library(ggplot2)
library(plotly)
library(flexdashboard)
library(DT)
library(highcharter)
```
```{r}
#utility function
create_commodity_hscode_name1 <- function(cm,h){
#cm <- gsub(pattern = ',',replacement = ';',x = cm)
com <-substr(cm,start = 1,stop = 15)
# com <- com[1]
hsc <- paste("HSCode",as.character(h),sep=': ')
paste(com,hsc,sep=' - ')
}
create_commodity_hscode_name2 <- function(cmv,hv){
rtv <- character()
for(i in 1:length(cmv)){
rtv[i] <- create_commodity_hscode_name1(cmv[i],hv[i])
}
rtv
}
```
```{r}
# reading the data
df_export <- read.csv("2018-2010_export.csv", stringsAsFactors = F) %>% mutate(country = trimws(country))
df_import <- read.csv("2018-2010_import.csv", stringsAsFactors = F) %>% mutate(country = trimws(country))
#HSCode with Comodity
df_hsc <- df_import[,c(1,2)] %>% distinct()
df_hsc <- df_hsc %>% mutate(Commodity_HSCode = create_commodity_hscode_name2(Commodity,HSCode))
# sebsetting the blank columns
df_export_na <- df_export %>% filter(is.na(value))
df_import_na <- df_import %>% filter(is.na(value))
df_export <- df_export %>% filter(!is.na(value))
df_import <- df_import %>% filter(!is.na(value))
```
Overview
=======================================================================
Column {.tabset .tabset-fade data-width=700 .colored }
-----------------------------------------------------------------------
### Import and Export per year
```{r}
df_1_1 <- df_export %>% group_by(year) %>% summarise(export = sum(value))
df_1_2 <- df_import %>% group_by(year) %>% summarise(import = sum(value))
df_1 <- merge(df_1_1,df_1_2,x.by = year, y.by = year) %>% gather('Type','Value',-year)
df_1 <- df_1 %>% mutate(Value_in_billion = Value/1000)
g1 <- ggplot(df_1)+
geom_segment(aes(x = 2017, y = 384350.3/1000, xend= 2017,yend = 931148.0/1000), arrow = arrow(length = unit(0.1, "inches")))+
geom_segment(aes(x = 2016, y = 384350.3/1000, xend= 2017,yend = 384350.3/1000), linetype =2)+
geom_point(aes(x=year,y=Value_in_billion,color=Type),size = 4, alpha = 0.8)+
geom_line(aes(x=year,y=Value_in_billion,color=Type))+
annotate('text',x = 2014,y = 650,
label = "From 2016 to 2017\nimport increased by\n546.7 billion")+
labs(x = "", y="Import/Export in billions")+
theme(legend.title = element_blank(),
panel.border = element_rect(colour = "black", fill=NA, size=1))
ggplotly(g1) %>% layout(legend = list(x = 0, y = 1,
bordercolor="Black",
borderwidth=1))
```
### Top 5 Exports of India
```{r}
df_2_1 <- df_export %>% group_by(HSCode) %>% summarise(value = sum(value)) %>% top_n( 5,value)
#df_2_1 <- merge(df_2_1,df_hsc,by='HSCode')
df_2 <- df_export %>% filter(HSCode %in% df_2_1$HSCode) %>%
group_by(year,HSCode) %>%
summarise(value_n_billion = sum(value)/1000)
df_2 <- merge(df_2,df_hsc,by='HSCode')
g2 <- ggplot(df_2)+
geom_point(aes(x=year,y=value_n_billion,color=Commodity_HSCode),size = 1, alpha = 0.8)+
geom_line(aes(x=year,y=value_n_billion,color=Commodity_HSCode))+
labs(x = "", y="Export in billions")+
theme(panel.border = element_rect(colour = "black", fill=NA, size=1))
ggplotly(g2) %>% hide_legend()
```
### Top 5 Imports of India
```{r}
df_3_1 <- df_import %>% group_by(HSCode) %>% summarise(value = sum(value)) %>% top_n( 5,value)
df_3 <- df_import %>% filter(HSCode %in% df_2_1$HSCode) %>%
group_by(year,HSCode) %>%
summarise(value_n_billion = sum(value)/1000)
df_3 <- merge(df_3,df_hsc,by='HSCode')
g3 <- ggplot(df_3)+
geom_point(aes(x=year,y=value_n_billion,color=Commodity_HSCode),size = 1, alpha = 0.8)+
geom_line(aes(x=year,y=value_n_billion,color=Commodity_HSCode))+
labs(x = "", y="Import in billions")+
theme(panel.border = element_rect(colour = "black", fill=NA, size=1))
ggplotly(g3) %>% hide_legend()
```
Column { data-width=300}
-----------------------------------------------------------------------
### Export growth per year in billions (2010-2018)
```{r}
df_4 <- df_1 %>% filter(Type == 'export')
value_4 <- (df_4$Value_in_billion[9] - df_4$Value_in_billion[1])/9
valueBox(round(value_4,2), icon = 'fa-sign-out-alt')
```
### Total Export in billions (2010-2018)
```{r}
value_6 <- sum(df_4$Value_in_billion)
valueBox(round(value_6,2), icon = 'fa-sign-out-alt')
```
### Import growth per year in billions (2010-2018)
```{r}
df_5 <- df_1 %>% filter(Type == 'import')
value_5 <- (df_5$Value_in_billion[9] - df_5$Value_in_billion[1])/9
valueBox(round(value_5,2), icon = 'fa-sign-in-alt', color = 'white')
```
### Total Import in billions (2010-2018)
```{r}
value_7 <- sum(df_5$Value_in_billion)
valueBox(round(value_7,2), icon = 'fa-sign-in-alt', color = 'white')
```
Commodities
=======================================================================
Column {.tabset .tabset-fade data-width=750 .colored }
-----------------------------------------------------------------------
### Export
```{r}
df_drill_1 <- df_export %>% group_by(HSCode) %>%
summarise(value_in_billions=sum(value)/1000) %>% top_n(n = 10,wt = value_in_billions) %>%
merge(df_hsc,by = 'HSCode') %>%
rename(name = Commodity_HSCode, y = value_in_billions) %>% mutate(drilldown = tolower(name))%>%
select(name, y, drilldown) %>% arrange(desc(y))
codes <- df_drill_1$name
df_drill_year = list()
countries_id <- tolower(codes)
df_drill_2 <- df_export %>% merge(df_hsc,by = 'HSCode') %>% filter(Commodity_HSCode %in% codes) %>%
group_by(Commodity_HSCode ,year) %>%
summarise(value = sum(value)/1000) %>% rename(name = year)
for(i in 1:length(codes)){
cd <- codes[i]
df_drill_year[[i]] <- list(
id = tolower(cd),
name = cd,
data = df_drill_2 %>% filter(Commodity_HSCode == cd) %>% ungroup() %>% select(name,value) %>% list_parse2()
)
}
highchart() %>%
hc_title(text = 'DrillDown plot of export by commodity',
style = list(fontSize = "15px")) %>%
hc_add_series(df_drill_1, type = "column",
hcaes(x = factor(name), y = y),showInLegend = FALSE) %>%
hc_xAxis(categories = df_drill_1$name) %>%
hc_yAxis(title = list(text = 'Export in Billions')) %>%
hc_drilldown(allowPointDrilldown = TRUE,series = df_drill_year)
```
> This is a drilldown plot of trade export:
* *1st Level* : Top 10 commodity India exported with respect to total value.(2010-2018)
* *2nd Level* : For each 10 commodity their export values ploted per year
### Import
```{r}
df_drill_1 <- df_import %>% group_by(HSCode) %>%
summarise(value_in_billions=sum(value)/1000) %>% top_n(n = 10,wt = value_in_billions) %>%
merge(df_hsc,by = 'HSCode') %>%
rename(name = Commodity_HSCode, y = value_in_billions) %>% mutate(drilldown = tolower(name))%>%
select(name, y, drilldown) %>% arrange(desc(y))
codes <- df_drill_1$name
df_drill_year = list()
countries_id <- tolower(codes)
df_drill_2 <- df_import %>% merge(df_hsc,by = 'HSCode') %>% filter(Commodity_HSCode %in% codes) %>%
group_by(Commodity_HSCode ,year) %>%
summarise(value = sum(value)/1000) %>% rename(name = year)
for(i in 1:length(codes)){
cd <- codes[i]
df_drill_year[[i]] <- list(
id = tolower(cd),
name = cd,
data = df_drill_2 %>% filter(Commodity_HSCode == cd) %>% ungroup() %>% select(name,value) %>% list_parse2()
)
}
highchart() %>%
hc_title(text = 'DrillDown plot of import by commodity',
style = list(fontSize = "15px")) %>%
hc_add_series(df_drill_1, type = "column",
hcaes(x = factor(name), y = y),showInLegend = FALSE) %>%
hc_xAxis(categories = df_drill_1$name) %>%
hc_yAxis(title = list(text = 'Export in Billions')) %>%
hc_drilldown(allowPointDrilldown = TRUE,series = df_drill_year)
#
# cols <- union((df_export %>% group_by(HSCode) %>% summarise(value = sum(value)/1000) %>% top_n(n = 5,wt = value))$HSCode,
# (df_import %>% group_by(HSCode) %>% summarise(value = sum(value)/1000) %>% top_n(n = 5,wt = value))$HSCode)
# temp1 <- df_export %>% filter(HSCode %in% cols) %>% group_by(HSCode,year) %>% summarise(value=sum(value)/1000) %>% mutate(type = 'export')
# temp2 <- df_import %>% filter(HSCode %in% cols) %>% group_by(HSCode,year) %>% summarise(value=sum(value)/1000) %>% mutate(type = 'import')
# temp <- rbind(temp1,temp2) %>% spread(key = type,value = value) %>% mutate(profit_loss=(export - import))
#
#
# plot_list <- list()
# cn <- 1
# for(cl in cols){
# df <- temp %>% filter(HSCode == cl)
#
# for(i in 2:9) df$profit_loss[i] <- df$profit_loss[i]-df$profit_loss[i-1]
#
# df$profit_loss <- round(df$profit_loss,2)
#
# plot_list[[cn]] <- plot_ly(df, name = "20", type = "waterfall",
# x = ~year, textposition = "outside", y= ~profit_loss, text =~as.character(profit_loss),
# connector = list(line = list(color= "rgb(63, 63, 63)"))) %>%
# layout(title = "Profit and loss for top Commodities ",
# xaxis = list(title = ""),
# yaxis = list(title = ""),
# autosize = TRUE,
# showlegend = F) %>%
# add_annotations(
# text = paste0('HSCode:',as.character(cl)),
# x=2010,
# y=sum(df$profit_loss)/2,
# showarrow=F
# )
# cn <- cn +1
# }
# subplot(plot_list[[1]], plot_list[[2]], plot_list[[3]],plot_list[[4]],plot_list[[5]],plot_list[[6]], nrows = 3,margin = 0.05)
```
> This is a drilldown plot of trade export:
* *1st Level* : Top 10 commodity India imported with respect to total value.(2010-2018)
* *2nd Level* : For each 10 commodity their import values ploted per year
Countries
=======================================================================
Column {.tabset .tabset-fade}
-----------------------------------------------------------------------
### Export
```{r}
df_drill_1 <- df_export %>% group_by(country) %>%
summarise(value_in_billions=sum(value)/1000) %>% top_n(n = 10,wt = value_in_billions) %>% mutate(drilldown = tolower(country)) %>%
rename(name = country, y = value_in_billions) %>% select(name, y, drilldown) %>% arrange(desc(y))
countries <- df_drill_1$name
df_drill_year = list()
countries_id <- tolower(countries)
df_drill_2 <- df_export %>% filter(country %in% countries) %>% group_by(country,year) %>%
summarise(value = sum(value)/1000) %>% rename(name = year)
for(i in 1:length(countries)){
cntr <- countries[i]
df_drill_year[[i]] <- list(
id = tolower(cntr),
name = cntr,
data = df_drill_2 %>% filter(country == cntr) %>% ungroup() %>% select(name,value) %>% list_parse2()
)
}
##### not ready
#
# df_drill_1_i <- df_import %>% group_by(country) %>%
# summarise(value_in_billions=sum(value)/1000) %>%
# top_n(n = 10,wt = value_in_billions) %>% mutate(drilldown = paste(tolower(country),'_i',sep='')) %>%
# rename(name = country, y = value_in_billions) %>% select(name, y, drilldown) %>% arrange(desc(y))
#
# countries_i <- df_drill_1$name
#
# df_drill_2_i <- df_import %>% filter(country %in% countries) %>% group_by(country,year) %>%
# summarise(value = sum(value)/1000) %>% rename(name = year)
#
# for(i in 1:length(countries_i)){
# cntr <- countries_i[i]
# i <- length(countries_e)+i
# df_drill_year[[i]] <- list(
# id = paste(tolower(cntr),'_i',sep=''),
# name = paste(tolower(cntr),'_i',sep=''),
# data = df_drill_2_i %>% filter(country == cntr) %>% ungroup() %>% select(name,value) %>% list_parse2()
# )
# }
#
#
#
# highchart() %>%
# hc_title(text = 'DrillDown plot of export by country',
# style = list(fontSize = "15px")) %>%
# hc_add_series(df_drill_1_e, type = "column",name = 'Export',
# hcaes(x = name, y = y),showInLegend = T) %>%
# hc_add_series(df_drill_1_i, type = "column",name = 'Import',
# hcaes(x = name, y = y),showInLegend = T) %>%
# hc_xAxis(categories = c(df_drill_1_e$name,df_drill_1_i$name)) %>%
# hc_yAxis(title = list(text = 'Export in Billions')) %>%
# hc_drilldown(allowPointDrilldown = TRUE,series = df_drill_year)
########################
highchart() %>%
hc_title(text = 'DrillDown plot of export by country',
style = list(fontSize = "15px")) %>%
hc_add_series(df_drill_1, type = "column",
hcaes(x = name, y = y),showInLegend = FALSE) %>%
hc_xAxis(categories = df_drill_1$name) %>%
hc_yAxis(title = list(text = 'Export in Billions')) %>%
hc_drilldown(allowPointDrilldown = TRUE,series = df_drill_year)
```
> This is a drilldown plot of trade export:
* *1st Level* : Top 10 countries India exported to with respect to total value.(2010-2018)
* *2nd Level* : For each 10 countries their export values ploted per year
### Import
```{r}
df_drill_1 <- df_import %>% group_by(country) %>%
summarise(value_in_billions=sum(value)/1000) %>% top_n(n = 10,wt = value_in_billions) %>% mutate(drilldown = tolower(country)) %>%
rename(name = country, y = value_in_billions) %>% select(name, y, drilldown) %>% arrange(desc(y))
countries <- df_drill_1$name
df_drill_year = list()
countries_id <- tolower(countries)
df_drill_2 <- df_import %>% filter(country %in% countries) %>% group_by(country,year) %>%
summarise(value = sum(value)/1000) %>% rename(name = year)
for(i in 1:length(countries)){
cntr <- countries[i]
df_drill_year[[i]] <- list(
id = tolower(cntr),
name = cntr,
data = df_drill_2 %>% filter(country == cntr) %>% ungroup() %>% select(name,value) %>% list_parse2()
)
}
highchart() %>%
hc_title(text = 'DrillDown plot of import by country',
style = list(fontSize = "15px")) %>%
hc_add_series(df_drill_1, type = "column",
hcaes(x = name, y = y),showInLegend = FALSE) %>%
hc_xAxis(categories = df_drill_1$name) %>%
hc_yAxis(title = list(text = 'Export in Billions')) %>%
hc_drilldown(allowPointDrilldown = TRUE,series = df_drill_year)
```
> This is a drilldown plot of trade import:
* *1st Level* : Top 10 countries India imported from with respect to total value.(2010-2018)
* *2nd Level* : For each 10 countries their import values ploted per year
Time
=======================================================================
Column {.tabset .tabset-fade}
-----------------------------------------------------------------------
### Country
```{r}
df_drill_1_i <- df_import %>% group_by(year) %>%
summarise(value_in_billions=sum(value)/1000) %>% mutate(drilldown = paste(as.character(year),'_i',sep='')) %>%
rename(name = year, y = value_in_billions) %>% select(name, y, drilldown)
years_i <- df_drill_1_i$name
df_drill_year = list()
df_drill_2_i <- df_import %>% group_by(year,country) %>%
summarise(value = sum(value)/1000) %>% rename(name = country)
for(i in 1:length(years_i)){
yr <- years_i[i]
df_drill_year[[i]] <- list(
id = paste(as.character(yr),'_i',sep=''),
name = paste(as.character(yr),'_i',sep=''),
data = df_drill_2_i %>% filter(year == yr) %>% ungroup() %>% select(name,value) %>% arrange(value)%>% list_parse2(),
type = 'pie'
)
}
df_drill_1_e <- df_export %>% group_by(year) %>%
summarise(value_in_billions=sum(value)/1000) %>% mutate(drilldown = paste(as.character(year),'_e',sep='')) %>%
rename(name = year, y = value_in_billions) %>% select(name, y, drilldown)
years_e <- df_drill_1_e$name
df_drill_2_e <- df_export %>% group_by(year,country) %>%
summarise(value = sum(value)/1000) %>% rename(name = country)
for(i in 1:length(years_e)){
yr <- years_e[i]
i <- length(years_i)+i
df_drill_year[[i]] <- list(
id = paste(as.character(yr),'_e',sep=''),
name = paste(as.character(yr),'_e',sep=''),
data = df_drill_2_e %>% filter(year == yr) %>% ungroup() %>% select(name,value) %>% arrange(value)%>% list_parse2(),
type = 'pie'
)
}
highchart() %>%
hc_title(text = 'DrillDown plot by time',
style = list(fontSize = "15px")) %>%
hc_add_series(df_drill_1_i,name = "Import",type = 'spline', hcaes(x = name, y = y),showInLegend = T) %>%
hc_yAxis(title = list(text = 'Import/Export in Billions')) %>%
hc_add_series(df_drill_1_e,name = "Export",type = 'spline', hcaes(x = name, y = y),showInLegend = T) %>%
hc_drilldown(allowPointDrilldown = TRUE,series = df_drill_year)
```
> This is a drilldown plot of trade by time:
* *1st Level* : Import export vs in billion usd(2010-2018)
* *2nd Level* : For that year Import or export division by country
### Commodity
```{r}
#df_drill_1_i <- df_import %>% group_by(year) %>%
# summarise(value_in_billions=sum(value)/1000) %>% mutate(drilldown = paste(as.character(year),'_i',sep='')) %>%
# rename(name = year, y = value_in_billions) %>% select(name, y, drilldown)
years_i <- df_drill_1_i$name
df_drill_year = list()
df_drill_2_i <- df_import %>% group_by(year,Commodity) %>%
summarise(value = sum(value)/1000) %>% rename(name = Commodity)
for(i in 1:length(years_i)){
yr <- years_i[i]
df_drill_year[[i]] <- list(
id = paste(as.character(yr),'_i',sep=''),
name = paste(as.character(yr),'_i',sep=''),
data = df_drill_2_i %>% filter(year == yr) %>% ungroup() %>% select(name,value) %>% arrange(value)%>% list_parse2(),
type = 'pie'
)
}
#df_drill_1_e <- df_export %>% group_by(year) %>%
# summarise(value_in_billions=sum(value)/1000) %>% mutate(drilldown = paste(as.character(year),'_e',sep='')) %>%
# rename(name = year, y = value_in_billions) %>% select(name, y, drilldown)
years_e <- df_drill_1_e$name
df_drill_2_e <- df_export %>% group_by(year,Commodity) %>%
summarise(value = sum(value)/1000) %>% rename(name = Commodity)
for(i in 1:length(years_e)){
yr <- years_e[i]
i <- length(years_i)+i
df_drill_year[[i]] <- list(
id = paste(as.character(yr),'_e',sep=''),
name = paste(as.character(yr),'_e',sep=''),
data = df_drill_2_e %>% filter(year == yr) %>% ungroup() %>% select(name,value) %>% arrange(value)%>% list_parse2(),
type = 'pie'
)
}
highchart() %>%
hc_title(text = 'DrillDown plot by time',
style = list(fontSize = "15px")) %>%
hc_add_series(df_drill_1_i,name = "Import",type = 'spline', hcaes(x = name, y = y),showInLegend = T) %>%
hc_yAxis(title = list(text = 'Import/Export in Billions')) %>%
hc_add_series(df_drill_1_e,name = "Export",type = 'spline', hcaes(x = name, y = y),showInLegend = T) %>%
hc_drilldown(allowPointDrilldown = TRUE,series = df_drill_year)
```
> This is a drilldown plot of trade by time:
* *1st Level* : Import export vs in billion usd(2010-2018)
* *2nd Level* : For that year Import or export division by commodity