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 = ‘https://api.treasuredata.com’)
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:

%%td_use_sample_datasets

%%td_presto
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:

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

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/ipython_config.py

c = get_config()
c.InteractiveShellApp.extensions=[
‘pandas_td.ipython’,
]

Let’s review

Loading your data:

Querying your data with presto:

Accessing stored columns:

Plotting:

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.