Making Magic with pandas-td

Making Magic with pandas-td

Magic-hat-icon 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:

use_sample_datasets_nasdaq_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!

tab_edit_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!

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

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

Let’s review

Loading your data:
review_load

Querying your data with presto:
review_query with presto

Accessing stored columns:
review_stored_columns

Plotting:
review_plot

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.
Related Posts
  • 5 Geo-targeting Success Stories (and What You Can Learn From Them)Location, location, location. Smart marketers know that geotargeting represents a huge opportunity to increase customer conversion. Location-based advertising (LBA) has long been known to be an effective techni...
  • How to Create Four Different Customer Journey Maps (And Why You Might Need Them All)The most successful marketers understand how their customers arrive at a decision to buy—as well as how and where to meet a customer and become a trusted guide for the rest of their journey. Getting to kn...
  • The Data Nerd’s Guide to eTail EastAugust 19-22, eTail East comes to Boston. For retailers and marketers who are fascinated by data—and that should be all of us—this show offers such an embarrassment of riches, it's easy to get option paraly...