<jupyter_start><jupyter_text>Magic functionsYou can enable magic functions by loading ``pandas_td.ipython``:<jupyter_code>%load_ext pandas_td.ipython<jupyter_output><empty_output><jupyter_text>It can be loaded automatically by the following configuration in "~/.ipython/profile_default/ipython_config.py":<jupyter_code>c = get_config()

c.InteractiveShellApp.extensions = [
    'pandas_td.ipython',
]<jupyter_output><empty_output><jupyter_text>After loading the extension, type "%td" and press TAB to list magic functions:![Image](http://i.gyazo.com/ff9d7c5de1b16a21236fa7fb5d47d5a4.png)  List functions``%td_databases`` returns the list of databases:<jupyter_code>%td_databases<jupyter_output><empty_output><jupyter_text>``%td_tables`` returns the list of tables:<jupyter_code>%td_tables sample<jupyter_output><empty_output><jupyter_text>``%td_jobs`` returns the list of recently executed jobs:<jupyter_code>%td_jobs<jupyter_output><empty_output><jupyter_text>Use database ``%td_use`` is a special function that has side effects.  First, it pushes table names into the current namespace:<jupyter_code>%td_use sample_datasets<jupyter_output>INFO: import nasdaq
INFO: import www_access<jupyter_text>By printing a table name, you can describe column names:<jupyter_code>nasdaq<jupyter_output><empty_output><jupyter_text>Tab completion is also supported:![Tab completion](http://i.gyazo.com/fde882bc1bd665ba080cbd72222e27cb.png) As the second effect of ``%td_use``, it implicitly changes "default database", which is used when you write queries without database names.  Query functions ``%%td_hive``, ``%%td_pig``, and ``%%td_presto`` are cell magic functions that run queries:<jupyter_code>%%td_presto
select count(1) cnt
from nasdaq<jupyter_output><empty_output><jupyter_text>The result of the query can be stored in a variable by ``-o``:<jupyter_code>%%td_presto -o df
select count(1) cnt
from nasdaq
df<jupyter_output><empty_output><jupyter_text>Or you can save the result into a file by ``-O``:<jupyter_code>%%td_presto -O './output.csv'
select count(1) cnt
from nasdaq<jupyter_output>INFO: saved to './output.csv'<jupyter_text>Python-style variable substition is supported:<jupyter_code>start = '2010-01-01'
end = '2011-01-01'
%%td_presto
select count(1) cnt
from nasdaq
where td_time_range(time, '{start}', '{end}')<jupyter_output><empty_output><jupyter_text>You can preview the actual query by ``--dry-run`` (or ``-n``):<jupyter_code>%%td_presto -n
select count(1) cnt
from nasdaq
where td_time_range(time, '{start}', '{end}')<jupyter_output><empty_output><jupyter_text>Time-series indexWith magic functions, "time" column is converted into time-series index automatically.  You can use ``td_date_trunc()`` or ``td_time_format()`` in combination with ``GROUP BY`` for aggregation:<jupyter_code>%%td_presto
select
    -- Time-series index (yearly)
    td_date_trunc('year', time) time,

    -- Same as above
    -- td_time_format(time, 'yyyy-01-01') time,

    count(1) cnt
from
    nasdaq
group by
    1
limit
    3<jupyter_output><empty_output><jupyter_text>Plotting``--plot`` is a convenient option for plotting.  The first column represents x-axis.  Other columns represent y-axis:<jupyter_code>%matplotlib inline
%%td_presto --plot
select
    -- x-axis
    td_date_trunc('year', time) time,

    -- y-axis
    min(low) low,
    max(high) high
from
    nasdaq
where
    symbol = 'AAPL'
group by
    1<jupyter_output><empty_output><jupyter_text>In practice, however, it is more efficient to execute rough calculation on the server side and store the result into a variable for further analysis:<jupyter_code>%%td_presto -o df
select
    -- daily summary
    td_date_trunc('day', time) time,

    min(low) low,
    max(high) high,
    sum(volume) volume
from
    nasdaq
where
    symbol = 'AAPL'
group by
    1
# Use resample for local calculation
df['high'].resample('1m', how='max').plot()<jupyter_output><empty_output><jupyter_text>``--plot`` provides a shortcut way of plotting "pivot charts", as a combination of ``pivot()`` and ``plot()``.  If the query result contains non-numeric columns, or column names ending with "_id", they are used as ``columns`` parameter:<jupyter_code>%%td_presto --plot
select
    -- x-axis
    td_date_trunc('month', time) time,

    -- columns
    symbol,

    -- y-axis
    avg(close) close 
from
    nasdaq
where
    symbol in ('AAPL', 'MSFT')
group by
    1, 2<jupyter_output><empty_output><jupyter_text>Pivot tables ``--pivot`` creates a pivot table from the result of query.  Like ``--plot``, the first column represents index and other non-numeric columns represents new columns:<jupyter_code>%%td_presto --pivot
select
    td_date_trunc('year', time) time,
    symbol,
    avg(close) close 
from
    nasdaq
where
    td_time_range(time, '2010', '2015')
    and symbol like 'AA%'
group by
    1, 2<jupyter_output><empty_output><jupyter_text>Verbose outputBy passing ``-v`` (``--verbose``) option, you can print pseudo Python code that was executed by the magic function.<jupyter_code>%%td_presto -v --plot
select
    td_date_trunc('year', time) time,
    sum(volume) volume
from
    nasdaq
group by
    1<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1198379004245465, 'fraction_numerical': 0.015052103434967195, 'mean_word_length': 5.503136762860728, 'pattern_counts': {'":': 1, '<': 71, '<?xml version=': 0, '>': 71, 'https://': 0, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '68992', 'license': 'Apache-2.0', 'n_tokens_mistral': 1854, 'n_tokens_neox': 1810, 'n_words': 479, 'path': 'doc/magic.ipynb', 'repo_name': 'tokubai/pandas-td', 'size': '5182'}