charts-and-graphs-python(Python)

Import Notebook

Chart and Graph Types with Python

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

While Python 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 are displayed in the table view.

from pyspark.sql import Row
 
array = map(lambda x: Row(key="k_%04d" % x, value = x), range(1, 5001))
largeDataFrame = spark.createDataFrame(sc.parallelize(array))
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
18
19
20
21
22
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
k_0018
18
k_0019
19
k_0020
20
k_0021
21
k_0022
22

Showing all 5000 rows.

Configure tables 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 allow 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 the Graph icon below a result and select Pivot.

# Click on the Plot Options Button...to see how this pivot table was configured.
from pyspark.sql import Row
 
largePivotSeries = map(lambda x: Row(key="k_%03d" % (x % 200), series_grouping = "group_%d" % (x % 3), value = x), range(1, 5001))
largePivotDataFrame = spark.createDataFrame(sc.parallelize(largePivotSeries))
largePivotDataFrame.registerTempTable("table_to_be_pivoted")
display(spark.sql("select * from table_to_be_pivoted"))
 
key
group_1
group_2
group_0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
k_001
21609
20008
18408
k_002
18416
21618
20016
k_003
20024
18424
21627
k_004
21636
20032
18432
k_005
18440
21645
20040
k_006
20048
18448
21654
k_007
21663
20056
18456
k_008
18464
21672
20064
k_009
20072
18472
21681
k_010
21690
20080
18480
k_011
18488
21699
20088
k_012
20096
18496
21708
k_013
21717
20104
18504
k_014
18512
21726
20112
k_015
20120
18520
21735
k_016
21744
20128
18528
k_017
18536
21753
20136
k_018
20144
18544
21762
k_019
21771
20152
18552
k_020
18560
21780
20160
k_021
20168
18568
21789

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, but the schema of the results is different.
%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
sum(value)
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 Product 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 rows are summed for pivoting.
from pyspark.sql import Row
salesEntryDataFrame = spark.createDataFrame(sc.parallelize([
  Row(category="fruits_and_vegetables", product="apples", year=2012, salesAmount=100.50),
  Row(category="fruits_and_vegetables", product="oranges", year=2012, salesAmount=100.75),
  Row(category="fruits_and_vegetables", product="apples", year=2013, salesAmount=200.25),
  Row(category="fruits_and_vegetables", product="oranges", year=2013, salesAmount=300.65),
  Row(category="fruits_and_vegetables", product="apples", year=2014, salesAmount=300.65),
  Row(category="fruits_and_vegetables", product="oranges", year=2015, salesAmount=100.35),
  Row(category="butcher_shop", product="beef", year=2012, salesAmount=200.50),
  Row(category="butcher_shop", product="chicken", year=2012, salesAmount=200.75),
  Row(category="butcher_shop", product="pork", year=2013, salesAmount=400.25),
  Row(category="butcher_shop", product="beef", year=2013, salesAmount=600.65),
  Row(category="butcher_shop", product="beef", year=2014, salesAmount=600.65),
  Row(category="butcher_shop", product="chicken", year=2015, salesAmount=200.35),
  Row(category="misc", product="gum", year=2012, salesAmount=400.50),
  Row(category="misc", product="cleaning_supplies", year=2012, salesAmount=400.75),
  Row(category="misc", product="greeting_cards", year=2013, salesAmount=800.25),
  Row(category="misc", product="kitchen_utensils", year=2013, salesAmount=1200.65),
  Row(category="misc", product="cleaning_supplies", year=2014, salesAmount=1200.65),
  Row(category="misc", product="cleaning_supplies", year=2015, salesAmount=400.35)
]))
salesEntryDataFrame.registerTempTable("test_sales_table")
display(spark.sql("select * from test_sales_table"))
20122013201420150500100015002000
fruits_and_vegetablesfruits_and_vegetablesbutcher_shopbutcher_shopmiscmiscyearsalesAmountcategorycategory

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 Category and there is different color to denote each of those.
  • The Values is salesAmount and appears on the Y-Axis.
  • Sum is selected as the aggregation method 
%sql select cast(string(year) as date) as year, category, salesAmount from test_sales_table
Jan 2012Mar 2012May 2012Jul 2012Sep 2012Nov 2012Jan 2013Mar 2013May 2013Jul 2013Sep 2013Nov 2013Jan 2014Mar 2014May 2014Jul 2014Sep 2014Nov 2014Jan 20150500100015002000
fruits_and_vegetablesfruits_and_vegetablesbutcher_shopbutcher_shopmiscmiscyearsalesAmountcategorycategory

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.

Tip: Apply a smoothing function to your graph if your values are not evenly distributed.

from pyspark.sql import Row
stateRDD = spark.createDataFrame(sc.parallelize([
  Row(state="MO", value=1), Row(state="MO", value=10),
  Row(state="NH", value=4),
  Row(state="MA", value=8),
  Row(state="NY", value=4),
  Row(state="CA", value=7)
]))
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.

from pyspark.sql import Row
worldRDD = spark.createDataFrame(sc.parallelize([
  Row(country="USA", value=1000),
  Row(country="JPN", value=23),
  Row(country="GBR", value=23),
  Row(country="FRA", value=21),
  Row(country="TUR", value=3)
]))
display(worldRDD)
+−N/A0-200200-400400-600600-800800-1000