<jupyter_start><jupyter_text>Comics Rx [A comic book recommendation system](https://github.com/MangrobanGit/comics_rx) ---  A. Data Procurement and Cleaning ---  Libraries<jupyter_code>!pip install psycopg2
%matplotlib inline
%load_ext autoreload
%autoreload 2  # 1 would be where you need to specify the files
#%aimport data_fcns

import pandas as pd # dataframes
import os
import gspread_pandas
from gspread_pandas import Spread, Client # gsheets interaction

# Data storage
from sqlalchemy import create_engine # SQL helper
import psycopg2 as psql #PostgreSQL DBs
import sys
sys.path.append('..')
# Custom
import data_fcns as dfc
import keys  # Custom keys lib<jupyter_output><empty_output><jupyter_text>Part 1: Get and Prep Data  Import Arcane's transaction data  Using **Google Sheets** as a data sourceThe data is currently stored on Google drive, and is readable as a Google Sheet. I'm going to try to use a Google Sheet API to get into Pandas so I don't have to worry about it traversing across my local machine everytime I need to re-import it. Before starting you need your credentials stored on your computer. More details [here](https://gspread-pandas.readthedocs.io/en/latest/getting_started.htmlinstallation-usage).<jupyter_code>#gspread_pandas.conf.get_config()<jupyter_output><empty_output><jupyter_text>Work through `gspread_pandas` Example<jupyter_code>file_name = "http://stats.idre.ucla.edu/stat/data/binary.csv"
df = pd.read_csv(file_name)
df.head()
spread = Spread('https://docs.google.com/spreadsheets/d/19_AEcTEwHXe7LS-U1scZIHLqP0iF7W4UYMiSlBUE5bs/edit#gid=0')
spread.sheets
spread.url

spread.open_sheet(0)
spread
deef = spread.sheet_to_df(index=None)
deef<jupyter_output><empty_output><jupyter_text>**Sweet!** That example worked. I was able to download a Google Sheet into this Juypter Notebook as a `pandas dataframe`.  Let's try to get the dataset!  Get the transactions dataset **Instantiate spreadsheet object**<jupyter_code>sheet_url = 'https://docs.google.com/spreadsheets/d/1IznAOevvBbV0k3OKPImUMUESSwWXoOaASngPJUepnlU'

trans = Spread(sheet_url)<jupyter_output><empty_output><jupyter_text>**Double check details of the spreadsheet  to make sure we are looking at the correct one.**<jupyter_code>print(trans.sheets)

print(trans.url)<jupyter_output>[<Worksheet 'Copy of Detailed Sales Report.tab' id:1615090597>]
https://docs.google.com/spreadsheets/d/1IznAOevvBbV0k3OKPImUMUESSwWXoOaASngPJUepnlU<jupyter_text>**Open the sheet and dump into a Pandas dataframe**<jupyter_code>trans.open_sheet(0)
trans_df = trans.sheet_to_df(index=None, start_row=3)
trans_df.head()
trans_df.tail()<jupyter_output><empty_output><jupyter_text>We started at row 3 because I could see that the 'actual' headers didn't start until the third row. We can see the first two rows are likely just summary rows; we can take care of those down below.Seems good so far. Let's list some tasks we want to accomplish, just from inspecting the rows above:  - Standardize column headers - Make sure date sold is a `date` - Change `Account ` to a string? - Is `;Department` all the same value? In which case we can probably just drop it. --- Let's get started. I will save a copy first so I won't have to keep re-importing it from Google Sheets (in case we make a mistake).<jupyter_code>trans_df_orig = trans_df.copy()<jupyter_output><empty_output><jupyter_text>Drop rows without account numbers.This should eliminate the superfluous summary rows.<jupyter_code>trans_df = trans_df.loc[trans_df['Account #']!='',:].copy()<jupyter_output><empty_output><jupyter_text>Check the values of `;Department`<jupyter_code>trans_df[';Department'].unique()<jupyter_output><empty_output><jupyter_text>They're all the same.  Drop `;Department` column.<jupyter_code>trans_df.drop([';Department'], axis=1, inplace=True)
trans_df.head()<jupyter_output><empty_output><jupyter_text>The rest of the columns look useful. Now's a good time to change the column headers to a more standard format.`Category` looks like Publisher. Let's get the lay of the land.<jupyter_code>trans_df['Category'].value_counts()<jupyter_output><empty_output><jupyter_text>It looks like it's basically the publisher fo the comic. This is likely the context of Category relative to `;Department` of `New Comics` that we dropped earlier.Let's look at those headers again.<jupyter_code>trans_df.head(1)<jupyter_output><empty_output><jupyter_text>Assign new column names.<jupyter_code># Create list of new column names
col_names = ['publisher', 'item_id', 'title_and_num', 'qty_sold', 'date_sold', 'account_num']
trans_df.columns = col_names
trans_df.head(1)<jupyter_output><empty_output><jupyter_text>Convert Account Number to string with leading zeroesLet's look at those account numbers.<jupyter_code>trans_df['account_num'].value_counts()
trans_df['account_num'].max()<jupyter_output><empty_output><jupyter_text>That's not what I expected for max. Probably a string.<jupyter_code>trans_df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 494703 entries, 2 to 494704
Data columns (total 6 columns):
publisher        494703 non-null object
item_id          494703 non-null object
title_and_num    494703 non-null object
qty_sold         494703 non-null object
date_sold        494703 non-null object
account_num      494703 non-null object
dtypes: object(6)
memory usage: 26.4+ MB<jupyter_text>Yes, that is the case.<jupyter_code>trans_df['account_num'].astype(int).max()<jupyter_output><empty_output><jupyter_text>Ok that seems more realistic. Let's add some leading zeros. Since there are about 3K accounts, let's just settle on 5 characters.<jupyter_code>trans_df['account_num'] = trans_df['account_num'].str.zfill(5)

trans_df['account_num'].head()
trans_df.head()<jupyter_output><empty_output><jupyter_text>Convert dates Found a relatively quick date converter on StackOverflow:  https://stackoverflow.com/questions/29882573/pandas-slow-date-conversionSaved a copy in custom libary, `data_fcns`<jupyter_code>trans_df['date_sold'] = dfc.date_converter(trans_df['date_sold'])
trans_df.head()
trans_df.tail()<jupyter_output><empty_output><jupyter_text>Seems ok!  Create `comic_title` columnIndividual issues are not going to work for recommendations. We'll need to roll it up to title/volume is possible. For example: *Spider-Man* is useful; *Spider-Man 23* is not.Based on what data we have available at this time, for the time being I think the following will have to suffice to proxy `comic_title`:- Cut off the issue number off of `title_and_num`- Concatenate `publisher` on the back end to prevent any potential duplicate titles across publishers. We created function to strip off everything to the right of the pound sign () on a string, named `cut_issue_num`. Refer to the library `data_fcns`.<jupyter_code>trans_df['comic_title'] = (trans_df['title_and_num'].apply(dfc.cut_issue_num) 
                           + ' (' + trans_df['publisher'] + ')')
