charts-and-graphs-scala(Scala)

Import Notebook

Chart and Graph Types with Scala

This notebook covers the various charts and graphs that are built into Databricks.

While Scala is used to generate the test data displayed in the visualizations in this notebook, all the information about how to configure these charts & graphs applies to all notebooks.

A Table View is the most basic way to view data.

  • Only the first 1000 rows will be displayed in the table view.
case class MapEntry(key: String, value: Int)
val largeSeries = for (x <- 1 to 5000) yield MapEntry("k_%04d".format(x), x)
val largeDataFrame = sc.parallelize(largeSeries).toDF()
largeDataFrame.registerTempTable("largeTable")
display(spark.sql("select * from largeTable"))
 
key
value
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
k_0001
1
k_0002
2
k_0003
3
k_0004
4
k_0005
5
k_0006
6
k_0007
7
k_0008
8
k_0009
9
k_0010
10
k_0011
11
k_0012
12
k_0013
13
k_0014
14
k_0015
15
k_0016
16
k_0017
17

Truncated results, showing first 1000 rows.

Tables in Databricks Cloud are configured with Plot Options....

  • The Keys section is for specifying the control variable which is typically displayed as the X-Axis on many of the graph types. Most graphs can plot about 1000 values for the keys, but again - it varies for different graphs.
  • The Values section is for specifying the observed variable and is typically displayed on the Y-Axis. This also tends to be an observed numerical value on most graph types.
  • The Series groupings section is for specifying ways to break out the data - for a bar graph - each series grouping has a different color for the bars with a legend to denote that value of each series grouping. Many of the graph types can only handle series groupings that has 10 or less unique values.

Some graph types also specifying even more options - and those will be discussed as applicable.

A Pivot Table is another way to view data in a table format.

Instead of just returning the raw results of the table - it can automatically sort, count total or give the average of the data stored in the table.

  • Read more about Pivot Tables here: http://en.wikipedia.org/wiki/Pivot_table
  • For a Pivot Table, key, series grouping and value fields can be specified.
  • The Key is the first column, and there will be one row per key in the Pivot Table.
  • There will be additional column for each unique value for the Series Grouping.
  • The table will contain the Values field in the cells. Value must be a numerical field that can be combined using aggregation functions.
  • Cell in the Pivot Table are calculated from multiple rows of the original table.
    • Select SUM, AVG, MIN, MAX, or COUNT as the way to combine the original rows into that cell.
  • Pivoting is done on the server side of Databricks Cloud to calculate the cell values.

To create a Pivot Table, click on the Graph Icon below a result and select Pivot:

plot-menu-pick-pivot

// Click on the Plot Options Button...to see how this pivot table was configured.
// NOTE how Pivot Tables are highlighted in green to distinguish them from regular charts.
case class PivotEntry(key: String, series_grouping: String, value: Int)
val largePivotSeries = for (x <- 1 to 5000) yield PivotEntry("k_%03d".format(x % 200),"group_%01d".format(x % 3), x)
val largePivotDataFrame = sc.parallelize(largePivotSeries).toDF()
largePivotDataFrame.registerTempTable("table_to_be_pivoted")
display(spark.sql("select * from table_to_be_pivoted"))
 
key
group_0
group_1
group_2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
k_000
21600
20000
23400
k_001
18408
21609
20008
k_002
20016
18416
21618
k_003
21627
20024
18424
k_004
18432
21636
20032
k_005
20040
18440
21645
k_006
21654
20048
18448
k_007
18456
21663
20056
k_008
20064
18464
21672
k_009
21681
20072
18472
k_010
18480
21690
20080
k_011
20088
18488
21699
k_012
21708
20096
18496
k_013
18504
21717
20104
k_014
20112
18512
21726
k_015
21735
20120
18520
k_016
18528
21744
20128
k_017
20136
18536
21753
k_018
21762
20144
18544
k_019
18552
21771
20152

Aggregated in the backend.

Another way to think of a pivot table is that it does a group by on your original table by the key & series grouping, but instead of outputting (key, series_grouping, aggregation_function(value)) tuples, it outputs a table where the schema is the key and every unique value for the series grouping.

  • See the results of group_by statement below, which contains all the data that is in the pivot table above.
%sql select key, series_grouping, sum(value) from table_to_be_pivoted group by key, series_grouping order by key, series_grouping
 
