#Import Libraries
import teradata
import pandas as pd

#Connect to Teradata 
udaExec = teradata.UdaExec (appName="Teradata_Test", version="1.0",
        logConsole=False)

session = udaExec.connect(method="odbc", system="****",
        username="****", password="****")

#Load the data into a Pandas dataframe  
query = """
   select from where blah blah 
    """

df_transactions_2018 = pd.read_sql(query,session) 

print(df_transactions_2018.head(10))                      
                         
#mlxtend is a machine learning extension package
from mlxtend.preprocessing import TransactionEncoder
from mlxtend.frequent_patterns import apriori
from mlxtend.frequent_patterns import association_rules

#Manipulate the transaction dataframe into a basket dataframe, reset index
df_basket_2018 = df_transactions_2018.groupby('TXN_ID').PROD_ID.apply(list).reset_index()

#Print head df_basket_2018 to check if transaction data is properly manipulated
#1
print(df_basket_2018.head(15))

#Transform Pandas dataframe into a sparse Numpy boolean array with TransactionEncoder to prepare for Apriori algorithm
te = TransactionEncoder()
dset = df_basket_2018.PROD_ID.values
te_ary = te.fit(dset).transform(dset, sparse=True)
StringCols = [str(i) for i in te.columns_]

#Find frequent itemsets through the Apriori algorithm and min_supp. threshold.
aprioriData = pd.SparseDataFrame(te_ary, columns=StringCols, default_fill_value=False)
Results = apriori(aprioriData, min_support=0.01, use_colnames=True, verbose=0)
#2
print(Results)

#Generate association rules from frequent itemsets using a minimum confidence or lift threshold
rules = association_rules(Results, metric="lift", min_threshold=1)

#3
print(rules)

#Load rules dataframe back into Teradata...