vastorbit.jupyter.extensions.chart_magic.chart_magic

vastorbit.jupyter.extensions.chart_magic.chart_magic(line: str, cell: str | None = None, local_ns: dict | None = None)

Draws responsive charts using the Matplotlib, Plotly library.

Different cutomization parameters are available for Plotly and Matplotlib. For a comprehensive list of customization features, please consult the documentation for the respective plotting libraries: plotly, matplotlib.

Parameters:

• --command (-c /) – SQL Command to execute.

• --file (-f /) – Input File. You can use this option if you want to execute the input file.

• --kind (-k /) –

  Chart Type, one of the following:

  • area:

    Area Chart.

  • area_range:

    Area Range Chart.

  • area_ts:

    Area Chart with Time Series Design.

  • bar:

    Bar Chart.

  • biserial:

    Biserial Point Matrix (Correlation between binary variables and numerical)

  • boxplot:

    Box Plot.

  • bubble:

    Bubble Plot.

  • candlestick:

    Candlestick and Volumes (Time Series Special Plot).

  • cramer:

    Cramer’s V Matrix (Correlation between categories).

  • donut:

    Donut Chart.

  • donut3d:

    3D Donut Chart.

  • heatmap:

    Heatmap.

  • hist:

    Histogram.

  • kendall:

    Kendall Correlation Matrix.

    Warning

    This method uses a CROSS JOIN during computation and is therefore computationally expensive at O(n * n), where n is the total count of the VastFrame.

  • line:

    Line Plot.

  • negative_bar:

    Multi-Bar Chart for binary classes.

  • pearson:

    Pearson Correlation Matrix.

  • pie:

    Pie Chart.

  • pie_half:

    Half Pie Chart.

  • pie3d:

    3D Pie Chart.

  • scatter:

    Scatter Plot.

  • spider:

    Spider Chart.

  • spline:

    Spline Plot.

  • stacked_bar:

    Stacker Bar Chart.

  • stacked_hist:

    Stacked Histogram.

  • spearman:

    Spearman Correlation Matrix.

• --output (-o /) – Output File. You can use this option if you want to export the result of the query to the HTML format.

Return type:

Chart Object

Examples

The following examples demonstrate:

• Setting up the environment

• Drawing graphics

• Exporting to HTML

• Using variables

• Using SQL files

Hint

To see more examples, please refer to the ref:chart_gallery.guide.

Setting up the environment

If you don’t already have one, create a new connection:

import vastorbit as vo

# Save a new connection
vo.new_connection(
    {
        "host": "10.211.55.14",
        "port": "5433",
        "database": "testdb",
        "password": "XxX",
        "user": "dbadmin",
    },
    name = "VASTDSN",
)

Otherwise, to use an existing connection:

vo.connect("VASTDSN")

Load the chart extension:

Run the following to load some sample datasets. Once loaded, these datasets are stored in the ‘public’ schema. You can change the target schema with the ‘schema’ parameter:

from vastorbit.datasets import load_titanic, load_amazon, load_iris

titanic = load_titanic()
amazon = load_amazon()
iris = load_iris()

Use the set_option() function to set your desired plotting library:

vo.set_option("plotting_lib","plotly")

Drawing graphics

The following examples draw various responsive charts from SQL queries.

Pie Chart

%chart -k pie -c "SELECT pclass, AVG(age) AS av_avg FROM titanic GROUP BY 1;"

Line Plot

%%chart -k line
SELECT
    date,
    AVG(number) AS number
FROM amazon
GROUP BY 1
ORDER BY 1;

Correlation Matrix

%%chart -k pearson
SELECT
    *
FROM titanic;

Bar Chart

%%chart -k bar
SELECT
    pclass,
    SUM(survived)
FROM titanic GROUP BY 1
ORDER BY 1;

Scatter Plot

%%chart -k scatter
SELECT
    PetalLengthCm,
    PetalWidthCm,
    Species
FROM iris;

Boxplot

%%chart -k boxplot
SELECT * FROM titanic;

Exporting to HTML

Export a chart to HTML:

%%chart -k scatter -o "my_graphic"
SELECT age, fare FROM titanic;

The following lines open the HTML file:

Note

The HTML graphic can be embedded in an external environment, such as a website.

file = open("my_graphic.html", "r")
file.read()
file.close()

Using Variables

You can use variables in charts with the ‘:’ operator:

import vastorbit.sql.functions as vof

class_fare = titanic.groupby(
    "pclass",
    [vof.avg(titanic["fare"])._as("avg_fare")],
)

	123 pclass Integer	123 avg_fare Double
1	3	13.30288870056499
2	2	21.1791963898917
3	1	87.50899164086687

Rows: 1-3 | Columns: 2

You can then use the variable in the query:

Note

In this example, we use a VastFrame, but it’s also possible to use a pandas.DataFrame, a numpy.array, and many other in-memory objects.

%%chart -k bar
SELECT * FROM :class_fare;

Using SQL files

Create charts from a SQL file:

file = open("/Users/badr.ouali/Documents/VastOrbit-master/docs/query.sql", "w+")
file.write("SELECT PetalLengthCm, PetalWidthCm, Species FROM iris;")
file.close()

file = open("query.sql", "w+")
file.write("SELECT PetalLengthCm, PetalWidthCm, Species FROM iris;")
file.close()

Using the -f option, we can easily read the above SQL file:

%chart -f query.sql -k scatter