key
series_grouping
_c2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
k_000
group_0
21600
k_000
group_1
20000
k_000
group_2
23400
k_001
group_0
18408
k_001
group_1
21609
k_001
group_2
20008
k_002
group_0
20016
k_002
group_1
18416
k_002
group_2
21618
k_003
group_0
21627
k_003
group_1
20024
k_003
group_2
18424
k_004
group_0
18432
k_004
group_1
21636
k_004
group_2
20032
k_005
group_0
20040
k_005
group_1
18440

Showing all 600 rows.

A Bar Chart is a type of visual pivot table graph and a great basic way to visualize data.

  • Plot Options... was used to configure the graph below.
  • The Key is Year and appears on the X-Axis.
  • The Series groupings is Category and there is a different color to denote each of those.
  • The Values is salesAmount and appears on the Y-Axis.
  • Sum was selected as the aggregation method, which means sales amounts are summed for all the products in the category for that year.
case class SalesEntry(category: String, product: String, year: Int, salesAmount: Double)
val salesEntryDataFrame = sc.parallelize(
  SalesEntry("fruits_and_vegetables", "apples", 2012, 100.50) :: 
  SalesEntry("fruits_and_vegetables", "oranges", 2012, 100.75) :: 
  SalesEntry("fruits_and_vegetables", "apples", 2013, 200.25) :: 
  SalesEntry("fruits_and_vegetables", "oranges", 2013, 300.65) :: 
  SalesEntry("fruits_and_vegetables", "apples", 2014, 300.65) :: 
  SalesEntry("fruits_and_vegetables", "oranges", 2015, 100.35) ::
  SalesEntry("butcher_shop", "beef", 2012, 200.50) :: 
  SalesEntry("butcher_shop", "chicken", 2012, 200.75) :: 
  SalesEntry("butcher_shop", "pork", 2013, 400.25) :: 
  SalesEntry("butcher_shop", "beef", 2013, 600.65) :: 
  SalesEntry("butcher_shop", "beef", 2014, 600.65) :: 
  SalesEntry("butcher_shop", "chicken", 2015, 200.35) ::
  SalesEntry("misc", "gum", 2012, 400.50) :: 
  SalesEntry("misc", "cleaning_supplies", 2012, 400.75) :: 
  SalesEntry("misc", "greeting_cards", 2013, 800.25) :: 
  SalesEntry("misc", "kitchen_utensils", 2013, 1200.65) :: 
  SalesEntry("misc", "cleaning_supplies", 2014, 1200.65) :: 
  SalesEntry("misc", "cleaning_supplies", 2015, 400.35) ::
  Nil).toDF()
salesEntryDataFrame.registerTempTable("test_sales_table")
display(spark.sql("select * from test_sales_table"))
0.002004006008001.0k1.2k1.4k1.6k1.8k2.0k2.2k20122013201420150.002004006008001.0k1.2k1.4k1.6k1.8k2.0k2.2k20122013201420150.002004006008001.0k1.2k1.4k1.6k1.8k2.0k2.2k2012201320142015TOOLTIPfruits_and_vegetablesbutcher_shopmiscyearsalesAmountcategoryfruits_and_vegetablesfruits_and_vegetablesbutcher_shopbutcher_shopmiscmisc

Tip: Hover over each bar in the chart below to see the exact values plotted.

A Line Graph is another example of a pivot table graph that can highlight trends for your data set.

  • Plot Options... was used to configure the graph below.
  • The Key is Year and appears on the X-Axis.
  • The Series groupings is Product and there is different color to denote each of those.
  • The Values is salesAmount and appears on the Y-Axis.
  • Sum was selected as the aggregation method, which means sales amounts are summed for all the products in the category for that year.
%sql select cast(string(year) as date) as year, category, salesAmount from test_sales_table
year201220145001.0k1.5k2.0kcategorymiscmiscfruits_and_vegetabl...fruits_and_vegetablesbutcher_shopbutcher_shop

A Pie Chart is pivot table graph type that can allow you to see what percentage of the whole your values represent.

  • NOTE: As opposed to the previous examples, Key & Series Groupings have been switched.
  • Plot Options... was used to configure the graph below.
  • The Key is Category and one color is used for each product.
  • The Series groupings is Year and there is different pie chart for each year.
  • The Values is salesAmount and is used to calculate the percentage of the pie.
  • Sum is selected as the aggregation method.
