<jupyter_start><jupyter_text>check for duplicate invoice numbers across the data sets<jupyter_code>print('min: ', df1['invoice'].min())
print('max: ', df1['invoice'].max())
print('min: ', df2['invoice'].min())
print('min: ', df2['invoice'].max())
i1 = set(df1['invoice'].unique().tolist())
i2 = set(df2['invoice'].unique().tolist())
same_invoice = i1.intersection(i2)
len(same_invoice)<jupyter_output><empty_output><jupyter_text>There are a large number of duplicates. These will need to be changed as this will be used as a key to join in other data once the data is split into separate tables<jupyter_code># offset the second data set information
df2['invoice'] = df2['invoice'] + 31343
i1 = set(df1['invoice'].unique().tolist())
i2 = set(df2['invoice'].unique().tolist())
same_invoice = i1.intersection(i2)
len(same_invoice)
# check the package_id for redundancies between stores
# this would be fine if I had used identical mapping to change the original item names for both stores, but I didn't
df2['package_id'] = df2['package_id'].map(lambda x: '{:.0f}'.format(x))
p1 = set(df1['package_id'].unique().tolist())
p2 = set(df2['package_id'].unique().tolist())
same_package = p1.intersection(p2)
len(same_package)<jupyter_output><empty_output><jupyter_text>Join the sales transactions data sets<jupyter_code>df = pd.concat((df1, df2), axis= 0)<jupyter_output><empty_output><jupyter_text>Separate the transaction basket and item information. Package ID can be a key to join it back<jupyter_code>transaction_contents = ['invoice', 'package_id', 'qty', 'unit_price', 'sale_amount']
transaction_status = ['invoice', 'date', 'day', 'time', 'transaction', 'category', 'store_category', 'campaign']
item_columns = ['package_id', 'item_name']

df_contents = df[transaction_contents].copy().reset_index(drop= True)
df_status = df[transaction_status].copy().reset_index(drop= True)
df_items = pd.DataFrame(df.groupby('package_id')[item_columns].max()).reset_index(drop= True) # in case there are some non-unique package_id / item_name pairs, take the most common one
df_contents.head()
df_status.head()
df_items.head()
df_markets.head()<jupyter_output><empty_output><jupyter_text>Write the dataframes to the Azure PostgreSQL database as tables<jupyter_code># sqlalchemy can be used to write a pandas dataframe into a SQL table, one row at a time
# create the sql-write engine
engine = create_engine(f'postgresql+psycopg2://{user}:{password}@{host}/{dbname}')
# df_status had an error, delete the table and start over
# define the bad_table and SQL statement
bad_table = 'demand_forecasting_transactions_contents'
delete_statement = "DROP TABLE IF EXISTS %s;"%bad_table

# Execute the drop table command
cursor.execute(delete_statement)
conn.commit()
# conn.close()
# create a psql table from df_contents
# use the pandas .to_sql function with the sqlalchemy engine
df_contents.to_sql('demand_forecasting_transactions_contents', engine) # this process will take some time
# create a psql table from df_items
df_items.to_sql('demand_forecasting_items', engine) # this process will take some time
# create a psql table from df_status
df_status.to_sql('demand_forecasting_transactions_status', engine) # this process will take some time<jupyter_output>ERROR:root:Internal Python error in the inspect module.
Below is the traceback from this internal error.<jupyter_text>This table-creation had an error (connection interrupted) and did not complete.  Unfortunately, this operation will not restart partially through a table creation (at least how it is configured here), and must be restarted. The failed-table must be dropped, and the connection commited for the drop to take effect<jupyter_code># get all table names
cursor.execute("select relname from pg_class where relkind='r' and relname !~ '^(pg_|sql_)';")
print(cursor.fetchall())
# df_status had an error, delete the table and start over
# define the bad_table and SQL statement
bad_table = 'demand_forecasting_items'
delete_statement = "DROP TABLE IF EXISTS %s;"%bad_table

# Execute the drop table command
cursor.execute(delete_statement)
conn.commit()
# conn.close()
# reconnect the psycopg2 cursor
conn = pg.connect(conn_string) 
print("Connection established")

cursor = conn.cursor()
# get all table names
cursor.execute("select relname from pg_class where relkind='r' and relname !~ '^(pg_|sql_)';")
print(cursor.fetchall())
# re-create a psql table from df_status
df_status.to_sql('demand_forecasting_transaction_status_', engine)
# create a psql table from df_markets
df_markets.to_sql('demand_forecasting_market_data', engine) # this process will take some time
# get all table names
cursor.execute("select relname from pg_class where relkind='r' and relname !~ '^(pg_|sql_)';")
print(cursor.fetchall())
# check and see if column names were included in tables
cursor.execute("""
SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'demand_forecasting_items';""") # 
print(cursor.fetchall())
# everything looks good!
# close the connection for the day
conn.close()<jupyter_output><empty_output><jupyter_text>Query from the Postgres databases<jupyter_code>test = 'demand_forecasting_transaction_status'
statement = f'''
SELECT * 
from {test};'''
cursor.execute(statement)
out1 = cursor.fetchall()

statement2 = f"""
SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_NAME = '{test}';"""
cursor.execute(statement2) 
out2 = cursor.fetchall()
names = [x[0] for x in out2]

# create the dataframe
df_test = pd.DataFrame.from_records(out1, index= 'index', columns= names) # set_index('index')
df_test.head()
df_test.columns<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11732339368122116, 'fraction_numerical': 0.007809726659566915, 'mean_word_length': 6.99290780141844, 'pattern_counts': {'":': 0, '<': 28, '<?xml version=': 0, '>': 28, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '541959', 'license': 'MIT', 'n_tokens_mistral': 1806, 'n_tokens_neox': 1755, 'n_words': 536, 'path': 'tutorials/Demand_forecasting/data_prep_to_SQL.ipynb', 'repo_name': 'jarredbultema/ts_helpers_package', 'size': '5634'}