Bottom Page

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 Insert Pandas Data Frame into Teradata DB
#1
Hi all,

I am using the Teradata python module to read transaction data from Teradata into a Pandas data frame for analysis. Now that I have done my analysis, I need to load the new data frame (rules) back into Teradata. The new data frame does not need to be altered further and only needs to be inserted into Teradata the way it is. I appreciate any help you can provide! Here is my code so far...

#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...
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Loop pandas data frame by position ? Johnse 1 98 Sep-06-2019, 12:26 AM
Last Post: scidam
  pandas data frame dervast 1 123 Aug-28-2019, 12:40 PM
Last Post: ThomasL
  Need Help With Filtering Data For Excel Files Using Pandas eddywinch82 9 446 Aug-06-2019, 03:44 PM
Last Post: eddywinch82
  Loading .csv data using Pandas zaki424160 1 263 Jul-15-2019, 09:48 AM
Last Post: perfringo
  [pandas] Convert categorical data to numbers pradeep_as400 1 336 Jun-15-2019, 08:27 AM
Last Post: ThomasL
  Creating new rows and adding them to empty data frame kapilan15 0 231 May-31-2019, 10:19 AM
Last Post: kapilan15
  [pandas]How to liner fit time series data and get linear fit equation and r square Sri 5 526 Apr-04-2019, 12:00 PM
Last Post: Sri
  Getting the difference between two times in a Data Frame soulkid555 4 408 Mar-21-2019, 11:22 AM
Last Post: perfringo
  Inserting data from python list into a pandas dataframe mahmoud899 0 385 Mar-02-2019, 04:07 AM
Last Post: mahmoud899
  save video frames into pandas data-frame tofi 0 443 Oct-18-2018, 07:02 PM
Last Post: tofi

Forum Jump:


Users browsing this thread: 1 Guest(s)