%sql select * from test_sales_table
fruits_and_vegetablesbutcher_shopmisc14%29%57%14%29%57%14%29%57%14%29%57%fruits_and_vegetablesbutcher_shopmisc2012201320142015

A Map Graph is a way to visualize your data on a map.

  • Plot Options... was used to configure the graph below.
  • Keys should contain the field with the location.
  • Series groupings is always ignored for World Map graphs.
  • Values should contain exactly one field with a numerical value.
  • Since there can multiple rows with the same location key, choose "Sum", "Avg", "Min", "Max", "COUNT" as the way to combine the values for a single key.
  • Different values are denoted by color on the map, and ranges are always spaced evenly.
    • Apply a smoothing function to your graph if your values are not evenly distributed.

To plot just a graph of the US by state, use US state postal codes as the key.

case class StateEntry(state: String, value: Int)
val stateRDD = sc.parallelize(
  StateEntry("MO", 1) :: StateEntry("MO", 10) ::
  StateEntry("NH", 4) ::
  StateEntry("MA", 8) ::
  StateEntry("NY", 4) ::
  StateEntry("CA", 7) ::  Nil).toDF()
stateRDD.registerTempTable("test_state_table")
display(spark.sql("Select * from test_state_table"))
+−N/A0-22-44-66-88-1010-12

To plot a graph of the world, use country codes in ISO 3166-1 alpha-3 format as the key.

// Reminder: It's not a requirement to register this RDD as a temp table for Spark SQL - display can also be called directly on the RDD. 
case class WorldEntry(country: String, value: Int)
val worldRDD = sc.parallelize(
  WorldEntry("USA", 1000) ::
  WorldEntry("JPN", 23) ::
  WorldEntry("GBR", 23) ::
  WorldEntry("FRA", 21) ::
  WorldEntry("TUR", 3) ::
  Nil).toDF()
display(worldRDD)
+−N/A0-200200-400400-600600-800800-1000

A Scatter Plot allows you to see if there is a correlation between two variables.

  • Plot Options... was selected to configure the graph below.
  • Keys will be used to color the points on the graphs - with a legend on the side.
  • Series Grouping is ignored.
  • Value must contain at least two numberical fields. This graph has a, b, and c as the values.
  • The diagonal of the resulting plot is the kernal density plot of the variable.
  • The row always has the variable in the Y-Axis, and the column has the variable in the X-Axis.
case class ScatterPlotEntry(key: String, a: Double, b: Double, c: Double)
val scatterPlotRDD = sc.parallelize(
  ScatterPlotEntry("k1", 0.2, 120, 1) :: ScatterPlotEntry("k1", 0.4, 140, 1) :: ScatterPlotEntry("k1", 0.6, 160, 1) :: ScatterPlotEntry("k1", 0.8, 180, 1) ::
  ScatterPlotEntry("k2", 0.2, 220, 1) :: ScatterPlotEntry("k2", 0.4, 240, 1) :: ScatterPlotEntry("k2", 0.6, 260, 1) :: ScatterPlotEntry("k2", 0.8, 280, 1) ::
  ScatterPlotEntry("k1", 1.2, 120, 1) :: ScatterPlotEntry("k1", 1.4, 140, 1) :: ScatterPlotEntry("k1", 1.6, 160, 1) :: ScatterPlotEntry("k1", 1.8, 180, 1) ::
  ScatterPlotEntry("k2", 1.2, 220, 2) :: ScatterPlotEntry("k2", 1.4, 240, 2) :: ScatterPlotEntry("k2", 1.6, 260, 2) :: ScatterPlotEntry("k2", 1.8, 280, 2) ::
  ScatterPlotEntry("k1", 2.2, 120, 1) :: ScatterPlotEntry("k1", 2.4, 140, 1) :: ScatterPlotEntry("k1", 2.6, 160, 1) :: ScatterPlotEntry("k1", 2.8, 180, 1) ::
  ScatterPlotEntry("k2", 2.2, 220, 3) :: ScatterPlotEntry("k2", 2.4, 240, 3) :: ScatterPlotEntry("k2", 2.6, 260, 3) :: ScatterPlotEntry("k2", 2.8, 280, 3) ::
  Nil).toDF()
