Getting Started Guide
- Getting Started GuideRefreshed: 1/6/2019 10:03:15 AM2k views, 7 likes
- Open Without Header
- Sign Up
# Exploratory Quick Start Guide *Duration (Time to finish) : About 60 minutes* This is a getting started guide that walks you through how to get around with Exploratory Desktop by performing exploratory data analysis for US flight delay data. The main areas to be covered are: - Import data - Inspect Data with Summary view - Data Wrangling (Data Transformation) - Visualize Data - Use Machine Learning Algorithm - Create a Dashboard and Share There is Table of Contents at the left-hand side, you can click on each item to directly jump to the corresponding topic, though we’d recommend you follow this tutorial step by step from the beginning to the end. If you have any questions while you go through the steps, feel free to ask through the Chat window,  or, send your questions to support@exploratory.io by email. # Preparation ## Download Flight Delay Data In this tutorial, we’re going to use US flight delay data for September, 2016, which has the following information for all the US domestic flights that flew in the month. - Flight dates and times - Airline company codes - States, Cities, and Airports of the departures and the arrivals - Air times and distances for the flights You can download the sample data from the link below. - [US Flight Delay Data for 2016 September](https://exploratory.io/data/kanaugust/US-Flight-Data-2016-September-zpa0tPG5yU/US%20Flight%20Data%20-%202016%20September.csv) ## Create a New Project Once you start Exploratory app, create a new project to start.  Type a project name and click ‘Create’ button.  ## Import Data Inside the project, you can click on ‘+’ (plus) button next to ‘Data Frame’ text in the left side pane, and select ‘File Data’ from the dropdown menu.  In the 'File Data Source' dialog, select 'Text File (CSV, delimited)'.  This will open a file picker where you want to select the US Flight Delay data you have downloaded at the previous step. If you haven't, download it from [here](https://exploratory.io/data/kanaugust/US-Flight-Data-2016-September-zpa0tPG5yU/US%20Flight%20Data%20-%202016%20September.csv). Once you have selected the file, you will see 'Import Local Data' dialog.  In the preview section at the right hand side, you would see the first 50 rows of the data. You can keep all the parameters as is for this exercise, and simply click ‘Save’ button at the bottom. It will ask you to enter a name for this data. For this exercise, you can keep it as is and hit 'Create' button.  Once the data file is imported, you should see it in Summary View like below.  Click ‘Hide Side Bar’ button at the top to hide the left hand side section so that you will have more space for the main view.  # Understand Data at Glance under Summary View In this view, you can briefly understand the data at glance. For example, you can see that there are 12 unique airline carriers in CARRIER column and top seven most frequent airline carriers in this data set as a bar chart.  Each number shown on each bar indicates how many rows for each value. For example, we can read that WN (South West) airline has 104,083 rows of data in this data. Let's work with this data to clean and transform a little bit in the next Data Wrangling section. # Data Wrangling We call it Data Wrangling, but what it means is anything to do with transforming the data to make it easier to visualize and analyze. Data Wrangling is not about cleaning the data or something that should be done before the data analysis. It's often conducted as part of the data analysis, and the 80% of the data analysis time is indeed spent for this Data Wrangling related tasks. This is why Exploratory doesn't treat Data Wrangling as a separate task, rather it provides a seamless experience well integrated with data visualization and analysis with statistics and machine learning algorithms in a reproducible way. There are hundreds, if not thousands, of many data wrangling tasks you can do in Exploratory, but let's take a look at the following 3 tasks in this tutorial. - Parse character data and convert to Date - Separate a column into multiple columns - Remove NA (Missing Values) ## Parse Character data and Convert to Date Take a look at FL_DATE column under Summary view.  It seems to contain date data but it’s registered as ‘character’ data type. > In general, the data type for each column is determined appropriately with heuristic guess based on the original data at the Data Import time. But sometimes it doesn’t give you what you expect due to the way the original data is formatted. But, you can quickly and flexibly convert the data type after the import. And being Character data type makes each date to be treated as a category when we try to visualize this data, which we don't want. We'd rather want this column to be Date data type so that we can treat it as a time series data, which would not only make the data visualization easier but also allows us to summarize the data at different levels of date information. To convert Character data to Date data, select ‘Change Data Type’ -> ‘Convert to Date / Time’ -> ‘Year, Month, Day’ from the column header menu.  We selected ‘Year, Month, Day’ because the original data was formatted in this particular order. (e.g. 2016 Sep. 05)  Selecting the menu above will open ‘Mutate (Create Calculation)’ dialog.  In Calculation Editor, you will notice the following function is already pre-populated. ``` ymd(FL_DATE) ``` This ‘ymd’ function is one of the date / time parsing functions from R package called ‘lubridate’, which parses the incoming data with an assumption that the data is presented in an order of Year, Month, and Day, and convert to ‘Date’ data type appropriately. Note that the order of the date component is the only thing that matters here. In other words, the separating characters or if the month name is full name (August) vs. short name (Aug) doesn’t matter. Any of the following text data can be converted by the 'ymd' function appropriately. ``` 2016 Sep. 29 2016 Septempber 29 2016/Septempber/29 2016-9-29 year 2016, on Septempber 29th 2016年9月29日 ``` What it really matters is the order of Year, Month, and Day. It’s that simple! You can see more details about the function by clicking on ‘Show Document' button.  Also, you can click on ‘Function List’ button to see all the other R functions that are available out of the box. Anyway, you can click on ‘Run’ button to execute the command. At this point, you would notice two things. First, you can see a new step called 'Mutate' being added to the right hand side Data Wrangling step section.  You can now see ‘FL_DATE’ column being converted to Date. It’s showing a histogram chart visualizing how the data is distributed along the timeline. Also, you can see that the date range is from 2016-08-01 to 2016-08-31. You would also notice that there are two color boxes (purple and green) at the top. In this step, you can see a green colored token (box) and read the following text inside the token. ``` FL_DATE = ymd(FL_DATE) ``` This means that we used the 'ymd' function to convert 'FL_DATE' column to Date data type and overrides FL_DATE column with the returned values. This 'Mutate' is one of the data wrangling commands and it is used when you want to do some calculations for each row. It is similar to what you would do with the formula in Excel. There are other data wrangling commands like Summarize, Filter, Select, etc. but we'll cover them at different sections or different tutorials. Now, another thing you would notice is the FL_DATE column itself. It is now shown as Date type.  When you look at the chart you can see how the data is distributed along with the time line. Also, you can see the data is ranging from 2016-09-01 to 2016-09-30 by looking at the metrics area. Now, let's take a look at how this FL_DATE column is different before and after the data type conversion we have just done. Click the Data Source step at the right hand side that is before the Mutate step we have just added.  You can see the FL_DATE column being Character data type like it used to be.  Now, click the '2. Create Calculation (Mutate)' step at the right hand side again.  Then, you can see the FL_DATE column is back to Date data type again.  By clicking between the steps inside the Data Wrangling Step section, you can check how the data has been transformed, which will allow you to perform any data wrangling tasks iteratively and efficiently. Let's take a look at one more Mutate (Create Calculation) operation. ## Create a column to show whether a given flight is delayed or not When you scroll down the Summary view you will see ARR_DELAY (Arrival Delay time) column.  The unit is Minute, so we can see that the arrival delay time is ranging between -152 minutes and 1,334 minutes. The minus means that the flights actually arrived earlier than scheduled, instead of being delayed. Let's create a column that will show TRUE if a given flight is delayed and FALSE if a given flight is NOT delayed. Select 'Create Calculation (Mutate)' from the column header menu of the ARR_DELAY column.  In the 'Create Calculation (Mutate)' dialog, enter the following formula. ``` ARR_DELAY > 0 ```  This formula evaluates the value of ARR_DELAY column for each row and returns TRUE when the value is greater than 0, otherwise returns FALSE. Enter 'is_delayed' as the column name.  And click 'Run' button at the bottom. As you have seen before, you would notice two things here. First, take a look at Data Wrangling Step at the right hand side.  There is another token being added to the same '2. Create Calculation (Mutate)' step that was created before. You can see the conditional formula you have entered above. When you add a new calculation to either override the existing column or create a new column, it will be added to this 'Create Calculation (Mutate)' step. When you try to add a different type of data wrangling operation such as Filter, Summarize, Select, etc. then there will be a new step added to the Data Wrangling Step. Now, another thing you will notice here is the newly created column called 'is_delayed'.  This column's data type is Logical, which can have only TRUE or FALSE as its values. By looking at the metrics area, we can see that 136,361 flights were delayed and that's 29.98% of the whole flights. We have looked at how to create calculations to either override existing columns or create new columns. Now, let's take a look at how to separate an existing column into multiple columns. ## Separate a column into multiple columns Let’s take a look at how you can separate a column into multiple columns. Scroll up for a little bit, you can find a column called 'ORIGIN_CITY_NAME', which has both city name and US State name information.  By looking at the bar chart, we can see that Atlanta, GA (Georgia) has the most flights in this data. Also, by looking at the metrics area, we can see that there are 296 cities in this data. Now, let's say we want to see how many flights there were at US State level rather than the city level. Currently, since each value contains both the State and the city names, states like California is separated into many cities such as Los Angels, San Francisco, Oakland, etc., which makes it harder to see how many flights are for California, for example.  The good news is, we can simply separate this ORIGIN_CITY_NAME column into two columns, one is for City and another is for State. Let's move to Table view to make it easier see what we are going to do from now.  Select Separate -> Separate to Columns by -> Comma (,) from the column header menu of **ORIGIN_CITY_NAME** column.  In the opened 'Separate to Columns' dialog, type column names for the columns created by this command. Here, you can simply enter 'city' and 'state'.  By clicking 'Run' button you will see two new columns being added. One is the city column and another is the state column.  Again, let's take a look at Data Wrangling Step at the right hand side. You will notice that there is a new 'Separate to Columns' step being added.  At this point, the original 'ORIGIN_CITY_NAME' column is gone. But you can keep it along with the newly separated columns if you want. In such cases, click on the token inside the 'Separate to Columns' step to open the configuration dialog.  Then, change 'Remove Original Column' parameter to FALSE, then click 'Run' button again.  We can see both the original column and the newly separated columns.  Let's take a look at the last data wrangling operation, that is Filter. ## Remove NA (Missing Values) When you look at ARR_DELAY column in Summary view, you would notice that there is a slight red color in the green bar.  This red bar indicates the ratio of NA data or missing values in this column. You can see that 0.51% of the data is NA. There are many ways to handle NA values, but let’s try removing them with ‘filter’ command operation in this tutorial. Select ‘Filter’ -> ‘is not NA’ from the column header menu of ARR_DELAY column.  This will open ‘Filter’ dialog with ‘is not NA’ operator being already selected.  If you are interested, you can click on the operator dropdown to see many other data type specific operators that are supported out-of-the-box. If you are familiar with R and want to customize it further, you can always go to ‘Custom’ tab and start typing your ‘filter’ conditions the way you like. Anyway, you want to simply click on Run button here. You will see that NA is gone now.  Notice that there is a new step called '4. Filter' being added to the Data Wrangling Step section at the right hand side. You can click on the previous step to see how it was before this Filter step. ## Moving Data Wrangling Steps By the way, we have used this ARR_DELAY column as part of the calculation to create the 'is_delay' column. At that time, the calculation was done by ignoring NA values. We didn't have much of a problem with the operation, but usually it's better to take care of NA values before such calculation. In such cases, we want to move this Filter step where we removed the NAs to before the '2. Create Calculation (Mutate)' step.  You can move the step to anywhere by drag and drop.  Once you have moved the step, clicking on the 'Create Calculation (Mutate)' step will re-run the step, which means all the calculations inside the step will be re-caclulated on top of the filtered data automatically.  There are many other things you can do with Exploratory in Data Wrangling. If you are interested in more, take a look at other tutorials at our [How To](https://exploratory.io/howto/data%20wrangling) page. Now, let's start visualizing the flight delay data to explore. # Visualize Data There are many ways to visualize data with various chart types in Exploratory. In this exercise, let’s use the following 3 chart types to explore the data. - Pivot Table - Aggregation and Window Calculation - Line Chart - Time Series Data - Scatter Chart - Correlations and Trend lines But before going into the details of the charts, there is one cool feature you should know. That is called 'Pin'. ## Chart Pinning First, click 'Chart' tab to move to the Chart view.  Now, notice that there is a button called 'Pin', which is highlighted as green color, and there is a line between the button and '4. Separate to Columns' step, which is also highlighted as green.  This means that the data displayed in this chart (or pivot table) always comes from this highlighted step regardless of which step you select at the right hand side. This is convenient because some of the columns you assign to chart might not be presented in some steps and you don't want your chart to break because of this. Also, this allows you to go to different steps and update them while you are looking at the chart (or pivot) visualizing the updated data. If you want to change the Pin-ed step you can simply drag the Pin button and drop to the step you want the chart to show the data for. In the next few sections, we'll use the data from '4. Separate to Columns' step so if you Pin button is pointing to other steps then drag the Pin button and drop to the '4. Separate to Columns' step. Having the Pin button configured, let's create a Pivot Table. ## Pivot Table Let’s use Pivot table to understand which intersections of the states and days of week have more flights than the others. Select Pivot Table, and assign 'state' column to Row first.  You will see a number of the flights for each US State. Now, we want to show Day of Week at the Column. To do this, assign FL_DATE column to Column.  Since FL_DATE column is Date data type, we can change the way to aggregate the data along with various level of the date information. The default is to round (floor) the dates by Year, but we can change this to, for example, Week.  We can see the number of the flights for each week and each state. Not only rounding the date level, we can also extract date components from FL_DATE data. Select 'Day of Week Name' from the date function list.  We can see the number of the flights for each intersection of the states and day of week.  ### Color Setting We can use Color to make it easier to compare the numbers. Select 'All (Down then Across)' from 'Color By' dropdown menu.  There are many pre-registered color palettes, and you can change it from the Color Setting dialog.  ### Window Calculation - % of Total The numbers we are looking at in the Pivot Table are numbers of the flights here. But if you want to see the ratio rather than the actual numbers, then you want to use the Window Calculation feature. Click the three bars icon at the right hand side of Value box and select 'Window Calculation' from the menu.  Select '% of' from Calculation Type dropdown.  By default, it will show the ratio of the total number of rows (flights). But you might want to see the ratio of each state or each day of the week. In such cases, you can change the 'Direction' to 'Row' and see which day of the week has more flights for each state.  And you want to also change the Color By setting to 'Row' so that it matches with the direction of the '% of Total' calculation.  We can see that most of the states have more flights between Monday and Wednesday. And Saturday is the lowest for most of the states. You can click on ‘Sat’ to sort the data based on the values in this column. It turned out VI (US Virgin Islands) has more flights on Saturday than any other days of week, unlike any other US States. This is probably because more people go there for vacation than for business.  ## Line Chart - Time Series Data Let’s take a look at the trend of the flight departure delay times during this time period of September, 2016. Click the 'Plus' button at the left hand side of the chart tab to create a new chart.  Select ‘Line’ chart and assign ‘FL_DATE’ column to X-Axis. Since ‘FL_DATE’ column is Date data type, you can select a date level (e.g. Year, Month, Day) to aggregate the data. For example, you can set it to ‘Day’ to aggregate the data by day, instead of the default ‘Year’.  Now, assign ‘ARR_DELAY’ column to Y-Axis to see the trend of the arrival delay time. The default data aggregation function is set to ‘Sum’, but you can change this to something like ‘Average’.  You can assign ‘CARRIER’ column to Color to see the trend by each airline carrier.  Double click on any carrier code in the chart legend will show only the carrier's line in the chart.  And Single click on any carrier code will show or hide the line for the carrier code. If you want to bring all the carrier codes back just double click on any of the code. You can also use Drag and Drop gesture to select an area to zoom in.  Double click on any white part of the chart area will bring it back to the original. Also, you can show 'Time Range Slider' at the bottom, which makes it easier to select a time range to zoom in the chart.  ## Scatterplot Now, how the departure delay time and the arrival delay time are correlated? In other words, did the flights that delayed for the departure time also delayed for the arrival time? If so, were there any correlation between those two delayed times? Let's use Scatter chart to visualize the relationship between the two numeric columns. Click the Plus button to create a new chart.  Now, we can go to Viz view and select ‘Scatter’ as the chart type, assign ‘DEP_DELAY’ to X-Axis and ‘ARR_DELAY’ to Y-Axis.  There seems to be some degree of positive correlation between the two columns. In another word, when the value of one column increases the value of another column tend to increase as well. To investigate further, let's display 'Trend Line'. Select 'Trend Line' from the Y-Axis's action menu.  Select 'Linear Regression' as Type.  This will build a linear regression model to predict ARR_DELAY based on DEP_DELAY behind the scene and draw the predicted result as a linear line. This line is drawn so that all the distances between the line and all the dots combined are the smallest. You can bring your mouse pointer on top of the line to show a pop-up where you can see a few metrics.  One of them is called 'Correlation', which shows how much the two columns are correlated. It can range between -1 and 1. Being closer to 1 means it has a strong positive correlation while being closer to -1 means it has a strong negative correlation. And 0 means there is no correlation. Here, it is 0.94, which means there is a strong positive correlation between the arrival delay times and the departure delay times. Another useful metric is 'Coefficient', which indicates the slope of the line. Here, it is 1, which means that 1 minute increase of the departure delay (DEP_DELAY) ends up having 1 minute increase of the arrival delay (ARR_DELAY) for the same flight. Now, can this correlation be observed for all the airline carriers in the same way? To find that out, assign CARRIER column to 'Repeat By'. This will create multiple scatter charts each of which is for each carrier.  You can adjust ‘Repeat By’ properties to see all the charts better. For example, we can select ‘4’ for the number of the charts for each row, check ‘Fit All the Charts in Viz Area’ checkbox, and un-check ‘Sync Scales Among Charts’ to not synchronize the scales for X and Y axes among the charts.  We can see positive correlations between the departure delay and the arrival delay for all the carriers, though by looking at the correlation value on the trend lines some carriers show stronger correlation than the others. At this point, we can publish the charts we have created to Exploratory Cloud to embed them in web sites, share them in a reproducible way, or schedule them to make them always up-to-date. If you are interested, take a look at the following note. - Introducing Interactive Chart Sharing and Embedding - [Link](https://blog.exploratory.io/introducing-interactive-chart-sharing-and-embedding-4cfb801c063e) --- Visualization is great when you can recognize the patterns and trends with your eyes. But in reality, things are more complicated and it is hard to understand the patterns relying only on our eyes. This is when we want to use Statistics and Machine Learning algorithms to help us discover and understand such patterns inside the data. Let's take a look at one of the Machine Learning algorithm in the next section. --- # Use Machine Learning Algorithm - Random Forest For example, out of all these columns (or variables) like FL_DATE, ARR_DELAY, DEP_DELAY, FL_MUM, DISTANCE, etc, which are the most useful to characterize the carrier? In other words, if we don’t have CARRIER column, can we still predict which carrier is for any given row of the flight data? You can answer to this type of questions by using Machine Learning or Statistical algorithms. In Exploratory, you can use ‘Variable Importance’ feature under Analytics view. This internally uses Random Forest algorithm and help you find which columns are more influential in terms of predicting the outcome that you are interested. First, click Analytics Tab.  and select ‘Variable Importance (Random Forest)’ from Analytics Type pulldown list.  Then select ‘CARRIER’ for ‘Target Variable'.  Click ‘Variable Columns’ to open Column Selection Dialog.  You can see a list of the columns at the right hand side. This is where you will select the predictor variables (columns). Click the first column 'FL_DATE' at the top and click the last column 'is_delayed' while pressing Shift key. This will select all the columns between them, except 'CARRIER' column, which is already used for Target Variable.  Now, we want to remove the following 4 columns from the selection because it doesn't really make sense to include them. - ORIGIN_CITY_NAME - this column has already been split into 'city' and 'state'. - AIR_TIME - this information is basically the same information as DISTANCE. - CANCELLATION_CODE - this information is similar to CANCELLED. - is_delayed - this column was created based on ARR_DELAY column and has the similar information. Click these 4 columns to remove them from the selection.  Click 'OK' button to close this dialog, and click 'Run' button to run this analytics.  Once it's run, we can see a bar chart that shows which columns are more important to predict which carriers it would be. In another word, this chart shows which columns are more useful to characterize each carrier.  We can see that FL_NUM is one single most important column to characterize (or predict) carriers, and other columns like DISTANCE, DEST_CITY_NAME, ORIGIN, etc. are also more important than the others. Now that we know that some of the columns are more useful to identify which carrier a given flight is, but how? When you say FL_NUM variable is more important than the others, how are the values of FL_NUM variable different for each carrier? To answer this question, you can click 'Effect by Variables' tab. Under this tab, you can see how the relationships are between each variable and CARRIER (airline companies).  Each chart's Y-Axis is the probability, X-Axis shows each variable's values, and the color indicates which CARRIER is. By looking at the first chart, which is for FL_NUM variable, we can see that if the values of FL_NUM are greater than 4699 those flights have higher probability of being OO (Skywest Airline).  There are many other insights you can dig in from this analytics, but we will stop here for this tutorial. --- Once you have discovered useful insights you want to communicate and share them with others. In Exploratory, you can use Dashboard, Note, and Slides to do such job easily. In the next section, let's create a Dashboard, embed some of the charts we have created so far, and publish it to share with others in a reproducible way. If you are interested in Note or Slides, take a look at the following notes. - [An Introduction to Exploratory Note](https://exploratory.io/note/kanaugust/An-Introduction-to-Note-with-Simple-Markdown-Editor-5790975430740516) - [An Introduction to Exploratory Slides](https://exploratory.io/note/kei/9285503110951652) # Create Dashboard and Share You can create a new Dashboard from the Document menu. But, for this tutorial, let's try an easier short cut. Go back to the Chart view where we created the charts previously.  If we want to add this chart to a Dashboard, you can simply click on 'Add to Dashboard' button.  This will open 'Add to Dashboard' dialog. You can either click 'Add' button to add the chart to one of the existing Dashboards or click 'Add to a new Dashboard' to create a new Dashboard and add the chart to it. Here, let's do the latter. Click 'Add to a new Dashboard'.  Then, a new Dashboard has just been created and the chart has been added to the Dashboard.  You can click 'Open' button for this new Dashboard to open Dashboard Editor window directly.  You will see the chart is inside the Dashboard.  Let's add another chart we have created to this Dashboard. We want to add a new chart to the right side of the existing chart so click the Plus button at the right side.  This time, we want to add a chart, not an analytics chart, so select 'Chart' from the drop-down menu.  It will open a chart selection dialog. Make sure the right data frame is selected first, then click on the Pivot Table to select.  Then, click OK button. You will see two charts are now located next to each other.  You can modify the chart title that will be shown as the section header text, if you want.  Now that we are ready for the first Dashboard, let's see how it looks as an output. Click on 'Run' button at the top.  You will see the Dashboard output being generated.  You can share the Dashboard with others or schedule to make it always up-to-date by publishing it. Let's take a look how the publishing works in the next section. ## Publish Dashboard You can publish Dashboard simply by clicking 'Publish' button at the right hand side top.  Once you have entered Title, Description, etc. then you can click 'Publish' button at the bottom to publish the Dashboard to Exploratory Cloud (exploratory.io).  You can choose either Private Mode or Public Mode when you publish. If you choose to publish with Private Mode you will be able to set whom you want to share with later.  Once it's published successfully, the published Dashboard will have an unique URL to access it. You can click 'Open in Browser' link to open the published Dashboard directly.  Here is how it looks at Exploratory Cloud.  At the published Dashboard page, you can click 'Share' button to open a dialog window where you can configure whom you want to share with.  You can type email addresses of those you want to share with. They will receive notification emails to create Exploratory's viewer accounts (Free!) and open the Dashboard. If you don't want to bother asking others to create accounts and login to Exploratory Cloud, then you can enable 'URL Link' access by clicking on Enable button. This will create a special URL for this Dashboard which your audience can use to open the Dashboard. ## Schedule Dashboard In this tutorial, we have created the Dashboard with charts whose source data was the CSV file that was saved at your local PC. But, if you used remote data such as Databases, Cloud Apps (Google Analytics, Stripe, Mail Chimp, etc.) , Remote files (CSV, Excel, etc.), Web Scraping, etc. then you would see 'Schedule' button next to 'Share' button.  You can click this button to schedule your Dashboard periodically. There are many other things you can do with Dashboard. If you are interested, take a look at this note. - An Introduction to Exploratory Dashboard - [Link](https://exploratory.io/note/2ac8ae888097/0937480844541865) --- That's it for this tutorial! Congrats! If you have any questions, feel free to contact [Team Exploratory](mailto:support@exploratory.io). Cheers! Team Exploratory
<iframe src="https://exploratory.io/note/kanaugust/2617200410576325?embed=true" frameborder="0" width="100%" height="100%" > </iframe>
