---
title: "Create network graph to plan join strategy"
author: "Uwe Sterr"
date: "12/9/2017"
output: 
    html_document:
         code_folding: hide
---

# Find a strategy to join data.frames
This script creates a network graph of all data.frames available within the workspace. This helps me to plan a join strategy of the data.frames. The more data sources will be available this tool might become even more helpful.

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo=FALSE, error=FALSE, warning = FALSE)# For network graph
library(visNetwork)
library(tidyverse)
library(data.table)
```


### Define function to draw network plot
This function detects all data.frames available in the workspace and build nodes and edges for the network graph. 
The network graph is build with a single command. If you want to see more about how a network graph can be used check my popular football kernel https://www.kaggle.com/nappon/the-fans-stay-loyal-the-players-move-on 
```{r }
drawNetwork <- function(){
  
  edge <- data.frame(from=character(), 
                     to=character(), 
                     stringsAsFactors=FALSE) 
  dfNames <- names(which(sapply(.GlobalEnv, is.data.frame))) 
  list_of_frames <- list(NULL)
  for (f in 1:length(dfNames)){
    dfName <- dfNames[f]
    # fread would be faster, but assumes "." as deci separator, setting to "," resultet in error
    edge<- rbind(edge,data.frame(from= dfNames[f], to=names(get(dfNames[f]))))
    
  }
  
  
  nodesFrom <-  edge %>% cbind(unlist(.$from),"Table") %>% select(3,4) %>% data.frame  
  nodesTo <-  edge %>% cbind(unlist(.$to),"Attribute") %>% select(3,4) %>% data.frame 
  
  names(nodesFrom) <- c("id", "group")
  names(nodesTo) <- c("id", "group")
  
  nodes <- rbind(nodesFrom,nodesTo) %>% unique() 
  nodes$id <- as.character((nodes$id))  
  nodes <- nodes %>% unique() %>% arrange(id)
  visNetwork(nodes, edge)%>%
    visOptions(highlightNearest = list(enabled = TRUE, degree = 2), nodesIdSelection = TRUE) %>%
    visEdges(arrows = "to") %>%  
    visGroups(groupname = "Table",     shape = "icon", icon = list(code = "f114", color = "green",size = 75)) %>%
    visGroups(groupname = "Attribute", shape = "icon", icon = list(code = "f115", color = "lightgreen", size = 45)) %>%
    addFontAwesome() 
  # list of icons http://astronautweb.co/snippet/font-awesome/
  }
```

### Read the data in
Read the data into data.frames (tibbles). Could also only read in the headers, but this script is meant to be used in the context of a full data analysis.

```{r }
train <- sample_frac(as.tibble(fread('../input/train.csv')),0.001)
test <- as.tibble(fread('../input/test.csv'))
stores <- as.tibble(fread('../input/stores.csv'))
items <- as.tibble(fread('../input/items.csv'))
trans <- as.tibble(fread('../input/transactions.csv'))
oil <- as.tibble(fread('../input/oil.csv'))
holidays <- as.tibble(fread('../input/holidays_events.csv'))
```
### Create the network
The graph interactive, play with it.
You can click on a attribute and all connected data.frame will be highlighted. Give it a try and let me know if you have a suggestion for improvement.

```{r, figure}
drawNetwork()
```