display(scatterPlotRDD)
keyk1k1k2k2a1.001.502.002.503.001.002.00200b1502002501.002.003.00c

LOESS Fit Curves for Scatter Plots

LOESS is a method of performing local regression on your data to produce a smooth estimation curve that describes the data trend of your scatter plot. It does this by interpolating a curve within its neighborhood of data points. The LOESS fit curve is controlled by a bandwidth parameter that specifies how many neighboring points should be used to smooth the plot. A high bandwidth parameter (close to 1) gives a very smooth curve that may miss the general trend, while a low bandwidth parameter (close to 0) does not smooth the plot much.

LOESS fit curves are now available for scatter plots. Here is an example of how you can create a LOESS fit for your scatter plots.

NOTE: If your dataset has more than 5000 data points, the LOESS fit is computed using the first 5000 points.

// Create data points for scatter plot
val rng = new scala.util.Random(0)
val points = sc.parallelize((0L until 1000L).map { x => (x/100.0, 4 * math.sin(x/100.0) + rng.nextGaussian()) }).toDF()
rng: scala.util.Random = scala.util.Random@734027a8 points: org.apache.spark.sql.DataFrame = [_1: double, _2: double]

You can turn this data into a scatter plot using the controls on the bottom left of the display table.

plot-menu-pick-scatter

You can now access the LOESS fit option when you select Plot Options:

screen shot 2015-10-13 at 3 43 16 pm

You can experiment with the bandwith parameter to see how the curve adapts to noisy data.

Once you accept the change, you will see the LOESS fit on your scatter plot!

display(points)
-6.00-5.00-4.00-3.00-2.00-1.000.001.002.003.004.005.006.000.002.004.006.008.00_1_2

A Histogram allows you to determine the distribution of values.

  • Plot Options... was selected to configure the graph below.
  • Value should contain exactly one field.
  • Series Grouping is always ignored.
  • Keys can support up to 2 fields.
    • When no key is specified, exactly one histogram is output.
    • When 2 fields are specified, then there is a trellis of histograms.
  • Aggregation is not applicable.
  • Number of bins is a special option that appears only for histogram plots, and controls the number of bins in the histogram.
  • Bins are computed on the serverside for histograms, so it can plot all the rows in a table.
// Hover over the entry in the histogram to read off the exact valued plotted.
case class HistogramEntry(key1: String, key2: String, value: Double)
val HistogramRDD = sc.parallelize(
  HistogramEntry("a", "x", 0.2) :: HistogramEntry("a", "x", 0.4) :: HistogramEntry("a", "x", 0.6) :: HistogramEntry("a", "x", 0.8) :: HistogramEntry("a", "x", 1.0) ::
  HistogramEntry("b", "z", 0.2) :: HistogramEntry("b", "x", 0.4) :: HistogramEntry("b", "x", 0.6) :: HistogramEntry("b", "y", 0.8) :: HistogramEntry("b", "x", 1.0) ::
  HistogramEntry("a", "x", 0.2) :: HistogramEntry("a", "y", 0.4) :: HistogramEntry("a", "x", 0.6) :: HistogramEntry("a", "x", 0.8) :: HistogramEntry("a", "x", 1.0) ::
  HistogramEntry("b", "x", 0.2) :: HistogramEntry("b", "x", 0.4) :: HistogramEntry("b", "x", 0.6) :: HistogramEntry("b", "z", 0.8) :: HistogramEntry("b", "x", 1.0) ::
  Nil).toDF()
display(HistogramRDD)
0.000.200.400.600.801.00ax0.200.300.400.500.600.700.800.901.00bz0.000.200.400.600.801.000.000.200.400.600.801.000.200.300.400.500.600.700.800.901.00yvalueDensity

A Quantile plot allows you to view what the value is for a given quantile value.

  • For more information on Quantile Plots, see http://en.wikipedia.org/wiki/Normal_probability_plot.
  • Plot Options... was selected to configure the graph below.
  • Value should contain exactly one field.
  • Series Grouping is always ignored.
  • Keys can support up to 2 fields.
    • When no key is specified, exactly one quantile plot is output.
    • When 2 fields are specified, then there is a trellis of quantile plots .
  • Aggregation is not applicable.
  • Quantiles are not being calculated on the serverside for now, so only the 1000 rows can be reflected in the plot.