Making Magic with pandas-td

Making Magic with pandas-td

Magic functions enable common tasks by saving you typing. (NOTE:  Pandas itself doesn’t have magic functions; the IPython kernel does.) Magic functions are functions preceeded by a % symbol. Magic functions have been introduced into pandas-td version 0.8.0!  Toru Takahashi from Treasure Data walks us through.

Treasure Data’s magic functions work by wrapping a separate % around the original function, making the functions callable by %%.  Let’s explore further to see how this works.

Until now

We start by creating a connection, importing our relevant libraries, and issuing a basic query, all from python (in Jupyter). Using the sample data, it would look like this:

import os
import pandas_td as td

#Initialize connection
con = td.connect(apikey=os.environ[‘TD_API_KEY’], endpoint = ‘’)
engine = con.query_engine(database=’sample_datasets’, type=’presto’)
#Read Treasure Data query into a DataFrame
df = td.read_td(‘select * from www_access, engine’)

With the magic function

We can now do merely this:


select count(1) as cnt
from nasdaq

If you add the table name nasdaq after %% td_use, you can also see the schema:


Even better, you can tab edit the stored column names:

Get Treasure Data blogs, news, use cases, and platform capabilities.

Thank you for subscribing to our blog!


As long as %matplotlib inline is enabled; then you can throw a query into magic’s %%td_presto – -plot and immediately visualize it!

Screen Shot 2015-08-14 at 2.05.20 PM

Very convenient!

How to enable it

Set the API_KEY environment variable:
export TD_API_KEY=1234/abcd…

You can then load the magic comment automatically! You’ll want to save the following to ~/.ipython/profile_default/

c = get_config()

Let’s review

Loading your data:

Querying your data with presto:
review_query with presto

Accessing stored columns:


Stay tuned for many more useful functions from pandas-td! These tools, including Pandas itself, as well as Python and Jupyter are always changing, so please let us know if anything is working differently than what’s shown here.

John Hammink
John Hammink
John Hammink is Chief Evangelist for Treasure Data. An 18-year veteran of the technology and startup scene, he enjoys travel to unusual places, as well as creating digital art and world music.