trans_df.head()<jupyter_output><empty_output><jupyter_text>The publisher names can be unwieldly long. I will create a dictionary to shorten so easier to digest the title names once the the publisher is concatentated. Refer to `code_archive.py` and `data_fcns.py` for development and final output.<jupyter_code>dfc.pub_dict<jupyter_output><empty_output><jupyter_text>Ok, let's go ahead and redo the `comic_title` with the shorter publisher names.<jupyter_code>trans_df['comic_title'] = (trans_df['title_and_num'].apply(dfc.cut_issue_num) +
                           ' (' + trans_df['publisher'].map(dfc.pub_dict) +
                           ')')
trans_df.head()
trans_df.tail()<jupyter_output><empty_output><jupyter_text>YES.Okay, I think the last task is to convert the `qty_sold` column to an integer.  Convert `qty_sold` to integer<jupyter_code>trans_df['qty_sold'] = trans_df['qty_sold'].astype(int)
trans_df.head()
trans_df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 494703 entries, 2 to 494704
Data columns (total 7 columns):
publisher        494703 non-null object
item_id          494703 non-null object
title_and_num    494703 non-null object
qty_sold         494703 non-null int64
date_sold        494703 non-null datetime64[ns]
account_num      494703 non-null object
comic_title      494703 non-null object
dtypes: datetime64[ns](1), int64(1), object(5)
memory usage: 30.2+ MB<jupyter_text>OK! I think this is all we can do for now. Let's save this to a DB.  Part 2: Save to AWS DB  Set up AWS connection to PostgreSQL DB<jupyter_code># Define path to secret
secret_path_aws = os.path.join(os.environ['HOME'], '.secret',
                               'aws_ps_flatiron.json')
secret_path_aws
aws_keys = keys.get_keys(secret_path_aws)
user = aws_keys['user']
ps = aws_keys['password']
host = aws_keys['host']
db = aws_keys['db_name']

aws_ps_engine = ('postgresql://' + user + ':' + ps + '@' + host + '/' + db)

# Setup PSQL connection
conn = psql.connect(database=db,
                    user=user,
                    password=ps,
                    host=host,
                    port='5432')<jupyter_output><empty_output><jupyter_text>Write dataframe to DB Use SQLAlchemy to create PSQL engine<jupyter_code># dialect+driver://username:password@host:port/database
sql_alch_engine = create_engine(aws_ps_engine)<jupyter_output><empty_output><jupyter_text>BELOW HAS BEEN SET TO MARKDOWN BECAUSE IT ONLY NEED TO BE RUN ONCE  Use built-in pandas functionality to write to AWStrans_tbl_nm = 'comic_trans'trans_df.to_sql(trans_tbl_nm, con=sql_alch_engine, if_exists='append',                 chunksize=3000) Let's check the records.<jupyter_code># Setup PSQL connection
conn = psql.connect(database=db,
                    user=user,
                    password=ps,
                    host=host,
                    port='5432')
