%matplotlib inline

import os
import numpy as np
import pandas as pd
import pandas_td as td

# Initialize connection
con = td.connect(apikey=os.environ['TD_API_KEY'], endpoint='https://api.treasuredata.com/')

"""
Explanation: Getting Started
Pandas-TD allows you to read a query result into a DataFrame and to store a DataFrame to Treasure Data.
Before starting any session, you need to set your API key to the environment variable TD_API_KEY:
export TD_API_KEY=1234/abcd...

You can initialize your connection by connect:
End of explanation
"""


# Get a list of databases
con.databases()

# Get a list of tables
con.tables('sample_datasets')

"""
Explanation: Using a connection, you can use some convenient methods like databases and tables:
End of explanation
"""


# Select a query engine
presto = con.query_engine(database='sample_datasets', type='presto')

# Run a query
td.read_td('select * from nasdaq limit 3', presto)

"""
Explanation: You can choose a query engine and use it to run a queby by read_td:
End of explanation
"""


# Read from a table with 5-percent sampling
df = td.read_td_table('nasdaq', presto, time_range=('2000-01-01', '2010-01-01'), sample=0.05, limit=10000)

"""
Explanation: Alternatively, you can read a table into a DataFrame by read_td_table, optionally with a specific time range, sample rate, and/or a limit:
End of explanation
"""


# Create a DataFrame with random values
df = pd.DataFrame(np.random.rand(3, 3), columns=['x', 'y', 'z'])

# Import it into 'tutorial.tut1'
td.to_td(df, 'tutorial.tut1', con, if_exists='replace', index=False)

# Check the result
td.read_td('select * from tutorial.tut1', presto)

"""
Explanation: Pandas-TD also supports importing a DataFrame into a table by to_td:
End of explanation
"""


help(td.read_td_query)

"""
Explanation: read_td_query
End of explanation
"""


# Convert "time" to a time series index
df = td.read_td_query("select time, close from nasdaq where symbol='AAPL'",
                      presto,
                      index_col='time',
                      parse_dates={'time': 's'})
df.plot()

"""
Explanation: Examples
End of explanation
"""


help(td.read_td_table)

"""
Explanation: read_td_table
End of explanation
"""


# Read all records (up to 10,000 rows by default)
df = td.read_td_table("www_access", presto)
df.head(3)

# From 1990 to 2010, 1-percent sample, no limit
df = td.read_td_table("nasdaq",
                      presto,
                      index_col='time',
                      parse_dates={'time': 's'},
                      columns=['time', 'symbol', 'close'],
                      time_range=('1990', '2010'),
                      sample=0.01,
                      limit=None)
len(df)

df[df.symbol=='AAPL'].plot()

"""
Explanation: Examples
End of explanation
"""


help(td.to_td)

"""
Explanation: to_td
End of explanation
"""


# Create a DataFrame with random values
df = pd.DataFrame(np.random.rand(3, 3), columns=['x', 'y', 'z'])

"""
Explanation: Examples
End of explanation
"""


td.to_td(df, 'tutorial.tut1', con)

# Set "if_exists" to 'replace' or 'append'
td.to_td(df, 'tutorial.tut1', con, if_exists='replace')

"""
Explanation: to_td will fail if table already exists:
End of explanation
"""


td.to_td(df, 'tutorial.tut1', con, if_exists='replace', index=False)

"""
Explanation: Use index=False if you don't need to insert DataFrame index:
End of explanation
"""


import datetime

df = pd.DataFrame(np.random.rand(3, 3), columns=['x', 'y', 'z'])

# Set "time" column explicitly
df['time'] = datetime.datetime.now()

# Use "time" as the time column in Treasure Data
td.to_td(df, 'tutorial.tut1', con, if_exists='replace', index=False, time_col='time')

"""
Explanation: to_td inserts the current time as "time" column.  You can pass "time" column explicitly by "time_col":
End of explanation
"""


df = pd.DataFrame(np.random.rand(3, 3), columns=['x', 'y', 'z'])

# Set time series index
df.index = pd.date_range('2001-01-01', periods=3)

# Use index as the time column in Treasure Data
td.to_td(df, 'tutorial.tut1', con, if_exists='replace', index=False, time_index=0)

"""
Explanation: If you are using a time series index, set "time_index" to 0 (or the right level of indexes):
End of explanation
"""