# Instantiate cursor
cur = conn.cursor()
#  Count records.
query = """
    SELECT count(*) from comic_trans;
"""
# Execute the query
cur.execute(query)
# Check results
temp_df = pd.DataFrame(cur.fetchall())
temp_df.columns = [col.name for col in cur.description]
temp_df<jupyter_output><empty_output><jupyter_text>And let's remind ourselves how many records we had in our dataframe:<jupyter_code>trans_df.shape<jupyter_output><empty_output><jupyter_text>Let's also pull a sample and see if the data returns correctly.<jupyter_code># Get sample of 10 records
query = """
    SELECT * from comic_trans limit 10;
"""
# Execute the query
cur.execute(query)
# Use if execute fails
# conn.rollback()
# Check results
temp_df = pd.DataFrame(cur.fetchall())
temp_df.columns = [col.name for col in cur.description]
temp_df.head()<jupyter_output><empty_output><jupyter_text>Great. Now, just because I'm risk-averse, let's back up the table to another copy.  BELOW HAS BEEN SET TO MARKDOWN BECAUSE IT ONLY NEED TO BE RUN ONCE  create dupe tablequery = """    create table comic_trans_20190624 as    select * from comic_trans;"""<jupyter_code># Execute the query
cur.execute(query)<jupyter_output><empty_output><jupyter_text>Now check the dupe table.<jupyter_code># Check results
temp_df = pd.DataFrame(cur.fetchall())
temp_df.columns = [col.name for col in cur.description]
temp_df.head()<jupyter_output><empty_output><jupyter_text>Looks good!Now that we have a decent dataset we can move forward to building our first draft model.  Prepping data for PySpark There is a way to use JDBC drivers to import directly from PostgreSQL to Spark, it seems. But the time to get that up and running seems very high. So for now, let's go basic and write it back out to text file, and use that as input to be read into Spark.  BELOW HAS BEEN SET TO MARKDOWN BECAUSE IT ONLY NEED TO BE RUN ONCE trans_df.to_json('raw_data/trans.json', orient='records', lines=True)<jupyter_code>!head raw_data/trans.json<jupyter_output><empty_output><jupyter_text>--- **Reverse record check from the ALS notebook**<jupyter_code>trans_df.loc[trans_df['item_id'] == 'DCD151935']<jupyter_output><empty_output><jupyter_text>Create `comic_id`Each `comic_title` will need an integer `comic_id`, since that is what is supported by the matrix factorization process via ALS.<jupyter_code># Setup PSQL connection
conn = psql.connect(
    database=db,
    user=user,
    password=ps,
    host=host,
    port='5432'
)
# Instantiate cursor
cur = conn.cursor()<jupyter_output><empty_output><jupyter_text>BELOW HAS BEEN SET TO MARKDOWN BECAUSE IT ONLY NEED TO BE RUN ONCE   Create comics tablequery = """    CREATE TABLE comics AS    SELECT         row_number() over (ORDER BY comic_title) AS comic_id        ,comic_title     FROM         comic_trans    GROUP BY         comic_title     ORDER BY 1    ;"""<jupyter_code># Execute the query
cur.execute(query)
conn.rollback()
conn.commit()<jupyter_output><empty_output><jupyter_text>Let's check that the table is there!<jupyter_code>#  Get sample from comics.
query = """
    SELECT * from comics;
"""
# Instantiate cursor
cur = conn.cursor()
# Execute the query
cur.execute(query)
# Check results
temp_df = pd.DataFrame(cur.fetchall())
temp_df.columns = [col.name for col in cur.description]
temp_df.head()
temp_df.shape<jupyter_output><empty_output><jupyter_text>Now let's output to JSON for use in the ALS workflow.<jupyter_code>!pwd<jupyter_output><empty_output><jupyter_text>BELOW HAS BEEN SET TO MARKDOWN BECAUSE IT ONLY NEED TO BE RUN ONCE temp_df.to_json('support_data/comics.json', orient='records', lines=True)<jupyter_code>!head support_data/comics.json<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0985247238490622, 'fraction_numerical': 0.01586477870857736, 'mean_word_length': 5.23959296947271, 'pattern_counts': {'":': 0, '<': 145, '<?xml version=': 0, '>': 145, 'https://': 6, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '36', 'dir': 'jupyter-structured-clean-dedup', 'id': '280974', 'license': 'MIT', 'n_tokens_mistral': 4687, 'n_tokens_neox': 4403, 'n_words': 1279, 'path': 'comrx/dev/comics_rx-01_data_prep.ipynb', 'repo_name': 'MangrobanGit/comic_rx', 'size': '13489'}